MySQL数据库运维之数据备份01

上篇文章介绍了MySQL主从复制中常见问题排查。在主从复制架构搭建完成之后，需要定期对数据进行备份，本篇文章开始就来介绍MySQL数据库的数据备份与恢复，由于备份及恢复内容较多，分多个小节介绍，本小节先来介绍MySQL数据库备份的相关概念及mysqldump逻辑备份工具。

1、备份类型
（1）按照备份后的文件来划分

a、物理备份：直接备份数据库文件，常用的有LVM逻辑卷备份，或者直接拷贝压缩数据库数据目录
b、逻辑备份：使用备份工具将数据库中的数据导出为数据库sql脚本或者数据文件

（2）按照是否停止数据库服务划分

a、冷备：备份期间需要停止数据库服务，会造成不可读不可写
b、温备：备份期间不需要停止数据库服务，但是需要锁表，故只能读，不可写
c、热备：备份期间，数据库服务完全不受影响，可读可写

（3）按照备份的周期和备份内容划分

a、完全备份：一次性备份数据库实例中的所有数据
b、增量备份：如果刚进行过一次完全备份，则增量备份就指的是全备之后到当前时间点之间增加的数据；
            如果上次为增量备份，则增量备份指的内容是上次增量之后到当前时间点之间增加的数据
c、差异备份：仅备份上次全量备份之后发生变化的数据，由于比增量备份耗费的磁盘空间更多，故用的比较少

2、备份策略

（1）全量备份+增量备份
（2）全量备份+差异备份

3、备份工具介绍

（1）mysqldump：基于MySQL客户端的一个逻辑备份工具，可实现温备，可以使用-u，-p，-h等选项备份远程数据库上的数据
（2）mysqlhotcopy：基于MySQL客户端的一个物理工具，只可冷备，备份过程中需指定-u，-p，-h，-S等基本参数，这些基本参数的含义和mysql命令对应的参数含义相同
（3）lvm2：基于lvm的物理备份工具，由于是通过对逻辑卷做快照来实现的，所以备份速度极快，可在瞬间完成，可粗略认为是热备
（4）xtrabackup：Percona旗下的一块开源MySQL备份工具，可以实现在线热备和温备

除此之外还有一些其他备份工具，如mysqldumper，PhpMyAdmin等，感兴趣可自己查资料学习。

4、mysqldump逻辑备份基本操作
（1）用法

mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]

（2）常用参数选项OPTIONS

-A,--all-databases：该选项表示备份实例中的所有数据库
-B,--databases：指定要备份的数据库名称，后面可以同时跟多个数据库
-E,--events：表示备份过程中包括数据库中的事件
-F,--flush-logs：表示备份完成之后刷新日志，滚动日志点，如果没有开启二进制日志，使用该选项会提示错误
--flush-privileges：表示备份最新的权限表数据
--hex-blob：表示备份过程中包括数据库中的二进制数据(BINARY,VARBINARY,BLOG)
-x,--lock-all-tables：该选项的作用是在备份过程中锁定所有的表，通常用于备份MyISAM存储引擎类型的表数据
--single-transaction：该选项表示备份过程中保证数据的一致性，使用事务隔离状态和一致性快照保证，目前只支持InnoDB类型的表
    注：--lock-all-tables选项和--single-transaction选项同时只能用一个
--master-data=N：该选项用来设置在导出的数据中是否包括对二进制日志文件和日志点的记录，常用的值有：1和2
    1：表示在导出的sql文件中包括了"change master to master_log_file='',master_log_pos=N"内容，用来做快速主从复制
    2：表示在导出的sql文件中不包括"change master to master_log_file='',master_log_pos=N"内容
-t,--no-create-info：表示只备份数据，不备份表的创建信息
-d,--no-data：表示只备份表结构，不备份任何数据
-R,--routines：表示备份中同时备份存储过程和函数
--tables：如果不需要备份整个库，只需要备份部分表，可以使用该选项指定
--triggers：表示备份中同时备份触发器
-u：指定完成备份操作的用户名
-p：指定完成备份操作的用户密码
-h：指定完成备份操作的域名或者ip地址
-P：指定完成备份操作的端口号。注意，是大写的P

（3）示例
示例1：完成127.0.0.1服务器上3306数据库实例的全量备份

[root@WB-BLOG ~]# mkdir -pv /backup/
[root@WB-BLOG ~]# mysqldump -uroot -proot -h127.0.0.1 -P3306 -S /tmp/mysql.sock --all-databases --routines --triggers --single-transaction --events --master-data=2 | gzip > /backup/full_db_`date +%F`.sql.gz
#检查文件是否正常，这步很很很重要。有时候由于一些原因，备份文件生成了，但是打不开，所以务必检查
[root@WB-BLOG ~]# gzip -d /backup/full_db_2018-06-18.sql.gz 
[root@WB-BLOG ~]# less /backup/full_db_2018-06-18.sql

示例2：使用root用户备份127.0.0.1服务器上3306实例中的test库，并完成压缩放在/backup目录下

[root@WB-BLOG ~]# mysqldump -uroot -proot -h127.0.0.1 -P3306 -S /tmp/mysql.sock --databases test --routines --triggers --single-transaction --events --master-data=2 | gzip > /backup/test-`date +%F`.sql.gz
[root@WB-BLOG ~]# ls /backup/test-2018-06-18.sql.gz 
#查看是否备份成功
[root@WB-BLOG ~]# gzip -d /backup/test-2018-06-18.sql.gz 
[root@WB-BLOG ~]# less /backup/test-2018-06-18.sql

示例3：备份127.0.0.1服务器上3306实例中的test库中的user表

[root@WB-BLOG ~]# mysqldump -uroot -proot -h127.0.0.1 -P3306 -S /tmp/mysql.sock --databases test --tables user --routines --triggers --master-data=2 --events| gzip > /backup/Db-test_Tb-user_`date +%F`.sql.gz
[root@WB-BLOG ~]# ls /backup/Db-test_Tb-user_2018-06-18.sql.gz 
#查看是否备份成功
[root@WB-BLOG ~]# gzip -d /backup/Db-test_Tb-user_2018-06-18.sql.gz 
[root@WB-BLOG ~]# less /backup/Db-test_Tb-user_2018-06-18.sql

示例4：备份127.0.0.1服务器上3306实例中的mysql库的表结构，不备份数据

[root@WB-BLOG ~]# mysqldump -uroot -proot -h127.0.0.1 -S /tmp/mysql.sock -P3306 --databases mysql --no-data --triggers --routines --master-data=2 --events| gzip > /backup/mysql_table_frame_`date +%F`.sql.gz
#查看是否备份成功
[root@WB-BLOG ~]# gzip -d /backup/mysql_table_frame_2018-06-18.sql.gz 
[root@WB-BLOG ~]# less /backup/mysql_table_frame_2018-06-18.sql

示例5：备份127.0.0.1服务器上3306实例中的test库中所有表的数据，不备份表结构

[root@WB-BLOG ~]# mysqldump -uroot -proot -h127.0.0.1 -S /tmp/mysql.sock -P3306 --databases test --no-create-info --triggers --routines --master-data=2 --events| gzip > /backup/test_table_data_`date +%F`.sql.gz
#查看是否备份成功
[root@WB-BLOG ~]# gzip -d /backup/test_table_data_2018-06-18.sql.gz 
[root@WB-BLOG ~]# less /backup/test_table_data_2018-06-18.sql

实例6：在windows上远程备份192.168.0.10服务器上的3306数据库实例中的mysql数据库
前提：需要在0.10服务器上授予windows主机的远程连接权限，授权操作可查看之前文章，此处略

D:\SoftWare\mysql-5.7.21\bin>mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases mysql > D:/remote_mysql.sql
#查看D盘根目录，发现已经备份完成，查看备份文件也正常

至此，第一小节MySQL备份相关概念及mysqldump逻辑备份工具介绍完毕，下一篇文章将继续介绍MySQL的物理备份工具Xtrabackup，欢迎评论转发!