之前写过基于位置点的数据库主从复制（https://note.t4x.org/database/configuration-mysql-master-slave/），下面这个是关于GTID的复制方式：
MySQL安装：
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
[root@Test5 ~]# groupadd -r mysql -g 27 [root@Test5 ~]# useradd -r mysql -u 27 -g mysql -d /data/ -s /sbin/nologin [root@Test5 ~]# yum install pcre-devel openssl openssl-devel gcc gcc-c++ cmake libaio-devel library* ncurses-devel bison [root@Test5 ~]# cd mysql-5.6.33 [root@Test5 mysql-5.6.33]# cmake . -DCMAKE_INSTALL_PREFIX=/opt/mysql-5.6.33 -DMYSQL_UNIX_ADDR=/opt/mysql-5.6.33/tmp/mysql.sock -DMYSQL_DATADIR=/opt/mysql-5.6.33/data -DWITH_DEBUG=0 -DEXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_ZLIB=system -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 [root@Test5 mysql-5.6.33]# make -j 2 && make install [root@Test5 mysql-5.6.33]# ln -s /opt/mysql-5.6.33 /usr/local/mysql [root@Test5 mysql-5.6.33]# cp /etc/my.cnf /etc/my.cnf.bk [root@Test5 mysql-5.6.33]# cp support-files/my-default.cnf /etc/my.cnf #老版本可能会影响，因此先将新版本的复制过来，先确保mysql可以启动成功。 [root@Test5 mysql-5.6.33]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql [root@Test5 mysql-5.6.33]# cp support-files/mysql.server /etc/init.d/mysqld [root@Test5 mysql-5.6.33]# chmod +x /etc/init.d/mysqld [root@Test5 mysql-5.6.33]# netstat -tunlp|grep 3306 tcp 0 0 :::3306 :::* LISTEN 635/mysqld |
MASTER配置：
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
mysql> show variables like '%log_bin%'; +---------------------------------+---------------------------------------+ | Variable_name | Value | +---------------------------------+---------------------------------------+ | log_bin | ON | | log_bin_basename | /usr/local/mysql/data/mysql-bin | | log_bin_index | /usr/local/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+---------------------------------------+ mysql> show variables like '%server_id%'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | server_id | 1 | | server_id_bits | 32 | +----------------+-------+ mysql> grant replication slave on *.* to abc@'%' identified by 'abc'; mysql> flush privileges; mysql> select user,host from mysql.user; +------+------------------+ | user | host | +------+------------------+ | abc | % | | root | 127.0.0.1 | | root | localhost | | root | test4.hz.t4x.org | +------+------------------+ mysql> create database abc; mysql> use abc; mysql> create table test( -> id int(4) not null primary key auto_increment, -> name char(20) not null -> ); mysql> insert into test(id,name) values(1,'zy'); mysql> select * from test; +----+------+ | id | name | +----+------+ | 1 | zy | +----+------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES like '%gtid%'; +---------------------------------+-----------+ | Variable_name | Value | +---------------------------------+-----------+ | binlog_gtid_simple_recovery | OFF | | enforce_gtid_consistency | ON | | gtid_executed | | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | simplified_binlog_gtid_recovery | OFF | +---------------------------------+-----------+ 8 rows in set (0.00 sec) mysql> SHOW VARIABLES like '%server_uuid%'; +---------------+--------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------+ | server_uuid | a030dc5f-a0f9-11e6-ac8c-000c2934a839 | +---------------+--------------------------------------+ 1 row in set (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000001 | 1114 | | | a030dc5f-a0f9-11e6-ac8c-000c2934a839:1-5 | +------------------+----------+--------------+------------------+------------------------------------------+ ---------------------------多余操作--------------------------- [root@Test4 mysql-5.6.33]# /usr/local/mysql/bin/mysqldump -uroot -p -B abc --set-gtid-purged=OFF > /tmp/abc$(date +%F).sql #数据备份，不需要 ---------------------------多余操作--------------------------- |
SLAVE配置：
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
---------------------------多余操作--------------------------- [root@Test5 ~]# /usr/local/mysql/bin/mysql -uroot -p < /root/abc2016-11-02.sql #不要数据还原 mysql> use abc mysql> show tables; +---------------+ | Tables_in_abc | +---------------+ | test | +---------------+ mysql> select * from test; +----+------+ | id | name | +----+------+ | 1 | zy | +----+------+ ---------------------------多余操作--------------------------- mysql> reset slave; mysql> CHANGE MASTER TO -> MASTER_HOST='1.1.1.104', -> MASTER_PORT=3306, -> MASTER_USER='abc', -> MASTER_PASSWORD='abc', -> 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: 1.1.1.104 Master_User: abc Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 191 Relay_Log_File: relay-bin.000006 Relay_Log_Pos: 401 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: mysql.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 191 Relay_Log_Space: 849 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: a030dc5f-a0f9-11e6-ac8c-000c2934a839 Master_Info_File: /usr/local/mysql/data/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: a030dc5f-a0f9-11e6-ac8c-000c2934a839:1-5 Executed_Gtid_Set: a030dc5f-a0f9-11e6-ac8c-000c2934a839:1-5 Auto_Position: 1 1 row in set (0.00 sec) |
验证：
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
mysql> show processlist\G #主库 *************************** 1. row *************************** Id: 1 User: root Host: localhost db: NULL Command: Query Time: 0 State: init Info: show processlist *************************** 2. row *************************** Id: 4 User: abc Host: 1.1.1.105:39419 db: NULL Command: Binlog Dump GTID Time: 670 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 3. row *************************** Id: 5 User: abc Host: 1.1.1.105:39432 db: NULL Command: Binlog Dump GTID Time: 309 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL mysql> insert into abc.test(id,name) values(2,'zyf'); ================================================== mysql> use abc #备库 mysql> select * from test; +----+------+ | id | name | +----+------+ | 1 | zy | | 2 | zyf | +----+------+ 2 rows in set (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 1.1.1.104 Master_User: abc Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 431 Relay_Log_File: relay-bin.000006 Relay_Log_Pos: 641 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: mysql.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 431 Relay_Log_Space: 1089 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: a030dc5f-a0f9-11e6-ac8c-000c2934a839 Master_Info_File: /usr/local/mysql/data/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: a030dc5f-a0f9-11e6-ac8c-000c2934a839:1-6 Executed_Gtid_Set: a030dc5f-a0f9-11e6-ac8c-000c2934a839:1-6 Auto_Position: 1 1 row in set (0.00 sec) |
配置文件：
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
[client] port = 3306 socket = /usr/local/mysql/tmp/mysql.sock [mysqld] user = mysql port = 3306 socket = /usr/local/mysql/tmp/mysql.sock basedir = /usr/local/mysql datadir = /usr/local/mysql/data open_files_limit = 10240 back_log = 600 max_connections = 3000 max_connect_errors = 6000 external-locking = FALSE max_allowed_packet = 32M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 300 query_cache_size = 64M query_cache_limit = 4M query_cache_min_res_unit = 2k replicate-wild-ignore-table=mysql.% thread_stack = 192K transaction_isolation = READ-COMMITTED tmp_table_size = 256M max_heap_table_size = 256M long_query_time = 2 log-error = /usr/local/mysql/tmp/error.log pid-file = /usr/local/mysql/tmp/mysql.pid log-bin = /usr/local/mysql/data/mysql-bin relay_log = /usr/local/mysql/relay-bin relay-log-info-file = /usr/local/mysql/relay-log.info binlog_cache_size = 4M max_binlog_cache_size = 8M max_binlog_size = 512M expire_logs_days = 7 key_buffer_size = 32M read_buffer_size = 1M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_repair_threads = 1 myisam_recover lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062,1007 replicate-ignore-db = mysql server-id = 9266 gtid_mode = on enforce_gtid_consistency = on log-bin = /usr/local/mysql/data/mysql-bin log-slave-updates = 1 binlog_format = row skip_slave_start = 1 log-slave-updates = true |
说明：SourceByrd's Weblog-https://note.t4x.org/database/mysql-global-transaction-identifieds/
一、GTID的概述：SourceByrd's Weblog-https://note.t4x.org/database/mysql-global-transaction-identifieds/
1、全局事物标识：global transaction identifieds。
2、GTID事物是全局唯一性的，且一个事务对应一个GTID。
3、一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。
4、GTID用来代替classic的复制方法，不在使用binlog+pos开启复制。而是使用master_auto_postion=1的方式自动匹配GTID断点进行复制。
5、MySQL-5.6.5开始支持的，MySQL-5.6.10后开始完善。
6、在传统的slave端，binlog是不用开启的，但是在GTID中，slave端的binlog是必须开启的，目的是记录执行过的GTID（强制）。SourceByrd's Weblog-https://note.t4x.org/database/mysql-global-transaction-identifieds/二、GTID的组成部分：SourceByrd's Weblog-https://note.t4x.org/database/mysql-global-transaction-identifieds/
前面是server_uuid：后面是一个序列号
例如：server_uuid：sequence number
7800a22c-95ae-11e4-983d-080027de205a:10
UUID：每个mysql实例的唯一ID，由于会传递到slave，所以也可以理解为源ID。
Sequence number：在每台MySQL服务器上都是从1开始自增长的序列，一个数值对应一个事务。SourceByrd's Weblog-https://note.t4x.org/database/mysql-global-transaction-identifieds/三、GTID比传统复制的优势：SourceByrd's Weblog-https://note.t4x.org/database/mysql-global-transaction-identifieds/
1、更简单的实现failover，不用以前那样在需要找log_file和log_Pos。
2、更简单的搭建主从复制。
3、比传统复制更加安全。
4、GTID是连续没有空洞的，因此主从库出现数据冲突时，可以用添加空事物的方式进行跳过。SourceByrd's Weblog-https://note.t4x.org/database/mysql-global-transaction-identifieds/四、GTID的工作原理：
1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
2、slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
6、在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。 SourceByrd's Weblog-https://note.t4x.org/database/mysql-global-transaction-identifieds/
阿里云RDS实战复制：
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
[root@BACKUP 123]# yum install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL [root@BACKUP 123]# yum install libev [root@BACKUP 123]# rpm -ivh percona-xtrabackup-24-debuginfo-2.4.4-1.el6.x86_64.rpm #阿里云采用percona-Xtrabackup [root@BACKUP 123]# wget http://oss.aliyuncs.com/aliyunecs/rds_backup_extract.sh?spm=5176.7741817.0.0.yzujR0&file=rds_backup_extract.sh [root@BACKUP 123]# bash rds_backup_extract.sh -f hins1979171_data_20151118233620.tar.gz -C abc [root@BACKUP 123]# tar zcf /tmp/mysql.$(date +%F).tar.gz /usr/local/mysql/data [root@BACKUP 123]# mv /usr/local/mysql/data/ /tmp/ #主要是mysql数据库，阿里云的mysql数据库可能会导致下面1872的错误 [root@BACKUP 123]# mkdir /usr/local/mysql/data [root@BACKUP 123]# mv abc/* /usr/local/mysql/data/ [root@BACKUP 123]# chown -R mysql.mysql /usr/local/mysql/data/ [root@BACKUP 123]# ll /usr/local/mysql/data/ [root@BACKUP 123]# innobackupex --defaults-file=/usr/local/mysql/data/backup-my.cnf --apply-log /usr/local/mysql/data/ #这步是数据还原，请看阿里云官方文档 InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 10243027208 161121 14:41:25 completed OK! [root@BACKUP 123]# grep "#" /usr/local/mysql/data/backup-my.cnf #innodb_fast_checksum=false #innodb_page_size=16384 #innodb_log_block_size=512 #rds_encrypt_data=false [root@BACKUP 123]# mysqld_safe --defaults-file=/usr/local/mysql/data/backup-my.cnf --user=mysql --datadir=/usr/local/mysql/data/ & #如果启动错误，请查看err_log一般都有很容易查找错误原因。 [root@BACKUP 123]# netstat -tunlp|grep 3306 #空密码 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 18679/mysqld ###注意事项，此时可以将data目录下的mysql移动走，将之前备份过的mysql移动过来。### |
数据库配置GTID复制：
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | root | 127.0.0.1 | | root | ::1 | | root | localhost | +------+-----------+ 3 rows in set (0.00 sec) mysql> truncate table mysql.slave_master_info; #不需要，我采用我本地mysql数据库 mysql> truncate table mysql.slave_relay_log_info; #不需要，我采用我本地mysql数据库 [root@Master data]# cat /usr/local/mysql/data/xtrabackup_slave_info #查看阿里云的gtid mysql> reset slave; #重置slave，不需要 mysql> SET GLOBAL gtid_purged='3e11144c-a631-77e6-8e92-6c92bf292864:1-26821, 4c6df71f-a631-77e6-8e92-6c92bf29285e:1-2713482'; # mysql> CHANGE MASTER TO MASTER_HOST='abc.mysql.rds.aliyuncs.com', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='abc', MASTER_AUTO_POSITION=1; mysql> START SLAVE; ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository #这个是因为阿里云mysql数据库的问题，请采用默认数据库，就是之前备份的MYSQL数据库 |
错误整理：
1：错误1
|
0 1 2 |
Q：2016-11-02 20:42:48 1811 [ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires --log-bin and --log-slave-updates A：my.cnf参数中通gtid_mode=ON,log_slave_updates,enforce_gtid_consistency必须同时存在 |
2：错误2
|
0 1 2 |
Q：Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). A：mysql> SET GLOBAL server_id=2; # 原因是我在my.cnf里面配置了两个server-id，然后我一直在修改其中一个。。。然后。。 |
3：错误3
|
0 1 2 |
Q：Last_SQL_Error: Error 'Table 'test' already exists' on query. Default database: 'abc'. Query: 'create table test( id int(4) not null primary key auto_increment, #Last_SQL_Errno: 1050 A：重新启动即可。由于我思路问题，gtid会自动同步，不需要进行备份还原操作，或者通过上面的阿里云那个案例还原的方式，一般主库binlog存在，默认就好，可能会慢一点。 |
帮助文档：http://rpmfind.net/linux/rpm2html/search.php?query=libev.so.4&submit=Search+...
参考文档：
1：http://www.cnblogs.com/abobo/p/4242417.html
2：https://help.aliyun.com/knowledge_detail/41817.html
3：http://blog.csdn.net/dongsong1117/article/details/51800072
4：http://blog.csdn.net/thundermeng/article/details/50401150
5：http://www.cnblogs.com/cenalulu/p/4309009.htmlSourceByrd's Weblog-https://note.t4x.org/database/mysql-global-transaction-identifieds/ SourceByrd's Weblog-https://note.t4x.org/database/mysql-global-transaction-identifieds/
申明：除非注明Byrd's Blog内容均为原创,未经许可禁止转载!详情请阅读版权申明!
