mysql备份方式、策略、恢复等简析

下文内容主要给大家带来mysql备份方式、策略、恢复等简析，这里所讲到的知识，与书籍略有不同，都是专业技术人员在与用户接触过程中，总结出来的，具有一定的经验分享价值，希望给广大读者带来帮助。

rmysql的备份：

误操作、mysql崩溃、******、软件故障、硬件故障、升级数据库，测试等都会造成mysql数据的损坏，这时如果有备份的话还好，没备份的话就尴尬了。

备份类型：

完全备份：备份整个数据库

部分备份：只备份数据库中的几张表或库

增量备份：相对于上一次完全备份或增量备份，只备份变化的数据集 （可以是二进制日志）

差异备份：相对于上一次完全备份，仅备份变化的数据集

备份方式：

物理备份：直接复制数据文件进行备份：速度快

缺点：当在备份的过程中有用户通过应用程序访问更新数据，这样就无法备份当时的数据，如果数据库表在文件系统备份过程中被修改，进入备份的表文件主语不一致的状态，而对以后的恢复表将失去意义，备份过的数据集还原时得跟备份之前数据库用到的存储引擎一致。

逻辑备份：从数据库中"导出"数据另存而进行的备份，速度比较慢

缺点：备份的速度比较慢。如果是数据量很多的时候。就很耗时间。如果数据库云服务器处在提供给用户服务状态，在这段长时间操作过程中，意味着要锁定表(一般是读锁定，只能读不能写入数据)。那么服务就会影响的，备份过的数据集还原时无需担心存储引擎的问题。

备份策略:

冷备份： 备份过程中读写操作均不可执行，好处是能保证数据的完整性，不会出现事务未提交的请求，坏处是需要mysql停止工作

热备份： 备份过程中读写操作均可执行，好处是不需要停止mysql

温备份： 备份过程中读操作可执行，写操作不可执行。

备份时需要考虑的问题：

备份时锁表需要多久、备份需要多长、备份时产生多大的负载、恢复时需要多长时间

备份的方案：

对于数据集是完全备份加增量备份，还是完全备份加差异备份

备份的方式：物理备份还是逻辑备份

备份时的策略：选择冷备份、温备份、还是热备份

备份的工具：

mysqldump：mysql官方自己提供的工具，是一款逻辑备份工具，适用于所有存储引擎，对MyISAM引擎支持温备，不支持热备，支持完全备份，部分备份，对InnoDB存储引擎是支持热备的

cp，tar等复制归档工具：物理备份工具，对所有引擎都支持备份

lvm2的快照：几乎热备，需要记住与文件系统管理工具，cp、mv等

mysqlhotcopy：冷备工具

xtarbackup：percona提供的快速备份工具，可以热备、温备

一、mysqldump实现数据备份和还原

mysqldump是客户端命令，通过mysql协议来连接msql数据库实现备份， 是采用SQL级别的备份机制，它将数据表导成 SQL 脚本文件

 mysqldump [option] [db_name [tbl_name..]]    ]# mysqldump [options] db_name [tbl_name ...]    //备份单个表，还原时需要手动创建数据库 ]# mysqldump [options] --databases db_name . //备份指定数据库，还原无需创建数据库        ]# mysqldump [options] --all-databases       //备份所有数据库，还原无需创建数据库  [options] --event -E：备份指定数据相关的所有事件  --default-character-set=charset：指定导出数据时采用何种字符集，如果数据表不是采用默认的 latin1 字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题 -R，--routines：备份指定数据库相关的所有存储过程和存储函数 --triggers：备份表相关的触发器 --skip-triggers：跳过备份触发器 --lock-all-tables：锁定所有库的所有表 --lock-tables：锁定指定数据库的所有表 --no-create-info，-t：只导出数据，而不添加 CREATE TABLE 语句 --no-data，-d：不导出任何数据，只导出数据库表结构。 --single-transaction：该选项在导出数据之前提交一个 BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 InnoDB 和 BDB。  注意：本选项和 --lock-tables 选项是互斥的，因为 LOCK TABLES 会使任何挂起的事务隐含提交 --master-data[=value]：记录二进制日志和文件的位置    0：不启用    1、记录为CHANGE MASTER TO语句，此语句不被注释    2、记录为注释的CHANGE MASTER TO语句 --flush-logs: 对二进制日志进行日志滚动

示例：完全备份数据库，然后恢复

]# mysqldump -uadmin -padmin --databases hellodb -R --triggers --master-data=2 >/backup/mysqlback.sql]# service mariadb stopRedirecting to /bin/systemctl stop  mariadb.service]# rm -rf /var/lib/mysql/*                             //删除mysql数据]# service mariadb start                                    Redirecting to /bin/systemctl start  mariadb.service    ]# mysql  show databases;+--------------------+| Database           |+--------------------+| information_schema || hellodb            |                           //直接还原回来| mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec)还原时还有一种方式：MariaDB [(none)]> source /backup/mysqlback.sql       //两种还原方式都是mysql客户端提供的

备份完后，在下次备份之前数据库崩溃需要还原，那么中间产生数据需要通过二进制日志根据时间点还原。

二进制日志： 记录导致数据改变或潜在导致数据改变的SQL语句。

]# mysqldump -uroot --databases hellodb -R --triggers --master-data=2 >/backup/mysqlbackv2.sqlMariaDB [hellodb]> insert into students (Name,Age) values ("Hello",20);Query OK, 1 row affected (0.01 sec)备份完后又做了写操作，这时数据发生了改变，需要通过二进制日志进行重放]# less /backup/mysqlbackv2.sql        -- Host: localhost    Database: hellodb-- -------------------------------------------------------- Server version       5.5.44-MariaDB-log/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Position to start replication or point-in-time recovery from---- CHANGE MASTER TO MASTER_LOG_FILE='master.000003', MASTER_LOG_POS=7925;    //显示备份时二进制的文件及二进制事件开始的位置]# mysqlbinlog --start-position=245 /root/master.000003 >/backup/mysqlbin.sql                       //把记录的之后事件的二进制日志导成sql语句                       ]# mysql  show databases;+--------------------+| Database           |+--------------------+| information_schema || hellodb            |                   //数据库没问题| mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec)   MariaDB [hellodb]> select * from students;|    26 | Hello         |  20 | F      |    NULL |      NULL |    //新添加的数据也恢复了+-------+---------------+-----+--------+---------+-----------+

二、lvm2实现数据的备份与恢复

创建lvm逻辑卷，把mysql数据存放目录挂载到逻辑卷上

]# pvcreate /dev/sdb1  Physical volume "/dev/sdb1" successfully created     ]# vgcreate myvg /dev/sdb1   Volume group "myvg" successfully created]# lvcreate -L 1G -n mysql_lvm /dev/mmapper/ mcelog  mem     midi    mqueue/ ]# lvcreate -L 1G -n mysql_lvm /dev/mmapper/ mcelog  mem     midi    mqueue/ ]# lvcreate -L 1G -n mysql_lvm /dev/myvg  Logical volume "mysql_lvm" created.]# mke2fs -t ext4 /dev/myvg/mysql_lvm mke2fs 1.42.9 (28-Dec-2013)Filesystem label=OS type: LinuxBlock size=4096 (log=2)Fragment size=4096 (log=2)Stride=0 blocks, Stripe width=0 blocks65536 inodes, 262144 blocks13107 blocks (5.00%) reserved for the super userFirst data block=0Maximum filesystem blocks=2684354568 block groups32768 blocks per group, 32768 fragments per group8192 inodes per groupSuperblock backups stored on blocks:         32768, 98304, 163840, 229376Allocating group tables: done                            Writing inode tables: done                            Creating journal (8192 blocks): doneWriting superblocks and filesystem accounting information: done  ]# mkdir /data/mysql]# mount /dev/myvg/mysql_lvm /data/mysql/]# chown -R mysql.mysql /data/mysql/]# service mariadb start                                //启动mysql报错Redirecting to /bin/systemctl start  mariadb.service  Job for mariadb.service failed. See 'systemctl status mariadb.service' and 'journalctl -xn' for details.[root@localhost /]# tail -f /var/log/mariadb/tail: error reading '/var/log/mariadb/': Is a directorytail: /var/log/mariadb/: cannot follow end of this type of file; giving up on this nametail: no files remaining[root@localhost /]# tail -f /var/log/mariadb/mariadb.log 160609 18:14:55 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended160609 18:18:21 mysqld_safe Starting mysqld daemon with databases from /data/mysql160609 18:18:21 [Note] /usr/libexec/mysqld (mysqld 5.5.44-MariaDB-log) starting as process 5585 ...160609 18:18:21 [Warning] Can't create test file /data/mysql/localhost.lower-test160609 18:18:21 [ERROR] mysqld: File './master-bin.index' not found (Errcode: 13)发生这个原因可能是/data/mysql这个目录的问题，但我上面已经设置好了还报错]# getenforce                       //selinux的问题，关闭就行Enforcing]# setenforce 0]# service mariadb startRedirecting to /bin/systemctl start  mariadb.service

备份数据库：

MariaDB [hellodb]> flush tables with read lock;        //先锁表MariaDB [hellodb]> flsuh logs;                         //滚动二进制日志]# mysql -e "show master status;" >/root/mysqlbin.date+ "%F"   //记录二进制日志文件及位置]# lvcreate -L 500M -s -p r -n mysql_lvm_snap /dev/myvg/mysql_lvm  //快照  Logical volume "mysql_lvm_snap" created.]# mount /dev/myvg/mysql_lvm_snap /backup/             ]# cp -r * /data/mysqlback/                           //数据备份成功

恢复数据：

]# cp -r /data/mysqlback/* /data/mysql/]# chown -R mysql.mysql ./*]# service mariadb startMariaDB [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || hellodb            |                 //数据没问题| mysql              || performance_schema || test               |+--------------------+5 rows in set (0.01 sec)然后根据二进制日志恢复完全备份之后丢失的数据就行

三、xtrabackup实现数据备份和恢复

Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：

(1)备份过程快速、可靠；

(2)备份过程不会打断正在执行的事务；

(3)能够基于压缩等功能节约磁盘空间和流量；

(4)自动实现备份检验；

(5)还原速度快；

]# lspercona-xtrabackup-2.3.2-1.el7.x86_64.rpm]# yum -y install *.rpm

xtrabackup完全备份：

]# innobackupex --user=root /backup/        //如果数据库有密码则加上--password160609 19:08:43 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully.           At the end of a successful backup run innobackupex           prints "completed OK!"..................160609 19:08:55 Executing UNLOCK TABLES160609 19:08:55 All tables unlocked160609 19:08:55 Backup created in directory '/data/mysql//2016-06-09_19-08-43'MySQL binlog position: filename 'master-bin.000005', position '245'160609 19:08:55 [00] Writing backup-my.cnf160609 19:08:55 [00]        ...done160609 19:08:56 [00] Writing xtrabackup_info160609 19:08:56 [00]        ...donextrabackup: Transaction log of lsn (1597945) to (1597945) was copied.160609 19:08:56 completed OK!

数据还原：

还原准备：      在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。"准备"的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。 ]# innobackupex --apply-log /backup/2016-06-09_19-16-30/InnoDB: Setting log file ./ib_logfile101 size to 48 MBInnoDB: Setting log file ./ib_logfile1 size to 48 MBInnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0    //事务合并时innobackupex会把事务日志文件设置为48M，事务日志默认为5M，这样会导致mariadb启动不了xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 1598486160609 19:17:40 completed OK!  删除数据集，恢复]# innobackupex --copy-back /backup/2016-06-09_19-16-30/  160609 19:22:27 [01]        ...done160609 19:22:28 [01] Copying ./performance_schema/threads.frm to /data/mysql/performance_schema/threads.frm160609 19:22:28 [01]        ...done160609 19:22:28 [01] Copying ./xtrabackup_info to /data/mysql/xtrabackup_info160609 19:22:28 [01]        ...done160609 19:22:28 completed OK!]# chown -R mysql.mysql ./*   ]# service mariadb startMariaDB [(none)]> show databases;+--------------------+| Database           |+--------------------+| information_schema || hellodb            || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec)

xtrabackup进行增量备份:

插入数据：MariaDB [hellodb]> insert into students (Name,Age) values ("Hello",21);Query OK, 1 row affected (0.01 sec)]# innobackupex --incremental /backup --incremental-basedir=/backup/2016-06-09_19-16-30/         //basedir后面跟最近一次完全备份的目录，如果上次备份的增量备份，则指向上一次增量备份

注意：恢复时mysql是不能启动的，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

增量备份的整合事务时和完全备份有一些不同，需要注意：

1、需要在每个备份上（增量备份和完全备份），将已提交的事务进行"重放"，"重放"之后，所有的备份数据都合并到完全备份上

2、合并完后在基于所有的备份上将有些还未提交的事务进行"回滚"

为什么不能把增量备份上未提交的事务进行"回滚"？

因为这次增量备份上事务未提交就备份完了，在下次增量备份上事务就可能已经提交了，所有不能进行事务回滚，只有把所有的备份的事务都"重放"完后，在基于所有备份上把所有的未完成事务进行"回滚"。

]# ]# innobackupex --apply-log --redo-only /backup/2016-06-09_19-55-38  //先完全备份事务整合]# ]# innobackupex --apply-log --redo-only /backup/2016-06-09_19-55-38 --incremental=dir=/backup/2016-06-09_19-57-43/     //第一次增量备份，如果有多个，只把incremental后面的目录改成第二次增量备份]# rm -rf /data/mysql/*]# innobackupex --copy-back /backup/2016-06-09_19-55-38/]# service mariadb startMariaDB [hellodb]> select * from students;|    26 | Hello         |  21 | F      |    NULL |      NULL |+-------+---------------+-----+--------+---------+-----------+26 rows in set (0.01 sec)

每个备份目录下都有些xtarbackup文件：

]# cd /backup/2016-06-09_19-55-38/[root@localhost 2016-06-09_19-55-38]# lsxtrabackup_binlog_info     ：mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二 进制日志事件的位置xtrabackup_checkpoints     ：备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息，增量备份就是通过查看LSN发生改变的内容去备份修改的数据xtrabackup_logfile         ：xtrabackup备份是日志xtrabackup_binlog_pos_innodb  ：二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
xtrabackup_info            ：xtrabackup备份数据库的各种信息
对于以上关于mysql备份方式、策略、恢复等简析，如果大家还有更多需要了解的可以持续关注我们的行业推新，如需获取专业解答，可在官网联系售前售后的，希望该文章可给大家带来一定的知识更新。