mysql-5.6的GTID复制的实现

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,感谢各位