一、数据库的重要性主要的体现

1、提高系统的高可用性和灾难可恢复性，在数据库系统奔溃的时候，没有数据库备份就没法找到数据。

2、使用数据库备份还原数据库是数据库是数据库系统奔溃是提供数据恢复最小代价的最优方案。

3、没有数据就没有一切，数据库备份就是一种防范于未然的强力手段，没有了数据，应用再花哨也是镜花水月。

二、备份的类别

对于现代互联网公司而言，存储在服务器数据库中的数据，逐步成为企业和公司的命脉，对企业和公司的生存发展具有十分重大的影响。为了保证数据库数据存储安全，确保不会因为误操作和不可抗力而破坏，通常我们都会对数据库中的数据进行备份。对MySQL数据库的备份，可以有很多种方式，我们接下来按照不同的分类标准，来讲解一下这些备份方式：

1、按照备份对数据库的影响分类

• 1、热备份（Hot Backup）。 指在数据库运行过程中进行备份，并且对数据库正常运行没有任何影响。
• 2、冷备份（Cold Backup）。 指在数据库停止运行后开始进行的备份。
• 3、温备份（Warm Backup）。 指在数据库运行过程中进行备份，但是会造成数据库性能下降，对数据库提供服务造成影响的备份方式。

2、按照备份的文件进行分类

• 1、逻辑备份。 采用逻辑备份的方式，备份出的数据通常是.sql类型的文件。备份后的内容可读且为文本文件。该方法一般用于数据库的升级、迁移或者对数据库表的批量修改等场景。该方法恢复时间较长。
• 2、裸文件备份。 指拷贝数据库的物理文件，采用这种方式，数据库恢复时间较短。

3、按照备份的方式进行分类

• 1、完全备份。 指的是对数据库进行完整的备份。
• 2、增量备份。 指的是在上一次备份的基础上，对更新的数据进行备份，而不是备份所有数据。
• 3、日志备份。 指的是对数据库的日志进行备份，MySQL主从同步架构中就是采用这种备份方式。

三、常见的备份方式

手动备份

1、使用MySQLDump进行MySQL备份

mysqldump是MySQL内置的工具，允许用户将数据库指定不同的选项备份到文件、服务器，甚至是压缩gzip文件中。mysqldump实用程序灵活，快速，可执行高级备份，并接受各种命令行参数，用户可通过这些参数来更改备份数据库的方式。

2、使用MySQLpump进行MySQL备份

MySQLpump类似于mysqldump，生成支持并行化的逻辑备份。MySQLpump以并行方式为多个数据库执行所有与转储相关的操作，这大大减少了执行逻辑备份所花费的时间。

但是，MySQLpump不备份performance_schema，sys schema和ndbinfo，因此您必须使用 – databases来命名它们，它也不会转储用户帐户定义。

mysqlpump –exclude-databases=% --users

3、使用AutoMySQLBackup进行MySQL备份

AutoMySQLBackup可以备份服务器上的数据库，无论是单个、多个还是所有数据库。每个数据库都保存在一个单独的文件中，可以压缩该文件。如何执行呢?只需下载一个文件bash脚本，保存并根据自己的喜好自定义，使其可执行。

手动运行脚本：./automysqlbackup.sh.2.5

4、使用mydumper进行MySQL备份

Mydumper是一个实用的软件程序，可以保证快速可靠的多线程MySQL备份过程。众所周知，Mydumper可以很好地处理大型数据库，提供并行性。用户需要创建如下所示的备份，然后将变量($)替换实变量，压缩文件夹并将其放在首选位置。

Mydumper

--database=$DB_NAME

--host=$DB_HOST

--user=$DB_PASS

--rows=500000

--compress

--build-empty-files

--compress-protocol

快速和自动备份

1、MySQL自动备份与ottomatik

Ottomatik使用备用降落伞为您的MySQL数据库提供了极好的备份解决方案。Ottomatik支持云或本地服务器，通过安装脚本，它会自动安装连接到Linux服务器的SSH密钥，还可以为备份作业选择多个服务器。

2、使用PHPMyAdmin进行MySQL备份

PHPMyAdmin是一个流行的应用程序，用于管理MySQL数据库，可以免费使用。很多服务提供商都使用PHPMyAdmin，有可能你自己都安装了却不自知。

备份数据库所需要做的就是打开PHPMyAdmin，选择文件或文件夹，然后单击“导出”链接，另外，也有“另存为”和“压缩”等在本地保存数据的选项。

3、使用文件系统快照进行MySQL备份

可以使用Veritas，LVM或ZFS等文件系统进行系统快照。要使用文件系统快照，从客户端程序执行具有读锁定的刷新表，然后从另一个shell执行运行mount vxfs snapshot。从第一个客户端解锁表，然后将文件从快照复制到目标文件夹，稍后卸载快照。

4、通过复制表文件进行备份

通过将表文件复制为*.MYD，*.MYI文件来备份MyISAM表，使用时需要停止服务器或锁定并刷新相关表：

FLUSH TABLES tbl_list WITH READ LOCK;

在数据库中复制文件时，读锁允许其他客户机继续查询表，而刷新则确保在备份过程之前将所有活动索引页写入磁盘。

无论完全备份还是增量备份，MySQL数据库备份都是必不可少的，而且随着数据库大小的增加，您可能还需要更改备份策略。

四、完全备份

1、关闭数据库，打包备份

 systemctl stop mysqld         #先关闭服务
 mkdir /backup/                #创建备份目录
 ​
 rpm -q xz                     #使用xz工具进行压缩，检查xz工具是否已安装
 yum install xz -y             #如果没安装，可以先yum安装
 tar Jcf /backup/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data  #打包数据库文件 /usr/local/mysql/data 为数据库文件存放目录
 ​
 cd /backup/        #切换到备份目录 
 ls                 #查看目录内容
 tar tf mysql_all_2022-11-29.tar.xz    #查看tar包内的文件 

2、完全恢复

将数据库迁移到另一台主机，测试完全恢复。

 #主机A，使用scp命令将tar包传给另一台主机B
 scp /backup/mysql_all_2022-11-29.tar.xz 192.168.37.135:/opt
 ​
 ##主机B的操作##
 systemctl stop mysqld     #关闭mysql
 cd /opt/
 mkdir /opt/bak/            #创建备份目录                  
 tar Jxf mysql_all_2022-11-29.tar.xz -C /opt/bak/  #将tar包解压到备份目录
 cd /opt/bak/usr/local/mysql   
 \cp -af data/ /usr/local/mysql   #将data目录复制到/usr/local/mysql/目录下，覆盖原有文件
 ​
 systemctl start mysqld       #启动mysql
 mysql -u root -p             #登录数据库查看
 show databases;

五、逻辑备份

在 MySQL 中，逻辑备份的最大优点是对于各种存储引擎都可以用同样的方法来备份；而物理备份则不同，不同的存储引擎有着不同的备份方法，因此，对于不同存储引擎混合的数据库，逻辑备份会简单一点。

1. 备份

MySQL 中的逻辑备份是将数据库中的数据备份为一个文本文件，备份的文件可以被查看和编辑。在 MySQL 中，可以使用 mysqldump 工具来完成逻辑备份。

1）备份一个或多个库

注意：备份用的目录需要提前创建，数据库也需要在启动状态

 mysqldump -uroot -p[密码] --databases 库名1 [库名2].. >/备份路径/备份文件名.sql#导出的就是数据库脚本文件
 ​
 示例:
 mysqldump -uroot -p --databases itzy > /opt/mysql_bak/itzy.sql    #完全备份一个库itzy
 mysqldump -uroot -p --databases itzy nmixx > /opt/mysql_bak/itzy-nmixx.sql   #完全备份多个库，itzy库和nmixx库

2）备份所有库

 mysqldump -u root -p[密码] --all-databases > / 备份路径/备份文件名.sql
 ​
 示例:
 mysqldump -u root -p --all-databases > /opt/mysql_bak/all.sql   #完全备份所有的库

3）备份指定库内的指定表

 mysqldump -u root -p[密码] [-d] 库名 表名1 [表名2] ... > /备份路径/备份文件名.sql
 #使用“-d"选项，说明只保存数据库的表结构
 #不使用“-d”选项，说明表数据也进行备份
 
示例： mysqldump -u root -p -d itzy info2 room > /opt/mysql_bak/inroom.sql

2、完全恢复

1）恢复数据库

 ##删除数据库##
 mysql -u root -p -e 'drop database itzy;'
 #"-e"选项，用于指定连接MySQL后执行的命令，命令执行完后自动退出
 ​
 mysql -u root -p -e 'SHOW DATABASES;'         #查看当前有哪些数据库
 ​
 ##恢复数据库##
 mysql -u root -p < /opt/mysql_bak/itzy.sql     #重定向输入库文件
 ​
 mysql -u root -p -e 'SHOW DATABASES';       #查看当前有哪些数据库

2）恢复数据表

 ##备份itzy库中的info2表##
 mysqldump -uroot -p itzy info2 > /opt/mysql_bak/itin.sql
 ​
 ##删除itzy库中的info2表##
 mysql -u root -p -e 'drop table itzy.info2';
 ​
 mysql -u root -p -e 'show tables from itzy;'     #查看
 ​
 ##恢复itzy库中的info2表##
 mysql -u root -p itzy < /opt/mysql_bak/itin.sql   #重定向导入备份文件，必须指定库名，且目标库必须存在
 ​
 mysql -u root -p -e 'show tables from itzy';

六、增量备份与恢复

增量备份

1、开启二进制日志功能

 vim /etc/my.cnf
 [mysqld]
 log-bin=mysql-bin    #开启二进制日志。如果使用相对路径，则保存在/usr/local/mysql/data/目录下
 binlog_format = MIXED     #可选，指定二进制日志(binlog)的记录格式为MIXED
 server-id = 1
 ​
 systemctl restart mysqld
 ls -l /usr/local/mysql/data/mysql-bin.*
 

2、插入数据，模拟数据变更

 insert into info2 values(1, 'zangao',122);
 insert into info2 values(2, 'gaojiasuo',133);

3、再次生成新的二进制日志文件

mysqladmin -u root -p flush-logs

4、查看二进制日志内容

cp /usr/local/mysql/data/mysql-bin.000002 /bak
mysqlbinlog --no-defaults --base64-output=decode-rows -v /bak/mysql-bin.000002

一般恢复

1、删除上面增加的两行数据

 delete from info2 where id=1;   #删除今天新增加的两条数据
 delete from info2 where id=2;
 ​
 #增量恢复（今天新增加的两条数据记录保存在mysql-bin.000002日志中）
 mysqlbinlog --no-defaults /bak/mysql-bin.000002 | mysql -u root -p
 ​
 mysql -u root -p -e 'select * from itzy。info2';  #查看表中数据

2、恢复数据

 mysql -u root -p -e 'drop table itzy.info2;'   #删除整个表
 mysql -u root -p -e 'show tables from itzy;'   #查看库中的表
 ​
 #先完全恢复历史数据
 mysql -u root -p itzy < /bak/itzy_2022-11.29.sql  
 
 #再进行增量恢复今天新增的2条数据（mysql-bin.000002日志中保存了今天新增加的两条数据记录）
 mysqlbinlog --no-defaults /bak/mysql-bin.000002 | mysql -u root -p
 ​
 mysql -u root -p -e 'select * from itzy.info2;'   #查看表中数据

断点恢复

发生更改后进行以下操作：

 mysqladmin -u root -p flush-logs        #刷新日志，生成新的二进制日志
 cp /usr/local/mysql/data/mysql-bin.000003 /bak/   #将前一个日志复制到/bak/目录下

 mysqlbinlog --no-defaults --base64-output=decode-rows -v /bak/mysql-bin.000003 > /bak/binlog.txt  #将二进制文件内容重定向到binlog.txt文件中

 delete from class where cid between 5 and 8;  #删除cid为5到8的数据记录
 ​
 cat /bak/binlog.txt    #查看二进制文件内容，找到目标位置点
 ​
 #从头开始恢复，在"竹笛"的SQL语句后面截止 
 mysqlbinlog --no-defaults --stop-position='5843' /bak/mysql-bin.000003 | mysql -u root -p
 ​
 mysql -u root -p -e 'select * from itzy.info2;' #查看表数据

基于时间断点恢复

 mysqlbinlog --no-defaults --start-datetime='时间点' 文件名 | mysql -u root -p
 #从某一个时间点开始恢复，一直到日志结尾
 ​
 mysqlbinlog --no-defaults --stop-datetime='时间点' 文件名 | mysql -u root -p
 #从日志开头，一直恢复到某一个时间点前结束
 ​
 mysqlbinlog --no-defaults --start-datetime='起始时间点' --stop-datetime'结束时间点' 文件名 | mysql -u root -p
 #从某一个时间点开始恢复，一直到某一个位置点前结束

时间格式："yyyy-mm-dd"

七、总结

1、完全备份

物理冷备份 >> 关闭Mysqld 服务 >> tar命令打包 >> data目录 >> 恢复就是解压tar包

逻辑备份 mysqldump -u-p--datebases 库1 库2 > xxx.sql

mysqldump -u -p --all-datebases  > Xxx.sql
mysqldump -u -p 库1 表1 表2 >XXx.sql

2、完全恢复

mysql -u -p <xxx.sql 恢复库
mysql-u -p 库名 < XXx.sql 恢复表

3、增量备份

1、开启二进制日志，设置二进制日志格式MIXED

2、进行一次完全备份，可每周备份一次，通过crontab -e

3、使用mysqladm -uroot -p flush-logs刷新分割出二进制日志，由于刷新之前的数据操作都记录在老的二进制日志里，所以需要把老的二进制日志复制到一个安全的目录中保管

4、可以通过mysqlbinlog --no-defaults --base64-output=decode-rows -v二进制日志文件名查看日志内容

5、可以通过mysqlbinlog --no-defaults二进制日志文件名l mysql -uroot -p恢复丢失的数据

4、位置恢复

 先完全恢复历史数据  mysql -u root -p 表名 < /bak/日志文件名.sql  

 再进行增量恢复数据  mysqlbinlog --no-defaults /bak/增量日志文件名 | mysql -u root -p

5、断点恢复

从某一个时间点开始恢复，一直到日志结尾

mysqlbinlog --no-defaults --start-datetime='时间点' 文件名 | mysql -u root -p   从日志开头，一直恢复到某一个时间点前结束

 mysqlbinlog --no-defaults --stop-datetime='时间点' 文件名 | mysql -u root -p

从某一个时间点开始恢复，一直到某一个位置点前结束

 mysqlbinlog --no-defaults --start-datetime='起始时间点' --stop-datetime'结束时间点' 文件名 | mysql -u root -p