Recently compiled mysql common command _ MySQL-mysql tutorial

Recently compiled mysql common command bitsCN.com

Recently compiled mysql common commands

--- View the log-binfile number and pos number of the master database

Mysql-uroot -pzqgame.com-e "show master status/G" | cut-d:-f 2 | sed-n'2, 3p'

--- View table information

Show table status/G;

--- Mysql records slow SQL execution:

Log =/tmp/mysqld. SQL

Log-slow-queries =/data/mysql/slowquery. log

Long_query_time = 2

--- View table creation statements

Show create table tablename;

--- View table structure

DESCRIBE table_name;

--- View the complete execution SQL

Show full processlist;

--- Display all processes running in the system.

Show processlist

--- View system resources

Show status

--- Check the variables defined in the my. cnf configuration file.

Show variables

--- View the error warning generated by the latest SQL statement and view the. err log for other statements.

Show warnings

--- View error

Show errors

--- View the load

/S;

-- Reset the mysql password

Service mysql stop

Mysqld_safe-skip-grant-table &

Mysql

Use mysql;

Update user set password = password ('test') where user = 'root ';

--- Changing the character set of a table

Alter table table_name convert to character set gbk/utf8;

Show variables like "% char % ";

SET character_set_client = 'gbk ';

SET character_set_connection = 'gbk ';

SET character_set_results = 'gbk ';

--- Query the size of all data

Select concat (round (sum (DATA_LENGTH/1024/1024), 2), 'mb') as data from information_schema.TABLES;

--- View the size of a specified database instance, for example, database forexpert

Select concat (round (sum (DATA_LENGTH/1024/1024), 2), 'mb') as data from information_schema.TABLES where table_schema = 'forexpert ';

--- View the table size of the specified database, for example, the member table in the database forexpert.

Select concat (round (sum (DATA_LENGTH/1024/1024), 2), 'mb') as data from information_schema.TABLES where table_schema = 'forexpert 'and table_name = 'member ';

--- View version number

Select verison ();

--- View the execution plan

Explain select...

--- Index failure repair

Analyze table table_name;

Show index from table_name;

--- Repair table

Repair table table_name;

--- Clear log files starting with mysql-bin

Purge master logs to 'MySQL-bin.010 ';

Purge master logs before '2017-12-19 21:00:00 ';

Note: If the slave server is completely synchronized with the MASTER server, you can use the reset master to delete these files.

---- Partition table

Query partition name:

Select

Partition_name part,

Partition_expression expr,

Partition_description descr,

Table_rows

From information_schema.partitions where

Table_schema = schema ()

And table_name = 'test ';

Alter table emp CHECK partition p1, p2;

This command tells you whether the data or index in p2 has been damaged in the partition p1 of the table emp. If this happens, use "alter table... repair partition" to fix the PARTITION.

---- Fix master-slave inconsistency

Pt-table-sync-execute-sync-to-master h = 10.232.31.109-databases test-uroot-p1234546

---- Mysql uninstall

Delete an rpm Package

Rpm-qa | grep-I mysql

Rpm-e...

Delete the configuration file (or rename it)

Rm/etc/my. cnf

Delete the datadir Directory

Rm/data/mysql

Delete mysql service

Rm/etc/init. d/mysql

Chkconfig -- del mysql

Delete scattered mysql folders

Whereis mysql

Delete separately

Rm-rf/usr/lib/mysql/

Rm-rf/usr/share/mysql

The preceding commands are commonly used commands recently compiled and will be improved later.

BitsCN.com