mysql物理备份步骤

原库10.153.88.5,新建数据库实例10.153.88.6,注意/etc/my.cnf配置和88.5一致,测试目的是通过copy数据文件到88.6来恢复数据库。

在数据库10.153.88.5打包数据文件

[mysql@t3-dtpoc-dtpoc-web04 mysql]$ cd /testdata/mysql
[mysql@t3-dtpoc-dtpoc-web04 mysql]$ tar zcf /home/mysql/liys/mysql_backup.tar.gz ./
[mysql@t3-dtpoc-dtpoc-web04 mysql]$ cd /home/mysql/liys/
[mysql@t3-dtpoc-dtpoc-web04 liys]$ ls
mysql_backup.tar.gz 

传输到目标库10.153.88.6
scp mysql_backup.tar.gz mysql@10.153.88.6:/testdata/mysql

在10.153.88.6解压并覆盖数据文件
[mysql@t3-dtpoc-dtpoc-web05 mysql]$ tar -xvf mysql_backup.tar.gz
[mysql@t3-dtpoc-dtpoc-web05 mysql]$ ls -ltr
total 213368
drwx------ 2 mysql mysql     8192 Jul 13 10:10 performance_schema
-rw------- 1 mysql mysql       56 Jul 13 10:10 auto.cnf
drwx------ 2 mysql mysql     8192 Jul 13 10:10 sys
-rw------- 1 mysql mysql      398 Jul 19 15:28 t3-dtpoc-dtpoc-web04-slow.log
drwx------ 3 mysql mysql       17 Aug 18 16:50 log
drwx------ 2 mysql mysql       48 Aug 22 14:06 mytest
drwx------ 2 mysql mysql       61 Aug 22 14:48 backup
-rw------- 1 mysql mysql      572 Aug 23 16:11 ib_buffer_pool
-rw------- 1 mysql mysql        8 Aug 23 16:11 mysql.pid
-rw------- 1 mysql mysql 12582912 Sep  4 15:18 ibtmp1
-rw------- 1 mysql mysql    98304 Sep  4 15:27 myspacetest.ibd
drwx------ 2 mysql mysql     4096 Sep  4 15:30 mysql
-rw------- 1 mysql mysql 79691776 Sep  4 15:31 ibdata1
-rw------- 1 mysql mysql 50331648 Sep  4 15:31 ib_logfile1
-rw------- 1 mysql mysql 50331648 Sep  4 15:31 ib_logfile0
-rw------- 1 mysql mysql   159957 Sep  5 00:47 mysql.err
-rwx------ 1 mysql mysql 25240776 Sep  5 16:50 mysql_backup.tar.gz

在10.153.88.6重启数据库实例:
[mysql@t3-dtpoc-dtpoc-web05 mysql]$ service mysql restart
 ERROR! MySQL server process #3299579 is not running!
Starting MySQL. SUCCESS! 
[mysql@t3-dtpoc-dtpoc-web05 bin]$ ./mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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库下employee表完全恢复了,mytest库的表也恢复了。
[root@localhost:(none)]>use mysql;
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
[root@localhost:mysql]>select * from employee;
+----+------------+
| id | name       |
+----+------------+
|  1 | sff        |
|  3 | fhjl       |
|  6 | woaizhogng |
| 32 | asfsf      |
| 33 | asfsf      |
| 34 | asfsf      |
+----+------------+
6 rows in set (0.00 sec)

[root@localhost:mysql]>use mytest;
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
[root@localhost:mytest]>select * from t1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaoming |
|    2 | xiaohong |
|    3 | xiaoli   |
|    3 | xiaoli   |
|    4 | xiaozhao |
+------+----------+
5 rows in set (0.00 sec)

[root@localhost:mytest]>

这和DB2的物理备份是一样的,当一个DB2数据库数据库很大时,比如几十T,通过数据库的在线热备是非常耗时的,这个适合可以通过操作系统的快照功能,对db2的实例目录/home/db2inst1,数据库目录DBPATH(一般就是实例目录),数据目录(表空间文件)进行磁盘快照备份。如果磁盘损坏发生,可以通过磁盘快照来恢复数据。当然也可以建立一个新的DB2实例环境,创建一个空的db2inst1实例,然后保持/etc/services里DB2端口监控配置和原库一致,保持重要的系统变量比如其他重要的内存内核参数:
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
kernel.shmmni = 129280

和原库一致,把磁盘快照mount到新环境下,就可以起数据库实例了,能进行正常的读写,和本文的Mysql物理备份原理是一样的