4种备份MySQL数据库
前言
我们试着想一想, 在生产环境中什么最重要？如果我们服务器的硬件坏了可以维修或者换新, 软件问题可以修复或重新安装, 但是如果数据没了呢？这可能是最恐怖的事情了吧, 我感觉在生产环境中应该没有什么比数据跟更为重要. 那么我们该如何保证数据不丢失、或者丢失后可以快速恢复呢？只要看完这篇, 大家应该就能对MySQL中实现数据备份和恢复能有一定的了解。
为什么需要备份数据？
其实在前言中也大概说明了为什么要备份数据, 但是我们还是应该具体了解一下为什么要备份数据
在生产环境中我们数据库可能会遭遇各种各样的不测从而导致数据丢失, 大概分为以下几种.
- 硬件故障
- 软件故障
- 自然灾害
- 黑客攻击
- 误操作 (占比最大)
所以, 为了在数据丢失之后能够恢复数据, 我们就需要定期的备份数据, 备份数据的策略要根据不同的应用场景进行定制, 大致有几个参考数值, 我们可以根据这些数值从而定制符合特定环境中的数据备份策略
- 能够容忍丢失多少数据
- 恢复数据需要多长时间
- 需要恢复哪一些数据
数据的备份类型
数据的备份类型根据其自身的特性主要分为以下几组
- 完全备份
- 部分备份
完全备份指的是备份整个数据集( 即整个数据库 )、部分备份指的是备份部分数据集(例如: 只备份一个表)
而部分备份又分为以下两种
- 增量备份
- 差异备份
增量备份指的是备份自上一次备份以来(增量或完全)以来变化的数据; 特点: 节约空间、还原麻烦
差异备份指的是备份自上一次完全备份以来变化的数据 特点: 浪费空间、还原比增量备份简单
示意图
MySQL备份数据的方式
在MySQl中我们备份数据一般有几种方式
- 热备份
- 温备份
- 冷备份
热备份指的是当数据库进行备份时, 数据库的读写操作均不是受影响
温备份指的是当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作
冷备份指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线
MySQL中进行不同方式的备份还要考虑存储引擎是否支持
MyISAM
- 热备 ×
- 温备 √
- 冷备 √
InnoDB
- 热备 √
- 温备 √
- 冷备 √
我们在考虑完数据在备份时, 数据库的运行状态之后还需要考虑对于MySQL数据库中数据的备份方式
物理备份一般就是通过tar,cp等命令直接打包复制数据库的数据文件达到备份的效果
逻辑备份一般就是通过特定工具从数据库中导出数据并另存备份(逻辑备份会丢失数据精度)
备份策略
针对不同的场景下, 我们应该制定不同的备份策略对数据库进行备份, 一般情况下, 备份策略一般为以下四种：
- 直接cp,tar复制数据库文件
- mysqldump 复制BIN LOGS
- lvm2快照 复制BIN LOGS
- xtrabackup
以上的几种解决方案分别针对于不同的场景
- 如果数据量较小, 可以使用第一种方式, 直接复制数据库文件
- 如果数据量还行, 可以使用第二种方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果
- 如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果
- 如果数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份
mysqldump命令备份与还原
1. 备份
原理：
mysqldump命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。
mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。
备份一个数据库：
命令格式：
mysqldump -u username -p dbname table1 table2 ...-> BackupName.sqldbname参数表示数据库的名称；
table1和table2参数表示需要备份的表的名称，为空则整个数据库备份；
BackupName.sql参数表设计备份文件的名称，文件名前面可以加上一个绝对路径。通常将数据库被分成一个后缀名为sql的文件；
使用示例：
mysqldump -u root -p test person > D:\backup.sql备份多个数据库
命令格式：
mysqldump -u username -p --databases dbname2 dbname2 > Backup.sql使用示例：
mysqldump -u root -p --databases test mysql > D:\backup.sql备份所有数据库
命令格式：
mysqldump -u username -p -all-databases > BackupName.sql使用示例：
mysqldump -u -root -p -all-databases > D:\all.sql2. 还原
还原使用mysqldump命令备份的数据库
语法如下：
mysql -u root -p [dbname] < backup.sq使用示例：
mysql -u root -p < C:\backup.sqlcp复制数据文件备份及还原
过程演示
创建backup目录
[root@my57 mysql]# mkdir /backup
//将mysql安装目录下data目录下的文件复制进backup目录下
[root@my57 mysql]# cp -a /data/mysql/data/* /backup/
//查看backup目录下文件
[root@my57 mysql]# ls /backup/
auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 my57.err my57.pid mysql mysqld_safe.pid performance_schema sys test xtrabackup_info
//删除mysql安装目录下data目录下文件
[root@my57 mysql]# rm -rf /data/mysql/data/*
//mysql服务重置
[root@my57 mysql]# service mysql restart
ERROR! MySQL server PID file could not be found!
Starting MySQL... ERROR! The server quit without updating PID file (/data/mysql/data//my57.pid).
这时启动不了，我们再把备份文件拷贝回来，在启动就可以了。
//将backup目录下文件复制到mysql安装目录data目录下
cp -a /backup/* /data/mysql/data/lvm2快照+复制BIN LOGS
LVM快照简单来说就是将所快照源分区一个时间点所有文件的元数据进行保存，如果源文件没有改变，那么访问快照卷的相应文件则直接指向源分区的源文件，如果源文件发生改变，则快照卷中与之对应的文件不会发生改变。快照卷主要用于辅助备份文件。 这里只简单介绍，点击查看详细介绍
xtrabackup
Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：
- 备份过程快速、可靠；
- 备份过程不会打断正在执行的事务；
- 能够基于压缩等功能节约磁盘空间和流量；
- 自动实现备份检验；
- 还原速度快；
我们这里使用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 369661462
160427 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.cnf
drwx------ 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_logfile1
drwx------ 2 root root 4.0K Apr 27 07:30 mysql
drwx------ 2 root root 4.0K Apr 27 07:30 performance_schema
drwx------ 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 restart
MySQL 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.cnf
drwx------ 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.meta
drwx------ 2 root root 4096 Apr 27 07:57 mysql
drwx------ 2 root root 4096 Apr 27 07:57 performance_schema
drwx------ 2 root root 4096 Apr 27 07:57 test
drwx------ 2 root root 4096 Apr 27 07:57 TEST1
drwx------ 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_logfileBASEDIR指的是完全备份所在的目录，此命令执行结束后，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-5
7-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/ -l
total 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_control
drwx------ 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_logfile1
drwx------ 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.pid
drwx------ 2 mysql mysql 4096 Apr 27 08:05 performance_schema
drwx------ 2 mysql mysql 4096 Apr 27 08:05 test
drwx------ 2 mysql mysql 4096 Apr 27 08:05 TEST1
drwx------ 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_info
MariaDB [(none)]> SHOW DATABASES; #数据还原
+--------------------+
| Database |
+--------------------+
| information_schema |
| TEST1 |
| TEST2 |
| employees |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
#关于xtrabackup还有很多强大的功能没有叙述、有兴趣可以去看官方文档总结
| 备份方法 | 备份速度 | 恢复速度 | 便捷性 | 功能 | 一本用于 |
|---|---|---|---|---|---|
| cp | 快 | 快 | 一般、灵活性低 | 很弱 | 少量数据备份 |
| mysqldump | 慢 | 慢 | 一般、可无视存储引擎的差异 | 一般 | 中小型数据量的备份 |
| lvm2快照 | 快 | 快 | 一般、支持几乎热备、速度快 | 一般 | 中小型数据量的备份 |
| xtrabackup | 较快 | 较快 | 实现innodb热备、对存储引擎有要求 | 强大 | 较大规模的备份 |
内容基本是转载自 http://www.cnblogs.com/liangshaoye/p/5464794.html 想查看备份策略对应的详细示例可在原文处查看。
扫一扫
272
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
