Mysqldump逻辑备份恢复与binglog日志恢复

淡淡的烟草味﹌ 2022-12-30 13:41 151阅读 0赞

前言

环境:cetos7
数据库:Mysql7.4

Mysqldump简介

  1. mysqldump 是 MySQL 自带的逻辑备份工具。
    它的备份原理是通过协议连接到 MySQL 数据库,将需要备份的数据查询出来,将查询出的数据转换成对应的insert 语句,当我们需要还原这些数据时,只要执行这些 insert 语句,即可将对应的数据还原。
  2. 语法:
    远程库: mysqldump -h 服务器 -u数据库用户名 -p数据库密码 要备份的数据库名 > 备份文件.sql
    本地库: mysqldump -u数据库用户名 -p数据库密码 要备份的数据库名 > 备份文件.sql
  3. 命令参数:

    -A, —all-databases #备份所有库

    -B, —databases #备份多个数据库

    -F, —flush-logs #备份之前刷新binlog日志

    -set, —default-character #指定导出数据时采用何种字符集,如果数据表不是采用默认的latin1字符集的话,那么导出时必须指定该选项,否则再次导入数据后将产生乱码问题。

    -d, —no-data, #不导出任何数据,只导出数据库表结构。

    -f, —force #即使在一个表导出期间得到一个SQL错误,继续。

    —lock-tables #备份前,锁定所有数据库表 #此项默认数据库是开启的,备份时默认锁表。

    —single-transaction # 此项解锁 ,数据库备份时不锁表,保证数据的一致性和服务的可用性

备份数据库

  1. 1. 备份所有库
  2. mysqldump -u数据库用户名 -p'数据库密码' -A > /opt/$(date +%F).sql # 设置备份文件名为当前系统时间
  3. 2. 备份指定库
  4. mysqldump -u数据库用户名 -p'数据库密码' -B 库名1 库名2 库名3.... > /opt/$(date +%F).sql

恢复备份库

  1. 恢复停止binlog日志,应为这等于是在执行sql语句,无需记录日志
  2. 1. 恢复所有库
  3. mysql -u数据库名称 -p'数据库密码' < /备份的库文件.sql
  4. 2. 恢复指定库
  5. 步骤一.
  6. 创建一个新库,create database new 假设创建一个new的新库。
  7. 步骤二.
  8. mysql -u数据库名称 -p'数据库密码' new < /备份的库文件.sql

备份数据表

  1. 1. 备份指定表
  2. mysqldump -u数据库用户名 -p'数据库密码' 库名 表名 > /备份文件.sql
  3. 2. 备份多个表
  4. mysqldump -u数据库用户名 -p'数据库密码' 库名 表名1 表名2... > /备份文件.sql
  5. 3. 只备份表结构
  6. mysqldump -u数据库用户名 -p'数据库密码' -d 库名 表名 > /备份文件.sql
  7. 4. 只导出表数据
  8. 步骤一.
  9. mysql> show variables like "secure_file_priv"; ----查询导入导出的目录,可修改。修改完要在
  10. /etc/my.cnf 里[mysqld] 追加secure_file_priv=/新的路径,然后重启。
  11. 步骤二.
  12. 登陆数据查看数据
  13. mysql> show databases; #找到test库
  14. mysql> use test #进入test库
  15. mysql> select * from t3 into outfile '/sql/test.t3.bak'; #导出t3表的数据
  16. 步骤三.
  17. 数据的导入
  18. mysql> delete from t3; #先将原来表里面的数据清除掉,保证是空表,只保留表结构
  19. mysql> load data infile '/sql/test.t3.bak' into table t3;
  20. 如果将数据导入别的表,需要创建这个表并创建相应的表结构。

恢复数据表

  1. 方法一.
  2. mysql -u数据库用户名 -p'数据库密码' 新建一个库 < 备份的库.sql
  3. 方法二.
  4. 创建一个库
  5. create table nwe;
  6. 进入库
  7. use nwe;
  8. 执行
  9. mysql> source 备份的库; -------加路径和备份的文件

通过binlog日志恢复

  1. vim /etc/my.cnf #修改配置文件,开启数据库binlog日志
  2. log-bin=/var/log/sql-bin/mylog
  3. server-id=1
  4. [root@mysql-server ~]# mkdir /var/log/sql-bin #创建日志目录
  5. [root@mysql-server ~]# chown mysql.mysql /var/log/sql-bin #给予日志目录mysql权限
  6. [root@mysql-server ~]# systemctl restart mysqld # 重启数据库开启日志
  7. mysql> flush logs; # 刷新binlog日志会截断产生新的日志文件
  8. [root@mysql-server sql-bin]# mysqlbinlog mylog.000001 #查看日志文件 at1 log_pos 20 为结束
  9. 恢复 日志中at1 log_pos 20 的操作
  10. [root@mysql-server sql-bin]# mysqlbinlog --start-position 1 --stop-position 20 mylog.000001 | mysql -u数据库用户名 -p'数据库密码'
  11. 在数据库中 binlog日志的其他操作:
  12. 1.查看所有binlog日志列表
  13. mysql> show master logs;
  14. 2.查看master状态,即最后(最新)一个binlog日志的编号名称,及其最后一个操作事件pos结束点(Position)值
  15. mysql> show master status;
  16. 3.刷新log日志,自此刻开始产生一个新编号的binlog日志文件
  17. mysql> flush logs;
  18. 注:每当mysqld服务重启时,会自动执行此命令,刷新binlog日志;在mysqldump备份数据时加 -F 选项也会刷新binlog日志;
  19. 4.重置(清空)所有binlog日志
  20. mysql> reset master;
  21. 5.查询第一个(最早)的binlog日志:
  22. mysql> show binlog events;
  23. 6.指定查询 mysql-bin.000002 这个文件:
  24. mysql> show binlog events in 'mysql-bin.000002';
  25. 7.指定查询 mysql-bin.000002 这个文件,从pos点:8224开始查起:
  26. mysql> show binlog events in 'mysql-bin.000002' from 8224;
  27. 8.指定查询 mysql-bin.000002 这个文件,从pos点:8224开始查起,查询10
  28. mysql> show binlog events in 'mysql-bin.000002' from 8224 limit 10;
  29. 9.指定查询 mysql-bin.000002 这个文件,从pos点:8224开始查起,偏移2行,查询10
  30. mysql> show binlog events in 'mysql-bin.000002' from 8224 limit 2,10\G;

======================================================================================================================================================================================================================================================================================================
辛苦浏览观看,如果对你有帮助,请顺手点个赞吧 (σ゚∀゚)σ…:*☆

发表评论

表情:
评论列表 (有 0 条评论,151人围观)

还没有评论,来说两句吧...

相关阅读