mysql常用操作

为gaingreat添加test本地用户权限,密码123456

# grant all privileges on gaingreat.* to test@localhost identified by '123456';

 
查看用户权限:

> show grants for username@localhost;
或
> select * from mysql.user where user='username';

 
收回权限

> revoke all on gaingreat.* from test@’192.168.1.212’  #假设ip为192.168.1.212

 
修改用户密码,有3种方法

# /usr/local/mysql/bin/mysqladmin -uroot -p password '123456'
Enter password: 旧密码
或
> use mysql;
> SET PASSWORD FOR root=PASSWORD(’new password’);
或
> use mysql;
> UPDATE user SET password=PASSWORD(”new password”) WHERE user=’root’;

 
查看创建数据库时用到的参数

> show create database dbname;

 
查看创建表时用到的参数

> show create table tickets;

 
表改名

> RENAME TABLE USER TO user;

 
mysqldump导出数据库

# /usr/local/mysql/bin/mysqldump -uroot -p 数据库名 > /tmp/mysql_20121204.sql //导出整个数据库,包括数据,结构
# /usr/local/mysql/bin/mysqldump -uroot -p -d 数据库名 > /tmp/mysql_20121204.sql //只导出数据库结构,-d只导出结构
# /usr/local/mysql/bin/mysqldump -uroot -p 数据库名 表名 > /tmp/mysql_test_20121204.sql //导出一个表,包括表结构和数据
# /usr/local/mysql/bin/mysqldump -uroot -p -d 数据库名 表名 > /tmp/mysql_test_20121204.sql //只导出一个表结构,-d只导出结构
# /usr/local/mysql/bin/mysqldump -uroot -p --all-databases > /tmp/mysql_all_20140813.sql  //导出所有数据库
# /usr/local/mysql/bin/mysqldump -uroot -p -d --all-databases > /tmp/mysql_all_20140813.sql  //导出所有数据库,-d只导出结构
# /usr/local/mysql/bin/mysqldump -uroot -p --databases db1 db2 > db1db2.sql  //同时导出db1,db2数据库
# /usr/local/mysql/bin/mysqldump -uroot -p db1 table1 table2 > tb1tb2.sql  //同时导出db1的table1,table2
# /usr/local/mysql/bin/mysqldump -uroot -p -t --all-databases > /tmp/all.sql  //导出所有数据库数据,-t只导出数据,不导出结构
# /usr/local/mysql/bin/mysqldump -uroot -p -t extmail mailbox --where="domain = 'coolnull.com'" > /home/coolnull/mailbox.20141020.sql  //导出extmail库、mailx表中domain为coolnull.com的数据,不导出结构

 
source还原数据库

# /usr/local/mysql/bin/mysql -uroot -p < all.sql  //导入所有数据库
# /usr/local/mysql/bin/mysql -uroot -p db1 < db1.sql;  //导入db1数据库 
或
# /usr/local/mysql/bin/mysql -uroot -p
> create database db1
> use db1
> source /tmp/mysql_20121204.sql

 
查看数据库所有的表引擎

# /usr/local/mysql/bin/mysql -u test -p
> use db1
> show table status;
+--------------------------------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+--------------------------------------------------------------+
| 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                                                      |
+--------------------------------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+--------------------------------------------------------------+
| cdef                           | MyISAM |      10 | Dynamic    |   15 |             67 |        1016 |   281474976710655 |         2048 |         0 |             25 | 2013-06-24 19:44:05 | 2013-06-25 15:31:08 | NULL                | latin1_swedish_ci |     NULL |                |                                                              |
| cdef_items                     | MyISAM |      10 | Dynamic    |   37 |             59 |        2192 |   281474976710655 |         3072 |         0 |             45 | 2013-06-24 19:44:05 | 2013-06-25 15:31:08 | NULL                | latin1_swedish_ci |     NULL |                |

 

清除root密码,实现mysql -S /tmp/mysql.sock,直接socket登录本机mysql

> use mysql;
> select Host,User,Password from user where User='root';
> update user set password=password("") where user='root' and Host='localhost';   //修改localhost的root密码为空
> select Host,User,Password from user where User='root';
> flush privileges;  //或/etc/init.d/mysql restart重启
# mysql -S /tmp/mysql.sock  //这样就可以直接mysql.sock登录本机mysql

 
查看当前使用数据库

mysql> select database();
+------------+
| database() |
+------------+
| percona    |
+------------+
1 row in set (0.00 sec)
或者status;

发表评论

邮箱地址不会被公开。 必填项已用*标注