247AppsStudio Blog

Setting up MySQL replication In this article the Master is 210.126.187.015 and the Slave is 210.126.187.016

On the Master, edit /etc/mysql/my.cnf (my.ini in Windows):
1) server-id = 126187015 # choose a unique integer
2) log_bin = /var/log/mysql/mysql-bin.log
3) bind-address = 0.0.0.0 # listen on all interfaces (by default 127.0.0.1 is used)
4) binlog_do_db = mydb # replicate only the ‘mydb’ database

On Windows environment:
1) server-id = 127001 # choose a unique integer
2) log_bin = C:/Documents and Settings/JohnDoe/My Documents/Application Data/MySQL/MySQL56/mysql-bin.log
3) bind-address = 0.0.0.0 # listen on all interfaces (by default 127.0.0.1 is used)
4) binlog_do_db = mydb # replicate only the ‘mydb’ database

Test your new configuration for syntax error:
1 bash# mysqld –help

Make sure the skip-networking option is not enabled. OFF on Windows
Make sure server-id is unique. You can for instance use the last 3 octets of the IP address of the server.

Create a replication user on the Master using the IP address of the Slave that will access it (200.126.187.66):
1) mysql> select version();
2) +——————-+
3) | version() |
4) +——————-+
5) | 5.1.37-1ubuntu5.1 |
6) +——————-+
7) 1 row in set (0.00 sec)
8 ) mysql> CREATE USER ‘replication’@’210.126.187.016’ IDENTIFIED BY ‘slavepass’;
9) mysql> GRANT REPLICATION SLAVE ON mydb.users TO ‘replication’@’200.126.187.66’;
10) mysql> GRANT SELECT ON mydb.users TO replication;

Restart MySQL for the changes to take effect:
1 bash# /etc/init.d/mysql restart (You can use MySQL Notifier or Command to restart server on Windows)

On the Slave, edit /etc/mysql/my.cnf to set the server-id:
1 server-id = 126187016 # choose a unique integer

Copy the current database on the Master with mysqldump and load it on the Slave.

Check the current status of the Master by retrieving its log file name and position:
1) mysql> SHOW MASTER STATUS;
2) +——————+———-+————–+——————+
3) | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
4) +——————+———-+————–+——————+
5) | mysql-bin.000001 | 23577 | mydb | |
6) +——————+———-+————–+——————+
7) 1 row in set (0.00 sec)

Start the replication on the Slave.

mysql> CHANGE MASTER TO MASTER_HOST='210.126.187.xxx', MASTER_USER='replication', MASTER_PASSWORD='slavepasswd',
 MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0;
mysql> START SLAVE;

We did take some shortcuts here: the proper way would have been to:
1/ prevent the Master from committing anything
2/ backup the DB on the Master
3/ check the log file name and log position on the Master (‘SHOW MASTER STATUS’)
4/ load the backup on the Slave
5/ start replication using the log file name and position on the Master
6/ unlock the Master so the commits can happen again

A nightmare if the Master is already a live production server because it means from step 1/ to 6/ the Master is dead not committing anything.

As we did a ‘dirty’ backup, the replication will bump into errors that will stop the Slave (mainly because of entries already on the Slave, so we can safely skip them).
List them on the Slave with ’show slave status \G’ (look at the ‘Last_Error’) and if you can skip it, do it this way:
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;

Continue skipping the failed insert (if they can be skipped of course) until the slave completely catches up with the Master.
How do I know when the Slave is done catching up with the Master?

Issue a ‘SHOW SLAVE STATUS’ on the Slave:
mysql> SHOW SLAVE STATUS \G