This tutorial will setup Master-Slave replication presuming the following:
Master – 10.10.10.1
Slave – 10.10.10.2
Master Server
Edit the MySQL configuration file
vim /etc/mysql/my.cnf
The following needs updated – comment out bind-address, uncomment server-id, uncomment log_bin, uncomment binglog_do_db:
#bind-address = 127.0.0.1
server-id =1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = newdatabase
Save and quit.
*NOTE – newdatabase = the database which is going to be replicated.
Restart MySQL:
/etc/init.d/mysql restart
Next we access MySQL:
mysql -u root -p
The slave server needs privileges to access the master server. Set username and password as follows:
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
Now are apply/flush privileges:
FLUSH PRIVILEGES;
The next stage is to find the log file position of the master database. This is done by:
USE newdatabase;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 150 | newdatabase | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Now we take a copy of the database on the Master server and transfer to the slave:
mysqldump -u root -p newdatabase > newdatabase.sql
Lastly we need to unlock the tables and quit:
unlock tables;
quit;
Slave Server
Setup the new slave database (same name as master for replication):
create database newdatabase;
quit;
Import the database which was previously exported from the master:
mysql -u root -p newdatabase < /path/to/newdatabase.sql
Now the MySQL configuration file needs updated similar to the master setup:
vim /etc/mysql/my.cnf
The following needs updated – comment out bind-address, uncomment server-id, uncomment log_bin, uncomment binglog_do_db, add relay-log:
#bind-address = 127.0.0.1
server-id =2
log_bin = /var/log/mysql/mysql-bin.log
relay-log= /var/log/mysql/mysql-relay-bin.log
binlog_do_db = newdatabase
Save and quit. Restart MySQL:
/etc/init.d/mysql restart
Next is to enable replication within the MySQL shell:
mysql -u root -p
CHANGE MASTER TO MASTER_HOST='10.10.10.2',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 150;
Now start slave:
Start Slave;
To check the slave is running run the following:
SHOW SLAVE STATUSG
This should output something similar to:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.10.1
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 525399
Relay_Log_File: mysqld-relay-bin.000005
Relay_Log_Pos: 464001
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 525399
Relay_Log_Space: 525805
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Below are the 2 most important parts of the slave status:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
If both are yes then you’re replication is up and running.
To test the replication, simply add new records to the master database and verify the same the records appear on the slave.
Monitoring script – https://www.linuxtutorial.co.uk/master-slave-replication-bash-script-monitor/
Recent Comments