mySQL Master-Slave Replication Notes

 

---

This post contains short notes on how we can create mySQL master to slave replication. It is generally required to fulfill multi stage backup topology. Example using the SLAVE server, we can have always uptodate live replica partner which can be used in case of any master server failure.

Very useful for replicate the DB in real time , or to be used in DR sites scenarios as well! I deployed it at few local networks, & results were good when it comes to DR/DB recovery.

 

Pros:
Master-Slave is very fast as in general it doesn’t impose any restrictions on performance.We can split read and write requests to different servers. For example, all analytics queries can be made on Slave nodes.

Cons:
Write requests can hardly be scaled. The only option to scale write requests is to increase compute capacity (RAM and CPU) of the Master node. Failover process is manual in a general case. You should take care of promotion replica node to master one.

Components used in this guide,

• Two VM’s
• OS: Ubuntu 16.04.3 LTS (Xenial Xerus)
• mySQL Version: mysql Ver 14.14 Distrib 5.7.32, for Linux (x86_64) using EditLine wrapper
• Name/IP: mySQL MASTER server: master-254 / 101.11.11.254
• Name/IP: mySQL SLAVE server : slave-255 / 101.11.11.255
• Credentials: mysql root password: zaib1234

Assumptions:

Mysql is installed on both servers.

MASTER Configuration

mysql -uroot -pzaib1234
create database radius;
grant all on radius.* to radius@localhost identified by "zaib1234";
# Create separate ID for replication , it will be used on SLAVE
create user 'zaib'@'%' identified by 'zaib1234';
grant replication slave on *.* to 'zaib'@'%';
exit

Edit mysql configuration to make it MASTER

nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add the below entries in [mysqld] section

log-bin=mysql-bin
#(comment below line if you want all DB's to be replicated, in below e.g we are doing only radius DB replication)
binlog-do-db=radius
server-id=1
log_bin = /var/log/mysql/mysql-bin.log
bind-address = 0.0.0.0 # Search this and change it manually , donot copy paste

Restart mySQL service so changes can take effect

service mysql restart

Now we need to lock table & note down the file/position number which will be later use in SLAVE configuration

mysql -uroot -pzaib1234
FLUSH TABLES WITH READ LOCK;
show master status;
# Note down the File name and Position number, this is important, we will use it in SLAVE server
exit

Export the required DB (This will be imported on SLAVE server later)

mysqldump -u root -pzaib1234 radius --master-data > master.sql

once the export is done, unlock the tables

mysql -uroot -pzaib1234
UNLOCK TABLES;
exit

SLAVE Configuration

using any tool like winscp, copy the master.sql from the master server into this slave server.& import it.

mysql -uroot -pzaib1234 radius < master.sql

Once done, Edit mySQL configuration to make it SLAVE

nano /etc/mysql/mysql.conf.d/mysqld.cnf

log-bin=mysql-bin
#(comment below line if you want all DB's to be replicated, in below e.g we are doing only radius DB replication)
binlog-do-db=radius
# This is slave, it will have server ID number 2, on master server we have id=1
server-id=2
log_bin = /var/log/mysql/mysql-bin.log
bind-address = 0.0.0.0 # Search this and change it manually , donot copy paste

SAVE & EXIT

Restart mySQL service so that changes can take effect

service mysql restart

Now login to mySQL & config the change master parameter

mysql -uroot -pzaib1234
CHANGE MASTER TO MASTER_HOST='101.11.11.254',MASTER_USER='zaib', MASTER_PASSWORD='zaib1234', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
START SLAVE;

[Note: In above code, we used MASTER_LOG_FILE & MASTER_LOG_POS parameters when we issued show master status in master configuration section]

---

TEST

Login to master mySQL & create any tables or entries

root@MASTER-254:~# mysql -uroot -pzaib1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 98
Server version: 5.7.32-0ubuntu0.16.04.1-log (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use radius;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------+
| Tables_in_radius |
+----------------------+
| masterdb_mast_table1 |
+----------------------+
1 row in set (0.00 sec)

mysql>

now login to SLAVE mySQL & look for the tables status

root@SLAVE-255:/tmp# mysql -uroot -pzaib1234
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.7.32-0ubuntu0.16.04.1-log (Ubuntu)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use radius;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------+
| Tables_in_radius |
+----------------------+
| masterdb_mast_table1 |
+----------------------+
1 row in set (0.00 sec)

& we can see that tables got replicated fine.

---

Tips:

If you want to exclude one table from exporting (master-data) (example any large table within DB which is not important)

mysqldump -u root -pSQLPASS  DBNAME  --master-data --ignore-table=DBNAME.TABLE > export_file_name.sql