mysql-5.6的GTID复制的实现
- WBOY原创
- 2016-06-07 14:52:011124浏览
mysql-5.6 的GTID复制的实现 全局事务标示符（Global Transactions Identifier）是MySQL 5.6复制的一个新特性。它为维护特定的复制拓扑结构下服务器的DBA们大幅度改善他们的工作状况提供了多种可能性。然而，你还应该明白当前实现的一些局限，如果使用GTID的
mysql-5.6的GTID复制的实现
全局事务标示符（Global Transactions Identifier）是MySQL 5.6复制的一个新特性。它为维护特定的复制拓扑结构下服务器的DBA们大幅度改善他们的工作状况提供了多种可能性。然而，你还应该明白当前实现的一些局限，如果使用GTID的复制的话不得不启用多个复制参数才能实现基于GTID的复制，比如：
binlog-format #二进制日志的格式，有row、statement和mixed几种类型；需要注意的是：当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW，现在MySQL官方认为STATEMENT这个已经不再适合继续使用；但mixed类型在默认的事务隔离级别下，可能会导致主从数据不一致；
log-slave-updates #slave更新的时候是否记录至日志当中
gtid-mode #指定gtid的类型
enforce-gtid-consistency #是否强制gtid的一致性
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号必须惟一；
安装Mysql 5.6
[root@node2 tools]# tar xfmysql-5.6.13-linux-glibc2.5-x86_64.tar.gz
[root@node2 tools]# mv mysql-5.6.13-linux-glibc2.5-x86_64 /usr/local/mysql
[root@node2 mysql]# useradd -r mysql
[root@node2 mysql]# chown -R root.mysql ./*
[root@node2 mysql]# ll
total 156
drwxr-xr-x 2 root mysql 4096 Jul 30 14:36 bin
-rw-r--r-- 1 root mysql 17987 Jul 11 2013 COPYING
drwxr-xr-x 3 root mysql 4096 Jul 30 14:36 data
drwxr-xr-x 2 root mysql 4096 Jul 30 14:36 docs
drwxr-xr-x 3 root mysql 4096 Jul 30 14:36 include
-rw-r--r-- 1 root mysql 88178 Jul 11 2013 INSTALL-BINARY
drwxr-xr-x 3 root mysql 4096 Jul 30 14:36 lib
drwxr-xr-x 4 root mysql 4096 Jul 30 14:36 man
drwxr-xr-x 10 root mysql 4096 Jul 30 14:36 mysql-test
-rw-r--r-- 1 root mysql 2496 Jul 11 2013 README
drwxr-xr-x 2 root mysql 4096 Jul 30 14:36 scripts
drwxr-xr-x 28 root mysql 4096 Jul 30 14:36 share
drwxr-xr-x 4 root mysql 4096 Jul 30 14:36 sql-bench
drwxr-xr-x 3 root mysql 4096 Jul 30 14:36 support-files
创建数据目录
[root@node2 mysql]# mkdir /data/mydata -p
更改数据目录权限
[root@node2 mysql]# chown -R mysql.mysql/data/
复制配置文件
但是注意的是5.6的my-default.cnf 中没有任何的内容，如果有需求，则需要将5.5的配置文件复制到5.6上即可，否则要自行提供配置
如果我们不提供配置也可以启动，因为默认都是有设定好的参数
这里我们复制mysql 5.5 的配置文件并对其进行修改
[root@node1 mysql]# cpsupport-files/my-default.cnf /etc/my.cnf
[root@node1 mysql]# cpsupport-files/mysql.server /etc/init.d/mysqld
[root@node1 mysql]# chmod +x/etc/init.d/mysqld
修改参数
[root@node1 mysql]# vim /etc/my.cnf
thread_concurrency = 2
datadir = /mydata/data
初始化mysql 并启动
[root@node1 mysql]# scripts/mysql_install_db--user=mysql --datadir=/mydata/data/
[root@node1 mysql]# /etc/init.d/mysqld start
StartingMySQL.. [ OK ]
[root@node1 mysql]# netstat -lnt | grep 3306
tcp 0 0:::3306 :::* LISTEN
登录mysql并查看uuid
mysql> show global variables like'%uuid%';
+---------------+----------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------+
| server_uuid | 18e5d1eb-17bf-11e4-a30a-52540024b957 |
+---------------+----------------------------------------+
1 row in set (0.04 sec)
切换至主节点创建复制权限的用户
mysql> grant replication slave,replicationclient on *.* to 'reluser'@'10.12.33.%' identified by 'replpass';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
基于这种模型的时候启动从节点比较独特
切换至从节点
mysql> CHANGE MASTER TOMASTER_HOST='10.12.33.58',MASTER_USER='reluser',MASTER_PASSWORD='replpass',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.10sec)
查看警告
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code |Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near'wainings' at line 1 |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.12.33.58
Master_User: rep_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File:node2-relay-bin.000001 #发现mysql自己已启动中继日志，之前我们是没有定义的
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running:No
Slave_SQL_Running: No
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:0
Relay_Log_Space: 151
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:NULL
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:0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay:0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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
1 row in set (0.00 sec)
启动从节点
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.12.33.58
Master_User: reluser
Master_Port: 3306
Connect_Retry:60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 547
Relay_Log_File:node2-relay-bin.000002
Relay_Log_Pos:757
Relay_Master_Log_File:mysql-bin.000003
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
再次查看warning信息
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code |Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1753 | slave_transaction_retries is not supported inmulti-threaded slave mode. In the event of a transient failure, the slave willnot retry the transaction and will stop. |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#提示显示我们的从服务器事物重试在多线程模型中是不被支持的，但是可以忽略的
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User |Host | db | Command | Time |State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 1 | root | localhost | NULL |Query | 0 |init | show processlist|
| 2 | system user| | NULL | Connect| 113 | Waiting for master to sendevent |
ULL |
| 3 | system user| | NULL | Connect| 113 | Slave has read all relay log; waiting for the slave I/O thread toupdate it | NULL
| 4 | system user| | NULL | Connect| 406 | Waiting for an event fromCoordinator | NULL |
| 5 | system user| | NULL | Connect| 113 | Waiting for an event fromCoordinator | NULL |
| 6 | system user| | NULL | Connect| 113 | Waiting for an event fromCoordinator | NULL |
| 7 | system user| | NULL | Connect| 412 | Waiting for an event from Coordinator | NULL |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
7 rows in set (0.00 sec)
分别启动了4个复制进程
如果我们在主服务器上，同时创建了多个数据库，而每个数据库都启动了事物，那么这些事物都可以同时进行了
验证效果
创建数据库，并查看主库是否同步
mysql> create database heelllo;
Query OK, 1 row affected (0.00 sec)
在从库查看数据库是否被同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| heelllo |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
查看连接详细，提示已经读进了所有的中继日志信息，并在本地已经应用
而IO目前没有任何信息，随时等待请求
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User |Host | db | Command | Time |State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 1 | root | localhost | NULL |Query | 0 |init | show processlist |
| 2 | system user| | NULL | Connect| 228 | Waiting for master to sendevent | NULL |
| 3 | system user| | NULL | Connect| 46 | Slave has read allrelay log; waiting for the slave I/O thread to update it |NULL |
| 4 | system user| | NULL | Connect| 521 | Waiting for an event fromCoordinator | NULL |
| 5 | system user| | NULL | Connect| 228 | Waiting for an event fromCoordinator | NULL |
| 6 | system user | | NULL | Connect | 228 | Waiting for an event fromCoordinator | NULL |
| 7 | system user| | NULL | Connect| 46 | Waiting for an event fromCoordinator | NULL |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
7 rows in set (0.00 sec)
查看主服务器
因此从服务器目前没有任何数据发送主服务器，所以没有启动任何额外的线程，但是已经显示有一个从节点连接进来了
主机名为node2 并且通过50757端口连接进主服务器3306端口
并且我们已经将GTID信息发送至从服务器
mysql> show processlist;
+----+---------+-------------+------+------------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db |Command | Time |State | Info |
+----+---------+-------------+------+------------------+------+-----------------------------------------------------------------------+------------------+
| 1 | root | localhost | NULL |Query | 0 |init | show processlist |
| 2 | reluser | node2:50767 | NULL | Binlog Dump GTID | 346 |Master has sent all binlog to slave; waiting for binlog to be updated |NULL |
+----+---------+-------------+------+------------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
END,感谢各位