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。最后隱式提交。