MYSQL Tips

1 初始化

mysql-server-5.1 deb 包安装时，会提示设置 mysql 的 root 密码

$ mysql -u root -p
Enter password:
mysql> show databases;
 
mysql> create database YOUR_DB_NAME;
Query OK, 1 row affected (0.00 sec)
 
mysql> grant all privileges on YOUR_DB_NAME.* to USER_NAME@localhost identified by ‘YOUR_PASSWORD';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

2 连接服务器

$ mysql -h localhost -u USER_NAME -p
Enter password: 
mysql> 

3 查看数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| YOUR_DB_NAME       |
| mysql              |
+--------------------+

4 查看和改变当前默认使用的数据库

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+

mysql> use YOUR_DB_NAME;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select database();
+------------+
| database() |
+------------+
|YOUR_DB_NAME|
+------------+

delete database:
mysql> drop database YOUR_DB_NAME;

5 查看数据库里的表

mysql> show tables from YOUR_DB_NAME;  (show tables;)
+-----------------------+
| Tables_in_YOUR_DB_NAME|
+-----------------------+
| wp_commentmeta        |
| wp_comments           |
| wp_links              |
| wp_options            |
| wp_postmeta           |
| wp_posts              |
| wp_term_relationships |
| wp_term_taxonomy      |
| wp_terms              |
| wp_usermeta           |
| wp_users              |
+-----------------------+

6 数据库备份

 $ mysqldump -u user_name -p password --opt YOUR_DB_NAME | gzip -9 > /root/YOUR_DB_NAME-db-20110801.sql.gz

#-d 不导出数据只导出结构 --add-drop-table 在每个create语句之前增加一个drop table 
mysqldump -u root -p -d --add-drop-table db_name >/root/eprs_db.sql

# 导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u root -p db_name users> db_users.sql

7 数据库恢复

 $ mysql -uUSER_NAME -p USER_PASSWORD YOUR_DB_NAME < /root/YOUR_DB_NAME-db-20110801.sql