MySQL Replication

How to Set Up MySQL Replication

For detailed instructions read the MySQL reference manual: http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html The following instructions are provided for your convenience. We do not provide support for configuring replication on your MySQL server.

Create User for Replication

Execute these commands on your MySQL master while logged in as root. Replace SLAVEUSER and SLAVEPASS with the user and password that youwant to create. For increased security replace % with the IP of your MySQL slave.

CREATE USER 'SLAVEUSER'@'%' IDENTIFIED BY 'SLAVEPASS';
GRANT REPLICATION SLAVE ON *.* TO 'SLAVEUSER'@'%';

MySQL Master Configuration

Add the following to your MySQL master's my.ini or my.cnf under [mysqld]. Then restart the MySQL service.

log-bin=mysql-bin
server-id=1
sync_binlog=1

Obtain the Master Binary Log Coordinates

Log in to your MySQL master as root and execute the following commands. DO NOT CLOSE THIS MYSQL SESSION YET. These commands lock your database to prevent writing. This is required to guarantee data integrity while the data is being transferred to the MySQL slave. Your TCAdmin control panel might not work correctly while the tables are locked.

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

You should see output similar to the following. The log file name and position will be required for the following steps.

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |   609384 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Transfer the Current Database to the MySQL Slave

Create an empty database and user on the MySQL slave. The database should be named the same as your TCAdmin database on the MySQL master. Execute the following command from a command prompt or SSH to backup and restore the database to the slave. Replace the values as needed. On Windows, mysqldump.exe and mysql.exe are located in the MySQL\bin folder.

mysqldump -hMASTERIP -uMASTERDBUSER -pMASTERDBPASS TCADMINDB | mysql -hSLAVEIP -uSLAVEDBUSER -pSLAVEDBPASS --database=TCADMINDB

After the command completes go to the session connected to the MySQL master and execute this command to unlock the tables.

UNLOCK TABLES;

MySQL Slave Configuration

Add the following to your MySQL Slave's my.ini or my.cnf under [mysqld]. Replace the values as needed. Then restart the MySQL service.

#each slave should have a unique id
server-id=2
#enter the name of the database that will be replicated
replicate-do-db=tcadmin2
#make the database read only to prevent accidental writing on the slave
read-only

After restarting the service log in to the MySQL Slave server as root. Execute the following commands. Replace the values as needed.

stop slave;
change master to master_host='MYSQLMASTERIP', master_user='REPLICATIONUSER', master_password='REPLICATIONPASSWORD', master_log_file='LOGFILENAME', master_log_pos=LOGFILEPOSITION;
start slave;

Testing your Configuration

Log in to your MySQL master and execute these commands. Replace the database name as needed. This command will update the admin's last name to 'REPLICATION SUCCESS'. You can change it back later.

use tcadmin2;
UPDATE tc_users set last_name='REPLICATION SUCCESS' WHERE user_id=3;

Now log in to your MySQL slave and execute these commands. Replace the database name as needed.

use tcadmin2;
select last_name from tc_users where user_id=3;

If you see the following it means you have configured replication correctly.

+---------------------+
| last_name           |
+---------------------+
| REPLICATION SUCCESS |
+---------------------+
1 row in set (0.00 sec)

Adding More Slaves

The process to add more slaves is exactly the same. Just make sure you assign a unique server-id to the slave's my.ini or my.cnf. If you specified the replication user's IP you will need to create a new user with the new slave's IP.

Configuring TCAdmin

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<values>
  <add key="TCAdmin.Database.MySql.ConnectionString" value="Data Source=MYSQLMASTERIP;User Id=MYSQLMASTERUSER;Password=MYSQLMASTERPASS;Database=TCADMINDB;Pooling=false;Compress=false;Connection Lifetime=900;" type="System.String,mscorlib" />
  <add key="TCAdmin.Database.MySql.ConnectionString.Encrypted" value="False" type="System.Boolean,mscorlib" />
</values>
Retrieved from "https://help.tcadmin.com/index.php?title=MySQL_Replication&oldid=570"