MySQL 备份和还原-白红宇

MySQL 备份和还原

阅读量：5862 次

发布时间：2019-06-19

本文共 16631 字，大约阅读时间需要 55 分钟。

大纲

一、备份类型分类

二、各备份工具对比图

三、mysqldump实现备份

四、lvm快照实现备份

五、xtrabackup实现备份

一、备份类型分类

(1)、根据备份时服务器是否能在线分类

热备份：hot backup，读、写不受影响

温备份：warm backup，仅可以执行读操作

冷备份：cold backup，离线备份；读、写操作均中止

(2)、根据备份时数据导出方式分类

物理备份：直接复制(归档)数据文件的备份方式

逻辑备份：把数据从库中提取出来保存为文本文件

(3)、根据备份时的数据集分类

完全备份：备份全部数据

增量备份：仅备份上次完全备份或增量备份以后变化的数据

差异备份：仅备份上次完全备份以来变化的数据

二、各备份工具对比图

三、mysqldump实现备份

 mysqldump - a database backup programSYNOPSIS       mysqldump [options] [db_name [tbl_name ...]]    --master-data={0|1|2}        	0: 不记录二进制日志文件及路位置        	1：以CHNAGE MASTER TO的方式记录位置，可用于恢复后直接启动从服务器        	2：以CHANGE MASTER TO的方式记录位置，但默认为被注释    --lock-all-tables：锁定所有表    --flush-logs: 执行日志flush；	    --all-databases: 备份所有库    --databases DB_NAME,DB_NAME,...: 备份指定库

1、先做一次完全备份

MyISAM存储引擎的备份[root@soysauce ~]# mysqldump -uroot -p --all-databases --lock-all-tables --flush-logs --master-data=2 > /root/data.sqlEnter password:InnoDB存储引擎的备份[root@soysauce ~]# mysqldump -uroot -p --all-databases --single-transaction --flush-logs --master-data=2 > /root/data.sqlEnter password:

2、查看一下二进制日志滚动的位置

[root@soysauce ~]# head -25 /root/data.sql -- MySQL dump 10.13  Distrib 5.1.73, for redhat-linux-gnu (x86_64)---- Host: localhost    Database: -- -------------------------------------------------------- Server version	5.5.28-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='mysql-bin.000017', MASTER_LOG_POS=107;    # 找到这一行---- Current Database: `mysql`

3、备份一下以前的二进制日志文件之后再删除

[root@soysauce ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 23Server version: 5.5.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> SHOW BINARY LOGS;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000008 |       126 || mysql-bin.000009 |      1654 || mysql-bin.000010 |     20386 || mysql-bin.000011 |       126 || mysql-bin.000012 |       477 || mysql-bin.000013 |       126 || mysql-bin.000014 |       399 || mysql-bin.000015 |       235 || mysql-bin.000016 |       150 || mysql-bin.000017 |      4771 || mysql-bin.000018 |       534 || mysql-bin.000019 |       107 |+------------------+-----------+3 rows in set (0.00 sec)10 rows in set (0.00 sec)mysql> PURGE BINARY LOGS TO 'mysql-bin.000019';Query OK, 0 rows affected (0.07 sec)mysql> SHOW BINARY LOGS;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000019 |       107 |+------------------+-----------+1 row in set (0.00 sec)mysql> exitBye

4、每天做一次增量备份，以做完增量备份的第一天为例

[root@soysauce ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 37Server version: 5.5.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> SHOW MASTER STATUS;+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000019 |      107 |              |                  |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)mysql> FLUSH LOGS;Query OK, 0 rows affected (0.04 sec)mysql> exitBye[root@soysauce ~]# cd /mydata/data/[root@soysauce data]# lsibdata1      ib_logfile1  mysql             mysql-bin.000020  node1.network.com.err  soysauce.err  test    vsftpdib_logfile0  jiaowu       mysql-bin.000019  mysql-bin.index   performance_schema     soysauce.pid  testdb[root@soysauce data]# mkdir /bin_backup[root@soysauce data]# mysqlbinlog mysql-bin.000019 > /bin_backup/mon-incremental.sql

5、当某一天磁盘损坏，所有库丢失，先初始化mysql

[root@soysauce ~]# cd /usr/local/mysql[root@soysauce mysql]# scripts/mysql_install_db --user=mysql --datadir=/data/mysql/Installing MySQL system tables...OKFilling help tables...OKTo start mysqld at boot time you have to copysupport-files/mysql.server to the right place for your systemPLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !To do so, start the server, then issue the following commands:./bin/mysqladmin -u root password 'new-password'./bin/mysqladmin -u root -h soysauce password 'new-password'Alternatively you can run:./bin/mysql_secure_installationwhich will also give you the option of removing the testdatabases and anonymous user created by default.  This isstrongly recommended for production servers.See the manual for more instructions.You can start the MySQL daemon with:cd . ; ./bin/mysqld_safe &You can test the MySQL daemon with mysql-test-run.plcd ./mysql-test ; perl mysql-test-run.plPlease report any problems with the ./bin/mysqlbug script!

6、先暂时关闭记录二进制日志功能

mysql> SET @@GLOBAL.sql_log_bin=OFF;Query OK, 0 rows affected (0.00 sec)mysql> SELECT @@GLOBAL.sql_log_bin;+----------------------+| @@GLOBAL.sql_log_bin |+----------------------+|                    0 |+----------------------+1 row in set (0.01 sec)

7、重新登录mysql，恢复完全备份

[root@soysauce mysql]# mysql  < /root/data.sql

8、恢复增量备份

[root@soysauce mysql]# mysql < /bin_backup/mon-incremental.sql

9、即使点还原

[root@soysauce mysql]# mysqlbinlog mysql-bin.000020 | mysql

10、当所有数据恢复之后，再开启二进制日志记录功能

mysql> SET @@GLOBAL.sql_log_bin=ON;Query OK, 0 rows affected (0.00 sec)mysql> SELECT @@GLOBAL.sql_log_bin;+----------------------+| @@GLOBAL.sql_log_bin |+----------------------+|                    1 |+----------------------+1 row in set (0.00 sec)

四、lvm快照实现备份

1、前提

数据文件要在逻辑卷上

此逻辑卷所在卷组必须有足够空间使用快照卷

数据文件和事务日志要在同一个逻辑卷上

2、lvm实现备份恢复

(1)、打开会话，施加读锁，锁定所有表

mysql> FLUSH TABLES WITH READ LOCK;Query OK, 0 rows affected (0.09 sec)mysql> FLUSH LOGS;Query OK, 0 rows affected (0.10 sec)

(2)、通过另一个终端，保存二进制日志文件及相关位置信息

[root@soysauce mysql]# mysql -uroot -p -e 'SHOW MASTER STATUS\G' > /backup/master-`date +%F`.info

(3)、创建快照卷

[root@soysauce mysql]# lvcreate -s -L 50M -p r -n mydata-snap /dev/myvg/mydata

(4)、释放锁

mysql> UNLOCK TABLES;

(5)、挂载快照卷，备份

[root@soysauce ~]# mount -o /dev/myvg/mydata-snap /mnt/[root@soysauce ~]# cd /mnt/[root@soysauce mnt]# lsdata  lost+found[root@soysauce mnt]# cd data/[root@soysauce data]# mkdir -pv /backup/full-backup-`date +%F`mkdir: created directory `/backup/full-backup-2015-12-26'[root@soysauce data]# cp -a ./* /backup/full-back-2015-12-26

6、删除快照卷

[root@soysauce data]# cd [root@soysauce ~]# umount /mnt[root@soysauce ~]# lvremove /dev/myvg/mydata-snap

7、增量备份二进制日志

[root@soysauce ~]# cd /backup/full-backup-2015-12-26 [root@soysauce full-backup-2015-12-26]# lsibdata1      ib_logfile1  mysql             mysql-bin.000002  mysql-bin.000004  performance_schema  soysauce.pid  testdbib_logfile0  jiaowu       mysql-bin.000001  mysql-bin.000003  mysql-bin.index   soysauce.err        test          vsftpd[root@soysauce full-backup-2015-12-26]# rm -rf mysql-bin.*[root@soysauce full-backup-2015-12-26]# cd [root@soysauce ~]# cat /backup/master-2015-12-26.info *************************** 1. row ***************************            File: mysql-bin.000004        Position: 107    Binlog_Do_DB: Binlog_Ignore_DB: [root@soysauce ~]# mysqlbinlog --start-datetime='2015-12-26 21:36:38' mysql-bin.000004 mysql-bin.000005 > /backup/incremental-`date +%F-%T`.sql

8、恢复完全备份至数据目录

[root@soysauce ~]# cp -a /backup/full-backup-2015-12-26/* /mydata/data

9、恢复增量备份

[root@soysauce ~]# mysqlWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 14Server version: 5.5.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> SET @@SESSION.sql_log_bin=OFF;Query OK, 0 rows affected (0.03 sec)mysql> SELECT @@SESSION.sql_log_bin;+-----------------------+| @@SESSION.sql_log_bin |+-----------------------+|                     0 |+-----------------------+1 row in set (0.00 sec)mysql> source /backup/incremental-2015-12-26-22\:36\:09.sqlmysql> SET @@SESSION.sql_log_bin=ON;Query OK, 0 rows affected (0.03 sec)mysql> SELECT @@SESSION.sql_log_bin;+-----------------------+| @@SESSION.sql_log_bin |+-----------------------+|                     1 |+-----------------------+1 row in set (0.00 sec)

五、xtrabackup实现备份

1、简介

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

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

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

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

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

(5)还原速度快；

2、安装

自行根据系统及硬件平台下载：

[root@soysauce ~]# wget [root@soysauce ~]# yum localinstall -y percona-xtrabackup-2.3.3-1.el6.x86_64.rpm

3、完全备份

[root@soysauce ~]# innobackupex --user=root --password='redhat' /backup/    # 制定备份文件存储路径[root@soysauce ~]# cd /backup/2015-12-27_11-35-36/[root@soysauce 2015-12-27_11-35-36]# lsbackup-my.cnf  jiaowu  performance_schema  xtrabackup_binlog_info  xtrabackup_infoibdata1        mysql   test                xtrabackup_checkpoints  xtrabackup_logfile如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：mysql> CREATE USER ’bkpuser’@’localhost’ IDENTIFIED BY 'redhat’;mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM ’bkpuser’@'localhost';mysql> GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT ON *.* TO ’bkpuser’@’localhost’;mysql> FLUSH PRIVILEGES;使用innobakupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命令的目录中。在备份的同时，innobackupex还会在备份目录中创建如下文件：(1)xtrabackup_checkpoints —— 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。(2)xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。(3)xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件；(4)backup-my.cnf —— 备份命令用到的配置选项信息；在使用innobackupex进行备份时，还可以使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。

4、准备(prepare)一个完全备份

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。innobakupex命令的--apply-log选项可实现上述功能。

[root@soysauce ~]# innobackupex --apply-log /backup/2015-12-27_11-35-36/xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 1670166151227 11:56:40 completed OK!在实现“准备”的过程中，innobackupex通常还可以使用--use-memory选项来指定其可以使用的内存的大小，默认通常为100M。如果有足够的内存可用，可以多划分一些内存给prepare的过程，以提高其完成速度。

5、从一个完全备份中恢复数据

[root@soysauce mysql]# innobackupex --copy-back /backup/2015-12-27_11-35-36/    # 恢复时不需要启动mysqlinnobackupex命令的--copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息请确保如上信息的最行一行出现“innobackupex: completed OK!”。[root@soysauce mysql]# chown -R mysql.mysql ./*        # 修改数据目录下所有文件的属主属组为mysql恢复完成后可做一次完全备份

6、使用innobackupex进行增量备份

每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础，即innobackupex通过备份上次完全备份之后发生改变的页面来实现。要实现第一次增量备份，可以使用下面的命令进行：[root@soysauce mysql]# innobackupex --incremental /backup --incremental-dir=BASEDIR其中，BASEDIR指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/backup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外，在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向上一次的增量备份所在的目录。需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。“准备”(prepare)增量备份与整理完全备份有着一些不同，尤其要注意的是：(1)需要在每个备份(包括完全和各个增量备份)上，将已经提交的事务进行“重放”。“重放”之后，所有的备份数据将合并到完全备份上。(2)基于所有的备份将未提交的事务进行“回滚”。于是，操作就变成了：# innobackupex --apply-log --redo-only BASE-DIR接着执行：# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1而后是第二个增量：# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2其中BASE-DIR指的是完全备份所在的目录，而INCREMENTAL-DIR-1指的是第一次增量备份的目录，INCREMENTAL-DIR-2指的是第二次增量备份的目录，其它依次类推，即如果有多次增量备份，每一次都要执行如上操作；最后直接还原完全备份就ok例如:完全备份[root@soysauce ~]# innobackupex --apply-log --redo-only /backup/full-backup/2015-12-27_21-31-36/第一次增量备份[root@soysauce ~]# innobackupex --apply-log --redo-only /backup/full-backup/2015-12-27_21-31-36/ --incremental-dir=/backup/incremental/2015-12-27_21-34-20/第二次增量备份[root@soysauce ~]# innobackupex --apply-log --redo-only /backup/full-backup/2015-12-27_21-31-36/ --incremental-dir=/backup/incremental/2015-12-27_21-37-14/恢复完全备份[root@soysauce ~]# innobackupex --copy-back /backup/full-backup/2015-12-27_21-31-36/

7、Xtrabackup的“流”及“备份压缩”功能

Xtrabackup对备份的数据文件支持“流”功能，即可以将备份的数据通过STDOUT传输给tar程序进行归档，而不是默认的直接保存至某备份目录中。要使用此功能，仅需要使用--stream选项即可。如：# innobackupex --stream=tar  /backup | gzip > /backup/`date +%F_%H-%M-%S`.tar.gz甚至也可以使用类似如下命令将数据备份至其它服务器：# innobackupex --stream=tar  /backup | ssh user@www.magedu.com  "cat -  > /backups/`date +%F_%H-%M-%S`.tar" 此外，在执行本地备份时，还可以使用--parallel选项对多个文件进行并行复制。此选项用于指定在复制时启动的线程数目。当然，在实际进行备份时要利用此功能的便利性，也需要启用innodb_file_per_table选项或共享的表空间通过innodb_data_file_path选项存储在多个ibdata文件中。对某一数据库的多个文件的复制无法利用到此功能。其简单使用方法如下：# innobackupex --parallel  /path/to/backup同时，innobackupex备份的数据文件也可以存储至远程主机，这可以使用--remote-host选项来实现：# innobackupex --remote-host=root@www.magedu.com  /path/IN/REMOTE/HOST/to/backup

6、导入或导出单张表

默认情况下，InnoDB表不能通过直接复制表文件的方式在mysql服务器之间进行移植，即便使用了innodb_file_per_table选项。而使用Xtrabackup工具可以实现此种功能，不过，此时需要“导出”表的mysql服务器启用了innodb_file_per_table选项（严格来说，是要“导出”的表在其创建之前，mysql服务器就启用了innodb_file_per_table选项），并且“导入”表的服务器同时启用了innodb_file_per_table和innodb_expand_import选项。(1)“导出”表导出表是在备份的prepare阶段进行的，因此，一旦完全备份完成，就可以在prepare过程中通过--export选项将某表导出了：# innobackupex --apply-log --export /path/to/backup此命令会为每个innodb表的表空间创建一个以.exp结尾的文件，这些以.exp结尾的文件则可以用于导入至其它服务器。(2)“导入”表要在mysql服务器上导入来自于其它服务器的某innodb表，需要先在当前服务器上创建一个跟原表表结构一致的表，而后才能实现将表导入：mysql> CREATE TABLE mytable (...)  ENGINE=InnoDB;然后将此表的表空间删除：mysql> ALTER TABLE mydatabase.mytable  DISCARD TABLESPACE;接下来，将来自于“导出”表的服务器的mytable表的mytable.ibd和mytable.exp文件复制到当前服务器的数据目录，然后使用如下命令将其“导入”：mysql> ALTER TABLE mydatabase.mytable  IMPORT TABLESPACE;导入导出记得保证tb_name.cfg tb_name.ibd tb_name.frm属主属组都为mysql，否则会报错

7、使用Xtrabackup对数据库进行部分备份

Xtrabackup也可以实现部分备份，即只备份某个或某些指定的数据库或某数据库中的某个或某些表。但要使用此功能，必须启用innodb_file_per_table选项，即每张表保存为一个独立的文件。同时，其也不支持--stream选项，即不支持将数据通过管道传输给其它程序进行处理。此外，还原部分备份跟还原全部数据的备份也有所不同，即你不能通过简单地将prepared的部分备份使用--copy-back选项直接复制回数据目录，而是要通过导入表的方向来实现还原。当然，有些情况下，部分备份也可以直接通过--copy-back进行还原，但这种方式还原而来的数据多数会产生数据不一致的问题，因此，无论如何不推荐使用这种方式。(1)创建部分备份创建部分备份的方式有三种：正则表达式(--include), 枚举表文件(--tables-file)和列出要备份的数据库(--databases)。(a)使用--include使用--include时，要求为其指定要备份的表的完整名称，即形如databasename.tablename，如：# innobackupex --include='^mageedu[.]tb1'  /path/to/backup(b)使用--tables-file此选项的参数需要是一个文件名，此文件中每行包含一个要备份的表的完整名称；如：# echo -e 'mageedu.tb1\nmageedu.tb2' > /tmp/tables.txt# innobackupex --tables-file=/tmp/tables.txt  /path/to/backup(c)使用--databases此选项接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开；同时，在指定某数据库时，也可以只指定其中的某张表。此外，此选项也可以接受一个文件为参数，文件中每一行为一个要备份的对象。如：# innobackupex --databases="mageedu testdb"  /path/to/backup(2)整理(preparing)部分备份prepare部分备份的过程类似于导出表的过程，要使用--export选项进行：# innobackupex --apply-log --export  /pat/to/partial/backup此命令执行过程中，innobackupex会调用xtrabackup命令从数据字典中移除缺失的表，因此，会显示出许多关于“表不存在”类的警告信息。同时，也会显示出为备份文件中存在的表创建.exp文件的相关信息。(3)还原部分备份还原部分备份的过程跟导入表的过程相同。当然，也可以通过直接复制prepared状态的备份直接至数据目录中实现还原，不要此时要求数据目录处于一致状态。

注意：

1、将数据和备份放在不同的磁盘设备上；异机或异地备份存储较为理想；

2、备份的数据应该周期性地进行还原测试；

3、每次灾难恢复后都应该立即做一次完全备份；

4、针对不同规模或级别的数据量，要定制好备份策略；

5、二进制日志应该跟数据文件在不同磁盘上，并周期性地备份好二进制日志文件；

从备份中恢复应该遵循步骤：

1、停止MySQL服务器；

2、记录服务器的配置和文件权限；

3、将数据从备份移到MySQL数据目录；其执行方式依赖于工具；

4、改变配置和文件权限；

5、以限制访问模式重启服务器；mysqld的--skip-networking选项可跳过网络功能；