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》