【问题现象】
一个数据库实例的备库在做备份时,备份的log 一直显示
>> log scanned up to (3015320266621)
>> log scanned up to (3015320266621)
....
>> log scanned up to (3015320266621)
>> log scanned up to (3015320266621)
>> log scanned up to (3015320266621)
长达 10多个小时。
mysql> show processlist;
+--------+-------------+--------------------+------+------------+---------+----------------------------------
| Id | User | Host | db | Command | Time |State
+--------+-------------+--------------------+------+------------+---------+----------------------------------
| 1 | system user | | NULL | Connect | 2684619 | Waiting for master to sendevent
| 2 | system user | | NULL | Connect | 31 | Waiting for release of readlock ......
4 rows in set (0.00 sec)
system用户显示 Waiting for release of readlock
执行备份的用户为Xtraback 对应的time 列则每4s 循环一次。system user 的状态表示 xtraback 进程已经执行了flush tables with read only，并等待xtrabackup进程释放read lock。我们首先来了解一下xtrabackup 大致过程：
backup()并记录XTRABACKUP_BINARY信息
mysql_open() 启动管道链接的子进程连接mysql
mysql_check() 检查连接mysql的子进程是否OK，发送一个虚拟查询，等待mysql_response_timeout超时退出
mysql_close() 关闭连接mysql子进程
start_ibbackup() 创建子进程运行ibbackup来备份innodb数据和索引
如果不是远程备份，不是stream模式，进入wait_for_ibbackup_suspend() 检查ibbackup子进程是否还活着
mysql_open()
如果指定–safe-slave-backup 进入wait_for_safe_slave() ，通过每隔3s启停sql thread来不断检查实例上打开临时表数目是否为0
mysql_check()
如果没有指定–no-lock,进入 mysql_lockall()，执行FLUSH TABLES WITH READ LOCK;
backup_files() 拷贝除ibd外所有的文件
mysql_unlockall() 执行UNLOCK TABLES
如果指定–safe-slave-backup START SLAVE SQL_THREAD
mysql_close()
问题出现在 发出flush tables with read lock 并获取了全局锁(systemuser 才在等待read lock) innodb 拷贝除ibd外所有的文件.
但是根据
xtrabackup源码
# flush tables with read lock
mysql_check(); --没有问题
mysql_lockall() if !$option_no_lock;
# timeout in seconds for a reply from mysql
my $mysql_response_timeout = 900;
sub mysql_send {
my $request = shift;
$current_mysql_request = $request;
print MYSQL_WRITER "$request\n";
mysql_check();
$current_mysql_request = '';
}
sub mysql_lockall {
$now = current_time();
print STDERR "$now $prefix Starting to lock all tables...\n";=
mysql_send "USE mysql;";
mysql_send "SET AUTOCOMMIT=0;";
if (compare_versions($mysql_server_version, '4.0.22') == 0
|| compare_versions($mysql_server_version, '4.1.7') == 0) {
# MySQL server version is 4.0.22 or 4.1.7
mysql_send "COMMIT;";
mysql_send "FLUSH TABLES WITH READ LOCK;";
} else {
# MySQL server version is other than 4.0.22 or 4.1.7
mysql_send "FLUSH TABLES WITH READ LOCK;";
mysql_send "COMMIT;";
}
write_binlog_info;
write_slave_info if $option_slave_info;
$now = current_time();
print STDERR "$now $prefix All tables locked and flushed to disk\n";
}
--在执行flush tables with read lock时，mysql_send函数执行超时900S，备份失败。我的实例的备份却一直表现为log scanned up to (3015320266621)。
记录一个疑问在这里。
参考资料：
http://bugs.mysql.com/bug.php?id=54436
http://www.taobaodba.com/html/1552_innobackupex%E8%B6%85%E6%97%B6%E9%80%80%E5%87%BA.html
http://www.dbunix.com/?p=3277