MySQL用户管理、常用SQL语句、MySQL数据库备份恢复

mysql用户管理

1.创建一个普通用户并授权

[root@gary-tao ~]# mysql -uroot -p'szyino-123'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 24Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by 'szyino-123';  //创建一个普通用户并授权Query OK, 0 rows affected (0.00 sec)

用法解释说明：

• grant：授权；
• all:表示所有的权限（如读、写、查询、删除等操作）；
• .：前者表示所有的数据库,后者表示所有的表；
• identified by：后面跟密码，用单引号括起来；
• 'user1'@'127.0.0.1':指定IP才允许这个用户登录，这个IP可以使用%代替，表示允许所有主机使用这个用户登录;

2.测试登录

[root@gary-tao ~]# mysql -uuser1 -pszyino-123 //由于指定IP，报错不能登录Warning: Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)[root@gary-tao ~]# mysql -uuser1 -pszyino-123 -h227.0.0.1 //加-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 26Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> mysql> grant all on *.* to 'user1'@'localhost' identified by 'szyino-123';  //授权localhost，所以该用户默认使用（监听）本地mysql.socket文件，不需要指定IP即可登录Query OK, 0 rows affected (0.00 sec)mysql> ^DBye[root@gary-tao ~]# mysql -uuser1 -pszyino-123  //正常登录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 28Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> 

3.查看所有授权

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

4.指定用户查看授权

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

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

常用sql语句

1.最常见的查询语句

第一种形式:

mysql> use db1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select count(*) from mysql.user; +----------+| count(*) |+----------+|        8 |+----------+1 row in set (0.00 sec)//注释：mysql.user表示mysql的user表，count(*)表示表中共有多少行。

第二种形式:

mysql> select * from mysql.db;//它表示查询mysql库的db表中的所有数据mysql> select db from mysql.db;+---------+| db      |+---------+| test    || test\_% |+---------+2 rows in set (0.00 sec)//查询db表里的db单个字段mysql> select db,user from mysql.db;+---------+------+| db      | user |+---------+------+| test    |      || test\_% |      |+---------+------+2 rows in set (0.00 sec)//查看db表里的db，user多个字段mysql> select * from mysql.db where host like '192.168.%'\G;//查询db表里关于192.168.段的ip信息

2.插入一行

mysql> desc db1.t1;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id    | int(4)   | YES  |     | NULL    |       || name  | char(40) | YES  |     | NULL    |       |+-------+----------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> select * from db1.t1;  Empty set (0.00 sec)mysql> insert into db1.t1 values (1, 'abc');  //插入一行数据Query OK, 1 row affected (0.01 sec)mysql> select * from db1.t1;+------+------+| id   | name |+------+------+|    1 | abc  |+------+------+1 row in set (0.00 sec)mysql> insert into db1.t1 values (1, '234');Query OK, 1 row affected (0.00 sec)mysql> select * from db1.t1;+------+------+| id   | name |+------+------+|    1 | abc  ||    1 | 234  |+------+------+2 rows in set (0.00 sec)

3.更改表的一行。

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

4.清空某个表的数据

mysql> truncate table db1.t1;  //清空表Query OK, 0 rows affected (0.03 sec)mysql> select * from db1.t1;Empty set (0.00 sec)mysql> desc db1.t1;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id    | int(4)   | YES  |     | NULL    |       || name  | char(40) | YES  |     | NULL    |       |+-------+----------+------+-----+---------+-------+2 rows in set (0.00 sec)

5.删除表

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

6.删除数据库

mysql> drop database db1;Query OK, 0 rows affected (0.00 sec)

mysql数据库备份恢复

1.备份恢复库

[root@gary-tao ~]# mysqldump -uroot -pszyino-123 mysql > /tmp/mysql.sql  //备份库Warning: Using a password on the command line interface can be insecure.[root@gary-tao ~]# mysql -uroot -pszyino-123 -e "create database mysql2"  //创建一个新的库Warning: Using a password on the command line interface can be insecure.[root@gary-tao ~]# mysql -uroot -pszyino-123 mysql2 < /tmp/mysql.sql  //恢复一个库Warning: Using a password on the command line interface can be insecure.[root@gary-tao ~]# mysql -uroot -pszyino-123 mysql2Warning: Using a password on the command line interface can be insecure.Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -AWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 38Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select database();+------------+| database() |+------------+| mysql2     |+------------+1 row in set (0.00 sec)

2.备份恢复表

[root@gary-tao ~]# mysqldump -uroot -pszyino-123 mysql user > /tmp/user.sql  //备份表Warning: Using a password on the command line interface can be insecure.[root@gary-tao ~]# mysql -uroot -pszyino-123 mysql2 < /tmp/user.sql  //恢复表Warning: Using a password on the command line interface can be insecure.

3.备份所有库

[root@gary-tao ~]# mysqldump -uroot -pszyino-123 -A > /tmp/mysql_all.sqlWarning: Using a password on the command line interface can be insecure.[root@gary-tao ~]# less /tmp/mysql_all.sql

4.只备份表结构

[root@gary-tao ~]# mysqldump -uroot -pszyino-123 -d mysql > /tmp/mysql.sqlWarning: Using a password on the command line interface can be insecure.