Twenty-seven lessons

MySQL master-slave configuration

Introduction of MySQL Master and Subordinate

  • MySQL master-slave is also called Replication and AB replication. Simply speaking, when A and B are masters and slaves, they write data on A, and the other B will write data along with them. The data of both machines are synchronized in real time.
  • MySQL master and slave are based on binlog. The master and slave need to open the binlog in order to master and slave.
    Bilog is the writing operation of recorded MySQL, mainly creating, inserting, modifying, deleting and so on.
    The binlog file is a binary file and cannot be viewed directly by cat.
  • There are roughly three steps in the master-slave process.
    1 The master records the change operation in the binlog
    2. Synchronize the main binlog event (sql statement) to the local machine and record it in the relaylog
    3 execute in sequence from sql statements in relaylog
  • There is a log dump thread on the main to pass binlog to the slave I/O thread
  • There are two threads on the slave, where I/O thread is used to synchronize the main binlog and generate relaylog, and another SQL thread is used to land SQL statements in relaylog.

Dead work

You need to prepare two virtual machines, each with MySQL installed and running properly.
Note: Cloned virtual machines need to delete UUID s from network card configuration files, otherwise there will be conflicts.
The environment of this experiment is two virtual machines of CentOS 7.4 system.

Main server:
    Host name: changed to mysql-master
    MySQL Version: 5.6.35

from server
    Minimize the installed CentOS 7.4 system and update MySQL services installed with binary packages
    Host name: mysql-slave
    MySQL Version: 5.6.35

Configure the master server

Modify the/etc/my.cnf configuration file

# Add the following two lines of configuration to the contents of the / etc/my.cnf file

Start or restart mysqld service

# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS!
//View the files in the datadir directory after restart
# ls /data/mysql/
abcopy.000001  auto.cnf  ib_logfile0  mysql             performance_schema
abcopy.index   ibdata1   ib_logfile1  server-lnmp.err     zrlog

Among them, abcopy.000001 and abcopy.index are the key files of master-slave configuration.
There will be many files like abcopy.000001, 000002, 000003, and so on.

Back up the mysql library and restore it to the testbase library for later testing

// Create a backup file storage directory
# mkdir /mysqlbak

//Backup mysql Library
//Note that if you do not add the mysql/bin directory to PATH 
//Then the mysql and mysqldump commands need to use absolute paths, or define an alias
# mysqldump -uroot -p123456 mysql > /mysqlbak/mysql.sql

//Create a new library testbase
# mysql -uroot -p123456 -e "create database testbase"

// Restore the files from the mysql library you just backed up to the testbase Library
# mysql -uroot -p123456 testbase < /mysqlbak/mysql.sql

Creating Users for Synchronizing Data

// This user only needs two permissions, replication and slave, but for all libraries
# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'repl'@'' identified by '123456'

Lock tables, backup all libraries that need to be synchronized

Lock tables are meant to pause the database until this point. They can only be read, not written.
Then make a backup, and the backup data will be restored from the server.
This is to make the data between the primary server and the slave server the same.

// Lock table
# mysql -uroot -p123456 -e 'flush tables with read lock'

// Backup libraries that need synchronization   
// Note: mysql library is used to store mysql user data and rights. Generally, it does not need synchronization, so it does not need backup.
// So here you just need to back up two libraries: testbase zrlog
# mysqldump -uroot -p123456 -B testbase zrlog > /mysqlbak/base_bak.sql

View the status of the primary server

# mysql -uroot -p123456 -e 'show master status'
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| abcopy.000001 |   661858 |              |                  |                   |

Be careful:
There are two things to remember, which will be used from the server later.
File: abcopy.000001
Position: 661858

Configure the slave server

Modify / etc/my.cnf file

// Add the following line configuration to the contents of the / etc/my.cnf file to OK.

# Note: server-id cannot be the same as the primary server

Restart mysql service after modification

# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

Copy the files backed up by the primary server to the slave server

//Here you can use scp commands, or FTP tools to copy files
# scp ./
root@'s password:      //Enter the password of the source host here.
base_bak.sql                                    100%  681KB  81.7MB/s   00:00

Create the corresponding database and restore the data

//Here you need to create two libraries, testbase and zrlog, and the root password of mysql has not been set from the server yet.
# mysql -uroot -e 'create database testbase'
# mysql -uroot -e 'create database zrlog'
//Restore data
# mysql -uroot < ./base_bak.sql
//Check whether the data has been restored successfully
# mysql -uroot -e 'select count(*) from testbase.user'
| count(*) |
|       10 |
//Data show that the recovery has been successful

Configure slave server parameters

// The first step is to enter the mysql operating interface
# mysql -uroot
//Close slave
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
//Configure slave parameters
mysql> change master to master_host='', master_user='repl', master_password='123456', master_log_file='abcopy.000001', master_log_pos=661858;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

# Parameter interpretation
# change master to fixed command
# master_host = defines the IP of the primary server, or ip:port
# master_user = Defines users on the primary server for synchronization
# master_password = Defines the password of the synchronous user
# master_log_file = Defines the File Viewed by show master status on the primary server
# master_log_pos= Defines the Position Viewed by show master status on the primary server, without double or single quotation marks

//Then open slave
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

// After that, you can unlock the lock table status of the primary server.
// Note that this step operates on the primary server
# mysql -uroot -p123456 -e 'unlock tables'

Check whether master-slave synchronization is normal

// See whether Slave_IO_Running and Slave_SQL_Running are Yes
# mysql -uroot -e 'show slave status\G' | grep Running
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

//Note: If Slave_IO_Running is Connecting 
// You need to check whether the main server firewall releases the port of msyql
// Or check if the slave parameters such as ip, user, password are defined correctly

// Need attention
# Seconds_Behind_Master: 0// Master-Slave Delay Time
# Last_IO_Errno: 0// IO Thread Error Code
# Last_IO_Error: // IO thread error message
# Last_SQL_Errno: 0// SQL Thread Error Code
# Last_SQL_Error: // SQL thread error message

Test master-slave synchronization

// First check whether the libraries of the master and slave servers are the same

// Host Server Library
# mysql -uroot -p123456 -e 'show databases'
| Database           |
| information_schema |
| mysql              |
| performance_schema |
| testbase           |
| zrlog              |

// The Library of the slave server is the same as that of the master server
# mysql -uroot -e 'show databases'
| Database           |
| information_schema |
| mysql              |
| performance_schema |
| testbase           |
| zrlog              |

// Create a library test001 on the primary server
# mysql -uroot -p123456 -e 'create database test001'
# mysql -uroot -p123456 -e 'show databases'
| Database           |
| information_schema |
| mysql              |
| performance_schema |
| test001            |  //This library was just created.
| testbase           |
| zrlog              |

// Check to see if the library is automatically created from the slave server
# mysql -uroot -e 'show databases'
| Database           |
| information_schema |
| mysql              |
| performance_schema |
| test001            |  //Automatically created
| testbase           |
| zrlog              |

// Remove the main server's testbase Library
# mysql -uroot -p123456 -e 'drop database testbase'

// Check to see if there is a testbase library from the slave server
# mysql -uroot -e 'show databases'
| Database           |
| information_schema |
| mysql              |
| performance_schema |
| test001            |
| zrlog              |
// Tesbase is also automatically deleted from the server.

Be careful:
Never do anything about writing from the server.
Writing operations on slave servers can lead to inconsistencies between master and slave data and serious need to re-master and slave data.


Some students, encounter the master never normal synchronization, prompt the same error uuid. This is because of the cloning machine.
 On-line Master-slave Configuration of Non-stop Storage and Non-lock Table
Master asynchronism
 About auto_increment
 mysql-proxy for read-write separation
 mysql-proxy similar products are:
Mycat is based on Ali's open source software cobar,
 mycat realizes sub-database and sub-table
 atlas comes from 360 and does not update without maintenance.
mysql ring master-slave
 mysql Architecture Evolution
 MHA architecture
 Complicated mysql Cluster Architecture

Tags: Operation & Maintenance MySQL Database SQL master-slave

Posted on Sat, 02 Feb 2019 01:27:17 -0500 by phuggett