MySQL常用命令

[b]创建一个User[/b]
[quote]# mysql -uroot -p
mysql> create user USERNAME;
mysql> grant all on *.* to USERNAME@localhost identified by 'PASSWORD';
mysql> set password for USERNAME@localhost=password('PASSWORD');
mysql> flush privileges;[/quote]
[b]连接数据库[/b]
[quote]# mysql -uUSERNAME -pPASSWORD
# mysql -uUSERNAME -pPASSWORD databasename[/quote]
[b]列举数据库[/b]
[quote]mysql> show databases;[/quote]
[b]切换数据库[/b]
[quote]mysql> use databasename;[/quote]
[b]列举表[/b]
[quote]mysql> show tables;[/quote]
[b]执行SQL文件[/b]
[quote]mysql> source test.sql[/quote]
[b]查看执行时间[/b]
[quote]mysql> set profiling=1;
mysql> select * from tablename;
mysql> show profiles;
mysql> show profile;[/quote]
[b]查看表结构[/b]
[quote]mysql> desc tablename;
mysql> describe tablename;[/quote]
[b]备份数据库[/b]
[quote]# mysqldump -uUSERNAME -pPASSWORD --databases databasename > /tmp/databasename.sql
# mysqldump -uUSERNAME -pPASSWORD databasename > /tmp/databasename.sql #dump中不会CREATE DATABASE[/quote]
[b]备份数据库的一张表[/b]
[quote]# mysqldump -c -uUSERNAME -pPASSWORD databasename tablename > /tmp/databasename.tablename.sql
# mysqldump -uUSERNAME -pPASSWORD databasename tablename > /tmp/databasename.tablename.sql #INSERT文不带列名[/quote]
[b]恢复数据库[/b]
[quote]# mysql -uUSERNAME -pPASSWORD databasename < /tmp/databasename.sql[/quote]