Mysql mysqldump备份数据 mysql

mysqldump客户端实用程序执行逻辑备份，生成一组 SQL 语句，可以执行这些语句来重现原始数据库对象定义和表数据。它转储一个或多个 MySQL 数据库以进行备份或传输到另一台 SQL 服务器。mysqldump 命令还可以生成 CSV、其他分隔文本或 XML 格式的输出。

一、逻辑备份和物理备份的区别

文章图片

【Mysql mysqldump备份数据】

二、mysqldump备份数据

① 备份命令

mysqldump --single-transaction -B test > back.sql## -B 表示备份单个数据库 -A表示所有的数据库

--single-transaction 备份前创建一个事务进行备份操作
② 比较全的备份命令

mysqldump --single-transaction -R --triggers -E -B test > back.sql

-R 在输出中包括转储数据库的存储例程（过程和函数）
-E 定时器
--triggers 触发器备份
③ 备份表

mysqldump --single-transaction -R --triggers -E test ll > back.sql

备份test数据库中的ll表
④ 通过where查询进行备份

mysqldump --single-transaction -R --triggers -E test l -w "a=2"> back4.sql

-w 可以加where添加进行备份， -w "a = 2" 则是where的条件
⑤ 备份到另外一台服务器

mysqldump --single-transaction -R --triggers -E test l -w "a=2" | gzip -c | sshpass -p '123456' ssh root@192.168.0.1 'cat > /tmp/back2.sql.gz'

通过连接ssh进行备份
⑥ 导入数据
mysql < back2.sql

三、mysqldump背后实现sql怎么看

可以把general_log set global general_log = 1;
打开然后观察一下mysqldump的执行

2022-03-23T11:33:00.595914+08:006329 QuerySHOW GLOBAL STATUS 2022-03-23T11:33:01.520364+08:006331 Connectroot@localhost onusing Socket 2022-03-23T11:33:01.520503+08:006331 Query/*!40100 SET @@SQL_MODE='' */ 2022-03-23T11:33:01.520588+08:006331 Query/*!40103 SET TIME_ZONE='+00:00' */ 2022-03-23T11:33:01.520684+08:006331 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 2022-03-23T11:33:01.520738+08:006331 QuerySTART TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ 2022-03-23T11:33:01.520830+08:006331 QuerySHOW VARIABLES LIKE 'gtid\_mode' 2022-03-23T11:33:01.522339+08:006331 QueryUNLOCK TABLES 2022-03-23T11:33:01.522487+08:006331 QuerySELECT 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 AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME IN ('l'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME 2022-03-23T11:33:01.523576+08:006331 QuerySELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME IN ('l')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME 2022-03-23T11:33:01.524216+08:006331 QuerySHOW VARIABLES LIKE 'ndbinfo\_version' 2022-03-23T11:33:01.525098+08:006331 Init DBtest 2022-03-23T11:33:01.525167+08:006331 QuerySHOW TABLES LIKE 'l' 2022-03-23T11:33:01.525322+08:006331 QuerySAVEPOINT sp 2022-03-23T11:33:01.525391+08:006331 Queryshow table status like 'l' 2022-03-23T11:33:01.525586+08:006331 QuerySET SQL_QUOTE_SHOW_CREATE=1 2022-03-23T11:33:01.525645+08:006331 QuerySET SESSION character_set_results = 'binary' 2022-03-23T11:33:01.525707+08:006331 Queryshow create table `l` 2022-03-23T11:33:01.525788+08:006331 QuerySET SESSION character_set_results = 'utf8' 2022-03-23T11:33:01.525859+08:006331 Queryshow fields from `l` 2022-03-23T11:33:01.526134+08:006331 Queryshow fields from `l` 2022-03-23T11:33:01.526400+08:006331 QuerySELECT /*!40001 SQL_NO_CACHE */ * FROM `l` WHERE a=2 2022-03-23T11:33:01.526566+08:006331 QuerySET SESSION character_set_results = 'binary' 2022-03-23T11:33:01.526626+08:006331 Queryuse `test` 2022-03-23T11:33:01.526687+08:006331 Queryselect @@collation_database 2022-03-23T11:33:01.526758+08:006331 QuerySHOW TRIGGERS LIKE 'l' 2022-03-23T11:33:01.527033+08:006331 QuerySET SESSION character_set_results = 'utf8' 2022-03-23T11:33:01.527114+08:006331 QueryROLLBACK TO SAVEPOINT sp 2022-03-23T11:33:01.527174+08:006331 QueryRELEASE SAVEPOINT sp 2022-03-23T11:33:01.527228+08:006331 Queryshow events

文章图片

① 备份前的准备

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READSTART TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

A) mysqldump 是在RR的隔离级别进行备份的
B) 是立马开启事务的，不是begin; 而是 START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */，因为begin; 开启事务，真正创建事务是begin后的第一条sql开始的。所以假如用mysqldump备份数据需要30分钟，那么备份后的数据是在30分钟前的，而不是实时的。
② SAVEPOINT设置保存点，并和rollback结合使用，实现回滚到指定保存点
这样的好处是为了，当我们查询完表元数据时，可以立马回滚，这样导出的事务不会造成其他事务同时操作表元数据时，产生阻塞。也就是元数据上面的MDL锁。

四、mysqldumper

因为mysqldump是单线程进行备份的，所以我们一般使用mydumper进行备份。
mydumper工具是可以支持多线程的逻辑备份工具，比mysqldump的性能更好，能够支持大数据量的逻辑表备份，测试的结果是可以支持一库多表或全表，不支持多库同时备份，在centons6.5及mysql5.6版本上是可以使用的
① 安装 mydumper
A) 、下载mydumper 源代码 mydumper-0.9.1.tar.gz
B)、安装依赖包
Mysqldumper工具的一些依赖包需要提前安装
#yum -y install glib2-devel mysql-devel zlib-developenssl-devel
C)、安装dumper工具及验证
#tar -xzvf mydumper-0.9.1.tar.gz
#cd mydumper-0.9.1/
#cmake .
#make & make install
安装成功就会有 mydumper 和 myloader的两个命令
② 备份文件
mydumper -G -E -R --trx-consistency-only -t 4 -c -B test-o backup_20220323
-G触发器
-E定时器
-R存储例程（过程和函数）
-t开启线程个数
-B数据库
-c压缩输出文件
-o 文件路径
③ 导入数据
myloader -d /usr/local/back.tar -t 4 -B sb
-B 可以重新导入新的数据库