记一次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

成功启动

 本文转自 angry_frog 51CTO博客，原文链接:http://blog.51cto.com/l0vesql/1894693