MySQL权限和备份

最新推荐文章于 2025-03-18 15:36:29 发布

Eric-Luo

最新推荐文章于 2025-03-18 15:36:29 发布

阅读量458

点赞数

分类专栏： linux笔记文章标签：用户权限数据备份增量备份完全备份

本文链接：https://blog.youkuaiyun.com/baidu_16824131/article/details/96288802

版权

linux笔记专栏收录该内容

17 篇文章

订阅专栏

本文详细介绍了MySQL的用户权限管理，包括添加、修改和删除用户，以及权限的授予和撤销。接着讨论了数据备份的方法，包括物理备份和逻辑备份，以及完全备份、增量备份和差异备份的概念。最后，重点讲解了如何利用MySQL的binlog日志进行增量备份，并演示了数据恢复的流程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

〔1〕用户权限

〔2〕数据备份和恢复

〔3〕逻辑备份与恢复总结

〔1〕用户权限

添加用户并授权
- 命令格式: grant 权限列表 on 库名 to 用户名@'客户端地址' identified by '密码' with grant option;
  - grant all on db2.* to admin@'%' identified by '密码' with grant option;
- with grant option 支持授权用户拥有授权的权限
权限列表
- 所有权限: all
- 无权限: usage
- 个别权限: select, update, insert
- 指定字段: select, update (字段1,..., 字段N)
库名
- 所有库所有表: *.*
- 一个库: 库名.*
- 一张表: 库名.库表
用户名
- 自定义,具有标识性,存储在mysql库的user表里
客户端地址
- 所有主机: %
- 网段内的所有主机: 192.168.4.%
- 一台主机: 192.168.4.1
- 数据库服务器本机: localhost
授权用户登录后相关命令
- 显示登录用户名和地址
  - select user();
- 显示登录用户自身访问权限
  - show grants;
- 授权用户的修改连接密码
  - set password=password("密码");
- 查询密码加密字符串
  - select password("密码");
管理员管理授权用户相关命令
- 查看已有授权用户的权限
  - show grants for 用户名@'客户端地址';
- 重置授权用户的连接密码
  - set password for 用户名@'客户端地址'=password("密码");
  - alter user 用户名@'客户端地址' identified by '密码';
- 删除授权用户
  - drop user 用户名@'客户端地址';
授权库: mysql库
- user表,记录已有的授权用户及权限
  - select user,host from mysql.user; //查看所有授权用户
- db表,记录已有授权用户对数据库的访问权限
  - select user,host,db from mysql.db; //查看对哪些数据库有访问权限
- tables_priv表,记录已有授权用户对表的访问权限
  - select * from mysql.tables_priv;
  - 若是授权用户对某个数据库所有表有权限,该表不会记录
  - 只对指定的某张表的授权用户进行记录,可以查询显示出来
- columns_priv表,记录已有授权用户对字段的访问权限
  - 只对具体指定表的字段进行权限控制
撤销权限
- 命令格式
  - revoke 权限列表 on 库名.表 from 用户名@"客户端地址";
root密码修改
- 密码忘记,恢复操作
  - 修改/etc/my.cnf文件,在[mysqld]下添加skip-grant-tables保存退出
    - 如果有密码策略的话先注释,root密码修改后在取消注释
  - 重启服务systemctl restart mysqld
  - update mysql.user set authentication_string=password("新密码") where user="root" and host="localhost";
  - 退出,注释skip-grant-tables,重启服务,密码登录root
- 重置密码
  - mysqladmin -uroot -p password "新密码";提示输入旧密码,有警告提示,不用管

〔2〕数据备份和恢复

数据备份
- 物理备份
  - 冷备: cp ,tar...
- 逻辑备份
  - 概念:软件自带的备份命令
  - 备份命令: mysqldump
  - 恢复命令: mysql
物理备份操作
- tar命令备份数据库
  - cd /var/lib/msyql
  - tar -zcf /root/mysql.bak.tar.gz ./*
- 传递备份
  - scp /root/mysql.bak.tar.gz root@192.168.4.51:
- 删除原有数据文件,解压备份
  - rm -rf /var/lib/mysql/*
  - tar -xf /root/mysql.bak.tar.gz -C /var/lib/mysql/
- 修改权限
  - chown -R mysql:mysql /var/lib/mysql
- 重启服务
  - systemctl restart mysqld
逻辑备份
- 备份策略
  - 完全备份: 备份所有数据
  - 增量备份: 备份上次备份后,所有新产生的数据
  - 差异备份: 备份完全备份后,所有新产生的数据
- 完全备份
  - 格式: mysqldump -uroot -p密码库名 > 目录/xxx.sql
  - 库名表示
    - 所有库备份: --all-databases 或 -A
    - 单个库备份: 数据库名
    - 单张表备份: 数据库表名
    - 多个库备份: -B 数据库1 数据库2
    - 例
      - mysqldump -uroot -p123456 -A > /root/alldb.sql
      - mysqldump -uroot -p123456 db3> /root/alldb.sql
      - mysqldump -uroot -p123456 db3 user> /root/alldb.sql
      - mysqldump -uroot -p123456 -B db3 db4 > /root/alldb.sql
- 完全恢复
  - 格式: mysql -uroot -p密码 [库名] < 目录／xxx.sql
  - 说明: 对单个库或者单个表的恢复必须指定库名,库必须事先存在
- 完全备份缺点
  - 备份时对表加锁,此时用户无法进行读写操作
  - 备份的数据恢复时,缺少新产生的数据,因此需要结合增量备份或差异备份
- 增量备份
  - 由MySQL服务的binlog日志文件实现
  - binlog日志文件
    - 二进制日志文件,MySQL服务日志文件的一种,默认不启用
    - 实时记录除查询(select,desc,show)之外的所有SQL命令,可用于数据备份和恢复
    - 配置MySQL主从同步的必要条件
  - 启用binlog
    - 配置选项
      - server_id=数字数字范围1-255,通常指定为主机IP地址的主机位
      - log-bin[=目录/文件名] 启用binlog日志,默认日志文件路径是/var/lib/mysql/
      - max_binlog_size=数值m 指定日志文件容量,默认为1G
    - 修改配置文件/etc/my.cnf
      - 默认配置
        vim /etc/my.cnf 添加 log-bin , server_id=50两行;
      - 自定义日志文件目录和日志文件名称
        mkdir /mylog
        chown mysql:mysql /mylog
        vim /etc/my.cnf 添加 log-bin=/mylog/db50 , server_id=50两行;
      - systemctl restart mysqld
  - binlog文件默认命名
    - 索引文件
      - 主机名-bin.index
      - 如: mysql50-bin.index
    - 第一个二进制日志文件
      - 主机名-bin.000001
      - 如: mysql50-bin.000001
    - 第n个二进制日志文件
      - 主机名-bin.n
  - 手动生成新的binlog日志文件
    - 重启MySQL服务
      - systemctl restart mysqld
      - 每重启一次生成一个新的日志文件,同时MySQL的日志会记录在最新的日志文件中
    - 在 shell命令行中调用SQL命令
      - 格式: mysql -uroot -p密码 -e 'SQL命令'
      - 使用flush logs刷新日志文件,也会生成新的日志文件
        mysql -uroot -p123456 -e 'flush logs'
    - 在mysql命令行中使用命令flush logs
    - 备份时添加 --flush-logs
      - mysqldump -uroot -p123456 --flush-logs db3 > /bbb/db3.sql
  - 清理日志
    - 删除指定编号之前的binlog日志文件
      - purge master logs to "binlog文件名";
      - purge master logs to "mysql50-bin.000003"; //删除编号1,2 的日志文件
    - 删除所有日志文件,并创建新的日志文件
      - reset master;
  - 分析日志
    - 查看当前mysql服务使用的日志文件
      - show master status; //可以看到日志文件名称,位置偏移量
    - 查看当前日志的记录格式
      - show variables like "binlog_format";
      - 记录格式有三种
        statement 报表模式
        row 行模式
        mixed 混合模式
    - 修改日志记录格式
      - 修改配置文件/etc/my.cnf,添加binlog_format=模式名称;
        如: binlog_format=mixed
      - 重启服务
  - 查看日志内容
    - 命令格式
      - mysqlbinlog [选项] binlog日志文件
      - 如: mysqlbinlog /mylog/db50.000001
    - 选项
      - 起始时间: --start-datetime="yyyy-mm-dd hh:mm:ss"
      - 结束时间: --stop-datetime="yyyy-mm-dd hh:mm:ss"
      - 起始偏移量: --start-position=数字
      - 结束偏移量: --stop-position=数字
- 恢复数据
  - 思路: 使用mysqlbinlog 提取历史的SQL操作,通过管道交给mysql命令执行
  - 格式: mysqlbinlog [选项] binlog日志文件 | mysql -uroot -p123456
    - 添加选项是读取日志指定范围的SQL操作,不加就是全部执行日志文件的SQL
    - 若是同一日志文件产生新的SQL记录,如果不指定范围的恢复,则会报错
  - 例:指定范围
    - mysqlbinlog --start-position=2000 --stop-position=4000 db50.000001 | mysql -uroot -p123456