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

ZacGrabowsk 9年前

来自: http://www.cnblogs.com/darren-lee/p/5160802.html

一、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

拓扑如下:

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

1、准备数据存放目录、创建用户

 1 [root@masterdb ~]#mkdir /data/mysqldata -p   #创建数据存放目录  2 [root@masterdb ~]#mkdir /data/mysqlLog/logs -p  #创建日志存放目录  3 [root@masterdb ~]#groupadd -r mysql  4 [root@masterdb ~]#useradd -g mysql -r -s /sbin/nologin -M -d /mydata/data mysql  5 [root@masterdb ~]#chown -R mysql:mysql /data/mysqldata  6 [root@masterdb ~]#chown -R mysql:mysql /data/mysqlLog/logs    View Code

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/    View Code

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:启用之可用于在二进制日志记录事件相关的信息,可降低故障排除的复杂度;

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

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

主数据库上:

 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数据库已经存在于从数据库上。

</div>