Mysql用户管理、常用SQL语句及数据库的备份与恢复

Wesley13
• 阅读 759

本文索引:

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

MySQL用户管理

创建用户

  1. 指定具体ip

    这里指定了具体的ip

    .:第一个表示任意的数据库,第二个表示任意表

    mysql> grant all on . to 'test1'@'127.0.0.1' identified by '1234456'; Query OK, 0 rows affected (0.03 sec)

不指定ip无法登录

[root@localhost ~]# mysql -utest1 -p123456
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test1'@'localhost' (using password: YES)

-h指定ip后成功登录

[root@localhost ~]# mysql -utest1 -p123456 -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 4
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> quit
Bye
  1. 限制命令

    对test2用户只给予SELECT,UPDATE,INSERT权限

    mysql> grant SELECT,UPDATE,INSERT on db1.* to 'test2'@'192.168.65.1' identified by '111'; Query OK, 0 rows affected (0.01 sec)

    查看test2@'192.168.65.1'的权限

    mysql> show grants for test2@'192.168.65.1'; +-----------------------------------------------------------------------------------------------------------------+ | Grants for test2@192.168.65.1 | +-----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON . TO 'test2'@'192.168.65.1' IDENTIFIED BY PASSWORD '832EB84CB764129D05D498ED9CA7E5CE9B8F83EB' | | GRANT SELECT, INSERT, UPDATE ON db1. TO 'test2'@'192.168.65.1' | +-----------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

  2. 任意ip

    %表示任意的意思

    mysql> grant all on db1.* to 'test3'@'%' identified by '111'; Query OK, 0 rows affected (0.00 sec)

    mysql> show grants for test3; +------------------------------------------------------------------------------------------------------+ | Grants for test3@% | +------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON . TO 'test3'@'%' IDENTIFIED BY PASSWORD '832EB84CB764129D05D498ED9CA7E5CE9B8F83EB' | | GRANT ALL PRIVILEGES ON db1. TO 'test3'@'%' | +------------------------------------------------------------------------------------------------------+

  3. 同一个用户指定多个ip

    这里以test2为例

    通过show grants命令查看创建命令,其内的密码时加密后的,这里我们无法使用下列的命令直接创建

    grant SELECT,UPDATE,INSERT on db1.* to 'test2'@'192.168.65.2' identified by '*832EB84CB764129D05D498ED9CA7E5CE9B8F83EB';

    但是我们可以通过修改显示出的命令,实现创建目的

    mysql> show grants for test2@'192.168.65.1'; +-----------------------------------------------------------------------------------------------------------------+ | Grants for test2@192.168.65.1 | +-----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON . TO 'test2'@'192.168.65.1' IDENTIFIED BY PASSWORD '832EB84CB764129D05D498ED9CA7E5CE9B8F83EB' | | GRANT SELECT, INSERT, UPDATE ON db1. TO 'test2'@'192.168.65.1' | +-----------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

    将查看到的命令进行修改,如下所示

    mysql> GRANT USAGE ON . TO 'test2'@'192.168.65.2' IDENTIFIED BY PASSWORD '*832EB84CB764129D05D498ED9CA7E5CE9B8F83EB'; Query OK, 0 rows affected (0.00 sec)

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

    可以查看密码并没有修改

    mysql> show grants for test2@'192.168.65.2'; +-----------------------------------------------------------------------------------------------------------------+ | Grants for test2@192.168.65.2 | +-----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON . TO 'test2'@'192.168.65.2' IDENTIFIED BY PASSWORD '832EB84CB764129D05D498ED9CA7E5CE9B8F83EB' | | GRANT SELECT, INSERT, UPDATE ON db1. TO 'test2'@'192.168.65.2' | +-----------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)


简单常用SQL语句

  • 查看表内的行数

    mysql> select count() from mysql.user; +----------+ | count() | +----------+ | 11 | +----------+ 1 row in set (0.03 sec)

  • 获取表的内容

    mysql> select * from mysql.db; +--------------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv | +--------------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | % | test | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | N | N | Y | Y | | % | test_% | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | N | N | Y | Y | | 192.168.65.1 | db1 | test2 | Y | Y | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | 192.168.65.2 | db1 | test2 | Y | Y | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | % | db1 | test3 | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | +--------------+---------+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ 5 rows in set (0.00 sec)

    使用\G代替;,使显示更规整

    mysql> select * from mysql.db\G

  • 查看表的字段

    查看表的某个字段

    mysql> select db from mysql.db; +---------+ | db | +---------+ | db1 | | test | | test_% | | db1 | | db1 | +---------+ 5 rows in set (0.00 sec)

    查看表的多个字段,字段间使用,分割

    mysql> select db,user from mysql.db; +---------+-------+ | db | user | +---------+-------+ | db1 | test3 | | test | | | test_% | | | db1 | test2 | | db1 | test2 | +---------+-------+ 5 rows in set (0.00 sec)

  • 查看指定ip/ip范围的字段

    like表示模糊匹配,这里%可以是1-255的任意一个

    mysql> select * from mysql.db where host like '192.168.65.%'; +--------------+-----+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv | +--------------+-----+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | 192.168.65.1 | db1 | test2 | Y | Y | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | 192.168.65.2 | db1 | test2 | Y | Y | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | +--------------+-----+-------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ 2 rows in set (0.00 sec)

    mysql> select * from mysql.db where host like '192.168.65.%'\G *************************** 1. row *************************** Host: 192.168.65.1 Db: db1 User: test2 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.65.2 Db: db1 User: test2 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) mysql> insert into db1.t1 values (1, 'abc'); Query OK, 1 row affected (0.04 sec)

  • 插入内容: insert

    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)

  • 修改内容:update

    根据id字段修改name字段

    mysql> update db1.t1 set name='aaa' where id=1; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0

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

    根据name字段修改id字段

    mysql> update db1.t1 set id=8 where name='abc'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

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

  • 清空表内数据(表的结果为删除)

    mysql> truncate db1.t1; Query OK, 0 rows affected (0.01 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.01 sec)

  • 删除表内的某些数据

    mysql> delete from db1.t1 where id=2; Query OK, 0 rows affected (0.00 sec)

    mysql> delete from db1.t1 where id=1; Query OK, 2 rows affected (0.01 sec)

  • 删除操作:drop

    删除表

    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

    删除数据库

    mysql> drop database db1; Query OK, 0 rows affected (0.04 sec) mysql> use db1; ERROR 1049 (42000): Unknown database 'db1'

作为运维人员,truncate、drop、delete等删除命令不能随意使用,极易造成数据的丢失。


MySQL数据库的备份与恢复

  • 备份库

    mysqldump -uroot -p123456 mysql > /tmp/mysql.sql

  • 恢复库

    mysql -uroot -p123456 mysql < /tmp/mysql.sql

  • 备份表

    mysqldump -uroot -p123456 mysql user > /tmp/user.sql

  • 恢复表

    mysql -uroot -p123456 mysql < /tmp/user.sql

  • 备份所有库

    mysqldump -uroot -p -A > /tmp/123.sql

  • 只备份表结构

    mysqldump -uroot -p123456 -d mysql > /tmp/mysql.sql


点赞
收藏
评论区
推荐文章
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
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
3个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
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 )
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
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
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
9个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这