MySQL8备份恢复（一）

末蓝、 2024-03-24 13:58 7阅读 0赞

### 备份工具说明 ###

**GUI工具: navicat**

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

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

### Navicat备份恢复 ###

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

#### backup/restore ####

backup: 打开数据库 -> Backups右键 -> New Backup 备份文件 $  
HOME/.config/navicat/settings/0/0/MySQL//<日期时间>.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;

1.  删除数据库

drop database if exists TEST;

1.  新建数据库

create database TEST;

1.  导入备份

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插入一行