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
Thanks for sharing
On Wed, 18 Nov 2020, 11:44 AM Syed Jahanzaib Personal Blog to Share Knowledge !, wrote:
> Syed Jahanzaib / Pinochio~:) posted: ” 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 whic” >
LikeLike
Comment by Sahir Khan — November 18, 2020 @ 11:50 AM