MySQL/MariaDB数据备份和数据恢复完整教程

类型：过滤器插件大小：7.6M语言：中文评分：6.6标签:立即下载MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区维护，由GPL授权。这个分支发展的原因之一是，在Oracle收购MySQL之后，存在关闭MySQL的潜在风险，所以社区采用分支的方式来规避这个风险。

数据对我们来说非常重要。我们怎样才能尽可能安全？当我们的数据丢失时，我们该怎么办？因此，数据备份对于我们的数据安全来说太重要了。

数据对我们来说既熟悉又常见。我们每天都会接触到各种各样的数据，这些数据记录了我们平时相关的业务信息，所以数据对我们来说非常重要。如果我们的数据丢失，我们会无法开展相关业务吗？这应该是个麻烦的问题。我们如何保护需要数据备份的数据安全？如何执行备份恢复：备份和恢复是工作环境中最重要的。为什么需要备份和恢复？1.灾难恢复通常会完成，例如自然灾害。2.例如，可以审核某个数据在过去是什么样子的。3.例如对于测试，一种新的业务架构数据存储方式能否支持业务访问备份的目的是为了恢复，如果备份数据在使用时无法恢复怎么办，那么就需要对备份数据的恢复进行测试。还要定期做检查。备份类型：备份时判断数据库服务器是否在线的方法有很多：冷备份：服务器应该是离线的，也就是说我们的读写操作无法进行。这是最安全也是最不可靠的备份方式。热备份：热备份，全局应用共享锁。只读、未写入的备份称为热备份。热备份，数据库不离线，InnoDB记录数据时会给数据一个序列号。因此，在备份期间，基于MVCC(多版本并发控制)机制，快照将自动加速，并且每次启动事务时都会创建当前集的快照。然后，基于MVCC的机制为它记录每个序列号。备份时，只备份序列号或序列号之前的数据，不会备份以后发生的数据。如果事务的隔离级别不是特别高，也不会影响事务的读写操作，而且这样备份的数据必须是同一个时间点的数据，所以通常是基于事务的存储引擎才能完成热备份。备份时按数据集分类：完全备份：备份整个库，而部分备份当前数据集的整个库的数据：部分备份：只备份某个表或某个表的部分数据，有时需要备份单个表。备份时按界面进行物理备份(直接备份数据文件或通过mysql服务器导出数据):直接复制(归档)数据文件的备份方式；跨平台能力不如逻辑备份。对于大型数据集，最好使用这个。逻辑备份：取出数据库，保存为文本文件；常用的工具是mysqldump，逻辑备份，不适用于大容量数据。恢复速度很慢，占用空间很大，这取决于备份时是备份整个数据还是只备份更改的数据：完全备份，与备份数据集中的完全备份概念相同，也备份整个库增量备份：增量备份，其中最后一次完全备份后的更改数据由增量备份来备份，例如周一备份一次，周二备份一次，周三备份一次，所以称为每天增量备份。比较节省空间。差异备份：以差异备份为例，周一做备份，周二周一周二做备份，周三周一周二周三做备份，称为差异备份。更容易恢复。

备份策略：需要考虑的问题1。选择备份方式，根据我们生产环境的需要选择哪种方式；2.备份时间，选择访问最少的时间做备份是合理的；3.考虑回收成本：回收时间；4.备份成本：考虑锁定时间、备份时间和备份负载；备份对象：我们需要备份什么进行备份？1.备份数据库中的数据是最重要的；2.MySQL配置文件，这也是我们的备份对象3。MySQL代码也需要备份：存储过程、存储函数、触发器4。与操作系统相关的配置文件，如crontab配置计划和相关脚本5。在主从复制场景中，与复制相关的信息也应该备份6。为了保证数据足够可靠，Binary日志文件还需要通过常用的备份工具进行备份：mysqldump:一种逻辑备份工具，是单线程备份工具，因此在某台服务器上只能启动一个CPU和一个线程进行备份，性能相对较差。InnoDB热备用，MyISAM热备用，Aria热备用，备份恢复过程缓慢；mysqldump:多线程mysqldump，可以同时在多个库或表上执行，提高性能；Mysqldump和MySQL dump都是逻辑备份工具。通常，很难实现差异备份或增量备份。只能进行完整备份，但可以进行部分备份。例如，只能备份一个表；基于冷备份时：cp，基于lvm-snapshot逻辑卷快照的备份接近热备用工具，因为要先请求全局锁，再创建快照，创建快照后释放全局锁；然后使用cp、tar等工具进行物理备份(因为复制了源数据文件)，所以数据备份和恢复速度快，很难实现增量备份，请求全局锁定需要一段时间，尤其是在繁忙的服务器上。SELECT子句INTO OUTFILE '/path/to/some file '；将选中的子句保存到某个文件是部分备份工具，不备份关系定义，只备份表中的数据，但也是逻辑备份工具，速度比mysqldump快，无法实现增量备份。LOAD ADTA INFILE '/path/from/some file '；指示从何处读取数据进行恢复；Innobase:提供商业备份工具Innobackup，可以实现InnoDB的热备份，支持增量备份；但是MyISAM不支持增量备份，只能实现完全备份，属于物理备份，速度更快。xtra backup:Percona组织提供的开源备份工具，物理备份速度快；Mysqlhostcopy:几乎冷待机，吹牛工具，不适用；Mysqldump:常用的备份工具也是逻辑备份工具，用于小数据备份，一般用5G以下的小数据进行备份；文本可用于二次加工；相当于MySQL的客户端工具使用的格式是：MySQL dump[选项][数据库名[TBL名.]]备份单个库时使用此工具：mysqldump[option] db_name。恢复时，如果目标库不存在，需要提前手动创建。

-all-databases:备份所有数据库- databasesdb1db2:备份多个由空格分隔的指定数据库-锁定所有表：备份前请求锁定所有表。一般只有MyISAM保暖，InnoDB和Aria也可以保暖。-events:backup event scheduler code-routines:backup存储过程和存储函数-触发器：backup触发器-flush-log:backup前，lock请求后，backup时日志滚动，手动滚动需要手动锁定应用程序-master-data=[0 | 1 | 2]复制时同步位置标记，0表示no 1 record carngemaster语句，2记录为changemaster语句-单事务：可以实现InnoDB存储引擎的热备份，启动单个大事情，基于MVCC(多版本并发控制)实现InnoDB存储引擎的热备份，它会自动锁定，并且做到# mysqldump-databaseshelodb-lock-all-tables/tmp/hello db . SQL:备份数据库并请求表锁，此时所有的备份写操作都会被阻止，这并不理想。# MySQL dump-databaseshello db-lock-all-tables-flush-logs/tmp/hello db . SQL # MySQL dump-databaseshello db- single-transaction-flush-logs/tmp/hello db . SQL:在保证该库下所有表的存储引擎均为InnoDB的前提下，使用-single-transaction进行热备。看不同时间段的二进制日志，重定向到某个文件：# mysqlbinlog-start-porition=367-stop-position=669 master-bin . 00005/tmp/hello db . Inc . SQL定义一个起点和一个终点，然后将二进制文件重定向到某个文件进行恢复；

使用mysqldump进行备份时：请求锁定：-锁定所有表；innodb热备用使用单事务；滚动日志：-刷新日志-选择要备份的库：-数据库指定二进制日志文件及其位置：-主数据=2=2注意：在备份之前，有必要锁定它。恢复时，建议关闭二进制日志并关闭其他用户的连接。备份策略：mysqldump二进制日志文件：Restore:将每个二进制日志文件中的事件完整备份到现在，并还原过程中发生的事件。因此，二进制日志文件应该在恢复期间暂时关闭：

Maria db[(hello db)]setsessionsql _ log _ bin=0:暂时关闭二进制日志文件Maria db[(hello db)]source/tmp/hello db . SQL；直接从数据库命令行读取备份文件

MySQL配置文件和与MySQL相关的OS配置文件每次修改后都要直接备份。lvm-快照：基于LVM快照1的备份。基于快照的备份具有先进性，事务日志和数据文件必须在同一个卷上；2.在创建快照卷之前，请求MySQL的全局锁，并在创建快照后释放锁；3.完成此操作后，请求全局锁进行日志滚动；标记、时间记录、二进制日志文件和位置标记(需要手动完成)；注：1。将数据和备份放在不同的磁盘设备上，最好将备份存储在不同的机器或不同的地方；2.备份数据应定期恢复和测试；3.每次灾难恢复后立即进行完整备份。4.为不同规模或级别的数据定制备份策略；5.二进制日志应该与数据文件在不同的磁盘上，并且应该定期备份二进制日志文件；

从备份中恢复时要遵循的步骤：1。停止MySQL服务器；2.记录服务器的配置和文件权限；3.将数据从备份移动到MySQL数据目录，其执行模式依赖于工具；4.更改配置和文件权限；5.用限制访问模块重启服务器，mysqld的- skip-network选项可以跳过网络功能；方法：编辑my.cnf配置文件，添加如下项skip-networking socket=/tmp/MySQL-recovery . sock 6，加载逻辑备份(如果有)，然后检查并重放二进制日志7。检查恢复的数据；8.再次以完全访问模式重新启动服务器；

使用mysqldump实现备份，二进制日志恢复数据。这里我们以当前系统上的数据库hellodb为例：

步骤1:首先对hellodb数据库进行完整备份。当然，如果数据库的数据量较大，比如大于10G，不建议使用mysqldump作为备份工具。在这里，我们只想解释一下这个问题：

#-u指定用户，-p指用户密码，- databases指定要备份的数据库，-lock-all-tables指在备份期间请求表锁定。-flush-logs rolling log[root @ node 0 ~]# MySQL dump-urot-p Linux-databaseshellodb-lock-all-tables-flush-logs-master-data=2/root/hellodb . SQL[root @ node 0 ~]# ll-hhellodb . SQL-rw-r-r-1 root root 7.8 kmay 214336026 hellodb . SQL[root @ node 0 ~]#

第二步：修改或创建数据库hellodb中的一些表或数据，使之前备份的数据与现有数据库中的数据有所区别，然后通过二进制日志恢复数据：

MariaDB[hello db]createTableNewTable；错误1113(42000): tableemusthaftelast1 columnmaridb[hello db]CREATETABLEnewtable(NameCHAR(20))；QueryOK，0 rowsaffected(1.11 sec)Mariadb[hello db]INSERTINTOnewtablevalues(' Tom ')，(' Jerry '，(' Lucy ')；查询，3个未完成(0.13秒)的记录33603重复33600警告：0

步骤3:删除这个hellodb数据库：

MariaDB[hello db]createTableNewTable；错误1113(42000): tableemusthaftelast1 columnmaridb[hello db]CREATETABLEnewtable(NameCHAR(20))；QueryOK，0 rowsaffected(1.11 sec)Mariadb[hello db]INSERTINTOnewtablevalues(' Tom ')，(' Jerry '，(' Lucy ')；查询，3个未完成(0.13秒)的记录33603重复33600警告：0

第四步：检查我们的二进制日志文件，保存后来修改创建的记录二进制日志的信息，这样我们就可以恢复新添加的数据内容。我们在备份过程中进行了日志滚动，因此可以检查最后的日志信息；

[root @ node 0 ~]# CD/my data/data/[root @ node 0 data]# MySQL binlog-start-position=403-stop-position=663 MySQL-bin . 000004/tmp/hello db . binlog . SQL

第五步：恢复数据，进入mysql命令行模式，关闭二进制日志，恢复数据时不需要将恢复信息记录到二进制日志中；

#关闭当前调用的二进制日志文件Maria db[(none)]setsessionsql _ log _ bin=0；MariaDB[(无)]/source/root/hello db . sqlmariadb[hello db]SHOWMASTESTATUS；检查二进制文件是否已更改两次；----|文件|位置| Binlog _ Do _ DB | Binlog _ Ignore _ DB |----| MySQL-bin . 000004 | 815 | | |-----Mariadb[hello DB]SHOWTABLES；MariaDB[hello db]source/tmp/hello db . binlog . sqlmariadb[hello db]SELECT * from new table；-| Name |-| Tom | | Jerry | | Lucy |-3rowsinset(0.00秒)Mariadb[hello db]SETsessionsql _ log _ bin=1；将二进制文件联机

好的，这是基于mysqldump二进制日志完成的完整数据恢复。二进制日志可以帮助我们恢复可能没有紧急备份的数据，这对我们的数据安全非常重要。

Lvm-snapshot:备份基于逻辑卷快照。这里需要注意的是，快照不是备份，最多是通过快照的方式进行数据备份，快照本身不是备份。

拍摄快照后，一旦需要修改源卷的数据，就需要将源卷上的数据拷贝到快照卷。将来通过快照卷进行访问时，这些修改的数据将通过快照存储进行访问，而那些未更改的数据也将通过源卷进行访问。快照仅存储已更改的数据；1.它只是作为源卷的访问路径；2.刚创建快照卷时，快照卷中没有数据，只有所有数据指向源卷的数据，所以访问的数据来自源卷；3.一旦源卷中的数据需要修改，修改前需要将数据的副本复制到快照卷中，这样以后通过快照卷访问的数据将全部来自快照卷和源卷；修改后的数据来自快照卷，未修改的数据来自源卷。快照卷只能为我们提供时间一致文件的访问通道；

朱庇特朱庇特朱庇特：1亚力坤阿力坤阿力坤阿力坤阿力坤阿力坤阿力坤阿力坤阿力坤阿力坤阿力坤阿力坤阿力坤阿力坤阿力坤阿力坤阿力坤，我的意思是，我的意思是，我的意思是，我的意思是，我的意思是，我的意思是，我的意思是，我的意思是，我的意思是，我的意思是，我的意思是，我的意思是，我的意思是，我的意思是，我的意思是，我的意思是，我的意思是，我的意思是，我的意思是，我的意思是；2 ._唉呀呀呀呀呀呀呀呀呀呀呀呀呀呀呀呀呀呀呀呀呀呀呀呀，绿筠小姐MySQL(MySQL)阿字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯字伯，吴登盛朱登盛吴登盛吴登盛；{ 1 }陈列品陈列品陈列品陈列品陈列品陈列品，云大哥；杨俊钦和杨俊钦，伊切主数据(主数据):

:你是说，你是说，你是说，你是说，你是说，你是说，你是说，你是说，我是说，你是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是不是…,装载-什么，喂，喂，喂，喂，喂，喂；

[root @ node 0 ~]# mount/dev/mapper/VG-rootn/type ext 4(rw)procon/proctype proc(rw)sysfson/systype sys pts(rw，gid=5，mode=620)tmp fson/dev/shmtypetmpfs(rw)/dev/sda 1 on/boottypetext 4(rw)/dev/mapper/VG 0-usr on/usr type text 4(rw)/dev/mapper/VG 0-varon

魏冄：诶哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟，贺盛德贺盛德贺盛德贺盛德贺盛德，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不，不；

mariadb[(无)]flushtwithread lock；#诶哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟QueryOK，0 rowsaffefted(0.01秒)mariadb[(无)]flush logs；#吴经熊535QueryOK，0 rowsaffefted(0.02秒)mariadb[(无)]显示主状态；#韩素宁？韩素宁位置魏冄- |档案|位置| binlog _ do _ db | binlog _ ignore _ db |-| MySQL-bin。000002 | 365 |-1行插图(0.04秒)MariaDB[(无)]

:哼哼哼哼哼哼哼，我的天啊，我的天啊！我的天啊！我的天啊！我的天啊！我的天啊！我的天啊！我的天啊！我的天啊！我的天啊！我的天啊！我的天啊！我的天啊！我的天啊！我的天啊！我的天啊！我的天啊！我的天啊，你是说.

[root @ node 0 ~]# mkdirbackup[root @ node 0 ~]# MySQL-e ' showmasterstatus '-| file | position | binlog _ do _ db | binlog _ ignore _ db |-MySQL-bin。000002 | 365 |--[root @ node 0 ~]# MySQL-e ' showmasterstatus '/root/backup/binlog。pos[root @ node 0 ~]# lvcreate-l100 m-s-nmydata-snap pr/dev/my data/MySQL datacalvo volume ' my data '

阿积：朱庇特朱庇特MySQL(MySQL)范仲淹把你的手从我的手里拿开，我要把你的手从我的手里拿开，我要把你的手从我的手里拿开，我要把你的手从我的手里拿开，我要把你的手从我的手里拿开：

马里亚布[(无)]不可卸载；QueryOK，0 rowsaffefted(0.01秒)MariaDB[(无)]

魏冄：亚力坤阿力伯阿力伯阿力伯阿力伯阿力伯阿力伯阿力伯阿力伯阿力伯阿力伯阿力伯阿力伯阿力伯阿力伯阿力伯，你知道吗，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，我是说，范仲淹！范仲淹！范仲淹！范仲淹！范仲淹，喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂，是卓云：

[root @ node 0 ~]# mount/dev/my data/my data-snap/data-gold[root @ node 0 ~]# CD/data/[root @ node 0 data]# lsaria _ log。00000001 ibdata 1多主机。信息ysql-bin。000002节点0。tanxw。com。pidgia _ log _ control IB _ log文件0 mysqlmysql-bin。索引hello bib _ log文件1 MySQL-bin。000001节点0。tanxw。com。err[根@节点0数据

魏冄：贺盛瑞阿瑞克斯阿瑞克斯阿瑞克斯阿瑞克斯阿瑞克斯，嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨嗨，喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂，喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂喂：

[root @ node 0 data]# CP-a/data//root/backup/data-2014-05-02 #-a存档和复制数据[root @ node 0 data]# CD/root/backup/data-2014-05-02[root @ node 0 data-2014]LSA RIA _ log . 00000001 bdata 1 multi-master . infomysql-bin . 000002 node 1 . tanxw.com . pidaria _ log _ controlib _ master . infomysql-bin . 00000002 node 1 . tanxw.com[y/n]:y逻辑卷“mydata-snap”已成功删除[root@node0~]#

这里需要注意的是，如果在备份的瞬间有数据修改，那么我们备份的数据不会保存快照后修改的数据；

结束：

每个人都知道数据备份和恢复的重要性。当然，备份也是保护数据安全最重要的手段之一。因此，熟练掌握数据备份和恢复对我们来说非常重要。如果有什么不对的地方，希望上帝能给我们更多的建议。