MYSQL Tips
来自Jack's Lab
目录 |
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 查看数据表属性
mysql> show table status from YOUR_DB_NAME like 'TABLE_NAME'; +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ | datapoints | MyISAM | 10 | Dynamic | 136 | 54 | 7404 | 281474976710655 | 28672 | 0 | 167 | 2015-01-09 00:36:01 | 2015-01-15 12:42:26 | 2015-01-09 00:36:01 | utf8_general_ci | NULL | | | +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ 1 row in set (0.00 sec)
7 修改数据表属性
mysql> alter table YOUR_DB_NAME.TABLE_NAME auto_increment=168; Query OK, 136 rows affected (0.05 sec) Records: 136 Duplicates: 0 Warnings: 0 mysql> show table status from YOUR_DB_NAME like 'TABLE_NAME'; +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ | datapoints | MyISAM | 10 | Dynamic | 136 | 54 | 7404 | 281474976710655 | 28672 | 0 | 168 | 2015-01-15 13:24:44 | 2015-01-15 13:24:44 | 2015-01-15 13:24:44 | utf8_general_ci | NULL | | | +------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ 1 row in set (0.00 sec)
8 数据库备份
$ 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
9 数据库恢复
$ mysql -uUSER_NAME -p USER_PASSWORD YOUR_DB_NAME < /root/YOUR_DB_NAME-db-20110801.sql