, MySQL Master-Slave Architecture

MySQL Master-Slave Architecture

11.1 mysql replication

In production environments, if data is read and written in the same database. It is completely impossible to meet the actual needs in terms of security, high availability, or high concurrency. Therefore, data synchronization is usually done by master-slave replication to ensure data security, and read-write separation to improve the concurrent load capacity of the database.

1. What is mysql replication(mysql master-slave replication)

1. Master-slave replication refers to the real-time synchronization of changes to one or more slaves when update s, insert s, delete s in the master database result in changes to the data.

2. Replicate asynchronously by default without maintaining a long connection.

3. Configuration allows you to select the libraries and tables you want to synchronize.

4. MySQL is built-in when replicating, and has its own.

2. Principle of mysql replication

Simply put, master writes updates to binary logs, slave synchronizes these binary logs to relay logs, reads the contents of the relay logs, parses the binary logs into SQL statements, and executes them to keep them consistent with the data in the primary database.

3. The role of mysql replication

1. Read-write separation to provide query services

2. Real-time disaster preparedness for failover

3. Horizontally expand the load capacity of the database

4. How mysql replication works

1. master records changes in binary logs (these records are called binary log events)

2. slave copies master's binary log events to its relay log

3. slave operates on the slave database to make it consistent with the master database based on events in the relay log

11.2 Common MySQL replication scenarios

1. One master and one subordinate

2. One Master and Multiple Subordinates

Master is responsible for write operations, Master is responsible for read operations, and other slaves are responsible for read operations. This architecture has the most problematic I/O pressure concentration. Multiple slaves need to synchronize data from the master, affecting the master's IO performance.

3. Cascade Replication

In cascade replication mode, the data synchronization of some slaves does not connect to the primary node, but to the slave node. Because if there are too many slave nodes in the master node, some performance will be lost for replication, so we can have 3-5 slave nodes connect to the master node and other slave nodes connect to the slave node as secondary or tertiary, which not only alleviates the pressure on the master node, but also has no negative impact on data consistency.

For example, using a slave as a relay to share the pressure of Master, the slave relay needs to turn on bin-log and configure log-slave-updates

4. Mutual Preparedness

5. Multiple masters and one subordinate

11.3 Principle of MySQL master-slave replication

From replication in MySQL involves three threads, one running on the primary node (binary dump thread) and the other two (I/O thread, SQL thread) running on the secondary node


1. Primary node binary log dump thread

When a secondary node is connected to a primary node, the primary node generates a binlog dump Threads, used to send from nodes binary-log Content in. Reading from Node binary-log When the content in is read, the thread locks, and when the read is complete, the lock is released before it is sent to the slave node.

2. Slave Node I/O Threads

When the start slave command is executed from the node, an I/O thread is generated from the node to accept content sent from the bin log dump thread of the primary node and store the content in the relay log.

3. Slave Node SQL Thread

The sql thread principally converts binaries in relay-log into sql statements and executes them to maintain the consistency between the slave and primary nodes.

Be careful:

For each master-slave connection, three threads are required to complete. When the primary node has more than one slave node connected, the primary node creates a binary log dump thread for the slave node, and each slave node has its own I/O and SQL threads.

Master-slave replication process

1. Update events (update, insert, delete) occurring on the master database are written to the binlog

2. After starting from the database, I/O threads are generated to initiate connections from the library

3. Main database generates binary log dump thread to send binlog log log from database

4. Receive binlog content from I/O threads in the database and write it to relay log

5. Convert the contents of the relay log into SQL statements and execute them from the SQLL thread in the data air.

Be careful

The master database must have binary log turned on because all master-slave synchronization operations are based on binary files

11.4 mysql master-slave replication mode

1. Asynchronous mode

MySQL master-slave replication defaults to asynchronous mode. In this mode, the transaction commit of the master database is not confirmed by slave, and master does not care if slave receives the binlog sent by master. When slave is accepted, it is written to the relay log and converted to sql execution. If slave does not receive the binlog sent by the master library, the master library is not aware of it.

If the master is down, binlog has not yet arrived to be accepted by slave and slave is elected as the new master library, which will result in inconsistencies in the data.

2. Semi-synchronous mode

Semi-synchronous mode is an improvement on the defect of asynchronous mode. The main library must accept one of the binlog s from the library to generate the relay log and give a response before commit is completed.

[External chain picture transfer failed, source station may have anti-theft chain mechanism, it is recommended to save the picture and upload it directly (img-XxrDOF4k-164398722) (C:UsersAppDataRoamingTyporatypora-user-images1611388.png)]

Compared with asynchronous replication, semi-synchronous replication improves the security of data and also causes a certain degree of latency. Therefore, semi-synchronous replication is best used on networks with low latency. Semi-synchronization mode is not built-in to mysql. Integration from mysql 5.5 requires master and slave installation plug-ins to turn on semi-synchronization mode.

3. Full Synchronization Mode

This means that when the primary library completes a transaction, all slave libraries execute the transaction before returning it to the client. The performance of full synchronous replication is bound to be severely affected because you need to wait for all slave libraries to complete the transaction before returning.

11.5 Deploy mysql master-slave synchronization (one master-one slave)

Experimental environment:

host nameipEditionrole

Be careful!!! Attention!!!: See which virtual machine you are working on

1. Establishing a time server and synchronizing time

1. Install NTP

 [root@mysqlmaster ~]# yum -y install ntp

2. Configure NTP

NTP is generated when NTP is installed. Conf file to modify it

[root@mysqlmaster ~]# Vim/etc/ntp. Conf //Add the following two lines
 fudge stratum 8

Parameter description:

server //local clock, synchronizes with local system time. is an IP address here, not a segment. fudge stratum 8 sets the time zone to Eastern Eighth, when installing the system, select Shanghai Time

3. Restart NTP Service

 [root@mysqlmaster ~]# systemctl restart ntpd //restart ntp service
 [root@mysqlmaster ~]# Netstat-anpt | grep ntpd 	// View ntp services
 [root@mysqlmaster ~]# systemctl enable netpd 	// Set Auto Start Up

4. Close the firewall

 [root@mysqlmaster ~]# systemctl stop firewalld //close firewall
 [root@mysqlmaster ~]# systemctl disable firewalld //Turn off power on autostart

5. Synchronize time from node

 [root@mysqlslave1 ~]# Yum-y install nptdate //install npt
 [root@mysqlslave1 ~]# /usr/bin/ntpdate //The ip here is its own repository ip

2. Configuring the master database server

1. Go into mysql to create test libraries and data

 mysql> create database HA;		//Create a database
 mysql> use HA;				//Enter this database
 mysql> create table T1(id int,name varchar(20));	//Create Table
 mysql> insert into T1 values(1,'Tom1');	//Insert a piece of data

2. Create an authorized user for master-slave replication

 mysql> grant replication slave on *.* to slave@ identified by '123'
//When you copy from a database, you use the slave user whose privilege is to replication slave to all tables under all libraries

Note: is your own slave ip

3. Configure my main configuration file for the main library. CNF

 [root@mysqlmaster ~]# Vim/etc/my. CNF //Add something
 ......   //Omit some content
 log-bin=/data/mysql/log/mysql-bin-master  #Enable binary logging
 server-id=1   #Database Server ID Identification
 binlog-do-db=HA #The database that can be copied from the server, that is, the name of the database whose binary needs to be synchronized

4. Restart mysql

 [root@mysqlmaster ~]# /etc/init.d/mysqld restart

5. View master status

 mysql> show master status;

The information shown here is different for everyone.

6. View binary logs

 [root@mysqlmaster ~]# ls /data/mysql/log/

7. Export Database

 [root@mysqlmaster ~]# Mysqldump-uroot-p123 HA > HA. SQL //everywhere database
 [root@mysqlmaster ~]# scp HA.sql root@ //Send data to slave server

3. Configuring slave databases

1. The versions of the two databases should be identical

mysql> show variables like '%version%';   //View mysql version

2. Test whether the connection to the master server is working

 [root@mysqlslave1 ~]# Mysql-uslave-p123-h //Log on using a user authorized in the primary Library
 mysql> show databases; //View Library
 You only see one here information_schema Because user-authorized permissions are only copying permissions( replication slave)

3. Import the database in accordance with the main library

 [root@mysqlslave1 ~]# Mysql-uroot-p123-e "create database HA;" // Create HA Library
 [root@mysqlslave1 ~]# Mysql-uroot-p123 HA<HA. SQL //Import data into HA

4. Modify my from the library. Conf file

 [root@mysqlslave1 ~]# vim /etc/my.cnf

Add the following configuration

 server-id = 2  
 relay-log=/data/mysql/log/relay-log-bin  //Path name of relay log file
 relay-log-index=/data/mysql/log/slave-relay-bin.index  //Path name of relay log index file

Note: Server-id: The slave server ID number should not be the same as the master ID. If multiple slave servers are set, each slave server must have a unique server-id value, which must be different from the master server and other slave servers. These ID values uniquely identify each server instance in the replication server cluster

5. Restart Services

 [root@mysqlslave1 ~]#  /etc/init.d/mysqld restart

6. Change its related configuration from the library

 [root@mysqlslave1 ~]#  mysql -uroot -p123
 mysql> stop slave; //Stop slave
 mysql> change master to master_host='',


master_host: ip address of the primary Library

master_user: Authorized user name with replication privilege done in the primary Library

master_password: The database password for the primary database

master_log_file: the binary file of the main library

master_log_pos: The actual location of the binaries in the main library

Here master_log_file, master_ Log_ The value of POS is shown by the command above, master status; When viewing in the main library, be sure to match the query results of the main library.

7. Start slave

 mysql> start slave;

4. View the status of each part

1. View slave status in slave

mysql> show slave status;

View the key information:

1) Master_Host: Is the ip address of the primary library correct

2) Master_User: Is the user authorized in the primary library used

3) Master_Log_File: Is this consistent with the information shown in the show master status in the main library

4) Slave_IO_Running: Is the I/O thread from the library open

5) Slave_SQL_Running: Whether the SQL thread from the library is open or not

2. See if a connection has been created in the main library

mysql> show processlist \G;

View the key information:

1) User: Is the user authorized to aplication slave

2) Host: Is it an ip address from the library

3) Command: Is Binlog Dump on

5. Error Analysis

Errors typically occur in the following situations:

1. Firewall is not closed


 [root@mysqlmaster ~]# systemctl stop firewalld

2. Primary library ip write error from secondary library when user authorizes


Enter Main Library mysql View
mysql> select user,host from mysql.user;
View authorized user pairs ip Is it a slave Library

3. Error in changing configuration from library


Enter Re-change Configuration from Library
mysql> change master to master_host='',
master_log_file master_log_pos These two values go into the main library for viewing and are consistent with the main library
mysql> show master status;

Be careful!!! Master_here Log_ File, master_ Log_ The value of Po must correspond to the primary Library

11.6 Deploy Level-On-Line Replication (M-S-S)

Experimental environment

HostIPSystem Versionrole
mysqlmaster192.168.116.20CentOS 7Master
mysqlslave1192.168.116.3CentOS 7Slave Relay
mysqlslave2192.168.116.2CentOS 7Slave

1. Synchronization Time

Do the same on all three virtual machines

2. Deployment of Master

1. Authorize users on the primary server

[root@mysqlmaster ~]# Mysql-uroot-p123 	// Enter database
mysql> grant replication slave on *.* to slave@'192.168.116.%' identified by '123' //Authorized User
mysql> flush privileges; //Refresh Authorized Users

2. Create a database

 mysql> create database HA;
 mysql> use HA;
 mysql> create table T1(id int,name varchar(20));
 mysql> insert into T1 values(1,'tom1');

3. Modify the main library configuration file

[root@mysqlmaster ~]# vim /etc/my.cnf
 Add the following
.........Omit some content

Parameter Description

server-id: Specify the primary library instance, the ID cannot be the same on each Mysql server

binlog-do-db: Specify the library to replicate

log-bin: file location for binaries

sync-binlog: Synchronize to disk every number of write buffers

sync_binlog=1: Represents synchronous write buffers and disk binary log files, does not use file system caching, when using the innodb transaction engine, replicates

In a system environment, to ensure maximum availability, they are set to "1", but this will affect the performance of io.

binlog-format: formatting binary file logs

4. Restart service, view status

[root@mysqlmaster ~]# /etc/init.d/mysqld restart
//After entering the database
mysql> show master status;//Displays the current binlog file and event location for the primary server

5. Export a full backup of the master server HA library to the relay and slave servers

 [root@mysqlmaster ~]# mysqldump -uroot -p123 -B HA>ha.sql
 [root@mysqlmaster ~]# scp ha.sql root@
 [root@mysqlmaster ~]# scp ha.sql root@

3. Deploying Slave Relay

1. Import Database

 [root@mysqlslave1 ~]# mysql -uroot -p123 <ha.sql

2. Configure configuration files for slave Libraries

 [root@mysqlslave1 ~]# vim /etc/my.cnf
 Add the following
  ............   //Omit some content

Parameter Description

server-id: Specify the primary library instance, the ID cannot be the same on each Mysql server

log-slave-updates: In the copying process described above, the master library binaries are read from the library through the I/O thread and written to its own relay log, where Slave not only needs to receive, it also needs to pass the binaries down. So when this parameter is added, the relay slave library writes the received binaries to the slave library as well as to its own binaries. This parameter is necessary in cascading replication.

log-bin: Specify binary file location

relay-log: specify relay file location

relay-log-index: Specify the relay file index location

3. Restart mysql

 [root@mysqlslave1 ~]# /etc/init.d/mysqld restart

4. Authorize slave Libraries

 [root@mysqlslave1 ~]# mysql -uroot -p123
 mysql> stop slave;
 msyql> change master to master_host='',master_user='slave',
 mysql> start slave;

The values of these parameters have been described in the previous experiment, and are not explained much here.

5. View the status of relay services

 mysql> show slave status \G 
 //Check the state of the two threads, parameter I/O and SQL, using the above method

6. Authorized users (for use with mysqlslave2)

mysql> grant replication slave on *.* to 'slave'@'192.168.116.%' identified by '123'
mysql> flush privileges;
mysql> show master status; //View the current binlog file, the event location, of the slave relay server

IV. Deploying slave s

1. Import Database

 [root@mysqlslave2 ~]# mysql -uroot -p123 <ha.sql

2. Modify Profile

 [root@mysqlslave2 ~]# vim /etc/my.cnf
 //Add the following
 .........Omit some content
 server-id = 3

3. Restart mysql

 [root@mysqlslave2 ~]# /etc/init.d/mysqld restart

4. Authorization

 [root@mysqlslave2 ~]# Mysql-uroot-p123 //Enter database
 mysql> stop slave;
 mysql> change master to master_host='',master_user='slave',
 mysql> start slave;

5. View Status

 mysql> show slave status\G

The deployment of cascade replication is completed when the state is judged to be normal by the master-slave experiments mentioned above.

...... Omit some content
server-id = 3

3,restart mysql

 [root@mysqlslave2 ~]# /etc/init.d/mysqld restart

4. Authorization

 [root@mysqlslave2 ~]# Mysql-uroot-p123 //Enter database
 mysql> stop slave;
 mysql> change master to master_host='',master_user='slave',
 mysql> start slave;

5. View Status

 mysql> show slave status\G

The deployment of cascade replication is completed when the state is judged to be normal by the master-slave experiments mentioned above.

Keywords: Database MySQL architecture

Added by Keaner on Fri, 04 Feb 2022 19:40:22 +0200