MySQL的数据库备份与恢复

数据运维 2023-07-30 剑圣无痕 手机阅读
Scan me!

一、前言

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

二、为什么需要备份数据？

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

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

  • 硬件故障
  • 软件故障
  • 自然灾害
  • 黑客攻击
  • 误操作 (占比最大)

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

  • 能够容忍丢失多少数据
  • 恢复数据需要多长时间
  • 需要恢复哪一些数据

三、数据的备份类型

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

  • 完全备份：指的是备份整个数据集( 即整个数据库 )
  • 部分备份：指的是备份部分数据集(例如: 只备份一个表)
    • 增量备份：指的是备份自上一次备份以来(增量或完全)以来变化的数据; 特点: 节约空间、还原麻烦
    • 差异备份：指的是备份自上一次完全备份以来变化的数据 特点: 浪费空间、还原比增量备份简单

图片[1]-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

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

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

    • 八、实战演练

    1、使用cp进行备份

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

    查看数据库的信息

    		mysql> SHOW DATABASES;    #查看当前的数据库, 我们的数据库为employees
    		+--------------------+
    		| Database          |
    		+--------------------+
    		| information_schema |
    		| employees          |
    		| mysql              |
    		| test              |
    		+--------------------+
    		4 rows in set (0.00 sec)
    		 
    		mysql> USE employees;
    		Database changed
    		mysql> 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)

    备份数据文件

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

    模拟数据丢失并恢复

    		rm -rf /var/lib/mysql/*    #删除数据库的所有文件
    		service mysqld restart  #重启MySQL, 如果是编译安装的应该不能启动, 如果rpm安装则会重新初始化数据库
    		mysql> SHOW DATABASES;    #因为我们是rpm安装的, 连接到MySQL进行查看, 发现数据丢失了！
    		+--------------------+
    		| Database          |
    		+--------------------+
    		| information_schema |
    		| mysql              |
    		| test              |
    		+--------------------+
    		3 rows in set (0.00 sec)
    		 
    		rm -rf /var/lib/mysql/*    #这一步可以不做
    		cp -a /backup/* /var/lib/mysql/    #将备份的数据文件拷贝回去
    		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)

    2、使用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] --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;    #查看当前的数据库, 我们的数据库为employees
    		+--------------------+
    		| Database          |
    		+--------------------+
    		| information_schema |
    		| employees          |
    		| mysql              |
    		| test              |
    		+--------------------+
    		4 rows in set (0.00 sec)
    		 
    		mysql> USE employees;
    		Database changed
    		mysql> 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)

    使用mysqldump备份数据库

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

    3、使用lvm2快照备份数据

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

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

    部署lvm环境

    		添加硬盘; 这里我们直接实现SCSI硬盘的热插拔, 首先在虚拟机中添加一块硬盘, 不重启
    		 
    		ls /dev/sd*  #只有以下几块硬盘, 但是我们不重启可以让系统识别新添加的硬盘
    		/dev/sda  /dev/sda1  /dev/sda2
    		 
    		echo '- - -' > /sys/class/scsi_host/host0/scan 
    		echo '- - -' > /sys/class/scsi_host/host1/scan 
    		echo '- - -' > /sys/class/scsi_host/host2/scan 
    		 
    		ls /dev/sd*    #看！sdb识别出来了
    		/dev/sda  /dev/sda1  /dev/sda2  /dev/sdb
    		 
    		fdisk /dev/sdb  #分区,步骤省略
    		 
    		partx -a /dev/sdb  
    		BLKPG: Device or resource busy
    		error adding partition 1
    		 
    		##创建逻辑卷
    		pvcreate /dev/sdb1
    		  Physical volume "/dev/sdb1" successfully created
    		vgcreate myvg /dev/sdb1 
    		  Volume group "myvg" successfully created
    		lvcreate -n mydata -L 5G myvg 
    		  Logical volume "mydata" created.
    		 
    		mkfs.ext4 /dev/mapper/myvg-mydata  #格式化
    		mkdir /lvm_data
    		mount /dev/mapper/myvg-mydata /lvm_data  #挂载到/lvm_data
    		 
    		 
    		vim /etc/my.cnf    #修改mysql配置文件的datadir如下
    		 
    		datadir=/lvm_data
    		 
    		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 changed
    		mysql> 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/  #挂载snap
    		mount: block device /dev/mapper/myvg-mydata--snap is write-protected, mounting read-only
    		 
    		[root@node1 lvm_data]# cd /lvm_snap/
    		[root@node1 lvm_snap]# ls
    		employees  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
    		 
    		umount /lvm_snap/  #卸载snap
    		lvremove myvg mydata-snap  #删除snap

    恢复数据

    		[root@node1 lvm_snap]# rm -rf /lvm_data/*
    		service mysqld start    #启动MySQL, 如果是编译安装的应该不能启动(需重新初始化), 如果rpm安装则会重新初始化数据库
    		 
    		 
    		mysql> SHOW DATABASES;  #查看数据库, 数据丢失!
    		+--------------------+
    		| Database          |
    		+--------------------+
    		| information_schema |
    		| mysql              |
    		| test              |
    		+--------------------+
    		3 rows in set (0.00 sec)
    		 
    		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  test
    		 
    		mysql> SHOW DATABASES;  #数据恢复了
    		+--------------------+
    		| Database          |
    		+--------------------+
    		| information_schema |
    		| employees          |
    		| mysql              |
    		| test              |
    		+--------------------+
    		4 rows in set (0.00 sec)
    		 
    		##完成

    4、使用Xtrabackup备份

    九、阿里云RDS MYSQL物理备份恢复

    1、全量全库恢复

    以恢复RDS MySQL 5.6到ubuntu 18.04中为例

    ①下载物理备份文件

    在阿里云控制台RSD实例的备份与恢复页面下载要恢复的物理备份文件

    ②解压

    解压后文件很大，注意磁盘容量

    tar -izxvf .tar.gz -C /data/mysql/data

    ③安装Percona XtraBackup

    注意Percona XtraBackup版本

    • MySQL 5.6及之前的版本需要安装 Percona XtraBackup 2.3，安装指导请参见官方文档Percona XtraBackup 2.3。
    • MySQL 5.7版本需要安装 Percona XtraBackup 2.4，安装指导请参见官方文档Percona XtraBackup 2.4。
    • MySQL 8.0版本需要安装 Percona XtraBackup 8.0，安装指导请参见官方文档Percona XtraBackup 8.0。
    		# ubuntu 安装Percona XtraBackup2.3
    		wget https://repo.percona.com/apt/percona-release_0.1-6.$(lsb_release -sc)_all.deb
    		dpkg -i percona-release_0.1-6.bionic_all.deb
    		apt-get update
    		apt-get install percona-xtrabackup
    		 
    		# CentOS 安装Percona XtraBackup2.3
    		yum install -y perl rsync perl-Data-Dumper
    		wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.5/binary/redhat/7/x86_64/percona-xtrabackup-2.3.5-1.el7.x86_64.rpm
    		yum clean all 
    		yum repolist
    		yum localinstall percona-xtrabackup-2.3.2-1.el7.x86_64.rpm

    ④安装MySQL

    Deb安装

    		# 安装MySQL 5.6 
    		wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-server_5.6.46-1debian8_amd64.deb
    		wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-client_5.6.46-1debian8_amd64.deb
    		wget https://downloads.mysql.com/archives/get/p/23/file/mysql-server_5.6.46-1debian8_amd64.deb
    		wget https://downloads.mysql.com/archives/get/p/23/file/mysql-client_5.6.46-1debian8_amd64.deb
    		dpkg -i mysql*

    YUM 安装

    bash -c 'cat > /etc/yum.repos.d/mysql.repo