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 name | ip | Edition | role |
|---|---|---|---|
| mysqlmaster | 192.168.116.20 | centos7 | Master |
| mysqlslave1 | 192.168.116.3 | centos7 | slave |
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 server 127.127.1.0 fudge 127.127.1.0 stratum 8
Parameter description:
server 127.127.1.0 //local clock, synchronizes with local system time. 127.127.1.0 is an IP address here, not a segment. fudge 127.127.1.0 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 192.168.116.20 //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@192.168.116.3 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: 192.168.116.3 is your own slave ip
3. Configure my main configuration file for the main library. CNF
[root@mysqlmaster ~]# Vim/etc/my. CNF //Add something [mysqld] ...... //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;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
|---|---|---|---|---|
| mysql-bin-master.000019 | 588 | HA |
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@192.168.116.3 //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 192.168.116.20 //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='192.168.116.20', master_user='slave',master_password='123', master_log_file='mysql-bin-master.000019',master_log_pos=588;
Notes:
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
Solve:
[root@mysqlmaster ~]# systemctl stop firewalld
2. Primary library ip write error from secondary library when user authorizes
Solve:
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
Solve:
Enter Re-change Configuration from Library mysql> change master to master_host='192.168.116.20', master_user='slave',master_password='123', master_log_file='mysql-bin-master.000019',master_log_pos=588; 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
| Host | IP | System Version | role |
|---|---|---|---|
| mysqlmaster | 192.168.116.20 | CentOS 7 | Master |
| mysqlslave1 | 192.168.116.3 | CentOS 7 | Slave Relay |
| mysqlslave2 | 192.168.116.2 | CentOS 7 | Slave |
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 [mysqld] .........Omit some content server-id=1 binlog-do-db=HA log-bin=/data/mysql/log/mysql-bin-master sync-binlog=1 binlog-format=row
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@192.168.116.2:~ [root@mysqlmaster ~]# scp ha.sql root@192.168.116.3:~
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 server-id=2 log-slave-updates=1 log-bin=/data/mysql/log/mysql-bin-slave1 relay-log=/data/mysql/log/relay-log-bin relay-log-index=/data/mysql/log/slave-relay-bin.index
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='192.168.116.20',master_user='slave', master_password='123',master_log_file='mysql-bin-master.000001', master_log_pos=154; 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 [mysqld] .........Omit some content server-id = 3 relay-log=/data/mysql/log/relay-log-bin relay-log-index=/data/mysql/log/slave-relay-bin.index
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='192.168.116.3',master_user='slave', master_password=123,master_log_file='mysql-bin-slave1.000001', master_log_pos='448'; 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
relay-log=/data/mysql/log/relay-log-bin
relay-log-index=/data/mysql/log/slave-relay-bin.index
3,restart mysql ```bash [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='192.168.116.3',master_user='slave', master_password=123,master_log_file='mysql-bin-slave1.000001', master_log_pos='448'; 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.