MySQL备份方案-->（利用mysqldump以及binlog二进制日志）

最新推荐文章于 2024-11-01 12:20:35 发布
最新推荐文章于 2024-11-01 12:20:35 发布
阅读量282 收藏
点赞数
文章标签： 数据库 shell 操作系统

MySQL备份方案-->（利用mysqldump以及binlog二进制日志）

随着数据不断增加，而且为了兼容以后的innodb存储引擎, 所以考虑采用mysqldump全备+日志增量备份的策略。使用mysqldump对于mysql大部分mysql存储引擎比如myisam、innodb都有很好的支持。

方案一：mysqldump全备份+日志增量备份

1， mysqldump备份方案：

周一凌晨3点全备

周二到周日凌晨3点增量备份

2， 备份步骤

(1) 创建备份目录，备份脚本存放目录

Shell > mkdir /usr/mysqlbackup;

Shell > chmod 755 /usr/mysqlbackup;

Shell > mkdir /usr/mysqlbackup/daily;

Shell > chmod 755 /usr/mysqlbackup/daily;

Shell > mkdir /usr/script;

Shell > chmod 777 /usr/script/*.sh

(2) 启用二进制日志

如果日志没有启开，必须启用binlog，要重启mysqld，首先，关闭mysqld，打开/etc/my.cnf，加入以下几行：

[mysqld]

log-bin

然后重新启动mysqld，会产生hostname-bin.000001以及hostname-bin.index，前面的日志文件是记录所有对数据的更新操作，后面的文件是存储所有二进制文件的索引，不能轻易被删除。

(3) 全备份，增量备份。

详细见mysqlFullBackup.sh、mysqlDailyBackup.sh脚本（请注意脚本里面的备份目录、mysql软件安装目录、压缩文件名以及用户名密码，如有不符，请修改）。

下面是部分shell上单个手动执行的测试命令。

Shell> /usr/local/mysql/bin/mysqldump -uroot-pnYuIman25040slave201012301124 --no-create-info=FALSE --order-by-primary=FALSE--force=FALSE --no-data=FALSE --tz-utc=TRUE --flush-privileg

es=FALSE --compress=FALSE --replace=FALSE--insert-ignore=FALSE --extended-insert=TRUE --quote-names=TRUE --hex-blob=TRUE--complete-insert=FALSE --add-locks=TRUE --port=3306 --d

isable-keys=TRUE --delayed-insert=FALSE--create-options=TRUE --delete-master-logs=FALSE --comments=TRUE--default-character-set=utf8 --max_allowed_packet=1G --flush-logs=FALSE -

-dump-date=TRUE --lock-tables=TRUE--allow-keywords=FALSE --events=FALSE --single-transaction=TRUE --routines--all-databases > /backup/mysql/full/mysql_20110104_195546.sql

(4)设置crontab任务，每天执行备份脚本

shell> crontab –e

#每个星期日凌晨3:00执行完全备份脚本

0 3 * * 0 /usr/scrpit/mysqlFullBackup.sh>/dev/null 2>&1

#周一到周六凌晨3:00做增量备份

0 3 * * 1-6/root/MySQLBackup/mysqlDailyBackup.sh >/dev/null 2>&1

(5)清除旧的备份文件。

每天去看查看下备份磁盘空间，删除旧的备份压缩文件。

3， 数据库恢复

【注释】如果增量恢复操作中报“ERROR 1051 (42S02) at line 32: Unknown table 'fin_cell_attr_his'”类似的错误，则需要现create一张空表，然后再执行增量恢复。

Mysql> createtable tb1 ……;

(1)全恢复：

用 mysqldump 备份出来的文件是一个可以直接倒入的 SQL 脚本,直接用 mysql命令恢复最后一次全备份：

先从备份文件服务器上copy出来，解压缩，再恢复，例如：

Shell>/root/s3cmd/s3cmd get s3://csf-backup/mysqldb/ mysql_20101216_231900.sql.tar.gz/usr/mysqlrestore/

Shell>/root/s3cmd/s3cmd get s3://csf-backup/mysqldb/ mysql_binlog_20101217_030100.tar.gz/usr/mysqlrestore/

Shell>cd /usr/mysqlrestore/

Shell>tar -xvf mysql_20101216_231900.sql.tar.gz

Shell>tar -xvf mysql_binlog_20101217_030100.tar.gz

刷新日志：

Mysql>flush logs;

恢复最后一次全备份命令：

Shell>/usr/local/mysql/bin/mysql -uroot -p123456 </tmp/mysqlbackup/backup_20101215_adb.sql

恢复最后一次全备份之后的所有增量备份命令：

Shell>/usr/local/mysql/bin/mysqlbinlog mysql-bin.00007 mysql-bin.00008mysql-bin.00009 mysql-bin.00010 mysql-bin.00011 | mysql

检查：查看数据库结构以及数据。

(2)基于时间点恢复一，当天全备份之后的insert点数据故障误操作：

如果上午全备份之后的insert数据被误删除, 在11点发生了误操作.可以直接用bin-log日志将数据库恢复到11点之前的那一刻,然后跳过故障时间点11点,再进行恢复下面所有的操作,命令如下:

刷新日志：

Mysql>flush logs;

先从备份文件服务器上把binlog日志copy出来，解压缩。

Shell>/root/s3cmd/s3cmd get s3://csf-backup/mysqldb/ mysql_binlog_20101221_030100.tar.gz /usr/mysqlrestore/

Shell>cd /usr/mysqlrestore/

Shell>tar -xvf mysql_binlog_20101221_030100.tar.gz

日志增量恢复到故障点之前

Shell>/usr/local/myql/bin/mysqlbinlog --stop-date="2010-12-21 10:59:59" mysql-bin.00011|mysql -u root -p 123456
跳过故障的时间点,继续执行后面的binlog,完成恢复
Shell> /usr/local/myql/bin/mysqlbinlog--start-date="2010-12-21 10:59:59" mysql-bin.00011| mysql -u root -p 123456

检查：

用select * from tb等sql命令查看表以及数据是否恢复。

(3)基于时间点恢复二，当天11点数据故障误操作：

如果上午全备份之后的insert数据被误删除, 在11点发生了误操作.丢失的数据是最后一次备份之前录入的数据，则必须先用全备份文件进行全恢复，然后可以用bin-log日志将数据库恢复到11点之前的那一刻,然后跳过故障时间点11点,再进行恢复下面所有的操作,步骤过程如下：

刷新日志：

Mysql>flush logs;

先从备份文件服务器上copy出来，解压缩，再恢复

Shell>/root/s3cmd/s3cmd get s3://csf-backup/mysqldb/mysql_20101216_231900.sql.tar.gz/usr/mysqlrestore/

Shell>/root/s3cmd/s3cmd get s3://csf-backup/mysqldb/ mysql_binlog_20101217_030100.tar.gz /usr/mysqlrestore/

Shell>cd /usr/mysqlrestore/

Shell>tar -xvf mysql_20101216_231900.sql.tar.gz

Shell>tar -xvf mysql_binlog_20101217_030100.tar.gz

恢复最后一次全备份命令：

Shell>/usr/local/mysql/bin/mysql -uroot -p123456 < /tmp/mysqlbackup/mysql_20101216_231900.sql

日志增量恢复到故障点之前

Shell>/usr/local/myql/bin/mysqlbinlog --stop-date="2010-12-21 10:59:59" mysql-bin.00011|mysql -u root -p 123456
跳过故障的时间点,继续执行后面的binlog,完成恢复
Shell> /usr/local/myql/bin/mysqlbinlog--start-date="2010-12-21 10:59:59" mysql-bin.00011| mysql -u root -p 123456

检查：

用select * from tb等sql命令查看表以及数据是否恢复。

(4)基于时间点恢复三，昨天11点数据故障误操作：

恢复流程，先全恢复，然后日志增量恢复跳过11点后继续日志增量恢复，如下：

先从备份文件服务器上copy出来，解压缩，再恢复

Shell>/root/s3cmd/s3cmd get s3://csf-backup/mysqldb/mysql_20101216_231900.sql.tar.gz/usr/mysqlrestore/

Shell>/root/s3cmd/s3cmd get s3://csf-backup/mysqldb/ mysql_binlog_20101217_030100.tar.gz /usr/mysqlrestore/

Shell>cd /usr/mysqlrestore/

Shell>tar -xvf mysql_20101216_231900.sql.tar.gz

Shell>tar -xvf mysql_binlog_20101217_030100.tar.gz

刷新日志：

Mysql>flush logs;

全恢复：

Shell>/usr/local/mysql/bin/mysql -uroot -p123456 < /tmp/mysqlbackup/ mysql_20101216_231900.sql

日志增量恢复到故障点：

Shell>/usr/local/myql/bin/mysqlbinlog --stop-date="2010-12-20 10:59:59" mysql-bin.00010|mysql -u root -p 123456

跳过故障点继续恢复：

Shell>/usr/local/myql/bin/mysqlbinlog --start-date="2010-12-20 10:59:59" mysql-bin.00010mysql-bin.00011| mysql -u root -p 123456

(5)基于时间点恢复四，多故障点恢复比如2个故障点数据故障误操作：

第一个故障点：2010-12-2113:41:41 –> 2010-12-21 13:42:36

第二个故障点：2010-12-2113:43:16 –> 2010-12-21 13:46:05

恢复流程，先全恢复，然后日志增量恢复跳过第一个故障点后继续日志增量恢复到第二个故障点，然后跳过第二个故障点继续恢复到终点，如下：

先从备份文件服务器上copy出来，解压缩，再恢复

Shell>/root/s3cmd/s3cmd get s3://csf-backup/mysqldb/mysql_20101216_231900.sql.tar.gz/usr/mysqlrestore/

Shell>/root/s3cmd/s3cmd get s3://csf-backup/mysqldb/ mysql_binlog_20101217_030100.tar.gz /usr/mysqlrestore/

Shell>cd /usr/mysqlrestore/

Shell>tar -xvf mysql_20101216_231900.sql.tar.gz

Shell>tar -xvf mysql_binlog_20101217_030100.tar.gz

刷新日志：

Mysql>flush logs;

全恢复：

Shell>/usr/local/mysql/bin/mysql -uroot -p123456 < /tmp/mysqlbackup/ mysql_20101216_231900.sql

日志增量恢复到第一个故障点之前：

Shell>/usr/local/mysql/bin/mysqlbinlog --stop-date="2010-12-21 13:41:41"mysql-bin.000012| mysql -u root -p123456;

跳过故障点继续恢复到第二个故障点之前：

Shell> /usr/local/mysql/bin/mysqlbinlog--start-date="2010-12-21 13:42:36" --stop-date="2010-12-2113:43:16" mysql-bin.000012 mysql-bin.000013|mysql -u root -p123456;

跳过第二个故障点恢复到flushlogs之时

Shell>/usr/local/mysql/bin/mysqlbinlog --start-date="2010-12-21 13:46:05" --stop-date="2010-12-2113:46:33" mysql-bin.000012 mysql-bin.000013| mysql -u root -p123456;

检查：运用sql语句查询误操作的表以及数据是否已经恢复。

(6)恢复单一库：

Shell>/usr/local/mysql/bin/mysql -uroot -pmysql --database=csf </tmp/mysqlbackup/backup_20101215_adb_003.sql

(7)基于单库中的各种时间点情况恢复：

请参考 以上步骤(1)到(5)，过程基本一样，只要在恢复命令后面加上一个空格和库名就可以了。

(8)恢复单库中的单张表：

Mysql所提供的工具目前无法做到这一步，有个方案是：先恢复整个库到一个测试数据服务器上，然后在此服务器上导出单张表的sql，然后把这个sql导入进生产库上。

在测试服务器上导入库：

Shell>/usr/local/mysql/bin/mysql -uroot -pmysql --database=csf </tmp/mysqlbackup/backup_20101215_adb_003.sql

从测试服务器导出单个表：

Shell>/usr/local/mysql/bin/mysqldump --user=root -pmysql --port=3306--default-character-set=utf8--single-transaction=TRUE"csf" "tmp_excel_data_000728" > /tmp/mysqlbackup/backup_20101215_single_table_013.sql

在生产库上导入此表记录

Shell>/usr/local/mysql/bin/mysql -uroot -pmysql csf < /tmp/mysqlbackup/backup_20101215_single_table_013.sql

检查数据

Select* from csf. tmp_excel_data_000728;

(9)电源故障恢复

重启服务器之后，重启数据库，mysql自动恢复，断电时候正在运行的数据库任务和操作需要重新执行一遍。

(10) 操作系统崩溃恢复

操作系统恢复之后，重启数据库，mysql自动恢复，系统崩溃时正在运行的数据库任务以及操作需要重新执行一遍

(11) 文件系统崩溃恢复

文件系统恢复之后，重启数据库即可，系统崩溃时正在运行的数据库任务以及操作需要重新执行一遍。

(12) 硬件坏块恢复

重格磁盘,然后恢复数据库。

iteye_6637
关注
mysql dump 二进制_MySQL备份方案-->（利用mysqldump以及binlog二进制日志）
weixin_28944067的博客
01-27 293
随着数据不断增加，而且为了兼容以后的innodb存储引擎, 所以考虑采用mysqldump全备+日志增量备份的策略。使用mysqldump对于MySQL大部分mysql存储引擎比如myisam、innodb都有很好的支持。方案一：mysqldump全备份+日志增量备份1， mysqldump备份方案：周一凌晨3点全备周二到周日凌晨3点增量备份2， 备份步骤(1) 创建备份目录，备份脚本存放...
mysql 备份binlog,Mysql备份与还原——mysqldump结合binlog
weixin_42467768的博客
03-17 879
MySQL 备份一般采取全库备份加日志备份的方式，例如每天执行一次全备份，每小时执行一次二进制日志备份。这样在 MySQL 故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。一、mysqldump 备份结合 binlog 日志恢复1、binlog介绍mysql 的二进制日志记录着该数据库的所有增删改的操作日志，还包括了这些操作的执行时间。我们可以使用mysqlbi...
mysql备份解决方案
06-21
mysql备份解决方案mysql备份解决方案mysql备份解决方案mysql备份解决方案mysql备份解决方案mysql备份解决方案mysql备份解决方案mysql备份解决方案mysql备份解决方案mysql备份解决方案mysql备份解决方案mysql备份解决方案
mysql数据备份方案
weixin_41902931的博客
05-18 506
另外，还有一些第三方工具和自动化脚本可用于备份 MySQL 数据库，它们提供了更多的选项和灵活性。你可以根据具体情况选择适合你的备份方案。这些是常见的备份方案，具体选择哪种方案取决于你的需求和环境。请注意，在执行备份操作之前，确保你具有适当的权限和对应的工具已经安装。需要注意的是，上述命令中的 、、、、 等参数需要根据实际情况进行替换。复制 MySQL 数据目录（通常是 /var/lib/mysql）到备份目录。打开命令行或终端窗口。
mysql 数据备份方案_Mysql数据备份方案分享
weixin_35812582的博客
01-18 499
考虑到数据的重要性，数据库需要定期的备份，一个比较好的方案记录一下。采用的备份工具为innobackupex，安装方法可以自行搜索一下，或者我之后写一篇文章，也说下mysql的增量和全量备份原理及方法。备份计划：周六进行全量备份，其余时间增量备份，增量的基础是前一天的增量备份，特殊的是周天是对周六的全量备份进行增量。以下是我编写的两个shell脚本，特此分享出来，不足之处可以在下方留言备份脚本运行...
mysql 每日数据备份方案_mysql数据库备份方案
weixin_42380646的博客
02-07 449
方案以一天一次的频率，在每天凌晨2:00备份生产环境数据库至文件服务器。为保证备份文件不过多占用文件服务器空间，备份文件以tgz压缩包格式保存且只保存7天的备份文件。另外每次备份都保存备份日志，一旦出现备份不成功等意外情况保证有据可查。实现思路在文件服务器上，编写备份shell脚本，以mysqldump方式远程备份正式环境mysql中的生产数据库，压缩备份好的sql文件，再删除源文件。然后文件试服...
mysql dump 二进制_基于mysqldump二进制备份恢复
weixin_31100705的博客
01-19 706
备份需要哪些文件？除了数据库数据，还有mysql配置文件，mysql代码(存储过程，存储函数，触发器) ，OS相关的 配置文件，如：crontab配置计划任务及脚本，备份工具本身也需要备份，在主从环境中和复制相关的配置，二进制日志文件，也需要备份！备份工具：mysqldump单机备份工具，对Innodb热备,MyISAM温备，Aria温备。但是mysqldump备份和恢复的过程较慢，数据过10G...
mysqldump备份结合binlog日志恢复
m0_46648661的博客
11-28 645
MySQL备份一般采取全库备份加日志备份的方式，例如每天执行一次全备份，每小时执行一次二进制日志备 份。这样在MySQL故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时 间。 mysql的二进制日志记录着该数据库的所有增删改的操作日志(前提是要在自己的服务器上开启binlog)，还包括 了这些操作的执行时间。为了显示这些二进制内容，我们可以使用mysqlbinlog命令来查看。 Binlog的用途 1. 主从同步 2. 恢复数据 开启binary log功能 通过编辑my
MySQL全量备份与增量备份-mysqldump&mysqlbinlog+binlog2sql
Anon
01-29 352
MySQL数据备份全量备份mysqldump1、全量备份数据库增量备份mysqlbinlog2、保证bin-log已开启查看binlog相关系统参数值binlog的三种格式3、导出binlog查看binlog文件的2种方式binlog2sql3、binlog转化为可执行的sql4、执行增量备份的sql参考文档 上周对测试环境的MySQL进行了一次迁移，学习了一些MySQL数据备份相关的知识。主要...
MYSQL自动备份方案整理
08-02
MYSQL自动备份方案整理,包含了lINUX,WINDOW下的备份，相信对初学者有很大的帮助
MySQL平台数据库备份方案详细说明
boy002的专栏
01-15 973
在数据库表丢失或损坏的情况下，备份你的数据库是很重要的。如果发生系统崩溃，你肯定想能够将你的表尽可能丢失最少的数据恢复到崩溃发生时的状态。 有时，正是MySQL管理员造成破坏。管理员已经知道表已破坏，用诸如vi或Emacs等编辑器试图直接编辑它们，这对表绝对不是件好事。 备份数据库两个主要方法是用mysqldump程序或直接拷贝数据库文件（如用cp、cpio或tar等）。每种方法都有其优缺点： ...
MySQL备份系列--备份方案总结性梳理
guo_3472428370的博客
05-29 1768
mysql数据库备份有多么重要已不需过多赘述了，废话不多说！以下总结了mysql数据库的几种备份方案： 一、binlog二进制日志通常作为备份的重要资源，所以再说备份方案之前先总结一下binlog日志~~ 1.binlog日志内容 1）引起mysql服务器改变的任何操作。 2）复制功能依赖于此日志。 3）slave服务器通过复制master服务器的二进制日志完成主从复制，在执行之前保存于中继日志(relay log)中。 4）slave服务器通常可以关闭二进制日志以提升性能。 ...
MySQL数据库备份
热门推荐
Mapinyi666的博客
07-27 1万+
数据库备份是指将数据库中的数据、表格、视图、存储过程、触发器等信息备份到另一个地方，以便在数据库丢失或损坏时进行恢复。数据库备份是数据库管理中必不可少的一项工作，通过备份可以保护数据库中的数据和业务。
mysql三种备份方案
番茄酱
01-01 5977
Mysql备份方案 方案一：mysqldump+binlog: （ 推荐） 完全备份，通过备份二进制日志实现增量备份 方案二: xtrabackup: 对InnoDB：热备，支持完全备份和增量备份 对MyISAM：温备，只支持完全备份 方案三： lvm2快照+binlog： 几乎热备，物理备份 具体操作如下： 方案一解析： mysqldump+binlog 命
最全的MySQL备份方法分享
hdxx2022的博客
08-12 2299
Innodb 所有的表都保存在同一个数据文件 ibdata1中（也可能是多个文件，或者是独立的表空间文件），相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份 binlog，或者用mysqldump。权限要求类似上面所述。恢复时，可以采用类似以下语句来做到： /usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001。/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001该工具允许你显示指定的数据库下的所有。
MySQL数据库备份的4种方式
yygr的博客
06-24 530
备份方法备份速度恢复速度便捷性功能一般用于cp快快一般、灵活性低很弱少量数据备份mysqldump慢慢一般、可无视存储引擎的差异一般中小型数据量的备份lvm2快照快快一般、支持几乎热备、速度快一般中小型数据量的备份xtrabackup较快较快实现innodb热备、对存储引擎有要求强大较大规模的备份其实我们还可以通过进行备份。
oceanbase V4.2.2社区版集群离线部署
最新发布
king_harry的专栏
11-01 709
版本，选择是上传文件，包含oceanbase-ce、oceanbase-ce-libs、oceanbase-ce-utils。关闭时展示，为 OBProxy 集群的访问地址，仅用于生成租户的连接串，不影响实际使用，需要自主配置负载均衡，如果是 VIP 地址，还需要您自主申请并绑定到 OBProxy Server。集群配置选项卡—更多配置—更改系统内存保留选项，默认是30G，如果测试机内存只有16G，则需要调小，本测试调整为2G，这样ocp-server安装不会失败。自定义待管理的集群的名称。
MySQL备份恢复详解：从mysqldump到二进制日志
总结来说，MySQL的备份和恢复机制涵盖了多种方法，包括SQL级别的mysqldump、物理级别的文件拷贝以及二进制日志的应用。选择合适的备份和恢复策略，结合定期备份、合理利用binlog以及选择适合的工具，可以有效地保护...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值