在一金融客戶的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

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

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

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資料庫中相關的資料庫檔案。

MySQL 非常規恢復與物理備份恢復

相關文章