阿里云-云小站（无限量代金券发放中）
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL逻辑备份mysqldump

526次阅读
没有评论

共计 6535 个字符，预计需要花费 17 分钟才能阅读完成。

MySQL 备份之 mysqldump

mysqldump

mysqldump 工具备份：

本质： 导出的是 SQL 语句文件

优点： 不论是什么存储引擎，都可以用 mysqldump 备成 SQL 语句

缺点： 速度较慢，导入时可能会出现格式不兼容的突发情况，无法做增量备份和累计增量备份

提供三种级别的备份，表级，库级和全库级 

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

说明：

如果备份对象下的数据库绝大多数都是 myisam 类型表，为了保证数据的一致性，备份时需要锁定表

如果是针对 innodb 的表进行备份由于 innodb 是事务型的引擎，会话与会话之间是隔离的，所以备份的时候不影响数据库的正常使用，无需锁表 

--lock-tables          如果备份的数据库里的表与其他库没有关系的话，那么只需要锁定该库下的表就可以了
--lock-all-tables      如果备份的数据库里的表与其他库有关系的话，那么需要锁定整个 mysql 数据库的所有库下的所有表
--flush-logs, -F       开始备份前刷新日志
--flush-privileges     备份包含 mysql 数据库时刷新授权表
--lock-all-tables, -x MyISAM 一致性 服务可用性
--lock-tables, -l      备份前锁表
--single-transaction   适用 InnoDB 引擎，保证一致性 服务可用性
--master-data=1|2      该选项将会记录 binlog 的日志位置与文件名并追加到文件中

表级备份 / 恢复

表级备份：

[root@Admin ~]# mysqldump -p123 db01 table01 > /tmp/mysqlback/table01.sql              # 备份单个表 

[root@Admin ~]# mysqldump -p123 db01 table01 table02 > /tmp/mysqlback/table01_02.sql   # 备份多个表

表级恢复：

[root@Admin ~]# mysql -p123 db01 < /tmp/mysqlback/table01.sql

或者在 mysql 数据库内使用 source 命令来执行外部的 sql 文件
mysql> source /tmp/mysqlback/table01.sql

库级备份 / 恢复

库级备份：

[root@Admin ~]# mysqldump --databases db01 -p123 > /tmp/mysqlback/db01.sql             # 备份单个库 

[root@Admin ~]# mysqldump --databases db01 db02 -p123 > /tmp/mysqlback/db01_02.sql     # 备份多个库

表级恢复：

[root@Admin ~]# mysql -p123 < /tmp/mysqlback/db01.sql

mysql> source /tmp/mysqlback/db01_02.sql
说明：不要从操作层面删除任何一个库和任何一个表，如果真的这么做了，那么恢复的时候会恢复失败，因为 mysql 库里面不仅记录了用户信息等还记录了数据库表结构等。从操作层面删除，而 mysql 库里面并不知道。所以坚决不能从操作层面删除任何一个库和任何一个表。

全库备份 / 恢复

全库级备份：

考虑到数据库有 innodb，也有其他类型的表，那么就只能锁表备份 

[root@Admin ~]# mysqldump -p123 --lock-tables --all-databases > /tmp/mysqlback/alldb.sql

全库级恢复：

[root@Admin ~]# mysql -p123 < /tmp/mysqlback/alldb.sql

mysql> source /tmp/mysqlback/alldb.sql

注意：如果是在终端直接在数据目录里面将数据全部删除，再恢复数据的话就需要初始化才能恢复。

mysqldump+binlog

完全备份（mysqldump）+ 增量备份（binlog）

适用于中小型数据库；通过结合二进制日志文件，把数据库恢复到最新的状态

二进制日志默认会记录下所有对数据库变化的操作

二进制日志文件中会记录某个操作的详细 SQL 语句，还有执行的时候环境，时间，以及该记录在二进制日志文件的起始和结束点 pos 值 

error log  # 错误日志，记录 mysql 服务端在运行时产生的错误信息，以及 mysql 启动和关闭的日志信息（排错）
slow log   # 慢查询日志，慢查询时间阀值，以秒为单位，如果超过这个阀值就是慢查询（调优）
bin log    # 二进制日志，记录对数据库增、删、改的 SQL 操作，可以使用这个日志做增量备份（备份）
Relay log  # 中继日志（主从复制日志）从机器上从主机器复制过来日志，根据日志来同步数据（复制）

配置二进制日志

查看二进制日志是否开启：

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------+
| Variable_name                   | Value                    |
+---------------------------------+--------------------------+
| log_bin                         | OFF                      |
| log_bin_basename                | /data/DB/mysql-bin       |
| log_bin_index                   | /data/DB/mysql-bin.index |
| log_bin_trust_function_creators | ON                       |
| log_bin_use_v1_row_events       | OFF                      |
| sql_log_bin                     | ON                       |
+---------------------------------+--------------------------+
6 rows in set (0.00 sec)

log_bin  |OFF 关闭   ON 开启

修改配置文件 

[root@Admin ~]# vim /etc/my.cnf
log-bin=/var/lib/mysql/mysql56-bin.log
log-bin（可直接这样写就 ok）[root@Admin ~]# service mysqld restart 
Shutting down MySQL....                                    [确定]
Starting MySQL.....                                        [确定]

再次查看开启：

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------+
| Variable_name                   | Value                    |
+---------------------------------+--------------------------+
| log_bin                         | ON                       |
| log_bin_basename                | /data/DB/mysql-bin       |
| log_bin_index                   | /data/DB/mysql-bin.index |
| log_bin_trust_function_creators | ON                       |
| log_bin_use_v1_row_events       | OFF                      |
| sql_log_bin                     | ON                       |
+---------------------------------+--------------------------+
6 rows in set (0.00 sec)

mysqlbinlog

--start-datetime=name 开始的时间
--stop-datetime=name  结束的时间
--start-position=#    开始的位置（POS）--stop-position=#     结束的位置

示例 1

备份

先做全量备份，然后更新数据并误操作，数据恢复 

[root@Admin ~]# mysqldump -p123 --flush-logs --master-data=2 --all-databases > /tmp/mysqlback/all_back.sql
  • –flush-logs      备份时先将内存中日志写回磁盘，然后截断日志，并产生新的日志文件
  • –master-data=2  该选项将二进制日志的位置和文件名写入到备份文件，等于 2 表示 CHANGE
  • MASTER 语句被写成 SQL 注释；1 表示没有注释，默认是 1.

查看完整备份文件中的字段 

[root@Admin ~]# vim /tmp/mysqlback/all_back.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=120;

数据更改

添加几条数据，然后随便删除一个库

更改完后查看 mysql-bin.000008 日志文件找到误删除的 POS 值

MySQL 逻辑备份 mysqldump

[root@Admin DB]# mysqlbinlog --no-defaults mysql-bin.000008 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180520  3:55:12 server id 1  end_log_pos 120 CRC32 0x077f82c8     Start: binlog v 4, server v 5.6.31-log created 180520  3:55:12
# Warning: this binlog is either in use or was not closed properly.
BINLOG 'IIEAWw8BAAAAdAAAAHgAAAABAAQANS42LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAciC
fwc=
'/*!*/;
# at 120
#180520  3:56:46 server id 1  end_log_pos 201 CRC32 0xa954edb5     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1526759806/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 201
#180520  3:56:46 server id 1  end_log_pos 316 CRC32 0xc34378c8     Query    thread_id=1    exec_time=0    error_code=0
use `login`/*!*/;
SET TIMESTAMP=1526759806/*!*/;
insert into t1(id,name) values(6,'eee')
/*!*/;
# at 316
#180520  3:56:46 server id 1  end_log_pos 347 CRC32 0xea43bde1     Xid = 1227
COMMIT/*!*/;
# at 347
#180520  3:56:53 server id 1  end_log_pos 428 CRC32 0x5fd30851     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1526759813/*!*/;
BEGIN
/*!*/;
# at 428
#180520  3:56:53 server id 1  end_log_pos 543 CRC32 0x97402f36     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1526759813/*!*/;
insert into t1(id,name) values(8,'aaa')
/*!*/;
# at 543
#180520  3:56:53 server id 1  end_log_pos 574 CRC32 0xfa2cc4ba     Xid = 1228
COMMIT/*!*/;
# at 574
#180520  3:57:00 server id 1  end_log_pos 655 CRC32 0x7ba6913f     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1526759820/*!*/;
BEGIN
/*!*/;
# at 655
#180520  3:57:00 server id 1  end_log_pos 771 CRC32 0x7856052f     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1526759820/*!*/;
insert into t1(id,name) values(10,'bbb')
/*!*/;
# at 771
#180520  3:57:00 server id 1  end_log_pos 802 CRC32 0x0b597d2b     Xid = 1229
COMMIT/*!*/;
# at 802
#180520  3:57:19 server id 1  end_log_pos 894 CRC32 0x47136864     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1526759839/*!*/;
drop database db01
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

mysql-bin.000008 日志文件

通过查看二进制日志，由于我刚刚是删除的一个 db01 库，删除 db01 库的那条操作上面的 POS 值是 at 802，所以我应该恢复到 802

恢复

先进行全库恢复 

[root@Admin ~]# mysql -p123 < /tmp/mysqlback/all_back.sql
[root@Admin ~]# mysqlbinlog --start-position=120 --stop-position=802 /data/DB/mysql-bin.000008 |mysql -p123

恢复完成后进入数据库查看是否存在刚刚添加的数据

总结

mysqldump+binlog 做增量备份——> 通过 binlog 日志恢复到最新状态

  • 当前数据库必须开启二进制日志（修改配置文件）
  • 使用 mysqldump 工具做全库备份
  • 更新数据
  • 直接恢复
  • 使用全库备份恢复
  • 使用 binlog 日志恢复到最新状态

 

正文完
星哥玩云-微信公众号
post-qrcode
MySQL 数据库
发表至： 数据库技术
2022-01-28
 0
星锅
版权声明：本站原创文章，由 星锅 于2022-01-28发表，共计6535字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
【腾讯云】推广者专属福利，新客户无门槛领取总价值高达2860元代金券，每种代金券限量500张，先到先得。
Windows Server 2012下MySQL 5.7解压版(zip)安装配置
Oracle创建用户、角色、授权、建表
HP-UX平台Oracle启动实例遭遇：ORA-27154,ORA-27300,ORA-27301,ORA-27302
MySQL事务处理实现方法步骤
关于SQL Server 2005存储过程的写法
MySQL主从复制及遇到问题的解决
配置MySQL GTID 主从复制
MySQL 5.7多实例安装配置实例
阿里云-最新活动爆款每日限量供应
评论（没有评论）
验证码
星哥说事
星哥说事
分享互联网知识
阅读量
3391827
评论数
4
文章搜索
热门文章
基于开源MaxKB构建大语言模型的本地知识库系统

基于开源MaxKB构建大语言模型的本地知识库系统

基于开源 MaxKB 构建大语言模型的本地知识库系统 什么是 MaxKB MaxKB 是一款基于 LLM（La...
获取各大人工智能AI工具通过API和KEY调用的方法

获取各大人工智能AI工具通过API和KEY调用的方法

获取各大人工智能 AI 工具通过 API 和 KEY 调用的方法 前言 随着人工智能技术的飞速发展，AI 工具...
安装开源软件ChatALL（齐叨）来聚合各大人工智能工具

安装开源软件ChatALL（齐叨）来聚合各大人工智能工具

安装开源软件 ChatALL（齐叨）来聚合各大人工智能工具 前言 如果你跟我一样无论遇到什么问题都要询问人工智...
给你的NAS无限可能，安装小晓雅全家桶影音库

给你的NAS无限可能，安装小晓雅全家桶影音库

给你的 NAS 无限可能，安装小晓雅全家桶影音库 家庭存储和娱乐的需求日益增长。无论是高清电影、电视剧，还是音...
Python历史

Python历史

Python 是著名的“龟叔”Guido van Rossum 在 1989 年圣诞节期间，为了打发无聊的圣诞...
CDN
阿里云CDN-提高用户访问的响应速度和成功率
随机文章
MySQL binlog后面的编号如何取值？

MySQL binlog后面的编号如何取值？

导读 每个 binlog 文件都有编号，从最早的 3 位数（没错，很老的版本只有 3 位数~），到现在扩展到 ...
Linux 上设置自己的公共时间服务器

Linux 上设置自己的公共时间服务器

导读 最重要的公共服务之一就是报时 timekeeping，但是很多人并没有意识到这一点。大多数公共时间服务器...
详解：MaxScale中间件部署数据库读写分离

详解：MaxScale中间件部署数据库读写分离

操作系统:CentOS Linux release 7.3.1611 (Core) 数据库:MariaDB-1...
Linux下有趣的命令

Linux下有趣的命令

让我们来一起学习一下 linux 里好玩的命令 screenfetch: 显示系统、主题信息 安装使用: su...
内网之mobaxterm远程访问

内网之mobaxterm远程访问

导读 MobaXterm 是您的远程计算的终极工具箱。在单个 Windows 应用程序中，它提供了大量为程序员...
一言一句话
-「」
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中