Shulou(Shulou.com)06/01 Report--

Lecture notes:

The main contents are as follows:

1.mysqldump

2.mysqlcheck

3.mysqlbinlog

Parsing binlog

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

Mysql_upgrade (upgrade)

Update the table structure of the new version

Mysql_upgrade-S / tmp/mysql3306.sock

Perror (error code)

The security of database

Select user,password,host from mysql.user

After-class assignments:

Mysqldump

1. Describe the working mechanism of mysqldump (you can test the mysqldump process and analyze it with general_log)

These two parameters of mysqldump-- 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

Parse the ordinary log 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 # sets the isolation level of session transactions to repeatable

22 Query START TRANSACTION / *! 40100 WITH CONSISTENT SNAPSHOT * / transaction that starts a consistent snapshot

22 Query SHOW STATUS LIKE 'binlog_snapshot_%'

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

22 Query UNLOCK TABLES # release the lock

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 sets a SavePoint for the table

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 rollback to SavePoint sp (ensuring transaction consistency)

22 Query RELEASE SAVEPOINT sp release SavePoint 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

No parameter 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

By comparison, we can see that the lock is released at last without single_transaction, no rollback, no savepoing.

2. Mysqlbinlog parses a transaction

[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. Session. Gtids. domaincake idwise.

/ *! 100001 SET @ @ session.serveredulid71493306 Universe accounts /

/ *! 100001 SET @ @ session.gtidhands seqipment 284 accounting accounts /

BEGIN

/ *! * /

# at 423

# at 467

# 160727 23:03:03 server id 71493306 end_log_pos 467Table_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

# @ 1roomuu`

# @ 2room99

# SET

# @ 1 roommate test'

# @ 2room99

# at 677

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

Com _ MIT _ blank /

# End of log file

ROLLBACK / * added by mysqlbinlog * /

/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/

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

You can see from the parsed binlog:

The gtid number, the logpossition467 at the beginning of the transaction, and the logposition677 at the end of the transaction are modified to change the id=99 in the table test to test. Finally, implicitly submit.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.