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。最后隐式提交。
网站标题：mysqldump，mysqlbinlog
路径分享：http://myzitong.com/article/gosesi.html
主要讲解内容：
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。最后隐式提交。
网站标题：mysqldump，mysqlbinlog
路径分享：http://myzitong.com/article/gosesi.html