最近用innobackup进行备份测试，我们只备份一个innodb类型的库，数据大小大概50多G，用innobackupex大概用了5个多小时，但是mysqldump只用了大约2个小时，这让我很费解，有哪位知道的同志能够交流下？按理说innobackupex应该快的，还有就是大家在备份时不要放到高峰期，因为innobackupex备份时比较吃系统IO，下面就是我备份脚本，分为全备和增备，使用innobackupex需要安装依赖包：
yum install perl-DBD-MySQL*
一、全备
1、全备脚本
--全备到远程服务器
/data/mysql/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password="*******" --database=report
--stream=tar /data/backup/fullbackup 2>/data/backup/fullbackup/bak.log |gzip| ssh root@192.168.1.1 "cat - > /data/backup/fullbackup/`date +%F_%H-%M-%S`.tar.gz "
--全量备份到本地
/data/mysql/bin/innobackupex --defaults-file=/etc/my.cnf --user=root --password="******" --database=report
--stream=tar /data/backup/fullbackup 2>/data/backup/fullbackup/bak.log | gzip > /data/backup/fullbackup/`date +%F_%H-%M-%S`.tar.gz
注意事项：1、--defaults-file参数需要放在第一个位置，否则报错提示，通过这个参数，将数据文件（由cnf里的变量datadir指定）拷贝至备份目录下。备份成功后，将在备份目录下创建一个时间戳目录，在该目录下存放备份文件。
2、--stream=tar表示通过流的方式将文件传送给tar进行归档。
3、还有其他的参数请参考官方文档。
2、全备恢复
--解压
tar -izxvf 2015-10-26_18-11-16.tar.gz -C 2015-10-26_18-11-16
注意事项：需要添加-i参数，否则解压出来的文件不全，通过-C指定目标目录
--全量恢复：假设恢复还原到3307端口的库中
恢复分为两步，第一步需要“prepare”，其实就是xtrabackup应用事务日志，对于已经commit的操作进行前滚，对于rollback的操作进行回滚，这个跟crash recovery类似。
--应用日志：
/data/mysql/bin/innobackupex --apply-log /data/backup/fullbackup/2015-10-26_18-11-16
注意：apply-log后备份目录会出现xtrabackup_binlog_pos_innodb，这个文件记录应用到binlog日志的文件和位置。有时可能会有xtrabackup_binlog_info文件，如果做从库，那么以哪个为准呢？
1 对于纯 InnoDB 操作，备份出来的数据中上述两个文件的内容是一致的
2 对于 InnoDB 和非事务存储引擎混合操作，xtrabackup_binlog_info 中所示的 position 应该会比 xtrabackup_pos_innodb 所示的数值大。此时应以 xtrabackup_binlog_info 为准；
--copy文件
#需先关闭mysql服务
/data/mysql/bin/mysqladmin -S /tmp/mysql3307.sock --user=root --password="*******" shutdown
#删除数据库目录文件和日志
rm -rf /data/mysql/data2/report ib*
#进行文件同步
rsync -avz report ib* /data/mysql/data2
#最后别忘了修改权限
chown -R mysql:mysql /data/mysql/data2/*
注意事项：使用--copy-back，要求data目录下面为空，如不为空，会报错如下Original data directory /data/mysql/data2 is not empty!这种适合备份实例下的所有数据库的情况，这时可以清空data目录。但是不适合只备份特定的某些库，由于我们是备份特定report库，所以这里直接用rsync来同步文件。下面的是官方的copy-back供参考：
/data/mysql/bin/innobackupex --defaults-file=/etc/my_3307.cnf --copy-back /data/backup/fullbackup/2015-10-26_18-11-16
到这里，数据库就恢复还原好了，可以重新启动mysql服务，登录mysql查看数据恢复情况。一般来说全备的恢复比较简单。但是每次备份的数据量比较大，时间也长，大家根据实际情况决定备份方式。
二、增备
增量备份：以某个特定的全备为基础，个人理解可以细分两种：一种是每天都以该全备为基础进行增备。另一种是以前一天的增量为基础进行增量。
/data/mysql/bin/innobackupex --user=root --password="*******" --database=report
--incremental --incremental-basedir=/data/backup/fullbackup/2015-10-27_15-27-04 /data/backup/fullbackup
注意事项：--incremental参数指明用增量备份方式；--incremental-basedir参数指定以哪个全备为基础
对于第一种恢复方法：
/data/mysql/bin/innobackupex --apply-log --redo-only /data/backup/fullbackup/2015-10-27_15-27-04
/data/mysql/bin/innobackupex --apply-log /data/backup/fullbackup/2015-10-27_15-27-04 --incremental-dir=/data/backup/fullbackup/2015-10-27_16-28-40
对于第二种恢复方法：
/data/mysql/bin/innobackupex --apply-log --redo-only /data/backup/fullbackup/2015-10-27_14-20-35
/data/mysql/bin/innobackupex --apply-log --redo-only /data/backup/fullbackup/2015-10-27_14-20-37
......redo每一天的增量
/data/mysql/bin/innobackupex --apply-log /data/backup/fullbackup/2015-10-27_14-20-35 --incremental-dir=/data/backup/fullbackup/2015-10-27_14-35-11
注意事项：
对于第一种方式:由于每次都是以全备为基础增量，所以只需恢复全备和最后一次的增量即可。
对于第二种方式：需要按顺序对全备，第一次增备...第N-1次增备进行应用事务日志文件，最后一次的增量不需要，最终日志都应用到全备中了，故最后只需要将全备文件夹中的文件copy到对应data目录即可。
三、从全备中恢复指定的表
其实，我们很多情况下并不需要恢复整个库，更多的是恢复指定的表，如果用mysqldump备份的就非常蛋疼了，我曾经从一个mysqldump备份的70G文件中抽取恢复一张有2000万记录的表，可想而知，这种方式效率有多低下吧，下面我介绍下应用innobackupex全备情况下，如何恢复还原指定表。
使用innobackupex恢复指定表前提条件：
- 源和目标数据库server需要使用独立表空间，即开启InnoDB_File_Per_Table = ON参数
- 目标数据库需要使用mysql5.6及以上版本或者xtradb引擎，源数据库不作限制
【恢复还原】
1、应用日志，这里需要加上--export参数：
/data/mysql/bin/innobackupex --apply-log --export /data/backup/test
打印应用日志过程如下：
InnoDB: Table kartriderrushplus3_log/t_order_notify_log in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table kartriderrushplus3_log/t_tsi_transaction_sync_error in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table kartriderrushplus3_log/t_user_buy_item_count_log in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table kartriderrushplus3_log/t_user_kart_log in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table kartriderrushplus3_log/t_user_lotto_log in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table kartriderrushplus3_log/t_user_lucci_log in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table kartriderrushplus3_log/t_user_mall_log in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table kartriderrushplus3_log/t_user_oil_log in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table kartriderrushplus3_log/t_user_pve_log in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table kartriderrushplus3_log/t_user_recharge_log in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table kartriderrushplus3_log/t_user_silver_log in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table kartriderrushplus3_log/t_user_world_pve_log in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table mysql/innodb_index_stats in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table mysql/innodb_table_stats in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table mysql/slave_master_info in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table mysql/slave_relay_log_info in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table mysql/slave_worker_info in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/boxgetitem in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/cart in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/dgoldsilversave in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/getoil in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/getplayahead in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/getplayaheadpnum in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/goldcostitem in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/goldgetcost in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/goldsilversave in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/matchplay in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/mg_basic_stat in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/mg_basic_stat_monthly in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/onlinestatus in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/pvemodepass in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/pvepoint in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/pvepointpasslv in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/pvplog in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/shop_log in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/silvercostitem in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/silvergetcost in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/t_conf_user_level_exp in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/t_monthly_index in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/userlvnum in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table test/userviplevelnum in the InnoDB data dictionary has tablespace id , but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6. started; log sequence number
xtrabackup: export option is specified.
xtrabackup: export metadata of table 'KartRiderRushPlus3/connection_log' to file `./KartRiderRushPlus3/connection_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_buy_egg_log' to file `./KartRiderRushPlus3/t_buy_egg_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_lotto' to file `./KartRiderRushPlus3/t_user_lotto.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=idx_user_id, id.low=, page=
xtrabackup: name=key_user_idx, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_treasure_const' to file `./KartRiderRushPlus3/t_conf_treasure_const.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_admin_ptcl_record' to file `./KartRiderRushPlus3/t_admin_ptcl_record.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_server_user_status' to file `./KartRiderRushPlus3/t_server_user_status.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_session' to file `./KartRiderRushPlus3/t_user_session.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_bingo' to file `./KartRiderRushPlus3/t_conf_bingo.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_item_count' to file `./KartRiderRushPlus3/t_user_item_count.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=user_idx_item_idx, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_const' to file `./KartRiderRushPlus3/t_conf_const.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_lucci_log' to file `./KartRiderRushPlus3/t_user_lucci_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_challenge' to file `./KartRiderRushPlus3/t_user_challenge.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_mark' to file `./KartRiderRushPlus3/t_user_mark.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_apple_charge_error_log' to file `./KartRiderRushPlus3/t_apple_charge_error_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=user_idx, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_kart_level' to file `./KartRiderRushPlus3/t_conf_kart_level.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_game_item' to file `./KartRiderRushPlus3/t_user_game_item.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_kart_system_level' to file `./KartRiderRushPlus3/t_conf_kart_system_level.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_trace_log_m' to file `./KartRiderRushPlus3/t_trace_log_m.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_apple_charge_notify_log' to file `./KartRiderRushPlus3/t_apple_charge_notify_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=user_idx, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_mission' to file `./KartRiderRushPlus3/t_conf_mission.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_world_pve_point' to file `./KartRiderRushPlus3/t_conf_world_pve_point.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_gm_broadcast' to file `./KartRiderRushPlus3/t_gm_broadcast.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_pve_clearing' to file `./KartRiderRushPlus3/t_conf_pve_clearing.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_cup_rank_award_log' to file `./KartRiderRushPlus3/t_cup_rank_award_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_item_upgrade' to file `./KartRiderRushPlus3/t_conf_item_upgrade.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_world_pve' to file `./KartRiderRushPlus3/t_conf_world_pve.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_bingo_treasure' to file `./KartRiderRushPlus3/t_conf_bingo_treasure.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_buy_oil_money_log' to file `./KartRiderRushPlus3/t_buy_oil_money_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/questionnaire_info' to file `./KartRiderRushPlus3/questionnaire_info.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_pve_log' to file `./KartRiderRushPlus3/t_user_pve_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_decomposition' to file `./KartRiderRushPlus3/t_conf_decomposition.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_status_log_m' to file `./KartRiderRushPlus3/t_status_log_m.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_login_info_log' to file `./KartRiderRushPlus3/t_login_info_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_composition' to file `./KartRiderRushPlus3/t_conf_composition.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_speed_force' to file `./KartRiderRushPlus3/t_conf_speed_force.exp` ( indexes)
xtrabackup: name=GEN_CLUST_INDEX, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_skill_treasure' to file `./KartRiderRushPlus3/t_conf_skill_treasure.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_match_record_log' to file `./KartRiderRushPlus3/t_match_record_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_achievement_schedule' to file `./KartRiderRushPlus3/t_user_achievement_schedule.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=user_idx_achievement_group_id, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user' to file `./KartRiderRushPlus3/t_user.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=id, id.low=, page=
xtrabackup: name=key_name, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_achievement' to file `./KartRiderRushPlus3/t_conf_achievement.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_pve' to file `./KartRiderRushPlus3/t_conf_pve.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_oil_money' to file `./KartRiderRushPlus3/t_conf_oil_money.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/user_extend' to file `./KartRiderRushPlus3/user_extend.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_questionnaire' to file `./KartRiderRushPlus3/t_conf_questionnaire.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/face_img' to file `./KartRiderRushPlus3/face_img.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_mall' to file `./KartRiderRushPlus3/t_conf_mall.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_pve_ai' to file `./KartRiderRushPlus3/t_conf_pve_ai.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_world_pve' to file `./KartRiderRushPlus3/t_user_world_pve.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_shop' to file `./KartRiderRushPlus3/t_conf_shop.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/status_log' to file `./KartRiderRushPlus3/status_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_hot_time' to file `./KartRiderRushPlus3/t_conf_hot_time.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_pve_award' to file `./KartRiderRushPlus3/t_conf_pve_award.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_mail_box' to file `./KartRiderRushPlus3/t_mail_box.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=key_user_idx, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_device' to file `./KartRiderRushPlus3/t_user_device.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=IX_user_device_user_idx, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_item_log' to file `./KartRiderRushPlus3/t_user_item_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_admin_user_ping_log' to file `./KartRiderRushPlus3/t_admin_user_ping_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_point_log' to file `./KartRiderRushPlus3/t_user_point_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=UN_t_user_point_log_stat_date_user_idx, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_user_level' to file `./KartRiderRushPlus3/t_conf_user_level.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_item_skill_upgrade_log' to file `./KartRiderRushPlus3/t_item_skill_upgrade_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_treasure' to file `./KartRiderRushPlus3/t_conf_treasure.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_rp_item' to file `./KartRiderRushPlus3/t_conf_rp_item.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_kart_ranking' to file `./KartRiderRushPlus3/t_user_kart_ranking.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_composition_log' to file `./KartRiderRushPlus3/t_composition_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_vip' to file `./KartRiderRushPlus3/t_conf_vip.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_prohibit_str' to file `./KartRiderRushPlus3/t_conf_prohibit_str.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_pve_point' to file `./KartRiderRushPlus3/t_conf_pve_point.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_skill' to file `./KartRiderRushPlus3/t_conf_skill.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_notice' to file `./KartRiderRushPlus3/t_conf_notice.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_mall' to file `./KartRiderRushPlus3/t_user_mall.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=user_idx_item_idx, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_charge' to file `./KartRiderRushPlus3/t_user_charge.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_admin_section_record' to file `./KartRiderRushPlus3/t_admin_section_record.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_order' to file `./KartRiderRushPlus3/t_order.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_game_item_probability' to file `./KartRiderRushPlus3/t_conf_game_item_probability.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_sport' to file `./KartRiderRushPlus3/t_conf_sport.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_game_item_treasure_log' to file `./KartRiderRushPlus3/t_game_item_treasure_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_point' to file `./KartRiderRushPlus3/t_user_point.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=IX_t_user_point_point_last_update_time, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_login_reward' to file `./KartRiderRushPlus3/t_conf_login_reward.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_mall_package' to file `./KartRiderRushPlus3/t_conf_mall_package.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_order_notify_log' to file `./KartRiderRushPlus3/t_order_notify_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_item_count_log' to file `./KartRiderRushPlus3/t_user_item_count_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_egg' to file `./KartRiderRushPlus3/t_user_egg.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_item_upgrade_log' to file `./KartRiderRushPlus3/t_item_upgrade_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_pve_detail' to file `./KartRiderRushPlus3/t_user_pve_detail.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=key_user_idx, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_guide' to file `./KartRiderRushPlus3/t_conf_guide.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/prohibit_str' to file `./KartRiderRushPlus3/prohibit_str.exp` ( indexes)
xtrabackup: name=GEN_CLUST_INDEX, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_decomposition_log' to file `./KartRiderRushPlus3/t_decomposition_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/temp_kart_ranking_params' to file `./KartRiderRushPlus3/temp_kart_ranking_params.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=item_idx item_level item_control_level, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_track_definition' to file `./KartRiderRushPlus3/t_conf_track_definition.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_kart_system_group' to file `./KartRiderRushPlus3/t_conf_kart_system_group.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_silver_log' to file `./KartRiderRushPlus3/t_user_silver_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_kart_definition' to file `./KartRiderRushPlus3/t_conf_kart_definition.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_tsi_transaction_sync_error' to file `./KartRiderRushPlus3/t_tsi_transaction_sync_error.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_buy_shop_log' to file `./KartRiderRushPlus3/t_buy_shop_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_skill_random' to file `./KartRiderRushPlus3/t_conf_skill_random.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/user_login_daily' to file `./KartRiderRushPlus3/user_login_daily.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=user_idx, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_match_item' to file `./KartRiderRushPlus3/t_conf_match_item.exp` ( indexes)
xtrabackup: name=GEN_CLUST_INDEX, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/cdk' to file `./KartRiderRushPlus3/cdk.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_prohibit_str_old' to file `./KartRiderRushPlus3/t_conf_prohibit_str_old.exp` ( indexes)
xtrabackup: name=GEN_CLUST_INDEX, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_egg' to file `./KartRiderRushPlus3/t_conf_egg.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_attribute_point' to file `./KartRiderRushPlus3/t_conf_attribute_point.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_manager_status_log' to file `./KartRiderRushPlus3/t_manager_status_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/users_buy_oil_money_info' to file `./KartRiderRushPlus3/users_buy_oil_money_info.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_pvp' to file `./KartRiderRushPlus3/t_user_pvp.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=pvp_point, id.low=, page=
xtrabackup: name=pvp_point_team, id.low=, page=
xtrabackup: name=pvp_point_speed, id.low=, page=
xtrabackup: name=temp_pvp_point_total, id.low=, page=
xtrabackup: name=pvp_point_total, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/test' to file `./KartRiderRushPlus3/test.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_sport' to file `./KartRiderRushPlus3/t_user_sport.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=user_idx sport_idx, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_pve' to file `./KartRiderRushPlus3/t_user_pve.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=key_user_idx, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_mail' to file `./KartRiderRushPlus3/t_conf_mail.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_item' to file `./KartRiderRushPlus3/t_conf_item.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_item' to file `./KartRiderRushPlus3/t_user_item.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=key_user_idx, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_match_room_log' to file `./KartRiderRushPlus3/t_match_room_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_mission' to file `./KartRiderRushPlus3/t_user_mission.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=idx_user_id, id.low=, page=
xtrabackup: name=key_user_idx, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/user_login_continuous' to file `./KartRiderRushPlus3/user_login_continuous.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_achievement' to file `./KartRiderRushPlus3/t_user_achievement.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=user_idx_achievement_idx, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_treasure_in_item' to file `./KartRiderRushPlus3/t_conf_treasure_in_item.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_friend_info' to file `./KartRiderRushPlus3/t_friend_info.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=user_idx, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_vip' to file `./KartRiderRushPlus3/t_user_vip.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_face' to file `./KartRiderRushPlus3/t_conf_face.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_match_room_session' to file `./KartRiderRushPlus3/t_match_room_session.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_conf_item_fall_address' to file `./KartRiderRushPlus3/t_conf_item_fall_address.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_match_player_log' to file `./KartRiderRushPlus3/t_match_player_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/users_cdk' to file `./KartRiderRushPlus3/users_cdk.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_pve_ranking' to file `./KartRiderRushPlus3/t_user_pve_ranking.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: name=idx, id.low=, page=
xtrabackup: export metadata of table 'KartRiderRushPlus3/t_user_item_strengthen_log' to file `./KartRiderRushPlus3/t_user_item_strengthen_log.exp` ( indexes)
xtrabackup: name=PRIMARY, id.low=, page=
xtrabackup: Last MySQL binlog file position , file name mysql-bin. xtrabackup: starting shutdown with innodb_fast_shutdown =
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group =
xtrabackup: innodb_log_file_size =
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to MB
InnoDB: Progress in MB:
InnoDB: Setting log file ./ib_logfile1 size to MB
InnoDB: Progress in MB:
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=
InnoDB: Highest supported file format is Barracuda.
InnoDB: rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6. started; log sequence number
xtrabackup: starting shutdown with innodb_fast_shutdown =
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number
:: completed OK!
这时，在全备目录下数据库目录中多生成了.cfg和.exp两个文件，如下：
root > ll
-rw-r--r-- 1 root root 490 Nov 11 16:04 users_cdk.cfg
-rw-r--r-- 1 root root 16384 Nov 11 16:04 users_cdk.exp
-rw-rw---- root root Jul : users_cdk.frm
-rw-rw---- root root Jul : users_cdk.ibd
2、还原指定的t_user_session表数据：
- 需要在目标库中手工建好与源数据库一致的表结构：（省）
- discard t_user_session表空间：
mysql> ALTER TABLE t_user_session DISCARD TABLESPACE;
Query OK, rows affected (0.01 sec)
- 从备份目录复制.cfg .ibd文件到目标服务器数据库目录
[root@1.1.1.1]# cp t_user_session.ibd t_user_session.cfg /data/mysql/data/test/
[root@1.1.1.1]#
- 进入到MySQL之后Import表空间
mysql> ALTER TABLE t_user_session import TABLESPACE;
ERROR 1815 (HY000): Internal error: Cannot reset LSNs in table '"test"."t_user_session"' : Tablespace not found
--该错误是由于数据文件所属权限问题，需要改为mysql用户，比如：
-rw-r--r--1 root root Nov : t_user_session.cfg
-rw-rw---- mysql mysql Nov : t_user_session.frm
-rw-r----- root root Nov : t_user_session.ibd
--再次导入表空间
mysql> ALTER TABLE t_user_session import TABLESPACE;
Query OK, rows affected ( min 22.51 sec)
这时数据就还原完成了，比mysqldump抽取大文件快多了吧。
mysql> select * from t_user_session limit ;
+------------------+----------+---------------------+---------------------+-------------+----------------------+
| user_session_idx | user_idx | login_time | logout_time | logout_type | client_ip |
+------------------+----------+---------------------+---------------------+-------------+----------------------+
| | | -- :: | -- :: | LOGOUT | ******************* |
| | | -- :: | -- :: | LOGOUT | ******************* |
| | | -- :: | -- :: | LOGOUT | ******************* |
| | | -- :: | -- :: | LOGOUT | ******************** |
| | | -- :: | -- :: | LOGOUT | ******************** |
| | | -- :: | -- :: | LOGOUT | ******************** |
| | | -- :: | -- :: | LOGOUT | ******************** |
| | | -- :: | -- :: | LOGOUT | ******************** |
| | | -- :: | -- :: | LOGOUT | ******************** |
| | | -- :: | -- :: | LOGOUT | ******************** |
+------------------+----------+---------------------+---------------------+-------------+----------------------+
rows in set (0.00 sec)
【注意点】:
- 在复制备份文件的时候一定要复制后缀cfg文件,否则在Import的时候就会报Warning.例如如下的信息:
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory)
Error opening './test/t.cfg', will attempt to import without schema
verification |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
cfg文件的用处主要是在MySQL5.6执行"Flush Table xxx
Export;"之后使.ibd文件保持一致性,同时这个文件会生成一个.cfg文件,在做Import的时候会对导入过程进行校验,但是在
MySQL5.6.8版本之后也不是必须要有.cfg文件.如果真没有,在导入的时候有可能就会报出上面的错误,所以为了安全还是复制它.
- 如果表有外键在Discard的时候执行如下命令:set FOREIGN_KEY_CHECKS=0; 在Import表之后执行以下命令恢复外键检查：set FOREIGN_KEY_CHECKS=1;
- 从备份目录复制.cfg .ibd文件到目标服务器数据库目录后，别忘了查看文件所属权限，否则报如下错误：ERROR 1815 (HY000): Internal error: Cannot reset LSNs in table '"test"."t_user_session"' : Tablespace not found
四、相关备份脚本
下面提供两个shell写的备份脚本，是转网友的，个人觉得写的挺全的，给大家参考：
1、全备shell脚本
#!/bin/bash
user='root'
passwd='root'
database=test
my_config='/etc/my.cnf'
backup_dir='/data/backup/fullbackup'
log=$database-$(date +%Y%m%d%H%M).log
str=$database-$(date +%Y%m%d%H%M).tar.gz echo "Start to backup at $(date +%Y%m%d%H%M)" >> /data/backup/fullbackup/$log
if [ ! -d "$backup_dir" ];then
mkdir -p $backup_dir
fi
/data/mysql/bin/innobackupex --defaults-file=$my_config --user=$user --password=$passwd --database=$database --stream=tar $backup_dir >$backup_dir/$log | gzip >$backup_dir/ $str
if [ $? -eq ];then
echo "Backup is finish! at $(date +%Y%m%d%H%M)" >> /data/backup/fullbackup/$log
exit
else
echo "Backup is Fail! at $(date +%Y%m%d%H%M)" >> /data/backup/fullbackup/$log
exit
fi
2、全备加增备
#!/bin/bash
CONFIG_FILE="/etc/my.cnf"
BACKUP_USER="backup"
BACKUP_PASSWD="123A456"
MYSQL_PORT=""
MYSQL_HOST="127.0.0.1"
BACKUP_BASE="/home/xtr_backup"
XTR_BACKUPLOG="/tmp/xtr_mysql_backup.log"
DATE_DIR=`date +%Y-%m-%d`
SUNDAY_DATE=`date -d "Last sunday" +%Y-%m-%d`
DATE_WEEK=`date +%w`
DATE_TIME=`date +%Y-%m-%d-%H-%M-%S` ####################################################
#全量备份函数
####################################################
function Xtr_full_backup ()
{
if [ -d "${BACKUP_BASE}/full/${DATE_DIR}" ]
then
rm -rf "${BACKUP_BASE}/full/${DATE_DIR}"
fi
if [ ! -d "${BACKUP_BASE}/full" ]
then
mkdir -p "${BACKUP_BASE}/full"
fi
innobackupex --defaults-file=${CONFIG_FILE} --user=${BACKUP_USER} --password=${BACKUP_PASSWD} --port=${MYSQL_PORT} --host=${MYSQL_HOST} --no-lock --no-timestamp "${BACKUP_BASE}/full/${DATE_DIR}" >"/tmp/$$full_backup.log"
STATS_FULL_LOG=`awk '/innobackupex: completed OK\!/ {print $NF}' "/tmp/$$full_backup.log"`
if [ "$STATS_FULL_LOG" != "OK!" ]
then
echo "行号LINENO; 脚本名称(basename $0); 函数名称FUNCNAME; 时间DATE_TIME; Innobackupex Full Backup Is Fail">>"$XTR_BACKUPLOG"
exit
else
echo "行号LINENO; 脚本名称(basename $0); 函数名称FUNCNAME; 时间DATE_TIME; Innobackupex Full Backup Is Ok">>"$XTR_BACKUPLOG"
rm -rf "/tmp/$$full_backup.log"
fi
} ####################################################
#增量备份函数
####################################################
function Xtr_inc_backup ()
{
if [ -d "${BACKUP_BASE}/inc/${SUNDAY_DATE}/${DATE_DIR}" ]
then
rm -rf "${BACKUP_BASE}/inc/${SUNDAY_DATE}/${DATE_DIR}"
fi
if [ ! -d "${BACKUP_BASE}/inc/${SUNDAY_DATE}" ]
then
mkdir -p "${BACKUP_BASE}/inc/${SUNDAY_DATE}"
fi
if [ ! -d "${BACKUP_BASE}/full/${SUNDAY_DATE}" ]
then
echo "行号LINENO; 脚本名称(basename $0); 函数名称:$FUNCNAME; 时间:$DATE_TIME; Innobackupex Full Dir ${BACKUP_BASE}/full/$SUNDAY_DATE No Exit">>"$XTR_BACKUPLOG"
exit
fi
innobackupex --defaults-file=${CONFIG_FILE} --user=${BACKUP_USER} --password=${BACKUP_PASSWD} --port=${MYSQL_PORT} --host=${MYSQL_HOST} --no-timestamp --parallel= --incremental --incremental-basedir="${BACKUP_BASE}/full/${SUNDAY_DATE}" "${BACKUP_BASE}/inc/${SUNDAY_DATE}/${DATE_DIR}" >"/tmp/$$inc_backup.log"
STATS_INC_LOG=`awk '/innobackupex: completed OK\!/ {print $NF}' "/tmp/$$inc_backup.log"`
if [ "$STATS_INC_LOG" != "OK!" ]
then
echo "行号:$LINENO; 脚本名称:$(basename $0); 函数名称:$FUNCNAME; 时间:$DATE_TIME; Innobackupex Inc Backup Is Fail">>"$XTR_BACKUPLOG"
exit
else
echo "行号:$LINENO; 脚本名称:$(basename $0); 函数名称:$FUNCNAME; 时间:$DATE_TIME; Innobackupex Inc Backup Is Ok">>"$XTR_BACKUPLOG"
rm -rf "/tmp/$$inc_backup.log"
fi
} ####################################################
#主体备份函数
####################################################
function Main ()
{
if [ "${DATE_WEEK}" -eq ]
then
Xtr_full_backup
else
Xtr_inc_backup
fi
exit
} ####################################################
#开始备份
####################################################
Main