免费资源部落

这篇文章主要讲解了“MySQL怎么全量、增量备份与恢复数据”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL怎么全量、增量备份与恢复数据”吧！

数据备份的重要性

1、在生产环境中，数据的安全性是至关重要的，任何数据的丢失都可能产生严重的后果

2、造成数据丢失的原因

程序错误人为错误计算机失败磁盘失败灾难和偷窃

数据库备份的分类

从物理与逻辑的角度，备份可分为：

1、物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份物理备份又可以分为脱机备份(冷备份)和联机备份(热备份)：冷备份:是在关闭数据库的时候进行的热备份:数据库处于运行状态，这种备份方法依赖于数据库的日志文件2、逻辑备份:对数据库逻辑组件(如表等数据库对象)的备份从数据库的备份策略角度， 备份可分为：完全备份:每次对数据进行完整的备份差异备份:备份那些自从上次完全备份之后被修改过的文件增量备份:只有那些在.上次完全备份或者增量备份后被修改的文件才会被备份 注意：差异与增量相辅相成

MySQL完全备份1、完全备份是对整个数据库的备份、数据库结构和文件结构的备份2、完全备份保存的是备份完成时刻的数据库3、完全备份是增量备份的基础（1）完全备份的优点●备份与恢复操作简单方便（2）完全备份的缺点●数据存在大量的重复●占用大量的备份空间●备份与恢复时间长mysqldump备份库1、MySQL数据库的备份可以采用用多种方式●直接打包数据库文件夹，如/usr/local/mysql/data●使用专用备份工具mysqldump2、mysqldump命令●MySQL自带的备份工具，相当方便对MySQL进行备份●通过该命令工具可以将指定的库、表或全部的库导出为SQL脚本，在需要恢复时可进行数据恢复3、mysqldump命令对单个库进行完全备份mysqldump-u用户名-p[密码][选项][数据库名]>/备份路径/备份文件名单库备份例子mysqldump-uroot-pauth>/backup/auth.sqlmysqldump-uroot-pmysql>/bakcup/mysql.sql4、mysqldump命令对多个库进行完全备份mysqldump-u用户名-p[密码][选项]--databases库名1[库名2]...>/备份路径/备份文件名多库备份例子mysqldump-uroot-p--databasesautthmysql>/backup/databases-auth-mysql.sql5、对所有库进行完全备份mysqldump-u用户名-p[密码][选项]--all-databases>/备份路径/备份文件名所有库备份例子mysqldump-uroot-p--opt--all-databases>/backup/all-data.sqlmysqldump备份表1、在实际生产环境中，存在对某个特定表的维护操作，此时mysqldump同样发挥重大作用2、使用mysqldump备份表的操作mysqldump-u用户名-p[密码][选项]数据库名表名>/备份路径/备份文件名备份表的例子mysqldump-uroot-pmysqluser>/backup/mysql-user.sql3、使用mysqldump备份表结构的操作mysqldump-u用户名-p[密码][选项]-d数据库名表名>/备份路径/备份文件名备份表结构的例子mysqldump-uroot-pmysql-duser>/backup/mysql-user.sql恢复数据库1、使用mysqldump命令导出的SQL备份脚本，在进行数据恢复时可使用以下方法导入●source命令数据库模式中运行●mysq|命令Linux模式中运行2、使用source恢复数据库的步骤●登录到MySQL数据库●执行source备份sq|脚本的路径（绝对路径）source恢复例子MySQL[(none)]>source/backup/all-data.sql3、使用mysq|命令恢复数据mysql-u用户名-p[密码]<库备份脚本的路径mysql命令恢复例子mysql-uroot-p</backup/all-data.sql恢复表的操作1、恢复表时同样可以使用source或者mysql命令进行2、source恢复表的操作与恢复库的操作相同3、当备份文件中只包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在mysql-u用户名-p[密码]<表备份脚本的路径mysql-uroot-pmysql</backup/mysql-user.sql4、在生产环境中，可以使用shell脚本自动实现定期备份MySQL备份思路1、定期实施备份，制定备份计划或者策略，并严格遵守2、除了进行完全备份，开启MySQL服务器的日志功能是很重要的●完全备份加上日志，可以对MySQL进行最大化还原MySQL-bin：MySQL数据库的二进制日志,用于记录用户对数据库操作的SQL语句((除了数据查询语句)信息。可以使用mysqlbin命令查看二进制日志的内容3、使用统一的和易理解的备份文件名称●不要使用backup1、backup2等这样没有意义的名字●推荐使用库名或者表名加上时间的命名规则MySQL增量备份1、使用mysqldump进行完全备份的存在的问题●备份数据中有重复数据●备份时间与恢复时间长2、增量备份就是备份自上一次备份之后增加或变化的文件或者内容3、增量备份的特点●没有重复数据，备份量不大，时间短●恢复麻烦：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复4、MySQL没有提供直接的增量备份方法5、可以通过MySQL提供的二进制日志(binarylogs)间接实现增量备份6、MySQL二进制日志对备份的意义●二进制日志保存了所有更新或者可能更新数据库的操作●二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flushlogs命令后重新创建新的日志文件●只需定时执行flushlogs方法重新创建新的日志，生成二进制文件序列，并及时把这些旧的日志保存到安全的地方就完成了一个时间段的增量备份7、一般恢复添加数据——进行完全备份——录入新的数据——进行增量备份——模拟故障——恢复操作8、基于位置回复就是将某个起始时间的二进制日志导入数据库中，从而跳过某个发生错误的时间点实现数据的恢复9、基于时间点恢复使用基于时间点的恢复，可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作，所以我们需要一种更为精确的恢复方式10、增量恢复的方法（1）一般恢复mysqlbinlog[--no-defaults]增量备份文件|mysql-u用户名-p（2）基于位置的恢复●恢复数据到指定位置mysqlbinlog--stop-position=操作'id'1进制日志|mysql-u用户名-p密码●从指定的位置开始恢复数据mysqlbinlog--start-position=操作'id'二进制日志|mysql-u用户名-p密码（3）基于时间点的恢复●从日志开头截止到某个时间点的恢复mysqlbinlog[--no-defaults]--stop-datetime='年-月-日小时:分钟:秒'二进制日志|mysql-u用户名-p密码●从某个时间点到日志结尾的恢复mysqlbinlog[--nodefaults]--start-datetime='年-月-日小时:分钟:秒'二进制日志|mysql-u用户名-p密码●从某个时间点到某个时间点的恢复mysqlbinlog[--nodefaults]--start-datetime='年-月-日小时:分钟:秒'--stop-datetime='年-月-日小时:分钟:秒'二进制日志|mysql-u用户名-p密码查看二进制日志文件（解码）mysqlbinlog--no-defaults--base64-output=decode-rows-Vmysql-bin.000002>/opt/bak.txt全量备份与恢复实操

1，进入数据库，创建表，插入表数据

[root@master2~]#mysql-uroot-p##进入数据库Enterpassword:mysql>createdatabaseschool;##创建数据库QueryOK,1rowaffected(0.01sec)mysql>useschool;##使用数据库Databasechangedmysql>createtableinfo(##创建表->idint(3)notnullprimarykeyauto_increment,->namevarchar(10)notnull,->scoredecimal(4,1)notnull);QueryOK,0rowsaffected(0.02sec)mysql>descinfo;##查看表结构+-------+--------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+-------+--------------+------+-----+---------+----------------+|id|int(3)|NO|PRI|NULL|auto_increment||name|varchar(10)|NO||NULL|||score|decimal(4,1)|NO||NULL||+-------+--------------+------+-----+---------+----------------+3rowsinset(0.00sec)mysql>insertintoinfo(name,score)values('stu01',88),('stu02',77);##插入表数据QueryOK,2rowsaffected(0.02sec)Records:2Duplicates:0Warnings:0mysql>select*frominfo;##查看表内容+----+-------+-------+|id|name|score|+----+-------+-------+|1|stu01|88.0||2|stu02|77.0|+----+-------+-------+2rowsinset(0.01sec)mysql>select*frominfolimit1;##只显示表中的前1行+----+-------+-------+|id|name|score|+----+-------+-------+|1|stu01|88.0|+----+-------+-------+1rowinset(0.00sec)

2，对数据库进行物理的完全备份

[root@master2~]#cd/usr/local/mysql/data/##切换到数据库的数据目录下[root@master2data]#lsauto.cnfibdata1ib_logfile1mysqlschooltestib_buffer_poolib_logfile0ibtmp1performance_schemasys[root@master2data]#cdschool/[root@master2school]#ls##数据中的文件db.optinfo.frminfo.ibd[root@master2school]#cd..[root@master2data]#tarJcvf/opt/mysql-$(date+%F).tar.xz/usr/local/mysql/data/##用xz格式压缩[root@master2data]#cd/opt/[root@master2opt]#lsmysql-2019-11-26.tar.xzmysql-5.7.20rh

3，对单个数据库进行逻辑上的备份

[root@master2opt]#mysqldump-uroot-pschool>/opt/school.sql##逻辑备份单个数据库Enterpassword:[root@master2opt]#lsmysql-2019-11-26.tar.xzmysql-5.7.20rhschool.sql[root@master2opt]#vimschool.sql##查看备份数据库脚本...CREATETABLE`info`(`id`int(3)NOTNULLAUTO_INCREMENT,`name`varchar(10)NOTNULL,`score`decimal(4,1)NOTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=3DEFAULTCHARSET=utf8;...LOCKTABLES`info`WRITE;/*!40000ALTERTABLE`info`DISABLEKEYS*/;INSERTINTO`info`VALUES(1,'stu01',88.0),(2,'stu02',77.0);

4，对多个数据库进行备份

[root@master2opt]#mysqldump-uroot-p--databasesschoolmysql>/opt/db_school_mysql.sql##备份多个数据库Enterpassword:[root@master2opt]#lsdb_school_mysql.sqlmysql-2019-11-26.tar.xzmysql-5.7.20rhschool.sql

5，对数据库进行完全备份

[root@master2opt]#mysqldump-uroot-p--opt--all-databases>/opt/all.sql##完全备份Enterpassword:[root@master2opt]#lsall.sqlmysql-2019-11-26.tar.xzrhdb_school_mysql.sqlmysql-5.7.20school.sql

6，对数据库中的表进行备份

[root@master2opt]#mysqldump-uroot-pschoolinfo>/opt/school_info.sql##对数据库中的表进行备份Enterpassword:[root@master2opt]#lsall.sqlmysql-2019-11-26.tar.xzrhschool.sqldb_school_mysql.sqlmysql-5.7.20school_info.sql

7，对数据库中的表结构进行备份

[root@master2opt]#mysqldump-uroot-p-dschoolinfo>/opt/school_info_desc.sql##对表结构进行备份Enterpassword:[root@master2opt]#lsall.sqlmysql-5.7.20school_info.sqldb_school_mysql.sqlrhschool.sqlmysql-2019-11-26.tar.xzschool_info_desc.sql

8，基于脚本恢复数据库

[root@master2opt]#mysql-uroot-p##进入数据库Enterpassword:mysql>showdatabases;##查看数据库+--------------------+|Database|+--------------------+|information_schema||mysql||performance_schema||school||sys||test|+--------------------+6rowsinset(0.00sec)mysql>useschool;##使用数据库Databasechangedmysql>showtables;##查看表+------------------+|Tables_in_school|+------------------+|info|+------------------+1rowinset(0.00sec)mysql>droptableinfo;##删除表QueryOK,0rowsaffected(0.01sec)mysql>showtables;###查看表Emptyset(0.00sec)mysql>source/opt/school.sql##恢复数据库脚本文件mysql>showtables;##查看表+------------------+|Tables_in_school|+------------------+|info|+------------------+1rowinset(0.00sec)

9，基于外部MySQL命令恢复数据库

mysql>droptableinfo;##删除表QueryOK,0rowsaffected(0.01sec)mysql>showtables;##查看表Emptyset(0.00sec)mysql>quit##退出Bye[root@master2opt]#mysql-uroot-p123123school</opt/school.sql##利用mysql命令进行恢复mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@master2opt]#mysql-uroot-p123123##进入数据库mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.mysql>useschool;##使用数据库ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>showtables;##查看表+------------------+|Tables_in_school|+------------------+|info|+------------------+1rowinset(0.00sec)MySQL增量备份及恢复实操

1，开启二进制日志文件

[root@master2opt]#vim/etc/my.cnf##开启二进制日志文件[mysqld]user=mysqlbasedir=/usr/local/mysqldatadir=/usr/local/mysql/dataport=3306character_set_server=utf8pid-file=/usr/local/mysql/mysql.pidsocket=/usr/local/mysql/mysql.socklog-bin=mysql-bin##开启二进制日志文件server-id=1[root@master2opt]#systemctlrestartmysqld.service##重启mysql服务[root@master2opt]#cd/usr/local/mysql/data/##切换到mysql站点[root@master2data]#ls##查看二进制日志文件auto.cnfib_logfile0mysqlperformance_schematestib_buffer_poolib_logfile1mysql-bin.000001schoolibdata1ibtmp1mysql-bin.indexsys

2，进行完全备份

[root@master2data]#mysqldump-uroot-p123123school>/opt/school.sql##一次完全备份mysqldump:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@master2data]#lsauto.cnfib_logfile0mysqlperformance_schematestib_buffer_poolib_logfile1mysql-bin.000001schoolibdata1ibtmp1mysql-bin.indexsys[root@master2data]#mysqladmin-uroot-p123123flush-logs##刷新二进制日志文件mysqladmin:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@master2data]#ls##生成新的二进制日志文件，接下来的操作会保存在mysql-bin.000002中auto.cnfib_logfile0mysqlmysql-bin.indexsysib_buffer_poolib_logfile1mysql-bin.000001performance_schematestibdata1ibtmp1mysql-bin.000002school

3，进入数据库，模拟误操作

[root@master2data]#mysql-uroot-p123123##进入数据库mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.mysql>useschool;##使用数据库ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>select*frominfo;##查看表+----+------+-------+|id|name|score|+----+------+-------+|1|st01|88.0||2|st02|77.0|+----+------+-------+2rowsinset(0.00sec)mysql>insertintoinfo(name,score)values('by01',66);##正确操作QueryOK,1rowaffected(0.00sec)mysql>select*frominfo;+----+------+-------+|id|name|score|+----+------+-------+|1|st01|88.0||2|st02|77.0||3|by01|66.0|+----+------+-------+3rowsinset(0.00sec)mysql>deletefrominfowherename='st01';##错误操作QueryOK,1rowaffected(0.00sec)mysql>insertintoinfo(name,score)values('by02',99);##正确操作QueryOK,1rowaffected(0.00sec)mysql>select*frominfo;+----+------+-------+|id|name|score|+----+------+-------+|2|st02|77.0||3|by01|66.0||4|by02|99.0|+----+------+-------+3rowsinset(0.00sec)[root@master2data]#mysqladmin-uroot-p123123flush-logs##刷新二进制日志文件mysqladmin:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@master2data]#mysqlbinlog--no-defaults--base64-output=decode-rows-vmysql-bin.000002>/opt/bak.txt##用64位解码器查看二进制日志文件，并生成一个文件[root@master2data]#cd/opt/[root@master2opt]#lsbak.txtmysql-5.7.20rhschool.sql[root@master2opt]#vimbak.txt##查看二进制日志文件#at1084#19112720:14:01serverid1end_log_pos1132CRC320xdcc90eb5Write_rows:tableid221flags:STMT_END_F###INSERTINTO`school`.`info`##第一次正确操作的时间和位置###SET###@1=3###@2='by01'###@3=66.0...#at1302##停止位置点#19112720:14:46serverid1end_log_pos1357CRC320x6648509aTable_map:`school`.`info`mappedtonumber221#at1357#19112720:14:46serverid1end_log_pos1405CRC320x1eeb752bDelete_rows:tableid221flags:STMT_END_F###DELETEFROM`school`.`info`##第二次执行错误操作的时间和位置19112720:14:46###WHERE###@1=1###@2='st01'###@3=88.0#at1405##开始位置点#19112720:14:46serverid1end_log_pos1436CRC320xf1c8d903Xid=54...#at1630#19112720:15:16serverid1end_log_pos1678CRC320x08d9b0f4Write_rows:tableid221flags:STMT_END_F###INSERTINTO`school`.`info`##第二次正确操作的时间和位置19112720:15:16###SET###@1=4###@2='by02'###@3=99.0

4，基于时间点进行断点恢复

[root@master2opt]#mysql-uroot-p123123##进入数据库mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.mysql>useschool;##使用数据库ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>droptableinfo;##删除数据库QueryOK,0rowsaffected(0.01sec)mysql>select*frominfo;##查看表ERROR1146(42S02):Table'school.info'doesn'texistmysql>source/opt/school.sql##恢复完全备份数据库脚本...mysql>showtables;##查看表+------------------+|Tables_in_school|+------------------+|info|+------------------+1rowinset(0.00sec)mysql>select*frominfo;##查看表数据+----+------+-------+|id|name|score|+----+------+-------+|1|st01|88.0||2|st02|77.0|+----+------+-------+2rowsinset(0.00sec)[root@master2opt]#mysqlbinlog--no-defaults--stop-datetime='2019-11-2720:14:46'/usr/local/mysql/data/mysql-bin.000002|mysql-uroot-p123123##恢复bin.000002中前一个正确的执行语句（从第二个错误语句时间点停止）mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@master2opt]#mysql-uroot-p123123##进入数据库mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.mysql>useschool;##使用数据库ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>select*frominfo;##查看表数据，恢复了第一次正确操作+----+------+-------+|id|name|score|+----+------+-------+|1|st01|88.0||2|st02|77.0||3|by01|66.0|+----+------+-------+3rowsinset(0.00sec)[root@master2opt]#mysqlbinlog--no-defaults--start-datetime='2019-11-2720:15:16'/usr/local/mysql/data/mysql-bin.000002|mysql-uroot-p123123##跳过错误节点，恢复最后一个正确的操作（从最后一个正确的操作时间点开始）mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.[root@master2opt]#mysql-uroot-p123123##进入数据库mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.mysql>useschool;##使用数据库ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>select*frominfo;##查看表数据，恢复了第二次正确操作，跳过了错误的操作+----+------+-------+|id|name|score|+----+------+-------+|1|st01|88.0||2|st02|77.0||3|by01|66.0||4|by02|99.0|+----+------+-------+4rowsinset(0.00sec)

5，基于位置点进行断点恢复

mysql>deletefrominfowherename='by01';##为实验方便直接删除QueryOK,1rowaffected(0.01sec)mysql>deletefrominfowherename='by02';##删除QueryOK,1rowaffected(0.00sec)mysql>select*frominfo;##完全备份的初始状态+----+------+-------+|id|name|score|+----+------+-------+|1|st01|88.0||2|st02|77.0|+----+------+-------+2rowsinset(0.00sec)mysql>quitBye[root@master2opt]#mysqlbinlog--no-defaults--stop-position='1302'/usr/local/mysql/data/mysql-bin.000002|mysql-uroot-p123123##跳过错误操作的位置点从上一个位置点开始[root@master2opt]#mysql-uroot-p123123##进入数据库mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.mysql>useschool;##使用数据库ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>select*frominfo;##查看表数据，恢复了第一次正确的操作+----+------+-------+|id|name|score|+----+------+-------+|1|st01|88.0||2|st02|77.0||3|by01|66.0|+----+------+-------+3rowsinset(0.00sec)mysql>quitBye[root@master2opt]#mysqlbinlog--no-defaults--start-position='1405'/usr/local/mysql/data/mysql-bin.000002|mysql-uroot-p123123##从错误的位置后一个位置点开始，跳过错误操作的位置点[root@master2opt]#mysql-uroot-p123123##进入数据库mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.mysql>useschool;##使用数据库ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>select*frominfo;##查看表数据，跳过错误操作，恢复第二次正确操作数据+----+------+-------+|id|name|score|+----+------+-------+|1|st01|88.0||2|st02|77.0||3|by01|66.0||4|by02|99.0|+----+------+-------+4rowsinset(0.00sec)

6，对于增量备份全部恢复

[root@master2opt]#mysqlbinlog--no-defaults/usr/local/mysql/data/mysql-bin.000002|mysql-uroot-p123123##全部增量恢复

感谢各位的阅读，以上就是“MySQL怎么全量、增量备份与恢复数据”的内容了，经过本文的学习后，相信大家对MySQL怎么全量、增量备份与恢复数据这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！