Difference between revisions of "MySQL Replication"

Revision as of 19:15, 5 March 2015

MySQL replication allows you to replicate databases from a master server to a slave server. This is a one way replication only: Master -> Slave. Data changes are automatically made on the slave server. This setup is only available for our VPS products. This guide assumes you have a working installation of MySQL installed on both the Master and Slave server.

Setting up replication

On the Master server

1. Edit the my.ini (Windows) or my.cnf file (Linux). On Windows this is normally in MySQL installation directory (C:\MySQL or C:\Program Files\MySQL\). On Linux it is normally /etc/my.cnf or /etc/mysql/my.cnf.
2. Under the [mysqld] section add:

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

3. Restart the MySQL service

On the Slave server

1. Edit the my.ini or my.cnf file
2. Under the [mysqld] section add:

   server-id=2

3. Restart the MySQL service

On the Master server

1. You'll need to create a replication user that slave server will be using to connect to the MySQL master. Open the MySQL prompt and run the following to create the user:

2. GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'10.10.0.65' IDENTIFIED BY 'slavepass';

   Be sure to replace 10.10.0.65 with the real IP the slave will be connecting from and 'slavepass' with a real password for the replication user.
3. Then run

   Flush privileges;

4. Test connecting to the mysql master server with this new user you just created from the slave server to make sure it's working.
5. Now you'll need to get the current log position from MySQL master. At the MySQL prompt run:

   FLUSH TABLES WITH READ LOCK;
   SHOW MASTER STATUS;

   This will show you the current log file and position. Save this information for the next step and run:

   UNLOCK TABLES;

On the Slave server Finalize replication config

1. At the mysql prompt run:

   CHANGE MASTER TO
   MASTER_HOST='10.10.0.60',
   MASTER_USER='replication_user',
   MASTER_PASSWORD='slavepass',
   MASTER_LOG_FILE='mysql-bin.000003',
   MASTER_LOG_POS=239;
   START SLAVE;

   Be sure to replace 10.10.0.60 with the real IP of the master server, 'slavepass' with a real password for the replication user, MASTER_LOG_FILE and MASTER_LOG_POS with the information from the previous step.
2. Run the following to check the status of the slave:

   SHOW SLAVE STATUS\G;

   This will show if you if the slave is working.

Additional Info

For additional info refer to this MySQL doc: https://dev.mysql.com/doc/refman/5.5/en/replication.html