Mysql mysqldump备份数据_mysqldump备份表数据

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

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

二、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:00         6329 Query     SHOW GLOBAL STATUS
2022-03-23T11:33:01.520364+08:00         6331 Connect   root@localhost on  using Socket
2022-03-23T11:33:01.520503+08:00         6331 Query     /*!40100 SET @@SQL_MODE='' */
2022-03-23T11:33:01.520588+08:00         6331 Query     /*!40103 SET TIME_ZONE='+00:00' */
2022-03-23T11:33:01.520684+08:00         6331 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2022-03-23T11:33:01.520738+08:00         6331 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2022-03-23T11:33:01.520830+08:00         6331 Query     SHOW VARIABLES LIKE 'gtid\_mode'
2022-03-23T11:33:01.522339+08:00         6331 Query     UNLOCK TABLES
2022-03-23T11:33:01.522487+08:00         6331 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 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:00         6331 Query     SELECT 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:00         6331 Query     SHOW VARIABLES LIKE 'ndbinfo\_version'
2022-03-23T11:33:01.525098+08:00         6331 Init DB   test
2022-03-23T11:33:01.525167+08:00         6331 Query     SHOW TABLES LIKE 'l'
2022-03-23T11:33:01.525322+08:00         6331 Query     SAVEPOINT sp
2022-03-23T11:33:01.525391+08:00         6331 Query     show table status like 'l'
2022-03-23T11:33:01.525586+08:00         6331 Query     SET SQL_QUOTE_SHOW_CREATE=1
2022-03-23T11:33:01.525645+08:00         6331 Query     SET SESSION character_set_results = 'binary'
2022-03-23T11:33:01.525707+08:00         6331 Query     show create table `l`
2022-03-23T11:33:01.525788+08:00         6331 Query     SET SESSION character_set_results = 'utf8'
2022-03-23T11:33:01.525859+08:00         6331 Query     show fields from `l`
2022-03-23T11:33:01.526134+08:00         6331 Query     show fields from `l`
2022-03-23T11:33:01.526400+08:00         6331 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `l` WHERE a=2
2022-03-23T11:33:01.526566+08:00         6331 Query     SET SESSION character_set_results = 'binary'
2022-03-23T11:33:01.526626+08:00         6331 Query     use `test`
2022-03-23T11:33:01.526687+08:00         6331 Query     select @@collation_database
2022-03-23T11:33:01.526758+08:00         6331 Query     SHOW TRIGGERS LIKE 'l'
2022-03-23T11:33:01.527033+08:00         6331 Query     SET SESSION character_set_results = 'utf8'
2022-03-23T11:33:01.527114+08:00         6331 Query     ROLLBACK TO SAVEPOINT sp
2022-03-23T11:33:01.527174+08:00         6331 Query     RELEASE SAVEPOINT sp
2022-03-23T11:33:01.527228+08:00         6331 Query     show events

① 备份前的准备

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ  
 
START 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-devel  openssl-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 可以重新导入新的数据库