innobackupex -在恢复mysql备份之后，服务器无法启动。

Question

还原备份后，mysql服务器无法启动。

quit without updating PID file

备份命令行

innobackupex --user=root --password=pass --databases="the_database" --stream=tar ./ 2> /var/log/innobackupex.log | gzip -c -1 > /var/bak/backup.tar.gz

还原命令行

tar -izxf /var/bak/db/2013-11-16-2300_mysql.tar.gz -C /var/bak/db_import
innobackupex --defaults-file=/var/ini/my.cnf --use-memory=1G --apply-log /var/bak/db_import
service mysql stop
mv /var/lib/mysql /var/lib/mysql-old
mkdir /var/lib/mysql
innobackupex --defaults-file=/var/ini/my.cnf --copy-back /var/bak/db_import
chown -R mysql:mysql /var/lib/mysql
service mysql start

/var/lib/mysql

innodb_index_stats.ibd
innodb_table_stats.ibd
slave_master_info.ibd
slave_relay_log_info.ibd
slave_worker_info.ibd

错误日志

131121 14:08:12 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2013-11-21 14:08:13 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
2013-11-21 14:08:13 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
2013-11-21 14:08:13 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2013-11-21 14:08:13 14010 [Warning] Using pre 5.5 semantics to load error messages from /opt/mysql/server-5.6/share/english/.
2013-11-21 14:08:13 14010 [Warning] If this is not intended, refer to the documentation for valid usage of --lc-messages-dir and --language parameters.
2013-11-21 14:08:13 14010 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2013-11-21 14:08:13 14010 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2013-11-21 14:08:13 14010 [Note] Plugin 'FEDERATED' is disabled.
/usr/local/mysql/bin/mysqld: Table 'mysql.plugin' doesn't exist
2013-11-21 14:08:13 14010 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2013-11-21 14:08:13 14010 [Note] InnoDB: The InnoDB memory heap is disabled
2013-11-21 14:08:13 14010 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-11-21 14:08:13 14010 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-11-21 14:08:13 14010 [Note] InnoDB: Using Linux native AIO
2013-11-21 14:08:13 14010 [Note] InnoDB: Not using CPU crc32 instructions
2013-11-21 14:08:13 14010 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-11-21 14:08:13 14010 [Note] InnoDB: Completed initialization of buffer pool
2013-11-21 14:08:13 14010 [Note] InnoDB: Highest supported file format is Barracuda.
2013-11-21 14:08:13 14010 [Note] InnoDB: 128 rollback segment(s) are active.
2013-11-21 14:08:14 14010 [Note] InnoDB: Waiting for purge to start
2013-11-21 14:08:14 14010 [Note] InnoDB: 5.6.14 started; log sequence number 56227635
2013-11-21 14:08:14 14010 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: fb44ff89-52ad-11e3-9dff-002185624b9b.
2013-11-21 14:08:15 14010 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2013-11-21 14:08:15 14010 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2013-11-21 14:08:15 14010 [Note] Server socket created on IP: '127.0.0.1'.
2013-11-21 14:08:15 14010 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
131121 14:08:15 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Answer 1

您已删除(移动) /var/lib/mysql文件夹(mv /var/lib/mysql /var/lib/mysql-old)，该文件夹包含mysql系统数据库。

将此文件夹还原到其原始位置，然后加载转储。此转储仅包含来自所选数据库的数据。

转储仅恢复位于/var/lib/mysql/[your_database]中的数据。您不应该接触/var/lib/mysql/mysql下的文件。

完整性:在默认设置中，/var/lib/mysql文件夹包含以下重要的系统文件，在正常情况下不应被篡改：

• /var/lib/mysql/mysql/*：mysql系统数据库
• /var/lib/mysql/ib_logfile*：重做日志
• /var/lib/mysql/ibdata*：InnoDB表的默认表空间
• /var/lib/mysql/mysql-bin.*：二进制日志

除非你确切知道你在做什么，否则不要操作这些文件。

Answer 2

如果您不关心原始mysql特权配置(mysql数据库)，可以运行安装脚本，这将创建mysql数据库。

$ mysql_install_db

Answer 3

您应该备份所有数据，而不仅仅是一个数据库。

您需要使用xtrabackup命令：

ex: xtrabackup --prepare --target-dir=/data/backups/mysql/

backup.html