MySQL master-slave replication and semi-synchronous replication

MySQL master-slave replication and semi-synchronous replication principle

1, master-slave replication is mainly based on the binary log (binlog), the binary logging is a complete event.

2. Take the binary log of the primary server and run it again from the server.

3. Copy process:

1，slave端的IO线程连上master端，请求   2，master端返回给slave端，bin log文件名和位置信息      3，IO线程把master端的bin log内容依次写到slave端relay bin log里，并把master端的bin-log文件名和位置记录到master.info里。    4，salve端的sql线程，检测到relay bin log中内容更新，就会解析relay log里更新的内容，并执行这些操作；也就是说salve执行和master一样的操作而达到数据同步的目的；

The replication process involves 3 threads:

1、从库开启一个IO线程，负责链接主库请求和接收binlog日志并写入到relay-log2、从库开启一个sql线程，负责解析relay-log中的事件并执行3、主库开启一个dump线程，负责响应从库来的IO线程的请求。

Example of configuration of master-slave construction

Build configuration with MySQL multi-instance

环境说明[[email protected] ~]# getenforce Disabled[[email protected] ~]# /etc/init.d/iptables status iptables：未运行防火墙。[[email protected] ~]# mysql --versionmysql  Ver 14.14 Distrib 5.6.25, for Linux (x86_64) using  EditLine wrapper

Configuration files for the main library

[[email protected] ~]# vim /data/3306/my.cnf [mysqld]user=mysqlport=3306socket=/data/3306/mysql.sockbasedir=/usr/local/mysqldatadir=/data/3306/datalog-bin=/data/3306/mysql-bin    --指定binlog的存放路径server-id=1                 --主库和从库的server-id 不能一样skip_name_resolve=0             --表示跳过域名解析[client]socket=/data/3306/mysql.sock[mysqld_safe]log-error=/data/3306/mysql_3306.errpid-file=/data/3306/mysql.pid

Configuration files from the library

[[email protected] ~]# vim /data/3307/my.cnf [mysqld]user    = mysqlport    = 3307socket  = /data/3307/mysql.sockbasedir = /usr/local/mysqldatadir = /data/3307/datalog-bin = /data/3307/mysql-bin      --从库的binlog可以不开启server-id = 2           --server-id和主库的不一样skip_name_resolve = 0read_only = 1           --从库只读[client]port            = 3307socket          = /data/3307/mysql.sock[mysqld_safe]log-error=/data/3307/mysql_3307.errpid-file=/data/3307/mysqld.pid

Start the database

[[email protected] data]# /data/3306/mysql start2018-05-27 06:03:37 MySQL--启动中... [[email protected] data]# /data/3307/mysql start2018-05-27 06:03:47 MySQL--启动中... [[email protected] data]# netstat -nltup|grep mysqltcp        0      0 :::3306          :::*          LISTEN      4028/mysqld         tcp        0      0 :::3307          :::*          LISTEN      4268/mysqld         

Create a user for replication on the main library

[[email protected] data]# mysql -uroot -p -S /data/3306/mysql.sock  --登陆主库Enter password: mysql> grant super,replication slave on *.* to ‘hb‘@‘10.0.0.%‘ identified by ‘123‘;         --创建用户并授权Query OK, 0 rows affected (0.12 sec)mysql> flush privileges;    --刷新权限Query OK, 0 rows affected (0.05 sec)mysql> show master status;      --查看master的当前时间写到的二进制文件名和位置+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000011 |      409 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)--只有打开二进制日志，这句命令才有结果，表示当前数据库的二进制日志写到什么位置

(Note: If the main library is already open and has important data, the data must be backed up to be recovered from the library.) I'm just ignoring this step in the test environment. ）

Operations copied from the library

[[email protected] ~]# mysql -S /data/3307/mysql.sock       --登陆从库mysql> change master to    -> master_host=‘10.0.0.15‘,     --主的IP    -> master_port=3306,            --主的端口    -> master_user=‘hb‘,            --对应前面主授权时的用户    -> master_password=‘123‘,       --对应前面主授权时的密    -> master_log_file=‘mysql-bin.000011‘,      ----主上面查到的文件名    -> master_log_pos=409;                  --主上面查到的位置号Query OK, 0 rows affected, 2 warnings (0.11 sec)mysql> start slave;         --启动从库复制Query OK, 0 rows affected (0.07 sec)mysql> show slave status\G*************************** 1. row ***************************        ……             Slave_IO_Running: Yes            Slave_SQL_Running: Yes      --看到这两人个线程为yes，代表搭建成功        ……

Perform a test master-slave synchronization

在主库上创建库或表，再到从库上查看是否同步成功。 此步骤忽略。

MySQL a master more from the building

A master more from the above is a master one from the basis of adding a Slave server

(I add a centos7.3 to do from the demo)

[[email protected] ~]# vim/etc/my.cnf--Edit configuration file [mysqld]port=3306 datadir=/data/mysql Pid-file=/data/mysql/mysql . PID Socket=/data/mysql/mysql.socket Log-error=/data/mysql/mysql-err.log user=mysql server-id=3--Add a Server-id number with It's not the same as before. [client]socket=/data/mysql/mysql.socket[[email protected] ~]# systemctl restart Mysqld.service--reboot [    [email protected] ~]# lsof-i:3306 Login database operation is the same as the above from the server operation mysql> Change master to, master_host= ' 10.0.0.15 ', -master_port=3306, master_user= ' HB ',---master_password= ' 123 ', master_log_file= ' mysql-bin.0 00011 ', master_log_pos=409; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows Affected (0.00 sec) mysql> Show slave status\g*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos t:10.0.0.15 MASTER_USER:HB Master_port:3306 connect_retry:60 master_log_file:mysql-bin.000011 Read_master_log_pos: 503 relay_log_file:mysql-relay-bin.000002 relay_log_pos:377 Relay_master_log_file: mysql-bin.000011 Slave_io_running:yes Slave_sql_running:yes--see 2 to Yes that's a success

Semi-synchronous construction

The so-called semi-synchronous replication is the master each commit a transaction, to slave application of this thing back to the master signal. So that Master can commit things successfully. This ensures that the Master-slave data is absolutely consistent (but at the expense of Master's performance). But the waiting time can also be adjusted. MySQL semi-synchronous replication wait time expires (the default time is 10 seconds), it is automatically converted to asynchronous replication, compared to asynchronous replication, semi-synchronous replication improves the security of the data.

The version after mysql5.5 is installed because the semi-synchronous replication is implemented after this version

First step: First to build MySQL master-slave asynchronous replication, the above has been built on the process is omitted.

Second step: Turn to semi-synchronous replication on an asynchronous basis

1) Install this plugin on master mysql> install plugin rpl_semi_sync_master soname ' semisync_master.so '; Query OK, 0 rows affected (1.51 sec) (--Delete plugin method mysql > Uninstall plugin rpl_semi_sync_master;) mysql> show Global Var       Iables like ' rpl_semi_sync% '; --After installation OK, the Lord will be more than a few parameters +------------------------------------+-------+| variable_name | Value |+------------------------------------+-------+| rpl_semi_sync_master_enabled |      OFF | --off indicates that the half-sync is not turned on | Rpl_semi_sync_master_timeout |      10000 | --The default main wait time from return information, 10 seconds. | Rpl_semi_sync_master_trace_level |       32 | --Monitoring | Rpl_semi_sync_master_wait_no_slave |      On | --whether to allow the submission of each thing to wait for the slave signal. On for every thing waiting +------------------------------------+-------+4 rows in Set (0.83 sec) 2) Install the plugin on the slave mysql> install plugin rpl_semi_sync_slave soname ' semisync_slave.so '; Query OK, 0 rows affected (0.63 sec) mysql> show global variables like ' rpl_semi_sync% '; +------------------------------ ---+-------+|      Variable_name             | Value |+---------------------------------+-------+| rpl_semi_sync_slave_enabled | OFF | | Rpl_semi_sync_slave_trace_level | |+---------------------------------+-------+2 rows in Set (0.78 sec) 3) on master activates semi-synchronous replication mysql> set global rpl_semi_s Ync_master_enabled =on;   Query OK, 0 rows affected (0.04 sec) 4) Slave Activate the semi-synchronous replication and restart the IO thread mysql> set global rpl_semi_sync_slave_enabled=on;            --Activate the semi-synchronous query OK, 0 rows affected (0.04 sec) mysql> stop slave io_thread;           --Close IO thread query OK, 0 rows affected (0.14 sec) mysql> start slave io_thread; --Start IO thread query OK, 0 rows affected (0.11 sec) 5) in master view status mysql> show global status like ' rpl_semi_sync% '; +------------ --------------------------------+-------+| variable_name | Value |+--------------------------------------------+-------+| rpl_semi_sync_master_clients |  1 | --there is a semi-synchronous replication enabled from the server | Rpl_semi_sync_master_net_avg_wait_time |  0 | Average wait time for--master to wait for slave reply. Unitmilliseconds | Rpl_semi_sync_master_net_wait_time |  0 | --master the total waiting time. Unit milliseconds | Rpl_semi_sync_master_net_waits |  0 | --master waiting for slave reply total number of Waits | Rpl_semi_sync_master_no_times |  0 | --master How many times to turn off semi-synchronous replication | Rpl_semi_sync_master_no_tx |  0 | Number of--master wait Timeout | Rpl_semi_sync_master_status |  On | --Mark if Master is now a semi-synchronous replication state | Rpl_semi_sync_master_timefunc_failures |  0 | Number of times the--master call time (such as Gettimeofday ()) failed | Rpl_semi_sync_master_tx_avg_wait_time |  0 | --master average wait time on each transaction | Rpl_semi_sync_master_tx_wait_time |  0 | The total waiting time for--master to spend on things | Rpl_semi_sync_master_tx_waits |  0 | --master number of things to wait | Rpl_semi_sync_master_wait_pos_backtraverse |  0 | --The first to come, and the number of times to arrive first | rpl_semi_sync_master_wait_sessions |  0 | -How many sessions are currently waiting because of slave reply | Rpl_semi_sync_master_yes_tx |  0 | --Mark whether slave is in the semi-synchronous state +--------------------------------------------+-------+14 rows in Set (0.10 sec) 6) View status on slave only the following message mysql> show global status like ' rpl_semi_sync% '; +----------------------------+---- ---+| variable_name | Value |+----------------------------+-------+| Rpl_semi_sync_slave_status | On |+----------------------------+-------+1 row in Set (0.00 sec)

Third Step test:

mysql> create database  btbcs;Query OK, 1 row affected (0.10 sec)mysql> show global status like ‘rpl_semi_sync%_yes_tx‘; +-----------------------------+-------+| Variable_name               | Value |+-----------------------------+-------+| Rpl_semi_sync_master_yes_tx | 1     |     --表示这次事物成功从slave返回一次确认信号+-----------------------------+-------+1 row in set (0.04 sec)模拟错误，把slave上的IO线程停掉--再回到master上测试mysql> create database  btbcs2;Query OK, 1 row affected (10.34 sec)    --这次插入一个值需要等待10秒（默认的等待时间)mysql> create database  btbcs3;Query OK, 1 row affected (0.00 sec)     --再插入数据时候就发现自动转成了原来的异步模式了再把slave上的IO线程开启，查看数据发现刚才slave关闭期间的那几条数据还是会自动复制过来，数据又回到一致如果是把slave上的mysql停掉，再次把slave启动，看到半同步复制没启来，是异步模式，需要重新把同步模式再启起来就可以了mysql> set global rpl_semi_sync_slave_enabled=on;   --启动半同步模式mysql> stop slave IO_THREAD;        --关闭IO线程mysql> start slave IO_THREAD;       --启动IO线程slave启起来后数据也是一样会回到一致

MySQL master-slave replication and semi-synchronous replication