Common tools in MySQL

View currently logged in user:mysql> select Current_User ();

Character Set settings:

mysql> set names utf8mb4;

Mysql> Show variables like ' chara% ';

-e Execute SQL statement and exit

-E Vertical Display

-S Remove Line box display

-F Force Execution

-V Show More information

--show-warnings Display Warning

Mysqlbinlog

mysql> flush logs;

mysql> use MySQL;

Mysql> REVOKE Process on * * from [email protected];

mysql> use test;

mysql> truncate TABLE T2;

mysql> INSERT INTO T2 values (1), (2), (3);

Show all logs without any parameters:

[Email protected] 3306]#/usr/bin/mysqlbinlog mysql3306-bin.000009

The-D option will show only the operation log for the test database:

[Email protected] 3306]#/usr/bin/mysqlbinlog mysql3306-bin.000009-d test

Add-o option, ignoring the first 3 actions:

[Email protected] 3306]#/usr/bin/mysqlbinlog mysql3306-bin.000009-o 3

Add-r results output to file:

[Email protected] 3306]#/usr/bin/mysqlbinlog mysql3306-bin.000009-o 3-r/tmp/resultfile

Plus-S simple display:

[Email protected] 3306]#/usr/bin/mysqlbinlog mysql3306-bin.000009-s-o 3

Filter logs based on time and POS points:

[Email protected] 3306]#/usr/bin/mysqlbinlog mysql3306-bin.000009--start-datetime= "2016/05/05 13:54:41"-- Stop-datetime= "2016/05/05 13:55:48";

[Email protected] 3306]#/usr/bin/mysqlbinlog mysql3306-bin.000009--start-position=352--stop-position=628;

Check the MyISAM table:

[Email protected] wqh]#/usr/bin/mysqlcheck-p3306--socket=/data/3306/mysql.sock wqh-c T1

Fix table:

[Email protected] wqh]#/usr/bin/mysqlcheck-p3306--socket=/data/3306/mysql.sock wqh-r T1

Analysis Table:

[Email protected] wqh]#/usr/bin/mysqlcheck-p3306--socket=/data/3306/mysql.sock wqh-a T1

Optimization table:

[Email protected] wqh]#/usr/bin/mysqlcheck-p3306--socket=/data/3306/mysql.sock wqh-o T1

Mysqldump

--add-drop-database

--add-drop-table

-N--no-create-db

-T--no-create-info

-D--no-data

--compact output results are simple

The insert of the-C--complete-insert Output statement includes the field name

-t backup for data files and build table files

Set the exported client character set:

--default-character-set=utf8mb4

-F--flush-logs Refresh Log

-L--lock-tables to read locks on all tables

Mysqlshow

[Email protected] wqh]#/usr/bin/mysqlshow-p3306--socket=/data/3306/mysql.sock

[Email protected] wqh]#/usr/bin/mysqlshow-p3306--socket=/data/3306/mysql.sock--count

[Email protected] wqh]#/usr/bin/mysqlshow-p3306--socket=/data/3306/mysql.sock wqh--count

[Email protected] wqh]#/usr/bin/mysqlshow-p3306--socket=/data/3306/mysql.sock wqh T6--count

[Email protected] wqh]#/usr/bin/mysqlshow-p3306--socket=/data/3306/mysql.sock MySQL user-k

Mysql> Show full columns from WQH.T7;

Mysql> Show index from WQH.T7;

[[email protected] wqh]#/usr/bin/mysqlshow-p3306--socket=/data/3306/mysql.sock mysql user--status

[Email protected] wqh]#/usr/bin/mysqlshow-p3306--socket=/data/3306/mysql.sock MySQL user-i

Mysql> Show Table status from Wqh like "T5";

Error code viewing tool:

[Email protected] wqh]# Perror 30 60

[Email protected] wqh]#/usr/bin/perror 60

MySQL comes with a text replacement tool:

Coverage mode "--":

[email protected] ~]# Cat A

A1 A2 A3

B1 B2 B3

[[Email protected] ~]# Replace A1 AA1 B1 BB1--A

A converted

[email protected] ~]# Cat A

AA1 A2 A3

BB1 B2 B3

[Email protected] ~]#

Non-covered mode "<":

[email protected] ~]# Cat A

AA1 A2 A3

BB1 B2 B3

[[Email protected] ~]# Replace a C b d < A # replace the A in file a with a c,b to D.

CC1 C2 C3

DD1 D2 D3

[email protected] ~]# Cat A

AA1 A2 A3

BB1 B2 B3

[Email protected] ~]#

Common tools in MySQL