MySQL的备份和恢复-mysqldump-白红宇

MySQL的备份和恢复-mysqldump

阅读量：6503 次

发布时间：2019-06-24

本文共 11484 字，大约阅读时间需要 38 分钟。

　　　　　　　　　　　　　　　　　　　　　　MySQL的备份和恢复-mysqldump

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作者：尹正杰

一.为什么需要备份

1>.做灾难恢复

　　天有不测风云嘛，如果你的服务器被黑客攻击了（比如现在比较流行的比特币病毒），或是你的新同事手一哆嗦不小心删除了一个生产上的数据库，你们boss找你们技术总监解决问题，你发现你的新同事时跑路了，这个时候你就得感激你的备份啦！

2>.做审计

　　比如一个开发想要知道某一个数据在过去的某一时刻是什么样子的，这个时候你的备份就又一次可以大显身手啦！

3>.测试

　　比如MySQL想从5.1升级到5.5版本。当你升级的时候失败时，你可以进行恢复操作等等。

　　备份的目的是用于恢复，对备份数据做恢复测试是很有必要的。这样你参会知道你的备份内容是否有效，如果发现备份的内容无效的话，赶紧重新备份，不然如果生成环境哪天真出了事情，将会带来不可挽回的地步哟！尤其是电商平台，备份的相当有必要的！如果哪天数据库奔溃了你无法恢复这不仅仅意味着你的饭碗会丢，还会导致公司造成巨额损失。

二.备份类型

1>.根据备份时，数据库服务器是否在线

　　a>.冷备(cold backup)

　　　　需要服务器离线，这就意味着数据的读写操作都不能进行啦，对运维来讲，这是最安全的备份方式但也是最不靠谱的备份方式哟！不能因为备份就让服务下线，这样会映像客户体验的，你想想，淘宝网如果说采用这种方式，那全天下的妹纸们不都得找马云撒娇去啦！

　　b>.温备(warm backup)

　　　　全局施加共享锁，只能读，不能写。这种方式服务器不需要离线，只是限制了写操作。

　　c>.热备(hot backup)

　　　　服务器不需要离线，用户也可以进行读写操作。这种备份方式不会影响正常的业务，且保证数据的备份的时间点都是一致的。这种备份方式需要基于事物的支持哟！

2>.根据备份的数据集：

　　a>.完全备份（full backup）

　　　　它会备份整个数据库。

　　b>.部分备份（partial backup）

　　　　它可能只会备份某个库的部分数据，比如某张表或是一张表中的部分数据。

3>.根据备份时的接口（直接备份数据文件还是通过mysql服务器导出数据）

　　a>.物理备份（Physical backup）

　　　　直接复制（归档）数据文件的备份方式.说白了这种方式就是一个复制操作，我们只要使用tar命令和cp命令来协助我们完成本分即可。因此它不需要其他工具来帮助我们来备份。恢复起来也很简单，直接将数据解压到数据目录中去即可，但是可能存在版本兼容的问题（和存储引擎有关）哟，因此这种跨平台方式就相对逻辑备份要查很多呢。如果数据量大的话，这个时候逻辑备份的效率就会显得很低，因此建议使用物理备份。

　　b>.逻辑备份（Logical backup）

　　　　把数据从库中提取出来保存为文本文件.我们可以使用mysqldump来进行备份。

　　　　逻辑备份的优点：可以基于网络恢复，且我们可以用cat，grep等命令来查看文件的内容。这种方式备份跟存储引擎无关，我们可以通过sed命令对这个文本文件进行修改默认的存储引擎等等，有助于备份数据损坏。

　　　　逻辑备份的缺点：它恢复速度回比物理备份要满，且占用空间相对较大。最致命的一点就是无法完整保存浮点数的精度，更重要的是基于这种恢复方式需要你自己重建索引，如果恢复的数据较多时，重建索引又是一个令人头疼的问题

4>.根据备份时是备份整个数据还是仅备份变化的数据

　　a>.完全备份(full backup)

　　　　顾名思义，将整个数据库都备份出来。建议每周做一次完全备份。

　　b>.增量备份(incremental backup)

　　　　我们使用这种方式较多一点，和上一次备份对比，只备份修改过的内容。建议每天做一次增量备份。优点就是占用空间小。

　　c>.差异备份(differential backup)

　　　　从上一个完全备份开始备份,当我们想要恢复数据时，只需要最近一天的备份即可，很明显，差异备份占据的空间肯定是要比增量备份要大的。

三.备份策略

　　备份策略我们可以通过以下几个角度去思考：

　　　　1>.选择备份方式(推荐物理备份)

　　　　2>.选择备份时间（推荐凌晨2~3点）

　　　　3>.考虑到恢复成本（需要考虑恢复时长）

　　　　4>.考虑备份成本（锁时间，备份时长，备份负载）

四.备份对象

1>.数据；

2>.MySQL的配置文件；

3>.代码（存储过程、存储函数、触发器）；

4>.OS相关的配置文件，如crontab配置计划及相关脚本；

5>.跟复制相关的配置信息（二进制日志文件，尤其是主从同步的场景）；

五.常用的备份工具简介

1>.mysqldump（单线程）

　　它是一个逻辑备份工具，可以对不同的存储引擎实现不同的备份机制，如，支持：InnoDB热备、MyISAM温备、Aria温备。备份和恢复过程较慢，因为它是单线程的备份工具。数据大于10G以上不建议使用这个备份工具，因为效率太低啦！

2>.mysqldumper

　　它也是一个逻辑备份工具，相比mysqldump而言，它用时更短，你可以理解它是多线程的mysqldump。但是很难实现差异或增量备份

3>.基于lvm-snapshot

　　接近于热备的工具，因为要先请求全局锁，而后创建快照，并在创建完成后释放全局锁，使用cp、tar等工具进行物理备份。它的特点是备份和恢复速度较快，但是缺点就是很难实现增量备份，并且请求全局锁需要等待一段时间，在繁忙的服务器上尤其如此。

4>. 部分备份工具

　　SELECT clause INTO OUTFILE ‘/path/to/somefile’ #将数据备份到本地文件

　　LOAD DATA INFILE ‘/path/from/somefile’ #将数据从本地文件中恢复回来

　　它是个逻辑备份工具，快于mysqldump，部分备份工具，不会备份关系定义，仅备份表中的数据.无法实现增量备份。

5>.Innobase

　　它是商业备份工具，innobackup。

6>.Xtarbackup

　　由Percona提供的开源备份工具。支持InnoDB热备且支持增量备份，MyISM温备且不支持增量备份。它属于物理备份类型，备份和恢复的速度很快。

7>.mysqlhotcopy

　　这是MySQL自带的工具，很遗憾的是它几乎冷备工具。因此它用的很少。

六.mysqldump的应用

　　mysqldump其实就是MySQL客户端的一个工具，也就是说，你的MySQL服务器和你的mysqldump可以在不同的主机上，从而实现远程备份的方式。适合备份较小的数据，如果数据内容在5~10G之间的话，采用mysqldump的本分方式是再好不过的啦。

1.备份单个数据库使用格式，恢复时需要提前创建数据库

　　mysqldump [options] [db_name [tbl_name ...]]

　常用参数：

　　　　-u uername

　　　　-h 主机名

　　　　-p 密码

1 [root@yinzhengjie ~]# mkdir -p /yinzhengjie/mysql/backup        #创建备份的数据库目录  2 [root@yinzhengjie ~]# mysqldump -uroot -h127.0.0.1 -p yinzhengjie > /yinzhengjie/mysql/backup/yinzhengjie.sql                    #将数据库的“yinzhengjie”这个库进行备份  3 Enter password:   4 [root@yinzhengjie ~]#   5 [root@yinzhengjie ~]# ls -l /yinzhengjie/mysql/backup/  6 total 8  7 -rw-r--r--. 1 root root 5000 Nov 25 05:16 yinzhengjie.sql  8 [root@yinzhengjie ~]#   9 [root@yinzhengjie ~]# mysql -pyinzhengjie                #登录数据库，我们来搞事情 10 Welcome to the MySQL monitor.  Commands end with ; or \g. 11 Your MySQL connection id is 44 12 Server version: 5.1.73 Source distribution 13  14 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 15  16 Oracle is a registered trademark of Oracle Corporation and/or its 17 affiliates. Other names may be trademarks of their respective 18 owners. 19  20 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 21  22 mysql>  23 mysql> use yinzhengjie 24 Reading table information for completion of table and column names 25 You can turn off this feature to get a quicker startup with -A 26  27 Database changed 28 mysql>  29 mysql> select database(); 30 +-------------+ 31 | database()  | 32 +-------------+ 33 | yinzhengjie | 34 +-------------+ 35 1 row in set (0.00 sec) 36  37 mysql> show tables; 38 +-----------------------+ 39 | Tables_in_yinzhengjie | 40 +-----------------------+ 41 | Classes               | 42 | students              | 43 | tearchers             | 44 +-----------------------+ 45 3 rows in set (0.00 sec) 46  47 mysql> drop database yinzhengjie; 48 Query OK, 3 rows affected (0.48 sec) 49  50 mysql> show databases; 51 +--------------------+ 52 | Database           | 53 +--------------------+ 54 | information_schema | 55 | mysql              | 56 | test               | 57 +--------------------+ 58 3 rows in set (0.00 sec) 59  60 mysql> \q 61 Bye 62 [root@yinzhengjie ~]#  63 [root@yinzhengjie ~]# mysql -pyinzhengjie -e "show databases;" 64 +--------------------+ 65 | Database           | 66 +--------------------+ 67 | information_schema | 68 | mysql              | 69 | test               | 70 +--------------------+ 71 [root@yinzhengjie ~]# mysql -pyinzhengjie -e "create database yinzhengjie;"    #我们现在要做的是创建出库名，方便我们一会导入。 72 [root@yinzhengjie ~]# mysql -pyinzhengjie -e "show databases;" 73 +--------------------+ 74 | Database           | 75 +--------------------+ 76 | information_schema | 77 | mysql              | 78 | test               | 79 | yinzhengjie        | 80 +--------------------+ 81 [root@yinzhengjie ~]#  82 [root@yinzhengjie ~]# mysql -pyinzhengjie yinzhengjie < /yinzhengjie/mysql/backup/yinzhengjie.sql          #这里是进行恢复数据的操作，注意，需要指定库名哟！ 83 [root@yinzhengjie ~]# mysql -pyinzhengjie 84 Welcome to the MySQL monitor.  Commands end with ; or \g. 85 Your MySQL connection id is 50 86 Server version: 5.1.73 Source distribution 87  88 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 89  90 Oracle is a registered trademark of Oracle Corporation and/or its 91 affiliates. Other names may be trademarks of their respective 92 owners. 93  94 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 95  96 mysql> use yinzhengjie 97 Reading table information for completion of table and column names 98 You can turn off this feature to get a quicker startup with -A 99 100 Database changed101 mysql> show tables;102 +-----------------------+103 | Tables_in_yinzhengjie |104 +-----------------------+105 | Classes               |106 | students              |107 | tearchers             |108 +-----------------------+109 3 rows in set (0.00 sec)110 111 mysql>

2.备份所有的数据库

　　需要用到参数： --all-databases

1 [root@yinzhengjie ~]# mysqldump -uroot -h127.0.0.1 -p --all-databases > /yinzhengjie/mysql/backup/all_databases.sql2 Enter password: 3 -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.4 [root@yinzhengjie ~]# ll /yinzhengjie/mysql/backup/5 total 5286 -rw-r--r--. 1 root root 531738 Nov 25 17:45 all_databases.sql7 -rw-r--r--. 1 root root   5000 Nov 25 05:16 yinzhengjie.sql8 [root@yinzhengjie ~]#

3.备份指定的数据库

1 需要用到的参数：--databases 2 [root@yinzhengjie ~]# mysqldump -uroot -h127.0.0.1 -p --databases mysql yinzhengjie > /yinzhengjie/mysql/backup/custom.sql     #只备份mysql和yinzhengjie这两个数据库 3 Enter password:  4 -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly. 5 [root@yinzhengjie ~]# 6 [root@yinzhengjie ~]# ll /yinzhengjie/mysql/backup/ 7 total 1048 8 -rw-r--r--. 1 root root 531738 Nov 25 17:45 all_databases.sql 9 -rw-r--r--. 1 root root 531604 Nov 25 17:49 custom.sql10 -rw-r--r--. 1 root root   5000 Nov 25 05:16 yinzhengjie.sql11 [root@yinzhengjie ~]# 12 [root@yinzhengjie ~]# mysql -pyinzhengjie -e 'show databases;'13 +--------------------+14 | Database           |15 +--------------------+16 | information_schema |17 | mysql              |18 | test               |19 | yinzhengjie        |20 +--------------------+21 [root@yinzhengjie ~]#22 [root@yinzhengjie ~]# mysql -pyinzhengjie -e 'drop database mysql;'            #删库操作23 [root@yinzhengjie ~]# mysql -pyinzhengjie -e 'drop database yinzhengjie;'24 [root@yinzhengjie ~]# mysql -pyinzhengjie -e 'show databases;'25 +--------------------+26 | Database           |27 +--------------------+28 | information_schema |29 | test               |30 +--------------------+31 [root@yinzhengjie ~]# 32 [root@yinzhengjie ~]# mysql -pyinzhengjie < /yinzhengjie/mysql/backup/custom.sql     #恢复库的操作33 [root@yinzhengjie ~]# mysql -pyinzhengjie -e 'show databases;'34 +--------------------+35 | Database           |36 +--------------------+37 | information_schema |38 | mysql              |39 | test               |40 | yinzhengjie        |41 +--------------------+42 [root@yinzhengjie ~]#

4.备份前要加锁

a>.请求锁定所有表之后在备份，对MyISAM，InnoDB，Aria做温备

　　以上的3中备份方式在生产环境中并不适用，我们知道生产上的数据库可能随时都在和用户进行交互，如果我们用以上的方式备份肯定会存在问题。因为我们在备份的同时如果有人往里面写入数据了，可能拿到的结果和生产上的结果是不一致的，因此，我们在备份的时候可以添加写锁。即“[root@yinzhengjie ~]# mysqldump -pyinzhengjie --databases yinzhengjie --lock-all-tables > /yinzhengjie/mysql/backup/yzj.sql”，但是这种备份方式并不理想，因为在备份期间会导致所有的数据请求都会被阻塞住。

b>.对InnoDB存储引擎实现热备

　　只要你能确认你的库里面的所有表都是innodb存储类型的话，就可以使用热备的方式，在备份的过程中它会自动进行加锁操作。

5.备份代码的三个选项

　　--events：备份事件调度器代码

　　--routines：备份存储过程和存储函数

　　--triggers：备份触发器

6.备份时滚动日志：

　　每次备份都应该进行日志滚动，这样恢复的时候我们只需要用一个指定的二进制日志文件（即滚动日志）进行恢复操作。。

　　--flush-logs：备份前、请求到锁之后滚动日志

7.复制时的同步位置标记

　　--master-data={0|1|2}

　　"0"表示不记录，

　　“1”表示记录为change master语句，

　　“2”表示记录为注释的change master语句，

8. 使用mysqldump备份正确姿势

　　a>.请求锁：--lock-all-tables 或使用--single-transaction进行innodb热备

　　b>.滚动日志：--flush-logs

　　c>.选定要备份的库 --databases

　　d>.记录二进制文件及位置 --master-date

1 flush tables with read lock;'                #将所有表中位于缓冲区的数据同步到磁盘以后并请求施加全局的读锁，千万不要退出当前中断，一旦退出这个锁会自动释放这个锁哟！2 [root@yinzhengjie mysql]# mysql -pyinzhengjie -e 'show master status;'        #需要另起一个终端，日志滚动前需要看一下当前的记录日志文件ID3 [root@yinzhengjie ~]# mysql -pyinzhengjie -e 'show master status;'                         #滚动日志4 [root@yinzhengjie mysql]# mysql -pyinzhengjie -e 'show master status;'        #查看是否日志滚动成功5 [root@yinzhengjie ~]# mysqldump -uroot -h127.0.0.1 -p --databases mysql yinzhengjie > /yinzhengjie/mysql/backup/custom.sql                                     #只备份mysql和yinzhengjie这两个数据库6 [root@yinzhengjie mysql]# mysql -pyinzhengjie -e 'unlock tables;'                #备份完毕之后记得要释放锁哟。

手动请求全局锁案例展示

1 手动方式需要敲击很多命令，mysqldump命令给我们提供了很多参数让我们可以用一行就搞定上面的手动操作。2 [root@yinzhengjie mysql]# mysqldump -uroot -h127.0.0.1 -p --databases mysql yinzhengjie --lock-all-tables --flush-logs --master-data=2 > /yinzhengjie/mysql/backup/custom2.sql                     #温备方式3 [root@yinzhengjie mysql]# mysqldump -uroot -h127.0.0.1 -p --databases mysql yinzhengjie --single-transaction --flush-logs --master-data=2 > /yinzhengjie/mysql/backup/custom2.sql             #热备方式，注意，这里需要存储表的存储引擎都是innodb哟！

自动请求全局锁案例展示

9.恢复操作

　　建议：关闭二进制日志，关闭其他用户的写操作，应该用souce命令进行恢复操作。

1 mysql> set session sql_log_bin=1; #开启二进制日志功能2 Query OK, 0 rows affected (0.00 sec)3  4 mysql>5 mysql> set session sql_log_bin=0; #关闭二进制日志功能6 Query OK, 0 rows affected (0.00 sec)7  8 mysql>

10.基于mysqldump的备份策略

　　思路：mysqldump+二进制日志文件

　　周日做一次完全备份，备份的同时滚动日志，周一至周六：备份二进制日志，当我们做恢复操作时，使用我们的完全备份和二进制日志（完全备份+各二进制文件至此刻的事件）走一遍即可。我们可以把二进制当做增量备份。也可以每天使用日志滚动操作（[root@yinzhengjie mysql]# mysqladmin -pyinzhengjie flush-logs）。

11.备份配置文件

　　对于MYsql配置文件，以及与mysql相关的OS配置文件在每次修改后都应该直接进行备份。