gtid
不支持myisam,可能导致多个gtid分配给同一个事物
不支持:
create table .. select
create /drop temporary table
必须使用enforce-gtid-consistency
sql-slave-skip-counter不支持
5.6.9之后,mysqldump制作的备份己包含gtid,在导入时不要记录gtid
5.5.7之前,使用mysql_upgrade会出现问题
在传统的复制模式下调整:
步骤:
1.将master和slave服务器都设为read-only
set global.read_only=on
2.将m,s服务器都停下来
service msyqld stop
3.开启GTIDs
vi /etc/my.cnf
gtid-mode=on
log-bin
log-slave-updates
enforce-gtid-consistency
skip-slave-start //slave server
4.重新配置slave
change master to
master_host='192.168.134.133',
master_port=3306,
master_user='repl',
master_password='123456',
master_auto_position=1;
start slave;
----------------------
操作过程:
master:
mysqldump -uroot -p123456 -S /tmp/mysql.sock --single-transaction --master-data=2 -A >/root/full_db.sql
mysqldump: Error 1814: Tablespace has been discarded for table 'ibdtest2' when dumping table `ibdtest2` at row: 0
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED BY '123456';
flush privileges;
slave:
mysql -uroot -p123456 <full_db.sql
change master to
master_host='192.168.168.142',
master_port=3306,
master_user='repl',
master_password='123456',
master_log_file='mysql-bin.000030',
master_log_pos=120;
start slave;
测试复制错误:
slave:
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+------+
set sql_log_bin=off;
delete from test where id=3;
set sql_log_bin=on;
master:
delete from test where id=3;
slave:
show slave status\G;
Last_SQL_Error: Could not execute Delete_rows event on table av.test; Can't find record in 'test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000030, end_log_pos 569
mysqlbinlog --base64-output=decode-rows -v mysql-bin.000030
# at 527
#150907 13:51:28 server id 1 end_log_pos 569 CRC32 0xad898eae Delete_rows: table id 115 flags: STMT_END_F
### DELETE FROM `av`.`test`
### WHERE
### @1=3
### @2='c'
# at 569
该条语句引起的故障
解决:
stop slave;
set global sql_slave_skip_counter=1;
start slave;
---------------------
gtid模式下的复制错误:
mysqldump -uroot -p123456 -S /tmp/mysql.sock --single-transaction --default-character-set=utf8 -A >/root/full_db.sql
--set-gtid-purged=OFF
注意,上述脚本中,备份的部分不要加入--set-gtid-purged=OFF参数,防止在备份出的sql脚本中生成 SET @@global.gtid_purged 语句:
在传统的复制模式下调整:
步骤:
1.将master和slave服务器都设为read-only
set global.read_only=on
2.开启GTIDs
vi /etc/my.cnf
gtid-mode=on
log-bin
log-slave-updates
enforce-gtid-consistency
skip-slave-start //slave server
3.将m,s服务器都停下来
service mysqld restart
4.重新配置slave
reset slave;
change master to
master_host='192.168.168.142',
master_port=3306,
master_user='repl',
master_password='123456',
master_auto_position=1;
start slave;
show slave status\G;
slave:
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+------+
delete from test where id=2;
master:
delete from test where id=2;
跳过错误:
slave:
show slave status\G;
Last_SQL_Error: Could not execute Write_rows event on table av.test; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000031, end_log_pos 837
Retrieved_Gtid_Set: 7a393f51-2609-11e5-858f-000c29a50b09:1-4
Executed_Gtid_Set: 7a393f51-2609-11e5-858f-000c29a50b09:1-2
stop slave;
set gtid_next='7a393f51-2609-11e5-858f-000c29a50b09:3';
begin;
commit;
set gtid_next='automatic';
start slave;
show slave status\G;
Last_SQL_Error: Error 'Can't drop database 'sbtest'; database doesn't exist' on query. Default database: 'sbtest'. Query: 'drop database sbtest'
Retrieved_Gtid_Set: 7a393f51-2609-11e5-858f-000c29a50b09:1-4
Executed_Gtid_Set: 7a393f51-2609-11e5-858f-000c29a50b09:1-3
stop slave;
set gtid_next='7a393f51-2609-11e5-858f-000c29a50b09:4';
begin;
commit;
set gtid_next='automatic';
start slave;
show slave status\G;
mysql> show global variables like '%gtid%';
+--------------------------+------------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------------+
| enforce_gtid_consistency | ON |
| gtid_executed | 7a393f51-2609-11e5-858f-000c29a50b09:1-2 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+--------------------------+------------------------------------------+
5 rows in set (0.00 sec)
mysql> insert into test values(3,'c');
Query OK, 1 row affected (0.04 sec)
mysql> show global variables like '%gtid%';
+--------------------------+------------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------------+
| enforce_gtid_consistency | ON |
| gtid_executed | 7a393f51-2609-11e5-858f-000c29a50b09:1-3 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+--------------------------+------------------------------------------+
测试:OK
slave:
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+------+
delete from test where id=2;
master:
delete from test where id=2;
show global variables like '%gtid%';
+--------------------------+------------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------------+
| enforce_gtid_consistency | ON |
| gtid_executed | 7a393f51-2609-11e5-858f-000c29a50b09:1-5 |
注:第5条(delete from test where id=2; )在slave上出错了
slave:
Last_SQL_Error: Could not execute Delete_rows event on table av.test; Can't find record in 'test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000031, end_log_pos 1222
Retrieved_Gtid_Set: 7a393f51-2609-11e5-858f-000c29a50b09:1-5
Executed_Gtid_Set: 17e5d12a-552f-11e5-b8fe-000c2978d92d:1,7a393f51-2609-11e5-858f-000c29a50b09:1-4
解决出错:
stop slave;
set gtid_next='7a393f51-2609-11e5-858f-000c29a50b09:5';
begin;
commit;
set gtid_next='automatic';
start slave;
show slave status\G;
http://isadba.com/?p=433
----------
在gtid模式下的数据dump
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 3 | c |
| 4 | d |
+----+------+
mysqldump -uroot -p123456 -S /tmp/mysql.sock --single-transaction --set-gtid-purged=OFF --default-character-set=utf8 -A >/root/full_db2.sql
mysql <full_db2.sql
master:
Database changed
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 3 | c |
| 4 | d |
+----+------+
show global variables like '%gtid%';
+--------------------------+------------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------------+
| enforce_gtid_consistency | ON |
| gtid_executed | 7a393f51-2609-11e5-858f-000c29a50b09:1-5 |
mysql> insert into test values(6,'e');
Query OK, 1 row affected (0.10 sec)
mysql> show global variables like '%gtid%';
+--------------------------+------------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------------+
| enforce_gtid_consistency | ON |
| gtid_executed | 7a393f51-2609-11e5-858f-000c29a50b09:1-6 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+--------------------------+------------------------------------------+
5 rows in set (0.00 sec)
mysql> insert into test values(7,'f');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(8,'g');
Query OK, 1 row affected (0.02 sec)
mysql> insert into test values(9,'i');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values(10,'o');
Query OK, 1 row affected (0.01 sec)
mysql> show global variables like '%gtid%';
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| enforce_gtid_consistency | ON |
| gtid_executed | 7a393f51-2609-11e5-858f-000c29a50b09:1-10 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+--------------------------+-------------------------------------------+
5 rows in set (0.00 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 3 | c |
| 4 | d |
| 6 | e |
| 7 | f |
| 8 | g |
| 9 | i |
| 10 | o |
+----+------+
slave:
mysql <full_db2.sql
Last_SQL_Error: Could not execute Delete_rows event on table av.test; Can't find record in 'test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000031, end_log_pos 359
Retrieved_Gtid_Set: 7a393f51-2609-11e5-858f-000c29a50b09:1-10
Executed_Gtid_Set: 9f451db6-5534-11e5-b922-000c2978d92d:1-366
mysqlbinlog --base64-output=decode-rows -v mysql-bin.000031
# at 317
#150907 14:06:16 server id 1 end_log_pos 359 CRC32 0x4b98dd99 Delete_rows: table id 70 flags: STMT_END_F
### DELETE FROM `av`.`test`
### WHERE
### @1=2
### @2='b'
# at 359
mysql> show global variables like '%gtid%';
+--------------------------+--------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------+
| enforce_gtid_consistency | ON |
| gtid_executed | 9f451db6-5534-11e5-b922-000c2978d92d:1-366 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+--------------------------+--------------------------------------------+
reset master;
show global variables like '%gtid%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+--------------------------+-------+
reset slave;
change master to
master_host='192.168.168.142',
master_port=3306,
master_user='repl',
master_password='123456',
master_auto_position=1;
start slave;
show slave status\G;
Last_Error: Could not execute Delete_rows event on table av.test; Can't find record in 'test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000031, end_log_pos 359
Retrieved_Gtid_Set: 7a393f51-2609-11e5-858f-000c29a50b09:1-10
解决出错:通过多次跳过,OK
stop slave;
set gtid_next='7a393f51-2609-11e5-858f-000c29a50b09:1';
begin;
commit;
set gtid_next='automatic';
start slave;
show slave status\G;
stop slave;
set gtid_next='7a393f51-2609-11e5-858f-000c29a50b09:2';
begin;
commit;
set gtid_next='automatic';
start slave;
show slave status\G;
stop slave;
set gtid_next='7a393f51-2609-11e5-858f-000c29a50b09:3';
begin;
commit;
set gtid_next='automatic';
start slave;
show slave status\G;
stop slave;
set gtid_next='7a393f51-2609-11e5-858f-000c29a50b09:4';
begin;
commit;
set gtid_next='automatic';
start slave;
show slave status\G;
---------------------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/91975/viewspace-1793501/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/91975/viewspace-1793501/