in Database, Linux

MySql Master-Slave Replication

There are two functions when setting up a mysql slave for your database.

First for a backup, if your mysql master database goes down you already have a ready to use a a second mysql database environment that has exactly same data with the mysql master without need to restore from dump files where need more time especially if your db size is huge. Just by setting the mysql slave server to the read-write mode. And, you can rebuilt it again.

Second, you can use it as on mysql slave database as read-only database when the primary database server is healthy. So yo have capability for scaling the database read.

Master server

1 Install mysql server

apt-get update

apt-get instal mysql

2. Setup the configuration for the master on /etc/mysql/my.cnf

$ sudo nano /etc/mysql/my.cnf

bind-address =0.0.0.0

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index

3. Create replication user on mysql

mysql > createuser‘replication_user’@’%’ identified by ‘PASSWORD’;

mysql > GRANT REPLICATION SLAVEON *.* TO‘replication_user’@’%’;

mysql > FLUSH PRIVILEGES;

  • You can change the % to your ip range for the mysql server. Ex 192.168.1.%.
  • Use strong and secure passsword

4. Locate bin-log postition on master server. This information will be used to make both master and slave comunicate each others.

 

mysql> show master status; 
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5. Perform mysql dump. The sql dump will be restore on the slave server

 

Slave server

1. Install mysql server

apt-get update

apt-get instal mysql

2. Setup/modify config  in slave server on /etc/mysql/my.cnf

$ sudo nano /etc/mysql/my.cnf

bind-address =0.0.0.0

server-id = 2

log_bin = /var/log/mysql/mysql-bin.log

log_bin_index =/var/log/mysql/mysql-bin.log.index

relay_log = /var/log/mysql/mysql-relay-bin

relay_log_index = /var/log/mysql/mysql-relay-bin.index

3. Restore the sql dump from master server

4. Add the master_log_file and master_log_pos from master server to the slave information.

mysql> stop slave; 
mysql> CHANGE MASTER TO MASTER_HOST = '11.11.11.11', MASTER_USER = 'replication_user', MASTER_PASSWORD = 'PASSWORD', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 753;
mysql > start slave;
mysql > show slave status \G
mysql > Quit;

Now you can try start to add database or table on the master and check if the data is synced to slave server.

Do not forget to set the slave to read-only. To avoid changes data on slave writted and coused coruption on replication.

Thanks