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

[html]
vim /etc/mysql/my.cnf
[/html]

The following needs updated – comment out bind-address, uncomment server-id, uncomment log_bin, uncomment binglog_do_db:

[html]
#bind-address = 127.0.0.1
server-id =1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = newdatabase
[/html]

Save and quit.

*NOTE – newdatabase = the database which is going to be replicated.

Restart MySQL:

[html]
/etc/init.d/mysql restart
[/html]

Next we access MySQL:

[html]
mysql -u root -p
[/html]

The slave server needs privileges to access the master server. Set username and password as follows:

[html]
GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘password’;
[/html]

Now are apply/flush privileges:

[html]
FLUSH PRIVILEGES;
[/html]

The next stage is to find the log file position of the master database. This is done by:

[html]
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)
[/html]

Now we take a copy of the database on the Master server and transfer to the slave:

[html]mysqldump -u root -p newdatabase > newdatabase.sql[/html]

Lastly we need to unlock the tables and quit:

[html]
unlock tables;
quit;
[/html]

 

Slave Server

Setup the new slave database (same name as master for replication):
[html]
create database newdatabase;
quit;
[/html]

Import the database which was previously exported from the master:

[html]
mysql -u root -p newdatabase < /path/to/newdatabase.sql
[/html]

Now the MySQL configuration file needs updated similar to the master setup:

[html]
vim /etc/mysql/my.cnf
[/html]

The following needs updated – comment out bind-address, uncomment server-id, uncomment log_bin, uncomment binglog_do_db, add relay-log:

[html]
#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
[/html]

Save and quit. Restart MySQL:

[html]
/etc/init.d/mysql restart
[/html]

Next is to enable replication within the MySQL shell:

[html]
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;
[/html]

Now start slave:
[html]
Start Slave;
[/html]

To check the slave is running run the following:
[html]
SHOW SLAVE STATUSG
[/html]

This should output something similar to:

[html]

*************************** 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
[/html]

Below are the 2 most important parts of the slave status:

[html]
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[/html]

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://linuxtutorial.co.uk/master-slave-replication-bash-script-monitor/

Written by Matt Cooper
Hi, I'm Matt Cooper. I started this blog to pretty much act as a brain dump area for things I learn from day to day. You can contact me at: matt@matthewc424.sg-host.com.