澳门在线威尼斯官方 > 电脑数据库 > 半联袂复制,7观念复制到GTID在线切换

原标题:半联袂复制,7观念复制到GTID在线切换

浏览次数:85 时间:2019-09-25

Hostname IP/Port Identity OS Version MySQL Version GTID Mode Binlog Format
zlm2 192.168.1.101/3306 master CentOS 7.0 5.7.21 off row
zlm3 192.168.1.102/3306 slave CentOS 7.0 5.7.21 off row

    In order to improve the data consistency(since after_commit has avoidless deficiency),MySQL official enhances the semi-synchronous replication which can be called "loss-less semi-synchronous replication" in MySQL 5.7 by add after_sync mode in parameter "rpl_semi_sync_master_wait_point".

 

    

 

 

 

 

Preface

4. Master will return results to the client immediately after dump thread has sent these binary logs to slave.

 

  • Commonly,semi-sync replication is strongly recommended when implements MySQL replication nowadays(with gtid).
  • I utterly recommend to upgrade product system to MySQL 5.7 in order to use "after_sync" mode which can avoid data loss.
  • Be careful of specify an inappropriate value in parameter "rpl_semi_sync_master_timeout" which will cause converting semi-sync to async replication.

**Make sure that the classic replication is working normally on slave.**

威尼斯澳门在线 1                               

Framework

  • Slave has received the binary log,and then turns it into relay log and applys it.There's no transaction loss.
  • Slave hasn't received the binary log,the transaction committed by master just now will lose,but the client won't fail(only inconsistent in replication).

    Classic replication is commonly used in previous version of MySQL.It's really tough in managing them when our replications get into failures.Many new features are also depend on GTID.So it's urgent to use GTID replication as soon as possible.I'm gonna to demenstrate how to change classic replication to GTID replication online with two servers.Here we go.

 

Check  parameter "gtid_mode" and is "OFF" on both master and slave in the replication group.

  • semi-synchronous replication
  • GTID replication is the best practice in MySQL replicaiton now,especially in 5.7 version above.More and more new good features are relies on GTID,such as "Group Replication","Group Commit","Parallel Replication",etc.
  • We'd better replace all the classic replication to GTID replication in our product environment in order to get more benifits and work efficiently.
  • Chang classic replicaiton to GTID replicaiton online should follow the order of "off -> off_permissive -> on_permissive -> on" and execute them on both master and slaves.
  • Notice that change online is only support on MySQL 5.7.6 and above.

 

*威尼斯澳门在线 ,*Change  the parameter "gtid_mode" to "on_permissive"  on both master and slave.**

 

 

    In the picture above,the t1 transaction shouldn't be lost because of the master merely commits to the storage engine after receive the ACK signal from slave.In spite of master may crash before receiving ACK signal,no transaction will lose as the master hasn't commit at all.Meanwhile,the t2 transaction also get consistent query here.

 1 (root@localhost mysql3306.sock)[(none)]>show slave statusG
 2 *************************** 1. row ***************************
 3                Slave_IO_State: Waiting for master to send event
 4                   Master_Host: 192.168.1.101
 5                   Master_User: repl
 6                   Master_Port: 3306
 7                 Connect_Retry: 60
 8               Master_Log_File: mysql-bin.000006
 9           Read_Master_Log_Pos: 191183208
10                Relay_Log_File: relay-bin.000023
11                 Relay_Log_Pos: 41556833
12         Relay_Master_Log_File: mysql-bin.000006
13              Slave_IO_Running: Yes
14             Slave_SQL_Running: Yes
15               Replicate_Do_DB: 
16           Replicate_Ignore_DB: 
17            Replicate_Do_Table: 
18        Replicate_Ignore_Table: 
19       Replicate_Wild_Do_Table: 
20   Replicate_Wild_Ignore_Table: 
21                    Last_Errno: 0
22                    Last_Error: 
23                  Skip_Counter: 0
24           Exec_Master_Log_Pos: 175774368
25               Relay_Log_Space: 191183725
26               Until_Condition: None
27                Until_Log_File: 
28                 Until_Log_Pos: 0
29            Master_SSL_Allowed: No
30            Master_SSL_CA_File: 
31            Master_SSL_CA_Path: 
32               Master_SSL_Cert: 
33             Master_SSL_Cipher: 
34                Master_SSL_Key: 
35         Seconds_Behind_Master: 20
36 Master_SSL_Verify_Server_Cert: No
37                 Last_IO_Errno: 0
38                 Last_IO_Error: 
39                Last_SQL_Errno: 0
40                Last_SQL_Error: 
41   Replicate_Ignore_Server_Ids: 
42              Master_Server_Id: 1013306
43                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
44              Master_Info_File: mysql.slave_master_info
45                     SQL_Delay: 0
46           SQL_Remaining_Delay: NULL
47       Slave_SQL_Running_State: System lock
48            Master_Retry_Count: 86400
49                   Master_Bind: 
50       Last_IO_Error_Timestamp: 
51      Last_SQL_Error_Timestamp: 
52                Master_SSL_Crl: 
53            Master_SSL_Crlpath: 
54            Retrieved_Gtid_Set: 
55             Executed_Gtid_Set: 
56                 Auto_Position: 0 //This means we are using the classic replication now.
57          Replicate_Rewrite_DB: 
58                  Channel_Name: 
59            Master_TLS_Version: 
60 1 row in set (0.00 sec)

5. Slave receives the binary logs by IO_Thread and apply the relay logs by SQL_Thread.

Some error masseages may occur if you don't implement follow the sequence above.

 

**Change the parameter "enforce_gitd_consistency" to "warn" on both master and slave.**

Method of different MySQL Replication

**Make sure all the binlogs generated by classic replication has been disappeared on both master and slave by checking parameter '**ongoing_anonymous_transaction_count' whether it returns "0".**

    Generally speaking,the data changed on master will be continuously sent to slave.So the data on slave seems to be equal with the master.This mechanism is usually used to backup on slave(reduce the pressure of master),construct HA architecture(failover or separate reading/writing operations),etc.

 

    

 

    Further more,to configure semi-sychronous replication,you should implement the optional plugin component "rpl_semi_sync_master",which can be check by using command "show plugins;"

 

What will slave do?

Procedure

 

 1 [root@zlm2 07:22:53 ~/sysbench-1.0/src/lua]
 2 #sysbench oltp_insert.lua --mysql-host=192.168.1.101 --mysql-port=3306 --mysql-user=zlm --mysql-password=zlmzlm --mysql-db=sysbench --tables=10 --table-size=100000 --mysql-storage-engine=innodb prepare
 3 sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
 4 
 5 Creating table 'sbtest1'...
 6 Inserting 100000 records into 'sbtest1'
 7 Creating a secondary index on 'sbtest1'...
 8 Creating table 'sbtest2'...
 9 Inserting 100000 records into 'sbtest2'
10 Creating a secondary index on 'sbtest2'...
11 ...
12 
13 [root@zlm2 07:26:30 ~/sysbench-1.0/src/lua]
14 #sysbench oltp_insert.lua --mysql-host=192.168.1.101 --mysql-port=3306 --mysql-user=zlm --mysql-password=zlmzlm --mysql-db=sysbench --threads=3 --time=7200 --report-interval=60 --rand-type=uniform run
15 sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
16 
17 Running the test with following options:
18 Number of threads: 3
19 Report intermediate results every 60 second(s)
20 Initializing random number generator from current time
21 
22 
23 Initializing worker threads...
24 
25 Threads started!
26 
27 [ 60s ] thds: 3 tps: 1623.71 qps: 1623.71 (r/w/o: 0.00/1623.71/0.00) lat (ms,95%): 2.97 err/s: 0.00 reconn/s: 0.00
28 [ 120s ] thds: 3 tps: 1844.96 qps: 1844.96 (r/w/o: 0.00/1844.96/0.00) lat (ms,95%): 2.61 err/s: 0.00 reconn/s: 0.00
29 [ 180s ] thds: 3 tps: 1894.37 qps: 1894.37 (r/w/o: 0.00/1894.37/0.00) lat (ms,95%): 2.61 err/s: 0.00 reconn/s: 0.00
30 ...
31 
32 //Check the output of processlist.
33 (root@localhost mysql3306.sock)[(none)]>show processlist;
34 +----+------+------------+----------+-------------+------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
35 | Id | User | Host       | db       | Command     | Time | State                                                         | Info                                                                                                 |
36 +----+------+------------+----------+-------------+------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
37 | 41 | root | localhost  | NULL     | Query       |    0 | starting                                                      | show processlist                                                                                     |
38 | 43 | repl | zlm3:44252 | NULL     | Binlog Dump |  379 | Master has sent all binlog to slave; waiting for more updates | NULL                                                                                                 |
39 | 44 | zlm  | zlm2:56708 | sysbench | Query       |    0 | update                                                        | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 8106, '57837919367-24452778030-14591605115-8049012633 |
40 | 45 | zlm  | zlm2:56709 | sysbench | Query       |    0 | update                                                        | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5602, '45087463438-93604980565-67881991526-9944080034 |
41 | 46 | zlm  | zlm2:56710 | sysbench | Query       |    0 | update                                                        | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 3497, '01822437471-94427682076-39418270545-9867829936 |
42 +----+------+------------+----------+-------------+------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
43 5 rows in set (0.00 sec)

    In step 4,master won't judge whether slave has received the binary logs (which are sent by itself) or not.If the master crashs suddenly after it has sent the binary logs,but slave does not receive them at all on account of network delay.Only if the slave takes over the application at this time,the committed transactions will miss which means data loss.This is not commonly acceptable in most important product systems especially in the financial ones.

 

2. Master executes these dml operations from client in transaction.

 

Summary:

 1 //Master
 2 (root@localhost mysql3306.sock)[(none)]>set @@global.enforce_gtid_consistency=warn;
 3 Query OK, 0 rows affected (0.13 sec)
 4 
 5 (root@localhost mysql3306.sock)[(none)]>select @@global.enforce_gtid_consistency;
 6 +-----------------------------------+
 7 | @@global.enforce_gtid_consistency |
 8 +-----------------------------------+
 9 | WARN                              |
10 +-----------------------------------+
11 1 row in set (0.06 sec)
12 
13 //Error log of master
14 2018-07-13T07:37:56.877416+01:00 47 [Note] Changed ENFORCE_GTID_CONSISTENCY from OFF to WARN.
15 2018-07-13T07:39:15.748645+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 8825ms. The settings might not be optimal. (flushed=2001 and evicted=0, during the time.)
16 
17 //Slave
18 (root@localhost mysql3306.sock)[(none)]>set @@global.enforce_gtid_consistency=warn;
19 Query OK, 0 rows affected (0.49 sec)
20 
21 (root@localhost mysql3306.sock)[(none)]>select @@global.enforce_gtid_consistency;
22 +-----------------------------------+
23 | @@global.enforce_gtid_consistency |
24 +-----------------------------------+
25 | WARN                              |
26 +-----------------------------------+
27 1 row in set (1.35 sec)
28 
29 //Error log of slave
30 2018-07-13T07:38:02.556232+01:00 27 [Note] Changed ENFORCE_GTID_CONSISTENCY from OFF to WARN.
31 
32 //Make sure there's no warning messages on both master and slave.

 

    Notwithstanding the demonstrating was interupted accidentally but the porcedure of changing classic replication to GTID replicatioin is correct.Onlyif  the slave has finished to change the "gtid_mode" to "on",the implementing is accomplished.

    Synchronous replication requires master to return results to client only after the transactions have been committed by all the slaves(receive and apply).This method will severely lead to bad performance on master unless you can guarantee the slaves can commit immediate without any delay(infact it's tough).Now,the only solution of synchronous replication is still the MySQL NDB Cluster.Therefore,it's not recommended to use synchronous replication way.

 1 //Master
 2 (root@localhost mysql3306.sock)[(none)]>show variables like 'gtid_mode';
 3 +---------------+-------+
 4 | Variable_name | Value |
 5 +---------------+-------+
 6 | gtid_mode     | OFF   |
 7 +---------------+-------+
 8 1 row in set (0.01 sec
 9 
10 //Slave
11 (root@localhost mysql3306.sock)[(none)]>show variables like 'gtid_mode';
12 +---------------+-------+
13 | Variable_name | Value |
14 +---------------+-------+
15 | gtid_mode     | OFF   |
16 +---------------+-------+
17 1 row in set (0.00 sec)

 

 

after_sync(since MySQL 5.7):

本文由澳门在线威尼斯官方发布于电脑数据库,转载请注明出处:半联袂复制,7观念复制到GTID在线切换

关键词:

上一篇:没有了

下一篇:踩坑指南