![\"Mysql](\"https:\/\/linuxadmin.io\/wp-content\/uploads\/2017\/04\/MySQL-Logo-2-e1493431150113.png\"){kind=logo}
<\/p>\n<\/p>\n
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.<\/p>\n
Install MySQL on both servers:<\/p>\n
yum install -y mysql-server mysql-client mysql-devel<\/p>\n
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):<\/p>\n
server-id = 1<\/p>\n
On the master ensure a bin log is set in \/etc\/my.cnf<\/p>\n
log_bin = \/var\/log\/mysql\/mysql-bin.log<\/p>\n
Restart mysql<\/p>\n
service mysqld restart<\/p>\n
Connect o mysql on the master<\/p>\n
mysql -u root -p<\/p>\n
Grant privileges to the slave<\/p>\n
GRANT REPLICATION SLAVE ON *.* TO ‘slave’@’%’ IDENTIFIED BY ‘password’;<\/p>\n
Load the new privileges<\/p>\n
FLUSH PRIVILEGES;<\/p>\n
Lock the MySQL master so no new updates can be written while you are creating the slave<\/p>\n
FLUSH TABLE WITH READ LOCK;<\/p>\n
Get the current master status<\/p>\n
SHOW MASTER STATUS;<\/p>\n
This will return a similar result to this:<\/p>\n
mysql> SHOW MASTER STATUS;
\n+——————+———-+————–+——————+
\n| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
\n+——————+———-+————–+——————+
\n| mysql-bin.000001 | 107 | | |
\n+——————+———-+————–+——————+
\n1 row in set (0.00 sec)<\/p>\n
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.<\/p>\n
Open a new ssh client and dump the databases<\/p>\n
mysqldump -u root -p –all-databases > all.sql<\/p>\n
If it is particularly large mysql server, you can rsync all of \/var\/lib\/mysql<\/p>\n
Once the copy has completed go ahead and type the following on the MySQL master:<\/p>\n
UNLOCK TABLES;<\/p>\n
Go ahead and quit on the master<\/p>\n
Import the databases on the slave<\/p>\n
mysql < all.sql<\/p>\n
You should also enabled the server-id in \/etc\/my.cnf and restart it<\/p>\n
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:<\/p>\n
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;<\/p>\n
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:<\/p>\n
START SLAVE;<\/p>\n
To check current slave status to<\/p>\n
SHOW SLAVE STATUS;<\/p>\n
This is a basic Master-Slave Mysql replication configuration.<\/p>\n
Apr 29, 2017LinuxAdmin.io<\/p>\n