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 state