mysql备份还原之mysqldump使用

mysqldump基本语法mysqldump常见选项MyISAM备份选项InnoDB备份选项 示例系统完全删库后如何恢复系统中其中一张表被删除，10分钟后发现，怎么恢复数据

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