跨版本mysqldump恢复报错Errno1449

已经有一套主从mysql,新增两个slave
主库Server version: 5.6.22-log MySQL Community Server (GPL)
旧从库Server version: 5.6.28-log MySQL Community Server (GPL)

新增SLAVE 1: Server version: 5.6.22-log MySQL Community Server (GPL)
新增SLAVE 2: Server version: 5.7.10-log MySQL Community Server (GPL)
重新初始化新的两个slave后,从就从库的mysqldump文件导入恢复,因为增加了–dump-slave参数,可以看到主库的位置。
CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.001196′, MASTER_LOG_POS=71925475;

dump文件导入完成后,在新增SLAVE 1执行:
change master to master_host=’192.168.72.142′, master_user=’repl’,master_password=’password’,master_port=3306,MASTER_LOG_FILE=’mysql-bin.001196′, MASTER_LOG_POS=71925475,MASTER_CONNECT_RETRY=30;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 192.168.72.142
Master_User: repl
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.001201
Read_Master_Log_Pos: 821264848
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 792405
Relay_Master_Log_File: mysql-bin.001196
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: 1449
Last_Error: Error ‘The user specified as a definer (‘usr_pre’@’%’) does not exist’ on query. Default database: ‘pre’. Query: ‘SELECT `pre`.`_getAutoIncrement`(_utf8’taobaoSoHeader’ COLLATE ‘utf8_general_ci’)’
Skip_Counter: 0
Exec_Master_Log_Pos: 72717597
Relay_Log_Space: 6129100284
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: 1449
Last_SQL_Error: Error ‘The user specified as a definer (‘usr_pre’@’%’) does not exist’ on query. Default database: ‘pre’. Query: ‘SELECT `pre`.`_getAutoIncrement`(_utf8’taobaoSoHeader’ COLLATE ‘utf8_general_ci’)’
Replicate_Ignore_Server_Ids:
Master_Server_Id: 142
Master_UUID: 6552d58f-7323-11e5-bc52-24b6fdf64510
Master_Info_File: /mysqldata/mysql_data/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: 160217 09:44:22
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
用工具查看binlog日志内容,发现也出错了,网上查了一下,说是BUG
-bash-4.1$ mysqlbinlog –start-position=72717597 –stop-position=72717598 -d pre mysql-bin.001201
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160216 20:15:34 server id 142 end_log_pos 120 CRC32 0xd9c6730a Start: binlog v 4, server v 5.6.22-log created 160216 20:15:34
BINLOG ‘
5hLDVg+OAAAAdAAAAHgAAAAAAAQANS42LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQpz
xtk=
‘/*!*/;
ERROR: Error in Log_event::read_log_event(): ‘Sanity check failed’, data_len: 577005919, event_type: 111
ERROR: Could not read entry at offset 72717597: Error in log format or read error.
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
-bash-4.1$ mysqlbinlog –start-position=72717597 –stop-position=72717598 mysql-bin.001201
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160216 20:15:34 server id 142 end_log_pos 120 CRC32 0xd9c6730a Start: binlog v 4, server v 5.6.22-log created 160216 20:15:34
BINLOG ‘
5hLDVg+OAAAAdAAAAHgAAAAAAAQANS42LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQpz
xtk=
‘/*!*/;
ERROR: Error in Log_event::read_log_event(): ‘Sanity check failed’, data_len: 577005919, event_type: 111
ERROR: Could not read entry at offset 72717597: Error in log format or read error.
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

-bash-4.1$ mysqlbinlog –start-position=72717597 –offset=1 mysql-bin.001201
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160216 20:15:34 server id 142 end_log_pos 120 CRC32 0xd9c6730a Start: binlog v 4, server v 5.6.22-log created 160216 20:15:34
BINLOG ‘
5hLDVg+OAAAAdAAAAHgAAAAAAAQANS42LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQpz
xtk=
‘/*!*/;
ERROR: Error in Log_event::read_log_event(): ‘Sanity check failed’, data_len: 577005919, event_type: 111
ERROR: Could not read entry at offset 72717597: Error in log format or read error.
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
怀疑权限问题
主库5.6.22上执行
mysql> show grants for ‘usr_pre’@’%’;
+——————————————————————————————————–+
| Grants for usr_pre@% |
+——————————————————————————————————–+
| GRANT USAGE ON *.* TO ‘usr_pre’@’%’ IDENTIFIED BY PASSWORD ‘*EA8D6BC15C61128B3E5AB994B3EA12A9092114C5’ |
| GRANT ALL PRIVILEGES ON `pre`.* TO ‘usr_pre’@’%’ |
+——————————————————————————————————–+
2 rows in set (0.02 sec)

旧SLAVE 5.6.28 备份导出的从库
mysql> show grants for ‘usr_pre’@’%’;
+——————————————————————————————————–+
| Grants for usr_pre@% |
+——————————————————————————————————–+
| GRANT USAGE ON *.* TO ‘usr_pre’@’%’ IDENTIFIED BY PASSWORD ‘*EA8D6BC15C61128B3E5AB994B3EA12A9092114C5’ |
| GRANT ALL PRIVILEGES ON `pre`.* TO ‘usr_pre’@’%’ |
+——————————————————————————————————–+
2 rows in set (0.01 sec)

新SLAVE 1是5.6.22,执行报错
mysql> show grants for ‘usr_pre’@’%’;
ERROR 1141 (42000): There is no such grant defined for user ‘usr_pre’ on host ‘%’

在新SLAVE 1执行
mysql> GRANT USAGE ON *.* TO ‘usr_pre’@’%’ IDENTIFIED BY PASSWORD ‘*EA8D6BC15C61128B3E5AB994B3EA12A9092114C5’ ;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `pre`.* TO ‘usr_pre’@’%’;
ERROR 1133 (42000): Can’t find any matching row in the user table
flush privileges;
show grants for ‘usr_pre’@’%’;
发现已经恢复正常

开启slave后,可以正常复制。

———————–
处理新增SLAVE 2:跨一个大版本,5.7.10
执行下面语句时报错:
mysql> change master to master_host=’192.168.72.142′, master_user=’repl2′,master_password=’password’,master_port=3306,MASTER_LOG_FILE=’mysql-bin.001196′, MASTER_LOG_POS=71925475,MASTER_CONNECT_RETRY=30;

ERROR 1805 (HY000): Column count of mysql.slave_master_info is wrong. Expected 25, found 23. The table is probably corrupted

处理方法:
[mysql@lt-mysql02 mysql_backup]$ mysql_upgrade -s -uroot -p
Enter password:
The –upgrade-system-tables option was used, databases won’t be touched.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
The sys schema is already up to date (version 1.5.0).
Found 0 sys functions, but expected 21. Re-installing the sys schema.
Upgrading the sys schema.
Upgrade process completed successfully.
Checking if update is needed.
重复执行上面语句,恢复正常
mysql_upgrade是一个检查和升级表的命令,用于检查当前表是否和mysql server版本兼容,例如从低版本的mysqldump导入了高版本的情况。也可以用于升级系统表。
执行该命令的时候,如果该命令发现有表的兼容性有问题,那么会做表升级,
如果发现表有问题,会做表修复,如果该命令无法修复,则需要用重建的方式手工修复表或索引。
推荐每次升级后都执行mysql_upgrade

mysql_upgrade参数-s的意思是只升级系统表,不修复数据表
–upgrade-system-tables, -s
Upgrade only the system tables, do not upgrade data.

mysql的GTID复制和多源复制

配置基于GTID的复制
——————————————–
在参数文件/etc/my.cnf增加下面内容:
主库
master_info_repository=TABLE
relay_log_info_repository=TABLE
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-host=10.45.10.209
report-port=3306
server_id = 2091
从库除了上面的参数外,还要增加
relay-log = relay-log
relay-log-index = relay-log.index
配置GTID的slave
GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’ IDENTIFIED BY ‘repl123′;
flush privileges;
change master to master_host=’10.45.10.209′, master_user=’repl’,master_password=’repl123′,master_auto_position=1;

GTID复制的好处:
对运维人员来说应该是一件大喜的事情,在主从切换后,在传统的方式里,你需要找到binlog和POS点,然后change master to指向,
而不是很有经验的运维,往往会将其找错,造成主从同步复制报错,在mysql5.6里,你无须再知道binlog和POS点,
你只需要知道master的IP、端口,账号密码即可,因为同步复制是自动的,mysql通过内部机制GTID自动找点同步。

—————————————-
配置多源复制:
即多个主,复制到1个从库,所有db汇总到一个slave的实例
注意:
多源数据库不能有同名库,否则会导致多源复制失败
例如,在220的slave 3307实例上面配置多源复制,汇集3个主的数据
CHANGE MASTER TO MASTER_HOST=’10.45.10.209′, MASTER_USER=’repl’, MASTER_PORT=3307, MASTER_PASSWORD=’repl123′,MASTER_AUTO_POSITION=1 FOR CHANNEL ‘master209-2′;
CHANGE MASTER TO MASTER_HOST=’10.45.10.218′, MASTER_USER=’repl’, MASTER_PORT=3307, MASTER_PASSWORD=’repl123′,MASTER_AUTO_POSITION=1 FOR CHANNEL ‘master218-2′;
CHANGE MASTER TO MASTER_HOST=’10.45.10.219′, MASTER_USER=’repl’, MASTER_PORT=3307, MASTER_PASSWORD=’repl123′,MASTER_AUTO_POSITION=1 FOR CHANNEL ‘master219-2’;

常用管理命令:
mysql> select * from mysql.slave_master_info;

mysql> START SLAVE io_thread FOR CHANNEL ‘master209-2’;
mysql> START SLAVE FOR CHANNEL ‘master209-2’;

mysql> show SLAVE status FOR CHANNEL ‘master209-2’\G
多源复制的好处:
对于备份目的的主备架构,多个主可以备份到一台物理备机上,节约资源

oracle的约束隐式创建索引和先索引后约束的区别

两种情况:
1.对于创建约束时隐式创建的索引,在做删除操作的时候: 9i~11g都会连带删除该索引

2.对于先创建索引,再创建约束(使用到此索引)这种情况:
9i版本:需要区分索引是否唯一:
如果索引是唯一的,则删除约束的时候,会连带删除索引;如果非唯一的,则不会删除索引。
10g以后版本,包括11g:无论索引是否唯一,都只是删除约束,索引不会删除。

参考metalink文档:309821.1

实验验证下
$ ss

SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 1 18:29:31 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 889389056 bytes
Fixed Size 2233480 bytes
Variable Size 830475128 bytes
Database Buffers 50331648 bytes
Redo Buffers 6348800 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> conn hr/hr
Connected.

先创建的索引,无论是否是unique索引,都不会随约束删除而被删除
SQL> create table test(a number );

Table created.

SQL> create index ind on test ( a );

Index created.

SQL> alter table test add constraint c1_pk primary key(a) using index;

Table altered.

SQL> select index_name from user_indexes where table_name=’TEST’;

INDEX_NAME
——————————
IND

SQL> alter table test drop constraint c1_pk;

Table altered.

SQL> select index_name from user_indexes where table_name=’TEST’;

INDEX_NAME
——————————
IND

SQL> drop index IND ;

Index dropped.

 
SQL> create unique index ind2 on test ( a );

Index created.

SQL> alter table test add constraint c2_pk primary key(a) using index;

Table altered.

SQL> alter table test drop constraint c2_pk;

Table altered.

SQL> select index_name from user_indexes where table_name=’TEST’;

INDEX_NAME
——————————
IND2

 

清理一下环境,删除索引,然后直接建约束,隐式创建索引,索引会因为约束被删除,而同时被删除
SQL> drop index ind2 ;

Index dropped.

SQL>
SQL>
SQL> alter table test add constraint c2_pk primary key(a) using index;

Table altered.

SQL> select index_name from user_indexes where table_name=’TEST’;

INDEX_NAME
——————————
C2_PK

SQL> alter table test drop constraint c2_pk;

Table altered.

SQL> select index_name from user_indexes where table_name=’TEST’;

no rows selected

EXADATA智能扫描

提要:
查询特定的要求:
智能扫描只可用于完整的表或索引扫描。
智能扫描只能用于直接路径读取:
直接路径读取会自动用于并行查询。
直接路径读取可以用于串行查询。
默认情况下不使用它们进行小型表的串行扫描。
使用 _serial_direct_read=TRUE 可强制执行直接路径读取。

为了查询可以获得Exadata卸载能力的优势,优化器必须决定使用全表扫描或者快速全索引扫描来执行语句,这里的用词比较笼统,一般来说,这两个词对应的是执行计划中的TABLE ACCESS FULL和INDEX FAST FULL SCAN。在Exadata中,这些类似的操作的命名做了些许更改以表明访问的是Exadata存储。新的操作名称是TABLE ACCESS STORAGE FULL和INDEX STORAGE FAST FULL SCAN。请注意,也有一些细微的变化,比如 MAT_VIEW ACCESS STORAGE FULL事件也表示可以使用智能扫描。不过你应该知道,事实上就算执行计划里面显示 TABLE ACCESS STORAGE FULL操作,也并不意味着查询就一定执行了智能扫描,它仅仅意味着前提已经满足。我们将在本章稍后探讨如何确认一个语句是否确实通过智能扫描实现了卸载操作。

直接路径读是什么意思?下面两段话可以解释
智能扫描除了必须是全扫描之外,还需要读取操作是通过Oracle直接路径读取机制来执行的。直接路径读取已经存在很长一段时间了,传统上,这种读取机制是被服务于并行查询的从属进程(Slave Process)使用的。因为并行查询最初预计将用于访问非常大量的数据(通常大到无法全部放入Oracle缓冲区),所以决定并行从属进程直接读取数据,然后放入自己的内存中(也被称为程序全局区或者PGA)。直接路径读取机制完全跳过了标准的将数据库放入缓冲区这样的Oracle缓存机制,这对海量数据是非常有效的,因为它消除了那些额外的没有帮助的工作(缓存全表扫描获取到的却又可能不会被再次用到的数据),让这些块不至于将其他的数据块刷新出缓冲区。像我们之前提到的,kcfis(Kernel File Intelligent Storage)函数是被kcbldrget( Kernel Block Direct Read GET)函数调用的,所以,智能扫描只有在使用直接路径读取机制的时候才会执行。

除了并行从属进程,只要条件允许,直接路径读取也可能用在非并行SQL语句中。有一个隐含参数_SERIAL_DIRECT_READ可以控制此功能。当此参数设置为默认值AUTO时,Oracle自动判断是否要为非并行扫描使用直接路径读取。计算基于几个因素,包括对象大小、Buffer Cache大小,以及在Buffer Cache中已存在了多少该对象的数据块。另外,还有一个隐含参数(_SMALL_TABLE_THRESHOLD)定义了如果要使用串行直接路径读取,那么表至少要为多大。对于非串行扫描决定是否要使用直接路径读取机制的算法并未公开,虽然串行直接路径读取的功能早已存在,但是只是在最近才成为比较普遍的现象。Oracle数据库11gR2在计算是否对于非并行扫描使用直接路径读取上做了一些修改,新修改的算法使Oracle 11gR2比以前的数据库版本要更频繁地采用直接路径读取。这也许是因为有了Exadata智能扫描,因此希望尽可能地触发直接路径读取,但是这样的算法在非Exadata平台上可能略显激进。

备注 My Oracle Support文档:793845.1中包含如下表述。
在11g中,关于在串行表扫描中是使用直接路径读取还是使用缓存读取,我们做了探索性的改动。在10g中,对于大表的串行扫描默认是通过缓存的,在11g中,决定是直接读取还是通过缓存读取,要基于表大小、缓冲区大小和其他多种统计信息。因为避免了闩锁(Latche),因此直接路径读取比离散读(Scattered Read)更快,对其他进程影响更小。
完全索引扫描和快速完全索引扫描的区别:

完全索引扫描: 这个性能感觉是最差的,只能单块读取,而且还要通过表去获得数据,但是因为他是按索引的顺序读取的,所以不再需要排序. 比如 select * from table order by name, 如果name 上有索引,很显然这个时候一般有2个计划, 第一个,全表扫描,然后sort, 还有一个情况,完全索引扫描就OK. 因为此时不需要排序了.
索引全扫描是根据叶节点链来进行的。进行索引全扫描首先要从根开始,找到叶节点链上的第一个数据块,然后沿着叶节点链进行扫描,由于叶节点链是根据索引键值排序的,因此这样扫描出来的数据本身就是排序的,数据读出后不需要再次排序。这种扫描方式和索引快速全扫描相比,首先要找到索引的根,然后通过枝节点找到第一个叶节点,然后再顺着叶节点链扫描整个索引。索引全扫描的IO成本比索引快速全扫描要大很多,读取根节点和叶节点的成本相对不大,不过由于顺着叶节点链扫描整个索引的时候无法使用多块读,而只能使用单块读,因此这种扫描方式的IO开销要远大于索引快速全扫描。这种索引扫描,我们如果对会话进行跟踪,会发现大量的db file sequential read等待。

快速完全索引扫描:
属先这个扫描是不用通过表得到数据的.这个是重点. 那么不通过表得到数据,很明显只能通过索引得到数据了. 那就更明显了,那你要拿的字段必须在索引中,否者如何不通过表的到数据?通常比如:select name from table; 如果name上有索引,有可能走FFS.但是如果是select name,id from table,假如id没有索引,那么不管怎么样,都是不可能走FFS的.对索引的扫描可以根据该索引的extent来进行,采用多块读的方式进行。因此在这类操作中,我们可以看到会话会大量的出现db file scattered read等待。
后者是我们希望在EXADATA上出现的索引扫描方式

impdp报错ORA-39083 ORA-02304 Object type TYPE failed to create

impdp报错ORA-39083 ORA-02304 Object type TYPE failed to create

环境
Red Hat Enterprise Linux Server release 5.8 (Tikanga)
ORACLE Release 11.2.0.3.0 Production

我用expdp,impdp复制一个shema,在impdp导入的时候报错
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE “GENIDCDEV2”.”ARRAY_TYPE2″ OID ‘0367306C82464BF1E0534E092D0A4AB5′ is array(48) of varchar2(255)

查看官方文档的impdp章节,搜索OID,发现了如下有用的内容
查看impdp help=y 可以使用一个参数:transform

TRANSFORM
Enables you to alter object creation DDL for objects being imported.

OID – If the value is specified as n, the assignment of the exported OID during the
creation of object tables and types is inhibited. Instead, a new OID is assigned.
This can be useful for cloning schemas, but does not affect referenced objects. The
default value is y.

这个参数默认是Y,如果改成N,那么导入的时候会把各个对象重新赋予新的OID。
transform=oid:n
这样,再用impdp导入的时候就不会出现
ORA-39083,ORA-02304
注意:exp/imp不支持oid转换,又是一个使用数据泵的优势。

因此我想到了两种方法可以解决问题
1.用下面impdp语句重新导入
impdp system/’*****’ directory=dir_lisx REMAP_SCHEMA=WMSUAT:GENIDCDEV2 dumpfile=wmsuat20150304.dump include=TYPE transform=oid:n logfile=impdp_wmsuat20150304_2.log

Import: Release 11.2.0.3.0 – Production on Wed Mar 4 14:55:40 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_FULL_01″: system/******** directory=dir_lisx REMAP_SCHEMA=WMSUAT:GENIDCDEV2 dumpfile=wmsuat20150304.dump include=TYPE transform=oid:n logfile=impdp_wmsuat20150304_2.log
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Job “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully completed at 14:55:45
2.手工重建刚才导入失败的type
核查type的ddl语句
SELECT dbms_metadata.get_ddl(‘TYPE’,’ARRAY_TYPE2′,’WMSUAT’) from dual ;

CREATE OR REPLACE TYPE “WMSUAT”.”ARRAY_TYPE2″ is array(48) of varchar2(255) ;

记得重新编译一下新clone的schema
EXEC DBMS_UTILITY.compile_schema(schema => ‘GENIDCDEV2’);

mysql5.6配置semi_sync

测试环境:
Red Hat Enterprise Linux Server release 6.3 (Santiago)
Server version: 5.6.22-log MySQL Community Server (GPL)

mysql的replication协议是异步的,虽然异步效率、性能很好,但是却无法保证主从数据一致性,
如果master crash,已经commit的事务不会被传送到任何的slave上,
从mysql5.5之后,mysql为了保证主从库数据一致性,引进了semi-sync功能,
semi-sync意思是MASTER只需要接收到其中一台SLAVE的返回信息,就会commit;否则需等待直至切换成异步再提交。

优点:
当事务返回客户端成功后,则日志一定在至少两台主机上存在。
MySQL的Semi-sync适合小事务,且两台主机的延迟又较小,则Semi-sync可以实现在性能很小损失的情况下的零数据丢失。

缺点:
完成单个事务增加了额外的等待延迟,延迟的大小取决于网络的好坏。

配置方法:
在主库安装semisync_master插件:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’; //linux
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.dll’; //windows

在备库安装semisync_slave插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’; //linux
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;//windows
主库上,新增如下参数:
$vi /etc/my.cnf

[mysqld]

rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000

备库上新增:

$vi /etc/my.cnf

[mysqld]

rpl_semi_sync_slave_enabled=1

重启master和slave,semi_sync插件会自动加载

重启后,master显示如下:
mysql> show variables like ‘%rpl_semi%’;
+————————————+——-+
| Variable_name | Value |
+————————————+——-+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON | #表示即使没有SLAVE也会等待过期时间结束,是默认值
+————————————+——-+
4 rows in set (0.00 sec)

mysql> show status like ‘%semi%’;
+——————————————–+———-+
| Variable_name | Value |
+——————————————–+———-+
| Rpl_semi_sync_master_clients | 3 | #有多少个Semi-sync的备库,我配置了3个
| Rpl_semi_sync_master_net_avg_wait_time | 2525 | #事务提交后,等待备库响应的平均时间
| Rpl_semi_sync_master_net_wait_time | 45892342 | #总的网络等待时间
| Rpl_semi_sync_master_net_waits | 18174 | #等待网络响应的总次数
| Rpl_semi_sync_master_no_times | 0 | #一共有几次从Semi-sync跌回普通状态
| Rpl_semi_sync_master_no_tx | 0 | #备库未及时响应的事务数
| Rpl_semi_sync_master_status | ON | #主库上Semi-sync是否正常开启
| Rpl_semi_sync_master_timefunc_failures | 0 | #时间函数未正常工作的次数
| Rpl_semi_sync_master_tx_avg_wait_time | 1196 | #开启Semi-sync,事务返回需要等待的平均时间
| Rpl_semi_sync_master_tx_wait_time | 7918635 | #事务等待备库响应的总时间
| Rpl_semi_sync_master_tx_waits | 6620 | #事务等待备库响应的总次数
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 | #改变当前等待最小二进制日志的次数
| Rpl_semi_sync_master_wait_sessions | 0 | #当前有几个线程在等备库响应
| Rpl_semi_sync_master_yes_tx | 6549 | #Semi-sync模式下,成功的事务数
+——————————————–+———-+
14 rows in set (0.00 sec)

slave上显示如下:
mysql> show variables like ‘%rpl_semi%’;
+———————————+——-+
| Variable_name | Value |
+———————————+——-+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+———————————+——-+
2 rows in set (0.01 sec)

mysql> show status like ‘%semi%’;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| Rpl_semi_sync_slave_status | ON |
+—————————-+——-+
1 row in set (0.00 sec)

优化mysql slave的同步速度

测试环境:
Red Hat Enterprise Linux Server release 6.3 (Santiago)
Server version: 5.6.22-log MySQL Community Server (GPL)

我搭建了1主3从的环境,准备测试MHA架构,过程中发现,测试并发插入的时候,从库1可以跟上,从库2,3跟不上

如何判断是io thread慢还是 sql thread慢呢,有个方法,观察show slave status\G ,

判断3个参数(参数后面的值是默认空闲时候的正常值):
Slave_IO_State: Waiting for master to send event
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Seconds_Behind_Master: 0

1.sql thread慢的表现:
Seconds_Behind_Master越来越大
Slave_SQL_Running_State: Reading event from the relay log

2.io thread慢的表现:
Seconds_Behind_Master为0
Slave_SQL_Running_State: 显示正常值
Slave_IO_State:显示忙碌状态

而我观察到的值是
Slave_IO_State: Waiting for master to send event
Seconds_Behind_Master: 313
Slave_SQL_Running_State: Reading event from the relay log
因此推断是sql thread慢

为啥只有slave2,3慢,而slave1可以跟上呢,开始怀疑是参数配置的差异,比对/etc/my.cnf后发现,配置无差异
因此排除这个原因,后来用dstat观察,发现繁忙时候,slave的IO写速度上不去
slave1:
$ dstat
—-total-cpu-usage—- -dsk/total- -net/total- —paging– —system–
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
0 0 100 0 0 0|9308B 11k| 0 0 | 3B 3B| 63 63
3 4 54 40 0 0| 88k 10M| 45k 9438B| 0 0 |1857 2579
3 3 59 35 0 1| 80k 7552k| 40k 8486B| 0 0 |1675 2307
3 3 56 38 0 0| 72k 7824k| 42k 8816B| 0 0 |1727 2348
3 4 52 41 0 1| 96k 9688k| 49k 10k| 0 0 |2029 2874
3 4 54 39 0 0| 96k 8880k| 45k 9410B| 0 0 |1905 2674
3 3 53 40 0 1| 96k 9776k| 58k 10k| 0 0 |1935 2671
3 3 58 36 0 0| 64k 7848k| 40k 8420B| 0 0 |1724 2357
3 5 52 40 0 1| 96k 8936k| 49k 10k| 0 0 |1948 2680
3 4 51 42 0 1| 96k 9400k| 49k 10k| 0 0 |1988 2760
3 4 52 41 0 0| 88k 9752k| 49k 10k| 0 0 |2058 2868
4 4 51 41 0 1| 96k 9680k| 49k 9938B| 0 0 |1990 2750
3 3 59 35 0 0| 80k 7632k| 39k 8288B| 0 0 |1668 2275
3 4 52 42 0 1| 80k 8504k| 46k 9146B| 0 0 |1860 2523
3 4 51 42 0 0| 80k 8496k| 43k 8684B| 0 0 |1882 2516
2 3 65 30 0 0| 64k 5976k| 30k 6440B| 0 0 |1326 1802
3 4 53 40 0 1| 72k 8360k| 59k 10k| 0 0 |1859 2538
3 4 51 42 0 1| 96k 8840k| 53k 10k| 0 0 |1958 2648
2 4 51 43 0 0| 72k 7352k| 40k 7760B| 0 0 |1633 2219
3 4 51 42 0 1| 88k 7920k| 31k 6770B| 0 0 |1767 2373
3 3 54 40 0 0| 80k 8528k| 40k 8750B| 0 0 |1859 2549

slave2:
—-total-cpu-usage—- -dsk/total- -net/total- —paging– —system–
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
2 1 50 47 0 1|8192B 1168k| 55k 10k| 0 0 | 533 771
1 1 51 48 0 0|8192B 1048k| 33k 7046B| 0 0 | 427 622
1 1 51 48 0 0|8192B 1080k| 58k 9806B| 0 0 | 500 709
1 1 50 48 0 0| 0 1864k| 51k 8486B| 0 0 | 502 669
1 2 51 47 0 0|8192B 1120k| 42k 8156B| 0 0 | 496 674
1 1 51 47 0 0|8192B 1160k| 32k 6350B| 0 0 | 467 655
1 2 51 47 0 0| 0 1288k| 50k 10k| 0 0 | 563 797
1 1 51 47 0 0|8192B 1200k| 43k 8486B| 0 0 | 493 728
2 1 50 47 0 0|8192B 1024k| 45k 8816B| 0 0 | 481 659
1 1 50 48 0 0|8192B 1248k| 49k 9450B| 0 0 | 517 772
1 1 50 48 0 0| 0 1264k| 47k 9146B| 0 0 | 516 756
1 2 50 47 0 1|8192B 1144k| 50k 10k| 0 0 | 520 765
1 1 51 48 0 0|8192B 1200k| 51k 8156B| 0 0 | 484 716
1 2 50 48 0 0|8192B 968k| 50k 9278B| 0 0 | 470 684
1 1 50 48 0 0|8192B 1128k| 39k 7892B| 0 0 | 476 679
1 1 51 47 0 0| 0 1248k| 45k 9476B| 0 0 | 523 760
1 2 50 48 0 0|8192B 1448k| 41k 7826B| 0 0 | 552 805
1 1 50 48 0 0|8192B 1120k| 44k 8090B| 0 0 | 470 692

slave3:
—-total-cpu-usage—- -dsk/total- -net/total- —paging– —system–
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
1 1 50 49 0 0|8192B 1328k|1167B 170B| 0 0 | 385 515
1 1 51 48 0 0|8192B 1128k| 754B 170B| 0 0 | 325 449
1 1 50 49 0 0| 0 920k| 474B 314B| 0 0 | 279 381
0 1 50 49 0 0|8192B 664k|1633B 170B| 0 0 | 226 291
1 1 50 49 0 0|8192B 1200k|1250B 170B| 0 0 | 353 475
1 1 50 48 0 0| 0 1432k|1632B 170B| 0 0 | 402 551
1 1 51 48 0 0| 16k 1752k|1045B 170B| 0 0 | 487 664
1 1 50 48 0 0|8192B 1648k| 12k 170B| 0 0 | 461 636
1 1 51 48 0 0| 0 1272k| 886B 170B| 0 0 | 380 501
1 1 50 49 0 0|8192B 1000k|1023B 170B| 0 0 | 300 400
1 1 50 48 0 0|8192B 1096k| 747B 170B| 0 0 | 332 442
1 1 50 48 0 0|8192B 1448k|1003B 170B| 0 0 | 416 557
1 1 50 48 0 0| 0 1592k|1174B 170B| 0 0 | 450 614
1 1 51 48 0 0|8192B 1416k|1028B 170B| 0 0 | 404 552
0 1 50 49 0 0|8192B 1128k|1031B 170B| 0 0 | 331 447
1 1 51 48 0 0|8192B 1160k|1185B 170B| 0 0 | 340 458
1 1 50 49 0 0| 0 1120k| 633B 170B| 0 0 | 326 453
1 0 50 49 0 0|8192B 656k|8886B 170B| 0 0 | 221 288
1 1 50 49 0 0|8192B 1128k|1619B 170B| 0 0 | 335 451

slave1可以达到每秒9M的写入IO,而slave2,3只能达到每秒1M多,IO性能差很多,后来分析了下存储,发现是有很大差异的,也印证了我的推测

那么问题来了,要如何优化IO性能比较差的slave呢,其实很简单,修改两个参数
mysql> set global sync_binlog=20 ;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.00 sec)

innodb_flush_log_at_trx_commit
如果innodb_flush_log_at_trx_commit设置为0,log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行.该模式下,在事务提交的时候,不会主动触发写入磁盘的操作。
如果innodb_flush_log_at_trx_commit设置为1,每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去.
如果innodb_flush_log_at_trx_commit设置为2,每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。
注意:
由于进程调度策略问题,这个“每秒执行一次 flush(刷到磁盘)操作”并不是保证100%的“每秒”。

sync_binlog
sync_binlog 的默认值是0,像操作系统刷其他文件的机制一样,MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。
当sync_binlog =N (N>0) ,MySQL 在每写 N次 二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。
注意:
如果启用了autocommit,那么每一个语句statement就会有一次写操作;否则每个事务对应一个写操作。
而且mysql服务默认是autocommit打开的

修改参数后,slave2,3也一样可以跟上slave1的速度了
slave2,3:
—-total-cpu-usage—- -dsk/total- -net/total- —paging– —system–
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
3 2 94 2 0 0| 32k 80k| 49k 10k| 0 0 |1042 658
3 2 94 1 0 1| 32k 72k| 49k 10k| 0 0 |1258 964
2 2 95 2 0 0| 32k 72k| 44k 9146B| 0 0 |1126 882
2 1 95 2 0 0| 32k 72k| 41k 8486B| 0 0 | 959 659
2 2 96 1 0 0| 32k 72k| 47k 9476B| 0 0 |1153 841
2 2 95 2 0 0| 24k 72k| 39k 8090B| 0 0 | 866 504
2 2 96 1 0 0| 24k 72k| 42k 7562B| 0 0 | 908 663
2 1 95 2 0 0| 40k 72k| 52k 10k| 0 0 |1084 685
3 1 94 2 0 1| 40k 80k| 54k 11k| 0 0 |1204 873
2 2 96 1 0 0| 16k 32k| 30k 6044B| 0 0 | 846 802
2 1 97 1 0 0| 24k 32k| 35k 7760B| 0 0 |1059 888
2 1 95 3 0 0| 32k 856k| 44k 9278B| 0 0 | 943 551
2 1 94 3 0 0| 32k 104k| 42k 8618B| 0 0 | 986 704
2 1 96 1 0 0| 24k 72k| 34k 7034B| 0 0 | 863 682
2 2 95 2 0 0| 32k 64k| 45k 8684B| 0 0 |1052 750
2 2 90 7 0 0| 24k 416k| 38k 7166B| 0 0 | 906 722
3 2 93 2 0 1| 32k 80k| 57k 10k| 0 0 |1069 829
3 2 94 1 0 0| 32k 72k| 42k 8486B| 0 0 |1076 942
2 1 96 1 0 0| 24k 72k| 37k 7496B| 0 0 | 859 575
2 2 94 2 0 1| 32k 64k| 43k 8684B| 0 0 |1138 1011
3 2 94 1 0 0| 32k 72k| 42k 9014B| 0 0 |1099 782
2 3 94 2 0 0| 32k 72k| 50k 10k| 0 0 |1332 1359
2 2 95 2 0 0| 24k 72k| 34k 6902B| 0 0 | 921 799
2 2 94 2 0 0| 40k 72k| 55k 11k| 0 0 |1318 1016
1 2 96 2 0 0| 32k 80k| 41k 8882B| 0 0 |1020 719
而且我观察到slave2,3的写入数量减少了两个数量级,从1M多下降到70k

orzdba工具安装注意事项

orzdba是一个监控mysql性能的一个比较好用的perl脚本,是淘宝开源的小工具,下载地址http://code.taobao.org/p/orzdba/src/trunk/

配置过程中除了参照《orzdba工具使用说明》外,还有一些注意事项

 

我的安装环境是

Red Hat Enterprise Linux Server release 6.3 (Santiago)

5.6.22-log MySQL Community Server (GPL)

 

安装tcprstat前,先安装包

yum install glibc-static-2.12-1.80.el6.x86_64.rpm

 

参照

http://my.oschina.net/moooofly/blog/157063安装tcprstat

安装tcprstat过程中,除了前面的那个glibc-static外,还需要安装5个包

yum -y install automake

yum install bison

yum install flex

yum install patch

yum install make

配置tcprstat的权限

chown mysql:mysql -R tcprstat

ln -sf /mysqldata/soft/tcprstat/src/tcprstat /usr/bin/tcprstat

chown root:root /usr/bin/tcprstat

chmod u+s /usr/bin/tcprstat

ll /usr/bin/tcprstat

lrwxrwxrwx 1 root root 37 Feb 11 11:22 /usr/bin/tcprstat -> /mysqldata/soft/tcprstat/src/tcprstat

查看目录/mysqldata/soft/tcprstat/src

[root@open_mysql1 src]# ll

total 1996

-rw-r–r– 1 mysql mysql    3006 Feb 10 15:48 capture.c

-rw-r–r– 1 mysql mysql    1057 Feb 10 15:48 capture.h

-rw-r–r– 1 mysql mysql    4015 Feb 10 15:48 functions.c

-rw-r–r– 1 mysql mysql    1019 Feb 10 15:48 functions.h

-rw-r–r– 1 mysql mysql    4325 Feb 10 15:48 local-addresses.c

-rw-r–r– 1 mysql mysql    1065 Feb 10 15:48 local-addresses.h

-rw-r–r– 1 mysql mysql   41829 Feb 11 11:07 Makefile

-rw-r–r– 1 mysql mysql    1800 Feb 10 15:48 Makefile.am

-rw-r–r– 1 mysql mysql   47676 Feb 11 11:07 Makefile.in

-rw-r–r– 1 mysql mysql   10157 Feb 10 15:48 output.c

-rw-r–r– 1 mysql mysql    1315 Feb 10 15:48 output.h

-rw-r–r– 1 mysql mysql    3970 Feb 10 15:48 process-packet.c

-rw-r–r– 1 mysql mysql    1139 Feb 10 15:48 process-packet.h

-rw-r–r– 1 mysql mysql   10411 Feb 10 15:48 stats.c

-rw-r–r– 1 mysql mysql    1958 Feb 10 15:48 stats.h

-rw-r–r– 1 mysql mysql    7845 Feb 10 15:48 stats-hash.c

-rw-r–r– 1 mysql mysql    1480 Feb 10 15:48 stats-hash.h

-rwsr-xr-x 1 root  root   291086 Feb 11 11:08 tcprstat

-rw-r–r– 1 mysql mysql    5970 Feb 10 15:48 tcprstat.c

-rw-r–r– 1 mysql mysql   10720 Feb 11 11:07 tcprstat-capture.o

-rw-r–r– 1 mysql mysql   10272 Feb 11 11:07 tcprstat-functions.o

-rw-r–r– 1 mysql mysql    1339 Feb 10 15:48 tcprstat.h

-rw-r–r– 1 mysql mysql   13568 Feb 11 11:08 tcprstat-local-addresses.o

-rw-r–r– 1 mysql mysql   23312 Feb 11 11:08 tcprstat-output.o

-rw-r–r– 1 mysql mysql   16072 Feb 11 11:08 tcprstat-process-packet.o

-rwxr-xr-x 1 mysql mysql 1258608 Feb 11 11:08 tcprstat-static

-rw-r–r– 1 mysql mysql   10720 Feb 11 11:08 tcprstat_static-capture.o

-rw-r–r– 1 mysql mysql   10272 Feb 11 11:08 tcprstat_static-functions.o

-rw-r–r– 1 mysql mysql   13568 Feb 11 11:08 tcprstat_static-local-addresses.o

-rw-r–r– 1 mysql mysql   23312 Feb 11 11:08 tcprstat_static-output.o

-rw-r–r– 1 mysql mysql   16072 Feb 11 11:08 tcprstat_static-process-packet.o

-rw-r–r– 1 mysql mysql   16056 Feb 11 11:08 tcprstat_static-stats-hash.o

-rw-r–r– 1 mysql mysql   27232 Feb 11 11:08 tcprstat_static-stats.o

-rw-r–r– 1 mysql mysql   18576 Feb 11 11:08 tcprstat_static-tcprstat.o

-rw-r–r– 1 mysql mysql   16056 Feb 11 11:08 tcprstat-stats-hash.o

-rw-r–r– 1 mysql mysql   27232 Feb 11 11:08 tcprstat-stats.o

-rw-r–r– 1 mysql mysql   18576 Feb 11 11:07 tcprstat-tcprstat.o

普通的yum,rpm方式安装version模块会报错,用如下安装方式解决

yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

yum -y install perl-CPAN

yum install perl-Params-Validate

perl -MCPAN -e ‘install “Module::Build”‘

perl -MCPAN -e ‘install “File::Lockfile”‘

 

执行上面命令后,应该安装好了version, Class-Data-Inheritable, Module-Build, File::Lockfile等模块,可以用脚本查看是否安装成功

$ cat check_module.pl

#!/usr/bin/perl

use ExtUtils::Installed;

my $inst = ExtUtils::Installed->new();

print join “\n”,$inst->modules();

print “\n”;

对于orzdba脚本,在160行修改root密码,才能运行orzdba脚本,修改后样子如下:

my $MYSQL    = qq{mysql -s –skip-column-names -uroot -P$port -pLisx_new_123 };

改成这样后,会出现mysql5.6的明文密码告警:Warning: Using a password on the command line interface can be insecure.我是如下解决的

orzdba1

弄一个免密码的本地可以访问的管理员账户,用这个账户监控?

这个方案也不行,因为每一行都要回车确认

 

我的解决方案,在mysql的根目录新建隐藏文件,权限为400

-bash-4.1$ cat .my.cnf

[client]

password=lisx123

user=dbalisx

 

并且修改orzdba脚本的第160行处

my $MYSQL    = qq{mysql -s –skip-column-names -udbalisx -P$port };

orzdba2

好了,可以痛快的使用orzdba脚本了。

如何学习MySQL

转自高手的帖子:

1.坚持阅读官方手册,看MySQL书籍作用不会特别大;(挑选跟工作相关的内容优先阅读,例如InnoDB存储引擎,MySQL复制,查询优化)

2.阅读官方手册,同时对阅读的内容做对应的测试;

3.结合你现在的工作内容,多实战即可;

4.外部的BLOG ,也包过我的 http://mysqlops.com 只是作为参考即可,更多要培养自己的分析思考的模式;

备注:

国内人写的MySQL书籍都不要作为重点,包过我可能2014年出版一本关于MySQL的书籍,推荐大家只作为辅助的,可以上厕所的时候看看,坚持官方手册+测试+实战+思考总结为主的模式。

看来耐心看完官方文档是十分重要的!

pt-table-checksum、pt-table-sync核对主从库一致性

一.下载并安装工具
http://www.percona.com/downloads/percona-toolkit/
目前最新的版本是percona-toolkit_2.2.12.tar.gz
上传到服务器后,解压缩,并设置到环境变量
在mysql用户的环境变量文件增加路径
vi .bash_profile
export PATH=$PATH:/mysqldata/soft/percona-toolkit-2.2.12/bin

二.使用pt-table-checksum命令查找不一致的数据
主要关注的列是DIFFS,为0则一致,为1则不一致
-bash-4.1$ pt-table-checksum –nocheck-replication-filters –no-check-binlog-format h=localhost,u=root,p=passwd,P=3306
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
01-21T14:00:20 0 0 34 1 0 0.304 test.test_inv_log
01-21T14:00:20 0 0 9 1 0 0.304 test.test_inventory
01-21T14:00:21 0 0 2 1 0 0.061 test.test_lastexpressno
01-21T14:00:21 0 0 38 1 0 0.070 test.test_location
01-21T14:00:21 0 0 97 1 0 0.068 test.test_login_history
01-21T14:00:21 0 1 33 1 0 0.065 test.test_menu
01-21T14:00:21 0 0 11 1 0 0.061 test.test_pkd
01-21T14:00:21 0 0 1 1 0 0.061 test.test_promotion
01-21T14:00:21 0 0 1 1 0 0.064 test.test_promotion_condition
01-21T14:00:21 0 0 2 1 0 0.060 test.test_promotion_gift

实验1:在从库多插入记录,制造差异
上面标红的一行是我故意到从库手工插入的一条记录
为了制造不一致的数据,我停掉了双主复制的中的从库到主库的反向复制,关闭了只读参数,手工插入了一条记录。

pt-table-checksum还可以带很多参数,比较重要的用法如下:
-bash-4.1$ pt-table-checksum –nocheck-replication-filters –no-check-binlog-format [–replicate=rep_test.checksums –databases=rep_test –tables=test1] h=localhost,u=root,p=passwd,P=3306

中间的黑色部分,中括号里面的部分是可选的,
–nocheck-replication-filters :不检查复制过滤器,建议启用。后面可以用–databases来指定需要检查的数据库。
–no-check-binlog-format : 不检查复制的binlog模式,要是binlog模式是ROW,则会报错。
–replicate-check-only :只显示不同步的信息。
–replicate= :把checksum的信息写入到指定表中,建议直接写到被检查的数据库当中,如果不指定,默认会在主库新建一个database叫percona,检查结果存放在percona的checksums表中。
–databases= :指定需要被检查的数据库,多个则用逗号隔开。
–tables= :指定需要被检查的表,多个用逗号隔开

我们再次尝试一下,加2个好用的参数,replicate-check-only和database参数
-bash-4.1$ pt-table-checksum –nocheck-replication-filters –no-check-binlog-format –replicate-check-only –databases=test h=localhost,u=root,p=passwd,P=3306

使用这命令效果不好,不建议带replicate-check-only参数,因为输出的列不全
只使用—databases参数指定要核对的数据库比较好
-bash-4.1$ pt-table-checksum –nocheck-replication-filters –no-check-binlog-format –databases=test h=localhost,u=root,p=passwd,P=3306

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
01-21T14:34:30 0 0 34 1 0 0.303 test.test_inv_log
01-21T14:34:30 0 0 9 1 0 0.061 test.test_inventory
01-21T14:34:30 0 0 2 1 0 0.306 test.test_lastexpressno
01-21T14:34:30 0 0 38 1 0 0.322 test.test_location
01-21T14:34:31 0 0 97 1 0 0.307 test.test_login_history
01-21T14:34:31 0 1 33 1 0 0.305 test.test_menu
01-21T14:34:31 0 0 11 1 0 0.060 test.test_pkd
01-21T14:34:31 0 0 1 1 0 0.055 test.test_promotion
01-21T14:34:31 0 0 1 1 0 0.057 test.test_promotion_condition
01-21T14:34:31 0 0 2 1 0 0.068 test.test_promotion_gift
01-21T14:34:31 0 0 2 1 0 0.058 test.test_promotion_rule
01-21T14:34:31 0 0 33 1 0 0.056 test.test_role
01-21T14:34:31 0 0 95 1 0 0.059 test.test_search_tab
01-21T14:34:32 0 0 25 1 0 0.303 test.test_send_template

执行后,可以发现差异,但是核查结果表percona.checksums,却发现有BUG,结果表显示无差异,结果表主要关注两列this_crc和master_crc
mysql> show tables ;
+——————-+
| Tables_in_percona |
+——————-+
| checksums |
+——————-+
1 row in set (0.00 sec)
mysql> select db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums ;
+——-+—————————+——-+———-+———-+————+————+———————+
| db | tbl | chunk | this_crc | this_cnt | master_crc | master_cnt | ts |
+——-+—————————+——-+———-+———-+————+————+———————+
| test | test_inv_adjust_header | 1 | 2a7935e8 | 1 | 2a7935e8 | 1 | 2015-01-21 14:34:29 |
| test | test_inv_log | 1 | efc2d050 | 34 | efc2d050 | 34 | 2015-01-21 14:34:29 |
| test | test_lastexpressno | 1 | 96b2f90d | 2 | 96b2f90d | 2 | 2015-01-21 14:34:30 |
| test | test_location | 1 | d5582f63 | 38 | d5582f63 | 38 | 2015-01-21 14:34:30 |
| test | test_login_history | 1 | db189cf9 | 97 | db189cf9 | 97 | 2015-01-21 14:34:30 |
| test | test_menu | 1 | 6046676 | 33 | 6046676 | 33 | 2015-01-21 14:34:31 |
| test | test_pkd | 1 | ee959bc2 | 11 | ee959bc2 | 11 | 2015-01-21 14:34:31 |
| test | test_promotion | 1 | 2020c504 | 1 | 2020c504 | 1 | 2015-01-21 14:34:31 |
| test | test_promotion_condition | 1 | a71da967 | 1 | a71da967 | 1 | 2015-01-21 14:34:31 |
| test | test_promotion_gift | 1 | 5d6b31f | 2 | 5d6b31f | 2 | 2015-01-21 14:34:31 |
| test | test_promotion_rule | 1 | 76daf3aa | 2 | 76daf3aa | 2 | 2015-01-21 14:34:31 |

标红的部分显示,没有发现主从库的差异
因此,我推断核查差异的时候,以命令输出为准,检查结果表的数据不准确,后面的实验否定了这个结论

实验2:修改主库的一条数据,制造差异
停止从库的slave,在主库修改一条数据,和从库内容不一致
从库:
mysql> stop slave ;
Query OK, 0 rows affected (0.02 sec)

主库:
mysql> select * from test.test_menu where id=60 \G
*************************** 1. row ***************************
id: 60
createTime: 2015-01-21 13:58:18
updateTime: 2015-01-21 13:58:20
domain_id: -1
creator_id: NULL
updator_id: NULL
status_id: NULL
version: 0
menuName: MENU_TEST2
parent_id: 54
role_id: 57
sortIndex: 6
menuDescr: TEST2
remark: NULL
udf1: NULL
udf2: NULL
udf3: NULL
udf4: NULL
1 row in set (0.00 sec)

mysql> update test.test_menu set menuDescr=’TEST2222′ where id=60 ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> commit ;
Query OK, 0 rows affected (0.00 sec)

用pt-table-check命令核查,竟然没发现差异,难道是因为slave没有开启的缘故吗,但是一旦开启从库的话,同步了binlog,差异又没了,因此打算重新搭建slave,跳过几个transacation,试试看

从库执行
mysql> change master to master_host=’192.168.2.195′,master_user=’repl’,master_password=’slave123′,master_log_file=’mysql-bin.000006′,master_log_pos=692149 ;

mysql> start slave ;

配合后,主备复制正常,主备差异存在
主库:
mysql> select * from test.test_menu where id=60 \G
*************************** 1. row ***************************
id: 60
createTime: 2015-01-21 13:58:18
updateTime: 2015-01-21 13:58:20
domain_id: -1
creator_id: NULL
updator_id: NULL
status_id: NULL
version: 0
menuName: MENU_TEST2
parent_id: 54
role_id: 57
sortIndex: 6
menuDescr: TEST2222
remark: NULL
udf1: NULL
udf2: NULL
udf3: NULL
udf4: NULL
1 row in set (0.00 sec)

从库:
mysql> select * from test.test_menu where id=60 \G
*************************** 1. row ***************************
id: 60
createTime: 2015-01-21 13:58:18
updateTime: 2015-01-21 13:58:20
domain_id: -1
creator_id: NULL
updator_id: NULL
status_id: NULL
version: 0
menuName: MENU_TEST2
parent_id: 54
role_id: 57
sortIndex: 6
menuDescr: TEST2
remark: NULL
udf1: NULL
udf2: NULL
udf3: NULL
udf4: NULL
1 row in set (0.00 sec)
标红的就是主从库的差异

在主库再次执行pt-table-checksum命令
-bash-4.1$ pt-table-checksum –nocheck-replication-filters –no-check-binlog-format –databases=test h=localhost,u=root,p=passwd,P=3306
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
01-21T15:17:41 0 0 4440 4 0 0.283 test.test_address
01-21T15:17:42 0 0 340 1 0 0.304 test.test_allocation
01-21T15:17:42 0 0 2 1 0 0.059 test.test_allocationexp
01-21T15:17:42 0 0 10 1 0 0.055 test.test_asn_detail
01-21T15:17:42 0 0 9 1 0 0.051 test.test_asn_header
01-21T15:17:42 0 0 8 1 0 0.051 test.test_biz_inventory
01-21T15:17:42 0 0 0 1 0 0.300 test.test_combination_map
01-21T15:17:42 0 0 20 1 0 0.055 test.test_common_property
01-21T15:17:42 0 0 103 1 0 0.055 test.test_doc_log
01-21T15:17:42 0 0 0 1 0 0.047 test.test_etrackno_used
01-21T15:17:42 0 0 20 1 0 0.051 test.test_express_company
01-21T15:17:42 0 0 2 1 0 0.051 test.test_inter_promotion_rule
01-21T15:17:42 0 0 1 1 0 0.062 test.test_inv_adjust_detail
01-21T15:17:43 0 0 1 1 0 0.056 test.test_inv_adjust_header
01-21T15:17:43 0 0 34 1 0 0.058 test.test_inv_log
01-21T15:17:43 0 0 9 1 0 0.054 test.test_inventory
01-21T15:17:43 0 0 2 1 0 0.058 test.test_lastexpressno
01-21T15:17:43 0 0 38 1 0 0.056 test.test_location
01-21T15:17:43 0 0 97 1 0 0.058 test.test_login_history
01-21T15:17:43 0 1 34 1 0 0.067 test.test_menu
01-21T15:17:43 0 0 11 1 0 0.161 test.test_pkd
01-21T15:17:44 0 0 1 1 0 0.347 test.test_promotion
01-21T15:17:44 0 0 1 1 0 0.409 test.test_promotion_condition
01-21T15:17:44 0 0 2 1 0 0.334 test.test_promotion_gift
01-21T15:17:45 0 0 2 1 0 0.163 test.test_promotion_rule
01-21T15:17:45 0 0 33 1 0 0.059 test.test_role
01-21T15:17:45 0 0 95 1 0 0.055 test.test_search_tab
01-21T15:17:45 0 0 25 1 0 0.309 test.test_send_template
01-21T15:17:45 0 0 4 1 0 0.057 test.test_sequence
01-21T15:17:45 0 0 30 1 0 0.300 test.test_sku
01-21T15:17:46 0 0 21 1 0 0.298 test.test_so_detail
01-21T15:17:46 0 0 15 1 0 0.069 test.test_so_header
01-21T15:17:46 0 0 2 1 0 0.056 test.test_soh_ee_relation
01-21T15:17:46 0 0 14 1 0 0.308 test.test_soh_eo_relation
01-21T15:17:46 0 0 12 1 0 0.060 test.test_store
01-21T15:17:46 0 0 23 1 0 0.059 test.test_sys_code
01-21T15:17:46 0 0 3 1 0 0.054 test.test_sys_config
01-21T15:17:47 0 0 32 1 0 0.055 test.test_sys_domain
01-21T15:17:47 0 0 19 1 0 0.061 test.test_taobao_so_detail
01-21T15:17:47 0 0 14 1 0 0.312 test.test_taobao_so_header
01-21T15:17:47 0 0 50 1 0 0.063 test.test_trackprint_template
01-21T15:17:47 0 0 32 1 0 0.057 test.test_user
01-21T15:17:47 0 0 31 1 0 0.053 test.test_user_role
01-21T15:17:47 0 0 31 1 0 0.060 test.test_warehouse
终于发现了差异

在主库检查结果表percona.checksums,没有显示差异,难道还是有BUG ?
mysql> select db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums ;
+——-+—————————+——-+———-+———-+————+————+———————+
| db | tbl | chunk | this_crc | this_cnt | master_crc | master_cnt | ts |
+——-+—————————+——-+———-+———-+————+————+———————+
| test | test_lastexpressno | 1 | 96b2f90d | 2 | 96b2f90d | 2 | 2015-01-21 15:17:43 |
| test | test_location | 1 | d5582f63 | 38 | d5582f63 | 38 | 2015-01-21 15:17:43 |
| test | test_login_history | 1 | db189cf9 | 97 | db189cf9 | 97 | 2015-01-21 15:17:43 |
| test | test_menu | 1 | d9d69755 | 34 | d9d69755 | 34 | 2015-01-21 15:17:43 |
| test | test_pkd | 1 | ee959bc2 | 11 | ee959bc2 | 11 | 2015-01-21 15:17:43 |
| test | test_promotion | 1 | 2020c504 | 1 | 2020c504 | 1 | 2015-01-21 15:17:43 |
| test | test_promotion_condition | 1 | a71da967 | 1 | a71da967 | 1 | 2015-01-21 15:17:44 |

68 rows in set (0.00 sec)

在从库检查结果表percona.checksums
mysql> select db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums ;
+——-+—————————+——-+———-+———-+————+————+———————+
| db | tbl | chunk | this_crc | this_cnt | master_crc | master_cnt | ts |
| test | test_lastexpressno | 1 | 96b2f90d | 2 | 96b2f90d | 2 | 2015-01-21 15:17:43 |
| test | test_location | 1 | d5582f63 | 38 | d5582f63 | 38 | 2015-01-21 15:17:43 |
| test | test_login_history | 1 | db189cf9 | 97 | db189cf9 | 97 | 2015-01-21 15:17:43 |
| test | test_menu | 1 | 631fa3bf | 34 | d9d69755 | 34 | 2015-01-21 15:17:43 |
| test | test_pkd | 1 | ee959bc2 | 11 | ee959bc2 | 11 | 2015-01-21 15:17:43 |
| test | test_promotion | 1 | 2020c504 | 1 | 2020c504 | 1 | 2015-01-21 15:17:43 |
| test | test_promotion_condition | 1 | a71da967 | 1 | a71da967 | 1 | 2015-01-21 15:17:44 |
| test | test_promotion_gift | 1 | 5d6b31f | 2 | 5d6b31f | 2 | 2015-01-21 15:17:44 |

+——-+—————————+——-+———-+———-+————+————+———————+
68 rows in set (0.01 sec)
终于显示了差异,原来查看结果表记录的差异,要在slave库查看才行
前面在从库多插入的记录的那个实验,在从库查看的话,应该也没有问题,是我查看选了不正确的库查看导致的
结论:查看差异结果表,应该在slave库上查询

下面的结果是我后来又重做的实验,在从库查询的
select db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums where this_crc != master_crc ;
mysql> select db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums where this_crc != master_crc ;
+—–+———-+——-+———-+———-+————+————+———————+
| db | tbl | chunk | this_crc | this_cnt | master_crc | master_cnt | ts |
+—–+———-+——-+———-+———-+————+————+———————+
| test | test_menu | 1 | 3be44ed2 | 35 | d9d69755 | 34 | 2015-01-21 16:41:25 |
+—–+———-+——-+———-+———-+————+————+———————+
1 row in set (0.00 sec)
三.用pt-table-sync修复不一致数据
-bash-4.1$ pt-table-sync –print –replicate=percona.checksums h=localhost,u=root,p=passwd h=192.168.2.196,u=root,p=passwd

REPLACE INTO `test`.`test_menu`(`id`, `createtime`, `updatetime`, `domain_id`, `creator_id`, `updator_id`, `status_id`, `version`, `menuname`, `parent_id`, `role_id`, `sortindex`, `menudescr`, `remark`, `udf1`, `udf2`, `udf3`, `udf4`) VALUES (’60’, ‘2015-01-21 13:58:18’, ‘2015-01-21 13:58:20’, ‘-1’, NULL, NULL, NULL, ‘0’, ‘MENU_TEST2′, ’54’, ’57’, ‘6’, ‘TEST2222′, NULL, NULL, NULL, NULL, NULL) /*percona-toolkit src_db:test src_tbl:test_menu src_dsn:h=localhost,p=…,u=root dst_db:test dst_tbl:test_menu dst_dsn:h=192.168.2.196,p=…,u=root lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:8016 user:mysql host:open_mysql1*/;
-bash-4.1$ pt-table-sync –print –sync-to-master h=192.168.2.196,u=root,p=passwd –databases=test –tables=test_menu
REPLACE INTO `test`.`test_menu`(`id`, `createtime`, `updatetime`, `domain_id`, `creator_id`, `updator_id`, `status_id`, `version`, `menuname`, `parent_id`, `role_id`, `sortindex`, `menudescr`, `remark`, `udf1`, `udf2`, `udf3`, `udf4`) VALUES (’60’, ‘2015-01-21 13:58:18’, ‘2015-01-21 13:58:20’, ‘-1’, NULL, NULL, NULL, ‘0’, ‘MENU_TEST2′, ’54’, ’57’, ‘6’, ‘TEST2222′, NULL, NULL, NULL, NULL, NULL) /*percona-toolkit src_db:test src_tbl:test_menu src_dsn:P=3306,h=192.168.2.195,p=…,u=root dst_db:test dst_tbl:test_menu dst_dsn:h=192.168.2.196,p=…,u=root lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:8022 user:mysql host:open_mysql1*/;

上面两个命令等价:
参数的意义:
–replicate= :指定通过pt-table-checksum得到的表,这2个工具差不多都会一直用。
–databases= : 指定执行同步的数据库,多个用逗号隔开。
–tables= :指定执行同步的表,多个用逗号隔开。
–sync-to-master :指定一个DSN,即从的IP,他会通过show processlist或show slave status 去自动的找主。
h=127.0.0.1 :服务器地址,命令里有2个ip,第一次出现的是M的地址,第2次是Slave的地址。
u=root :帐号。
p=123456 :密码。
–print :打印,但不执行命令。
–execute :执行命令。

修复方法:上面生成的语句直接在slave库执行

注意如果从库比主库记录多,得到的是delete语句,也在从库执行
-bash-4.1$ pt-table-sync –print –replicate=percona.checksums h=localhost,u=root,p=passwd h=192.168.2.196,u=root,p=passwd
DELETE FROM `test`.`test_menu` WHERE `id`=’61’ LIMIT 1 /*percona-toolkit src_db:test src_tbl:test_menu src_dsn:h=localhost,p=…,u=root dst_db:test dst_tbl:test_menu dst_dsn:h=192.168.2.196,p=…,u=root lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:8029 user:mysql host:open_mysql1*/;

© 2016 Init dba

Theme by Anders NorenUp ↑