Common daily management tools for MySQL

mysqldump

"--opt Default Options"

-A full library-b specifies library-e scheduled task-r stored Procedure function

MYSQLDUMP-UROOT-PROOT-E-r-a Exports are stored procedures, functions, and scheduled tasks based on-a

MYSQLDUMP-UROOT-PROOT-E-r-b exports are B-based stored procedures, functions, and Scheduled Tasks

--default-character-set=utf8 default Character Set UTF8

--lock-all-tables Commit requests lock all tables in all databases to ensure data consistency, resulting in implicit commit of transactions

--master-data This option appends the location and file name of the Binlog to the output file

--dump-slave This option appends the Binlog location and file name of the master to the file in which the data is exported

--flush-logs Refresh log before starting export

--ignore-table=mysql.user--ignore-table=mysql.db

--no-create-db,-n exports only data without adding the Create DATABASE statement.

--no-create-info,-t exports only data without adding the Create TABLE statement.

--no-data,-D does not export any data, only the database table structure is exported.

--single-transaction This option does not block any applications and ensures consistent state of the database at the time of export.

--include-master-host-port add ' master_host=

To export a table structure:

Mysqldump-e-r-b db_xx--no-data--skip-add-drop-table-u user_xx-p pwd_xx >xxx.sql

From the data on the guide:

Mysqldump-e-r-b db_xx--dump-slave--single-transaction--skip-add-drop-table-u user_xx-p pwd_xx >xxx.sql

Our guide data:

Mysqldump-e-r-b db_xx--master-data--single-transaction--skip-add-drop-table-u user_xx-p pwd_xx >xxx.sql

MYSQLDUMP-E-r-b Insurance--no-data--skip-add-drop-table-u root-p >insurance20160331.sql

Mysqldumpslow

*-S, which means to sort by how

C Record Count T time L query time R back record number

AC, at, AL, AR, indicating the corresponding reverse;

*-T, is the meaning of top N, that is, to return the number of previous data;

*-G, behind can write a regular matching pattern, case-insensitive;

Mysqldumpslow-s c-t 10/database/mysql/slow-log

Mysqlbinlog

Mysqlbinlog-u user_xx-p Pwd_xx-p3306-hlocalhost

-d database name-R Read binary log

--start-datetime=datetime specified start time,--start-datetime= ' 2010-09-04 9:59:59 '

--stop-datetime=datetime Specify end time

--start-position= number Specify start position

--stop-position= number Specifies the end position (more precise based on position)

--no-default do not use default values

Mysqlbinlog--start-position=134--stop-position=330 mysqlbin-log.000001 >test1.txt

Mysqlbinlog--start-datetime= ' 2016-03-10 21:00:00 '

Mysqlbinlog-uroot-p-hlocalhost-vv--start-datetime= ' 2016-03-10 21:00:00 ' Mysql-bin.000098|more

Common daily management tools for MySQL