MySQL8备份恢复（一）

备份工具说明

GUI工具: navicat

逻辑备份工具: mysqldump mysqlpump 和 mydumper (不随 MySQL 提供)

物理备份工具: XtraBackup (不随MySQL提供) 普通文件备份

Navicat备份恢复

navicat可以有3个地方提供备份恢复功能

backup/restore

backup: 打开数据库 -> Backups右键 -> New Backup 备份文件 $HOME/.config/navicat/settings/0/0/MySQL/<Connection>/<日期时间>.nb3 restore: 打开数据库 -> Backups右键 -> Restore Backup from

dump/execute

dump: 打开数据库 -> 右键点击 -> Dump SQL File -> Structure And Data execute: 新建空白数据库 -> 右键点击 -> Execute SQL File

export/import

export: 选中表 -> 右键点击 -> export wizard 支持格式: Dbase Text CSV HTML EXCEL SQL XML JSON import: 选中表 -> 右键点击 -> import wizard

mysqldump备份恢复

参数

可以使用 mysqldump --help 查看说明和默认值

参数	说明
-h, --host=name	主机名
-u, --user=name	账号
-p, --password[=name]	密码
-R, --routines	备份存储过程和函数.该选项需要全局 SELECT 特权
-E, --events	备份事件调度器
-F, --flush-logs	在开始转储之前刷新 MySQL 服务器日志文件. 该选项需要 RELOAD 特权. 如果将此选项与该 --all-databases 选项结合使用, 则会为每个转储的数据库刷新日志. 例外情况是使用 --lock-all-tables, --master-data 或 --single-transaction 在这种情况下, 日志只刷新一次, 对应于所有表被锁定的时刻 FLUSH TABLES WITH READ LOCK 如果你希望你的转储和刷新日志到恰好在同一时刻发生, 你应该使用 --flush-logs 同在一起 --lock-all-tables, --master-data 或 --single-transaction
--triggers	备份触发器.默认启用; 禁用它 --skip-triggers
--single-transaction	在备份开始前, 通过将事务隔离模式更改为 REPEATABLE READ 模式, 并执行 START TRANSACTION 命令,以获得备份的一致性, 当前该参数只对 InnoDB 存储引擎有效
-l, --lock-tables	在备份中, 一次锁住每个架构下的所有表. 一般用于 MyISAM 存储引擎, 可以保证一致性. 这个参数与上面参数是互斥的, 只能用一个. 如果有两种表,只能选择这个. 但是只能保证同一架构下的一致性, 不能保证所有架构下的一致性
-x, --lock-all-tables	用于避免上面说的一致性问题,锁住所有数据库所有的表
--master-data[=#]	通过该参数产生的备份转存文件主要用来建立一个 replication. 当 value 为 1 时,转存文件中记录 CHANGE MASTER 语句. 为2时, CHANGE MASTER 语句被写出 SQL 注释. 默认值为空. 这个会忽略 --lock-tables 选项,如果没有使用 --single-transaction 就会自动加上 --lock-all-tables 选项
--dump-slave	备份在从服务器上进行,要获取备份时主服务器的二进制日志位置. --master-data 用于在 master 端 dump 数据, 用于建立 slave. --dump-slave 用户在 slave 端 dump 数据, 建立新的 slave, 至少是第2个 slave, 也就是已经有A-->B, 现在从B上导出数据建立 A-->C 的复制
-A, --all-databases	导出所有数据库数据
-B, --databases	这个不但可以用于建立 slave, 还可用于备份中, 用于读 binlog 进行 roll forward 的起点导出指定的数据库
--tables	覆盖 --databases 或 -B 选项. mysqldump 将选项后面的所有名称参数视为表名
--add-drop-database	在 create database 前先进行 drop. 这个参数需要和 --all-databases 或者 --databases 选项一起使用,默认关闭
--add-drop-table	在 create table 先进行 drop, 默认开启
--add-drop-trigger	在 create trigger 先进行drop, 默认关闭
-d, --no-data	不要写任何表行信息 (即不要转储表内容),默认关闭
-w, --where=name	导出指定条件的记录
--hex-blob	将 BINARY, VARBINARY, BLOG, BIT 列类型备份为十六进制的格式. 文本模式下有些字符可能看不见,默认关闭
-T, --tab=name	产生 TAB 分割的数据文件(mysqldump和mysqld在同一个主机上才可以)
--opt	此选项默认启用,是组合的简写. 它提供了一个快速转储操作, 并生成一个可以快速重新加载到MySQL服务器的转储文件. --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 禁用使用 --skip-opt
-q, --quick	此选项对于转储大型表格很有用. 它强制 mysqldump 每次从服务器的行中检索表的行, 而不是在将其写出之前检索整个行集并将其缓存在内存中.
--order-by-primary	如果存在这样的索引,请转储按主键排序的每个表的行, 或按其第一个唯一索引排序. 在将 MyISAM 表加载到 InnoDB 表中时这很有用,但是使转储操作花费更长时间

备份

mysqldump --login-path=bak --routines --events --single-transaction --master-data=2 --flush-logs --databases test > test.sql
#不导出数据
mysqldump --login-path=bak --set-gtid-purged=OFF --no-data --routines --events --single-transaction --master-data=2 --databases test  > test_ddl.sql 2>test.err
#只导出数据
mysqldump --login-path=bak --set-gtid-purged=OFF --no-create-db --no-create-info --add-drop-table=false --triggers=false --single-transaction --master-data=2 --databases test  > test_dml.sql 2>test.err           

恢复

假设误删除了数据库TEST，存在mysqldump备份和日志，恢复步骤如下：

1. 先停止事件调度器

set global event_scheduler = off;

2. 删除数据库

drop database if exists TEST;

3. 新建数据库

create database TEST;

4. 导入备份

mysql --login-path=bak TEST < ./test.sql

说明: 导入的数据只是dump的数据, 不完整, 使用mysqlbinlog对二进制日志执行增量恢复. 使用mysqlbinlog进行增量日志恢复最重要的就是确定待恢复的起始位置(start-position)和终止位置(stop-position),起始位置(start-position)是我们执行全备之后的位置,而终止位置则是故障发生之前的位置

5.确认mysqldump备份到的最终位置

cat test.sql |grep "CHANGE MASTER"

输出: -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000015', MASTER_LOG_POS=155;

确认: 备份到了 15 号日志的 155 位置,那么恢复的起点可以设置为: 15 号日志的 155

6.确认要恢复的终点位置,需要到 binlog 里面确认

查看日志文件

ls /var/lib/mysql/binlog*

查看删除数据库的语句

mysqlbinlog -v /var/lib/mysql/binlog.000015|grep -i "drop database"

#方便起见,先将 binlog 定向到文本文件

mysqlbinlog -v /var/lib/mysql/binlog.000015 > binlog.000015.txt

到文本文件中查找关键字,如下

# at 1231

#210121 14:14:24 server id 1 end_log_pos 1335 CRC32 0x11b06910 Query thread_id=32 exec_time=0 error_code=0 Xid = 671

SET TIMESTAMP=1611209664/*!*/;

SET @@session.sql_auto_is_null=0/*!*/;

drop database TEST

确认: 结束点为 15 号日志的 1231 位置

7.增量恢复

起点: 15 号日志的 155 位置 结束: 15 号日志的 1231 位置

mysqlbinlog --start-position=155 --stop-position=1231 /var/lib/mysql/binlog.000015 | mysql -h 192.168.55.44 -u root -p********

注意,如果起点位置和终点位置不在一个日志文件里, 则起点日志指定开始位置,终点日志指定结束位置,中间的日志不指定

例如起点: 15 号日志的 155 位置 结束: 18 号日志的 1231 位置,这需要运行3条语句

mysqlbinlog --start-position=155 /var/lib/mysql/binlog.000015 | mysql -h 192.168.55.44 -u root -p********

mysqlbinlog /var/lib/mysql/binlog.000016 /var/lib/mysql/binlog.000017 | mysql -h 192.168.55.44 -u root -p********

mysqlbinlog --stop-position=1231 /var/lib/mysql/binlog.000018 | mysql -h 192.168.55.44 -u root -p********

总结

要获得时间点恢复, 应该指定 --single-transaction --master-data

对于 DML 操作, binlog 记录了所有的DML数据变化: 对于 insert, binlog 记录了insert的行数据, 对于 update, binlog 记录了改变前的行数据和改变后的行数据, 对于 delete, binlog 记录了删除前的数据. 假如用户不小心误执行了 DML 操作,可以使用 mysqlbinlog 将数据库恢复到故障点之前

对于 DDL 操作, binlog 只记录用户行为, 而不记录行变化, 但是并不影响我们将数据库恢复到故障点之前

总之, 使用 mysqldump 全备加 binlog 日志, 可以将数据恢复到故障前的任意时刻

当恢复一个备份时,该备份的语句将被记录到二进制日志中,这可能拖慢恢复进程,如果不希望恢复过程被写入二进制日志. 则可以使用 SET SQL_LOG_BIN = 0 ; 选项在 session 级别关闭这个功能

(echo "SET SQL_LOG_BIN=0;" ; cat full_backup.sql) | mysql -h 192.168.55.44 -u root -p********

或者

mysql>SET SQL_LOG_BIN=0; source full_backup.sql

常用参数

mysqldump -R -E --skip-extended-insert -u root -p123456 ent_info > ent_info.sql

-d 结构(--no-data:不导出任何数据,只导出数据库表结构)

-t 数据(--no-create-info: 只导出数据, 而不添加 CREATE TABLE 语句)

-n (--no-create-db: 只导出数据, 而不添加 CREATE DATABASE 语句)

-R (--routines: 导出存储过程以及自定义函数)

-E (--events: 导出事件)

--triggers (默认导出触发器, 使用 --skip-triggers 屏蔽导出)

-B (--databases: 导出数据库列表, 单个库时可省略)

--tables 表列表(单个表时可省略)

① 同时 导出结构以及数据时可同时省略 -d 和 -t

② 同时 不导出结构和数据可使用 -ntd

③ 只导出存储过程和函数可使用 -R -ntd

④ 导出所有(结构, 数据, 存储过程, 函数, 事件, 触发器) 使用 -R -E (相当于①, 省略了 -d -t 触发器默认导出)

⑤ 只导出结构 函数 事件 触发器使用 -R -E -d

ps: 如果使用工具, 比如官方的 MySQL Workbench, 则导入导出都是极为方便的(当然为了安全性,一般情况下都是屏蔽对外操作权限,所以需要使用命令的情况更多些)

--skip-extended-insert 每个insert插入一行

mysqldump备份我写了专门的备份脚本，可以实现指定保存份数等功能，

翻我以前的文章可找到。

未完待续， 下一次讲 mysqlpump备份恢复