MySQL GTID使用小结

1设置gtid文档

GTID(GlobalTransaction ID)是对于一个已提交事务的编号，并且是一个全局唯一的编号。GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。

官方网址：http://dev.mysql.com/doc/refman/5.7/en/replication-gtids-howto.html

 

 

2配置gtid选项

在主库从库执行：

mysql> SET @@global.read_only = ON;

 

 

在主库从库设置gtid模式

设置gtid模式，在my.cnf里面的mysqld选项卡里面设置，设置完后，重启mysql服务生效：

[mysqld] gtid_mode=ON log-slave-updates=ON enforce-gtid-consistency=ON

 

3分配主从账号

在主库从库分配复制账号

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.249.%' IDENTIFIED BY 'rlpbright_1927@ys';

 

 

在从库上设置hostname映射

[root@amp_db_m2 ~]# more /etc/hosts 127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6 10.248.3.61 ampdb1 [root@amp_db_m2 ~]#

 

 

4配置主从复制

在从库上执行：

mysql> change master to master_user='repl', master_password='rlpdna_1927@ys', master_host='ampdb1',master_port=3317, master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.05 sec)   mysql> start slave;   PS：其中这里有差别，以前非gtid的是用master_log_file='mysql-bin.000009',master_log_pos=154;而gtid就采用了master_auto_positon=1;来自动同步主库的binlog了。

 

 

查看从库复制状态，是双YES，而且Seconds_Behind_Master: 0：

mysql> show slave status \G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: brightdb1                   Master_User: repl                   Master_Port: 3317                 Connect_Retry: 60               Master_Log_File: mysql-bin.000011           Read_Master_Log_Pos: 154                Relay_Log_File: mysql-relay-bin.000002                 Relay_Log_Pos: 367         Relay_Master_Log_File: mysql-bin.000011              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                    Last_Error:                  Skip_Counter: 0           Exec_Master_Log_Pos: 154               Relay_Log_Space: 574               Until_Condition: None                Until_Log_File:                 Until_Log_Pos: 0            Master_SSL_Allowed: No            Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No                 Last_IO_Errno: 0                 Last_IO_Error:                Last_SQL_Errno: 0                Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 12001                   Master_UUID: 4f4ad2f1-baeb-11e6-b9b0-0017fa002148              Master_Info_File: mysql.slave_master_info                     SQL_Delay: 0           SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates            Master_Retry_Count: 86400                   Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:                 Auto_Position: 1          Replicate_Rewrite_DB:                  Channel_Name:            Master_TLS_Version: 1 row in set (0.00 sec)   ERROR: No query specified   mysql>

 

 

5关闭只读模式

在从库执行，关闭read_only模式

mysql> SET @@global.read_only = OFF;

 

 

 

6验证主从数据一致性

主库上测试

mysql> create database test; Query OK, 1 row affected (0.01 sec)   mysql> create table z1 select 1 as a; ERROR 1046 (3D000): No database selected mysql> use test; Database changed mysql> create table z1 select 1 as a; ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT. mysql> mysql> create table z1(a int); Query OK, 0 rows affected (0.05 sec)   mysql> mysql> insert into z1 select 1; Query OK, 1 row affected (0.01 sec) Records: 1  Duplicates: 0  Warnings: 0   mysql>

 

从库上查询是否同步

ysql> use test; 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 z1; +------+ | a    | +------+ |    1 | +------+ 1 row in set (0.00 sec)   mysql>

 

看到，数据已经同步过来了。

 

7问题汇总

[root@amp_db_m1 ~]#/usr/local/mysql/bin/mysql -uroot --password='amp_yueworld'--socket='/usr/local/mysql/mysql.sock' -e "  set password = password('amp_yueworld');flush privileges;"

mysql: [Warning] Using a password on thecommand line interface can be insecure.

Please use --connect-expired-password optionor invoke mysql in interactive mode.

[root@amp_db_m1 ~]#

 

解决办法，登陆mysql命令行，手动敲键盘执行：

mysql> alter user root@localhostidentified by 'amp_yueworld';

Query OK, 0 rows affected (0.02 sec)

 

mysql>

 

这个只能命令行执行， -e执行永远会报错

Please use --connect-expired-passwordoption or invoke mysql in interactive mode.

 

 

官网记录：

Note

·        SETPASSWORD ... = PASSWORD('auth_string') syntax is deprecated as of MySQL5.7.6 and will be removed in a future MySQL release.

·        SETPASSWORD ... = 'auth_string' syntax is not deprecated,but ALTER USER is now the preferred statement forassigning passwords. For example:

ALTER USER user IDENTIFIED BY 'auth_string';

 

 

 

8使用的局限

（1）      gtid和非gtid的mysql实例是不能复制数据的，要么都是gtid，要么都是普通的。

 

（2）      更新非事务引擎表，在同一事务中更新事务表与非事务表将导致多个GTIDs分配给同一事务

 

（3）      临时表，事务内部不能执行创建删除临时表语句，但可以在事务外执行，但必须设置set autocommit = 1

 

（4）      CREATE TABLE … SELECTstatements
不安全的基于语句复制，实际是两个独立的事件，一个用于建表，一个用于向新表插入源表数据。

 

（5）      不执行不支持的语句
启用--enforce-gtid-consistency选项启动GTID模式，上述不支持的语句将会返回错误。

 

 

 

参考：http://blog.itpub.net/29733787/viewspace-1462550/