MySQL数据库备份与恢复 Part 1 : 基础篇

1 MySQL数据库备份

1.1 备份的意义

        大数据时代，数据对于企业越来越重要。失去数据就相当于失去商机、失去产品、失去客户，甚至会导致企业倒闭。而数据中的核心数据都存储在数据库中，那么数据库备份与恢复的重要性就不言而喻了。

1.2 mysqldump介绍

        mysqldump是MySQL自带的备份工具

        mysqldump是把数据从MySQL库中以SQL语句的形式直接输出或者生成备份文件，通常被称为逻辑备份。

--- mysqldump用法

mysqldump -u USERNAME -p 'PASSWORD' -options dbname > backupname.sql

--- 无参数备份单库

mysqldump dbname > backupname.sql

--- 参数'-B'备份多库

mysqldump -B dbname1 dbname2 > backupname.sql

备份文件中会增加"创建数据库和连接数据库语句"，而且'-B'参数后接多个库

CREATE DATABASE /*132312 IF NOT EXISTS*/ 'dbname' /*140100 DEFAULT CHARACTER SET utf8*/;

USE 'dbname';

--- 分库备份

mysqldump -u USERNAME -p 'PASSWORD' -B dbname1 > backupname1.sql

mysqldump -u USERNAME -p 'PASSWORD' -B dbname2 > backupname2.sql

mysqldump -u USERNAME -p 'PASSWORD' -B dbname3 > backupname3.sql

--- 备份数据表

mysqldump -u USERNAME -p 'PASSWORD' dbname tablename > backupname.sql

mysqldump -u USERNAME -p 'PASSWORD' dbname tablename1 tablename2 > backupname.sql

--- 分表备份

mysqldump -u USERNAME -p 'PASSWORD' dbname tablename1 > backupname1.sql

mysqldump -u USERNAME -p 'PASSWORD' dbname tablename2 > backupname2.sql

mysqldump -u USERNAME -p 'PASSWORD' dbname tablename3 > backupname3.sql

--- '-d'仅备份数据结构，无数据

mysqldump -B dbname1 > backupname.sql

--- '-t'仅备份数据，无数据结构

mysqldump -t dbname > backupname.sql

--- '-T'数据机构与数据分离成不同文件

my.cnf中需添加：secure_file_priv=''，否则会抛出1290错误。

mysqldump dbname tablename --compact -T /directory

--- '-F'刷新binlog

mysqldump -F -B dbname > backupname.sql

--- '--master-data'标记binlog点位

mysqldump --master-data=1 dbname > backupname.sql

--- '-x'锁定所有表备份

mysqldump -x dbname > backupname.sql

--- '--single-transaction'备份innodb表

mysqldump -B --master-data=2 --single-transaction dbname > backupname.sql

1.3 常用参数说明

常用参数	参数说明
-B，--databases	备份文件中加入create、use语句，可同时备份多个库
-A，--all-databases	备份所有数据库
-d，--no-data	备份表结构（SQL语句形式），没有数据
-t，--no-create-info	备份表数据（SQL语句形式），没有结构
-T，--tab=name	备份数据和结构分离成不同文件，表结构（SQL语句）、数据（文本文件）
-F，--flush-logs	刷新binlog，生成新binlog文件，增量恢复从新文件开始
--master-data=[1|2]	备份增加binlog文件名和位置点（change master）。'1'时是非注释。'2'时是注释
-x，--lock-all-tables	备份时，所有数据对象执行全局读锁
-l，--lock-tables	锁定所有表为只读
--single-transaction	备份InnoDB引擎数据表，获取一个一致性的数据快照，设定本次备份会话的隔离级别为REPEATABLE READ，并将备份放于一个事务里，确保备份时不会看到其他会话提交的数据。相当于锁表备份，但允许备份期间写数据，启动该参数会关闭 --lock-tables
-R，--routines	备份存储过程和函数数据
--triggers	备份触发器数据
--compact	显示很少的关键输出，适用于学习和测试

1.4 不同引擎的备份方式

--- InnoDB备份

mysqldump -A -B --master-data=2 --single-transaction > backupname.sql

--- MyISAM和InnoDB混合备份

mysqldump -A -B --master-data=2 > backupname.sql

注：'--master-data'会自动开启'-x'参数功能，备份期间锁表会影响数据写入

1.5 SQL语句方式导出数据表

SELECT * FROM tablename INTO OUTFILE 'filename' EXPORT_OPTIONS

export_options说明

Export_options	说明
character set utf8	导出配置字符集为utf8，默认与库字符集一致
fields terminated by ' - '	导出配置不同的域之间的分隔符 " - "，默认tab
fields enclosed by ' " '	导出配置字段内容的引用符 " 双引号 "，默认"空"
lines starting by ' = '	导出配置行首添加"等号"，默认"空"
lines terminated by ' = '	导出配置行尾的结束符"等号"，默认"回车符"

--- 导出数据

select * from test into outfile "/data/bak/ocean_test.txt"

--- 配置字符集

select * from test into outfile "/data/bak/ocean_test.txt" character set utf8;

--- 配置分隔符

select * from test into outfile "/data/bak/ocean_test.txt" fields terminated by '-';

--- 配置字段内容引用符

select * from test into outfile "/data/bak/ocean_test.txt" fields enclosed by '"';

1.6 SQL语句导入表

        select语句和mysqldump导出的纯文本数据，可以使用"load data"导入，也可以使用mysqlimport进行数据导入

LOAD DATA INFILE 'filename' INTO TABLE tablename import_options

Import_options	说明
character set utf8	导入配置字符集为utf8，默认与库字符集一致
fields terminated by ' - '	导入配置不同的域之间的分隔符 " - "，默认tab
fields enclosed by ' " '	导入配置字段内容的引用符 " 双引号 "，默认"空"
lines starting by ' = '	导入配置行首添加"等号"，默认"空"
lines terminated by ' = '	导入配置行尾的结束符"等号"，默认"回车符"
ignore number lines	不导入文件的前N行

        数据导入前需情况目标表

--- 清空目标表

delete from test;

--- 导入数据

load data infile '/data/ocean_table.txt' into table test;

--- 导入数据，指定分隔符‘-’

load data infile '/data/ocean_table.txt' into table test fields terminated by '-';

--- 导入数据，使用双引号引用的数据

load data infile '/data/ocean_table.txt' into table test fields enclosed by '"';

2 MySQL数据恢复

2.1 数据恢复原理

        mysql命令、source命令恢复数据库是在数据库中重新执行备份文件的SQL语句。恢复成功与否，取决于备份有效性和字符集配置。

2.2 source命令恢复数据

--- 登录数据库

mysql -uroot -p'root1234' -S /data/3306/my.sock

--- 切换恢复目标库

use ocean

--- source恢复数据

source ocean_db.sql

2.3 mysql命令恢复数据

        mysql命令是MySQL数据库的重要命令之一，使用mysqldump备份文件进行数据恢复

--- 恢复数据

mysql < ocean_table.sql

--- 指定数据库恢复

mysql dbname < ocean_table.sql

2.4 mysqlbinlog增量恢复

        mysqlbinlog是解析mysql的二进制binlog的日志内容，将二进制日志解析成SQL语句

--- 解析指定库的binlog

mysqlbinlog -d ocean ocean-bin.000004 -r bin.sql

grep -i insert bin.sql

--- 根据位置截取binlog

mysqlbinlog ocean-bin.000004 --start-position=365 --stop-position=456 -r bin.sql

mysqlbinlog ocean-bin.000004 --start-position=365 -r bin.sql

mysqlbinlog ocean-bin.000004 --stop-position=456 -r bin.sql

--- 根据时间截取binlog

mysqlbinlog ocean-bin.000004 --start-datetime='2019-04-01 9:09:09' --stop-datetime='2019-04-01 10:10:10' -r bin.sql

mysqlbinlog ocean-bin.000004 --start-datetime='2019-04-01 9:09:09' -r bin.sql

mysqlbinlog ocean-bin.000004 --stop-datetime='2019-04-01 10:10:10' -r bin.sql 

        mysqlbinlog常用参数

mysqlbinlog参数	参数说明
-d，--datebase = dbname	拆分binlog的数据库名
-r，--result-file = filename	解析binlog输出SQL语句的文件
-R，--read-from-remote-server	从MySQL服务器读取binlog，是"read-from-remote-master=BINLOG-DUMP-NON-GTIDS"的别名
-j，--start-position = #	binlog的起始位置，#号是具体位置
--stop-position = #	binlog的停止位置，#号是具体位置
--start-datetime = timestamp	binlog的起始时间，timestamp是具体时间，格式：2019-04-01 09:09:09
--stop-datetime = timestamp	binlog的停止时间，timestamp是具体时间，格式：2019-04-01 10:10:10
--base64-output = decode-rows	解析ROW级别binlog日志

 

相关链接

MySQL数据库备份与恢复 Part 2 : 进阶篇

MySQL数据库备份与恢复 Part 3：Xtrabackup

参考 《MySQL 5.7 Reference Manual》、《MySQL Source-Configuration Options》