mysql GTID从库突然断电导致1062错误 除了重做从库 有没有快速修复的办法

回复

hufuxin 发起了问题 • 1 人关注 • 0 个回复 • 1048 次浏览 • 2016-04-05 15:47 • 来自相关话题

官方半同步和增强半同步的问题?

huzilin 回复了问题 • 2 人关注 • 1 个回复 • 557 次浏览 • 2016-03-15 17:19 • 来自相关话题

关于参数binlog_rows_query_log_events的疑惑

zhugaojian 回复了问题 • 2 人关注 • 1 个回复 • 700 次浏览 • 2016-02-26 17:47 • 来自相关话题

[求助]GTID模式下变更架构报错

wubx 回复了问题 • 2 人关注 • 1 个回复 • 565 次浏览 • 2016-02-15 16:33 • 来自相关话题

mysql5.7 gtid复制安装及排错

gaoquan 发表了文章 • 0 个评论 • 474 次浏览 • 2016-01-27 10:56 • 来自相关话题

$$$$$$$$$$$$$$$$$$$$$$$$$环境说明$$$$$$$$$$$$$$$$$$$$$$$$$

主:192.168.31.71

从:192.168.31.72

操作系统:centos 6.6

mysql:5.7.10

mysql_base /usr/local/mysql

配置文件见附件,主从的区别是server_id不同,切记保证主从server_id不同

&&&&&&&&&&&&&&&&&&&&&&&&&主从操作$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

主:


初始化

[root@mysql57-71 data]# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/etc/my.cnf --initialize --user=mysql

生成ssl文件,5.7采用openssl安全加密,可以不用这一步

[root@mysql57-71 data]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --defaults-file=/data/mysql/mysql_3306/etc/my.cnf

更改权限

[root@mysql57-71 data]# chown -R mysql:mysql /data/mysql/mysql_3306/data/*

启动mysql

[root@mysql57-71 data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/etc/my.cnf &

获取root临时密码,5.7一大改进,之前版本root默认没有密码

[root@mysql57-71 data]# cat /data/mysql/mysql_3306/data/error.log |grep temporary

2016-01-25T11:18:48.302304+08:00 1 [Note] A temporary password is generated for root@localhost:Wl3jdkL_9_7c  

2016-01-25T11:24:12.307468+08:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables

更改root临时密码

mysql> mysql -S /tmp/mysql.sock -uroot -p

alter user 'root'@'localhost' identified by 'password';

创建复制账号

mysql> grant replication slave,replication client on *.* to 'repl'@'%' identified by 'slavepass';




从:

初始化

[root@mysql57-71 data]# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/etc/my.cnf --initialize --user=mysql

生成ssl文件,5.7采用openssl安全加密,可以不用这一步

[root@mysql57-71 data]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --defaults-file=/data/mysql/mysql_3306/etc/my.cnf

更改权限

[root@mysql57-71 data]# chown -R mysql:mysql /data/mysql/mysql_3306/data/*

启动mysql

[root@mysql57-71 data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/etc/my.cnf &

获取root临时密码,5.7一大改进,之前版本root默认没有密码

[root@mysql57-71 data]# cat /data/mysql/mysql_3306/data/error.log |grep temporary

2016-01-25T11:18:48.302304+08:00 1 [Note] A temporary password is generated for root@localhost: Wl3jdkL_9_7c  

2016-01-25T11:24:12.307468+08:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables

更改root临时密码

mysql> mysql -S /tmp/mysql.sock -uroot -p

alter user 'root'@'localhost' identified by 'password';

创建复制关系

change master to master_host='192.168.31.71',master_port=3306,master_user='repl',master_password='slavepass',master_auto_position=1;

启动slave

查看复制状态,注意此时Slave_SQL_Running: No,从库没能正常运行


mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.31.71

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000002

          Read_Master_Log_Pos: 741

               Relay_Log_File: relay.000002

                Relay_Log_Pos: 27063

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            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: 1062

                   Last_Error: Could not execute Write_rows event on table mysql.server_cost; Duplicate entry 'row_evaluate_cost' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 27118

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 26850

              Relay_Log_Space: 100809

              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: 1062

               Last_SQL_Error: Could not execute Write_rows event on table mysql.server_cost; Duplicate entry 'row_evaluate_cost' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 27118

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 713306

                  Master_UUID: 0fbdce0d-c495-11e5-9748-0800279bd495

             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: 160127 09:29:07

               Master_SSL_Crl: 

           Master_SSL_Crlpath: 

           Retrieved_Gtid_Set: 0fbdce0d-c495-11e5-9748-0800279bd495:1-135

            Executed_Gtid_Set: 0ee5a5bd-c495-11e5-82f1-0800279bd495:1-134,

0fbdce0d-c495-11e5-9748-0800279bd495:2-30

                Auto_Position: 1

         Replicate_Rewrite_DB: 

                 Channel_Name: 

1 row in set (0.00 sec)

错误原因分析

错误为1062,主键冲突错误,主要原因是在初始化时,mysql的元数据库会进行表的创建及插入,主从都会执行,做这些动作时,所有的事务都会相应的分配gtid号,因此当启动从时会将主上的事件回放一遍,当回放到事务号为31时,从库报主键错误,那么为何不是从1开始报错呢?是因为配置文件中启用了slave_skip_errors = ddl_exist_errors,在30号之前的事务id都是建表语句,之后为insert插入语句。

错误解决方式一

从库执行

mysql> stop slave;

mysql> reset master;

mysql> set gtid_purged="0ee5a5bd-c495-11e5-82f1-0800279bd495:1-134,0fbdce0d-c495-11e5-9748-0800279bd495:1-134"

mysql> start slave;

原理:跳过重复的gtid事务

错误解决方式二

主从初始化时指定gtid-mode=off:

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/etc/my.cnf --initialize --user=mysql --gtid-mode=off


此时采用mysqlbinlog去查看binlog日志可发现默认的gtid是ANONYMOUS:

[root@mysql57-71 logs]# mysqlbinlog -v --base64-output=decode-row ./mysql-bin.000001


#160127 10:41:49 server id 713306  end_log_pos 99259 CRC32 0xf89f3faf     Anonymous_GTID    last_committed=132    sequence_number=133


SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 99259

接下来的操作和上面主从操作一致。

原理:在初始化时mysql元数据的操作不记录gtid,启动后所有的操作记录gtid,且gtid事务号从1开始,从而避免1062错误。




[root@mysql57-71 logs]# mysqlbinlog -v --base64-output=decode-row ./mysql-bin.000002 

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#160127 10:49:41 server id 713306  end_log_pos 123 CRC32 0x35ee3e2d     Start: binlog v 4, server v 5.7.9-log created 160127 10:49:41 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

# at 123

#160127 10:49:41 server id 713306  end_log_pos 154 CRC32 0x21d84665     Previous-GTIDs

# [empty]

# at 154

#160127 10:50:38 server id 713306  end_log_pos 219 CRC32 0x2a912c89     GTID    last_committed=0    sequence_number=1

SET @@SESSION.GTID_NEXT= '7f2c189e-c49f-11e5-b3f9-0800279bd495:1'/*!*/;

# at 219

#160127 10:50:38 server id 713306  end_log_pos 398 CRC32 0x9bf46a0f     Query    thread_id=2    exec_time=0    error_code=0

SET TIMESTAMP=1453863038/*!*/;

SET @@session.pseudo_thread_id=2/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1436549120/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*CCDDC44F45A76B3B3C5E46A5B0D17E88ABC2B4BA'

/*!*/;

# at 398

#160127 10:51:13 server id 713306  end_log_pos 463 CRC32 0xaf535af9     GTID    last_committed=1    sequence_number=2

SET @@SESSION.GTID_NEXT= '7f2c189e-c49f-11e5-b3f9-0800279bd495:2'/*!*/;

# at 463

#160127 10:51:13 server id 713306  end_log_pos 701 CRC32 0x60a0eced     Query    thread_id=2    exec_time=0    error_code=0

SET TIMESTAMP=1453863073/*!*/;

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*809534247D21AC735802078139D8A854F45C31F3'

/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 查看全部
$$$$$$$$$$$$$$$$$$$$$$$$$环境说明$$$$$$$$$$$$$$$$$$$$$$$$$

主:192.168.31.71

从:192.168.31.72

操作系统:centos 6.6

mysql:5.7.10

mysql_base /usr/local/mysql

配置文件见附件,主从的区别是server_id不同,切记保证主从server_id不同

&&&&&&&&&&&&&&&&&&&&&&&&&主从操作$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

主:


初始化

[root@mysql57-71 data]# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/etc/my.cnf --initialize --user=mysql

生成ssl文件,5.7采用openssl安全加密,可以不用这一步

[root@mysql57-71 data]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --defaults-file=/data/mysql/mysql_3306/etc/my.cnf

更改权限

[root@mysql57-71 data]# chown -R mysql:mysql /data/mysql/mysql_3306/data/*

启动mysql

[root@mysql57-71 data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/etc/my.cnf &

获取root临时密码,5.7一大改进,之前版本root默认没有密码

[root@mysql57-71 data]# cat /data/mysql/mysql_3306/data/error.log |grep temporary

2016-01-25T11:18:48.302304+08:00 1 [Note] A temporary password is generated for root@localhost:Wl3jdkL_9_7c  

2016-01-25T11:24:12.307468+08:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables

更改root临时密码

mysql> mysql -S /tmp/mysql.sock -uroot -p

alter user 'root'@'localhost' identified by 'password';

创建复制账号

mysql> grant replication slave,replication client on *.* to 'repl'@'%' identified by 'slavepass';




从:

初始化

[root@mysql57-71 data]# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/etc/my.cnf --initialize --user=mysql

生成ssl文件,5.7采用openssl安全加密,可以不用这一步

[root@mysql57-71 data]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --defaults-file=/data/mysql/mysql_3306/etc/my.cnf

更改权限

[root@mysql57-71 data]# chown -R mysql:mysql /data/mysql/mysql_3306/data/*

启动mysql

[root@mysql57-71 data]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/etc/my.cnf &

获取root临时密码,5.7一大改进,之前版本root默认没有密码

[root@mysql57-71 data]# cat /data/mysql/mysql_3306/data/error.log |grep temporary

2016-01-25T11:18:48.302304+08:00 1 [Note] A temporary password is generated for root@localhost: Wl3jdkL_9_7c  

2016-01-25T11:24:12.307468+08:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables

更改root临时密码

mysql> mysql -S /tmp/mysql.sock -uroot -p

alter user 'root'@'localhost' identified by 'password';

创建复制关系

change master to master_host='192.168.31.71',master_port=3306,master_user='repl',master_password='slavepass',master_auto_position=1;

启动slave

查看复制状态,注意此时Slave_SQL_Running: No,从库没能正常运行


mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.31.71

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000002

          Read_Master_Log_Pos: 741

               Relay_Log_File: relay.000002

                Relay_Log_Pos: 27063

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            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: 1062

                   Last_Error: Could not execute Write_rows event on table mysql.server_cost; Duplicate entry 'row_evaluate_cost' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 27118

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 26850

              Relay_Log_Space: 100809

              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: 1062

               Last_SQL_Error: Could not execute Write_rows event on table mysql.server_cost; Duplicate entry 'row_evaluate_cost' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 27118

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 713306

                  Master_UUID: 0fbdce0d-c495-11e5-9748-0800279bd495

             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: 160127 09:29:07

               Master_SSL_Crl: 

           Master_SSL_Crlpath: 

           Retrieved_Gtid_Set: 0fbdce0d-c495-11e5-9748-0800279bd495:1-135

            Executed_Gtid_Set: 0ee5a5bd-c495-11e5-82f1-0800279bd495:1-134,

0fbdce0d-c495-11e5-9748-0800279bd495:2-30

                Auto_Position: 1

         Replicate_Rewrite_DB: 

                 Channel_Name: 

1 row in set (0.00 sec)

错误原因分析

错误为1062,主键冲突错误,主要原因是在初始化时,mysql的元数据库会进行表的创建及插入,主从都会执行,做这些动作时,所有的事务都会相应的分配gtid号,因此当启动从时会将主上的事件回放一遍,当回放到事务号为31时,从库报主键错误,那么为何不是从1开始报错呢?是因为配置文件中启用了slave_skip_errors = ddl_exist_errors,在30号之前的事务id都是建表语句,之后为insert插入语句。

错误解决方式一

从库执行

mysql> stop slave;

mysql> reset master;

mysql> set gtid_purged="0ee5a5bd-c495-11e5-82f1-0800279bd495:1-134,0fbdce0d-c495-11e5-9748-0800279bd495:1-134"

mysql> start slave;

原理:跳过重复的gtid事务

错误解决方式二

主从初始化时指定gtid-mode=off:

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/etc/my.cnf --initialize --user=mysql --gtid-mode=off


此时采用mysqlbinlog去查看binlog日志可发现默认的gtid是ANONYMOUS:

[root@mysql57-71 logs]# mysqlbinlog -v --base64-output=decode-row ./mysql-bin.000001


#160127 10:41:49 server id 713306  end_log_pos 99259 CRC32 0xf89f3faf     Anonymous_GTID    last_committed=132    sequence_number=133


SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 99259

接下来的操作和上面主从操作一致。

原理:在初始化时mysql元数据的操作不记录gtid,启动后所有的操作记录gtid,且gtid事务号从1开始,从而避免1062错误。




[root@mysql57-71 logs]# mysqlbinlog -v --base64-output=decode-row ./mysql-bin.000002 

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#160127 10:49:41 server id 713306  end_log_pos 123 CRC32 0x35ee3e2d     Start: binlog v 4, server v 5.7.9-log created 160127 10:49:41 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

# at 123

#160127 10:49:41 server id 713306  end_log_pos 154 CRC32 0x21d84665     Previous-GTIDs

# [empty]

# at 154

#160127 10:50:38 server id 713306  end_log_pos 219 CRC32 0x2a912c89     GTID    last_committed=0    sequence_number=1

SET @@SESSION.GTID_NEXT= '7f2c189e-c49f-11e5-b3f9-0800279bd495:1'/*!*/;

# at 219

#160127 10:50:38 server id 713306  end_log_pos 398 CRC32 0x9bf46a0f     Query    thread_id=2    exec_time=0    error_code=0

SET TIMESTAMP=1453863038/*!*/;

SET @@session.pseudo_thread_id=2/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1436549120/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*CCDDC44F45A76B3B3C5E46A5B0D17E88ABC2B4BA'

/*!*/;

# at 398

#160127 10:51:13 server id 713306  end_log_pos 463 CRC32 0xaf535af9     GTID    last_committed=1    sequence_number=2

SET @@SESSION.GTID_NEXT= '7f2c189e-c49f-11e5-b3f9-0800279bd495:2'/*!*/;

# at 463

#160127 10:51:13 server id 713306  end_log_pos 701 CRC32 0x60a0eced     Query    thread_id=2    exec_time=0    error_code=0

SET TIMESTAMP=1453863073/*!*/;

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*809534247D21AC735802078139D8A854F45C31F3'

/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[可能存在的问题]gtid同pt-table-sync和pt-table-checksum配合的问题

huzilin 发表了文章 • 5 个评论 • 561 次浏览 • 2016-01-22 13:07 • 来自相关话题

第一个出现的问题:
环境版本
1)$pt-table-sync --version
pt-table-sync 2.2.16 版本已经是最新的了
2)$./bin/mysqld --version
./bin/mysqld  Ver 5.6.23 for linux-glibc2.5 on x86_64 (MySQL Community Server (GPL))
操作
$pt-table-sync --execute --replicate=test.checksums h='xxxxx',u='xxxxx',p='xxxxxx',P=3306
报错
When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1. [for Statement "CREATE TEMPORARY TABLE `xxxxx`.`__xxxxxx` ( `id` varchar(100) NOT NULL) ENGINE=MEMORY"] at line 4027 while doing xxxxxxx.xxxxxxxx on xxxxxx
表是innodb表,这个确认过了

第二个出现的问题:
环境版本
1)pt-table-sync版本不变
2)$ mysqld --version
mysqld  Ver 5.6.26 for linux-glibc2.5 on x86_64 (MySQL Community Server (GPL))
操作
$ pt-table-checksum -uxxxxxx -pxxxxx -hxxxxx -P3306 --replicate=test.checksums --no-check-binlog-format --recursion-method=processlist --no-check-replication-filters
输出
一直卡在
Waiting to check replicas for differences:   0% 00:00 remain
Waiting to check replicas for differences:   0% 00:00 remain
Waiting to check replicas for differences:   0% 00:00 remain
Waiting to check replicas for differences:   0% 00:00 remain
Waiting to check replicas for differences:   0% 00:00 remain

autocommit是on


  查看全部
第一个出现的问题:
  • 环境版本

1)$pt-table-sync --version
pt-table-sync 2.2.16 版本已经是最新的了
2)$./bin/mysqld --version
./bin/mysqld  Ver 5.6.23 for linux-glibc2.5 on x86_64 (MySQL Community Server (GPL))
  • 操作

$pt-table-sync --execute --replicate=test.checksums h='xxxxx',u='xxxxx',p='xxxxxx',P=3306
  • 报错

When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1. [for Statement "CREATE TEMPORARY TABLE `xxxxx`.`__xxxxxx` ( `id` varchar(100) NOT NULL) ENGINE=MEMORY"] at line 4027 while doing xxxxxxx.xxxxxxxx on xxxxxx
表是innodb表,这个确认过了

第二个出现的问题:
  • 环境版本

1)pt-table-sync版本不变
2)$ mysqld --version
mysqld  Ver 5.6.26 for linux-glibc2.5 on x86_64 (MySQL Community Server (GPL))
  • 操作

$ pt-table-checksum -uxxxxxx -pxxxxx -hxxxxx -P3306 --replicate=test.checksums --no-check-binlog-format --recursion-method=processlist --no-check-replication-filters
  • 输出

一直卡在
Waiting to check replicas for differences:   0% 00:00 remain
Waiting to check replicas for differences:   0% 00:00 remain
Waiting to check replicas for differences:   0% 00:00 remain
Waiting to check replicas for differences:   0% 00:00 remain
Waiting to check replicas for differences:   0% 00:00 remain

autocommit是on


 

mha启动异常

gaoquan 回复了问题 • 2 人关注 • 7 个回复 • 557 次浏览 • 2016-01-21 20:17 • 来自相关话题

MySQL的socket文件被删除了,在不停库的情况下怎么搞回来?

wangjiemin 回复了问题 • 4 人关注 • 3 个回复 • 790 次浏览 • 2016-01-19 15:54 • 来自相关话题

innobackupex全备份+binlog恢复,出现问题server_id相同,但是我已经修改了server_id.

wubx 回复了问题 • 3 人关注 • 2 个回复 • 673 次浏览 • 2016-01-19 12:07 • 来自相关话题

mysql sql批量上线脚本

earl86 发表了文章 • 2 个评论 • 522 次浏览 • 2016-01-18 15:07 • 来自相关话题

mysql sql批量上线脚本
有时候需要对几个实例的同一个数据库同时上线一份sql,
使用方法
python sqlonline.py -f xxx.sql -s dbserver.list -d databasename
需要更新dbserver.list(一行一个数据库ip地址)文件 跟 databasename(数据库名字)及脚本里的user='dba'参数
 
脚本如下:
 #!/usr/bin/python
#coding=utf-8
#Author:earl86
#version 1.0
#how to use:python sqlonline.py -f xxx.sql -s dbserver.list -d databasename

'''
cat dbserver.list
192.168.1.100
192.168.1.100
'''
'''
cat xxx.sql
drop table if exists test;
CREATE TABLE test (
  a int(10) NOT NULL COMMENT 'ID',
  b int(10) NOT NULL DEFAULT '0' COMMENT 'xxx',
  c int(10) NOT NULL DEFAULT '0' COMMENT 'xxx',
  d int(10) NOT NULL DEFAULT '0' COMMENT 'xxx',
  e int(11) NOT NULL DEFAULT '0',
  f int(11) NOT NULL DEFAULT '0',
  g int(10) NOT NULL DEFAULT '0',
  h int(10) NOT NULL DEFAULT '0',
  i tinyint(4) NOT NULL DEFAULT '0' COMMENT '1:测试1; 0:测试2',
  PRIMARY KEY (a),
  KEY idx_b (b),
  KEY idx_c (c),
  KEY idx_d (d),
  KEY idx_e (e)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into test values(1,2,3,4,5,6,7,8,9);
insert into test values(2,2,3,4,5,6,7,8,9);
insert into test values(3,2,3,4,5,6,7,8,9);
drop index idx_e on test;
create index idx_h on test(h);
'''

import os
import string
import sys
import codecs
import MySQLdb
import argparse
import getpass
import itertools
from multiprocessing import Pool, freeze_support

parser = argparse.ArgumentParser()
parser.add_argument("-f", action="store", dest='sqlfile', help="input the sql file dir and file name", required=True)
parser.add_argument("-s", action="store", dest='dbserverlistfile', help="input the serverlist file dir and file name", required=True)
parser.add_argument("-d", action="store", dest='databasename', help="input the database name", required=True)
args = parser.parse_args()


reload(sys)
sys.setdefaultencoding('utf8')


def readserverlist():
    dbserverlist =
    with open(args.dbserverlistfile, 'r') as f:
        f = f.readlines()
        for line in f:
            dbserverlist.append(line.strip('\n'))
    return dbserverlist


def readallsql():
    with open(args.sqlfile, 'r') as f:
        sqlall = f.read()
    f.closed
    return sqlall
    
def readlinesql(dbserverlist):
    sql = ''
    with open(args.sqlfile, 'r') as f:
        f = f.readlines()
        for line in f:
            sql = sql + line.strip('\n') + ' '
            if line.strip('\n').endswith(';'):
                 print '++++++++++++execsql++++++++++++' + sql
                 try:
                     pool = Pool()
                     pool.map(func_start,itertools.izip(dbserverlist, itertools.repeat(sql)))
                     pool.close()
                     pool.join()
                 except Exception, e:
                     print e
                 sql =''


def execsql(dbserver,sql):
    try:
        conn = MySQLdb.connect(host=dbserver, port=3306, user='dba', passwd=password, db=dbname, charset="utf8")
    except Exception, e:
        print "error++++++" + dbserver
        print e
        os._exit()
    try:
          cursor = conn.cursor()
        cursor.execute(sql)
        conn.commit()
        print "succeess!-----" + dbserver
    except Exception, e:
          conn.rollback()
        print "sqlerror!=====" + dbserver
        print e
    cursor.close()
    conn.close()

def func_start(dbserver_sql):
    return execsql(*dbserver_sql)


if __name__ == '__main__':
    freeze_support()
    print '==============================Check DBName========================================='
    dbname = args.databasename
    print dbname
    content = raw_input("check dbname input(y or n):")
    if (content != "y"):
        sys.exit()

    print '==============================Check DBServer======================================='
    dbserverlist = readserverlist()
    print dbserverlist
    content = raw_input("check dbserver input(y or n):")
    if (content != "y"):
        sys.exit()

    print '==============================Check Sql============================================'
    sqlall = ''
    sqlall = readallsql()
    print sqlall
    content = raw_input("check sql input(y or n):")
    if (content != "y"):
        sys.exit()

    print '===============================Input Password======================================='
    password = getpass.getpass('Enter password: ')

    readlinesql(dbserverlist) 查看全部
mysql sql批量上线脚本
有时候需要对几个实例的同一个数据库同时上线一份sql,
使用方法
python sqlonline.py -f xxx.sql -s dbserver.list -d databasename
需要更新dbserver.list(一行一个数据库ip地址)文件 跟 databasename(数据库名字)及脚本里的user='dba'参数
 
脚本如下:
 #!/usr/bin/python
#coding=utf-8
#Author:earl86
#version 1.0
#how to use:python sqlonline.py -f xxx.sql -s dbserver.list -d databasename

'''
cat dbserver.list
192.168.1.100
192.168.1.100
'''
'''
cat xxx.sql
drop table if exists test;
CREATE TABLE test (
  a int(10) NOT NULL COMMENT 'ID',
  b int(10) NOT NULL DEFAULT '0' COMMENT 'xxx',
  c int(10) NOT NULL DEFAULT '0' COMMENT 'xxx',
  d int(10) NOT NULL DEFAULT '0' COMMENT 'xxx',
  e int(11) NOT NULL DEFAULT '0',
  f int(11) NOT NULL DEFAULT '0',
  g int(10) NOT NULL DEFAULT '0',
  h int(10) NOT NULL DEFAULT '0',
  i tinyint(4) NOT NULL DEFAULT '0' COMMENT '1:测试1; 0:测试2',
  PRIMARY KEY (a),
  KEY idx_b (b),
  KEY idx_c (c),
  KEY idx_d (d),
  KEY idx_e (e)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into test values(1,2,3,4,5,6,7,8,9);
insert into test values(2,2,3,4,5,6,7,8,9);
insert into test values(3,2,3,4,5,6,7,8,9);
drop index idx_e on test;
create index idx_h on test(h);
'''

import os
import string
import sys
import codecs
import MySQLdb
import argparse
import getpass
import itertools
from multiprocessing import Pool, freeze_support

parser = argparse.ArgumentParser()
parser.add_argument("-f", action="store", dest='sqlfile', help="input the sql file dir and file name", required=True)
parser.add_argument("-s", action="store", dest='dbserverlistfile', help="input the serverlist file dir and file name", required=True)
parser.add_argument("-d", action="store", dest='databasename', help="input the database name", required=True)
args = parser.parse_args()


reload(sys)
sys.setdefaultencoding('utf8')


def readserverlist():
    dbserverlist =
    with open(args.dbserverlistfile, 'r') as f:
        f = f.readlines()
        for line in f:
            dbserverlist.append(line.strip('\n'))
    return dbserverlist


def readallsql():
    with open(args.sqlfile, 'r') as f:
        sqlall = f.read()
    f.closed
    return sqlall
    
def readlinesql(dbserverlist):
    sql = ''
    with open(args.sqlfile, 'r') as f:
        f = f.readlines()
        for line in f:
            sql = sql + line.strip('\n') + ' '
            if line.strip('\n').endswith(';'):
                 print '++++++++++++execsql++++++++++++' + sql
                 try:
                     pool = Pool()
                     pool.map(func_start,itertools.izip(dbserverlist, itertools.repeat(sql)))
                     pool.close()
                     pool.join()
                 except Exception, e:
                     print e
                 sql =''


def execsql(dbserver,sql):
    try:
        conn = MySQLdb.connect(host=dbserver, port=3306, user='dba', passwd=password, db=dbname, charset="utf8")
    except Exception, e:
        print "error++++++" + dbserver
        print e
        os._exit()
    try:
          cursor = conn.cursor()
        cursor.execute(sql)
        conn.commit()
        print "succeess!-----" + dbserver
    except Exception, e:
          conn.rollback()
        print "sqlerror!=====" + dbserver
        print e
    cursor.close()
    conn.close()

def func_start(dbserver_sql):
    return execsql(*dbserver_sql)


if __name__ == '__main__':
    freeze_support()
    print '==============================Check DBName========================================='
    dbname = args.databasename
    print dbname
    content = raw_input("check dbname input(y or n):")
    if (content != "y"):
        sys.exit()

    print '==============================Check DBServer======================================='
    dbserverlist = readserverlist()
    print dbserverlist
    content = raw_input("check dbserver input(y or n):")
    if (content != "y"):
        sys.exit()

    print '==============================Check Sql============================================'
    sqlall = ''
    sqlall = readallsql()
    print sqlall
    content = raw_input("check sql input(y or n):")
    if (content != "y"):
        sys.exit()

    print '===============================Input Password======================================='
    password = getpass.getpass('Enter password: ')

    readlinesql(dbserverlist)