¡¾×ª¡¿³£ÓÃ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