MySQL binlog后面的编号最大是多大?

    在上课中讲到MySQL的binlog是mysql-bin.000001,有细心的学习提到,是不是这个达到mysql-bin.999999后数据库的binlog就要重新开始了?
对于这个问题一时间也被问住了,只是隐约记得这个可以出现大于999999,这次也来细致的研究一下,顺便Mark一下。
MySQL在启动时会扫一下binlog文件,找到sequence后最大的一个,然后产生下个文件,根据这个原理,我们可以先测试一下当有了mysql-bin.999999 后,数据库会不会产生mysql-bin.000001或是mysql-bin.1000000。

测试一:

把mysqld关掉,到日志目录:

touch mysql-bin.999999
[root@zst1 17:26:28 /data/mysql/mysql3307/logs]
#touch mysql-bin.999999

[root@zst1 17:26:37 /data/mysql/mysql3307/logs]
#ls -lh
total 12K
-rw-r----- 1 mysql mysql 923 May 22 22:16 mysql-bin.000001
-rw-r----- 1 mysql mysql 660 May 22 22:28 mysql-bin.000002
-rw-r--r-- 1 root root 0 May 24 17:26 mysql-bin.999999
-rw-r----- 1 mysql mysql 88 May 22 22:19 mysql-bin.index

[root@zst1 17:26:40 /data/mysql/mysql3307/logs]
#chown mysql:mysql *
[root@zst1 17:26:48 /data/mysql/mysql3307/logs]
#/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my3307.cnf &
[root@zst1 17:27:17 /data/mysql/mysql3307/logs]
#ls -lh
total 76K
-rw-r----- 1 mysql mysql 923 May 22 22:16 mysql-bin.000001
-rw-r----- 1 mysql mysql 660 May 24 17:27 mysql-bin.000002
-rw-r----- 1 mysql mysql 194 May 24 17:27 mysql-bin.1000000
-rw-r--r-- 1 mysql mysql 0 May 24 17:26 mysql-bin.999999
-rw-r----- 1 mysql mysql 133 May 24 17:27 mysql-bin.index

启动mysqld
show master status 确认:

root@zhishutang:mysql3307.sock [(none)]>show master status;
+-------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.1000000 | 194 | | | 61beeb3d-2a88-11e7-9db9-080027f7e774:3-4 |
+-------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

可以看到,并没有挂掉,也没重新从mysql-bin.000001开始。

对于这个问题,我们在来挖一下,具体是什么?
教学使用的版本是mysql-5.7.18下载相应的源码看看 : sql/binlog.cc 实际找到:
从这里可以看到:
zhishutang.com
if (max_found == MAX_LOG_UNIQUE_FN_EXT)
当找到的数是定义的最大数时,就退出,再找一下MAX_LOG_UNIQUE_FN_EXT定义:

#define MAX_LOG_UNIQUE_FN_EXT 0x7FFFFFFF

root@zhishutang:mysql3307.sock [(none)]>select conv('7FFFFFFF',16,10);
+------------------------+
| conv('7FFFFFFF',16,10) |
+------------------------+
| 2147483647 |
+------------------------+
1 row in set (0.00 sec)

现在看到是最大值是: pow(2,31)-1
我们来测试几个场景:

测试二: 测试当达到mysql-bin.2147483647 再进行flush logs;MySQL的反应。

touch mysql-bin.2147483640

启动后给的警告:

2017-05-24T09:57:43.081308Z 0 [Warning] Next log extension: 2147483641. Remaining log filename extensions: 6. Please consider archiving some logs.
2017-05-24T09:57:43.081436Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170524 17:57:43
2017-05-24T09:57:43.090250Z 0 [Warning] Next log extension: 2147483641. Remaining log filename extensions: 6. Please consider archiving some logs.

root@localhost:mysql3307.sock [(none)]>flush logs;
Query OK, 0 rows affected (0.01 sec)

root@localhost:mysql3307.sock [(none)]>show master status;
+----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.2147483647 | 194 | | | 61beeb3d-2a88-11e7-9db9-080027f7e774:3-4 |
+----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

进行切换超限一下试试

root@localhost:mysql3307.sock [(none)]>flush logs;
ERROR 1598 (HY000): Binary logging not possible. Message: Either disk is full or file system is read only while rotating the binlog. Aborting the server.
root@localhost:mysql3307.sock [(none)]>flush logs;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql3307.sock' (111)
ERROR:
Can't connect to the server

mysqld 直接退出…

报错日志:

2017-05-24T09:59:08.207040Z 3 [Warning] Next log extension: 2147483642. Remaining log filename extensions: 5. Please consider archiving some logs.
2017-05-24T09:59:09.970075Z 3 [Warning] Next log extension: 2147483643. Remaining log filename extensions: 4. Please consider archiving some logs.
2017-05-24T09:59:10.882140Z 3 [Warning] Next log extension: 2147483644. Remaining log filename extensions: 3. Please consider archiving some logs.
2017-05-24T09:59:12.050605Z 3 [Warning] Next log extension: 2147483645. Remaining log filename extensions: 2. Please consider archiving some logs.
2017-05-24T09:59:14.090312Z 3 [Warning] Next log extension: 2147483646. Remaining log filename extensions: 1. Please consider archiving some logs.
2017-05-24T09:59:26.483618Z 3 [Warning] Next log extension: 2147483647. Remaining log filename extensions: 0. Please consider archiving some logs.
2017-05-24T09:59:34.894426Z 3 [ERROR] Log filename extension number exhausted: 2147483647. Please fix this by archiving old logs and updating the index files.
2017-05-24T09:59:34.894441Z 3 [ERROR] Can't generate a unique log-filename /data/mysql/mysql3307/logs/mysql-bin.(1-999)

2017-05-24T09:59:34.894668Z 3 [ERROR] /usr/local/mysql/bin/mysqld: Binary logging not possible. Message: Either disk is full or file system is read only while rotating the binlog. Aborting the server.

看样是想产生mysql-bin.(1-999) 这样的文件。 再进行进行下面的测试。

测试三: 测试Binlog达到最大值后,能不能重新开始。

确保mysql-bin.000001不存在到999都不存在

root@localhost:mysql3307.sock [(none)]>show master status;
+----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.2147483646 | 154 | | | 61beeb3d-2a88-11e7-9db9-080027f7e774:3-4 |
+----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

root@localhost:mysql3307.sock [(none)]>flush logs;
Query OK, 0 rows affected (0.01 sec)

root@localhost:mysql3307.sock [(none)]>show master status;
+----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.2147483647 | 154 | | | 61beeb3d-2a88-11e7-9db9-080027f7e774:3-4 |
+----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

root@localhost:mysql3307.sock [(none)]>flush logs;
ERROR 1598 (HY000): Binary logging not possible. Message: Either disk is full or file system is read only while rotating the binlog. Aborting the server.
root@localhost:mysql3307.sock [(none)]>show master status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql3307.sock' (111)
ERROR:
Can't connect to the server

一样退出。并没有进行日志的轮换使用。

结论:
1. mysql binlog的最大sequence是:pow(2,31)-1 = 2147483647
2. 当binlog接近这个值是小于1000开始向error log中写入警告
3. binlog的sequence达到最大值时,不管有没有mysql-bin.000001类似这样的文件,mysqld都是退出。
4. 在mysql产生binlog时会读取当前日件文目录下的log-bin的base name获取下一个日志文件的后面的Seq。 所以日志目录下文件太多,会影响MySQL的启动及日志切换。 这里也有一个大的隐患运行中给放一个较大的日志文件,在下次日志文件切换时有可能很快就接近于最大值,造成mysqld crash退出。
5. 一定要监控error log的输出。并足够重视。

 

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

MySQL 5.7 传统复制到GTID在线切换

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

前题:

  1. 要求MySQL 5.7.6及以后版本。
  2. 所有组中成员gtid_mode 为off状态。

 

实现步骤:

  1. 所有的Server执行

set @@global.enforce_gtid_consistency = warn;

 

特别注意: 这一步是关建的一步使用不能出现警告。

 

2.所有的server上执行:

set @@global.enforce_gtid_consistency = on;

 

3.所有的Server上执行(不关心最先最后,但要执行完):

set @@global.gtid_mode = off_permissive;

 

  1. 执行:

set @@global.gtid_mode=on_permissive;

 

实质在这一步骤生的日志都是带GTID的日志了,这个步骤号称是不关心任何节点,但从实管理上推荐在slave上先执行,然后再去master上执行。

 

 

  1. 确认传统的binlog复制完毕,该值为0

show status like ‘ongoing_anonymous_transaction_count’;

 

需要所有的节点都确认为0.

 

  1. 所有节点进行判断 show status like ‘ongoing_anonymous_transaction_count’; 为零

所有的节点也可以执行一下: flush logs; 用于切换一下日志。

 

  1. 所有的节点启用gtid_mode

set @@global.gtid_mode=on

 

  1. 把gtid_mode = on相关配置写入配置文件

gtid_mode=on

enforce_gtid_consistency=on

 

  1. 启用Gtid的自动查找节点复制:

stop slave;

change master to master_auto_position=1;

start slave;

 

完毕。Good Luck。

 

 

MySQL运行中被改权限测试

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

今天一个接到一个朋友求助,说是数据在运行中,数据库的目录被改了权限。如: 数据库目结构如下:

datadir=/data/mysql/mysql3306/data
log-bin = /data/mysql/mysql3306/logs
tmpdir  = /data/mysql/mysql3306/tmp

被运维同步执行了:

chown -R  root:root /data/mysql/mysql3306

1.构建主从环境

mysql;3306 主
/data/mysql/mysql3306/{data,tmp,logs}
mysql;3307 从
/data/mysql/mysql3307/{data,tmp,logs}

2. 在主的wubx库里创建:

CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
)

确认复制正常。

3. 把主库的目录权限改成root

chown -R root:root /data/mysql/mysql3306

4. 弄出来大量的写入

for i in `seq(100000); do mysql wubx -e "insert into t2(name) vlaues('golang$i')"; done

5. 观查主库和从库上数据
发现日志没有切换时,数据都可以写入,同步正常。 主库上binlog还可以正常写入。

6. 模拟日拟切换
主库上执行: flush logs;
得到报错:
切换日志错误

从库同步报错:
1595错误
show slave status\G;
show slave status\G;

从这里看出来,从库获取到主库日志切换指令,但主库没能创建出来新的日志,所以造成复制中断。

7.结论
主库上不影响数据写入,但发生日志切换后,不能进行新的日志写入,但没卡住写入。
从库上在主库日志发生切后,能得到新的日志文件名,但不能获到新的日志,所以同步停掉。

8.修复建议:
通过实验说明,主库上的数据是最全的,在后续日志切换失败后,没有影响数据的写入。但数据没有同步到从上。

 

思考:

这个有点是mysqld的一个bug的感觉了,日志已经无法写入,但数据还可以写入。 很容易造成同步有问题。 对于数据不同步怎么修复。多次给学生们讲过,也能很快的把环境处理好。

Good luck!

MySQL 二进制日志格式基础(一)

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.
MySQL二制进日志用于记录数据库的变更记录,这里从结构上讨论一下日志的格式。

每个日志都包含4个字节的magic number 和event的描述包

  1. 日志有前四个字节是magic number: oxfe ox62 0x69 0x6e = 0xfe ‘b”i”n’ 转成整数:1852400382  用处就是读4个字节对比不是这个数,说明就不是二进制日志,就不用处理了。
      log_event.sh中可以查到
      /* 4 bytes which all binlogs should begin with */
         #define BINLOG_MAGIC        "\xfe\x62\x69\x6e"
    
  2. 每个event的header大概如下:
     - 每个event中包含: event的类型,什么时间,由哪版本的MySQL产生的
     - 从header头能找到该event的大小及一些变更信息
    
  3. 第一个event称为:format descriptor event(Event描述结构:FDE) 用于说明日志的格式
  4. 其它event就是依赖于描述结构不同,用不同的结构记录数据
  5. 最后一个event是: 日志切换事件(log-rotation event)用于指点定下个日志的文件名

每个event的结构大概如下:

+===================+
| event header      |
+===================+
| event data        |
+===================+

现在大多使用的V4结构,从MySQL 5.0起,具体如下:

+=====================================+
| event  | timestamp         0 : 4    |
| header +----------------------------+
|        | type_code         4 : 1    |
|        +----------------------------+
|        | server_id         5 : 4    |
|        +----------------------------+
|        | event_length      9 : 4    |
|        +----------------------------+
|        | next_position    13 : 4    |
|        +----------------------------+
|        | flags            17 : 2    |
|        +----------------------------+
|        | extra_headers    19 : x-19 |
+=====================================+
| event  | fixed part        x : y    |
| data   +----------------------------+
|        | variable part              |
+=====================================+

第一个event是FDE结构没有extra_headers部分,所以固定为19个字节。

FDE的event_data中定长部分为:

  • 2字节的的日志格式版本,从MySQL 5.0后都是4
  • 50字节 用于记录MySQL的版本号 如:5.6.16-64.2-rel64.2-log 不够50字节用0x00填充
  • 4字节 日志产生的时间
  • 1字节 header长度。一般是19,如果大于19,则下面的event都有extra_header字段
    对于FDE变长部分一般为空

其它Event计算

  • header length = x byte
  • data length = (event_lenth -x )byte
  • 数据区里定长部分长度
      fixed_part = y byte
      variable_part = (event_length - (x+y)) byte
    

如果给定的X不是19,则存extra_header里面有内存
Y依赖于event_type有不同的大小,需要参考不同的event进行特别处理
参考:http://dev.mysql.com/doc/internals/en/event-data-for-specific-event-types.html

内存表在同步环境注意事项

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

在一些场景想利用MySQL的内存表存一些数据来加快数据的操作。但如果在复制环境中这可不是一个好事情。
主要原因如下:
1. 内存表在数据库重启或是异常down机的情况下内存表的数据会全部丢失。如果从库重启一下则同步就不能进行了。
2. 另一方面,在主从环境下,如果从库上同时有大的操作或是排序工作,有借助于临时表的的场景,同时主库上内存表也有较大的写入,从库有可能会就出现报那个内存表is full (1114)这样的错误。
3. 特别需要注意在使用内存表的场景主库重启会主动发起一次对内存表的truncate table操作

那如何解决呢:
1. 从我对数据库的了解上来看推荐用Innodb表去替代memory表, Innodb表如果一个表的数据经常被访问,就会被加载到内存里,数据和索引都在内存,访问速度是比较快的。
2. 不要复制Innodb表。 利用replication-igore-table=db.tbaname形式声明不同步那个表。 这种情况下如果在statement级别的复制,禁止出现insert into otbname select c1 from tbname这种语句。
3.把监表单独放到一个实例下,和主要业务数据分开。

尝试mysqlbinlog的flashback功能

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

简介:
mysqlbinlog flashback功能是淘宝彭立勋(http://www.penglixun.com/)的一个很强劲的作品.
主要功能: 对rows格式的binlog可以进行逆向操作.delete反向生成insert, update生成反向的update,insert反向生成delete.让dba同学们也有机会简单的恢复数据.可恢复:insert, update,delete相关的操作

演示一下使用过程:

生成带有flashback mysqlbinlog 工具:

项止主页:http://mysql.taobao.org/index.php/Patch_source_code#Add_flashback_feature_for_mysqlbinlog

准备好MySQL-5.5.18的源码,这里用的Percona-MySQL-5.5.18源码

cd mysql-5.5.18
wget http://mysql.taobao.org/images/0/0f/5.5.18_flashback.diff
patch -p0<5.5.18_flashback.diff

即可以看到了mysqlbinlog , 因这里只为要mysqlbinlog这个程序,所以编译MySQL时没加特别的参数.该工具是否具备flashback功能可以确认一下是否有 "-B" 这个参数.

开始实验

mysql test
mysql> select count(*) from pic_url;
+----------+
| count(*) |
+----------+
| 786476 |
+----------+
1 row in set (0.11 sec)
mysql>delete from pic_url;
Query OK, 786476 rows affected (22.81 sec)
mysql>select count(*) from pic_url;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.03 sec)

mysql>show binary logs;
...
| mysql-bin.000011 | 195001447 |
| mysql-bin.000012 | 106 |
+------------------+-----------+

Tips:
定位日值,一般看当前的log如果当前的log文件比较小,那么就是上一个文件至于为什么,这里就不讲了.也可以用mysqlbinlog 去实际查看确认一下.

接下来就是要找到这个delete在log中position的变化区间了.这个没什么技巧,通常使用:

./mysqlbinlog -v --base64-output=decode-rows  /u1/mysql/logs/mysql-bin.000011 >11.sql

然后对11.sql文件进行搜索了表名,找到相应的节点.大表删除通常最后的stop点都在文件最后.找到节点后就可以:

./mysqlbinlog -v --base64-output=decode-rows -B --start-position=377 --stop-position=195001377 /u1/mysql/logs/mysql-bin.000011>B11.sql

同样对B11.sql这个文件验证一下.看看结尾是和预期一样.验证OK后就可以:

./mysqlbinlog -B --start-position=377 --stop-position=195001377 /u1/mysql/logs/mysql-bin.000011|mysql test

如果表比较大,则执行着比较慢.如不出错请耐心等待.执行完毕后:

mysql>select count(*) from pic_url;
+----------+
| count(*) |
+----------+
| 786476 |
+----------+
1 row in set (0.11 sec)

数据又恢复了.

注意:
为防止恢复报错需要把:max_allowed_packet 改到最大值1G;
mysql>set global max_allowed_packet=1024*1024*1024;

#max_allow_packet大小不够时报错如下:
ERROR 1153 (08S01) at line 403133: Got a packet bigger than 'max_allowed_packet' bytes

恢复操作有风险,请在备库操作或是在经验丰富的同学指导下进行.

二进制文件上传不到服务器上,传到github上一个二进制文件:https://github.com/wubx/mysql-binlog-statistic/tree/master/bin  在64的位的linux系统编译的. 有需要的直接下载.

从库无业务延迟严重排查

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

今天给一个客户巡检的情况下发从库没有业务的情况mysqld的cpu的一个core占用100%.查主库慢查询也没有关于写的SQL.
可以说是典的单进程复制把一个cpu占满造成的.知道原因了,就好分析了.
分析一下binlog中写的什么,看看有什么地方可以优化或是加速的.利用工具:pasrebinlog
利用show slave status\G; 查当前同步的到节点,然后对日值进行解析.

git clone https://github.com/wubx/mysql-binlog-statistic.git
cd mysql-binlog-statistic/bin/
parsebinlog /u1/mysql/logs/mysql-bin.000806

...
====================================
Table xx_db.xxtable:
Type DELETE opt: 101246
Type INSERT opt: 103265
================================
...

以最大的数排序看, 定位到: xx_db.xxtable,对于一个日值中能删除10几万,写入10几万.是不是这个表写入比较慢了呢.
在从库上查看innodb的相关情况:

MySQL> show engine innodb status\G;
...
---TRANSACTION 1C0C2DFDF, ACTIVE 3 sec fetching rows
mysql tables in use 1, locked 1
3361 lock struct(s), heap size 407992, 477888 row lock(s), undo log entries 42
MySQL thread id 43, OS thread handle 0x7fc1800c4700, query id 1908504 Reading event from the relay log
TABLE LOCK table xx_db.xxtable trx id 1C0C2DFDF lock mode IX
RECORD LOCKS space id 1002 page no 1975 n bits 1120 index `AK_movieid` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6965 n bits 264 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6967 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6973 n bits 264 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6982 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6983 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6987 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 6999 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
RECORD LOCKS space id 1002 page no 7000 n bits 256 index `GEN_CLUST_INDEX` of table xx_db.xxtable trx id 1C0C2DFDF lock_mode X locks rec but not gap
TOO MANY LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS
----------------------------
END OF INNODB MONITOR OUTPUT

...

从Innodb 的monitor output 中也可看到 xx_db.xxtable 这表已经是表级表了,造成并发比较低,而且有大量的: GEN_CLUST_INDEX 而且属于一个事务.  GEN_CLUST_INDEX表示没有主建,内部产生一个主建,对于内部产生的主建很很容易造成page拆分的操作.

问题到这里基本上可以得到解决问题的方法了:
给xx_db.xxtable 添加一个主建即可.这里后是给xx_db.xxtable 添加了一个无业务意义的id int 自增主建.这样立马可以看到mysqld占用的cpu单核降到了3%左右, 同时后续同步一切正常,观查一天没出现同步延迟的问题.

truncate table 不能复制到从库

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究。

          bug说明: 该BUG在是MySQL5.1.X中存在的一个问题。
重现方法:
        利用 5.1.31-enterprise-gpl-pro-log (Or 5.1.31-sp1-enterprise) 搭建master/slave结构同步正常进行(确认同步进行)
 注意参数:
事务隔级为: READ-COMMITTED
日值格式为: mixed

然后在主库建表:

create database wubx;
create table t1 (id int) engine=innodb;
insert into t1 values(1),(2),(3),(4),(5);
select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+

从库:

use wubx;
select * from t1 ;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+

主库上:

use wubx;
mysql> truncate table t1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
Empty set (0.01 sec)

从库:

use wubx;
select * from t1 ;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+

解决办法:
先删除该表,然后创建该表。
如: truncate table wubx;改变为:

drop table wubx;
create table wubx( id int) engine=innodb;

另一种方式:
修改事务隔离级别为默认的。可以MySQL的版本升级到MySQL-5.1.37后的版本。

Relay log read failure的处理

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式:select unhex(’777562696E67786940676D61696C2E636F6D’); 载请注明作/译者和出处,并且不能用于商业用途,违者必究。
       众所周知MySQL5.1的Replication是比较烂的。MySQL的每一个版本更新关于同步方面每次都是可以看到一大堆。但MySQL 5.1性能是比较突出的。所以经不住诱惑使用MySQL 5.1。所以也要经常遇到一些Bug。如: 

   
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.118
                  Master_User: repl_wu
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.005121
          Read_Master_Log_Pos: 64337286
               Relay_Log_File: relay-bin.003995
                Relay_Log_Pos: 18446697137031827760
        Relay_Master_Log_File: mysql-bin.005121
             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: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4
              Relay_Log_Space: 64337901
              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: 1594
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
1 row in set (0.00 sec)
 

        从上面可以看到是中继日值或是Master上的日值出问题了。
        首先如果是中继日值坏掉,那只需要找到同步的时间点,然后重新同步,这样就可以有新的中继日值了。如果Master上的日值坏了就麻烦了。
从经验来看,这是中继日值出问题了。处理方法:

    需要找到同步的点。

日值为:Master_Log_File: mysql-bin.005121,Relay_Master_Log_File: mysql-bin.005121以Relay_Master_Log_File为准,Master_Log_File为参考。

日值执行时间点:

Exec_Master_Log_Pos: 4

那么现在就可以:

  
      mysql>stop slave;

    mysql>change master to Master_Log_File=’mysql-bin.005121’, Master_Log_Pos=4;
   
    mysql>start slave;

    mysql>show slave status\G;

    进行确认。

 

   建议:

    在使用MySQL-5.1.36以下的版本的同学,请尽快升级到MySQL-5.1.40 & MySQL-5.1.37sp1