【转】常用mysql操作命令

   

Administration 管理

Kill a Thread  结束一个线程

mysql > KILL 999;

 

Optimize Table 优化表

mysql > OPTIMEZE TABLE foo;

 

Reload Users Permissions 刷新MySQL系统权限相关表  

mysql > FLUSH PRIVILEGES;

 

Repair Table 修复表

mysql > REPAIR TABLE foo;

 

Reset the Query Cache 清除查询缓存区中的所有的内容

mysql > RESET QUERY CACHE;

 

See Which Threads are Running 显示哪些线程正在运行

mysql > SHOW FULL PROCESSLIST;

 

 

Backup & Restore

Backup Database to a SQL File 备份数据库

#mysqldump --user admin -- password=password mydatabase > database.sql

 

Restore Database from SQL File 还原数据库

#mysql < database.sql

 

Data Definition 数据库描述

Add a Column to a Table

mysql > ALTER TABLE table1 ADD COLUMN newcol TEXT;

 

Browse Database

mysql > USE dbname;

mysql > SHOW TABLES;

 

Create Database

mysql > CREATE DATABASE dbname;

 

Create Table

mysql > CREATE TABLE table (field1 type1, field2 type2, ...)

 

Drop Database

mysql > DROP DATABASE dbanme;

 

Remove a Column from a table

mysql > ALTER TABLE table1 DROP COLUMN oldcol;

 

Rename a Table

mysql > ALTER TABLE table1 RENAME TO newtable2;

 

Data Manipulation 数据操作

Delete Row from Table

mysql > DELETE FROM table1 WHERE id = 100;

 

Insert Row to Table

mysql > INSERT INTO table1(field1, field2, ...) VALUES(value1,value2,...);

 

Update Row in Table

mysql > UPDATE table1 SET field1 = new_value1 WHERE id = 100;

 

Emergency Maintenance 常规操作

Find the error log

#tail -f /var/lib/mysqlserver.domain.co.il.err

 

Recover root Password

Stop MySQL service

#/etc/init.d/mysql stop

 

Start to MySQL server w/o password

#mysqld_safe --skip-grant-tables &

 

Connect to mysql

#mysql -u root

 

Setup new MySQL root user password

mysql > use mysql;

mysql > update user set password =PASSWORD("NEW-ROOT-PASSWORD") where User = 'root';

mysql > flush privileges;

mysql > quit

 

Stop MySQL Server

#/etc/init.d/mysql stop

 

Start MySQL Server

#/etc/init.d/mysql start

 

 

Repair Tables After Unclean Shutdown

#mysqlcheck --all-databases

 

 

Security 安全

Disable Remote Access

#/etc/my.cnf

[mysqld]

skip-networking

Restart MySQL

 

Prevent Local File Reading

[mysqld]

set-variable=local-infile=0

Restart MySQL

 

Remove Anonymous Accounts

#/usr/bin/mysql -u root

mysql > DROP USER '';

 

Remove Default Users/Databases

#/usr/bin/mysql -u root

mysql > DROP DATABASE test;

mysql > DELETE FROM user WHERE user LIKE 'root' AND host NOT LIKE 'localhost';

mysql > flush privileges;

 

Set The Root Password

#/usr/bin/mysql -u root;

mysql > SET PASSWORD FOR root@localhost = PASSWORD('new_password');

 

 

Starting & Stopping 启/停

Find MySQL Server Status

#mysqladmin -p status

 

Restart MySQL

#/sbin/service mysqld start

/sbin/service mysqld stop

/sbin/service mysqld restart

 

 

User Accounts 账户操作

Add a User

mysql > GREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';

mysql > GREATE ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION;

 

Change a User Password

mysql > SET PASSWORD FOR 'john'@'localhost' = PASSWORD('passwordhere');

 

Delete a User

mysql > DROP USER username;

 

Limit User Resources

mysql > GRANT ALL ON customer.* TO 'francis'@'localhost' WITH

MAX_QUERIES_PER_HOUR 20

MAX_UPDATES_PER_HOUR 10

MAX_CONNECTIONS_PER_HOUR 5

MAX_USER_CONNECTIONS 2;