mysql switch binlog_mysqldump，mysqlbinlog

听课笔记：

主要讲解内容：

1.mysqldump

2.mysqlcheck

3.mysqlbinlog

解析binlog

mysqlbinlog -v --base64-output=decode-rows binglogname >/dir/dir.txt

mysql_upgrade (升级)

更新一下新版本的表结构

mysql_upgrade -S /tmp/mysql3306.sock

perror (错误代码)

数据库的安全

select user，password，host from mysql.user;

课后作业：

mysqldump

1、描述mysqldump的工作机制(可以测试mysqldump过程，借助general_log分析)

mysqldump 的这2个参数 --single-transaction --master-data

time mysqldump -q --single-transaction --master-data  --opt -R  --triggers  uu test > test.sql

real    0m0.045s

user    0m0.010s

sys    0m0.007s

解析一下普通的日志general_log

[root@manager mysql]# cat manager.log

160727 19:29:57       22 Connect    root@localhost as anonymous on

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

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

22 Query    SHOW STATUS LIKE 'binlog_snapshot_%'

22 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ   #设置会话事务的隔离级别为可重复读

22 Query    START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */     开启一个一致性快照的事务

22 Query    SHOW STATUS LIKE 'binlog_snapshot_%'

22 Query    SELECT BINLOG_GTID_POS('mysql3306-mysql-bin.000017', '1176')

22 Query    UNLOCK TABLES                                                   #释放锁

22 Query    set optimizer_switch='semijoin=off'

22 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='uu' AND TABLE_NAME IN ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME

22 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='uu' AND TABLE_NAME IN ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

22 Query    set optimizer_switch=default

22 Init DB    uu

22 Query    SHOW TABLES LIKE 'test'

22 Query    SAVEPOINT sp       设置表的一个保存点

22 Query    show table status like 'test'

22 Query    SET SQL_QUOTE_SHOW_CREATE=1

22 Query    SET SESSION character_set_results = 'binary'

22 Query    show create table `test`

22 Query    SET SESSION character_set_results = 'utf8'

22 Query    show fields from `test`

22 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`

22 Query    SET SESSION character_set_results = 'binary'

22 Query    use `uu`

22 Query    select @@collation_database

22 Query    SHOW TRIGGERS LIKE 'test'

22 Query    SET SESSION character_set_results = 'utf8'

22 Query    ROLLBACK TO SAVEPOINT sp    回滚到保存点sp (保证事务的一致性)

22 Query    RELEASE SAVEPOINT sp        释放保存点sp

22 Query    use `uu`

22 Query    select @@collation_database

22 Query    SET SESSION character_set_results = 'binary'

22 Query    SHOW FUNCTION STATUS WHERE Db = 'uu'

22 Query    SHOW PROCEDURE STATUS WHERE Db = 'uu'

22 Query    SET SESSION character_set_results = 'utf8'

22 Quit

不加参数sigle_transaction

ime mysqldump -q   --opt -R  --triggers  uu test > test.sql

real    0m0.040s

user    0m0.010s

sys    0m0.008s

cat manager.log

160727 19:56:49       26 Connect    root@localhost as anonymous on

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

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

26 Query    set optimizer_switch='semijoin=off'

26 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='uu' AND TABLE_NAME IN ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE ORDER BY LOGFILE_GROUP_NAME

26 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='uu' AND TABLE_NAME IN ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

26 Query    set optimizer_switch=default

26 Init DB    uu

26 Query    SHOW TABLES LIKE 'test'

26 Query    LOCK TABLES `test` READ /*!32311 LOCAL */

26 Query    show table status like 'test'

26 Query    SET SQL_QUOTE_SHOW_CREATE=1

26 Query    SET SESSION character_set_results = 'binary'

26 Query    show create table `test`

26 Query    SET SESSION character_set_results = 'utf8'

26 Query    show fields from `test`

26 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test`

26 Query    SET SESSION character_set_results = 'binary'

26 Query    use `uu`

26 Query    select @@collation_database

26 Query    SHOW TRIGGERS LIKE 'test'

26 Query    SET SESSION character_set_results = 'utf8'

26 Query    LOCK TABLES mysql.proc READ

26 Query    use `uu`

26 Query    select @@collation_database

26 Query    SET SESSION character_set_results = 'binary'

26 Query    SHOW FUNCTION STATUS WHERE Db = 'uu'

26 Query    SHOW PROCEDURE STATUS WHERE Db = 'uu'

26 Query    SET SESSION character_set_results = 'utf8'

26 Query    UNLOCK TABLES

26 Query    UNLOCK TABLES

26 Quit

通过比较可以看出：不加single_transaction最后才释放锁，没有rollback，没有savepoing

2、mysqlbinlog解析一个事务

[root@manager data]# mysqlbinlog -v --base64-output=decode-rows mysql3306-mysql-bin.000020

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#160727 20:32:11 server id 71493306  end_log_pos 248     Start: binlog v 4, server v 10.0.22-MariaDB-log created 160727 20:32:11

# at 248

#160727 20:32:11 server id 71493306  end_log_pos 287     Gtid list [0-71493306-283]

# at 287

#160727 20:32:11 server id 71493306  end_log_pos 336     Binlog checkpoint mysql3306-mysql-bin.000019

# at 336

#160727 20:32:11 server id 71493306  end_log_pos 385     Binlog checkpoint mysql3306-mysql-bin.000020

# at 385

#160727 23:03:03 server id 71493306  end_log_pos 423     GTID 0-71493306-284

/*!100001 SET @@session.gtid_domain_id=0*//*!*/;

/*!100001 SET @@session.server_id=71493306*//*!*/;

/*!100001 SET @@session.gtid_seq_no=284*//*!*/;

BEGIN

/*!*/;

# at 423

# at 467

#160727 23:03:03 server id 71493306  end_log_pos 467     Table_map: `uu`.`test` mapped to number 76

#160727 23:03:03 server id 71493306  end_log_pos 677     Update_rows: table id 76 flags: STMT_END_F

### UPDATE `uu`.`test`

### WHERE

###   @1='uu'

###   @2=99

### SET

###   @1='test'

###   @2=99

# at 677

#160727 23:03:03 server id 71493306  end_log_pos 704     Xid = 460

COMMIT/*!*/;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

从解析出来的binlog中可以看到：

gtid号，事务开始的logpossition467 ,和结束的logposition677 ，所做的修改为更改表test中id=99的改为test。最后隐式提交。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30162734/viewspace-2122681/，如需转载，请注明出处，否则将追究法律责任。