mysql用户管理、常用sql语句、mysql数据库备份恢复

MySQL用户管理

创建一个普通用户并授权

mysql> grant all on *.* to 'ahao'@'127.0.0.1' identified by 'passwd';  
Query OK, 0 rows affected (0.00 sec)

用法解释说明：

  ● grant：授权；

  ● all:表示所有的权限（如读、写、查询、删除等操作）；

  ● *.*：前者表示所有的数据库,后者表示所有的表；

  ● identified by：后面跟密码，用单引号括起来；

  ● 'user1'@'127.0.0.1':指定IP才允许这个用户登录，这个IP可以使用%代替，表示允许所有主机使用这个用户登录;

  ● 其中主机ip可以用%替代，他表示所有ip，则localhost为本机

测试登入

[root@ahao-01 ~]# mysql -uahao -ppasswd -h127.0.0.1  //由于指定IP，所以需要加-h指定IP登录，正常
Warning: Using a password on the command line interface can be insecure.  
Welcome to the MySQL monitor.  Commands end with ; or \g.  
Your MySQL connection id is 10  Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.  

Oracle is a registered trademark of Oracle Corporation and/or its  
affiliates. Other names may be trademarks of their respective  
owners.  
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  
mysql> //授权localhost，所以该用户默认使用（监听）本地mysql.socket文件，不需要指定IP即可登录  
mysql> grant all on *.* to 'ahao'@'localhost' identified by 'passwd';  
Query OK, 0 rows affected (0.00 sec)

3.查看所有授权

mysql> show grants;  
+----------------------------------------------------------------------------------------------------------------------------------------+  
| Grants for root@localhost                                                                                                              |  
+----------------------------------------------------------------------------------------------------------------------------------------+  
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |  
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |  
+----------------------------------------------------------------------------------------------------------------------------------------+  
2 rows in set (0.00 sec)

4.指定用户查看授权

mysql>  show grants for ahao@127.0.0.1;  
+----------------------------------------------------------------------------------------------------------------------+  
| Grants for ahao@127.0.0.1                                                                                            |  
+----------------------------------------------------------------------------------------------------------------------+  
| GRANT ALL PRIVILEGES ON *.* TO 'ahao'@'127.0.0.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' |  
+----------------------------------------------------------------------------------------------------------------------+  
1 row in set (0.00 sec)

注意：假设你想给同个用户授权增加一台电脑IP授权访问，你就可以直接拷贝查询用户授权文件，复制先执行一条命令再执行第二条，执行的时候把IP更改掉,这样就可以使用同个用户密码在另外一台电脑上登录。

grant all on db1.* to 'user3'@'%' identified by 'passwd';

常用sql语句

1、查询mysql库中的user表，count（*）表示表中共有几行

mysql> select count(*) from mysql.user;  
+----------+  
| count(*) |  
+----------+  
|        9 |  
+----------+  
1 row in set (0.01 sec)

2、查询mysql数据哭的db表中所有的数据

mysql>  select * from mysql.db;  
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+  
| Host | Db      | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |  
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+  
| %    | test    |      | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | N                  | N            | Y          | Y            |  
| %    | test\_% |      | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | N                  | N            | Y          | Y            |  
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+  
2 rows in set (0.00 sec)

3、查询单个字段的数据

mysql> select db from mysql.db;  
+---------+  
| db      |  
+---------+  
| test    |  
| test\_% |  
+---------+  
2 rows in set (0.00 sec)

4、查询多个字段的数据

mysql> select db,user from mysql.db;  
+---------+------+  
| db      | user |  
+---------+------+  
| test    |      |  
| test\_% |      |  
+---------+------+  
2 rows in set (0.00 sec)

5、查询语句中也可以使用万能匹配符%如下图

mysql> select * from mysql.db where host like '192.168.%';

二、插入数据

mysql> insert into db1.t1 values (1, 'abc');  //插入一行数据  
Query OK, 1 row affected (0.01 sec)
//其中abc为字符串，必须要加引号，除了插入数字之外可以不加  
mysql>  select * from db1.t1;  //查询表  
+------+------+  
| id   | name |  
+------+------+  
|    1 | abc  |  
+------+------+  
1 row in set (0.00 sec)

2.1、更改表的一行。

mysql> update db1.t1 set name='aaa' where id=1; 
 Query OK, 1 row affected (0.00 sec)  Rows matched: 1  Changed: 1  Warnings: 0  
 mysql>  select * from db1.t1;  
 +------+------+  
 | id   | name |  
 +------+------+  
 |    1 | aaa  |  
 +------+------+  
 1 row in set (0.00 sec)

2.2、清空某个表的数据

注意：truncate只是清空表里面的数据，而drop是整个表都删除包括字段  
mysql>  truncate table db1.t1;  //清空表  
Query OK, 0 rows affected (0.05 sec)  mysql>  select * from db1.t1;  Empty set (0.00 sec)

2.3、删除表

mysql> drop table db1.t1;  
Query OK, 0 rows affected (0.00 sec)  
mysql> select * from db1.t1;  
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

2.4、删除数据库

mysql> drop database db1;  
Query OK, 2 rows affected (0.02 sec)

MySQL数据库备份恢复

3.1、备份还原数据库

[root@ahao-01 ~]# mysqldump -uroot -p密码  mysql > /tmp/mysql.sql  //备份库

[root@ahao-01 ~]#  mysql -uroot -p密码 mysql2 < /tmp/mysql.sql  //恢复一个库

3.2、创建库

[root@ahao-01 ~]#  mysql -uroot -p密码 -e "create database mysql2"   //创建一个库

3.3、备份还原表

[root@ahao-01 ~]# mysqldump -uroot -p密码 mysql user > /tmp/user.sql   //备份某张表  
[root@ahao-01 ~]# mysql -uroot -p密码 mysql < /tmp/user.sql   //恢复某张表

3.4、备份所有库

[root@ahao-01 ~]# mysqldump -uroot -p -A >/tmp/quanbu.sql   //备份所有库

3.5、备份表结构不要数据

[root@ahao-01 ~]#  mysqldump -uroot -p密码 -d mysql > /tmp/mysql.sql