MySQL 非常规恢复与物理备份恢复

    在一金融客户的mysql故障恢复过程中，遇到了这么个问题：客户想在原地、原版本恢复数据库，可以接受少量数据丢失；简言之，就是将原来的数据目录复制过来直接使用。这种恢复对于mysql可以吗？答案是肯定的，但是有地方需要注意。     实验过程如下： --操作系统版本信息如下： [root@mysqlpri mysql]# uname -a Linux mysqlpri 3.10.0-693.el7.x86_64 #1 SMP Tue Aug 22 21:09:27 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux [root@mysqlpri mysql]# cat /etc/redhat-release  CentOS Linux release 7.4.1708 (Core)  [root@mysqlpri mysql]# --mysql进程信息如下 [root@mysqlpri mysql]# ps -ef|grep mysql avahi      587     1  0 05:11 ?        00:00:00 avahi-daemon: running [mysqlpri.local] root     10243     1  0 19:27 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe mysql    10402 10243  2 19:27 ?        00:00:10 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock root     10553 10104  0 19:34 pts/0    00:00:00 grep --color=auto mysql [root@mysqlpri mysql]#        按照客户的意愿：将mysql数据目录从/var/lib/mysql迁移到/u01/data/。 --这里我们将zabbix数据库迁移到/u01/data下 [root@mysqlpri lib]# mysql -uroot -p Enter password:  Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.30-76.3 Percona Server (GPL), Release 76.3, Revision 3850db5 Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | test               | | zabbix             | +--------------------+ 5 rows in set (0.09 sec) mysql> use zabbix Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------------------+ | Tables_in_zabbix           | +----------------------------+ | acknowledges               | | actions                    | | alerts                     | | application_discovery      | | application_prototype      | ........... 140 rows in set (0.00 sec) mysql>  --zabbix要迁移的新环境中是没有zabbix这个数据库的 [root@mysqlpri bin]# mysql -S /u01/data/mysql.sock  Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.30-76.3 Percona Server (GPL), Release 76.3, Revision 3850db5 Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>  mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | test               | +--------------------+ 4 rows in set (0.00 sec) mysql>  --非常规zabbix迁移 [root@mysqlpri data]# cd /var/lib/mysql/ [root@mysqlpri mysql]# ls aria_log.00000001  auto.cnf  ib_logfile0  mysql               RPM_UPGRADE_HISTORY      test aria_log_control   ibdata1   ib_logfile1  performance_schema  RPM_UPGRADE_MARKER-LAST  zabbix [root@mysqlpri mysql]# tar -cvf zabbix.tar zabbix/ zabbix/ zabbix/httptest_field.frm zabbix/graph_theme.frm zabbix/opmessage_grp.frm zabbix/httpstep.frm ...... zabbix/sysmap_element_trigger.frm zabbix/sysmap_element_url.frm [root@mysqlpri mysql]# ls aria_log.00000001  auto.cnf  ib_logfile0  mysql               RPM_UPGRADE_HISTORY      test    zabbix.tar aria_log_control   ibdata1   ib_logfile1  performance_schema  RPM_UPGRADE_MARKER-LAST  zabbix [root@mysqlpri mysql]# mv zabbix.tar /u01/data/ [root@mysqlpri mysql]# cd /u01/data/ [root@mysqlpri data]# ls auto.cnf  ibdata1  ib_logfile0  ib_logfile1  my.cnf  mysql  mysqlpri.pid  mysql.sock  performance_schema  test  zabbix.tar [root@mysqlpri data]# tar -xvf zabbix.tar  [root@mysqlpri data]# chown -R mysql.mysql zabbix [root@mysqlpri data]# chmod 755 zabbix [root@mysqlpri data]#  --此时在新环境中查看zabbix是可以查看的 mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | test               | | zabbix             | +--------------------+ 5 rows in set (0.00 sec) --但是，一旦use查看zabbix中的表就会有错误提示，原先的旧环境中查看是没有任何问题的 mysql> use zabbixReading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A 2019-04-30 20:09:42 11461 [Warning] InnoDB: Cannot open table zabbix/acknowledges from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2019-04-30 20:11:29 11638 [Warning] InnoDB: Cannot open table zabbix/widget_field from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. Database changed --通过show tables也能查看zabbix中的数据表 mysql> show tables; +----------------------------+ | Tables_in_zabbix           | +----------------------------+ | acknowledges               | ........... | widget_field               | +----------------------------+ 140 rows in set (0.00 sec) mysql>  --如果查看zabbix中的数据表中的记录就完全不行了 mysql> select * from users; 2019-04-30 20:13:40 11638 [Warning] InnoDB: Cannot open table zabbix/users from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. ERROR 1146 (42S02): Table 'zabbix.users' doesn't exist mysql>      其实，需要将数据库zabbix文件目录上层的三个元数据文件、日志文件复制过来 [root@mysqlpri mysql]# su - mysql Last login: Wed Nov  7 16:02:49 CST 2018 from 192.168.192.1 on pts/3 [mysql@mysqlpri ~]$ cd /var/lib/mysql/ [mysql@mysqlpri mysql]$ ll total 395316 -rw-rw----. 1 mysql mysql     16384 Sep 25  2018 aria_log.00000001 -rw-rw----. 1 mysql mysql        52 Sep 25  2018 aria_log_control -rw-rw----  1 mysql mysql        56 Mar  8 16:57 auto.cnf -rw-rw----. 1 mysql mysql 304087040 Apr 30 19:57 ibdata1 -rw-rw----  1 mysql mysql  50331648 Apr 30 19:57 ib_logfile0 -rw-rw----  1 mysql mysql  50331648 Mar  8 18:00 ib_logfile1 drwx------. 2 mysql mysql      4096 Apr 30 19:55 mysql drwx------  2 mysql mysql      4096 Apr 30 19:57 performance_schema -rw-r--r--  1 root  root        288 Nov  4 20:43 RPM_UPGRADE_HISTORY -rw-r--r--  1 mysql mysql       288 Nov  4 20:43 RPM_UPGRADE_MARKER-LAST drwx------. 2 mysql mysql      4096 Mar  8 17:45 test drwx------. 2 mysql mysql      4096 May  6  2018 zabbix [mysql@mysqlpri mysql]$ mv ib* /u01/data/ [mysql@mysqlpri mysql]$  --复制innodb元数据 innodb_table_stats.frm到新环境下 [mysql@mysqlpri mysql]$pwd /var/lib/mysql/mysql [mysql@mysqlpri mysql]$ mv innodb_table_stats. innodb_table_stats.frm  innodb_table_stats.ibd   [mysql@mysqlpri mysql]$ cp innodb_table_stats.frm   /u01/data/mysql/ [mysql@mysqlpri mysql]$cd   /u01/data/mysql/ [mysql@mysqlpri mysql]$ ls -l innodb_table_stats* -rw-rw---- 1 mysql mysql  8830 Apr 30 19:55 innodb_table_stats.frm [mysql@mysqlpri mysql]$      再次重新启动Mysql服务 [root@mysqlpri bin]# /usr/sbin/mysqld --defaults-file=/u01/data/my.cnf & [1] 11993 [root@mysqlpri bin]#  [root@mysqlpri mysql]# mysql -uroot -S /u01/data//mysql.sock Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.30-76.3 Percona Server (GPL), Release 76.3, Revision 3850db5 Copyright (c) 2009-2016 Percona LLC and/or its affiliates Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use zabbix Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql>  mysql> show tables; +----------------------------+ | Tables_in_zabbix           | +----------------------------+ | acknowledges               | ........... | users                      | | users_groups               | | usrgrp                     | | valuemaps                  | | widget                     | | widget_field               | +----------------------------+ 140 rows in set (0.00 sec) mysql>  mysql> select * from users \G *************************** 1. row ***************************         userid: 1          alias: Admin           name: Zabbix        surname: Administrator         passwd: 5fce1b3e34b520afeffb37ce08c7cd66            url:       autologin: 1     autologout: 0           lang: en_GB        refresh: 30s           type: 3          theme: default attempt_failed: 0     attempt_ip:   attempt_clock: 0  rows_per_page: 50 *************************** 2. row ***************************         userid: 2          alias: guest           name:         surname:          passwd: d41d8cd98f00b204e9800998ecf8427e            url:       autologin: 0     autologout: 15m           lang: en_GB        refresh: 30s           type: 1          theme: default attempt_failed: 0     attempt_ip:   attempt_clock: 0  rows_per_page: 50 2 rows in set (0.00 sec) mysql>  mysql> show global variables like '%file%'; +---------------------------------------+-----------------------------+ | Variable_name                         | Value                       | +---------------------------------------+-----------------------------+ | character_set_filesystem              | binary                      | | core_file                             | OFF                         | | ft_stopword_file                      | (built-in)                  | | general_log_file                      | /u01/data/mysqlpri.log      | | init_file                             |                             | | innodb_buffer_pool_filename           | ib_buffer_pool              | | innodb_data_file_path                 | ibdata1:12M:autoextend      | | innodb_disable_sort_file_cache        | OFF                         | | innodb_file_format                    | Antelope                    | | innodb_file_format_check              | ON                          | | innodb_file_format_max                | Antelope                    | | innodb_file_per_table                 | ON                          | | innodb_log_file_size                  | 50331648                    | | innodb_log_files_in_group             | 2                           | | innodb_max_bitmap_file_size           | 104857600                   | | innodb_open_files                     | 2000                        | | keep_files_on_create                  | OFF                         | | large_files_support                   | ON                          | | local_infile                          | ON                          | | lower_case_file_system                | OFF                         | | max_binlog_files                      | 0                           | | max_slowlog_files                     | 0                           | | myisam_max_sort_file_size             | 9223372036853727232         | | open_files_limit                      | 5000                        | | performance_schema_max_file_classes   | 50                          | | performance_schema_max_file_handles   | 32768                       | | performance_schema_max_file_instances | 7693                        | | pid_file                              | /u01/data/mysqlpri.pid      | | relay_log_info_file                   | relay-log.info              | | secure_file_priv                      |                             | | slow_query_log_file                   | /u01/data/mysqlpri-slow.log | +---------------------------------------+-----------------------------+ 31 rows in set (0.00 sec) mysql>  到此为止，mysql的非常规恢复就完成了，但是，有个前提：能够获取到原mysql数据库中相关的数据库文件。