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資料庫中相關的資料庫檔案。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29357786/viewspace-2643057/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- Mysql備份與恢復(1)---物理備份MySql
- MySQL備份與恢復MySql
- Mysql的備份與恢復MySql
- Mysql 備份與恢復 二MySql
- Mysql備份恢復MySql
- mysql 備份恢復MySql
- 詳解叢集級備份恢復：物理細粒度備份恢復
- 物理冷備份與恢復的操作命令
- MySQL備份與恢復——基於MyDumper/MyLoader 邏輯備份恢復MySql
- 【物理熱備】（下）備份恢復系統表空間 手工備份恢復
- 入門MySQL——備份與恢復MySql
- 《入門MySQL—備份與恢復》MySql
- MySQL備份與恢復操作解析MySql
- Mysql資料備份與恢復MySql
- MySQL備份與恢復——實操MySql
- MySQL備份與恢復簡介MySql
- Effective MySQL之備份與恢復MySql
- 備份與恢復：polardb資料庫備份與恢復資料庫
- MySQL備份與恢復——基於OUTFILE /LOAD DATA 邏輯備份恢復MySql
- Mysql備份和恢復MySql
- 備份與恢復--利用備份的控制檔案恢復
- Mysql備份與恢復(2)---邏輯備份MySql
- 【MySQL】MySQL備份和恢復MySql
- 備份與恢復系列 十一 控制檔案的備份與恢復
- 備份與恢復（Parameter 檔案恢復篇）
- Oracle備份恢復之熱備份恢復及異機恢復Oracle
- MySQL入門--備份與恢復（一）MySql
- MySQL入門--備份與恢復（二）MySql
- MySQL入門--備份與恢復（三）MySql
- Mysql的幾種備份與恢復MySql
- MySQL增量備份與恢復例項MySql
- mysql簇備份與恢復(轉載)MySql
- OceanBase物理備份恢復實踐
- 【備份恢復】Oracle 資料備份與恢復微實踐Oracle
- Postgresql 備份與恢復SQL
- 【備份與恢復】控制檔案的恢復（不完全恢復）
- 【備份恢復】從備份恢復資料庫資料庫