【转】常用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 > upda