深入研究MySQL（四）、备份与恢复

一、前言

知道备份的重要性吗？没经历过的可能永远不知道他的痛，我们在重装系统、手机升级等时候，备份一下数据总是有好处的，指不定那个操作导致磁盘数据丢失，前些日子删库跑路被判刑的那位老哥，如果公司有备份的话，也不至于损失几个亿（听说是让绿了），所以本章来学习一下MySQL的备份与恢复，如果要定期备份的话，可以写个脚本定时执行，省去人来完成这重复的工作。

我们可以使用以下任何一种方法来生成MySQL数据库的备份：

1. 使用mysqldump程序
2. 使用二进制日志

在本文中，会解释如何使用mysqldump生成MySQL数据库的备份。

二、使用mysqldump程序生成备份

Mysqldump是一个命令行程序，用于生成MySQL数据库的备份。他生成的文件可以用于重新创建数据库对象和数据的SQL语句。

mysqldump -u [用户名] –p [密码] [选项] [数据库名] [表名]> [dumpfilename.sql]

参数如下：

-u [用户名]：连接到MySQL服务器的用户名。
-p [密码]： MySQL用户的密码
[option]：用于自定义备份的配置选项
[数据库名称]：要备份的数据库的名称
[表名]：这是一个可选参数。如果要获取备份特定表，则可以在命令中指定名称
[dumpfilename.sql]：备份文件的路径和名称。

1.生成单个数据库的备份

要生成单个数据库的备份，可以运行以下命令，这个命令将test数据库中的表结构和数据都保存到testdb.sql文件中。

mysqldump -u root -p test >/home/HouXinLin/test/testdb.sql

运行此命令时，会提示您输入密码，输入密码后，将在对应路径生成sql文件，内容如下。

2.生成多个数据库或所有数据库的备份

如果要生成多个数据库的备份，必须在mysqldump命令中添加--databases选项，以下命令将生成具有结构和数据的"test"和"db_score"数据库的备份。

mysqldump -u root -p --databases test db_score test >/home/HouXinLin/test/test_tbscoredb.sql

要生成整个数据库的备份，则需要加--all-databases选项。

mysqldump -u root -p --all-databases  >/home/HouXinLin/test/test_tbscoredb.sql

3.生成数据库结构的备份

如果仅要生成数据库结构的备份，则必须在mysqldump命令中加入–no-data选项，以下命令用于生成test数据库的数据库结构的备份。

mysqldump -u root -p --no-data test >/home/HouXinLin/test/testdb.sql

4.生成特定表的备份

如果仅要生成数据库中某个表的备份，则必须在mysqldump命令中指明表名称，以下命令用于生成test数据库中tb_books表的结构和数据备份。

mysqldump -u root -p  test tb_books >/home/HouXinLin/test/testdb.sql

5.仅生成数据库数据备份

如果要生成仅数据库数据的备份（没有创建表的语句），则必须在mysqldump命令中加入–no-create-info 选项。以下命令生成test数据库的数据备份。

mysqldump -u root -p   --no-create-info test >/home/HouXinLin/test/testdb.sql

生成的sql中，可以看到他只有insert语句，在插入的时候，还进行了锁表。

三、恢复数据

恢复的话比较简单，通过source程序传入sql文件即可，一下是完整的备份、删库，恢复命令。

## 备份
mysqldump -u root -p test >/home/HouXinLin/test/testdb.sql

## 删除test数据库
drop database test;

## 创建数据库
create database test;

## 选择数据库
use test;

## 恢复
source /home/HouXinLin/test/testdb.sql

四、免密码导出

从上面可以发现在使用mysqldump的时候每次都需要输入密码，当然这是可以避免的

touch ~/.my.cnf

vim  ~/.my.cnf 

然后编辑其内容（编辑完不需要重启），最后在使用mysqldump中一定要取消-p参数。

[mysqldump]
user=root
password=xxxxx

其实对于这类更改方式，在我心中必须要看到某个文件对这个软件有什么影响的官方文章说明，才会安心，即使知道他确实会对软件有影响，在没看到官方对这个解释前，心中的这石头总是放不下的，所以，我在官网中找到了对一些文件的解释。

文件名	目的
/etc/my.cnf	全局选项
/etc/mysql/my.cnf	全局选项
SYSCONFDIR/my.cnf	全局选项
$MYSQL_HOME/my.cnf	服务器特定的选项（仅服务器）
~/.my.cnf	用户特定的选项
~/.mylogin.cnf	用户特定的登录路径
DATADIR/mysqld-auto.cnf	系统变量使用 SET PERSIST或 SE PERSIST_ONLY保留（仅服务器）

1. SYSCONFDIR表示在构建MySQL时SYSCONFDIR 使用CMake选项指定的目录。

2. MYSQL_HOME是一个环境变量，其中包含服务器特定my.cnf文件所在目录的路径。

3. DATADIR表示MySQL数据目录。用于查找 mysqld-auto.cnf，其默认值是编译MySQL时内置的数据目录位置，但是可以通过–datadir 指定为在处理之前mysqld-auto.cnf处理的选项文件或命令行选项来更改。

五、定时备份

Linux自带一个定时任务系统，可以通过crontab -e进行添加，也可以修改/etc/crontab文件，另外这个服务的最低侦是分钟，也就是无法做到每隔x秒后去执行任务，这个服务会每分钟去读取一次 /etc/crontab 和 /var/spool/cron 里面的数据，在编辑/etc/crontab文件后，只需静静等待即可，下面以/etc/crontab为例子，在文件最后加入每隔1秒执行一次脚本，这需要对cron表达式有所了解。

# /etc/crontab: system-wide crontab
# Unlike any other crontab you don't have to run the `crontab'
# command to install the new version when you edit this file
# and files in /etc/cron.d. These files also have username fields,
# that none of the other crontabs do.

SHELL=/bin/sh
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# m h dom mon dow user	command
17 *	* * *	root    cd / && run-parts --report /etc/cron.hourly
25 6	* * *	root	test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.daily )
47 6	* * 7	root	test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.weekly )
52 6	1 * *	root	test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.monthly )
#

*/1 * * * * root  /home/HouXinLin/test/dump.sh

dump.sh

#!/bin/bash
mysqldump -u root test >/home/HouXinLin/test/testdb.sql

静静等待1分钟后，在对应的目录就会生成testdb.sql文件。