引言

内容为慕课网的**《高并发高性能高可用 MySQL 实战》**视频的学习笔记内容和个人整理扩展之后的笔记，本篇内容侧重Mysql备份的基本原理和常用介绍为主，大部分为理论相关的内容。

数据备份在平时的工作和学习中可能使用的比较少，但是对于一个线上项目来说却是不可或缺的一环，对于开发人员来说熟悉和了解备份的相关知识是很有必要的，学习备份的相关内容可以帮助我们了解运维工作中一些基本的备份操作。

本节内容偏向理论为主，重点在于了解Mysqldump如何实现增量备份和全量备份，为后面的文章介绍Mysql主备同步打下基础。

知识点：

Outfile 原生mysql工具介绍
MysqlDump对于Outfile工具改进，MysqlDump特点介绍
MysqlDump实现增量备份和全量备份的细节
Xtrabackup备份工具的介绍以及实现增量备份和全量备份的细节

备份介绍

为什么需要备份？

现代的多数服务多数系统高可用，数据无价，丢失会带来难以承担的损失。
一套完整的备份机制可以使得系统遇到不可抗力的情况时将数据的修复代价降到最低甚至零损失。
对于任何项目都应该具备定期备份数据的好习惯，无论是否为生产项目。

备份形式

物理备份：比如我们使用硬盘拷贝自己的重要数据，灵活性一般，安全性较高。
云服务器备份：将数据传到第三方的云数据库进行保管，维护成本一般，安全性取决于第三方维护商的质量。
自建服务器备份：开销比较大，但是数据安全性和稳定性都是最高的，也可以离线进行物理备份，可操作性强。

备份时候数据状态三种：

热备：正常运行备份。此时数据库可读可写。
冷备：停机备份。数据库无法进行任何操作。
温备：数据库只读。数据库可用性弱于热备，备份期间，数据库只能进行读操作，不能进行写操作

备份文件格式

备份文件的格式意味着导出的时候是什么样的：

逻辑备份：输出或者SQL语句，可以供技术人员阅读。
物理备份（裸文件）：备份数据库底层文件但是不可阅读

备份内容

完全备份：备份完整数据
增量备份：备份全量备份之后的数据差异
日志备份：也就是Binlog 备份

常用工具

常用的备份工具有下面两种

Mysqldump：逻辑备份，热备份，全量
xtrabackup：物理，热，全量 + 增量备份

小结

备份的基本形式：从备份的形式来看，可以使用物理磁盘备份，也可以依赖于三方服务商的服务器或者自建的服务器进行备份，而从备份数据状态来看，可以存在热备，冷备和温备，这里需要小心温备这个概念。
备份工具比较常用的有两种：Mysqldump和xtrabackup，这两种工具都需要重点掌握基础的操作使用，实践多余理论，多使用就会了。
开发的时候使用逻辑备份比较多，但是对于运维人员来说可能使用物理备份的方式更快，逻辑备份常常用于线上出问题的场景。

Outfile命令备份（了解）

怎么来？

关于这个命令我们只需要了解，在日常使用中并不涉及使用场景，此命令为mysql自带的命令同时也是mysql 的预留关键字，可以说是最原始的逻辑备份方式，可以作为了解MysqlDump的前置基础。

使用前提

要知道网站的绝对路径，可以通过报错信息、phpinfo界面、404界面等一些方式知道。
要有file的读写权限，建议给相关文件夹执行chmod -R /xxx/xxx 。
写的文件名一定是在文件管理中中不存在的，不然也会不成功！

特点

简单的导出SQL结果主要用于临时需要数据验证的场景。
Mysql原生命令支持的导出方式，执行效率高。
命令简单操作方便，可以导出一致性视图。

缺陷

导出的格式较为简陋，通常需要对于数据进行二次处理才能正常使用。
只能导出SQL执行结果，没有办法将导出后的数据用于还原。

通过上面的介绍可以看出Outfile这个命令只能用于日常开发的场景下需要测试数据临时导出，不能作为热备的主要工具，但是这个命令对于Mysqldump来说是启发性的。

如何使用？

前提条件：在具体的导出之前我们需要了解Mysql导出的具体路径，使用下面的语句检查一下当前的安全文件导出前缀，注意结果如果为NULL在Mysql5.6版本没有影响但是Mysql5.7版本是存在影响的。

另外个人使用的Mac系统的文件系统管理虽然和Linux大体一致，但是其实有很多权限等等细节问题也是踩了一波小坑。

show variables like '%secure%'
-- secure_file_priv  NULL

为什么说使用secure_file_priv为NULL是存在影响的？

解答：

Mysql5.7的版本中，在Mysql启动的时候，如果使用了这个参数的配置则会 限制你可以使用LOAD DATA INFILE加载文件的范围，意味着如果想要导出必须是在这个配置指定的目录下面才能成功，下面是此配置对应的变化：

1. secure_file_priv 为 NULL 时，表示限制mysqld不允许导入或导出。

2. secure_file_priv 为 /tmp 时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行。

3. secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出。

完成上面这些准备工作之后，我们需要搭建基本的操作环境，比如新建数据库或者表，这里依然使用了sakila数据库，我们可以使用下面的命令进行尝试导出，比如下面的语句中我们将payment表的所有数据导出。

select * from payment into Outfile '/Users/xxx/xxx/a.csv'

注：Sakila数据库在Mysql官方的example中可以直接下载。

但是实际执行过程中会出现如下的报错，从报错信息可以看到这里是因为secure_file_priv为NULL的问题：

1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement, Time: 0.004000s

再次强调个人学习的时候使用的是macos系统，设置起来比较麻烦这里也不啰嗦具体细节了，主要讲一下处理思路：

设置自定义的配置my.ini文件并且放到/etc 的目录下面（Mysql读取配置文件规则最高优先级），在文件结尾设置此参数：secure_file_priv=/Users/xxxx/xxx/然后:x保存（注意用sudo vim my.ini），导出路径建议选的当前/User/xxx家目录，方便导出之后立马打开。（根路径路径不太安全，macos系统也不允许你这么弄）
重启Mysql或者重启电脑，连接Mysql之后继续执行上述命令后发现报错：PermissionError: [Errno 13] Permission denied，明显是macOs的权限问题，通过命令chmod 777 导出文件夹/* 可以给整个文件夹开放权限（根目录不要这样做）。
如果出现重名文件使用命令一样报错，提示导出文件已经存在，切记每次执行前检查是否重名文件。

Macos使用brew安装Mysql会发现没有my.ini文件，个人从网上翻了份能用的直接在下面链接提供的文件尾部添加secure_file_priv=/Users/xxxx/xxx/即可，省去大伙的时间，当然是针对我这种蛋疼的MacOs系统来说的，其他操作系统应该可以直接找到相关配置文件。

链接: pan.baidu.com/s/1bM3cQtaX… 提取码: phkg

插曲：Maxos使用homebrew安装版本的启动和关闭：

关闭：sudo pkill -9 mysql

启动：cd /usr/local/mysql/support-file/mysql.server start（stop关闭）

上面啰嗦一大堆之后，下面是最终导出的结果，可以看到默认只使用了空格分隔，并且格式比较乱：

网络异常，图片无法展示

Outfile使用也是比较好记的，同时下面是Outfile的一些使用参数，通过这些参数可以自由配置：

SELECT ... INTO Outfile 'file_name'
        [CHARACTER SET charset_name]
        [export_options]
export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

我们发现上面的格式比较混乱，我们希望按照规范表格的形式导出，于是我们可以在每一行的数据之间添加都好，让导出之后的数据保持规范。

select * from payment into Outfile '/Users/xxx/xxx/a.csv' FIELDS terminated by ','

从结果可以看出Outfile只能用作一些简单的场景的导出操作：

网络异常，图片无法展示

到此为止我们只需要简单了解这个命令即可，为下面了解Mysqldump打下原理基础。

Mysqldump命令使用

Mysqldump的命令可以看作是Outfile命令的扩展，作为十分重要的备份工具经常用于开发和测试的场景，当然线上不推荐使用这种命令操作，一般需要由运维人员操作来导出需要的数据，如果直接对着整个库热备份很容易出问题。

「知识点」

Outfile的痛点，或者说Mysqldump改进点
Mysqldump特点
Mysqldump的操作（实战案例）
Mysqldump的增量备份如何实现（原理）

Binlog 忠实记录mysql变化
Mysqldump通常只能全量备份，所以借助Binlog作为增量备份。
关键：Mysqldump备份，切换新的Binlog文件，之后拷贝binlog文件作为增量备份，注意全量备份和增量备份文件的不同。
采用从零开始还原，采用全量还原 + Binlog还原。

Mysqldump通常只能全量备份，使用Binlog增量备份

关键：Mysqldump备份，切换新的Binlog文件。

采用从零开始还原：全量还原 + Binlog还原

Outfile的痛点

只要简单操作一下Outfile命令就会发现Outfile有下面这几个明显的缺点，Mysqldump其实就是解决了Outfile的很多现实问题，并且在此基础上改进让它更加简单好用。

只能导出数据，很难把数据再次导入
无法做逻辑备份，也就是备份SQL逻辑
导出形式单一，通常只能导出excel。

Mysqldump特点

Mysql官方内置命令，内置实现可以避开很多没有必要的问题。
支持远程备份，可以生成多种格式的文件。
与存储引擎无关，可以在多种存储引擎下进行备份恢复，对innodb引擎支持热备，对MyISAM引擎支持温备（施加表锁）。
免费。

如何学习Mysqldump？

官方开发的当然是官方文档学习最好啦，链接提供的是Mysql8.0的版本，其他版本需要根据自己当前使用的版本切换阅读，另外命令参数不需要去记忆也没有意义，在需要的时候翻出来看看然后看看官方文档即可：

任何工具类的东西适合使用的时候查阅，死记硬背是没有意义的，最后会发现只需要记住常用的方式即可。

dev.mysql.com/doc/refman/…

备份所需权限

如果需要备份数据至少需要SELECT权限。
备份视图需要SHOW VIEW权限。
备份触发器需要TRIGGER权限。
如果不使用参数--single-transaction，则需要相关权限进行锁表。
（从MySQL 8.0.21开始）如果不使用--no-tablespaces选项则需要PROCESS权限。
如果需要导入备份数据，则需要包含执行语句的所有权限，比如CREATE、ALTER、DELETE权限

实践：MysqlDump备份案例

我们可以尝试备份一下官方提供的example比如sakila，下面是一些简单的操作命令：

备份一个数据库

-- 第一种备份方法
./Mysqldump -uroot -pxxxxxx sakila > /Users/xxx/xxx/xxx/xxxx/backup-file.sql
-- Mysqldump: [Warning] Using a password on the command line interface can be insecure.

备份多个数据库到一个sql文件

./Mysqldump  --databases sakila sakila-db -uroot -xxx > /Users/xxx/xx/xxxx/xxx/backup-file_bk2.sql

将数据从一个服务器备份到另一个服务器

-- 个人是本地单机没有进行虚拟机模拟，实验结果未知
Mysqldump --opt db_name | mysql --host=remote_host -C db_name

如果使用InnoDB 的存储引擎Mysql有一种在线备份的方法：

-- 参数解释
-- --all-databases 所有数据库
-- --single-transaction RR级别的备份，也就是确保一致性的视图(Innodb存储引擎)
-- --master-data 将二进制日志文件的名称和位置写到输出端(留意一下，为下文的增量备份铺垫)
-- 如果不是InnoDB,需要使用下面的参数：
-- 1. --lock-all-tables 使用FTWRL锁锁住所有表（MyISAM）
-- 2. --lock-tables 使用READ LOCAL锁住当前库的表（MyISAM）
Mysqldump -uroot -pxx --all-databases --master-data --single-transaction > /Users/xxx/xxx/all_databases.sql

还原数据库

-- 
./Mysqldump -uroot -pxxxxxx sakila < /Users/xxx/xxx/xxx/xxxx/backup-file.sql
-- 第二种还原备份方法
-- 1. 使用具备相关权限的用户名和密码登陆连接到mysql服务器 mysql -uroot -proot 
-- 2. source /xxx路径/xx.sql文件 source xxx.sql
-- 第三种方式
mysql -e "source /path-to-backup/backup-file.sql" db_name

关于其他的命令这里就不再扩展了，这里介绍一些常用的基本够日常开发使用了，如果需要更多的写法可以参考上面的官方文档。

Mysqldump的增量备份实现原理

上面提到的都是全量备份的方式，虽然我们在拷贝的时候可以通过--single-transaction拷贝一致性的视图，虽然拷贝那一刻的数据记录是全量并且完整的，但是此时数据库依然是存在还在执行的增量数据的，那么这部分数据应该如何备份呢？

使用Mysqldump的进行增量备份首先需要了解增量备份的细节，所以这里就轮到Binlog日志上场了，Binlog的备份包含下面几个小点：

Binlog 忠实记录mysql变化，全量增量备份和还原过程。
Mysqldump通常只能全量备份，所以借助Binlog作为增量备份。
关键：Mysqldump备份，切换新的Binlog文件，之后拷贝binlog文件作为增量备份，注意全量备份和增量备份文件的不同。
采用从零开始还原，采用全量还原 + Binlog还原。

为什么不能同时增量和全量备份：

我们可以把 Mysql记录日志的过程看作是在纸上写字，此时Mysql在最新的Binlog日志中记录内容，如果我们把正在写的内容和之前的日志内容一并备份，就很可能导致备份出写了一半的数据，就好像我们写字的时候突然被抽中本子一样，这样就很有可能导致数据损坏。

Binlog 忠实记录mysql变化，全量增量备份和还原过程。

实现增量备份的关键点在于如何给Binlog日志做切入点，做Mysqldump增量备份存在的最大问题是我们无法知道当前的全量备份和增量数据的分界点。Binlog日志记录的是Mysql的变化内容比如CRUD的数据记录变动记录以及数据的结构的调整等等，并且和InnoDB的存储引擎的redo log双写保持事务一致性。

根据上面的内容介绍我们知道了Mysqldump只能全量备份，需要借助Binlog日志完成增量备份。

增量备份实现思路是在备份的时候将当前正在读写的Binlog日志停掉，并且将此文件进行拷贝，但是需要注意的是此时拷贝的是Binlog文件，和日常编写的逻辑SQL是不一样的，切记。

关键点：Mysqldump备份，Mysql服务器停止当前Binlog写入并且切换新的Binlog文件

Mysqldump提供了类似上面提到的操作，下面是Mysqldump全量备份+增量备份的操作流程：

-- --all-databases 所有数据库
-- --single-transaction RR级别的备份，也就是确保一致性的视图(Innodb存储引擎)
-- --master-data=[=Value]（8.0.26改为--source-data命令） 将二进制日志文件的名称和位置写到输出端(留意一下，为下文的增量备份铺垫)
-- --flush-logs 在备份之前刷新服务器的日志 
Mysqldump -uroot -pxx --all-databases --master-data=2 --flush-logs --single-transaction > /Users/xxx/xxx/all_databases.sql

通过执行上面的命令之后首先会进行全量备份同时会把Binlog切换到下一份日志文件重新开始进行读写，此时就可以把这一份停止写入对binlog日志文件备份出来进行后续的增量备份还原，简而言之：Mysql备份的同时切换Binlog，并且把当前写了一部分的Binlog日志进行拷贝。

Mysql其实还有一种备份方式那就是Binlog手动增量备份，实现方式是直接使用命令把缓存的日志刷到磁盘中并且切换到下一个Binlog，它的命令格式如下：

mysqladmin -uroot -p123456 flush-logs

需要注意的是这里使用的是mysqladmin工具，在执行命令之后我们可以手动将所有的Binlog进行备份。

还原方式：全量还原 + Binlog还原，还原操作和增量全量备份方式对应，因为是Mysqldump全量+Binlog增量备份，所以同样需要先进行全量还原再增量还原。

恢复全量备份：还原的操作最简单的方式是连接服务器之后执行source xxx.sql ，而Binlog增量还原操作案例如下：

mysqlBinlog Mysql-bin.00002 ... | mysql -uroot -p123456

小结

Mysqldump + Binlog 可以有效进行全量 + 增量备份。
Mysqldump实际上是对于Outfile工具的扩展和升级。
Binlog备份，Binlog还原，Mysqldump备份可以看出不同组件的搭配。
从理论上来说Binlog可以还原到任意的时刻。
Mysqldump 的参数较多，熟悉和掌握需要多加练习。
需要注意区分mysqladmin、mysqlBinlog、mysqldump

三高"Mysql - Mysql备份概览（上）

引言

知识点：

备份介绍

Outfile命令备份（了解）

Mysqldump命令使用

Outfile的痛点

Mysqldump特点

如何学习Mysqldump？

备份所需权限

实践：MysqlDump备份案例

Mysqldump的增量备份实现原理

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像