MySQL5.6：基于GTID的主从复制

　　一、GTID简介

　　是MySQL 5.6 的新特性之一，是加入了全局事务 ID (GTID) 来强化数据库的主备一致性，故障恢复，以及容错能力。

　　什么是GTID?

　　官方文档地址如下：http://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html在这篇文档里，我们可以知道全局事务 ID 的官方定义为：GTID = source_id:transaction_id

　　MySQL 5.6 中，每一个 GTID 代表一个数据库事务。在上面的定义中，source_id 表示执行事务的主库 uuid(server_uuid)，transaction_id 是一个从 1 开始的自增计数，表示在这个主库上执行的第 n 个事务。MySQL 会保证事务与 GTID 之间的 1 : 1 映射。

　　二、环境准备

　　操作系统：CentOS6.5 64位

　　数据库版本：MySQL5.6.23

　　1

　　拓扑如下所示：

　　2

　　三、安装主数据库(masterdb.example.com)

　　1、首先准备数据存放目录、创建用户，如下所示：

　　1 1 [root@masterdb ~]#mkdir /data/mysqldata -p #创建数据存放目录

　　2 2 [root@masterdb ~]#mkdir /data/mysqlLog/logs -p #创建日志存放目录

　　3 3 [root@masterdb ~]#groupadd -r mysql

　　4 4 [root@masterdb ~]#useradd -g mysql -r -s /sbin/nologin -M -d /data/mysqldata mysql

　　5 5 [root@masterdb ~]#chown -R mysql:mysql /data/mysqldata

　　6 6 [root@masterdb ~]#chown -R mysql:mysql /data/mysqlLog/logs

　　2、安装并且初始化mysql5.6.23，如下所示：

　　1 [root@masterdb ~]# tar xf mysql-advanced-5.6.23-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

　　2 [root@masterdb ~]# cd /usr/local/

　　3 [root@masterdb ~]# ln -sv mysql-advanced-5.6.23-linux-glibc2.5-x86_64 mysql

　　4 [root@masterdb ~]# chown -R root.mysql mysql

　　5 [root@masterdb ~]# cd mysql

　　6 [root@masterdb ~]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld

　　7 [root@masterdb ~]# cp support-files/my-default.cnf /etc/my.cnf

　　8 [root@masterdb ~]# chmod +x /etc/rc.d/init.d/mysqld

　　9 [root@masterdb ~]# chkconfig --add mysqld

　　10 [root@masterdb ~]# chkconfig mysqld on

　　11 [root@masterdb ~]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysqldata/

　　3、输出mysql的man手册至man命令的查找路径：

　　编辑/etc/man.config，添加如下行所示即可：

　　MANPATH /usr/local/mysql/man

　　4、输出mysql的头文件至系统头文件路径/usr/include：

　　这可以通过简单的创建链接实现：

　　1 [root@masterdb ~]#ln -sv /usr/local/mysql/include /usr/include/mysql

　　5、输出mysql的库文件给系统库查找路径：

　　1 [root@masterdb ~]#echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf

　　而后让系统重新载入系统库：

　　1 [root@masterdb ~]# ldconfig

　　6、修改PATH环境变量，让系统可以直接使用mysql的相关命令：

　　1 [root@masterdb ~]# vim /etc/profile.d/mysql.sh

　　2 export PATH=$PATH:/usr/local/mysql/bin

　　3 [root@masterdb ~]#source /etc/profile.d/mysql.sh

　　从数据库安装同上。

　　四、分别为主从数据库提供配置文件/etc/my.cnf

　　要在MySQL 5.6中使用复制功能，其服务配置段[mysqld]中于少应该定义如下选项：

　　binlog-format：二进制日志的格式，有row、statement和mixed几种类型;

　　需要注意的是：当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW，现在MySQL官方认为STATEMENT这个已经不再适合继续使用;但mixed类型在默认的事务隔离级别下，可能会导致主从数据不一致;

　　log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host：用于启动GTID及满足附属的其它需求;

　　master-info-repository和relay-log-info-repository：启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能;

　　sync-master-info：启用之可确保无信息丢失;

　　slave-paralles-workers：设定从服务器的SQL线程数;0表示关闭多线程复制功能;

　　binlog-checksum、master-verify-checksum和slave-sql-verify-checksum：启用复制有关的所有校验功能;

　　binlog-rows-query-log-events：启用之可用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度;

　　server-id：同一个复制拓扑中的所有服务器的id号必须惟一;

　　log-bin：启用二进制日志，这是保证复制功能的基本前提;

　　主数据库上：

　　1 [client]

　　2 port = 3306

　　3 socket = /tmp/mysql.sock

　　4 default-character-set = utf8

　　5 [mysql]

　　6 no-auto-rehash

　　7 default-character-set = utf8

　　8

　　9 [mysqld]

　　10 server-id = 1

　　11 port = 3306

　　12 user = mysql

　　13 basedir = /usr/local/mysql

　　14 datadir = /data/mysqldata

　　15 socket = /tmp/mysql.sock

　　16 default-storage-engine = INNODB

　　17 character-set-server = utf8

　　18 connect_timeout = 60

　　19 interactive_timeout = 28800

　　20 wait_timeout = 28800

　　21 back_log = 500

　　22 event_scheduler = ON

　　23 skip_name_resolve = ON;

　　24

　　25 ###########binlog##########

　　26 log-bin = /data/mysqlLog/logs/mysql-bin

　　27 binlog_format = row

　　28 max_binlog_size = 128M

　　29 binlog_cache_size = 2M

　　30 expire-logs-days = 5

　　31 log-slave-updates=true

　　32 gtid-mode=on

　　33 enforce-gtid-consistency=true

　　34 master-info-repository=TABLE

　　35 relay-log-info-repository=TABLE

　　36 sync-master-info=1

　　37 slave-parallel-workers=4

　　38 #rpl_semi_sync_master_enabled = 1

　　39

　　40 slow_query_log = 1

　　41 slow_query_log_file = /data/mysqlLog/logs/mysql.slow

　　42 long_query_time = 1

　　43

　　44 log_error = /data/mysqlLog/logs/error.log

　　45 max_connections = 3000

　　46 max_connect_errors = 32767

　　47 log_bin_trust_function_creators = 1

　　48 transaction_isolation = READ-COMMITTED

　　从数据库上：

　　1 [client]

　　2 port = 3306

　　3 socket = /tmp/mysql.sock

　　4 default-character-set = utf8

　　5

　　6 [mysql]

　　7 no-auto-rehash

　　8 default-character-set = utf8

　　9

　　10 [mysqld]

　　11 server-id = 205

　　12 port = 3306

　　13 user = mysql

　　14 basedir = /usr/local/mysql

　　15 datadir = /data/mysqldata

　　16 socket = /tmp/mysql.sock

　　17 default-storage-engine = INNODB

　　18 character-set-server = utf8

　　19 connect_timeout = 60

　　20 wait_timeout = 18000

　　21 back_log = 500

　　22 event_scheduler = ON

　　23

　　24 ###########binlog##########

　　25 log-bin = /data/mysqlLog/logs/mysql-bin

　　26 binlog_format = row

　　27 max_binlog_size = 128M

　　28 binlog_cache_size = 2M

　　29 expire-logs-days = 5

　　30 log-slave-updates=true

　　31 gtid-mode=on

　　32 enforce-gtid-consistency=true

　　33 master-info-repository=TABLE

　　34 relay-log-info-repository=TABLE

　　35 sync-master-info=1

　　36 slave-parallel-workers=4

　　37 #rpl_semi_sync_slave_enabled = 1

　　38 skip-slave-start

　　39

　　40 slow_query_log = 1

　　41 slow_query_log_file = /data/mysqlLog/logs/mysql.slow

　　42 long_query_time = 2

　　43

　　44 log-error = /data/mysqlLog/logs/error.log

　　45 max_connections = 3000

　　46 max_connect_errors = 10000

　　47 log_bin_trust_function_creators = 1

　　48 transaction_isolation = READ-COMMITTED

　　五、分别在主从数据库上启动mysqld服务

　　1 [root@masterdb ~]# service mysqld start

　　2 Starting MySQL...... [ OK ]

　　3 [root@masterdb ~]#

　　1 [root@slavedb ~]# service mysqld start

　　2 Starting MySQL...... [ OK ]

　　3 [root@slavedb ~]#

　　六、在主数据库上创建复制用户

　　1 mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@172.16.88.205 IDENTIFIED BY 'replpassword';

　　说明：172.16.88.205是从节点服务器;如果想一次性授权更多的节点，可以自行根据需要修改;

　　七、启动从数据库上的复制线程

　　mysql> CHANGE MASTER TO MASTER_HOST='masterdb.example.com', MASTER_USER='repluser', MASTER_PASSWORD='replpassword', MASTER_AUTO_POSITION=1;

　　mysql>start slave;

　　八、在从数据库上查看复制状态：

　　mysql> show slave status\G;

　　*************************** 1. row ***************************

　　Slave_IO_State: Waiting for master to send event

　　Master_Host: masterdb.56xyl.com

　　Master_User: repluser

　　Master_Port: 3306

　　Connect_Retry: 60

　　Master_Log_File: mysql-bin.000002

　　Read_Master_Log_Pos: 191

　　Relay_Log_File: slavedb-relay-bin.000003

　　Relay_Log_Pos: 401

　　Relay_Master_Log_File: mysql-bin.000002

　　Slave_IO_Running: Yes #IO线程已正常运行

　　Slave_SQL_Running: Yes #SQL线程已正常运行

　　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: 191

　　Relay_Log_Space: 1899

　　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: 1

　　Master_UUID: 971d7245-c3f8-11e5-8b6b-000c2999e5a5

　　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 the slave I/O thread to update it

　　Master_Retry_Count: 86400

　　Master_Bind:

　　Last_IO_Error_Timestamp:

　　Last_SQL_Error_Timestamp:

　　Master_SSL_Crl:

　　Master_SSL_Crlpath:

　　Retrieved_Gtid_Set: 971d7245-c3f8-11e5-8b6b-000c2999e5a5:1-6

　　Executed_Gtid_Set: 89e78301-c3f4-11e5-8b51-00505624d26a:1-3,

　　971d7245-c3f8-11e5-8b6b-000c2999e5a5:1-6

　　Auto_Position: 1

　　1 row in set (0.00 sec)

　　ERROR:

　　No query specified

　　mysql>

　　九、测试

　　在主库上创建数据库，如下所示：

　　1 mysql> create database log_statics;

　　2 Query OK, 1 row affected (0.11 sec)

　　3

　　4 mysql> use log_statics;

　　5 Database changed

　　到从数据库上查看log_statics是否已经复制过去

　　1 mysql> show databases;

　　2 +--------------------+

　　3 | Database |

　　4 +--------------------+

　　5 | information_schema |

　　6 | log_statics |

　　7 | mysql |

　　8 | performance_schema |

　　9 +--------------------+

　　10 4 rows in set (0.01 sec)

　　11

　　12 mysql>

　　我们就可以看到log_statics数据库已经存在于从数据库上。