首页 > 代码库 > 记一次xtrabackup全备恢复故障修复
记一次xtrabackup全备恢复故障修复
使用 innobackupex --copy-back完全恢复后，启动mysqld_safe --user=mysql &时报错，无法启动
[root@CentOS6 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 19G 4.5G 13G 26% /
tmpfs 931M 0 931M 0% /dev/shm
/dev/sda1 190M 27M 154M 15% /boot
/dev/sdb1 50G 49G 0 100% /mysqldata
/dev/sdc1 50G 17G 31G 35% /mysqlbackdata
怀疑是数据盘空间被占完导致的无法启动
清理掉其中一份一模一样的备份，省出空间来
[root@CentOS6 ~]# cd /mysqldata/
[root@CentOS6 mysqldata]# ll
总用量 40
drwxrwxr-x. 2 mysql mysql 16384 1月 27 19:57 lost+found
drwxrwxr-x. 6 mysql mysql 4096 2月 1 23:22 mysql_data
drwxrwxr-x. 2 mysql mysql 4096 1月 28 01:40 mysqldata1
drwxrwxr-x. 2 mysql mysql 4096 1月 28 01:40 mysqldata2
drwxrwxr-x. 2 mysql mysql 4096 1月 28 01:40 mysqldata3
drwxrwxr-x. 6 mysql mysql 4096 2月 1 20:40 temp
drwxrwxr-x. 5 mysql mysql 4096 2月 1 23:04 temp2
[root@CentOS6 mysqldata]# du -h
1.1M./temp/performance_schema
604K./temp/sys
44K./temp/test
11M./temp/mysql
17G./temp
4.0K./mysqldata1
1.1M./mysql_data/performance_schema
604K./mysql_data/sys
44K./mysql_data/test
11M./mysql_data/mysql
17G./mysql_data
4.0K./mysqldata3
4.0K./mysqldata2
16K./lost+found
1.1M./temp2/performance_schema
604K./temp2/sys
11M./temp2/mysql
17G./temp2
49G.
[root@CentOS6 mysqldata]# rm -rf temp2
root@CentOS6 mysqldata]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 19G 4.5G 13G 26% /
tmpfs 931M 0 931M 0% /dev/shm
/dev/sda1 190M 27M 154M 15% /boot
/dev/sdb1 50G 33G 15G 70% /mysqldata
/dev/sdc1 50G 17G 31G 35% /mysqlbackdata
重新尝试启动Mysql，但仍然报PID错误并自动停止进程
[root@CentOS6 mysqldata]# 2017-02-01T15:24:57.688517Z mysqld_safe Logging to ‘/mysqldata/mysql_data/error.log‘.
2017-02-01T15:24:57.741688Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/mysql_data
2017-02-01T15:25:00.558915Z mysqld_safe mysqld from pid file /mysqldata/mysql_data/centos68ip22.pid ended
[1]+ Done mysqld_safe --user=mysql
上网搜索后，有前辈说可能是数据目录权限不够的问题，尝试重新将数据目录分配到mysql.mysql上
[root@CentOS6 mysqldata]# chmod -R 775 /mysql*
[root@CentOS6 mysqldata]# chown -R mysql.mysql /mysql*
再次重新启动，仍然报同样的错
仔细查看error.log
[root@CentOS6 mysqldata]# tail -100 /mysqldata/mysql_data/error.log
2017-02-01T23:36:38.116883+08:00 0 [Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
2017-02-01T23:36:38.116898+08:00 0 [Warning] InnoDB: Using innodb_file_format_max is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
2017-02-01T23:36:38.116911+08:00 0 [Warning] InnoDB: innodb-page-size has been changed from the default value 16384 to 4096.
2017-02-01T23:36:38.116952+08:00 0 [Note] InnoDB: PUNCH HOLE support not available
2017-02-01T23:36:38.116960+08:00 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-02-01T23:36:38.116965+08:00 0 [Note] InnoDB: Uses event mutexes
2017-02-01T23:36:38.116969+08:00 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2017-02-01T23:36:38.116973+08:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-02-01T23:36:38.116977+08:00 0 [Note] InnoDB: Using Linux native AIO
2017-02-01T23:36:38.119994+08:00 0 [Note] InnoDB: Number of pools: 1
2017-02-01T23:36:38.121229+08:00 0 [Note] InnoDB: Using CPU crc32 instructions
2017-02-01T23:36:38.123839+08:00 0 [Note] InnoDB: Initializing buffer pool, total size = 1G, instances = 8, chunk size = 128M
2017-02-01T23:36:38.344790+08:00 0 [Note] InnoDB: Completed initialization of buffer pool
2017-02-01T23:36:38.369054+08:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-02-01T23:36:38.393378+08:00 0 [Note] InnoDB: Opened 3 undo tablespaces
2017-02-01T23:36:38.393403+08:00 0 [Note] InnoDB: 3 undo tablespaces made active
2017-02-01T23:36:38.393580+08:00 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-02-01T23:36:38.527971+08:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-02-01T23:36:38.528076+08:00 0 [Note] InnoDB: Setting file ‘./ibtmp1‘ size to 12 MB. Physically writing the file full; Please wait ...
2017-02-01T23:36:38.583898+08:00 0 [Note] InnoDB: File ‘./ibtmp1‘ size is now 12 MB.
2017-02-01T23:36:38.584479+08:00 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-02-01T23:36:38.584493+08:00 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-02-01T23:36:38.590900+08:00 0 [Note] InnoDB: Waiting for purge to start
2017-02-01T23:36:38.642571+08:00 0 [Note] InnoDB: 5.7.16 started; log sequence number 1885281
2017-02-01T23:36:38.647010+08:00 0 [Note] InnoDB: Loading buffer pool(s) from /mysqldata/mysql_data/ib_buffer_pool
2017-02-01T23:36:38.658256+08:00 0 [Note] Semi-sync replication initialized for transactions.
2017-02-01T23:36:38.658276+08:00 0 [Note] Semi-sync replication enabled on the master.
2017-02-01T23:36:38.659353+08:00 0 [Note] Starting ack receiver thread
2017-02-01T23:36:38.676154+08:00 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-02-01T23:36:38.676183+08:00 0 [Note] Server hostname (bind-address): ‘*‘; port: 3306
2017-02-01T23:36:38.676435+08:00 0 [Note] IPv6 is available.
2017-02-01T23:36:38.676448+08:00 0 [Note] - ‘::‘ resolves to ‘::‘;
2017-02-01T23:36:38.676463+08:00 0 [Note] Server socket created on IP: ‘::‘.
2017-02-01T23:36:38.686057+08:00 0 [Warning] ‘user‘ entry ‘root@localhost‘ ignored in --skip-name-resolve mode.
2017-02-01T23:36:38.686092+08:00 0 [Warning] ‘user‘ entry ‘mysql.sys@localhost‘ ignored in --skip-name-resolve mode.
2017-02-01T23:36:38.686115+08:00 0 [Warning] ‘db‘ entry ‘sys mysql.sys@localhost‘ ignored in --skip-name-resolve mode.
2017-02-01T23:36:38.686125+08:00 0 [Warning] ‘proxies_priv‘ entry ‘@ root@localhost‘ ignored in --skip-name-resolve mode.
2017-02-01T23:36:38.698283+08:00 0 [ERROR] Fatal error: Can‘t open and lock privilege tables: File ‘./mysql/columns_priv.MYD‘ not found (Errcode: 2 - No such file or directory)
2017-02-01T23:36:38.698301+08:00 0 [ERROR] Fatal: can‘t initialize grant subsystem - ‘File ‘./mysql/columns_priv.MYD‘ not found (Errcode: 2 - No such file or directory)‘
2017-02-01T23:36:38.698342+08:00 0 [ERROR] Aborting
2017-02-01T23:36:38.698736+08:00 0 [Note] Binlog end
2017-02-01T23:36:38.703745+08:00 0 [Note] Shutting down plugin ‘rpl_semi_sync_slave‘
2017-02-01T23:36:38.704165+08:00 0 [Note] Shutting down plugin ‘rpl_semi_sync_master‘
2017-02-01T23:36:38.704400+08:00 0 [Note] Stopping ack receiver thread
2017-02-01T23:36:38.704858+08:00 0 [Note] unregister_replicator OK
2017-02-01T23:36:38.704873+08:00 0 [Note] Shutting down plugin ‘ngram‘
2017-02-01T23:36:38.704877+08:00 0 [Note] Shutting down plugin ‘ARCHIVE‘
2017-02-01T23:36:38.704881+08:00 0 [Note] Shutting down plugin ‘FEDERATED‘
2017-02-01T23:36:38.704885+08:00 0 [Note] Shutting down plugin ‘partition‘
2017-02-01T23:36:38.704888+08:00 0 [Note] Shutting down plugin ‘BLACKHOLE‘
2017-02-01T23:36:38.704891+08:00 0 [Note] Shutting down plugin ‘CSV‘
2017-02-01T23:36:38.704897+08:00 0 [Note] Shutting down plugin ‘MEMORY‘
2017-02-01T23:36:38.704901+08:00 0 [Note] Shutting down plugin ‘MRG_MYISAM‘
2017-02-01T23:36:38.704906+08:00 0 [Note] Shutting down plugin ‘INNODB_SYS_VIRTUAL‘
2017-02-01T23:36:38.704910+08:00 0 [Note] Shutting down plugin ‘INNODB_SYS_DATAFILES‘
2017-02-01T23:36:38.704912+08:00 0 [Note] Shutting down plugin ‘INNODB_SYS_TABLESPACES‘
2017-02-01T23:36:38.704915+08:00 0 [Note] Shutting down plugin ‘INNODB_SYS_FOREIGN_COLS‘
2017-02-01T23:36:38.704918+08:00 0 [Note] Shutting down plugin ‘INNODB_SYS_FOREIGN‘
2017-02-01T23:36:38.704921+08:00 0 [Note] Shutting down plugin ‘INNODB_SYS_FIELDS‘
2017-02-01T23:36:38.704923+08:00 0 [Note] Shutting down plugin ‘INNODB_SYS_COLUMNS‘
2017-02-01T23:36:38.704926+08:00 0 [Note] Shutting down plugin ‘INNODB_SYS_INDEXES‘
2017-02-01T23:36:38.704929+08:00 0 [Note] Shutting down plugin ‘INNODB_SYS_TABLESTATS‘
2017-02-01T23:36:38.704932+08:00 0 [Note] Shutting down plugin ‘INNODB_SYS_TABLES‘
2017-02-01T23:36:38.704934+08:00 0 [Note] Shutting down plugin ‘INNODB_FT_INDEX_TABLE‘
2017-02-01T23:36:38.704937+08:00 0 [Note] Shutting down plugin ‘INNODB_FT_INDEX_CACHE‘
2017-02-01T23:36:38.704940+08:00 0 [Note] Shutting down plugin ‘INNODB_FT_CONFIG‘
2017-02-01T23:36:38.704942+08:00 0 [Note] Shutting down plugin ‘INNODB_FT_BEING_DELETED‘
2017-02-01T23:36:38.704945+08:00 0 [Note] Shutting down plugin ‘INNODB_FT_DELETED‘
2017-02-01T23:36:38.704948+08:00 0 [Note] Shutting down plugin ‘INNODB_FT_DEFAULT_STOPWORD‘
2017-02-01T23:36:38.704951+08:00 0 [Note] Shutting down plugin ‘INNODB_METRICS‘
2017-02-01T23:36:38.704954+08:00 0 [Note] Shutting down plugin ‘INNODB_TEMP_TABLE_INFO‘
2017-02-01T23:36:38.705000+08:00 0 [Note] InnoDB: Buffer pool(s) load completed at 170201 23:36:38
2017-02-01T23:36:38.704956+08:00 0 [Note] Shutting down plugin ‘INNODB_BUFFER_POOL_STATS‘
2017-02-01T23:36:38.705051+08:00 0 [Note] Shutting down plugin ‘INNODB_BUFFER_PAGE_LRU‘
2017-02-01T23:36:38.705054+08:00 0 [Note] Shutting down plugin ‘INNODB_BUFFER_PAGE‘
2017-02-01T23:36:38.705057+08:00 0 [Note] Shutting down plugin ‘INNODB_CMP_PER_INDEX_RESET‘
2017-02-01T23:36:38.705060+08:00 0 [Note] Shutting down plugin ‘INNODB_CMP_PER_INDEX‘
2017-02-01T23:36:38.705067+08:00 0 [Note] Shutting down plugin ‘INNODB_CMPMEM_RESET‘
2017-02-01T23:36:38.705070+08:00 0 [Note] Shutting down plugin ‘INNODB_CMPMEM‘
2017-02-01T23:36:38.705072+08:00 0 [Note] Shutting down plugin ‘INNODB_CMP_RESET‘
2017-02-01T23:36:38.705075+08:00 0 [Note] Shutting down plugin ‘INNODB_CMP‘
2017-02-01T23:36:38.705078+08:00 0 [Note] Shutting down plugin ‘INNODB_LOCK_WAITS‘
2017-02-01T23:36:38.705080+08:00 0 [Note] Shutting down plugin ‘INNODB_LOCKS‘
2017-02-01T23:36:38.705083+08:00 0 [Note] Shutting down plugin ‘INNODB_TRX‘
2017-02-01T23:36:38.705086+08:00 0 [Note] Shutting down plugin ‘InnoDB‘
2017-02-01T23:36:38.705308+08:00 0 [Note] InnoDB: FTS optimize thread exiting.
2017-02-01T23:36:38.705967+08:00 0 [Note] InnoDB: Starting shutdown...
2017-02-01T23:36:38.807046+08:00 0 [Note] InnoDB: Dumping buffer pool(s) to /mysqldata/mysql_data/ib_buffer_pool
2017-02-01T23:36:38.810313+08:00 0 [Note] InnoDB: Buffer pool(s) dump completed at 170201 23:36:38
2017-02-01T23:36:40.301342+08:00 0 [Note] InnoDB: Shutdown completed; log sequence number 1885300
2017-02-01T23:36:40.301498+08:00 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017-02-01T23:36:40.301508+08:00 0 [Note] Shutting down plugin ‘PERFORMANCE_SCHEMA‘
2017-02-01T23:36:40.301554+08:00 0 [Note] Shutting down plugin ‘MyISAM‘
2017-02-01T23:36:40.301567+08:00 0 [Note] Shutting down plugin ‘sha256_password‘
2017-02-01T23:36:40.301571+08:00 0 [Note] Shutting down plugin ‘mysql_native_password‘
2017-02-01T23:36:40.301707+08:00 0 [Note] Shutting down plugin ‘binlog‘
2017-02-01T23:36:40.301982+08:00 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
发现了如下两处错误（系统表格丢失）
2017-02-01T23:36:38.698283+08:00 0 [ERROR] Fatal error: Can‘t open and lock privilege tables: File ‘./mysql/columns_priv.MYD‘ not found (Errcode: 2 - No such file or directory)
2017-02-01T23:36:38.698301+08:00 0 [ERROR] Fatal: can‘t initialize grant subsystem - ‘File ‘./mysql/columns_priv.MYD‘ not found (Errcode: 2 - No such file or directory)‘
尝试从原数据目录（之前重命名备份了原来的数据目录）中直接复制这个表到系统制定数据库文件目录中
[root@CentOS6 ~]# find / -name columns_priv.MYD
/mysqldata/temp/mysql/columns_priv.MYD
[root@CentOS6 ~]# cp /mysqldata/temp/mysql/columns_priv.MYD /mysqldata/mysql_data/mysql/
重新尝试启动，查看错误日志发现又有新的文件丢失问题，再次从原目录复制一份过来
[root@CentOS6 ~]# cp /mysqldata/temp/mysql/procs_priv.MYD /mysqldata/mysql_data/mysql/
[root@CentOS6 ~]# chown -R mysql.mysql /mysqldata/mysql_data/mysql/*
启动服务
[root@CentOS6 mysqldata]# mysqld_safe --user=mysql &
[1] 10368
[root@CentOS6 mysqldata]# 2017-02-01T15:47:27.048865Z mysqld_safe Logging to ‘/mysqldata/mysql_data/error.log‘.
2017-02-01T15:47:27.104173Z mysqld_safe Starting mysqld daemon with databases from /mysqldata/mysql_data
[root@CentOS6 mysqldata]# ps -ef|grep mysql
root 10368 2563 0 23:47 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --user=mysql
mysql 11441 10368 0 23:47 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mysqldata/mysql_data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/mysqldata/mysql_data/error.log --pid-file=/mysqldata/mysql_data/centos68ip22.pid --socket=/tmp/mysql.sock --port=3306
root 11474 2563 0 23:49 pts/0 00:00:00 grep mysql
成功启动
本文出自 “FF的网管日记” 博客，请务必保留此出处http://l0vesql.blog.51cto.com/4159433/1894693
记一次xtrabackup全备恢复故障修复