研究mysqldump对我的数据库做了哪些操作

最新推荐文章于 2024-04-21 22:51:21 发布

东晔·

最新推荐文章于 2024-04-21 22:51:21 发布

阅读量74

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/zhangshengdongly/article/details/96426322

通过开启generallog并观察mysqldump后的generallog日志，验证了开启事务、设置默认字符集及选择特定数据库表的操作，确保数据的一致性和唯一性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/usr/local/mysql55/bin/mysqldump -uroot -S /data/mysqldata/3306/mysql.sock --single-transaction --default-character-set=gbk test t10>/usr/tmp/testzsd.sql

--single-transaction

--default-character-set=gbk

test t10

从官方文档看，上面的选项操作分别是：开启了事务，保证数据一致性，设置了默认字符集，选择了test数据库的t10表，但我却想验证上面的操作，是否对我的数据库做了如上操作：

方法：开启general log，观察mysqldump之后的general log日志。

mysql> show variables like '%log%';

mysql> set global general_log=on;

查看相应日志：

120713 15:41:53 20 Connect root@localhost on

20 Query /*!40100 SET @@SQL_MODE='' */

20 Query /*!40103 SET TIME_ZONE='+00:00' */

20 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ（这里的级别是repeatable read）

20 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */（确实开启了日志，为了获得一致性快照）

20 Query UNLOCK TABLES

20 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 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 ('t10'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME

120713 15:41:54 20 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 ('t10')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

20 Init DB test

20 Query SHOW TABLES LIKE 't10'

20 Query show table status like 't10'

20 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1

20 Query SET SESSION character_set_results = 'binary'

20 Query show create table `t10`

20 Query SET SESSION character_set_results = 'gbk'

20 Query show fields from `t10`

20 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t10`

120713 15:42:02 20 Query SET SESSION character_set_results = 'binary'

20 Query use `test`

20 Query select @@collation_database