学会用各种方式备份MySQL数据库

• 前言

• 为什么需要备份数据？

• 数据的备份类型

• Mysql备份数据的方式

• 备份需要考虑的问题

• 设计合适的备份策略

  • 使用cp进行备份

  • 使用mysqldump+复制BINARY LOG备份

  • 使用lvm2快照备份数据

  • 使用Xtrabackup备份

    
    

前言

   试着想一想, 在生产环境中什么最重要？如果我们服务器的硬件坏了可以维修或者换新, 软件问题可以修复或重新安装, 但是如果数据没了呢？这可能是最恐怖的事情了吧, 我感觉在生产环境中应该没有什么比数据跟更为重要. 那么我们该如何保证数据不丢失、或者丢失后可以快速恢复呢？只要看完这篇, 大家应该就能对Mysql中实现数据备份和恢复能有一定的了解。

为什么需要备份数据？

其实在前言中也大概说明了为什么要备份数据, 但是我们还是应该具体了解一下为什么要备份数据

在生产环境中我们数据库可能会遭遇各种各样的不测从而导致数据丢失, 大概分为以下几种.

• 硬件故障

• 软件故障

• 自然灾害

• 黑客攻击

• 误操作 (占比最大)

所以, 为了在数据丢失之后能够恢复数据, 我们就需要定期的备份数据, 备份数据的策略要根据不同的应用场景进行定制, 大致有几个参考数值, 我们可以根据这些数值从而定制符合特定环境中的数据备份策略

• 能够容忍丢失多少数据

• 恢复数据需要多长时间

• 需要恢复哪一些数据

数据的备份类型

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

• 完全备份

• 部分备份

  完全备份指的是备份整个数据集( 即整个数据库 )、部分备份指的是备份部分数据集(例如: 只备份一个表)

而部分备份又分为以下两种

• 增量备份

• 差异备份

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

示意图

MySQL备份数据的方式

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

• 热备份

• 温备份

• 冷备份

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

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

• MyISAM 
  

   热备 ×

   温备 √

   冷备 √

• InnoDB

   热备 √

   温备 √

   冷备 √

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

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

  • 物理备份

  • 逻辑备份

备份需要考虑的问题

定制备份策略前, 我们还需要考虑一些问题

我们要备份什么?

一般情况下, 我们需要备份的数据分为以下几种

• 数据

• 二进制日志, InnoDB事务日志

• 代码(存储过程、存储函数、触发器、事件调度器)

• 服务器配置文件

备份工具

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

设计合适的备份策略

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

• 直接cp,tar复制数据库文件

• mysqldump+复制BIN LOGS

• lvm2快照+复制BIN LOGS

• xtrabackup

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

1. 如果数据量较小, 可以使用第一种方式, 直接复制数据库文件

2. 如果数据量还行, 可以使用第二种方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果

3. 如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果

4. 如果数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份

实战演练

使用cp进行备份

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

查看数据库的信息

mysql> SHOW DATABASES;    #查看当前的数据库, 我们的数据库为employees
+set (USE employees; Database changed
mysql> SHOW TABLES;         #查看当前库中的表
+set (SELECT COUNT(*) FROM employees;   #由于篇幅原因, 我们这里只看一下employees的行数为300024
+set (FLUSH TABLES WITH READ LOCK;    #向所有表施加读锁
Query OK, 0 rows affected (0.00 sec) 

备份数据文件

[root/*    #这一步可以不做
[root@node1 ~]# cp -a /backup/* /var/lib/mysql/    #将备份的数据文件拷贝回去
[root@node1 ~]# service mysqld restart  #重启MySQL


#重新连接数据并查看

mysql> SHOW DATABASES;    #数据库已恢复
+--------------------+
| Database           |
+--------------------+
| infORMation_schema |
| employees          |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> USE employees;      

mysql> SELECT COUNT(*) FROM employees;    #表的行数没有变化
+----------+
| COUNT(*) |
+----------+
|   300024 |
+----------+
1 row in set (0.06 sec)


##完成 

使用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 DATABASES shell> mysqldump [options] shell> mysqldump [options] SHOW DATABASES;    #查看当前的数据库, 我们的数据库为employees
+set (USE employees; Database changed
mysql> SHOW TABLES;         #查看当前库中的表
+set (SELECT COUNT(*) FROM employees;   #由于篇幅原因, 我们这里只看一下employees的行数为300024
+set (SHOW MASTER STATUS@node1 ~]@node1 ~]@node1 ~]@node1 ~]@node1 ~]@node1 ~]or OSF disklabel
Building a new DOS disklabel with disk identifier in memory only, until you decide to write them.
After of course, the previous content wonto         switch and change display units to         sectors (command for help): n
Command action
   e   extended
   p   primary partition (default default value or +size{K,M,G} (default for help): t
Selected partition to list codes): of partition to for help): w
The 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 ~]BLKPG: Device or resource busy
error adding partition @node1 ~]@node1 ~]@node1 ~]@node1 ~]@node1 ~]@node1 ~]@node1 ~]@node1 ~]SHOW DATABASES;    #查看当前的数据库, 我们的数据库为employees
+set (USE employees; Database changed
mysql> SHOW TABLES;         #查看当前库中的表
+set (SELECT COUNT(*) FROM employees;   #由于篇幅原因, 我们这里只看一下employees的行数为300024
+set (@node1 lvm_data]@node1 lvm_data]@node1 lvm_data]write-protected, mounting read-only

[root@node1 lvm_data]@node1 lvm_snap]index  test
[root@node1 lvm_snap]@node1 ~]@node1 ~]
		

			

				备份过程快速、可靠； 
			
		
		

			

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

			

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

			

				自动实现备份检验； 
			
		
		

			

				还原速度快； 
			
		
	
	

		摘自马哥的文档 
	


	xtrabackup实现完全备份 


	

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

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


	备份过程 

[rootlog sequence number ***不用启动数据库也可以还原************* [rootdata/*   #删除数据 [root@node1 ~]# innobackupex R mysql.mysql /data/ [root@node1 ~]# ls /data/ -l MariaDB [(none)]> SHOW DATABASES;  #数据还原
+Database           |
+TEST1              |
| TEST2              |
| employees          |
| mysql              |
| performance_schema |
| test               |
+in set (0.00 sec) #关于xtrabackup还有很多强大的功能没有叙述、有兴趣可以去看官方文档 

	总结 


	



	

		

			

				备份方法 
			
			

				备份速度 
			
			

				恢复速度 
			
			

				便捷性 
			
			

				功能 
			
			

				一般用于 
			
		
	
	

		

			

				cp 
			
			

				快 
			
			

				快 
			
			

				一般、灵活性低 
			
			

				很弱 
			
			

				少量数据备份 
			
		
		

			

				mysqldump 
			
			

				慢 
			
			

				慢 
			
			

				一般、可无视存储引擎的差异 
			
			

				一般 
			
			

				中小型数据量的备份 
			
		
		

			

				lvm2快照 
			
			

				快 
			
			

				快 
			
			

				一般、支持几乎热备、速度快 
			
			

				一般 
			
			

				中小型数据量的备份 
			
		
		

			

				xtrabackup 
			
			

				较快 
			
			

				较快 
			
			

				实现innodb热备、对存储引擎有要求 
			
			

				强大 
			
			

				较大规模的备份 
			
		
	


	 


	其实我们还可以通过Master-Slave Replication 进行数据备份