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 映射。

1、主服务器配置：

[client]

port = 3306

default-character-set = utf8

[mysql]

no-auto-rehash

default-character-set = utf8

[mysqld]

server-id = 128

port = 3306

user = mysql

basedir = /usr/local/mysql

default-storage-engine = INNODB

connect_timeout = 60

interactive_timeout = 28800

wait_timeout = 28800

back_log = 500

event_scheduler = ON

skip_name_resolve = ON;

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

binlog_format = row

max_binlog_size = 128M

binlog_cache_size = 2M

expire-logs-days = 5

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=4

#rpl_semi_sync_master_enabled = 1

slow_query_log = 1

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

long_query_time = 1

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

max_connections = 3000

max_connect_errors = 32767

log_bin_trust_function_creators = 1

transaction_isolation = READ-COMMITTED

2、从服务器配置：

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[client]

port = 3306

default-character-set = utf8

[mysql]

no-auto-rehash

default-character-set = utf8

[mysqld]

server-id = 153

port = 3306

user = mysql

basedir = /usr/local/mysql

default-storage-engine = INNODB

connect_timeout = 60

wait_timeout = 18000

back_log = 500

event_scheduler = ON

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

binlog_format = row

max_binlog_size = 128M

binlog_cache_size = 2M

expire-logs-days = 5

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=4

#rpl_semi_sync_master_enabled = 1

slow_query_log = 1

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

long_query_time = 1

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

max_connections = 3000

max_connect_errors = 10000

log_bin_trust_function_creators = 1

transaction_isolation = READ-COMMITTED

"/etc/my.cnf" 69L, 1947C

3、分别在主从数据库上启动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 ~]#

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

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

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

change master to master_host='',master_port=3220,master_user=‘',master_password='',master_auto_position=;

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

mysql> show slave status\G;