mysql数据库逻辑备份与恢复_Mysqldump逻辑备份与恢复

川长思鸟来 2022-10-27 05:19 285阅读 0赞

文档结构：

![b894eede0f394ee135c67e119a3d12dd.png][]

mysqldump备份影响性能，可能会把内存里面的热数据给冲刷掉，5.7后，新增一个参数，innodb\_buffer\_pool\_dump\_pct，控制每个innodb\_buffer中转存活跃的使用innodb buffer pages的比例，只有当数据在1s内再次被访问时，才能放到热区域内，避免热数据被刷掉，默认值25%。

![a0d1be46d77f99f295e3dee1f7805631.png][]

重要的参数说明：

\--single-transaction

用于保证innodb 备份数据时的一致性，配合RR隔离级别一起使用；当发起事物时，读取一个事实的快照，直到备份结束时，都不会读取到本事物开始之前提交的任何数据(这个参数相当重要)

\--all-databases  (-A)

备份所有数据库。

\--master-data

该参数有1和2，如果等于1 ，就会在备份出来的文件中添加一个change master的语句(后期配置搭建主从架构)；如果值等于2，就会在备份出来的文件中添加一个change master语句，并在语句前面添加注释符号(后期配置搭建主从架构)。

\--dump-slave

该参数用于从库端备份数据，在线搭建新的从库时使用。

该参数也有1,2两个值，值为1是，也是在备份出来的文件中添加一个change master的语句；值为2时，则会在change master命令前增加注释信息。

\--no-create-info(-t)

备份过程中，只备份表数据，并不备份表结构。

\--no-data

备份过程中，只备份表结构，并不备份表数据。

\--complete-insert

使用完整的insert语句会包含表中的列信息，这么做可以提高插入效率。

\--databases

备份多个数据库。

mysqldump -S /tmp/mysql3307.sock -uroot -pmysql --set-gtid-purged=OFF --databases sys test > sys\_test.sql

\--default-character-set

字符集，MYSQL目前默认字符集，要与备份出的表的字符集保持一致。

\--quick

相当于加 sql\_no\_query,意味着并不会读取缓存中的数据。

\--where=name

按条件备份出想要的数据。

备份所有数据库

![ec9e506fdeeabbccaf15ccda403d481d.png][]

/usr/local/mysql5.7/bin/mysqldump --single-transaction -S /tmp/mysql3307.sock --set-gtid-purged=OFF -uroot -pmysql -A >all\_20180524.sql

5.7已经开启了GTID,备份过程中不想带GTID信息，加上--set-gtid-purged=OFF

恢复全库的过程

先删除test 测试库

![d58d8ae88edc21a0baea462c0a4c7df3.png][]

mysql -S /tmp/mysql3307.sock -uroot -pmysql < all\_20180524.sql

![549dad65be1809a6a4379674f52f3d15.png][]

查看恢复后的数据库：

![a5a6139f2160a18da4703490aa06f9e8.png][]

![6427127622b9459fa4fab21fc5a2ae23.png][]

备份单个数据库test的过程：

mysqldump -S /tmp/mysql3307.sock -uroot -pmysql --single-transaction --set-gtid-purged=OFF  test > 20180524test.sql

![3600a12d9518c8c821da1c90348248a3.png][]

恢复单库test的过程：

mysql> show databases;

\+--------------------+

| Database           |

\+--------------------+

| information\_schema |

| mysql              |

| performance\_schema |

| sys                |

| test               |

\+--------------------+

5 rows in set (0.00 sec)

mysql> drop database test;

Query OK, 9 rows affected (0.18 sec)

mysql> show databases;

\+--------------------+

| Database           |

\+--------------------+

| information\_schema |

| mysql              |

| performance\_schema |

| sys                |

\+--------------------+

4 rows in set (0.00 sec)

mysql> create database test;

Query OK, 1 row affected (0.00 sec)

mysql -S /tmp/mysql3307.sock -uroot -pmysql test < 20180524test.sql

![2640415957d8241f5de46633ea46a3c7.png][]

mysql> use test;

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\_test |

\+----------------+

| t              |

| t1             |

| t2             |

| t3             |

| t4             |

| tt             |

| ttt            |

| zs             |

| zs1            |

\+----------------+

9 rows in set (0.00 sec)

备份单表：

mysql> select TABLE\_SCHEMA,TABLE\_NAME,TABLE\_TYPE,ENGINE,TABLE\_ROWS from information\_schema.TABLES where table\_schema='test';

\+--------------+------------+------------+--------+------------+

\+--------------+------------+------------+--------+------------+

\+--------------+------------+------------+--------+------------+

9 rows in set (0.00 sec)

![e7f12889e716d22ba712bbab1c1ab1d3.png][]

mysqldump -S /tmp/mysql3307.sock -uroot -pmysql --single-transaction --set-gtid-purged=OFF test t1 >20180524\_t1.sql

恢复表的过程：

先删除，在恢复。

![a0aa99f0796254f356fadc7ff5a4606f.png][]

mysql -S /tmp/mysql3307.sock -uroot -pmysql test < 20180524\_t1.sql

![cf4824282f38114230b0cb4888f127f3.png][]

注意：

单表恢复的时候，不需要写表的名字，只需要写库的名字。

备份test库t1表的where 条件

mysqldump -S /tmp/mysql3307.sock -uroot -pmysql --single-transaction --set-gtid-purged=OFF test t1 --where='RECEIVETIME >="2018-08-31 00:00:00" and  RECEIVETIME <="2018-10-09 00:00:00"' >/data\_returnreport.sql

备份test库t1表的结构

mysqldump -S /tmp/mysql3307.sock -uroot -pmysql --single-transaction --set-gtid-purged=OFF -d test t1 > 20180504t1\_meta.sql

![b85407f6d674c5c3d375993ad760d849.png][]

或者

mysqldump -S /tmp/mysql3307.sock -uroot -pmysql --single-transaction --set-gtid-purged=OFF --no-data test t1 > 20180504t1\_meta01.sql

![56f2c5e37414257a5dc7c8e6dae7bba1.png][]

把-d 换成--no-date

备份test库中t表中数据信息：

mysqldump -S /tmp/mysql3307.sock -uroot -pmysql --single-transaction --set-gtid-purged=OFF -t test t1 >20180524t1\_data.sql

![a5ed2791b85119ec724e5fd923a41a88.png][]

从表结构备份和表数据备份中恢复单表

![33216d13ddffc26430cd4c65ca654bb0.png][]

先恢复表结构，在往里面导数据

![306b3f497c906f82165092a916dc5d6a.png][]

查看表结构：

![3273c375b8892f41a38e91eebe3143df.png][]

恢复数据：

mysql -S /tmp/mysql3307.sock -uroot -pmysql test <20180524t1\_data.sql

![d6748b399df5ba6b80bf454eb90ae052.png][]

备份test库指定条件数据

mysqldump -S /tmp/mysql3307.sock -uroot -pmysql --single-transaction --set-gtid-purged=OFF test t1 --where="id>45000" >20180524\_t\_part.sql

![be01fe437917c4830d3a3d5ae7007dee.png][]

![72637157a55ab55fd9fc7b059e7c51af.png][]

注意：

where 后面建议为双引号，以防止不识别条件。

查看备份文件：

![7bf0684484343326b5a4681ce06eb0a7.png][]

压缩备份：

当数据库或者表比较大的时候，可以在备份的时候压缩成tar文件：

mysqldump -uroot -ppassword abc | gzip > db\_abc.sql.tar.gz

mysqldump -S /tmp/mysql3307.sock -uroot -pmysql --set-gtid-purged=OFF --databases  test | gzip >  /root/backuptest.sql.tar.gz

恢复的时候：

gunzip

[b894eede0f394ee135c67e119a3d12dd.png]: /images/20221024/3a578ac8d9a3405c929fda7380edd67d.png
[a0d1be46d77f99f295e3dee1f7805631.png]: /images/20221024/6048f32ac028458f9d91099238cf2880.png
[ec9e506fdeeabbccaf15ccda403d481d.png]: /images/20221024/fec0c609e50543a3a407cd83fbdcf458.png
[d58d8ae88edc21a0baea462c0a4c7df3.png]: /images/20221024/e00665de76f04ec2833728f415ec66bd.png
[549dad65be1809a6a4379674f52f3d15.png]: /images/20221024/c06aac97b8d74797ba304102c05ab616.png
[a5a6139f2160a18da4703490aa06f9e8.png]: /images/20221024/4014ba0d159448ab9e13644b8c199bb2.png
[6427127622b9459fa4fab21fc5a2ae23.png]: /images/20221024/1db6d893114e4e969422277413f7a3f3.png
[3600a12d9518c8c821da1c90348248a3.png]: /images/20221024/b99f7d1cde9348b4a5ce9cd14fbe1e4e.png
[2640415957d8241f5de46633ea46a3c7.png]: /images/20221024/d28a0fcc3d974c61ae9da06f200cd826.png
[e7f12889e716d22ba712bbab1c1ab1d3.png]: /images/20221024/99ae7f24c62142718a3c4dc9b7f74b0c.png
[a0aa99f0796254f356fadc7ff5a4606f.png]: /images/20221024/919b91521f154465be46df49d1ee36ae.png
[cf4824282f38114230b0cb4888f127f3.png]: /images/20221024/7ed4315781924307954bf245df789e9a.png
[b85407f6d674c5c3d375993ad760d849.png]: /images/20221024/e15e3a5e44614ecc81c2fe70c39ac3c6.png
[56f2c5e37414257a5dc7c8e6dae7bba1.png]: /images/20221024/abfd950d3f904d778be3df01a2467931.png
[a5ed2791b85119ec724e5fd923a41a88.png]: /images/20221024/54b7cdca0b1f4d018681a382ad59efd2.png
[33216d13ddffc26430cd4c65ca654bb0.png]: /images/20221024/0e106d8113e44f1ab7aa32f84b224d8d.png
[306b3f497c906f82165092a916dc5d6a.png]: /images/20221024/f2339fa10a224df8b0d69998f735348e.png
[3273c375b8892f41a38e91eebe3143df.png]: /images/20221024/88c2d9a3287f4ceb8065a35a719bd859.png
[d6748b399df5ba6b80bf454eb90ae052.png]: /images/20221024/ec003e3cac3e4d1e8faab1330cd3621c.png
[be01fe437917c4830d3a3d5ae7007dee.png]: /images/20221024/ded1fa1ff1a4460eab2cb50e1b03ad4b.png
[72637157a55ab55fd9fc7b059e7c51af.png]: /images/20221024/1aec431d179340339e2ba4047b34c2a9.png
[7bf0684484343326b5a4681ce06eb0a7.png]: /images/20221024/59cf80b5cbbd4f599dbefdcb95b17cc5.png