MySQL备份恢复

一、简介

逻辑备份：

备份内容：数据库的结构定义语句+数据内容的插入语句，备份出来的文件是可以编辑的。

使用场景：数据量少的数据库，比较适合100G数据量以内的。

逻辑备份的特点：

1）sql语句组成的文件，可以截取部分单独使用。

2）备份文件比物理的小；

3）可以细化到表/表的内容；

4)速度慢；

5）可以跨平台恢复，迁移

备份工具：

1） mysqldump（单线程）、mysqlpump（多线程）

2）mydumper:开源的，基于mysqldump的一个优化，多线程

执行mysqldump必须拥有的权限

2019.0110

mysqldump的权限说明

1、对于table 来说mysqldump 最少要有select 权限。

2、对于view 来说mysqldump 要有show view 权限。
3、对于trrigger 来说mysqldump 要有trriger 权限。
4、如果要产生一份一致的备份 mysqldump 要有lock tables 权限

二、mysqldump参数详解

2.1 mysqldump客户读取：

my.cnf

[client]

[mysqldump]

2.2 常用参数

-u --user

-p --password

-h --host

-P --port

--protocol=tcp,sockrt,memory

2.3 备份内容的常用参数

可以备份不同的数据库，表，函数，触发器，存储过程等；

-A all database

-B database

-y all-tablespace

-n no-create-db 不导出数据库结构

-t no-create-info Don't write table creation info. 不导出表结构

-d --no-data No row information. 不导出数据，只是导出结构

-R --routines Dump stored routines (functions and procedures). 导出存储过程和函数

--trigger Dump triggers for each dumped table. 导出表的对应触发器

--skip -trigger use --skip-triggers to disable不导出触发器；

-E 导出调度事件

--ignore-table=name Do not dump the specified table. Each table must be specified with both database and table names, e.g.,--ignore-table=database.table.不导出表

-w, --where=name Dump only selected records. Quotes are mandatory.

2.4 备份事务和锁相关的选项

--sing-transaction 可以得到一致性的导出结果，只是针对innodb存储引擎，导出过程中不允许DDL操作,因为事务持有锁的元数据，而DDL会申请metadata的的互斥锁，

该参数会关闭你的默认的--lock-tables选项，只能加 --master-data才能加锁。cv

通过将导出操作封装在一个事务内来使得导出的数据是一个一致性快照。只有当表使用支持MVCC的存储引擎（目前只有InnoDB）时才可以工作；其他引擎不能保证导出是一致的。当导出开启了–single-transaction选项时，要确保导出文件有效（正确的表数据和二进制日志位置），就要保证没有其他连接会执行如下语句：ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE，这会导致一致性快照失效。这个选项开启后会自动关闭–lock-tables。

--lock-tables 默认打开的，这个锁是导一个锁一个，导出完毕后解锁。

--lock-all-tables 会把所有的表先锁了，等导出后在解锁；

上面三个参数是互斥的，只能用一个。

--flush-log 导出数据时候刷新二进制日志，达到一致性的目的；

--flush-privileges 导出权限

--master-data 有三个参数

0 默认是0 ，不写入binlog日志记录

1 在导出的文件中会加入一句 change master to.....记录binlog文件及终点

2 将 change master to 这条语句注释

–master-data
这个选项可以把binlog的位置和文件名添加到输出中，如果等于1，将会打印成一个CHANGE MASTER命令；如果等于2，会加上注释前缀。并且这个选项会自动打开–lock-all-tables，除非同时设置了–single-transaction（这种情况下，全局读锁只会在开始dump的时候加上一小段时间，不要忘了阅读–single-transaction的部分）。在任何情况下，所有日志中的操作都会发生在导出的准确时刻。这个选项会自动关闭–lock-tables。

简单的说，就是主从复制在做全量备份的时候，–master-data 选项可以自动帮我们锁表和识别binlog临界文件，就不需要我们锁表。再看临界文件编号，再执行CHANGE MASTER填写binglong位置信息到从库master.info文件中了，提高了从库部署效率。

举例：

mysqldump -uroot -p --single-transaction --master-date=2 --flush-logs --flush-privileges --routines jl>jlbak.sql

2.5 mysqldump与复制相关的参数

--master-data

--dump-slave 和master-data一样，在从库上面使用;

--apply-slave-statements 和 --master-slave=1类似

--set-gtid-purge 开启了gtid环境特性

2.7 字符集相关的参数

--set-charset 默认开启，--set-charset=1/0 是否开启字符；

--default-character-set 指定是什么字符集

2.8 mysqldump添加是否生成DDL语句

--add-drop-database

--add-drop-tables 默认打开

--skip-add-drop-tables 禁用删除表

no-create-db；-n 不创建数据库

no-create-tables；-t 不创建表

2.9 其他参数

-f ，--force 强制性导出

--log-error =/mysql/dum.log

--compatible=(oracle/mysql/postgresql) 兼容性参数

三、MySQL常用的备份案例

3.1 导出所有数据库

[root@localhost ~]# mysqldump -uroot -p --all-databases>allbak.sql

3.2 导出所有的库结构、表结构

[root@localhost ~]# mysqldump -uroot -p --all-databases --no-data>struct.sql

3.3 导出库里边的一张表

[root@localhost ~]# mysqldump -uroot -p jl yddl2>yddl2.sql

3.4 只导出表结构

[root@localhost ~]# mysqldump -uroot -p --no-data jl yddl2>structureyddl2.sql

3.5 导出多个表的数据和结构

[root@localhost ~]# mysqldump -uroot -p jl yddl2 yddl3>struct4.sql

3.5 导出单个数据库

[root@localhost ~]# mysqldump -uroot -p  --databases jl>only.sql

3.6 导出指定数据库结构

[root@localhost ~]# mysqldump -uroot -p --no-data  --databases jl>only.sql

3.7 导出指定数据库只要数据，不要结构

[root@localhost ~]# mysqldump -uroot -p --no-create-db --no-create-info --databases jl>nocreate.sql

3.8 导出数据库，忽略某张表

[root@localhost ~]# mysqldump -uroot -p --databases jl --ignore-table=jl.yddl3>ydd.sql

3.9 按照条件导出

[root@localhost ~]# mysqldump -uroot -p --skip-add-drop-table jl yddl2 --where="id=10">whe.sql

3.10 导出不要带删除库及表的语句

[root@localhost ~]# mysqldump -uroot -p --skip-add-drop-database --skip-add-drop-table --databases jl>sk.sql

四、mysqldump的原理

4.1 想要了解具体的原理，首先打开general log

vi  /etc/my.cnf
general_log=1
general_file=/mysql/log/general.log

4.2 查看general的日志

2022-10-22T04:40:16.129442Z        2 Query    SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%partitioned%';
2022-10-22T06:39:16.316755Z        3 Connect    root@localhost on  using Socket
2022-10-22T06:39:16.320221Z        3 Query    /*!40100 SET @@SQL_MODE='' */
2022-10-22T06:39:16.322239Z        3 Query    /*!40103 SET TIME_ZONE='+00:00' */
2022-10-22T06:39:16.323422Z        3 Query    FLUSH /*!40101 LOCAL */ TABLES
2022-10-22T06:39:16.335938Z        3 Query    FLUSH TABLES WITH READ LOCK
2022-10-22T06:39:16.336494Z        3 Refresh    
/mysql/app/mysql/bin/mysqld, Version: 5.7.20-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
2022-10-22T06:39:16.351702Z        3 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2022-10-22T06:39:16.352023Z        3 Query    START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2022-10-22T06:39:16.352849Z        3 Query    SHOW VARIABLES LIKE 'gtid\_mode'
2022-10-22T06:39:16.370475Z        3 Query    SHOW MASTER STATUS
2022-10-22T06:39:16.371150Z        3 Query    UNLOCK TABLES
2022-10-22T06:39:16.372100Z        3 Query    SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2022-10-22T06:39:16.374833Z        3 Query    SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2022-10-22T06:39:16.375847Z        3 Query    SHOW DATABASES
2022-10-22T06:39:16.377685Z        3 Query    SHOW VARIABLES LIKE 'ndbinfo\_version'
2022-10-22T06:39:16.381541Z        3 Init DB    jl
2022-10-22T06:39:16.381996Z        3 Query    SHOW CREATE DATABASE IF NOT EXISTS `jl`
2022-10-22T06:39:16.382428Z        3 Query    SAVEPOINT sp
2022-10-22T06:39:16.383161Z        3 Query    show tables
2022-10-22T06:39:16.384141Z        3 Query    show table status like 'qh'
2022-10-22T06:39:16.400586Z        3 Query    SET SQL_QUOTE_SHOW_CREATE=1
2022-10-22T06:39:16.401221Z        3 Query    SET SESSION character_set_results = 'binary'
2022-10-22T06:39:16.401711Z        3 Query    show create table `qh`
2022-10-22T06:39:16.402287Z        3 Query    SET SESSION character_set_results = 'utf8'
2022-10-22T06:39:16.408410Z        3 Query    show fields from `qh`

4.3 具体流程梳理

1) 连接数据库

2022-10-22T06:39:16.316755Z        3 Connect    root@localhost on  using Socket

2）关闭打开的表，并刷新日志

2022-10-22T06:39:16.323422Z        3 Query    FLUSH /*!40101 LOCAL */ TABLES
2022-10-22T06:39:16.335938Z        3 Query    FLUSH TABLES WITH READ LOCK

3）执行一个flush tabeles 会加一个全局锁

2022-10-22T06:39:16.335938Z        3 Query    FLUSH TABLES WITH READ LOCK

上全局读锁；

清理表缓存；

上全局commit锁。

4）设置隔离级别为重复读

2022-10-22T06:39:16.351702Z        3 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

5）获取当前数据库的快照，有 --single-transaction决定，只支持事务的表，innodb存储引擎

2022-10-22T06:39:16.352023Z        3 Query    START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

主要开启事务的时候，对所有表做一个select操作，得到一个快照，备份时候就可以一致。

6）由参数 --master-data参数决定，记录了开始备份时候，binlog的状态信息，包括binlog file和binlog postion

2022-10-22T06:39:16.370475Z        3 Query    SHOW MASTER STATUS

7）释放锁

2022-10-22T06:39:16.371150Z        3 Query    UNLOCK TABLES

8）查看要备份的数据库

2022-10-22T06:39:16.375847Z        3 Query    SHOW DATABASES

9）开始备份数据库

所有的备份不包括 information_schema ,sys,performanct_schema

如果要备份这三个数据库 mysqldump -uroot -p --skip-lock-tables --database information_schema sys performanct_schema

10) 没有缓存

2022-10-22T06:39:16.411610Z 3 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `qh`

11）备份触发器

    SHOW TRIGGERS LIKE 'plugin'

12）保存点

SAVEPOINT sp
ROLLBACK TO SAVEPOINT sp
RELEASE SAVEPOINT sp

这样做的好处是，不会阻塞在备份期间对已经备份表的DDL操作

4.4 总结

mysqldump的大致实现过程是：

连接；

初始化信息；

刷新表（锁表）

记录偏移量；

开启事务；

记录偏移量；

解锁表；

查看要备份的表；

开始备份数据；

4.5 重要参数

--lock-tables 会在整个导出过程中，lock read local 所有的表，innodb的表只能读，其他引擎的可读可写；

--lock-all-tables 请求一个全局读锁，会阻塞所有的写入操作（insert，update，delete），保证数据的一致性，备份完成后回话断开，自动解锁；

--single-transaction 单独使用不会有任何锁，但会对备份的表持有metedata lock的共享锁

--single-transaction 和 --master-data 结合使用：开始的时候会短暂请求一个全局的读锁，会阻止所有的写入操作。

五、生产环境的最佳备份命令

5.1 全备

innodb

[root@localhost ~]# mysqldump -uroot -p --single-transaction --master-data=2 --flush-logs --flush-privileges --routines --all-databases>fullbake.sql

myisam

[root@localhost ~]# mysqldump -uroot -p --lock-all-tables --master-data=2 --flush-logs --flush-privileges --routines --all-databases>fullbake.sql

posted @ 2022-10-25 10:39 中仕阅读(22) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

MySQL备份恢复

mysqldump的权限说明

公告