文档章节

MySQL 创建用户以及授权,常用的语句,利用mysqldump备份与恢复

野雪球
 野雪球
发布于 12/07 08:05
字数 2008
阅读 8
收藏 2

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.

© 著作权归作者所有

共有 人打赏支持
野雪球
粉丝 7
博文 56
码字总数 63722
作品 0
贵阳
私信 提问
mysql用户管理、常用语句、数据分备份恢复

mysql用户管理 创建用户并授权 指定登录ip 使用root用户登录录创建授权新用户: mysql> grant all on . to 'user1'@'127.0.0.1' identified by '123456'; // all 所有操作(增删查改) // 第...

豆渣锅
06/26
0
0
2018-3-23 13周5次课 MySQL常用操作(下)

13.4 mysql用户管理 ·创建用户:grant all on . to 'user1' identified by 'passwd'; 或指定来源ip:grant all on . to 'user1'@'ip' identified by 'passwd'; mysql> grant all on . to '......

alexis7gunner
03/22
0
0
MySQL用户管理、常用sql语句、备份和恢复数据

MySQL用户管理 创建一个普通用户user1并授权 grant all on . to 'user1'@'localhost' identified by '123456' all 表示所有的权限,也可以指定某一项权限,如SELECT 、UPDATE、INSERT等 “ . ...

黄昏残影
08/23
0
0
53.mysql用户管理 语句 备份与恢复

13.4 mysql用户管理 13.5 常用sql语句 13.6 mysql数据库备份恢复 扩展 SQL语句教程 http://www.runoob.com/sql/sql-tutorial.html 什么是事务?事务的特性有哪些? http://blog.csdn.net/yen...

王鑫linux
08/24
0
0
MYSQL常用操作(二),MYSQL用户管理,数据备份恢复

MYSQL用户管理 MySQL创建用户以及授权 常用SQL语句 数据库备份和恢复 远程备份,从A库备份到B库mysqldump -h 远程mysql-ip -uuser -ppassword dbname > /本地backup.sql...

bs_xyz
01/16
0
0

没有更多内容

加载失败,请刷新页面

加载更多

领哥,项目管理

领哥 https://www.leangoo.com/kanban/board_list

miaojiangmin
12分钟前
1
0
2018阿里云双12年终大促主会场全攻略

摘要: 双12官方攻略出炉! 2018阿里云双12年终大促活动已经于12月7日正式开启,从已开放的活动页面来看,活动分为两个阶段: 12月7日-12月23日的拉新返现阶段和12月24日-12月28日的TOP100英...

阿里云云栖社区
12分钟前
1
0
努力使失败保持原子性(64)

失败的原子调用应该使得对象保持在被调用之前的状态,所谓:失败原子性 几种途径实现: 设计一个不可变对象,其失败原子性是显然的 对于可变参数,执行前检查参数有效性 避免执行一半报错,后...

Java搬砖工程师
13分钟前
1
0
slot分发内容

slot元素作为组件模板之中的内容分发插槽。这个元素自身将被替换。 有 name 特性的 slot 称为具名 slot。 有 slot 特性的内容将分发到名字相匹配的具名 slot。 内容分发就是指混合父组件的内...

Carbenson
25分钟前
1
0
python开发入门

1.执行python文件 # python ./demo.py 2.Python ImportError: No module named 'requests'异常 解决方法: # pip install requests;...

硅谷课堂
26分钟前
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部