编程知识 cdmana.com

Combined with the actual combat, I summarized several graphs for master-slave replication of MySQL!

In the high concurrency scenario, the author has developed it , Simple to offer 、 Stable 、 Extensible delayed message queuing framework , It has precise timing task and delay queue processing function . Since the open source for more than half a year , It has successfully provided precise timing scheduling scheme for more than ten small and medium-sized enterprises , It has withstood the test of production environment . In order to benefit more children's shoes , Now give the open source framework address :

https://github.com/sunshinelyz/mykit-delay

PS: Welcome to Star Source code , It's fine too pr Your blazing code .

Write it at the front

A lot of friends MySQL The principle of master-slave replication is not very clear , So today , Let's combine MySQL Let's talk about it from the actual combat cases MySQL The underlying principle of master slave replication . If the article helps you a little , Please give me a compliment , Let's watch and forward . You can also leave a message at the end of the text or add me wechat 【sun_shine_lyz】 Communication technology , Learning together , Progress together ! notes : The article has been included in :https://github.com/sunshinelyz/technology-binghe .

install MySQL

On how to install MySQL, You can refer to 《MySQL And —— Source code compilation MySQL8.x+ upgrade gcc+ upgrade cmake( Test the full version of )》. here , I won't go back to .

MySQL Official documents

MySQL Master slave copy official document link address is as follows :

http://dev.mysql.com/doc/refman/8.0/en/replication.html

MySQL Master slave replication

MySQL5.6 There are two ways to start master-slave replication : Log based (binlog)、 be based on GTID( Global transaction identifier ). here , We mainly talk about log based (binlog) Copy . About GTID Master-slave replication of , Let's talk about it in detail later .

MySQL Master slave replication principle

MySQL Master slave replication principle , Also known as A/B principle .

(1) Master Record data changes to a binary log (binary log) in , That is, the configuration file log-bin Specified file , These records are called binary log events (binary log events);

(2) Slave adopt I/O Thread reads Master Medium binary log events And write it to its relay log (relay log);

(3) Slave Redo events in relay log , The event information in the relay log is executed locally one by one , End The data is stored locally , So that changes are reflected in its own data ( Data replay ).

Master slave configuration considerations

(1) The operating system version of the master and slave servers is consistent with the number of digits ;

(2) Master and Slave The database version should be consistent ;

(3) Master and Slave The data in the database should be consistent ;

(4) Master Turn on binary log ,Master and Slave Of server_id It must be unique within the LAN ;

Brief steps of master-slave configuration

1、Master Configuration on

(1) mount this database ;

(2) Modify the database configuration file , To specify server_id, Turn on binary log (log-bin);

(3) Start database , Check which log is currently ,position What's the number ;

(4) Log in to the database , Authorized data replication users (IP The address is slave IP Address , If it's a two-way master-slave , there You also need to authorize the local IP Address , At this time of their own IP The address is from IP Address );

(5) Backup database ( Remember to lock and unlock );

(6) Transfer backup data to Slave On ;

(7) Start database ;

The following steps , Build success for one-way master-slave , The steps needed to build a two-way master-slave system :

(1) Log in to the database , Appoint Master The address of 、 user 、 Password and other information ( This step is only required for two-way master-slave );

(2) Turn on synchronization , Check the status ;

2、Slave Configuration on

(1) mount this database ;

(2) Modify the database configuration file , To specify server_id( If it is to build a two-way master-slave , Also turn on binary journal log-bin);

(3) Start database , Restore backup ;

(4) Check which log is currently ,position What's the number ( One way master-slave does not need , Two way master-slave needs );

(5) Appoint Master The address of 、 user 、 Password and other information ;

(6) Turn on synchronization , Check the status .

One way master-slave environment construction

mount this database

Reference resources 《MySQL And —— Source code compilation MySQL8.x+ upgrade gcc+ upgrade cmake( Test the full version of )》.

To configure Master Of my.cnf

[root@liuyazhuang131 ~]# vi /etc/my.cnf  
#  stay  [mysqld]  Add the following configuration items  
#  Set up  server_id, Generally set as  IP 
server_id=131
#  Copy filter : The database that needs to be backed up , Output  binlog
#binlog-do-db=liuyazhuang
#  Copy filter : Databases that don't need to be backed up , No output (mysql  Libraries are generally out of sync ) 
binlog-ignore-db=mysql 
#  Turn on binary log function , You can take it , Better have meaning  
log-bin=lyz-mysql-bin 
##  For each  session  Allocated memory , Cache used to store binary logs during transactions  
binlog_cache_size=1M 
##  Master slave copy format (mixed,statement,row, The default format is  statement)
binlog_format=mixed
#  The binary log is automatically deleted / Days overdue . The default value is  0, Does not delete automatically . 
expire_logs_days=7
#  Skip all errors encountered in master-slave replication or errors of the specified type , avoid  slave  End copy interrupt .
#  Such as :1062  An error is a duplicate of some primary keys ,1032  The error is because the master-slave database data is inconsistent 
slave_skip_errors=1062

Replication filtering allows you to copy only a portion of the data in the server , There are two types of replication filtering :

(1) stay Master Filtering events in binary logs on ;

(2) stay Slave Filter events in the relay log on the . as follows :

MySQL For binary logs (binlog) Copy type of

(1) Statement based replication : stay Master Performed on SQL sentence , stay Slave Execute the same statement on the .MySQL Silent Use sentence based replication , High efficiency . Once it's found that you can't copy it exactly , Will automatically select row based copy .

(2) Line based replication : Copy the changes to Slave, Instead of putting orders in Slave Last execution . from MySQL5.0 Start supporting .

(3) Hybrid type of replication : Statement based replication is used by default , Once it is found that statement based replication cannot be accurate , You'll use row based replication .

restart Master library

start-up / restart Master Database services , Log in to the database , Create data synchronization users , And grant the corresponding authority

[root@liuyazhuang131 ~]# service mysql restart 
[root@liuyazhuang131 ~]# mysql -uroot -proot
## Create data synchronization users , And grant the corresponding authority  
mysql> grant replication slave, replication client on *.* to 'repl'@'192.168.209.132' identified by '123456'; 
Query OK, 0 rows affected (0.00 sec) ##  Refresh authorization table information  
mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec) 
##  see  position  Number , Write down the  position  Number ( You need this from the plane  position  And the current log file ) 
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| lyz-mysql-bin.000001 |     1312 |              | mysql            |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec) 

Simulation business database

establish lyz library 、 surface , And write a certain amount of data , It is used to simulate the existing business system database

create database if not exists lyz default charset utf8 collate utf8_general_ci;
use lyz; 
DROP TABLE IF EXISTS `lyz_user`; CREATE TABLE `lyz_user` ( 
`Id` int(11) NOT NULL AUTO_INCREMENT, 
`userName` varchar(255) NOT NULL DEFAULT '' COMMENT ' user name ', `pwd` varchar(255) NOT NULL DEFAULT '' COMMENT ' password ',
 PRIMARY KEY (`Id`) 
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT=' User information sheet '; 
INSERT INTO `lyz_user` VALUES (1,'yixiaoqun','123456');

Achieve initial data consistency

To guarantee Master and Slave The data are consistent , We use primary backup , From restore to achieve initial data consistency

##  Lock the watch temporarily first 
mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) 
##  Here we implement full database backup , In practice, , We might just synchronize one library , You can back up only one library 
[root@liuyazhuang131 mysql]# mysqldump -u root -proot lyz > /tmp/lyz.sql 
[root@liuyazhuang131 mysql]# cd /tmp
[root@liuyazhuang131 tmp]# ll | grep lyz.sql
-rw-r--r--  1 root  root     2031 Apr 25 01:18 lyz.sql
#  Be careful : Large amount of data in the actual production environment ( super  2G  data ) Backup of , Not recommended  mysqldump  Conduct   The score , Because it's going to be very slow . It is recommended to use  XtraBackup  Make a backup .
#  Unlock the watch 
mysql> unlock tables; 
Query OK, 0 rows affected (0.00 sec)

take Master Remote transfer of backup data to Slave On , For use Slave Recover data when configuring

[root@liuyazhuang131 tmp]# scp /tmp/lyz.sql root@192.168.209.132:/tmp/lyz.sql
The authenticity of host '192.168.209.132 (192.168.209.132)' can't be established.
RSA key fingerprint is da:70:7b:d5:0c:16:b3:1a:53:b7:3d:9f:20:01:26:3e.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.209.132' (RSA) to the list of known hosts.
root@192.168.209.132's password: 
lyz.sql          

To configure Slave library

Next processing Slave(192.168.209.132), The configuration file needs only one modification , The rest of the configuration is operated with commands

[root@liuyazhuang132 ]# vi /etc/my.cnf
#  stay  [mysqld]  Add the following configuration items  
#  Set up  server_id, Generally set as  IP 
server_id=132
#  Copy filter : The database that needs to be backed up , Output  binlog #binlog-do-db=lyz
#  Copy filter : Databases that don't need to be backed up , No output (mysql  Libraries are generally out of sync )
 binlog-ignore-db=mysql 
#  Turn on binary log , in preparation for  Slave  As something else  Slave  Of  Master  When using  
log-bin=lyz-mysql-slave1-bin 
##  For each  session  Allocated memory , Cache used to store binary logs during transactions  binlog_cache_size = 1M 
#  Master slave copy format (mixed,statement,row, The default format is  statement) 
binlog_format=mixed 
#  The binary log is automatically deleted / Days overdue . The default value is  0, Does not delete automatically . 
expire_logs_days=7 
#  Skip all errors encountered in master-slave replication or errors of the specified type , avoid  slave  End copy interrupt . 
#  Such as :1062  An error is a duplicate of some primary keys ,1032  The error is because the master-slave database data is inconsistent  
slave_skip_errors=1062 
## relay_log  Configure relay logs  
relay_log=lyz-mysql-relay-bin 
## log_slave_updates  Express  slave  Write the copy event to your own binary log  
log_slave_updates=1
## Prevent changing data ( Except for special threads )
read_only=1

If Slave For others Slave Of Master when , You have to set bin_log, ad locum , I turned on the binary log , And explicitly named ( The default name is hostname), But if hostname Change can lead to problems .

relay_log Configure relay logs ,log_slave_updates Express slave The event will be copied Write it into your own binary log . When setting log_slave_updates when , You can let slave Play others slave Of master. here ,slave hold sql Events executed by a thread are written into its own binary log (binary log) then , its slave You can take these events and execute them . As shown in the figure below ( Send replication events to other Slave):

Restore backup data

Restart after saving MySQL service , Restore backup data

[root@liuyazhuang132 ~]# service mysql restart
Shutting down MySQL. SUCCESS! 
Starting MySQL.. SUCCESS! 

Slave Create the same library on

[root@liuyazhuang132 ~]# mysql -uroot -proot
 mysql> use lyz;
 Database changed

Import data

[root@liuyazhuang132 ~]# mysql -uroot -proot lyz < /tmp/lyz.sql 
[root@liuyazhuang132 ~]# mysql -uroot -proot
mysql> use lyz;
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> select * from lyz_user;
+----+-----------+--------+
| Id | userName  | pwd    |
+----+-----------+--------+
|  1 | yixiaoqun | 123456 |
+----+-----------+--------+
1 row in set (0.00 sec)

Slave Library add parameters

Sign in Slave database , Add relevant parameters :Master Of IP、 port 、 Synchronize users 、 password 、position Number 、 Which log file to read

change master to master_host='192.168.209.131',master_user='repl',master_password='123456',master_port=3306,
master_log_file='lyz-mysql-bin.000001',master_log_pos=1312,master_connect_retry=30;

An explanation of the command executed above :

  • master_host='192.168.209.131' ##Master Of IP Address
  • master_user='repl' ## Users for synchronizing data ( stay Master Authorized user in )
  • master_password='123456' ## Sync data user's password
  • master_port=3306 ##master The port of the database service
  • master_log_file='lyz-mysql-bin.000001' ## Appoint Slave From which log file to start reading the copy file ( Can be found in Master Upper use show master status View log file name )
  • master_log_pos=429 ## From which POSITION Start reading on the
  • master_connect_retry=30 # When the master-slave connection is reestablished , If the connection fails , How long before you try again , The unit is in seconds , The default setting is 60 second , Synchronization delay tuning parameters .

Check the master-slave synchronization status

show slave status\G;

You can see Slave_IO_State It's empty ,Slave_IO_Runngin and Slave_SQL_Running yes No, Table time Slave Still didn't start the replication process .

Turn on master-slave synchronization

mysql> start slave;

Check the synchronization status again

#show slave status\G;

Look at the following two parameters , If these two parameters are Yes, It means that data synchronization is normal

Slave_IO_Running:Yes
Slave_SQL_Running:Yes

You can see master and slave Status of threads on , stay master On , You can see slave Of I/O Thread created connection

Master:mysql>show processlist\G;

1.row To deal with slave Of I/O Thread connection .

2.row To deal with MySQL Client connection thread .

3.row To handle threads on the local command line

Slave:mysql>show processlist\G;

1.row To deal with slave Of I/O Thread connection .

2.row To deal with MySQL Client connection thread .

3.row To handle threads on the local command line

Master slave data replication synchronization test

Master:
mysql> insert into lyz_user values(2,'test1','123456');
Slave:
mysql> start slave;

After the above configuration , stay 192.168.209.131 On the database / Add, delete, change and check the table , establish / Delete database / The tables are synchronized to 192.168.209.132 It's on the database .

thus , The whole configuration process ends .

Okay , That's all for today , I'm glacier , See you next time ~~

This article is from WeChat official account. - Glacier Technology (hacker-binghe)

The source and reprint of the original text are detailed in the text , If there is any infringement , Please contact the yunjia_community@tencent.com Delete .

Original publication time : 2020-11-28

Participation of this paper Tencent cloud media sharing plan , You are welcome to join us , share .

版权声明
本文为[glacier]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201224160737128V.html

Scroll to Top