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
1 2 3 4 5 6 7 | mysql -uroot -pzaib1234create database radius;grant all on radius.* to radius@localhost identified by "zaib1234";# Create separate ID for replication , it will be used on SLAVEcreate user 'zaib'@'%' identified by 'zaib1234';grant replication slave on *.* to 'zaib'@'%';exit |
Edit mysql configuration to make it MASTER
1 | nano /etc/mysql/mysql.conf.d/mysqld.cnf |
Add the below entries in [mysqld] section
1 2 3 4 5 6 | 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=radiusserver-id=1log_bin = /var/log/mysql/mysql-bin.logbind-address = 0.0.0.0 # Search this and change it manually , donot copy paste |
Restart mySQL service so changes can take effect
1 | service mysql restart |
Now we need to lock table & note down the file/position number which will be later use in SLAVE configuration
1 2 3 4 5 | mysql -uroot -pzaib1234FLUSH TABLES WITH READ LOCK;show master status;# Note down the File name and Position number, this is important, we will use it in SLAVE serverexit |
Export the required DB (This will be imported on SLAVE server later)
1 | mysqldump -u root -pzaib1234 radius --master-data > master.sql |
once the export is done, unlock the tables
1 2 3 | mysql -uroot -pzaib1234UNLOCK TABLES;exit |
SLAVE Configuration
using any tool like winscp, copy the master.sql from the master server into this slave server.& import it.
1 | mysql -uroot -pzaib1234 radius < master.sql |
Once done, Edit mySQL configuration to make it SLAVE
1 | nano /etc/mysql/mysql.conf.d/mysqld.cnf |
1 2 3 4 5 6 7 | 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=1server-id=2log_bin = /var/log/mysql/mysql-bin.logbind-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
1 | service mysql restart |
Now login to mySQL & config the change master parameter
1 2 3 | mysql -uroot -pzaib1234CHANGE 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | root@MASTER-254:~# mysql -uroot -pzaib1234mysql: [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 98Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | root@SLAVE-255:/tmp# mysql -uroot -pzaib1234mysql: [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 49Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+----------------------+| Tables_in_radius |+----------------------+| masterdb_mast_table1 |+----------------------+1 row in set (0.00 sec) |
