在一金融客户的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数据库中相关的数据库文件。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29357786/viewspace-2643057/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29357786/viewspace-2643057/