A. MASTER Configuration
1. Remove or comment out following 2 lines in /etc/my.cnf config file:
skip-networking
bind-address = 127.0.0.1
2. ADD these 3 lines in /etc/my.cnf :
log-bin = mysql-bin
binlog-do-db = sample_database
server-id=2
*Line 1: Instructs mysql what log file to use.
*Line 2: Instructs mysql which database to replicate
*Line 3: Instructs mysql that this machine is the master.
3. Restart mysqld or mariadb daemon (service mariadb restart)
4. Connect to mysql using root user & run following commands one by one :
GRANT REPLICATION SLAVE ON *.* TO 'YOUR_USER'@'SLAVE_IP_ADDRESS' IDENTIFIED BY 'YOUR_PASSWORD';
(New user creation & Grant Replication permissions.)
FLUSH PRIVILEGES;
USE sample_database; (sample_database is the database name we want to replicate)
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
Example :
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.005 | 102 | sample_database | |
+------------------+----------+--------------+------------------+
(Note Down this info that you get after running this command, we'll require it while setting up SLAVE)
B. SLAVE Configuration :
1. ADD these 2 lines in my.cnf :
server-id=3
replicate-do-db=sample_database
2. Restart mysqld or mariadb daemon (service mariadb restart)
3. Connect to mysql using root user & run following commands one by one :
CHANGE MASTER TO MASTER_HOST='IP_ADDRESS_OF_MASTER', MASTER_USER='USER', MASTER_PASSWORD='USER_PASSWORD',
MASTER_LOG_FILE='mysql-bin.005', MASTER_LOG_POS=102;
Where:
IP_ADDRESS_OF_MASTER is the actual IP address of the Master server.
USER is the actual user you created on the Master.
USER_PASSWORD is the actual password you gave the user on the Master.
mysql-bin.005 is the File name from the output of the SHOW MASTER STATUS command from above.
MASTER_LOG_POS is the Position given in the output of the SHOW MASTER STATUS command from above.
4. Start/Stop & check status commands for slave :
SLAVE START; (Leave it running for auto sync)
SHOW SLAVE STATUS; (Gives status of slave daemon)
SLAVE STOP; (To stop slave replication)
(Article referred : https://stackoverflow.com/questions/7707859/mysql-database-sync-between-two-databases)
--------------------------END OF CONFIGURATION--------------------------
TROUBLESHOOTING GUIDELINES :
A. SOME Nice Troubleshooting STEPS :
1. First, make sure you have run CHANGE MASTER TO and configured the server as a replica. If you’ve done this, you should get some output from SHOW SLAVE STATUS. If so, go to the next step.
2. Next, make sure you have set a server ID on both the master and the replica. Try running SHOW VARIABLES LIKE 'server_id' on both servers. If the value is zero or one, check the configuration file for an explicit setting, because zero or one is often the default value when nothing is specified. I have seen this cause the replica to fail, even in cases where the master’s ID is 1, which ought to work okay but sometimes doesn’t.
3. Finally, make sure your master and replica have different server IDs (on small networks, I usually set the server ID to the last octet in the server’s IP address, because it’s handy and easy to remember). MySQL replicas will refuse to replicate from a master with the same ID.
**Also Refer : https://dev.mysql.com/doc/refman/5.7/en/replication-problems.html (For troubleshooting tips)
B. If in case slave_sql_running is NO & slave_io_running is YES do following :
STOP SLAVE;
..................................................Troubleshooting ends.............................................
II. Replicating Multiple Databases (Multiple Master Slave):
(https://forums.mysql.com/read.php?26,171776,205870)
=============================
MASTER: add lines to my.cnf
=============================
binlog-do-db=database_name_1
binlog-do-db=database_name_2
binlog-do-db=database_name_3
=============================
MASTER: SQL SYNTAX
=============================
GRANT REPLICATION SLAVE ON *.* TO 'user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;
SHOW MASTER STATUS;
output> file | Position | Binlog_Do_DB
mysql-bin.000963 1570 database_name_1,database_name_2,database_name_3
=============================
SLAVE: add lines to my.cnf
=============================
replicate-do-db=database_name_1
replicate-do-db=database_name_2
replicate-do-db=database_name_3
=============================
SLAVE: SQL SYNTAX
=============================
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='192.168.0.2', MASTER_USER='user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000963', MASTER_LOG_POS=98;
START SLAVE;
SHOW SLAVE STATUS;
NOTE:
MASTER_LOG_FILE='mysql-bin.000963', MASTER_LOG_POS=98; is displayed when you run the SQL command from the master: cmd mysql#> SHOW MASTER STATUS;
ALSO:
When you run #> SHOW SLAVE STATUS;
make sure you see: Slave_IO_Running | Slave_SQL_Running
Yes Yes
1. Remove or comment out following 2 lines in /etc/my.cnf config file:
skip-networking
bind-address = 127.0.0.1
2. ADD these 3 lines in /etc/my.cnf :
log-bin = mysql-bin
binlog-do-db = sample_database
server-id=2
*Line 1: Instructs mysql what log file to use.
*Line 2: Instructs mysql which database to replicate
*Line 3: Instructs mysql that this machine is the master.
3. Restart mysqld or mariadb daemon (service mariadb restart)
4. Connect to mysql using root user & run following commands one by one :
GRANT REPLICATION SLAVE ON *.* TO 'YOUR_USER'@'SLAVE_IP_ADDRESS' IDENTIFIED BY 'YOUR_PASSWORD';
(New user creation & Grant Replication permissions.)
FLUSH PRIVILEGES;
USE sample_database; (sample_database is the database name we want to replicate)
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
Example :
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.005 | 102 | sample_database | |
+------------------+----------+--------------+------------------+
(Note Down this info that you get after running this command, we'll require it while setting up SLAVE)
B. SLAVE Configuration :
1. ADD these 2 lines in my.cnf :
server-id=3
replicate-do-db=sample_database
2. Restart mysqld or mariadb daemon (service mariadb restart)
3. Connect to mysql using root user & run following commands one by one :
CHANGE MASTER TO MASTER_HOST='IP_ADDRESS_OF_MASTER', MASTER_USER='USER', MASTER_PASSWORD='USER_PASSWORD',
MASTER_LOG_FILE='mysql-bin.005', MASTER_LOG_POS=102;
Where:
IP_ADDRESS_OF_MASTER is the actual IP address of the Master server.
USER is the actual user you created on the Master.
USER_PASSWORD is the actual password you gave the user on the Master.
mysql-bin.005 is the File name from the output of the SHOW MASTER STATUS command from above.
MASTER_LOG_POS is the Position given in the output of the SHOW MASTER STATUS command from above.
4. Start/Stop & check status commands for slave :
SLAVE START; (Leave it running for auto sync)
SHOW SLAVE STATUS; (Gives status of slave daemon)
SLAVE STOP; (To stop slave replication)
(Article referred : https://stackoverflow.com/questions/7707859/mysql-database-sync-between-two-databases)
--------------------------END OF CONFIGURATION--------------------------
TROUBLESHOOTING GUIDELINES :
A. SOME Nice Troubleshooting STEPS :
1. First, make sure you have run CHANGE MASTER TO and configured the server as a replica. If you’ve done this, you should get some output from SHOW SLAVE STATUS. If so, go to the next step.
2. Next, make sure you have set a server ID on both the master and the replica. Try running SHOW VARIABLES LIKE 'server_id' on both servers. If the value is zero or one, check the configuration file for an explicit setting, because zero or one is often the default value when nothing is specified. I have seen this cause the replica to fail, even in cases where the master’s ID is 1, which ought to work okay but sometimes doesn’t.
3. Finally, make sure your master and replica have different server IDs (on small networks, I usually set the server ID to the last octet in the server’s IP address, because it’s handy and easy to remember). MySQL replicas will refuse to replicate from a master with the same ID.
**Also Refer : https://dev.mysql.com/doc/refman/5.7/en/replication-problems.html (For troubleshooting tips)
B. If in case slave_sql_running is NO & slave_io_running is YES do following :
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;..................................................Troubleshooting ends.............................................
II. Replicating Multiple Databases (Multiple Master Slave):
(https://forums.mysql.com/read.php?26,171776,205870)
=============================
MASTER: add lines to my.cnf
=============================
binlog-do-db=database_name_1
binlog-do-db=database_name_2
binlog-do-db=database_name_3
=============================
MASTER: SQL SYNTAX
=============================
GRANT REPLICATION SLAVE ON *.* TO 'user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;
SHOW MASTER STATUS;
output> file | Position | Binlog_Do_DB
mysql-bin.000963 1570 database_name_1,database_name_2,database_name_3
=============================
SLAVE: add lines to my.cnf
=============================
replicate-do-db=database_name_1
replicate-do-db=database_name_2
replicate-do-db=database_name_3
=============================
SLAVE: SQL SYNTAX
=============================
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='192.168.0.2', MASTER_USER='user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000963', MASTER_LOG_POS=98;
START SLAVE;
SHOW SLAVE STATUS;
NOTE:
MASTER_LOG_FILE='mysql-bin.000963', MASTER_LOG_POS=98; is displayed when you run the SQL command from the master: cmd mysql#> SHOW MASTER STATUS;
ALSO:
When you run #> SHOW SLAVE STATUS;
make sure you see: Slave_IO_Running | Slave_SQL_Running
Yes Yes
