This article is part of a series of setting up MySQL replication. As with most things, there is always more than one way to do something. In the case of setting up MySQL replication, or rebuilding it, some options are better than others depending on your use case.
The articles in the series are below:
Setting up MySQL Replication using mysqldump
Setting up MySQL Replication using Percona XtraBackup
Setting up MySQL Replication using Rsync
Setting up MySQL Replication using LVM snapshots
This guide will document how to setup MySQL Master / Slave Replication using mysqldump.
MySQL Master / Slave Replication is useful for a number of situations, such as:
– Perform hourly backups on the Slave without locking the Master database
– Provide a degree of redundancy as you can promote the Slave
– Splitting the reads between the Master and Slave
Setting up MySQL Replication is pretty straight forward. Keep in mind that this guide requires you use mysqldump to create a backup of your existing databases, which will lock your tables while it runs. So be sure to schedule it accordingly!
Setup the Master MySQL server
Configure the my.cnf as shown below:
log-bin=/var/lib/mysql/db01-binary-log expire-logs-days=5 server-id=1
Then restart MySQL to apply the settings:
# CentOS / RHEL: [root@db01 ~]# service mysqld restart # Ubuntu / Debian: [root@db01 ~]# service mysql restart
Finally, grant access to the Slave so it has access to communicate with the Master:
mysql> GRANT REPLICATION SLAVE ON *.* to 'repl’@’10.x.x.x’ IDENTIFIED BY 'your_password';
Setup the Slave MySQL server
Configure the my.cnf as shown below:
relay-log=/var/lib/mysql/db02-relay-log relay-log-space-limit = 4G read-only=1 server-id=2
Then restart MySQL to apply the settings:
# CentOS / RHEL: [root@db02 ~]# service mysqld restart # Ubuntu / Debian: [root@db02 ~]# service mysql restart
Finally, prep the slave server to connect to the Master MySQL server by:
# For MySQL 5.0 and 5.1 mysql> STOP SLAVE; RESET SLAVE; # For MySQL 5.5 and 5.6 mysql> STOP SLAVE; RESET SLAVE ALL; # Then run this command for all MySQL versions mysql> CHANGE MASTER TO MASTER_HOST=’10.x.x.x’, MASTER_USER='repl', MASTER_PASSWORD='your_password'
RESET SLAVE will remove any previously configuration replication settings. Also, the MASTER_LOG_FILE and MASTER_LOG_POS is not needed as the following section will be dumping the databases with the –master-data flag.
Importing the databases onto the Slave MySQL server
A dump of the Master MySQL server is needed in order to get the Slave MySQL server in sync. Please note that this will lock your tables while this runs!
Perform this on the master server by:
[root@db01 ~]# mysqldump --all-databases --master-data | gzip -1 > /root/all.sql.gz
Now transfer over the database dump to the Slave MySQL server:
[root@db01 ~]# scp /root/all.sql.gz [email protected]:/root/
On the MySQL Slave server, import the dump by running:
[root@db02 ~]# zcat /root/all.sql.gz | mysql [root@db02 ~]# mysql mysql> FLUSH PRIVILEGES; mysql> quit
As this dump contains the internal database ‘mysql’, all the passwords, including root, will be the same as they are on the Master MySQL server. So be sure to update /root/.my.cnf with the proper credentials:
[root@db02 ~]# vi /root/.my.cnf [client] user=root password=db01_root_mysql_password
Also be sure to update any passwords your database backup scripts may use to reflect the new credentials!
Start and test replication
Start replication on the Slave MySQL server by:
[root@db02 ~]# mysql mysql> START SLAVE;
And verify replication is online and working by confirming the following output:
mysql> SHOW SLAVE STATUS\G ... Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 ...
If those values are the same as what is shown above, then replication is working properly! Perform a final test by creating a test database on the Master MySQL server, then check to ensure it shows up on the Slave MySQL server. Afterwards, feel free to drop that test database on the Master MySQL server.
From here, you should be good to go! Just be sure to setup a monitoring check to ensure that replication is always running and doesn’t encounter any errors. A very basic MySQL Replication check can be found here:
https://github.com/stephenlang/system-health-check