MySQL 数据备份与还原

数据的备份类型

数据的备份类型根据其自身的特性主要分为以下几组

1、完全备份备份整个数据集( 即整个数据库 )、部分备份指的是备份部分数据集(例如: 只备份一个表)
2、部分备份
而部分备份又分为以下两种

增量备份: 备份自上一次备份以来(增量或完全)以来变化的数据; 特点: 节约空间、还原麻烦差异备份: 备份自上一次完全备份以来变化的数据 特点: 浪费空间、还原比增量备份简单

示意图

MySQL备份数据的方式

在MySQl中我们备份数据一般有几种方式

1. 热备份 : 当数据库进行备份时, 数据库的读写操作均不是受影响
2. 温备份 : 当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作
3. 冷备份 :当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线

MySQL中进行不同方式的备份还要考虑存储引擎是否支持
MyISAM

 热备 × 温备 √ 冷备 √InnoDB 热备 √ 温备 √ 冷备 √

我们在考虑完数据在备份时, 数据库的运行状态之后还需要考虑对于MySQL数据库中数据的备份方式

1. 物理备份 : 通过tar,cp等命令直接打包复制数据库的数据文件达到备份的效果
2. 逻辑备份：通过特定工具从数据库中导出数据并另存备份(逻辑备份会丢失数据精度)

备份需要考虑的问题

我们要备份什么?

一般情况下, 我们需要备份的数据分为以下几种    数据    二进制日志, InnoDB事务日志    代码(存储过程、存储函数、触发器、事件调度器)    服务器配置文件

备份工具

这里我们列举出常用的几种备份工具     mysqldump : 逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备     cp, tar 等归档复制工具: 物理备份工具, 适用于所有的存储引擎, 冷备、完全备份、部分备份     lvm2 snapshot: 几乎热备, 借助文件系统管理工具进行备份     mysqlhotcopy: 名不副实的的一个工具, 几乎冷备, 仅支持MyISAM存储引擎     xtrabackup: 一款非常强大的InnoDB/XtraDB热备工具, 支持完全备份、增量备份, 由percona提供

设计合适的备份策略

针对不同的场景下, 我们应该制定不同的备份策略对数据库进行备份, 一般情况下, 备份策略一般为以下几种

1. 直接cp,tar复制数据库文件
2. mysqldump+复制BIN LOGS
3. lvm2快照+复制BIN LOGS
4. xtrabackup

以上的几种解决方案分别针对于不同的场景

1. 如果数据量较小, 可以使用第一种方式, 直接复制数据库文件
2. 如果数据量还行, 可以使用第二种方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARYLOG达到增量备份的效果
3. 如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARYLOG达到增量备份的效果
4. 如果数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份

实战演练

使用cp进行备份

1. 向数据库施加读锁

mysql> FLUSH TABLES WITH READ LOCK;

1. 备份数据文件

[root@node1 ~]# mkdir /backup   #创建文件夹存放备份数据库文件[root@node1 ~]# cp -a /var/lib/mysql/* /backup     #保留权限的拷贝源数据文件[root@node1 ~]# ls /backup   #查看目录下的文件employees  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.sock  test

1. 模拟数据丢失并恢复

[root@node1 ~]# rm -rf /var/lib/mysql/*    #删除数据库的所有文件[root@node1 ~]# service mysqld restart   #重启MySQL, 如果是编译安装的应该不能启动, 如果rpm安装则会重新初始化数据库mysql> SHOW DATABASES;    #因为我们是rpm安装的, 连接到MySQL进行查看, 发现数据丢失了！[root@node1 ~]# rm -rf /var/lib/mysql/*    #这一步可以不做[root@node1 ~]# cp -a /backup/* /var/lib/mysql/    #将备份的数据文件拷贝回去[root@node1 ~]# service mysqld restart  #重启MySQL#重新连接数据并查看mysql> SHOW DATABASES;    #数据库已恢复mysql> USE employees;      mysql> SELECT COUNT(*) FROM employees;    #表的行数没有变化

使用mysqldump+复制BINARY LOG备份

我们这里使用的是使用yum安装的mysql-5.1的版本, 使用的数据集为从网络上找到的一个员工数据库

我们通过mysqldump进行一次完全备份, 再修改表中的数据, 然后再通过binary log进行恢复 二进制日志需要在mysql配置文件中添加 log_bin=on 开启

mysqldump命令介绍
mysqldump是一个客户端的逻辑备份工具, 可以生成一个重现创建原始数据库和表的SQL语句, 可以支持所有的存储引擎, 对于InnoDB支持热备

官方文档介绍这里写链接内容

#基本语法格式shell> mysqldump [options] db_name [tbl_name ...]    恢复需要手动CRATE DATABASESshell> mysqldump [options] --databases db_name ...   恢复不需要手动创建数据库shell> mysqldump [options] --all-databases           恢复不需要手动创建数据库其他选项:     -E, --events: 备份事件调度器     -R, --routines: 备份存储过程和存储函数     --triggers: 备份表的触发器; --skip-triggers      --master-date[=value]           1: 记录为CHANGE MASTER TO 语句、语句不被注释         2: 记录为注释的CHANGE MASTER TO语句         基于二进制还原只能全库还原     --flush-logs: 日志滚动         锁定表完成后执行日志滚动

查看数据库的信息mysql> SHOW DATABASES;    #查看当前的数据库, 我们的数据库为employeesmysql> USE employees;Database changedmysql> SHOW TABLES;         #查看当前库中的表mysql> SELECT COUNT(*) FROM employees;   #由于篇幅原因, 我们这里只看一下employees的行数为300024

使用mysqldump备份数据库

[root@node1 ~]# mysql -e 'SHOW MASTER STATUS'   #查看当前二进制文件的状态, 并记录下position的数字[root@node1 ~]# mysqldump --all-databases --lock-all-tables  > backup.sql   #备份数据库到backup.sql文件中mysql> CREATE DATABASE TEST1;   #创建一个数据库mysql> SHOW MASTER STATUS;   #记下现在的position[root@node1 ~]# cp /var/lib/mysql/mysql-bin.000003 /root  #备份二进制文件[root@node1 ~]# service mysqld stop   #停止MySQL[root@node1 ~]# rm -rf /var/lib/mysql/*   #删除所有的数据文件[root@node1 ~]# service mysqld start    #启动MySQL, 如果是编译安装的应该不能启动(需重新初始化), 如果rpm安装则会重新初始化数据库mysql> SHOW DATABASES;   #查看数据库, 数据丢失!mysql> SET sql_log_bin=OFF;   #暂时先将二进制日志关闭 mysql> source backup.sql  #恢复数据，所需时间根据数据库时间大小而定mysql> SET sql_log_bin=ON; 开启二进制日志mysql> SHOW DATABASES;   #数据库恢复, 但是缺少TEST1 [root@node1 ~]# mysqlbinlog --start-position=106 --stop-position=191 mysql-bin.000003 | mysql employees #通过二进制日志增量恢复数据mysql> SHOW DATABASES;    #现在TEST1出现了！#完成

使用lvm2快照备份数据

做实验之前我们先回顾一下lvm2-snapshot的知识

LVM快照简单来说就是将所快照源分区一个时间点所有文件的元数据进行保存，如果源文件没有改变，那么访问快照卷的相应文件则直接指向源分区的源文件，如果源文件发生改变，则快照卷中与之对应的文件不会发生改变。快照卷主要用于辅助备份文件。 这里只简单介绍，查看详情

部署lvm环境

添加硬盘; 这里我们直接实现SCSI硬盘的热插拔, 首先在虚拟机中添加一块硬盘, 不重启

[root@node1 ~]# ls /dev/sd*   #只有以下几块硬盘, 但是我们不重启可以让系统识别新添加的硬盘/dev/sda  /dev/sda1  /dev/sda2[root@node1 ~]# echo '- - -' > /sys/class/scsi_host/host0/scan [root@node1 ~]# echo '- - -' > /sys/class/scsi_host/host1/scan [root@node1 ~]# echo '- - -' > /sys/class/scsi_host/host2/scan [root@node1 ~]# ls /dev/sd*    #看！sdb识别出来了/dev/sda  /dev/sda1  /dev/sda2  /dev/sdb[root@node1 ~]# fdisk /dev/sdb   #分区Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabelBuilding a new DOS disklabel with disk identifier 0xd353d192.Changes will remain in memory only, until you decide to write them.After that, of course, the previous content won't be recoverable.Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)WARNING: DOS-compatible mode is deprecated. It's strongly recommended to         switch off the mode (command 'c') and change display units to         sectors (command 'u').Command (m for help): nCommand action   e   extended   p   primary partition (1-4)pPartition number (1-4): 1First cylinder (1-2610, default 1): Using default value 1Last cylinder, +cylinders or +size{K,M,G} (1-2610, default 2610): +15GCommand (m for help): tSelected partition 1Hex code (type L to list codes): 8eChanged system type of partition 1 to 8e (Linux LVM)Command (m for help): wThe partition table has been altered!Calling ioctl() to re-read partition table.Syncing disks.You have new mail in /var/spool/mail/root[root@node1 ~]# partx -a /dev/sdbBLKPG: Device or resource busyerror adding partition 1##创建逻辑卷[root@node1 ~]# pvcreate /dev/sdb1  Physical volume "/dev/sdb1" successfully created[root@node1 ~]# vgcreate myvg /dev/sdb1   Volume group "myvg" successfully created[root@node1 ~]# lvcreate -n mydata -L 5G myvg   Logical volume "mydata" created.[root@node1 ~]# mkfs.ext4 /dev/mapper/myvg-mydata   #格式化[root@node1 ~]# mkdir /lvm_data[root@node1 ~]# mount /dev/mapper/myvg-mydata /lvm_data  #挂载到/lvm_data[root@node1 ~]# vim /etc/my.cnf    #修改mysql配置文件的datadir如下datadir=/lvm_data[root@node1 ~]# service mysqld restart  #重启MySQL####重新导入employees数据库########略过####

查看数据库的信息mysql> SHOW DATABASES;    #查看当前的数据库, 我们的数据库为employees+--------------------+| Database           |+--------------------+| information_schema || employees          || mysql              || test               |+--------------------+4 rows in set (0.00 sec)mysql> USE employees;Database changedmysql> SHOW TABLES;         #查看当前库中的表+---------------------+| Tables_in_employees |+---------------------+| departments         || dept_emp            || dept_manager        || employees           || salaries            || titles              |+---------------------+6 rows in set (0.00 sec)mysql> SELECT COUNT(*) FROM employees;   #由于篇幅原因, 我们这里只看一下employees的行数为300024+----------+| COUNT(*) |+----------+|   300024 |+----------+1 row in set (0.05 sec)创建快照卷并备份mysql> FLUSH TABLES WITH READ LOCK;     #锁定所有表Query OK, 0 rows affected (0.00 sec)[root@node1 lvm_data]# lvcreate -L 1G -n mydata-snap -p r -s /dev/mapper/myvg-mydata   #创建快照卷  Logical volume "mydata-snap" created.mysql> UNLOCK TABLES;  #解锁所有表Query OK, 0 rows affected (0.00 sec)[root@node1 lvm_data]# mkdir /lvm_snap  #创建文件夹[root@node1 lvm_data]# mount /dev/myvg/mydata-snap /lvm_snap/  #挂载snapmount: block device /dev/mapper/myvg-mydata--snap is write-protected, mounting read-only[root@node1 lvm_data]# cd /lvm_snap/[root@node1 lvm_snap]# lsemployees  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.index  test[root@node1 lvm_snap]# tar cf /tmp/mysqlback.tar *  #打包文件到/tmp/mysqlback.tar[root@node1 ~]# umount /lvm_snap/  #卸载snap[root@node1 ~]# lvremove myvg mydata-snap  #删除snap恢复数据[root@node1 lvm_snap]# rm -rf /lvm_data/*[root@node1 ~]# service mysqld start    #启动MySQL, 如果是编译安装的应该不能启动(需重新初始化), 如果rpm安装则会重新初始化数据库mysql> SHOW DATABASES;   #查看数据库, 数据丢失!+--------------------+| Database           |+--------------------+| information_schema || mysql              || test               |+--------------------+3 rows in set (0.00 sec)[root@node1 ~]# cd /lvm_data/[root@node1 lvm_data]# rm -rf * #删除所有文件[root@node1 lvm_data]# tar xf /tmp/mysqlback.tar     #解压备份数据库到此文件夹 [root@node1 lvm_data]# ls  #查看当前的文件employees  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.index  testmysql> SHOW DATABASES;  #数据恢复了+--------------------+| Database           |+--------------------+| information_schema || employees          || mysql              || test               |+--------------------+4 rows in set (0.00 sec)##完成

使用Xtrabackup备份

为了更好地演示, 我们这次使用mariadb-5.5的版本, 使用xtrabackup使用InnoDB能够发挥其最大功效, 并且InnoDB的每一张表必须使用单独的表空间, 我们需要在配置文件中添加 innodb_file_per_table = ON 来开启

下载安装xtrabackup

我们这里通过wget percona官方的rpm包进行安装

[root@node1 ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.4/binary/redhat/6/x86_64/percona-xtrabackup-2.3.4-1.el6.x86_64.rpm   [root@node1 ~]# yum localinstall percona-xtrabackup-2.3.4-1.el6.x86_64.rpm   #需要EPEL源

xtrabackup介绍

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

1. 备份过程快速、可靠；
2. 备份过程不会打断正在执行的事务；
3. 能够基于压缩等功能节约磁盘空间和流量；
4. 自动实现备份检验；
5. 还原速度快；

摘自马哥的文档

xtrabackup实现完全备份

我们这里使用xtrabackup的前端配置工具innobackupex来实现对数据库的完全备份

使用innobackupex备份时, 会调用xtrabackup备份所有的InnoDB表, 复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件, 同时还会备份触发器和数据库配置文件信息相关的文件, 这些文件会被保存至一个以时间命名的目录.

备份过程

[root@node1 ~]# mkdir /extrabackup  #创建备份目录[root@node1 ~]# innobackupex --user=root /extrabackup/ #备份数据###################提示complete表示成功*********************[root@node1 ~]# ls /extrabackup/  #看到备份目录2016-04-27_07-30-48    一般情况, 备份完成后, 数据不能用于恢复操作, 因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此, 此时的数据文件仍不一致, 所以我们需要”准备”一个完全备份[root@node1 ~]# innobackupex --apply-log /extrabackup/2016-04-27_07-30-48/  #指定备份文件的目录#一般情况下下面三行结尾代表成功*****************InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 369661462160427 07:40:11 completed OK![root@node1 ~]# cd /extrabackup/2016-04-27_07-30-48/[root@node1 2016-04-27_07-30-48]# ls -hl  #查看备份文件total 31M-rw-r----- 1 root root  386 Apr 27 07:30 backup-my.cnfdrwx------ 2 root root 4.0K Apr 27 07:30 employees-rw-r----- 1 root root  18M Apr 27 07:40 ibdata1-rw-r--r-- 1 root root 5.0M Apr 27 07:40 ib_logfile0-rw-r--r-- 1 root root 5.0M Apr 27 07:40 ib_logfile1drwx------ 2 root root 4.0K Apr 27 07:30 mysqldrwx------ 2 root root 4.0K Apr 27 07:30 performance_schemadrwx------ 2 root root 4.0K Apr 27 07:30 test-rw-r----- 1 root root   27 Apr 27 07:30 xtrabackup_binlog_info-rw-r--r-- 1 root root   29 Apr 27 07:40 xtrabackup_binlog_pos_innodb-rw-r----- 1 root root  117 Apr 27 07:40 xtrabackup_checkpoints-rw-r----- 1 root root  470 Apr 27 07:30 xtrabackup_info-rw-r----- 1 root root 2.0M Apr 27 07:40 xtrabackup_logfile

恢复数据

[root@node1 ~]# rm -rf /data/*   #删除数据文件***不用启动数据库也可以还原*************[root@node1 ~]# innobackupex --copy-back /extrabackup/2016-04-27_07-30-48/   #恢复数据, 记清使用方法#########我们这里是编译安装的mariadb所以需要做一些操作##########[root@node1 data]# killall mysqld[root@node1 ~]# chown -R mysql:mysql ./* [root@node1 ~]# ll /data/      #数据恢复total 28704-rw-rw---- 1 mysql mysql    16384 Apr 27 07:43 aria_log.00000001-rw-rw---- 1 mysql mysql       52 Apr 27 07:43 aria_log_control-rw-rw---- 1 mysql mysql 18874368 Apr 27 07:43 ibdata1-rw-rw---- 1 mysql mysql  5242880 Apr 27 07:43 ib_logfile0-rw-rw---- 1 mysql mysql  5242880 Apr 27 07:43 ib_logfile1-rw-rw---- 1 mysql mysql      264 Apr 27 07:43 mysql-bin.000001-rw-rw---- 1 mysql mysql       19 Apr 27 07:43 mysql-bin.index-rw-r----- 1 mysql mysql     2166 Apr 27 07:43 node1.anyisalin.com.err[root@node1 data]# service mysqld restartMySQL server PID file could not be found!                  [FAILED]Starting MySQL..                                           [  OK  ]MariaDB [(none)]> SHOW DATABASES;  #查看数据库, 已经恢复+--------------------+| Database           |+--------------------+| information_schema || employees          || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.00 sec

增量备份

#########创建连两个数据库以供测试#####################MariaDB [(none)]> CREATE DATABASE TEST1;Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> CREATE DATABASE TEST2;Query OK, 1 row affected (0.00 sec)[root@node1 ~]# innobackupex --incremental /extrabackup/ --incremental-basedir=/extrabackup/2016-04-27_07-30-48/ [root@node1 ~]# ls /extrabackup/2016-04-27_07-57-22/ #查看备份文件total 96-rw-r----- 1 root root   386 Apr 27 07:57 backup-my.cnfdrwx------ 2 root root  4096 Apr 27 07:57 employees-rw-r----- 1 root root 49152 Apr 27 07:57 ibdata1.delta-rw-r----- 1 root root    44 Apr 27 07:57 ibdata1.metadrwx------ 2 root root  4096 Apr 27 07:57 mysqldrwx------ 2 root root  4096 Apr 27 07:57 performance_schemadrwx------ 2 root root  4096 Apr 27 07:57 testdrwx------ 2 root root  4096 Apr 27 07:57 TEST1drwx------ 2 root root  4096 Apr 27 07:57 TEST2-rw-r----- 1 root root    21 Apr 27 07:57 xtrabackup_binlog_info-rw-r----- 1 root root   123 Apr 27 07:57 xtrabackup_checkpoints-rw-r----- 1 root root   530 Apr 27 07:57 xtrabackup_info-rw-r----- 1 root root  2560 Apr 27 07:57 xtrabackup_logfile    BASEDIR指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/extrabackup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外，在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向上一次的增量备份所在的目录。需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

整理增量备份

[root@node1 ~]# innobackupex --apply-log --redo-only /extrabackup/2016-04-27_07-30-48/[root@node1 ~]# innobackupex --apply-log --redo-only /extrabackup/2016-04-27_07-30-48/ --incremental-dir=/extrabackup/2016-04-27_07-57-22/

恢复数据

[root@node1 ~]# rm -rf /data/*   #删除数据[root@node1 ~]# innobackupex --copy-back /extrabackup/2016-04-27_07-30-48/     #整理增量备份之后可以直接通过全量备份还原[root@node1 ~]# chown -R mysql.mysql /data/[root@node1 ~]# ls /data/ -ltotal 28732-rw-rw---- 1 mysql mysql     8192 Apr 27 08:05 aria_log.00000001-rw-rw---- 1 mysql mysql       52 Apr 27 08:05 aria_log_controldrwx------ 2 mysql mysql     4096 Apr 27 08:05 employees-rw-r----- 1 mysql mysql 18874368 Apr 27 08:05 ibdata1-rw-r----- 1 mysql mysql  5242880 Apr 27 08:05 ib_logfile0-rw-r----- 1 mysql mysql  5242880 Apr 27 08:05 ib_logfile1drwx------ 2 mysql mysql     4096 Apr 27 08:05 mysql-rw-rw---- 1 mysql mysql      245 Apr 27 08:05 mysql-bin.000001-rw-rw---- 1 mysql mysql       19 Apr 27 08:05 mysql-bin.index-rw-r----- 1 mysql mysql     1812 Apr 27 08:05 node1.anyisalin.com.err-rw-rw---- 1 mysql mysql        5 Apr 27 08:05 node1.anyisalin.com.piddrwx------ 2 mysql mysql     4096 Apr 27 08:05 performance_schemadrwx------ 2 mysql mysql     4096 Apr 27 08:05 testdrwx------ 2 mysql mysql     4096 Apr 27 08:05 TEST1drwx------ 2 mysql mysql     4096 Apr 27 08:05 TEST2-rw-r----- 1 mysql mysql       29 Apr 27 08:05 xtrabackup_binlog_pos_innodb-rw-r----- 1 mysql mysql      530 Apr 27 08:05 xtrabackup_infoMariaDB [(none)]> SHOW DATABASES;  #数据还原+--------------------+| Database           |+--------------------+| information_schema || TEST1              || TEST2              || employees          || mysql              || performance_schema || test               |+--------------------+7 rows in set (0.00 sec)

转载请注明：linux运维部落 » ​学会用各种姿势备份MySQL数据库