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

可莉
• 阅读 574

13.4 MySQL用户管理

创建用户并授权

指定登录IP

[root@cham002 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
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> 
mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by '123456a';
Query OK, 0 rows affected (0.00 sec)
#创建user1用户并授予其所有权限“ *.* ”（通配符）
#第一个*表示db_name(库名)；第二个*表示tb_name
#同时指定其来源IP127.0.0.1（即，只可通过此IP登录）
#此处可以使用通配符%，代表所有IP（一般不使用）
#设定密码：identified by

mysql> quit
Bye

 指定登录IP

[root@cham002 ~]# mysql -uuser1 -p123456a
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
需要指定登录IP


[root@cham002 ~]# mysql -uuser1 -p123456a -h127.0.0.1
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 19
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.

 使用socket登录

[root@cham002 ~]# mysql -uroot -pchampin
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 20
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> grant all on *.* to 'user1'@'localhost' identified by '123456a';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

使用socket登录 

[root@cham002 ~]# mysql -uuser1 -p123456a
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 21
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.

说明： 因为指定登录主机为localhost，所以该用户默认使用（监听）本地mysql.socket文件，不需要指定IP即可登录。

查看用户指定授权 

#查看当前用户的权限
mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*EA348E6706D3952A7AA87EBB997CDF3629E22834' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)



#查看指定用户的权限
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 '*B012E8731FF1DF44F3D8B26837708985278C3CED' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

##创建user2用户，并授予其针对db1库SELECT,UPDATE,INSERT权限
mysql>  grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.133.1' identified by 'passwd';
Query OK, 0 rows affected (0.00 sec)  


mysql> show grants for user2@192.168.133.1;
+------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.133.1                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.133.1' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.133.1'                                               |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

#如果发现Ip输错了，需要更改ip，可直接复制show grants里面的内容，方法如下

mysql> GRANT USAGE ON *.* TO 'user2'@'192.168.230.135' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.230.135';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for user2@'192.168.230.135';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.230.135                                                                                   |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.230.135' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.230.135'                                               |
+--------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

13.5 常用sql语句

[root@cham002 ~]# mysql -uroot -pchampin
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 23
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> 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> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

#查看库的所有内容（不加大G会比较乱）
mysql> select * from mysql.db\G;
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User: 
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
*************************** 2. row ***************************
                 Host: %
                   Db: test\_%
                 User: 
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
*************************** 3. row ***************************
                 Host: 192.168.133.1
                   Db: db1
                 User: user2
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
*************************** 4. row ***************************
                 Host: 192.168.230.135
                   Db: db1
                 User: user2
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
4 rows in set (0.00 sec)

ERROR: 
No query specified

#查看库指定内容
mysql> select db from mysql.db;
+---------+
| db      |
+---------+
| test    |
| test\_% |
| db1     |
| db1     |
+---------+
4 rows in set (0.00 sec)
mysql> select db,user from mysql.db;
+---------+-------+
| db      | user  |
+---------+-------+
| test    |       |
| test\_% |       |
| db1     | user2 |
| db1     | user2 |
+---------+-------+
4 rows in set (0.00 sec)

#查看某些IP对应的库内容，like表示模糊匹配
mysql> select *from mysql.db where host like '192.168.%'\G;
*************************** 1. row ***************************
                 Host: 192.168.133.1
                   Db: db1
                 User: user2
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
*************************** 2. row ***************************
                 Host: 192.168.230.135
                   Db: db1
                 User: user2
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
2 rows in set (0.00 sec)

ERROR: 
No query specified


#在db1库下创建表t1
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');   #在db1库下创建表t1
Query OK, 1 row affected (0.00 sec)

mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
+------+------+
1 row in set (0.00 sec)


#更改表中指定内容
mysql> update db1.t1 set name='aaa' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from db1.t1;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
+------+------+
1 row in set (0.00 sec)

#删除表中指定内容
mysql> delete from db1.t1 where id=1;
Query OK, 1 row affected (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 | 234  |
+------+------+
1 row in set (0.00 sec)

#清空一个表中内容（仅仅是清空表里面的内容，表结构还存在）
mysql> truncate db1.t1;     
Query OK, 0 rows affected (0.07 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)

#删除表，（表和结构都删除）
mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> desc db1.t1;
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
mysql> select * from db1.t1;
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

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

13.6 MySQL数据库备份恢复

备份库以及恢复库

备份指定库  指定mysql库  
[root@cham002 ~]# mysqldump -uroot -pchampin  mysql > /tmp/mysqlbak180116.sql
Warning: Using a password on the command line interface can be insecure.
[root@cham002 ~]# ls /tmp/mysqlbak180116.sql 
/tmp/mysqlbak180116.sql

[root@cham002 ~]# mysql -uroot -pchampin -e "create database mysql2"
Warning: Using a password on the command line interface can be insecure.

[root@cham002 ~]# mysql -uroot -pchampin mysql2 < /tmp/mysqlbak180116.sql 
Warning: Using a password on the command line interface can be insecure.

[root@cham002 ~]# mysql -uroot -pchampin mysql2
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 29
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> select database();
+------------+
| database() |
+------------+
| mysql2     |
+------------+
1 row in set (0.00 sec)

 -A 是指定所有库
[root@cham002 ~]# mysqldump -uroot -pchampin -A > /tmp/mysql_all.sql
Warning: Using a password on the command line interface can be insecure.

 备份表及恢复表

#备份表  库后面空格加上表名字
[root@cham002 ~]# mysqldump -uroot -pchampin mysql user >/tmp/user.sql
Warning: Using a password on the command line interface can be insecure.
[root@cham002 ~]# ls /tmp/user.sql 
/tmp/user.sql
可以用less /tmp/user.sql看一看。不做截图

#恢复表
[root@cham002 ~]# mysql -uroot -pchampin mysql2 < /tmp/user.sql
Warning: Using a password on the command line interface can be insecure.

只备份表结构： 

[root@cham002 ~]# mysqldump -uroot -pchampin -d mysql2 > /tmp/mysql2.sql
Warning: Using a password on the command line interface can be insecure.
mysqlmysql update语句mysql创建数据库selectsql数据库priv前端前端
点赞
收藏
评论区
推荐文章
blmius blmius
•
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时，报错：原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等，这样可以更容易地在不同的环境中使用MySQL。全局s
4069
0
0
helloworld_34035044 helloworld_34035044
•
2年前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时，如何在新增行里自动增加id呢？能新增整数排序id吗？目前可以在新增行里自动增加id，但只能用uuid函数增加UUID编码，不能新增整数排序id。uuid函数说明：获取一个UUID，可以在填报表中用来创建数据ID语法：uuid()或uuid(sep)参数说明：sep布尔值，生成的uuid中是否包含分隔符''，缺省为
1112
0
0
待兔 待兔
•
3个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
1044
0
0
Jacquelyn38 Jacquelyn38
•
3年前
2020年前端实用代码段，为你的工作保驾护航
有空的时候，自己总结了几个代码段，在开发中也经常使用，谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
2361
0
0
Stella981 Stella981
•
3年前
Python3：sqlalchemy对mysql数据库操作，非sql语句
Python3：sqlalchemy对mysql数据库操作，非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
1126
0
0
Stella981 Stella981
•
3年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签，将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
1324
0
0
Wesley13 Wesley13
•
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败，请联系管理员！');中国在东8区所以加8方法二：selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
1286
0
0
Wesley13 Wesley13
•
3年前
00：Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork)，斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟，已经成为Web应用程序的首选开发语言。Java是简单易学，完全面向对象，安全可靠，与平台无关的编程语言。
1231
0
0
Wesley13 Wesley13
•
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
1486
0
0
京东云开发者 京东云开发者
•
1年前
为什么mysql不推荐使用雪花ID作为主键
作者：毛辰飞背景在mysql中设计表的时候，mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一)，而是推荐连续自增的主键id，官方的推荐是auto_increment，那么为什么不建议采用uuid，使用uuid究
1012
0
0
Python进阶者 Python进阶者
•
9个月前
Excel中这日期老是出来00：00：00，怎么用Pandas把这个去除
大家好，我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下：这日期老是出来00：00：00，怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下：pd.toexcel之前把这
483
0
0
可莉
可莉
Lv1
暂时还承受不起任何人的爱意。
文章
543
粉丝
1
获赞
1
热门文章
带你入门前端工程：微前端
了解Vuex状态管理模式
Vue 包大小优化
11款插件让你的Chrome成为全世界最好用的浏览器｜Chrome插件推荐
2020年webstorm最新激活码（同步更新）