澳门金沙vip 4

【澳门金沙vip】MySQL-5.6版本GTID的主从复制

 1 //Master
 2 (root@localhost mysql3306.sock)[(none)]>set @@global.gtid_mode=on_permissive;
 3 Query OK, 0 rows affected (3.26 sec)
 4 
 5 (root@localhost mysql3306.sock)[(none)]>select @@global.gtid_mode;
 6 +--------------------+
 7 | @@global.gtid_mode |
 8 +--------------------+
 9 | ON_PERMISSIVE      |
10 +--------------------+
11 1 row in set (0.00 sec)
12 
13 //Error log of master
14 2018-07-13T07:57:16.796632+01:00 48 [Note] Changed GTID_MODE from OFF_PERMISSIVE to ON_PERMISSIVE.
15 2018-07-13T07:57:20.034425+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4954ms. The settings might not be optimal. (flushed=752 and evicted=0, during the time.)
16 
17 //Slave
18 (root@localhost mysql3306.sock)[(none)]>set @@global.gtid_mode=on_permissive;
19 Query OK, 0 rows affected (2.22 sec)
20 
21 (root@localhost mysql3306.sock)[(none)]>select @@global.gtid_mode;
22 +--------------------+
23 | @@global.gtid_mode |
24 +--------------------+
25 | ON_PERMISSIVE      |
26 +--------------------+
27 1 row in set (0.06 sec)
28 
29 //Error log of slave
30 2018-07-13T07:56:57.921081+01:00 29 [Note] Changed GTID_MODE from OFF_PERMISSIVE to ON_PERMISSIVE.
31 2018-07-13T07:57:03.109628+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5853ms. The settings might not be optimal. (flushed=733 and evicted=0, during the time.)
32 
33 //I'm afraid it's better to execut "set gtid_mode=on_permissive;" on slave first for best practice even though sometimes it's not obliged to do that.

after_commit(Since
MySQL 5.5):

 

**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”.**

Preface

 

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

 

master_password=123,

 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.

 

 

 

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

 

 

 

2、启动之前,一定要先关闭master的写入,保证所有slave端都已经和master端数据保持同步。

   
The last thing to do in this case is to stop slave,set
“master_auto_position=1”  and start slave again.I’m not going to do
these last steps here(’cause the environment has been
destroyed.oops!).

  • connects
    to master
  • IO
    Thread asks for data(binary logs) and gets them
  • generates
    relay logs
  • SQL Thread applies data(relay
    logs)

(七)、Temporary
tables。

**Change  the parameter “gtid_mode” to
“on_permissive”  on both master and
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.

注意:如果系统运行了很久,无法找到GTID的变好了,可以通过上面的方式进行查找。

1 //The output of "show salve status\G"
2 Last_IO_Errno: 1593
3                 Last_IO_Error: The replication receiver thread cannot start because the master has GTID_MODE = ON and this server has GTID_MODE = OFF
4                 
5 //You cannot modify "gtid_mode" to "on" directly.
6 (root@localhost mysql3306.sock)[(none)]>set @@global.gtid_mode=on;
7 ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.

 

 

**Execute sysbench to generate some
transactions continuously on master.**

    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.

方法二、percona Xtrabackup

 

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

The slave IO thread stops because the master has @@GLOBAL.GTID_MODE ON
and this server has @@GLOBAL.GTID_MODE OFF。

Framework

What
will client do?

2、由于在这个GTID必须是连续的,正常情况同一个服务器产生的GTID是不会存在空缺的。所以不能简单的skip掉一个事务,只能通过注入空事物的方法替换掉一个实际操作事务。

 1 //Master
 2 (root@localhost mysql3306.sock)[(none)]>set @@global.enforce_gtid_consistency=on;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5 (root@localhost mysql3306.sock)[(none)]>select @@global.enforce_gtid_consistency;
 6 +-----------------------------------+
 7 | @@global.enforce_gtid_consistency |
 8 +-----------------------------------+
 9 | ON                                |
10 +-----------------------------------+
11 1 row in set (0.00 sec)
12 
13 //Slave
14 (root@localhost mysql3306.sock)[(none)]>set @@global.enforce_gtid_consistency=on;
15 Query OK, 0 rows affected (0.03 sec)
16 
17 (root@localhost mysql3306.sock)[(none)]>select @@global.enforce_gtid_consistency;
18 +-----------------------------------+
19 | @@global.enforce_gtid_consistency |
20 +-----------------------------------+
21 | ON                                |
22 +-----------------------------------+
23 1 row in set (0.00 sec)

澳门金沙vip 1 
                             

last_Errno:1756

 1 //Master
 2 (root@localhost mysql3306.sock)[(none)]>select @@global.gtid_mode;
 3 +--------------------+
 4 | @@global.gtid_mode |
 5 +--------------------+
 6 | ON                 |
 7 +--------------------+
 8 1 row in set (0.00 sec)
 9 
10 //Error log of master
11 2018-07-13T08:20:59.853460+01:00 50 [Note] Changed GTID_MODE from ON_PERMISSIVE to ON.
12 2018-07-13T08:21:01.804678+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6035ms. The settings might not be optimal. (flushed=745 and evicted=0, during the time.)
13 2018-07-13T08:21:56.202081+01:00 43 [Note] Aborted connection 43 to db: 'unconnected' user: 'repl' host: 'zlm3' (Failed on my_net_write())
14 
15 //Slave
16 (root@localhost mysql3306.sock)[(none)]>set @@global.gtid_mode=on;
17 ERROR 2006 (HY000): MySQL server has gone away
18 No connection. Trying to reconnect...
19 Connection id:    31
20 Current database: *** NONE ***
21 
22 //It's stuck here.Oh my!!!
23 
24 //Check the error log of slave see what has happened.
25 2018-07-13T08:20:49.070915+01:00 25 [ERROR] Disk is full writing './relay-bin.000044' (Errcode: 16026912 - No space left on device). Waiting for someone to free space...
26 2018-07-13T08:20:49.070948+01:00 25 [ERROR] Retry in 60 secs. Message reprinted in 600 secs
27 2018-07-13T08:20:49.104353+01:00 26 [ERROR] Disk is full writing '/data/mysql/mysql3306/logs/mysql-bin.000011' (Errcode: 16026912 - No space left on device). Waiting for someone to free space...
28 2018-07-13T08:20:49.104382+01:00 26 [ERROR] Retry in 60 secs. Message reprinted in 600 secs
29 2018-07-13T08:20:51.712891+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4001ms. The settings might not be optimal. (flushed=742 and evicted=0, during the time.)
30 2018-07-13T08:21:00.346384+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 7634ms. The settings might not be optimal. (flushed=2000 and evicted=0, during the time.)
31 
32 //It shows "[ERROR] Disk is full writing ... ".The test tables have been inserted too many data.
33 
34 [root@zlm3 08:08:06 ~]
35 #df -h
36 Filesystem               Size  Used Avail Use% Mounted on
37 /dev/mapper/centos-root  8.4G  8.4G   20K 100% /  //The root directory is full.
38 devtmpfs                 488M     0  488M   0% /dev
39 tmpfs                    497M     0  497M   0% /dev/shm
40 tmpfs                    497M  6.6M  491M   2% /run
41 tmpfs                    497M     0  497M   0% /sys/fs/cgroup
42 /dev/sda1                497M  118M  379M  24% /boot
43 none                      87G   80G  7.1G  92% /vagrant
44 
45 //Unfortunately,the disk on salve has been writen fully.

Summary:

方法一、

 

    In
this method,master performs a commit before it receives ACK signal from
slave.Let’s suppose a situation that once master crashs after it commits
a transaction but it hasn’t receive the ACK signal from
slave.Meanwhile,failover makes slave become the new
master.How does the slave deal with then?Will the transaction lose?It
depends.There’re two scenarios:

 master:直接执行一个create table select * from table;的sql

 

What
will slave do?

 

   
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

注意:在mysql5.6.9以后的命令才支持这个功能。

 

 

1、在备份的时候指定–master-data=2(来保存binlog的文件号和位置的命令)。

 

澳门金沙vip 2

2、报错:

 

  • generates
    transactions

  • commits
    transactions to master

  • receives
    results from master

3、优点:简单方便。

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

    Nevertheless,on account of different
reasons,slave frequently defers in almost all the scenarios what’s often
grumbled by MySQL dba.Below are different kinds of MySQL
replication.Let’s see the details.

 

**Change the parameter “gtid_mode” to
“on” on both master and slave.**

 
  

log-bin=mysqlbin

    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.

 

 

 

 

 

**Change  the parameter “gtid_mode” to
“off_permissive”  on both master
and slave.**

   
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)

 

3、恢复备份,开启change master to 命令。

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

 

2、错误编号:

  • 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.

What
will master do?

master_user = abobo,

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

    

需要重启才能生效,官方暂时不支持平滑的从classic
replication切换到GTID replication。

 

    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”.

a、在slave上做了无用的或者临时的errant
transaction操作,如果该slave升级成为master的话,连接到它的所有数据库都会获取到这个事务。如果一样就会产生冲突。

 

after_sync(since
MySQL 5.7):

2、报错:

 

澳门金沙vip 3

3、sql线程从relay log中获取GTID,然后对比slave端的binlog是否有记录。

Procedure

    As we all
know,there’re three kinds of replication in MySQL nowadays.Such
as,asynchronous replication,(full)synchronous
replication,semi-synchronous replication.What’s the difference between
them?First of all,let’s see the intact architecture picture of MySQL
replication:

要点:

Preface

3. Generates some binary logs which
contains the transaction information.

 

 

 

#relay log

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

Method of different MySQL
Replication

也就是说在线业务必须统一关闭,然后再启动,会导致服务中断。

*   
One more thing need to do is to modify your “my.cnf” file to make them
support GTID replication after restarting your mysqld process.
Make
sure these three
parameters:”enforce_gtid_consistency=on”,”gtid_mode=on”,”log_slave_updates=on”
are right in your configuration file “my.cnf”.***

    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.

前面是server_uuid:后面是一个序列号

 1 (root@localhost mysql3306.sock)[(none)]>show slave status\G
 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)

 

7800a22c-95ae-11e4-983d-080027de205a:10

Summary

  • semi-synchronous
    replication

 

 1 //Master
 2 (root@localhost mysql3306.sock)[(none)]>show status like 'ongoing_anonymous_transaction_count';
 3 +-------------------------------------+-------+
 4 | Variable_name                       | Value |
 5 +-------------------------------------+-------+
 6 | Ongoing_anonymous_transaction_count | 0     |
 7 +-------------------------------------+-------+
 8 1 row in set (0.66 sec)
 9 
10 //Slave
11 (root@localhost mysql3306.sock)[(none)]>show status like 'ongoing_anonymous_transaction_count';
12 +-------------------------------------+-------+
13 | Variable_name                       | Value |
14 +-------------------------------------+-------+
15 | Ongoing_anonymous_transaction_count | 0     |
16 +-------------------------------------+-------+
17 1 row in set (3.34 sec)
18 
19 //The value of 'ongoing_anonymous_transaction_count' become "0" what means there arn't non-gtid events in binlogs anymore.Therefore,we can do the last step,that is,to change the "gtid_mode" to "on".
  • executes transactions
  • generates binary logs
  • dump thread sends contents(binary
    logs) to slave
  • returns
    results to client

九、GTID的参数注释:

 

   
Semi-synchronous replication seems a workaround of above two method
which can strongly increase the consistency between master and
slave.It’s supported since MySQL 5.5 and enhanced in MySQL 5.7.What’s
the mechanism of semi-sychronouos replication?Master is permitted to
return the result to client merely after only one slave has received
binary logs,write them to the relay logs and returns an ACK signal to
master.There’re two ways of it,that is,after_commit & after_sync.Let’s
see the difference of them:

 

 

 

 

**Change the parameter
“enforce_gitd_consistency” to “on” on both master and
slave.**

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

一种是booking.com出品,这两个差别在淘宝9月份数据库月报里有说明,加了一个桥接的服务器,既可以运行GTID模式下,也可以运行classic模式下。

 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)

 

4、总之:尽量避免产生errant
transaction。可以通过:set
sql_log_bin=off的方式在slave执行sql,但是也要考虑到数据一致性。

 

 
  **Since MySQL
3.2.22,this kind of replication was supported with statement format of
binary log.Then,untill MySQL 5.1.5,row format of binary log was
supported either.The mechanism of it is that as soon as the master dump
thread has sent the binary logs to the slave,the master server returns
the result to client.There’s nothing to guarantee the binary logs are
normally received by the slave(maybe the network failure occurs
simultaneously).So it’s unsafe in consistency what  means your
transactions will lose in the replication.This is also the original
replication of MySQL.Here’s the picture about the procedure:

四、GTID的工作原理:

 1 //Master
 2 (root@localhost mysql3306.sock)[(none)]>set @@globa.gtid_mode=off_permissive;
 3 Query OK, 0 rows affected (0.72 sec)
 4 
 5 (root@localhost mysql3306.sock)[(none)]>select @@global.gtid_mode;
 6 +--------------------+
 7 | @@global.gtid_mode |
 8 +--------------------+
 9 | OFF_PERMISSIVE     |
10 +--------------------+
11 1 row in set (0.01 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 2018-07-13T07:42:38.472436+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 8569ms. The settings might not be optimal. (flushed=2001 and evicted=0, during the time.)
17 2018-07-13T07:44:03.886312+01:00 47 [Note] Changed ENFORCE_GTID_CONSISTENCY from WARN to ON.
18 2018-07-13T07:48:04.137251+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5067ms. The settings might not be optimal. (flushed=713 and evicted=0, during the time.)
19 2018-07-13T07:48:39.586306+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5394ms. The settings might not be optimal. (flushed=704 and evicted=0, during the time.)
20 2018-07-13T07:49:38.441594+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4927ms. The settings might not be optimal. (flushed=709 and evicted=0, during the time.)
21 2018-07-13T07:50:19.070954+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4539ms. The settings might not be optimal. (flushed=721 and evicted=0, during the time.)
22 2018-07-13T07:50:20.930564+01:00 47 [Note] Changed GTID_MODE from OFF to OFF_PERMISSIVE.
23 2018-07-13T07:50:36.490470+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4602ms. The settings might not be optimal. (flushed=705 and evicted=0, during the time.)
24 
25 
26 //Slave
27 (root@localhost mysql3306.sock)[(none)]>set @@global.gtid_mode=off_permissive;
28 Query OK, 0 rows affected (3.02 sec)
29 
30 (root@localhost mysql3306.sock)[(none)]>select @@global.gtid_mode;
31 +--------------------+
32 | @@global.gtid_mode |
33 +--------------------+
34 | OFF_PERMISSIVE     |
35 +--------------------+
36 1 row in set (0.00 sec)
37 
38 //Error log of slave
39 2018-07-13T07:38:02.556232+01:00 27 [Note] Changed ENFORCE_GTID_CONSISTENCY from OFF to WARN.
40 2018-07-13T07:44:22.628014+01:00 27 [Note] Changed ENFORCE_GTID_CONSISTENCY from WARN to ON.
41 2018-07-13T07:49:33.136288+01:00 27 [Note] Aborted connection 27 to db: 'unconnected' user: 'root' host: 'localhost' (Got timeout reading communication packets)
42 2018-07-13T07:50:27.360767+01:00 28 [Note] Changed GTID_MODE from OFF to OFF_PERMISSIVE.
43 2018-07-13T07:50:39.972826+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 10489ms. The settings might not be optimal. (flushed=2001 and evicted=0, during the time.)

 

两种方法:

 

澳门金沙vip 4

1、case重现:

  • 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).

2、gtid_executed:全局和seeeion级别都可以用。用来保存已经执行过的GTIDs。

  • asynchronous
    replication

master_auto_position = 1;

 

binlog_format=row   
  #强烈建议,其他格式可能造成数据不一致

    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.

3、将备份还原到slave后,使用change master to命令挂载master端。

1. Client sends dml operations to the
master while the transaction starts.

4、GTID是连续没有空洞的,因此主从库出现数据冲突时,可以用添加空事物的方式进行跳过。

   
Caution,semi-synchronous replication may turn into asynchronous
replication whenever the delay time of slave surpass the value which is
specified in parameter “rpl_semi_sync_master_timeout”(default
values is 10000 milliseconds).Why it is permitted?I’m afraid in order to
consider the performance of master.Notwithstanding,you can also
play a trick to prevent it from being converted over by set a infinite
number in this parameter such as “10000000” or above.Especially in case
that your product system is too important to not lose data.

2、原理是直接获取master所有的GTID并执行。

   
Therefore,after_commit cannot guarantee lossless replication.after_commit is the default mode(actually
it’s the only mode can be use) which is supported by MySQL 5.5 &
5.6.

3、比传统复制更加安全。

   
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;”

 

start slave;

 

 

2、GTID事物是全局唯一性的,且一个事务对应一个GTID。

如果–enforce_gtid_consistency参数开启,并且autocommit=1,那么可以使用。

 

1、update t1,t2 set t1.id=1000,t2.id=1000 where t1.id=t2.id;

1、master更新数据时,会在事务前产生GTID,一同记录到binlog日志中。

 

(1)、简单的stop slave; start
slave;就能够忽略错误。但是这个时候主从的一致性已经出现问题。需要手工的把slave差的数据补上。

3、使用下面的sql切换slave到新的master。

3、优点:是可以避免第一种方法的不足。

 

3、如果slave有errant
transaction产生,由于GTID协议中的规则,很容易导致failover失败。主要有两种情况:

 

将一个开启gtid的slave通过原始的binlog和pos方式连接到开启GTID的master。

三、GTID比传统复制的优势:

1、启动以后最好不要立即执行事务,而是先change master上。

4、这里的xxxxx:N 也就是你的slave sql
thread报错的GTID,或者说是你想要跳过的GTID。

(八)、关于Errant
transaction

2、然后在执行事务,当然知不是必须的。

 

set gtid_next=’xxxxxxx:N’;

 

error 1786

 

3、gtid_owned:全局和session级别都可用,全局表示所有服务器拥有GTIDs,session级别表示当前client拥有所有GTIDs。(此功能用的少)

(一)、使用GTID的方式,把salve端挂载master端: