MySQL Replication Master Slave Setup

MySQL Replication allows you to synchronize slave copies of a MySQL server. You can then use the slave to perform backups and a recovery option if the master should go offline for any reason. MySQL needs to be installed on both servers.

Install MySQL on both servers:

yum install -y mysql-server mysql-client mysql-devel

Edit /etc/my.cnf on both servers and set a unique numerical server id(any number is fine as long as they are not the same):

server-id = 1

Configure MySQL Replication On The Master

On the master ensure a bin log is set in /etc/my.cnf

log_bin = /var/log/mysql/mysql-bin.log

Restart mysql

service mysqld restart

Connect o mysql on the master

mysql -u root -p

Grant privileges to the slave

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'password';

Load the new privileges

FLUSH PRIVILEGES;

Lock the MySQL master  so no new updates can be written while you are creating the slave

FLUSH TABLE WITH READ LOCK;

Get the current master status

SHOW MASTER STATUS;

This will return a similar result to this:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 |  | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

This is the position the slave will be on, save this information for later. You will need to keep the mysql client open on the master. If you close it the read lock will stop and will cause replication issues when trying to sync it.

Open a new ssh client and dump the databases

mysqldump -u root -p --all-databases > all.sql

If it is particularly large mysql server, you can rsync all of /var/lib/mysql

Once the copy has completed go ahead and type the following on the MySQL master:

UNLOCK TABLES;

Go ahead and quit on the master

Configure MySQL Replication On The Slave

Import the databases on the slave

mysql < all.sql

You should also enabled the server-id in /etc/my.cnf and restart it

Once it has been restarted and the databases have been imported.  You can setup the replication with the following with the following command in the mysql client:

CHANGE MASTER TO MASTER_HOST='IP ADDRESS OF MASTER',MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  107;

Change MASTER_LOG_FILE and MASTER_LOG_POS to the values you got earlier from the master. Once you have entered the above command go ahead and start the slave:

START SLAVE;

To check current slave status to

SHOW SLAVE STATUS;

This is a basic Master-Slave Mysql replication configuration.