MySQL Management note

Related URL:

http://willvvv.iteye.com/blog/1563345

http://lxneng.iteye.com/blog/451985 This article on the use of variables and status write very well!

---------------------------------------------------------Landing-------------------------------------------------------------- ------

1.mysql-h192.168.0.181-utest-ptest;

Log in to MySQL command line

-------------------------------------------------------Database Operations------------------------------------------------------------- --

1.show databases;

View all databases

2.use MyDB;

Using the MyDB Database

3.drop database if exists ' mydb ';

Drop the database mydb if it exists

4.create database ' MyDB ';

Create a MyDB database

----------------------------------------------------User Rights Management---------------------------------------------------------------

1.grant all privileges in mydb.* to [email protected] '%.% ' identified by ' YourPassword ';

Authorized Youruser users can use YourPassword from any host to do anything to the MyDB database

2.revoke all privileges to mydb.* from [email protected] '% ';

Revoke Youruser permissions from any host operation MyDB

3.flush privileges;

Enable Grant and revoke authorization to take effect! Execute grant or revoke after execution!

---------------------------------------------------------data table Operation----------------------------------------------------------- --

1.show tables from MyDB;

View all data tables in the MyDB library

2.desc mydb.mytable;

View all column names, types, empty, default values, indexes, etc. for mytable tables in the MyDB library

3.show CREATE TABLE mydb.mytable;

View the SQL statement for MyTable tables in the MyDB library

4.select * from Mydb.mytable\g;

View all the contents of the MyTable table in the MyDB library, and view it as a key-value pair for each row of data, column name-value.

5.alter table mydb.mytable Rename mydb.mytablenew;

Table MyTable renamed to Mytablenew

6.alter table mydb.mytable Add column newColumn varchar (+) DEFAULT NULL COMMENT ' new column remarks ';

The MyTable table in the MyDB Library has a new column named Newcolumn, with a type of varchar (64) and a default of NULL, and the comment is "new column Memo"

7.alter table mydb.mytable Modify column ' modifycolumn ' varchar (+) DEFAULT NULL COMMENT ' modify column notes ';

Modify the column modifycolumn in the MyTable table in the MyDB library, type varchar (16), default to NULL, and comment "Modify column Notes"

8.truncate Table Mydb.mytable

Emptying the data of the MyDB Library mytable table is much faster than delete

----------------------------------------------------------Data Backup Recovery---------------------------------------------------------

1.mysqldump--opt--user=yours--password=yours-b "mydb1 mydb2"--max_allowed_packet=1048576--net_buffer_length= 16384 >/data/backup/20120630.sql

Export the database MYDB1 and MYDB2 as SQL statements to the/data/backup/20120630.sql file

2.source/data/backup/mydb.sql

Execute the SQL statement in the file

-----------------------------------------------------MySQL status------------------------------------------------------------- --

1.show processlist;

View current MySQL Connection

2.kill ID;

Disconnect a client connection, ID is the value of the show processlist command output first column

3.show variables;

View MySQL various variable values

4.show Global status;

Check out the various states of MySQL, the principle of cacti monitoring MySQL is to periodically collect these state value plots

http://lxneng.iteye.com/blog/451985 This article on the use of variables and status write very well!

5.select version (), current_date ();

View current data version and date

6.show engine InnoDB status;

View InnoDB engine Status

7.show table status from test like ' mydb.mytable '

View InnoDB Table Status

MySQL Management note