码迷,mamicode.com
                                                                              首页 > 数据库 > 详细

                                                                              MySQLdump之single-transaction详解

                                                                              时间:2018-06-18 21:46:25      阅读:4449      评论:0      收藏:0      [点我收藏+]

                                                                              标签:不能   参数   repeat   from   vcc   lte   stat   efault   cte   

                                                                              MySQLdump之single-transaction详解

                                                                              single-transaction

                                                                              • 开启general log选项
                                                                              1. 查看目前general log的情况

                                                                                mysql> show variables like ‘%general_log%‘;
                                                                                +------------------+--------------------------------------------+
                                                                                | Variable_name    | Value                                      |
                                                                                +------------------+--------------------------------------------+
                                                                                | general_log      | OFF                                        |
                                                                                | general_log_file | /data/mysqldata/3306/general_statement.log |
                                                                                +------------------+--------------------------------------------+
                                                                                2 rows in set (0.00 sec)
                                                                              2. 开启general log的选项

                                                                                mysql> set global general_log=on;
                                                                              3. 使用mysqldump命令:。

                                                                                [[email protected] ~]$ /usr/local/mysql/bin/mysqldump -uroot -p[email protected] -S /data/mysqldata/3306/mysql.sock --single-transaction --default-character-set=utf8 zdemo student > /tmp/studentbackup.sql
                                                                                其中使用了两个参数
                                                                              • --single-transaction
                                                                                ??此选项会将隔离级别设置为:REPEATABLE READ。并且随后再执行一条START TRANSACTION语句,让整个数据在dump过程中保证数据的一致性,这个选项对InnoDB的数据表很有用,且不会锁表。但是这个不能保证MyISAM表和MEMORY表的数据一致性。
                                                                                ??为了确保使用--single-transaction命令时,保证dump文件的?#34892;?#24615;。需没有下列语句ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE,因为一致性读不能隔离上述语句。所以如果在dump过程中,使用上述语句,可能会导致dump出来的文件数据不一致或者不可用。
                                                                                ??如何验证上述的过程呢,可以开启general log看看过程是否如上述所说。

                                                                              • --default-character-set=utf8
                                                                                导出的dump文件字符集为uft8,检验文件字符集的命令可以使用file -i

                                                                              通用查询日志文件如下:

                                                                              2018-06-18T11:42:31.035205Z      9163 Query     /*!40100 SET @@SQL_MODE=‘‘ */
                                                                              2018-06-18T11:42:31.036090Z      9163 Query     /*!40103 SET TIME_ZONE=‘+00:00‘ */
                                                                              2018-06-18T11:42:31.036905Z      9163 Query     /*!80000 SET SESSION information_schema_stats_expiry=0 */
                                                                              2018-06-18T11:42:31.037521Z      9163 Query     SET SESSION NET_READ_TIMEOUT= 700, SESSION NET_WRITE_TIMEOUT= 700
                                                                              2018-06-18T11:42:31.038398Z      9163 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
                                                                              2018-06-18T11:42:31.038977Z      9163 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
                                                                              2018-06-18T11:42:31.039859Z      9163 Query     SHOW VARIABLES LIKE ‘gtid\_mode‘
                                                                              2018-06-18T11:42:31.058093Z      9163 Query     UNLOCK TABLES
                                                                              中间日志省略
                                                                              ......
                                                                              2018-06-18T11:42:31.084432Z      9163 Query     SAVEPOINT sp
                                                                              2018-06-18T11:42:31.087632Z      9163 Query     show create table `student`
                                                                              2018-06-18T11:42:31.088094Z      9163 Query     SET SESSION character_set_results = ‘utf8‘
                                                                              2018-06-18T11:42:31.088407Z      9163 Query     show fields from `student`
                                                                              2018-06-18T11:42:31.092360Z      9163 Query     show fields from `student`
                                                                              2018-06-18T11:42:31.094718Z      9163 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `student`
                                                                              2018-06-18T11:42:32.815435Z      9163 Query     ROLLBACK TO SAVEPOINT sp
                                                                              2018-06-18T11:42:32.815546Z      9163 Query     RELEASE SAVEPOINT sp

                                                                              从上述日志分析:
                                                                              SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 设置隔离级别为REPEATABLE READ
                                                                              START TRANSACTION 开启了事务
                                                                              ??事务的实现是通过InnoDB存储引擎的MVCC机制事项,细节如下:InnoDB?#19988;?#20010;多版本控制的存储引擎。它可以对已修改的行保留一个旧版本的数据信息。用于支持事务特性。例如:并发和数据回滚。这个信息保留在数据结构中的表空间中,这个表空间称之为rollback segment回滚段。(在Oracle中也有一种类似的数据结构)。
                                                                              ??当事务需要回滚的时候,InnoDB会使用回滚段的信息,用于执行undo操作。对于某一行,InnoDB会?#36855;?#20808;版本的信息来保障读一致性(consistent read)。
                                                                              ??Undo日志在回滚段(rollback segment)中被分为两部分,一部分叫做插入undo日志(insert undo logs),另外一部分叫做更新undo日志(update undo logs)。插入undo日志只用于事务回滚,如事务一旦提交,那么日志就可?#21592;?#20002;弃。更新undo日志?#36855;?#35835;一致性,InnoDB会指定一个数据快照,这个快照的构建来自于更新undo日志中数据行的早期版本。通过数据行早期版本?#30446;?#29031;来保证读一致性,如果不需要这种事务数据保护的时候,这个日志可?#21592;?#20002;弃。

                                                                              保存点的日志分析

                                                                              SAVEPOINT SP
                                                                              ......中间日志省略...
                                                                              SELECT /*!40001 SQL_NO_CACHE */ * FROM `student`
                                                                              ......中间日志省略...
                                                                              ROLLBACK TO SAVEPOINT sp
                                                                              RELEASE SAVEPOINT sp

                                                                              可以看到通过REPEATABLE READ事务,保证数据一致性数据,然后
                                                                              设置保存点sp,当读取了所有数据?#30446;?#29031;,就回退这个保存点sp。可?#21592;?#21947;为游戏中存档之后,然后取?#24403;?#20221;成一个游戏外的文件,删除这个档。可以当作这个档在这个游戏内不存在。

                                                                              查看当前会话级别

                                                                              ## 会话级当前事务级别
                                                                              mysql> show variables like ‘%isolation%‘;
                                                                              +-----------------------+-----------------+
                                                                              | Variable_name         | Value           |
                                                                              +-----------------------+-----------------+
                                                                              | transaction_isolation | REPEATABLE-READ |
                                                                              +-----------------------+-----------------+
                                                                              1 row in set (0.03 sec)
                                                                              ## 系统全局级当前事务级别
                                                                              mysql> show global variables like ‘%isolation%‘;
                                                                              +-----------------------+-----------------+
                                                                              | Variable_name         | Value           |
                                                                              +-----------------------+-----------------+
                                                                              | transaction_isolation | REPEATABLE-READ 
                                                                              +-----------------------+-----------------+
                                                                              1 row in set (0.11 sec)
                                                                              ## 修改全局级事务级别
                                                                              mysql>set global transaction_isolation=‘read-committed‘

                                                                              就算修改了全局事务级别,Mysqldump导出时也会设定隔离事务级别为:REPEATABLE READ。用于保证数据的读一致性。

                                                                              导出文件的字符集类型

                                                                              [[email protected] tmp]$ file -i studentbackup.sql 
                                                                              studentbackup.sql: text/plain; charset=utf-8

                                                                              MySQLdump之single-transaction详解

                                                                              标签:不能   参数   repeat   from   vcc   lte   stat   efault   cte   

                                                                              原文地址:https://www.cnblogs.com/zhangshengdong/p/9196128.html

                                                                              (0)
                                                                              (1)
                                                                                 
                                                                              举报
                                                                              评论 一句话评论(0
                                                                              0条  
                                                                              登录后才能评论!
                                                                              ? 2014 mamicode.com 版权所有 京ICP备13008772号-2
                                                                              迷上了代码!
                                                                              宁夏11选5开奖查询