MySQL--数据库备份

前言：本博客仅作记录学习使用，部分图片出自网络，如有侵犯您的权益，请联系删除

一、为什么要备份

备份：能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。
冗余：数据有多份冗余，但不等备份，只能防止机械故障带来的数据丢失，例如主备模式、数据库集群。

二、MySQL数据备份需要重视的内容

备份内容：databases、 Binlog、 my.cnf
所有备份数据都应放在非数据库本地，而且建议有多份副本。
测试环境中做日常恢复演练，恢复较备份更为重要。

备份过程必须考虑因素：

 1. 数据的一致性2. 服务的可用性Mysql A (读写) 压力比较大|Mysql B (读) 压力比较小 从节点做备份

三、MySQL备份类型

1、物理备份

直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。

（1）热备（hot backup）

在线备份，数据库处于运行状态，这种备份方法依赖于数据库的日志文件；对应用基本无影响（但是性能还是会有下降,所以尽量不要在主库上做备份,在从库上做）

（2）冷备（cold backup）

备份数据文件，需要停机，是在关闭数据库的时候进行的；

（3）温备（warm backup）

针对myisam的备份(myisam不支持热备),备份时候实例只读不可写，数据库锁定表格（不可写入但可读）的状态下进行的
对应用影响很大
通常加一个读锁

2、逻辑备份

备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库，效率相对较低。

3、物理和逻辑备份的区别

	逻辑备份	物理备份
备份方式	备份数据库建表、建库、插入sql	备份数据库物理文件
优点	语句备份文件相对较小,只备份表中的数据与结构	恢复速度比较快
缺点	恢复速度较慢(需要重建索引,存储过程等)	备份文件相对较大（备份表空间，包含数据与索引）
对业务影响	I/0负载加大	I/O负载加大
代表工具	mysqldump	ibbackup、xtrabackup

四、MySQL备份工具

 1、ibbackup官方备份工具；收费；物理备份2、xtrabackup开源社区备份工具；开源免费；物理备份3、mysqldump官方自带备份工具；开源免费；逻辑备份（速度慢）4、mysqlbackupmysql 官方备份工具；innodb 引擎的表mysqlbackup 可以进行热备；非innodb 表mysqlbackup 就只能温备；物理备份，备份还原速度快；适合大规模数据使用；

五、物理备份的方式

1、完全备份——完整备份

每次对数据进行完整的备份，即对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。

优点：备份与恢复操作简单方便，恢复时一次恢复到位，恢复速度快

缺点：占用空间大，备份速度慢

2、增量备份

每次备份上一次备份到现在产生的新数据；只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化；

特点：因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份起按备份时间顺序，逐个备份版本进行恢复，恢复时间长，如中间某次的备份数据损坏，将导致数据的丢失。

3、差异备份

只备份跟完整备份不一样的；备份那些自从第一次次完全备份之后被修改过的所有文件，备份的时间起点是从第一次的完整备份起，且以后每次备份都是和第一次完整备份进行比较（注意是第一次，不是上一次），备份自第一次完整备份以来所有的修改过的文件；备份数据量会越来越大；

特点：占用空间比增量备份大，比完整备份小，恢复时仅需要恢复第一个完整版本和最后一次的差异版本，恢复速度介于完整备份和增量备份之间。

六、percona工具

Xtrabackup 是开源免费的支持MySQL 数据库热备份的软件，在 Xtrabackup 包中主要有Xtrabackup 和innobackupex 两个工具。其中 Xtrabackup 只能备份 InnoDB 和 XtraDB 两种引擎; innobackupex则是封装了Xtrabackup，同时增加了备份MyISAM引擎的功能。它不暂停服务创建Innodb热备份；

Software Downloads - Percona

1、安装xtrackbackup

百度自行搜索；

（1）上传peronaRPM包

 # yum -y install percona-xtrabackup-24-2.4.5-1.el7.x86_64.rpm

2、完全备份流程

 1、systemctl stop mysqld     # 关闭数据库2、mkdir /xtrabackup/        # 创建备份目录3、innobackupex --user=root --password=123 /xtrabackup/      # 备份4、innobackupex --apply-log /xtrabackup/<要恢复的目录>         # 准备恢复数据5、innobackupex --copy-back /xtrabackup/<要恢复的目录>         # 恢复数据6、chown mysql.mysql /data -R                                # 添加权限

具体示例演示：

 # 数据库备份[root@centos /]# innobackupex --user=root --password=123 /xtrabackup/# 备份完成，在/xtrabackup/目录下能看到备份好的目录[root@centos /]# cd /xtrabackup/[root@centos xtrabackup]# ls2024-07-26_15-41-02--------------------------------------------------------# 实验：删除数据库中的数据[root@centos /]# rm -rf /data/mysql/data/       # 删除完数据，可进入数据库查看库进行验证，开始恢复数据[root@centos /]# innobackupex --apply-log /xtrabackup/2024-07-26_15-41-02[root@centos /]# innobackupex --copy-back /xtrabackup/2024-07-26_15-41-02[root@centos /]# chown mysql.mysql /data -R # 恢复完成[root@centos /]# systemctl restart mysqld[root@centos /]# mysqlmysql> show databases;

3、增量备份流程

基本语法：

 1、systemctl stop mysqld2、innobackupex --user=root --password=123 /xtrabackup/ # 完全备份3、innobackupex --user=root --password=123 --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/完全备份的目录4、innobackupex --user=root --password=123 --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/上一次增量的备份目录5、innobackupex --apply-log --redo-only /xtrabackup/全备目录6、innobackupex --apply-log --redo-only /xtrabackup/全备 --incremental-dir=/xtrabackup/要恢复的上一次增量目录7、innobackupex --copy-back /xtrabackup/全备

具体示例演示：

 [root@centos /]# innobackupex --user=root --password=123 /xtrabackup/   #全备 [root@centos /]# cd /xtrabackup     # 检查第一次全备目录[root@centos xtrabackup]# ls2024-07-26_16-54-18# 开始实验，创建周一sunday库mysql> create database sunday;# 第一次备份选用上个备份目录，这里即全备目录[root@centos /]# innobackupex --user=root --password=123 --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2024-07-26_16-54-18   # 查看目录，发现多了带有sunday库的目录[root@localhost xtrabackup]# ls             2024-07-26_16-54-18  2024-07-26_17-01-01# 再创建一个周二tuesday库mysql> create database tuesday;# 第二次增量备份选用上次目录，即从sunday库备份的目录[root@centos /]# innobackupex --user=root --password=123 --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2024-07-26_17-01-01   [root@localhost xtrabackup]# ls         # 查看，多出的目录即带有tuesday的备份目录2024-07-26_16-54-18  2024-07-26_17-01-01  2024-07-26_17-03-34# 增加权限并重启mysqld服务[root@centos /]# rm -rf /data/mysql/data[root@centos /]# chown -R mysql.mysql /data[root@centos /]# systemctl restart mysqldmysql> show databases;      # 检查数据库，发现无数据库;开始恢复[root@centos /]# innobackupex --apply-log --redo-only /xtrabackup/2024-07-26_16-54-18[root@centos /]# innobackupex --apply-log --redo-only /xtrabackup/全备 --incremental-dir=/xtrabackup/2024-07-26_17-03-34  # 这里选择恢复tuesday备份目录mysql> show databases;      # 检查数据库，发现数据库中连带monday库一起恢复+--------------------+| Database           |+--------------------+| information_schema || monday             || mysql              || performance_schema || sys                || tuesday            |+--------------------+

4、差异备份流程

七、mysqldump逻辑备份

推荐优先使用

1、常用备份选项

 -A, --all-databases #备份所有库-B, --databases #备份多个数据库-F, --flush-logs #备份之前刷新binlog日志--default-character-set #指定导出数据时采用何种字符集，如果数据表不是采用默认的latin1字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。--no-data，-d #不导出任何数据，只导出数据库表结构。--lock-tables #备份前，锁定所有数据库表--single-transaction #保证数据的一致性和服务的可用性-f, --force #即使在一个表导出期间得到一个SQL错误，继续。

使用 mysqldump 备份数据库时避免锁表:

对一个正在运行的数据库进行备份请慎重！！如果一定要在服务运行期间备份，可以选择添加 --single-transaction选项，类似执行： mysqldump --single-transaction -u root -p123456 dbname > mysql.sql

2、备份表

 语法: # mysqldump -u root -p1 db1 t1 > /db1.t1.bak[root@mysql-server ~]# mkdir /home/back             # 创建备份目录[root@mysql-server ~]# mysqldump -uroot -p'qf123' company employee5 >/home/back/company.employee5.bak# 备份多个表：语法: mysqldump -u root -p1 db1 t1 t2 > /db1.t1_t2.bak[root@mysql-server ~]# mysqldump -uroot -p'qf123' company new_t1 new_t2 >/home/back/company.new_t1_t2.bak

3、备份库

 备份一个库：相当于将这个库里面的所有表全部备份。语法: # mysqldump -u root -p1 db1 > /db1.bak[root@mysql-server ~]# mysqldump -uroot -p'qf123' company >/home/back/company.bak备份多个库：语法：mysqldump -u root -p1 -B db1 db2 db3 > /db123.bak[root@mysql-server ~]# mysqldump -uroot -p'qf123' -B company testdb >/home/back/company_testdb.bak备份所有的库：语法：# mysqldump -u root -p1 -A > /alldb.bak[root@mysql-server ~]# mysqldump -uroot -p'qf123' -A > /home/back/allbase.bak

4、恢复数据库和表

为保证数据一致性，应在恢复数据之前停止数据库对外的服务,停止binlog日志因为binlog使用binlog日志恢复数据时也会产生binlog日志。

为实验效果先将刚才备份的数据库和表删除了。登陆数据库：

 [root@mysql-server ~]# mysql -uroot -pqf123mysql> show databases;mysql> drop database company;mysql> \q

恢复库

 登陆mysql创建一个库mysql> create database company;恢复：[root@mysql-server ~]# mysql -uroot -p'qf123' company < /home/back/company.bak

恢复表

 登陆到刚才恢复的库中将其中的一个表删除掉mysql> show databases;mysql> use companymysql> show tables;+-------------------+| Tables_in_company |+-------------------+| employee5 || new_t1 || new_t2 |+-------------------+mysql> drop table employee5;开始恢复:mysql> set sql_log_bin=0; #停止binlog日志Query OK, 0 rows affected (0.00 sec)mysql> use company;mysql> source /home/back/company.employee5.bak; -------加路径和备份的文件恢复方式二：# mysql -u root -p1 db1 < db1.t1.bak库名 备份的文件路径[root@mysql-server ~]# mysql -uroot -p'qf123' company </home/back/company.employee5.bak

5、备份及恢复表结构

备份 linux中执行

 mysqldump -uroot -p123 库 > 备份路径     mysqldump -uroot -p123 库 表1 表2 > 备份路径   # 备份一个库里的多张表mysqldump -uroot -p123 -B 库1 库2 > 备份路径  # 备份多个库mysqldump -uroot -p123 -A > 备份路径    # 备份所有数据

还原

 mysql -uroot -p 库 < 备份数据    # 恢复单个库需要手动创建库mysql -uroot -p  < 备份数据     # 如果还原多个库或者所有数据，无需手动创建库

seuce还原

在数据库中，执行 source 备份文件

备份恢复表结构

 1.备份表结构：语法：mysqldump -uroot -p123456 -d database table > dump.sql[root@mysql-server ~]# mysqldump -uroot -p'qf123' -d company employee5 >/home/back/emp.bak恢复表结构：登陆数据库创建一个库mysql> create database t1;语法：# mysql -u root -p1 -D db1 < db1.t1.bak[root@mysql-server ~]# mysql -uroot -p'qf123' -D t1 < /home/back/emp.bak

通过binlog日志恢复数据

先开启binl 重启服务
查看正在使用的binlog，找到binlog日志 /data/mysql/data /var/lib/mysql /usr/local/mysql
找到想要恢复数据的偏移量
linux下，mysqlbinlog --start-position 844899 --stop-position 844994 137.000003 |mysql -uroot
在数据库中查看数据是否恢复