一、创建普通用户并授权
1、创建用户并授权
[root@zlinux ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> grant all on *.* to user01 identified by '123456'
-> ; //上一行命令忘记输入‘；’，这里可以输入后继续执行
Query OK, 0 rows affected (0.01 sec)
创建user用户并授予其所有权限
*.*（第一个*表示所有数据库，第二个*表示所有表） 这里的user01特指localhost上的user01identified by：设定密码，用单引号括起来。
2、给网络上其他机器某个用户授权
mysql> grant all on *.* to 'user02'@'127.0.0.1' identified by '123456'; //指定IP，即只可通过此IP登录,用户和主机之间有个@。可以使用通配符%，代表所有IP（一般不使用）
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@zlinux ~]# mysql -uuser02 -p123456 //未连接到指定IP，登录报错
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user02'@'localhost' (using password: YES)
[root@zlinux ~]# mysql -uuser02 -p123456 -h127.0.0.1 //指定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 9
Server version: 5.6.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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>
3、查看用户授权
mysql> show grants //查看当前用户授权
-> ;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*A89494294B2411291D21451D05BAB332A65AAB5D' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for 'user02'@'127.0.0.1'; //查看指定用户授权
+------------------------------------------------------------------------------------------------------------------------+
| Grants for user02@127.0.0.1 |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user02'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4、授权部分权限(如读、写、查询、插入等)
mysql> grant SELECT, INSERT, UPDATE *.* user03 identified by '123456';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*.* user03 identified by '123456'' at line 1
mysql> grant SELECT, INSERT, UPDATE on *.* to user03 identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for user03
-> ;
+------------------------------------------------------------------------------------------------------------------------+
| Grants for user03@% |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE ON *.* TO 'user03'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
二、常用SQL语句
select count(*) from mysql.user; //查询mysql库中user表的行数
select * from mysql.db; //查询mysql库中db表的所有内容
select db from mysql.db; //查询mysql库中db表的db字段
select db,user from mysql.db; 查询mysql库中db表的db和user字段
select * from mysql.db where host like '192.168.%'; //查询mysql库中db表中匹配192.128.开头的内容
insert into db1.t1 values (1, 'abc'); //在db1数据库的t1表中插入对应 内容
update db1.t1 set name='aaa' where id=1; //更改db1数据库的t1表中name列内容，当id是1的时候
truncate table db1.t1; //清除表内数据
drop table db1.t1; //删除表
drop database db1; //删除数据库
三、MySQL数据库备份与恢复
mysql> create database db1; //创建库
Query OK, 1 row affected (0.01 sec)
mysql> use db1; //切换库
Database changed
mysql> create table t1 (`id` int(4),`name` char(40)); //创建表及字段
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
mysql> select * from t1;
Empty set (0.00 sec)
mysql> quit
Bye
[root@zlinux ~]# mysqldump -uroot -pzlinux123456 mysql >/tmp/mysql.sql //备份
Warning: Using a password on the command line interface can be insecure.
[root@zlinux ~]# ls /tmp/mysql.sql
/tmp/mysql.sql
[root@zlinux ~]# mysql -uroot -pzlinux123456 db2 < /tmp/mysql.sql //恢复
Warning: Using a password on the command line interface can be insecure.
[root@zlinux ~]# mysql -uroot -pzlinux123456
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 29
Server version: 5.6.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> use db2 //切换到db2，查看是否恢复成功
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> show tables;
+---------------------------+
| Tables_in_db2 |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| t1 |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
29 rows in set (0.01 sec)
mysql> quit
Bye
[root@zlinux ~]# mysqldump -uroot -pzlinux123456 mysql user > /tmp/mysqluser.sql //备份mysql库user表
Warning: Using a password on the command line interface can be insecure.
[root@zlinux ~]# mysql -uroot -pzlinux123456 db1 < /tmp/mysqluser.sql //恢复到db1
Warning: Using a password on the command line interface can be insecure.
[root@zlinux ~]# mysql -uroot -pzlinux123456
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 32
Server version: 5.6.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, 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> use db1 //查看是否成功恢复
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> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
| user |
+---------------+
2 rows in set (0.00 sec)
mysql>
```
