![\"How](\"https:\/\/www.rosehosting.com\/blog\/wp-content\/uploads\/2016\/06\/How-to-setup-MySQL-replication-on-CentOS.jpg\")
<\/p>\n
In this tutorial we will show you how to setup basic MySQL replication on two CentOS 7 servers<\/a>, but the same steps should work on all RPM based systems. We will use two test CentOS 7 virtual servers for this tutorial with the following IP addresses:<\/p>\n
\nMASTER:<\/strong> 123.456.789.1
\nSLAVE:<\/strong> 123.456.789.2<\/p>\n
Master Server:<\/strong><\/h3>\n\nTable of Contents<\/p>\n
- 1. Login via SSH<\/a><\/li>
- 2. Install MySQL server<\/a><\/li>
- 3.\u00a0Start the MySQL server<\/a><\/li>
- 4. Configure\u00a0MySQL server<\/a><\/li>
- 5.\u00a0 Restart MySQL server<\/a><\/li>
- 6. Create MySQL user for the slave server and configure it<\/a><\/li>
- 7. Login via SSH and install MySQL Server on the Slave Server<\/a><\/li>
- 8. Restart MySQL Server<\/a><\/li>
- 9. Configure MySQL Server<\/a><\/li><\/ul><\/nav><\/div>\n
<\/span>1. Login via SSH<\/span><\/h2>\nLogin to the master server as user root<\/p>\n
ssh root@IP_Address<\/pre>\n<\/span>2. Install MySQL server<\/span><\/h2>\nand install MySQL server if it is not already installed<\/p>\n
yum install mysql-server mysql<\/pre>\n<\/span>3.\u00a0Start the MySQL server<\/span><\/h2>\nStart the MySQL server and enable it to start at boot time<\/p>\n
systemctl start mysql\nsystemctl enable mysql<\/pre>\n<\/span>4. Configure\u00a0MySQL server<\/span><\/h2>\nNext, open the MySQL configuration file with a text editor and add the following lines<\/p>\n
vi \/etc\/my.cnf<\/pre>\nserver-id = 1\nbinlog-do-db=database\nrelay-log = mysql-relay-bin\nrelay-log-index = mysql-relay-bin.index\nlog-bin = mysql-bin<\/pre>\nDon’t forget to replace ‘database<\/em>‘ with the actual name of the database that you want to replicate to the slave server.<\/p>\n
<\/span>5.\u00a0 Restart MySQL server<\/span><\/h2>\nSave the configuration file and restart the MySQL server for the changes to take effect<\/p>\n
systemctl restart mysql<\/pre>\n<\/span>6. Create MySQL user for the slave server and configure it<\/span><\/h2>\nLogin to the MySQL server as user root and create a user that will be used by the slave<\/p>\n
mysql -u root -p<\/pre>\nmysql> GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY 'PASSWORD'\nmysql> FLUSH PRIVILEGES;\nmysql> FLUSH TABLES WITH READ LOCK;\nmysql> SHOW MASTER STATUS;\nmysql> SHOW MASTER STATUS;\n+------------------+----------+--------------+------------------+\n| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |\n+------------------+----------+--------------+------------------+\n| mysql-bin.000001 | 245 | database | |\n+------------------+----------+--------------+------------------+\n1 row in set (0.00 sec)\n\nmysql> exit<\/pre>\nWe will need the master’s coordinates that are going to be used for the slave server, so write them down.<\/p>\n
File:<\/strong> mysql-bin.000001
\nPosition:<\/strong> 245<\/em><\/p>\n
Replace ‘PASSWORD<\/em>‘ with an actual strong password.<\/p>\n
Now, if the database you want to replicate is not empty, create a dump from the database<\/p>\n
mysqldump -u root -p --master-data database > \/root\/database.sql<\/pre>\nand transfer the dump file to the slave server<\/p>\n
rsync -Waq -e ssh \/root\/database.sql 123.456.789.2:\/root<\/pre>\nSlave Server:<\/strong><\/h3>\n<\/span>7. Login via SSH and install MySQL Server on the Slave Server<\/span><\/h2>\nNext step is to configure the slave server. Login to the slave server via SSH as user root and install MySQL server as we did on the master server.<\/p>\n
After the MySQL server is installed and started, open its configuration file and add the following lines:<\/p>\n
vi \/etc\/my.cnf<\/pre>\nserver-id = 2\nmaster-host=123.456.789.1\nmaster-connect-retry=60\nmaster-user=slaveuser\nmaster-password=PASSWORD\nreplicate-do-db=database\nrelay-log = mysql-relay-bin\nlog-bin = mysql-bin<\/pre>\n<\/span>8. Restart MySQL Server<\/span><\/h2>\nSave the file and restart the MySQL server for the changes to change effect.<\/p>\n
systemctl restart mysql<\/pre>\nImport the MySQL dump file we created on the master server<\/p>\n
mysql -u root -p < \/root\/database.sql<\/pre>\n<\/span>9. Configure MySQL Server<\/span><\/h2>\nLogin to MySQL as user root stop the slave and configure it where to look for the ‘master log file’<\/p>\n
mysql -u root -p<\/pre>\nmysql> slave stop;\nmysql> CHANGE MASTER TO MASTER_HOST='123.456.789.1', MASTER_USER='slaveuser', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245;\nmysql> slave start;<\/pre>\nUse the MySQL username and password we created on the master server.<\/p>\n
You can check the slave status by executing the following MySQL command:<\/p>\n
mysql> show slave status;<\/pre>\n*************************** 1. row ***************************\n Slave_IO_State: Waiting for master to send event\n Master_Host: 123.456.789.1\n Master_User: slaveuser\n Master_Port: 3306\n Master_Log_File: mysql-bin.000001\n Slave_IO_Running: Yes\n Slave_SQL_Running: Yes\n Replicate_Do_DB: database<\/pre>\nIf ‘Slave_IO_State<\/em>‘ reports ‘Waiting for master to send event’ the replication is successful.<\/p>\n
That is all. It’s that easy. If you need more information on how to setup a replication master configuration you can find it on MySQL’s official website.<\/a><\/p>\n
\nOf course you don\u2019t have to\u00a0setup MySQL replication on CentOS, if you use one of our MySQL Hosting services<\/a>, in which case you can simply ask our expert Linux admins to setup MySQL replication on CentOS for you. They are available 24\u00d77 and will take care of your request immediately. You can also check new updates on How to Install MySQL on CentOS 7<\/a>.<\/p>\n
Table of Contents<\/p>\n
- 1. Login via SSH<\/a><\/li>
- 2. Install MySQL server<\/a><\/li>
- 3.\u00a0Start the MySQL server<\/a><\/li>
- 4. Configure\u00a0MySQL server<\/a><\/li>
- 5.\u00a0 Restart MySQL server<\/a><\/li>
- 6. Create MySQL user for the slave server and configure it<\/a><\/li>
- 7. Login via SSH and install MySQL Server on the Slave Server<\/a><\/li>
- 8. Restart MySQL Server<\/a><\/li>
- 9. Configure MySQL Server<\/a><\/li><\/ul><\/nav><\/div>\n
<\/span>1. Login via SSH<\/span><\/h2>\n
Login to the master server as user root<\/p>\n
ssh root@IP_Address<\/pre>\n
<\/span>2. Install MySQL server<\/span><\/h2>\n
and install MySQL server if it is not already installed<\/p>\n
yum install mysql-server mysql<\/pre>\n
<\/span>3.\u00a0Start the MySQL server<\/span><\/h2>\n
Start the MySQL server and enable it to start at boot time<\/p>\n
systemctl start mysql\nsystemctl enable mysql<\/pre>\n
<\/span>4. Configure\u00a0MySQL server<\/span><\/h2>\n
Next, open the MySQL configuration file with a text editor and add the following lines<\/p>\n
vi \/etc\/my.cnf<\/pre>\n
server-id = 1\nbinlog-do-db=database\nrelay-log = mysql-relay-bin\nrelay-log-index = mysql-relay-bin.index\nlog-bin = mysql-bin<\/pre>\n
Don’t forget to replace ‘database<\/em>‘ with the actual name of the database that you want to replicate to the slave server.<\/p>\n
<\/span>5.\u00a0 Restart MySQL server<\/span><\/h2>\n
Save the configuration file and restart the MySQL server for the changes to take effect<\/p>\n
systemctl restart mysql<\/pre>\n
<\/span>6. Create MySQL user for the slave server and configure it<\/span><\/h2>\n
Login to the MySQL server as user root and create a user that will be used by the slave<\/p>\n
mysql -u root -p<\/pre>\n
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY 'PASSWORD'\nmysql> FLUSH PRIVILEGES;\nmysql> FLUSH TABLES WITH READ LOCK;\nmysql> SHOW MASTER STATUS;\nmysql> SHOW MASTER STATUS;\n+------------------+----------+--------------+------------------+\n| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |\n+------------------+----------+--------------+------------------+\n| mysql-bin.000001 | 245 | database | |\n+------------------+----------+--------------+------------------+\n1 row in set (0.00 sec)\n\nmysql> exit<\/pre>\n
We will need the master’s coordinates that are going to be used for the slave server, so write them down.<\/p>\n
File:<\/strong> mysql-bin.000001
\nPosition:<\/strong> 245<\/em><\/p>\nReplace ‘PASSWORD<\/em>‘ with an actual strong password.<\/p>\n
Now, if the database you want to replicate is not empty, create a dump from the database<\/p>\n
mysqldump -u root -p --master-data database > \/root\/database.sql<\/pre>\n
and transfer the dump file to the slave server<\/p>\n
rsync -Waq -e ssh \/root\/database.sql 123.456.789.2:\/root<\/pre>\n
Slave Server:<\/strong><\/h3>\n
<\/span>7. Login via SSH and install MySQL Server on the Slave Server<\/span><\/h2>\n
Next step is to configure the slave server. Login to the slave server via SSH as user root and install MySQL server as we did on the master server.<\/p>\n
After the MySQL server is installed and started, open its configuration file and add the following lines:<\/p>\n
vi \/etc\/my.cnf<\/pre>\n
server-id = 2\nmaster-host=123.456.789.1\nmaster-connect-retry=60\nmaster-user=slaveuser\nmaster-password=PASSWORD\nreplicate-do-db=database\nrelay-log = mysql-relay-bin\nlog-bin = mysql-bin<\/pre>\n
<\/span>8. Restart MySQL Server<\/span><\/h2>\n
Save the file and restart the MySQL server for the changes to change effect.<\/p>\n
systemctl restart mysql<\/pre>\n
Import the MySQL dump file we created on the master server<\/p>\n
mysql -u root -p < \/root\/database.sql<\/pre>\n
<\/span>9. Configure MySQL Server<\/span><\/h2>\n
Login to MySQL as user root stop the slave and configure it where to look for the ‘master log file’<\/p>\n
mysql -u root -p<\/pre>\n
mysql> slave stop;\nmysql> CHANGE MASTER TO MASTER_HOST='123.456.789.1', MASTER_USER='slaveuser', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245;\nmysql> slave start;<\/pre>\n
Use the MySQL username and password we created on the master server.<\/p>\n
You can check the slave status by executing the following MySQL command:<\/p>\n
mysql> show slave status;<\/pre>\n
*************************** 1. row ***************************\n Slave_IO_State: Waiting for master to send event\n Master_Host: 123.456.789.1\n Master_User: slaveuser\n Master_Port: 3306\n Master_Log_File: mysql-bin.000001\n Slave_IO_Running: Yes\n Slave_SQL_Running: Yes\n Replicate_Do_DB: database<\/pre>\n
If ‘Slave_IO_State<\/em>‘ reports ‘Waiting for master to send event’ the replication is successful.<\/p>\n
That is all. It’s that easy. If you need more information on how to setup a replication master configuration you can find it on MySQL’s official website.<\/a><\/p>\n
\nOf course you don\u2019t have to\u00a0setup MySQL replication on CentOS, if you use one of our MySQL Hosting services<\/a>, in which case you can simply ask our expert Linux admins to setup MySQL replication on CentOS for you. They are available 24\u00d77 and will take care of your request immediately. You can also check new updates on How to Install MySQL on CentOS 7<\/a>.<\/p>\n