Tag Archives: mysql

MySQL relay_log_purge=0 时的风险

有时候,我们希望将 MySQL 的 relay log 多保留一段时间,比如用于高可用切换后的数据补齐,于是就会设置 relay_log_purge=0,禁止 SQL 线程在执行完一个 relay log 后自动将其删除。但是在官方文档关于这个设置有这么一句话:

Disabling purging of relay logs when using the --relay-log-recovery option risks data consistency and is therefore not crash-safe.

究竟是什么样的风险呢?查找了一番后,基本上明白了原因。

首先,为了让从库是 crash safe 的,必须设置 relay_log_recovery=1,这个选项的作用是,在 MySQL 崩溃或人工重启后,由于 IO 线程无法保证记录的从主库读取的 binlog 位置的正确性,因此,就不管 master_info 中记录的位置,而是根据 relay_log_info 中记录的已执行的 binlog 位置从主库下载,并让 SQL 线程也从这个位置开始执行。MySQL 启动时,相当于执行了 flush logs ,会新开一个 relay log 文件,新的 relay log 会记录在新的文件中。如果默认情况 relay_log_purge=1 时,SQL 线程就会自动将之前的 relay log 全部删除。而当 relay_log_purge=0 时,旧的 relay log 则会被保留。虽然这并不会影响从库复制本身,但还是会有地雷:

  1. 由于崩溃或停止 MySQL 时,SQL 线程可能没有执行完全部的 relay log,最后一个 relay log 中的一部分数据会被重新下载到新的文件中。也就是说,这部分数据重复了两次。
  2. 如果 SQL 跟得很紧,则可能在 IO 线程写入 relay log ,但还没有将同步到磁盘时,就已经读取执行了。这时,就会造成新的文件和旧的文件中少了一段数据。

如果我们读取 relay log 来获取数据,必须注意这一点,否则就会造成数据不一致。而保留 relay log 的目的也在于此。因此,在处理 relay log 时必须格外小心,通过其中 binlog 头信息来确保正确性。

关于如何配置 crash safe 的复制本身的配置,可以参照:
http://blog.itpub.net/22664653/viewspace-1752588/
http://www.innomysql.net/article/34.html

参考资料:
http://blog.booking.com/better_crash_safe_replication_for_mysql.html
https://bugs.mysql.com/bug.php?id=73038
http://bugs.mysql.com/bug.php?id=74324

使用 row 格式 binlog 撤销操作

MySQL 在使用 row 格式,并使用默认的 binlog_row_image=full 的时候,binlog 中记录了完整的更新前后的数据镜像。因此,根据 row 格式 binlog 进行撤销操作是可行的。我就试着做了这么个工具

[GitHub 页面] [linux 二进制文件]

例如

./binlog_undo -f /data/mysql/log-bin.000004 -p 3958 -o binlog.out

会扫描 /data/mysql/log-bin.000004 从位置 3958 到末尾的所有事务,倒转其中所有事务顺序和每个事务中的语句顺序,并反向所有的操作,把 WRITE 和 DELETE 反转,把 UPDATE 的前后镜像互换,然后将生成后的 binlog 文件写入 binlog.out 中。之后就可以用 mysqlbinlog 工具来回放这个 binlog 来撤销操作了。

这个工具可以用于在主备切换后,撤销掉原主上未同步到备的操作,避免完全重建;也可以作为误操作的后悔药。

记一次 MySQL 循环复制

有朋友的一对 MySQL 出现远大于其他类似实例的大量更新。因为开启了主备双向复制和 log_slave_updates,所以首先猜测是不是有循环复制,主 stop slave 后暂时正常。看了下主上的 relay_log,验证了这一点。幸而来回复制的只是监控用的心跳更新,不会搞坏数据。

但是 MySQL 会比较 server_id ,一般情况下是不会出问题的。于是又仔细看了下主上不正常的 relay_log ,发现其中 event 的 server_id 和当前的都不同。 问下来确实曾经在线改过 server_id。

想了下怎么触发这个问题,在自己的实例上复现了一把

  1. 开启 主->备 的复制
  2. 主上插入若干记录
  3. 更改主的server_id
  4. 开启 备->主 的复制
  5. 轰!

实际操作中,由于存在复制延时,即使没有先停止备->的复制也有可能触发问题。

然后就是怎么拆掉这个雷了。由于来回复制的只是心跳更新,所以只需要跳过就行,其实如果不是的话也已经完蛋没法救了。首先想到的是 binlog_ignore_db 和 replicate_ignore_db 。但是这两个不是动态变量,重启一次服务代价太大。同事 @Tachikoma 提醒我 change master 还有个 ignore_server_ids 选项。于是就只需要 change master to ignore_server_ids (1,2) 忽略之前的 server_id 就行。待复制正常,change master to ignore_server_ids () 就可以解除掉了。

移植 spider 到 MySQL 5.6

MariaDB 中自带了很多 MySQL 中没有的插件。我对其中的 spider 存储引擎很有兴趣。这个引擎可以让 MySQL 作为一个 proxy ,来实现 sharding、高可用等功能。这些功能已经有一些产品实现了,比如 MaxScale、Cobar、OneProxy、Atlas。但是我觉着 spider 把自己作为一个存储引擎来实现这些功能是有其优势的。SQL 解析和查询优化是个非常复杂而且很难做好的工作。其他替代产品都是自己实现,由于复杂性,这些产品都带来了一下限制,没能支持全部常见的 SQL 语句,给使用和实施带来了困难。而作为一个存储引擎,这些工作都由 MySQL 自身完成了,后面的工作就会简单很多,想做点优化的话也会容易些。

由于 MariaDB 从 MySQL 5.5 时代就分道扬镳了,做过很多改动后,和目前版本的 MySQL 已经有了不小差异,所以插件基本上没法直接拿到 MySQL 里编译使用。我就花了点功夫,把 spider 引擎移植到了 MySQL 5.6。

https://github.com/xiezhenye/mysql-plugin-spider-engine

编译使用和一般的插件差不多

cp -r src /path/to/mysql-src/storage/spider
cd /path/to/mysql-src
cmake . -DBUILD_CONFIG=mysql_release -DCMAKE_INSTALL_PREFIX=<mysql install dir>
cd storage/spider
make
make install

之后,执行附带的 install_spider.sql 安装插件,创建所需要的系统表。

mysql ... < scripts/install_spider.sql

具体文档参见 https://mariadb.com/kb/en/mariadb/spider/

在测试过程中,发现安装插件以后,重启 MySQL 后会 crash,然后再也启动不了,移除 ha_spider.so 后才行。具体来说,是在配置为

log_bin=off
spider_support_xa=on
spider_internal_xa=off

的时候。然而在 MariaDB 中却是正常的。开始以为是自己移植过程带来的 bug,或者有什么兼容问题未解决。追踪到后来,发现这居然是 MySQL 自身的 bug。于是去提交了一下。http://bugs.mysql.com/bug.php?id=78050

在使用外部 XA 的时候,如果没启用 binlog,会把 XA 信息通过 TC_LOG_MMAP 来持久化。然后 bug 就出在了那里。

这个 bug 曾经在 2009 年就被发现过,2012 年被 fix 过。但是显然并没有改对。待我自己做了 fix 以后,进一步发现,这个 bug 在 MariaDB 中已经被修复过,然后发现原来在 MySQL 5.7 分支下也是修复过的,但是并没应用到 5.6 分支。

都是几个相当低级的 bug。有成员未初始化,指针计算时搞错了指针类型,未判断空指针…… 。虽然这个地方确实是一般使用很难碰到,但是这代码质量简直无语。

获取 MySQL 崩溃时的 core file

对于一般进程,要让进程崩溃时能生成 core file 用于调试,只需要设置 rlimit 的 core file size > 0 即可。比如,用在 ulimit -c unlimited 时启动程序。

对 MySQL 来说,由于 core file 中会包含表空间的数据,所以默认情况下为了安全,mysqld 捕获了 SEGV 等信号,崩溃时并不会生成 core file,需要在 my.cnf 或启动参数中加上 core-file。

但是即使做到了以上两点,在 mysqld crash 时还是可能无法 core dump。还有一些系统参数会影响 core dump。以下脚本可供参考:

echo 2 >/proc/sys/fs/suid_dumpable
chmod 0777 /var/crash
echo /var/crash/core> /proc/sys/kernel/core_pattern
echo 1 >/proc/sys/kernel/core_uses_pid

由于 mysql 通常会以 suid 方式启动,所以需要打开 suid_dumpable 。对于 core_pattern,最好指定一个保证可写的绝对路径。

之后,就可以用 kill -SEGV 让 mysqld 崩溃,测试一下能不能正常产生 core file 了。

找到 mysql 数据库中的不良索引

为了演示,首先建两个包含不良索引的表,并弄点数据。

mysql> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(11) NOT NULL,
  `f1` int(11) DEFAULT NULL,
  `f2` int(11) DEFAULT NULL,
  `f3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `k1` (`f1`,`id`),
  KEY `k2` (`id`,`f1`),
  KEY `k3` (`f1`),
  KEY `k4` (`f1`,`f3`),
  KEY `k5` (`f1`,`f3`,`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table test2\G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `id1` int(11) NOT NULL DEFAULT '0',
  `id2` int(11) NOT NULL DEFAULT '0',
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id1`,`id2`),
  KEY `k1` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(*) from test2 group by b;                                                                                                        
+----------+
| count(*) |
+----------+
|       32 |
|       17 |
+----------+
2 rows in set (0.00 sec)

1. 包含主键的索引
innodb 本身是聚簇表,每个二级索引本身就包含主键,类似 f1, id 的索引实际虽然没有害处,但反映了使用者对 mysql 索引不了解。而类似 id, f1 的是多余索引,会浪费存储空间,并影响数据更新性能。包含主键的索引用这样一句 sql 就能全部找出来。

mysql> select c.*, pk from 
    ->   (select table_schema, table_name, index_name, concat('|', group_concat(column_name order by seq_in_index separator '|'), '|') cols 
    ->     from INFORMATION_SCHEMA.STATISTICS 
    ->     where index_name != 'PRIMARY' and table_schema != 'mysql'
    -> group by table_schema, table_name, index_name) c,
    ->   (select table_schema, table_name, concat('|', group_concat(column_name order by seq_in_index separator '|'), '|') pk 
    ->     from INFORMATION_SCHEMA.STATISTICS 
    ->     where index_name = 'PRIMARY' and table_schema != 'mysql'
    -> group by table_schema, table_name) p  
    -> where c.table_name = p.table_name and c.table_schema = p.table_schema and c.cols like concat('%', pk, '%');
+--------------+------------+------------+---------+------+
| table_schema | table_name | index_name | cols    | pk   |
+--------------+------------+------------+---------+------+
| test         | test1      | k1         | |f1|id| | |id| |
| test         | test1      | k2         | |id|f1| | |id| |
+--------------+------------+------------+---------+------+
2 rows in set (0.04 sec)

2. 重复索引前缀
包含重复前缀的索引,索引能由另一个包含该前缀的索引完全代替,是多余索引。多余的索引会浪费存储空间,并影响数据更新性能。这样的索引同样用一句 sql 可以找出来。

mysql> select c1.table_schema, c1.table_name, c1.index_name,c1.cols,c2.index_name, c2.cols from
    ->   (select table_schema, table_name, index_name, concat('|', group_concat(column_name order by seq_in_index separator '|'), '|') cols 
    ->     from INFORMATION_SCHEMA.STATISTICS 
    ->     where table_schema != 'mysql' and index_name!='PRIMARY'
    -> group by table_schema,table_name,index_name) c1,   
    ->   (select table_schema, table_name,index_name, concat('|', group_concat(column_name order by seq_in_index separator '|'), '|') cols 
    ->     from INFORMATION_SCHEMA.STATISTICS 
    ->     where table_schema != 'mysql' and index_name != 'PRIMARY'
    -> group by table_schema, table_name, index_name) c2 
    -> where c1.table_name = c2.table_name and c1.table_schema = c2.table_schema and c1.cols like concat(c2.cols, '%') and c1.index_name != c2.index_name;
+--------------+------------+------------+------------+------------+---------+
| table_schema | table_name | index_name | cols       | index_name | cols    |
+--------------+------------+------------+------------+------------+---------+
| test         | test1      | k1         | |f1|id|    | k3         | |f1|    |
| test         | test1      | k4         | |f1|f3|    | k3         | |f1|    |
| test         | test1      | k5         | |f1|f3|f2| | k3         | |f1|    |
| test         | test1      | k5         | |f1|f3|f2| | k4         | |f1|f3| |
+--------------+------------+------------+------------+------------+---------+
4 rows in set (0.02 sec)

3. 低区分度索引
这样的索引由于仍然会扫描大量记录,在实际查询时通常会被忽略。但是在某些情况下仍然是有用的。因此需要根据实际情况进一步分析。这里是区分度小于 10% 的索引,可以根据需要调整参数。

mysql> select p.table_schema, p.table_name, c.index_name, c.car, p.car total from
    ->   (select table_schema, table_name, index_name, max(cardinality) car
    ->     from INFORMATION_SCHEMA.STATISTICS
    -> where index_name != 'PRIMARY'
    -> group by table_schema, table_name,index_name) c,
    ->   (select table_schema, table_name, max(cardinality) car
    ->     from INFORMATION_SCHEMA.STATISTICS
    -> where index_name = 'PRIMARY' and table_schema != 'mysql'
    -> group by table_schema,table_name) p
    -> where c.table_name = p.table_name and c.table_schema = p.table_schema and p.car > 0 and c.car / p.car < 0.1;
+--------------+------------+------------+------+-------+
| table_schema | table_name | index_name | car  | total |
+--------------+------------+------------+------+-------+
| test         | test2      | k1         |    4 |    49 |
+--------------+------------+------------+------+-------+
1 row in set (0.04 sec)

4. 复合主键
由于 innodb 是聚簇表,每个二级索引都会包含主键值。复合主键会造成二级索引庞大,而影响二级索引查询性能,并影响更新性能。同样需要根据实际情况进一步分析。

mysql> select table_schema, table_name, group_concat(column_name order by seq_in_index separator ',') cols, max(seq_in_index) len
    ->    from INFORMATION_SCHEMA.STATISTICS
    ->    where index_name = 'PRIMARY' and table_schema != 'mysql'
    ->    group by table_schema, table_name having len>1;
+--------------+------------+-----------------------------------+------+
| table_schema | table_name | cols                              | len  |
+--------------+------------+-----------------------------------+------+
| test         | test2      | id1,id2                           |    2 |
+--------------+------------+-----------------------------------+------+
1 rows in set (0.01 sec)

MySQL 复制心跳

在 MySQL 主从复制时,有时候会碰到这样的故障:在 Slave 上 Slave_IO_Running 和 Slave_SQL_Running 都是 Yes,Slave_SQL_Running_State 显示 Slave has read all relay log; waiting for the slave I/O thread to update it ,看起来状态都正常,但实际却滞后于主,Master_Log_File 和 Read_Master_Log_Pos 也不是实际主上最新的位置。一种可能是 Master 上的 binlog dump 线程挂了。但有时候,在 Master 上检查也是完全正常的。那 Slave 的延误又是怎么造成的呢?

在 MySQL 的复制协议里,由 Slave 发送一个 COM_BINLOG_DUMP 命令后,就完全由 Master 来推送数据,Master、Slave 之间不再需要交互。如果 Master 没有更新,也就不会有数据流,Slave 就不会收到任何数据包。但是如果由于某种原因造成 Master 无法把数据发送到 Slave ,比如发生过网络故障或其他原因导致 Master 上的 TCP 连接丢失,由于 TCP 协议的特性,Slave 没有机会得到通知,所以也没法知道收不到数据是因为 Master 本来就没有更新呢还是由于出了故障。

好在 MySQL 5.5 开始增加了一个复制心跳的功能。

stop slave;
change master to master_heartbeat_period = 10;
set global slave_net_timeout = 25;
start slave;

就会让 Master 在没有数据的时候,每 10 秒发送一个心跳包。这样 Slave 就能知道 Master 是不是还正常。slave_net_timeout 是设置在多久没收到数据后认为网络超时,之后 Slave 的 IO 线程会重新连接 Master 。结合这两个设置就可以避免由于网络问题导致的复制延误。master_heartbeat_period 单位是秒,可以是个带上小数,如 10.5。最高精度为 1 毫秒。

slave_net_timeout 的默认是 3600,也就是一小时。也就是说,在之前的情况下,Slave 要延误 1 小时后才会尝试重连。而在没有设置 master_heartbeat_period 时,将 slave_net_timeout 设得很短会造成 Master 没有数据更新时频繁重连。

很奇怪的是,当前的 master_heartbeat_period 值无法通过 show slave status 查看,而要使用 show status like ‘Slave_heartbeat_period’ 查看。此外,状态变量 Slave_last_heartbeat 表示最后一次收到心跳的时间,Slave_received_heartbeats 表示总共收到的心跳次数。

如:

mysql> show status like 'slave%';
+----------------------------+---------------------+
| Variable_name              | Value               |
+----------------------------+---------------------+
| Slave_heartbeat_period     | 5.000               |
| Slave_last_heartbeat       | 2014-05-08 11:48:57 |
| Slave_open_temp_tables     | 0                   |
| Slave_received_heartbeats  | 1645                |
| Slave_retried_transactions | 0                   |
| Slave_running              | ON                  |
+----------------------------+---------------------+
6 rows in set (0.00 sec)

实现上,是 Slave 在发送 COM_BINLOG_DUMP 命令前,先执行 set @master_heartbeat_period=... 来设置。需要注意的是,这里的变量是整数类型,单位的纳秒。如果设为 5 秒,实际这里的值是 5000000000。

禁用 MYISAM 引擎的 MySQL 插件

在无法控制最终用户使用 MySQL 时,就可能出现混合使用 InnoDB 和 MYISAM 的情况。这时就需要考虑为两种引擎各自配置缓存等资源,即使最后实际只会用 InnoDB 的时候也是如此,这就造成了无法充分利用资源,也增加了资源限制的难度。同时由于 MYISAM 引擎的稳定性问题,为了减少运维工作,通常也不希望用户去使用 MYISAM 引擎。这时候就想禁用掉它。但是 MYISAM 引擎被 MySQL 系统表使用,还可能用于内部临时表,所以不可能直接禁用,因此 mysqld 也没有 –skip-myisam 这样的参数。所以只能想别的办法。比如,只禁止创建新的 MYISAM 表,对于原有的表则没有影响。于是就做了这么一个插件

https://github.com/xiezhenye/mysql-plugin-disable-myisam

其实这个插件很简单,只是替换了 MYISAM 引擎的 create 入口,改为返回一个包装过的,替换了 create 方法的 ha_myisam 对象。在试图创建表的时候直接错误返回。

按装插件后,就无法再创建任何新的 MYISAM 表了

mysql> CREATE TABLE `test4` (
  `id` int(11) AUTO_INCREMENT,
  `value` varchar(30),
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;
ERROR 1030 (HY000): Got error 1 from storage engine

显示 metadata lock 信息的 mysql 插件

有一回碰上在 mysql 5.5 中,一些表没法执行 DDL ,会一直被阻塞住。show processlist 会看到

mysql> show full processlist;
+----+------+-----------------+------+---------+------+---------------------------------+-----------------------+
| Id | User | Host            | db   | Command | Time | State                           | Info                  |
+----+------+-----------------+------+---------+------+---------------------------------+-----------------------+
...
|  6 | root | localhost:56841 | test | Query   |    0 | init                            | show full processlist |
|  7 | root | localhost:56843 | test | Query   |   13 | Waiting for table metadata lock | rename table u to uu  |
...
+----+------+-----------------+------+---------+------+---------------------------------+-----------------------+

可以看出,是被一个 metadata lock 阻塞住了。但是从 process list 里是没法看出是哪个线程持有了这个锁。用别的方式,也只能得到通过 lock table 方式获取的锁或 innodb 事务的信息。对于一些比较隐蔽的方式,比如

mysql> begin;
mysql> select * from test.u;

这样也同样会获取 metadata lock 。在 autocommit 为 0 的时候,没有显式 begin 也是如此。这类情况没法从任何途径获取究竟是谁上了锁。

于是我就试着做了一个 mysql 的 information schema 插件。 https://github.com/xiezhenye/mysql-plugin-mdl-info 安装后,只要 select * from information_schema.MDL_INFO 就可以获取到所有 metadata lock ,也就是 mdl 的信息。

mysql> select * from information_schema.MDL_INFO;
+-----------+-------------+---------------------+-----------+----------+------+
| THREAD_ID | DURATION    | TYPE                | NAMESPACE | DATABASE | NAME |
+-----------+-------------+---------------------+-----------+----------+------+
|         7 | STATEMENT   | INTENTION_EXCLUSIVE | GLOBAL    |          |      |
|         7 | TRANSACTION | SHARED_WRITE        | TABLE     | test     | t    |
+-----------+-------------+---------------------+-----------+----------+------+
2 rows in set (0.00 sec)

在写这个插件的时候,发现 mysql 把 mdl lock 的信息封装的相当严密,即使是写插件也没法用常规途径获取。只好用了一些很恶心的技巧。如果各位有碰到类似的问题,也可以先在测试环境试试。

在 mysql 中对特定的库禁用 DDL 语句

mysql 的权限控制功能虽然已经比较强大,但是是基于白名单规则,所以没法做到对除某某库,如 mysql 库以外的所有库分配权限,或者说单独禁用某个库的某些权限。虽然可以一个个库地分配,但是这样毕竟麻烦,尤其是在库的数量会动态变化的情况下。

对于 DDL 语句,也就是 create 、alter 、drop 之类,有一个特殊的办法可以做到。其实也很简单,去掉那个库所在的目录的写权限即可。例如:

chmod a-w mysql

之后,在 mysql 库上执行任意 DDL 语句都会出错。

这方法看着挺恶心,不过也没找到更好的解决办法。