mysql备份还原之mysqldump使用

mysqldump

基本语法

mysqldump [OPTIONS] database [tables]  #备份数据库里的某一个库或者某一个库中的某个表,一般不用这一种
mysqldump [OPTIONS] –B DB1 [DB2 DB3...]  #备份数据库里的多个库，库与库之间用空格隔开
mysqldump [OPTIONS] –A [OPTIONS]  #备份数据库里的所有数据
#-B 等同于--databases  -A等同于--all-databases

mysqldump常见选项

-A， --all-databases 备份所有数据库，含create database
-B , --databases db_name… 指定备份的数据库，包括create database语句
-E, --events：备份相关的所有event scheduler
-R, --routines：备份所有存储过程和自定义函数
–triggers：备份表相关触发器，默认启用,用–skip-triggers，不备份触发器
–default-character-set=utf8 指定字符集 备份前查看数据库用的是哪种字符集，需要匹配
–master-data[=#]： 此选项须启用二进制日志 若数据库没有主从，用2即可
1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1
2：记录为注释的CHANGE MASTER TO语句此选项会自动关闭–lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启–single-transaction）

-F, --flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合 -A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和–single-transaction或-x，–master-data 一起使用实现，此时只刷新一次日志
–compact 去掉注释，适合调试，生产不使用
-d, --no-data 只备份表结构
-t, --no-create-info 只备份数据,不备份create table
-n,–no-create-db 不备份create database，可被-A或-B覆盖
–flush-privileges 备份mysql或相关时需要使用
-f, --force 忽略SQL错误，继续执行
–hex-blob 使用十六进制符号转储二进制列，当有包括BINARY，VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick 不缓存查询，直接输出，加快备份速度

MyISAM备份选项

支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作
锁定方法如下：
-x,–lock-all-tables：加全局读锁，锁定所有库的所有表，同时加–singletransaction或–lock-tables选项会关闭此选项功能
注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,–lock-tables：对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,–skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用

InnoDB备份选项

支持热备，可用温备但不建议用

 --single-transaction

此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，
DROP TABLE，RENAME TABLE，TRUNCATE TABLE
此选项和–lock-tables（此选项隐含提交挂起的事务）选项是相互排斥
备份大型表时，建议将–single-transaction选项和–quick结合一起使用

示例

直接备份hello库，若原数据库中这个库损坏了可以直接导入，环境和之前备份出来的一模一样

mysqldump -B hello >/data/hello.sql
mysql < /data/hello.sql

完整备份整个数据库，并对二进制日志位置进行标记，确认备份时的二进制日志记录点

mysqldump -A --master-data=2 > /data/all.sql

系统完全删库后如何恢复

首先关闭二进制日志

set sql_log_bin=off;

把备份时到故障前的二进制日志导出

mysqlbinlog --start-position=5261480 mysql-bin.000001 >/data/inc.sql
 mysqlbinlog  mysql-bin.000002 >> /data/inc.sql
 mysqlbinlog  mysql-bin.000003 >> /data/inc.sql
 mysqlbinlog  mysql-bin.000004 >> /data/inc.sql
 mysqlbinlog  mysql-bin.000005 >> /data/inc.sql

最后按次序，先执行上一次完全备份，在执行完全备份后产生的二进制日志，并重新打开二进制日志记录

source /data/all.sql;
source /data/inc.sql;
set sql_log_bin=on;

系统中其中一张表被删除，10分钟后发现，怎么恢复数据

首先刷新日志

flush logs；

把备份时到发现问题前的二进制日志导出

 mysqlbinlog --start-position=521816 mysql-bin.000005 >/data/inc.sql

把删表的操作注释或者删除掉

vim /data/inc.sql
#DROP TABLE `students` /* generated by server */

删库并重启数据库服务

rm -rf /var/lib/mysql/*
systemctl restart mariadb

先关闭二进制日志功能，然后导入完全备份的数据，以及剔除删表操作的二进制日志数据，再重新开启二进制日志功能即可

set sql_log_bin=0
source /data/all_2020-07-01.sql
source /data/inc.sql
set sql_log_bin=1