MySQL创建用户以及授权,授权信息查看
grant语句因为是不安全的,所以在mysql的历史信息中看不见,及通过上下键翻不会翻到该语句。
[root@test-a ~]# mysql -uroot -p'test111' # root用户登录
mysql: [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 2
Server version: 5.7.23 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
| test_db0 |
+--------------------+
6 rows in set (0.10 sec)
mysql> use test_db0;
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> grant all on *.* to 'mysql_user1' identified by 'test111'; -- 授权并创建用户
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@test-a ~]# mysql -umysql_user1 -p'test111' -- mysql_user1登录
mysql: [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 3
Server version: 5.7.23 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> CREATE TABLE `test_tb1` ( `id` int(4) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 创建一个新表test_tb1
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye
[root@test-a ~]# mysql -uroot -p'test111' # root登录
mysql: [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 4
Server version: 5.7.23 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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 test_db0.test_tb1 to 'mysql_user2' identified by 'test111'; -- 创建mysql_user2用户并授权test_db0数据库的test_tb1表所有权限给test_tb1
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye
[root@test-a ~]# mysql -umysql_user2 -p'test111' # 登录
mysql: [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 5
Server version: 5.7.23 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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 test_db0; # 切换到test_db0库
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; -- 查看所有的数据表,没有test_db0
+--------------------+
| Tables_in_test_db0 |
+--------------------+
| test_tb1 |
+--------------------+
1 row in set (0.00 sec)
[root@test-a ~]# mysql -umysql_user1 -p'test111' # mysql_user2登录
mysql: [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 6
Server version: 5.7.23 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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 test_db0; -- 同样切换到test_db0库
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; -- 可以查看到test_tb0和test_tb1表
+--------------------+
| Tables_in_test_db0 |
+--------------------+
| test_tb0 |
| test_tb1 |
+--------------------+
2 rows in set (0.00 sec)
mysql> show grants; -- 查看当前登录用户的所有授权
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for mysql_user2; -- 查看mysql_user2的授权
+--------------------------------------------------------------------+
| Grants for mysql_user2@% |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mysql_user2'@'%' |
| GRANT ALL PRIVILEGES ON `test_db0`.`test_tb1` TO 'mysql_user2'@'%' |
+--------------------------------------------------------------------+
mysql> grant SELECT,UPDATE,INSERT on test_db0.* to 'mysql_user4'@'192.168.77.134' identified by 'test111'; -- 给从192.168.77.134登录的mysql_user4用户授权
mysql> show grants for mysql_user4; -- 这时会提示需要指定host
ERROR 1141 (42000): There is no such grant defined for user 'mysql_user4' on host '%'
mysql> show grants for mysql_user4@'192.168.77.134';
+--------------------------------------------------------------------------------+
| Grants for mysql_user4@192.168.77.134 |
+--------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mysql_user4'@'192.168.77.134' |
| GRANT SELECT, INSERT, UPDATE ON `test_db0`.* TO 'mysql_user4'@'192.168.77.134' |
+--------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
常用SQL语句
mysql> select count(*) from mysql.user; -- 查看用户数
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.30 sec)
mysql> select * from mysql.db\G -- 查看表数据
mysql> select db from mysql.db; -- 从db中读取所有db字段
+--------------------+
| db |
+--------------------+
| test_db0 |
| performance_schema |
| sys |
+--------------------+
3 rows in set (0.00 sec)
mysql> select db,user from mysql.db; -- 多个字段查询
+--------------------+---------------+
| db | user |
+--------------------+---------------+
| test_db0 | mysql_user4 |
| performance_schema | mysql.session |
| sys | mysql.sys |
+--------------------+---------------+
3 rows in set (0.03 sec)
mysql> select db,user,host from mysql.db where host like '192.168.%'; -- 模糊匹配查询
+----------+-------------+----------------+
| db | user | host |
+----------+-------------+----------------+
| test_db0 | mysql_user4 | 192.168.77.134 |
+----------+-------------+----------------+
1 row in set (0.03 sec)
mysql> desc test_tb0; -- 查看表字段
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
1 row in set (0.07 sec)
mysql> insert into test_tb0 values (1); -- 插入数据
Query OK, 1 row affected (0.09 sec)
mysql> select * from test_tb0;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> update test_tb0 set id=2; -- 把id的所有值改为2
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test_tb0;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
mysql> delete from test_tb0 where id=2; -- 删除数据
Query OK, 1 row affected (0.05 sec)
mysql> select * from test_tb0;
Empty set (0.00 sec)
mysql> insert into test_tb0 values (1);
Query OK, 1 row affected (0.00 sec)
mysql> truncate table test_tb0; -- 清空库
Query OK, 0 rows affected (0.34 sec)
mysql> desc test_tb0;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> select * from test_tb0;
Empty set (0.00 sec)
mysql> drop table test_tb0; -- 删除表
Query OK, 0 rows affected (0.09 sec)
mysql> desc test_tb0;
ERROR 1146 (42S02): Table 'test_db0.test_tb0' doesn't exist
备份与恢复
[root@test-a ~]# mysqldump -uroot -p'test111' mysql > /tmp/mysql.sql # 备份mysql库
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@test-a ~]# mysql -uroot -p'test111' -e 'create database mysql2' # 创建mysql2库
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@test-a ~]# mysql -uroot -p'test111' mysql2 < /tmp/mysql.sql # 恢复备份到mysql2库
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@test-a ~]#
[root@test-a ~]# mysql -uroot -p'test111'
mysql: [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 14
Server version: 5.7.23 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| mysql2 |
| performance_schema |
| sys |
| test_db0 |
+--------------------+
7 rows in set (0.00 sec)
mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from mysql2.user;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.03 sec)
[root@test-a ~]# mysqldump -uroot -p'test111' mysql2 user > /tmp/mysql2.user.sql # 备份user表
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@test-a ~]# mysql -uroot -p'test111' -e '
> ^C
[root@test-a ~]# mysql -uroot -p'test111' mysql2
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.23 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> drop table user; --删除user表
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------------------+
| Tables_in_mysql2 |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
+---------------------------+
30 rows in set (0.00 sec)
mysql> quit
Bye
[root@test-a ~]# mysql -uroot -p'test111' mysql2 < /tmp/mysql2.user.sql #恢复表
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@test-a ~]# mysql -uroot -p'test111' mysql2
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.7.23 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> show tables;
+---------------------------+
| Tables_in_mysql2 |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
[root@test-a ~]# mysqldump -uroot -p'test111' -A > /tmp/all.sql # 备份所有库
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@test-a ~]# grep 'Current Database:' /tmp/all.sql
-- Current Database: `db1`
-- Current Database: `mysql`
-- Current Database: `mysql2`
-- Current Database: `test_db0`
[root@test-a ~]# mysqldump -uroot -p'test111' -d mysql2 > /tmp/mysql2.struct.sql # -d 只备份表的结构
mysqldump: [Warning] Using a password on the command line interface can be insecure.