MySQL Common Operations Command collection

To view the number of connections

Show Processlist; You can also check the Information_schema.processlist table.

View the definition/default parameters

Show variables like '% parameter name% ';

To cancel a user's permissions:

Revoke SELECT, EXECUTE, SHOW VIEW on ' TSDP '. * from ' tsdp_ro ' @ ' 10.58.%.% ';

changing parameters

Set session parameter name = value;

Set global parameter name = value;

View status values

Show global status;

Show status like '% parameter name% ';

View synchronization Status

Show slave status\g from sync change Master to .....

Create User/Assign Permissions

Create user [email protected]' 111.111.111.111 ' identified by ' 123456 ';

Grant all on clus.* to [email protected]' 111.111.111.111 ';

View triggers

Use Clus;

Show triggers;

Show create trigger triggername; You can look at the Information_schema.trigger difference.

viewing stored procedures

Use Clus;

Show procedure status;

Show CREATE PROCEDURE procedurename;

View Event

Use Clus;

Show events;

Show Create Event eventname;

Build Library

CREATE DATABASE IF not EXISTS yourdbname DEFAULT CHARSET UTF8 COLLATE utf8_general_ci;

Add Field

ALTER TABLE tablename ADD column name varchar (10);

Add a self-increment primary key field

ALTER TABLE tablename add column ' ID ' int primary key auto_increment;

modifying field types

ALTER TABLE tablename Modify column name varchar (10);

Delete a field

ALTER TABLE tablename drop Clumn name;

Index

ALTER TABLE tablename Add index index_name (field);

ALTER TABLE tablename DROP INDEX index_name;

CREATE INDEX index_name on tablename (field);

Drop index index_name on TableName;

Primary key

ALTER TABLE TableName Add primary key (' ID ') auto_increment;

1.alter table tablename Modify column ' ID ' int

2.alter table tablename Drop primary KEY (' ID ');

Lock Library Backup

/usr/local/mysql/bin/mysqldump-uroot-p ' vxxx '-h127.0.0.1-p3306--databases library name--lock-all-tables--master-data > Axx_20130607.sql remove--lock-all-tables without locking--master-data

Multiple error chaining, MYSQLD interrupt host all requests, resume request status

Mysqladmin flush-hosts

Viewing user resource limits

Select Host,user,password,max_questions,max_updates,max_connections,max_user_connections from Mysql.user;

Convert MyISAM engine to InnoDB

ALTER TABLE Your_table_name engine= ' Innodb ';

See if there is a table structure using the MyISAM engine

SELECT * from Information_schema.tables where table_schema isn't in (' MySQL ', ' information_schema ', ' Performance_schema ') and engine! = ' InnoDB ';

Export table Structure

mysqldump-u***-p-d your_database_name > Database_structure.sql

View User Permissions

Show grants for [email protected] ' 127.0.0.1 ';

Modify permissions for a user

UPDATE mysql.user SET Grant_priv=‘Y‘, Super_priv=‘Y‘ WHERE User=‘root‘; 

FLUSH PRIVILEGES;

Mcluster limit Check SQL

SELECT DISTINCT       CONCAT(t.table_schema,‘.‘,t.table_name) astbl,       t.engine,       IF(ISNULL(c.constraint_name),‘NOPK‘,‘‘) ASnopk,       IF(s.index_type = ‘FULLTEXT‘,‘FULLTEXT‘,‘‘) asftidx,       IF(s.index_type = ‘SPATIAL‘,‘SPATIAL‘,‘‘) asgisidx  FROMinformation_schema.tables ASt  LEFTJOINinformation_schema.key_column_usage AS c    ON(t.table_schema = c.constraint_schema ANDt.table_name = c.table_name        ANDc.constraint_name = ‘PRIMARY‘)  LEFTJOINinformation_schema.statisticsASs    ON(t.table_schema = s.table_schema ANDt.table_name = s.table_name        ANDs.index_type IN(‘FULLTEXT‘,‘SPATIAL‘))  WHEREt.table_schema NOTIN(‘information_schema‘,‘performance_schema‘,‘mysql‘)    ANDt.table_type = ‘BASE TABLE‘    AND(t.engine <> ‘InnoDB‘ORc.constraint_name ISNULLORs.index_type IN(‘FULLTEXT‘,‘SPATIAL‘))  ORDERBYt.table_schema,t.table_name;

MySQL feature statistics for source database

select* frommysql.func;select* frominformation_schema.ROUTINES where ROUTINE_SCHEMA = ‘win4‘;select* frommysql.event;select* frominformation_schema.EVENTS whereEVENT_SCHEMA = ‘win4‘;select * frommysql.proc;select* frominformation_schema.VIEWS whereTABLE_SCHEMA = ‘win4‘;select* frominformation_schema.TRIGGERS whereTRIGGER_SCHEMA = ‘win4‘;select* frominformation_schema.tables wheretable_schema= ‘win4‘andengine != ‘InnoDB‘;select* frominformation_schema.STATISTICSwheretable_schema = ‘win4‘andINDEX_TYPE in(‘FULLTEXT‘,‘SPATIAL‘);select t.table_schema,t.TABLE_NAME,c.CONSTRAINT_TYPE frominformation_schema.tables t leftjoininformation_schema.TABLE_CONSTRAINTS c ont.TABLE_SCHEMA = c.TABLE_SCHEMA andt.TABLE_NAME = c.TABLE_NAME andc.CONSTRAINT_TYPE = ‘PRIMARY KEY‘ wheret.TABLE_SCHEMA = ‘win4‘andc.CONSTRAINT_TYPE isnull;

MySQL Common Operations Command collection