mysql数据库备份

最新推荐文章于 2024-03-26 08:42:43 发布

途径日暮不赏丶

最新推荐文章于 2024-03-26 08:42:43 发布

阅读量316

点赞数

分类专栏： mysql 文章标签： mysql linux

本文链接：https://blog.csdn.net/weixin_45880055/article/details/113872587

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

文章目录

一、备份
二、tar实现数据库物理全量备份
三、Lvm快照实现数据库物理全量备份
四、mysqldump全量（逻辑备份） + LOGBIN 增量备份
五、表的备份

一、备份

（一）为什么需要备份数据

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

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

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

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

备份：能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其他地方；所有备份数据都应放在非数据库本地，而且建议有多份副本

冗余：数据有多份冗余，但不等于备份，只能防止机械故障带来的数据丢失，主要是用来做读写分离，不能防止人为误操作。例如主备模式、数据库集群

（二）数据的备份类型

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

完全备份
部分备份（又分为增量备份和差异备份）

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

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

增量备份
差异备份

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

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

（三）MySQL备份数据的方式

在MySQl中备份数据一般有几种方式：热备份、温备份、冷备份

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

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

存储引擎 MyISAM：热备 × 、温备 √ 、冷备 √
存储引擎 InnoDB：热备 √ 、温备 √ 、冷备 √

备份时还需要考虑对于MySQL数据库中数据的备份方式：

物理备份：一般就是通过**tar,cp等命令直接打包复制数据库的数据文件达到备份的效果；直接复制数据库文件，速率较快，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到异构系统中如Windows。
逻辑备份：一般就是通过特定工具从数据库中导出数据并另存备份(逻辑备份会丢失数据精度)；备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），恢复时将这些语句进行执行以达到恢复数据的效果。速率较慢，适用于中小型数据库，效率相对较低。（mysqldump、mydumper）

两种备份方式的特点：

在备份速度上两种备份要取决于不同的存储引擎
物理备份的还原速度非常快。但是物理备份的最小粒度只能做到表
逻辑备份保存的结构通常都是纯ASCII的，所以我们可以使用文本处理工具来处理
逻辑备份有非常强的兼容性，而物理备份则对版本要求非常高
逻辑备份也对保持数据的安全性有保证逻辑备份要对RDBMS产生额外的压力，而裸备份无压力
逻辑备份的结果可能要比源文件更大。所以很多人都对备份的内容进行压缩逻辑备份可能会丢失浮点数的精度信息

（四）备份策略

备份过程中必须考虑的因素：

数据的一致性：能不能保证数据的实时备份，数据在随时变更的同时进行备份
服务的可用性：能不能在保证服务可用的同时进行备份

当然保证数据的一致性是最重要的，所以在选择备份策略时要在保证数据一致性的基础上，再保证服务的可用性

备份什么?

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

数据
日志文件（比如事务日志，二进制日志）
存储过程，存储函数，触发器
配置文件（十分重要，各个配置文件都要备份）
用于实现数据库备份的脚本，数据库自身清理的crontab等……

备份工具

常用的几种备份工具：

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

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

直接cp,tar复制数据库文件
mysqldump+复制BIN LOGS
lvm2快照+复制BIN LOGS
xtrabackup

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

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

二、tar实现数据库物理全量备份

注：备份期间，服务不可用

备份过程：【完全物理备份】

停止数据库

[root@localhost ~]# systemctl stop mysql

tar 备份数据库

[root@localhost ~]# mkdir /backup
[root@localhost ~]# tar -czf /backup/`date +%F`_mysql_full.tar.gz /usr/local/mysql
# 备份文件应该是复制到其他服务器或者存储设备上

还原到新mysql服务器上的过程：

停止数据库

[root@localhost ~]# systemctl stop mysql

清理环境

[root@localhost ~]# rm -rf /usr/local/mysql

导入备份数据

[root@localhost ~]# tar -xf /back/2020-02-02_mysql_full.tar.gz -C /
[root@localhost ~]# chown -R mysql:mysql /usr/local/mysql
[root@localhost ~]# systemctl start mysql

对与增量内容可以使用二进制日志（即BINLOG）进行恢复

三、Lvm快照实现数据库物理全量备份

数据一致，服务可用

注：数据库的数据文件必须是一个逻辑卷的挂载，在mysql数据的lv（逻辑卷）和将要创建的snapshot（快照卷）必须在同一个VG（卷组），因此VG必须有一定的剩余空间

优点：

几乎热备（创建快照前锁表，创建完后立即释放）
支持所有存储引擎
备份速度快
无需使用昂贵的商业软件

缺点：

可能需要跨部门协调（使用操作系统级别的命令，DBA一般没权限）
无法预计服务停止时间
数据如果分布在多个卷上会比较麻烦

步骤：

数据库加全局读锁
LVM mysql快照（给mysql逻辑卷打快照snapshot）
释放数据库读锁
挂载快照卷（只读）
从快照卷中复制数据（cp、tar）
卸载并删除快照卷

如果mysql运行一段时间，数据并没有存数在lvm上，需要将现在的数据迁移到lvm
准备lvm及文件系统

lvcreate -n lv-mysql -L 2G datavg
mkfs.xfs /dev/datavg/lv-mysql

将数据迁移到LVM

systemctl stop mysql
mount /dev/data/lv-mysql /mnt/ #临时挂载点
cp -a /usr/local/mysql/* /mnt #将mysql原数据镜像到临时挂载点(其实就是拷进lv-mysql逻辑卷中)
umount /mnt/
vim /etc/fstab
/dev/datavg/lv-mysql /usr/local/mysql xfs defaults 0 0
mount -a
chown -R mysql:mysql /usr/local/mysql
systemctl start mysql

LVM快照备份流程（确保mysql服务开启二进制文件功能）：
加全局读锁

mysql> flush tables with read lock; # 该步执行完毕后，不能关闭该会话，否则会释放读锁，导致数据不一致

2.创建快照

mysql> SYSTEM lvcreate -L 500M -s -n lv-mysql-snap /dev/datavg/lv-mysql;
mysql> system mysql -p'123ABC.com' -e 'show master status' > /backup/`date +%F`mysql_position.txt # 记录下此时备份的位置

3.释放锁

mysql> unlock tables;

切记：以上三步必须在同一会话中完成，即：

[root@localhost ~]# echo "flush tables with read lock; SYSTEM lvcreate -L 500M -s -n lv-mysql-snap /dev/datavg/lv-mysql; " |  mysql -p'123ABC.com' # 会话结束自行解除读锁

从快照中备份

[root@localhost ~]# mount -o ro,nouuid /dev/datavg/lv-mysql-snap /mnt/
[root@localhost ~]# cd /mnt/
[root@localhost mnt]# tar -cf /backup/`date +%F`-mysql-all.tar ./*

移除快照

[root@localhost ~]# cd;umount /mnt/
[root@localhost ~]# lvremove -f /dev/datavg/lv-mysql-snap

将以上步骤使用脚本 + cron 实现：

[root@localhost ~]# vim mysql_back.sh
#!/bin.bash
#LVM mysqlbackup
back_dir=/backup/`date +%F`

[ -d $back_dir ] || mkdir -p $back_dir
echo "FLUSH TABLES WITH READ LOCK;SYSTEM lvcreate -L 500M -s -n lv-mysql-snap /dev/datavg/lv-mysql;" | mysql -p`123ABC.com`

mount -o ro,nouuid /dev/datavg/lv-mysql-snap /mnt/

rsync -a /mnt/ $back_dir

if [ $? -eq 0 ]
then
	umount /mnt/
	lvremove -f /dev/datavg/lv-mysql-snap 
fi

[root@localhost ~]# crontab -e
0 2 * * * usr/bin/sh /root/mysql_back.sh # 每天凌晨两点执行

LVM快照恢复流程：
1.停止数据库

systemctl stop mysql

2.清理环境

rm -rf /usr/local/mysql/*

3.导入数据

tar xf /backup/mysql_2020-02-02-mysql-all.tar -C /usr/local/mysql/

注意：使用tar解压前，要查看当时压缩时是否带路径，使用该tar -tf mysql_2020-02-02-mysql-all.tar | less命令进行查看，如果不带路径就按照以上解压，如果带路径，则解压时 -C 选项就要根据路径视情指定

4.修改权限

chown -R mysql:mysql /usr/local/mysql/

5.启动数据库

systemctl start mysql

对与增量内容可以使用二进制日志（即BINLOG）进行恢复

四、mysqldump全量（逻辑备份） + LOGBIN 增量备份

mysqldump常见用法：

mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
关于数据库名：
	-A，-all-databases							所有库
	school												数据库名
	school stu_info t1 							shcool数据库的表 stu_info、t1
	-B,--databases bbs test mysql 		多个数据库
	关于其他参数说明：
		-h.--host=name 连接的主机名
		-p,--password					数据库密码
		-P,--port= 							指定连接的端口
		--single-transaction 			InnoDB 一致性，服务可用性
		-x,--lock-all-tables 			MyISAM 一致性，服务可用性
		-l,--lock-tables 				对所有表添加读锁
		-E,--events 						备份事件调度器代码
		--opt 								同时启动各种高级选项
		-R,--routines 					备份存储过程和存储函数
		-F,--flush-logs 					备份之前刷新日志
		--triggers 							备份触发器
		--master-data=1|2 			该选项将会记录binlog的日志位置与文件名并追加到文件中（1是对追加的内容前不加 # 号注释，常用于主从复制时；2是对追加的内容前加 # 号注释）

先进行完全备份：

[root@localhost ~]#  mysqldump -p123ABC.com --all-databases --single-transaction --master-data=1 --flush-logs > ./`date +%F-%T`-mysql-all.sql

在备份后又对数据库中的数据进行了某些操作，然后由于某种原因数据库所有文件丢失（前提是BINLOG日志一直在实时备份至其他地方，即binlog文件有冗余）：

此时使用重装的mysql服务器，先恢复完全备份的内容：

mysqldump -p123ABC.com < 2021-03-09-22\:08\:41-mysql-all.sql

同时打开备份文件 2021-03-09-22:08:41-mysql-all.sql 查看此次完全备份的结束位置（一般在文件开头注释后的第一行，该内容是在备份时使用选项 --master-data=1 产生的），以确定后面使用二进制文件恢复的位置

vim 2021-03-09-22\:08\:41-mysql-all.sql
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=155;

可以看出是 binlog.000005 的 155 位置处

所以使用二进制文件恢复时为：

 mysqlbinlog --start-position=155 binlog.000005  | mysql -u root -p123ABC.com
# 如果还有大于5的二进制文件也要全部恢复，则为：
  mysqlbinlog --start-position=155 binlog.000005 binlog.000006 binlog.000007... | mysql -u root -p123ABC.com

逻辑备份恢复时，由于BINLOG功能开启，所以会记录以上在恢复过程中的 2021-03-09-22:08:41-mysql-all.sql 文件和 binlog.00000x 文件中的所有sql语句也会被记录到二进制文件中（因为恢复实质上就是执行备份文件中的sql语句）；所以，如果不需要这些重复的内容记录到二进制日志文件中的话，需要在恢复前，关闭mysql的二进制日志功能（即在配置文件中删除 log_bin 这一行，再启动服务；或着可以直接再完全备份的文件中的正文第一行添加 set log_bin=0 语句），或者直接登录到数据库中，set log_bin=0;在当前会话中关闭二进制日志功能，再使用source /path/to/完全备份文件名.sql，这种方式也不会将恢复时执行的sql语句记录到二进制日志文件中。所以如果是恢复较大的文件，可以使用该方法避免新生成很大的二进制文件，同时也避免不必要的i/o操作。

如果是人为误操作删除了数据库中的某些表，则不能直接使用所有二进制日志文件进行全部恢复，需要在二进制文件中找到 误操作 的语句，跳过误操作处进行恢复即可，流程：

比如在完备之后，在数据库中做了许多操作，其中不乏重启mysql服务等会截断二进制日志文件的操作，并且在对数据库的操作中，由于不慎删除了某个库，那么此时恢复的方法为：
	1. 先使用 mysqldump 进行完全备份的恢复（前提是在完全备份的时候，有使用--master-data选项，因为这样备份后会清楚的知道完全备份到二进制日志文件的精确位置）
	2. 然后查看完全备份文件中所备份到的二进制日志文件的精确位置，因为此时要开始使用 BINLOG 进行增量备份
	3. 使用 mysqlbinlog 查看完备后的二进制文件确定 【人为误操作】 的语句（即删库的sql语句），记住这些语句的位置和二进制文件名
	4. 使用 mysqlbinlog 的 --start-position= 和 --stop-position= 参数进行恢复（跳过误操作语句进行恢复，如果直接进行全部恢复，那么恢复后相当于误操作的语句同样被执行了，这样的话并不能达到预期目的）；如果不想使用以上两个参数进行恢复，可以在二进制日志文件中找到误操作语句，直接在二进制文件中将其完整删除，然后进行恢复即可；在增量备份过程中如果不想记录这些重复的恢复语句则需要在增量恢复前关闭二进制日志功能）
	5. 注意：在生产环境中，完备后的文件要在别的服务器上，包括二进制日志文件也要在别的服务器冗余备份。不管完备是逻辑备份还是物理备份都需要将备份后的二进制日志文件位置进行记录。物理的话，使用show master status;进行记录；mysqldump逻辑的话使用--master-data= 的选项，有时为了便于恢复可以使用 --flush logs选项对二进制文件进行截断，以生成新的二进制文件。

五、表的备份

表的导出

[root@mysql2 ~]# mkdir /backup
[root@mysql2 ~]# chown mysql.mysql /backup # 或者 chmod 
[root@mysql2 ~]# vim /etc/my.cnf
secure_file_priv=/backup
[root@mysql2 ~]# systemctl restart mysql

mysql> show variables like '%secure_file%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| secure_file_priv | /backup/ |
+------------------+----------+
1 row in set (0.35 sec)
mysql> select * from school.student;
+------+----------+--------+
| id   | name     | sex    |
+------+----------+--------+
|    1 | zhangsan | male   |
|    2 | xiaohua  | female |
|    3 | lisi     | male   |
|    4 | xiaohong | female |
+------+----------+--------+
4 rows in set (0.24 sec)
mysql> select * from school.student into outfile '/backup/student.table';
Query OK, 4 rows affected (0.39 sec)

[root@mysql2 backup]# ls
student.table
[root@mysql2 backup]# cat student.table 
1	zhangsan	male
2	xiaohua	female
3	lisi	male
4	xiaohong	female
# 使用mysql命令导出：
[root@mysql2 ~]# mysql -p123ABC.com -e "select * from shcool.student" > ./student
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql2 ~]# cat student 
id	name	sex
1	zhangsan	male
2	xiaohua	female
3	lisi	male
4	xiaohong	female
[root@mysql2 ~]# mysql -p123ABC.com --xml -e "select * from shcool.student" > ./student
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql2 ~]# cat student 
<?xml version="1.0"?>

<resultset statement="select * from shcool.student
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
	<field name="id">1</field>
	<field name="name">zhangsan</field>
	<field name="sex">male</field>
  </row>

  <row>
	<field name="id">2</field>
	<field name="name">xiaohua</field>
	<field name="sex">female</field>
  </row>

  <row>
	<field name="id">3</field>
	<field name="name">lisi</field>
	<field name="sex">male</field>
  </row>

  <row>
	<field name="id">4</field>
	<field name="name">xiaohong</field>
	<field name="sex">female</field>
  </row>
</resultset>
[root@mysql2 ~]# mysql -p123ABC.com --html -e "select * from shcool.student" > ./student
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql2 ~]# cat student 
<TABLE BORDER=1><TR><TH>id</TH><TH>name</TH><TH>sex</TH></TR><TR><TD>1</TD><TD>zhangsan</TD><TD>male</TD></TR><TR><TD>2</TD><TD>xiaohua</TD><TD>female</TD></TR><TR><TD>3</TD><TD>lisi</TD><TD>male</TD></TR><TR><TD>4</TD><TD>xiaohong</TD><TD>female</TD></TR></TABLE>

自定义输出分隔符：

mysql> select user,host from mysql.user into outfile '/backup/mysql.user' 
    -> FIELDS TERMINATED BY '---->';
Query OK, 4 rows affected (0.06 sec)
mysql> system cat /backup/mysql.user
mysql.infoschema---->localhost
mysql.session---->localhost
mysql.sys---->localhost
root---->localhost

定义换行符：

mysql> select user,host from mysql.user into outfile '/backup/mysql.user1' 
    -> lines terminated by '\t';
Query OK, 4 rows affected (0.06 sec)

mysql> system cat /backup/mysql.user1                                                    
mysql.infoschema	localhost	mysql.session	localhost	mysql.sys	localhost	root	localhost	mysql

表的导入：

mysql>  select * from school.student into outfile '/backup/student'; # 导出
Query OK, 4 rows affected (0.06 sec)

mysql> system cat /backup/student # 查看执行结果
1	zhangsan	male
2	xiaohua	female
3	lisi	male
4	xiaohong	female
mysql> delete from school.student; # 删除表中内容
Query OK, 4 rows affected (0.35 sec)

mysql>  select * from school.student; # 查看是否删除成功
Empty set (0.06 sec)
mysql> load data infile '/backup/student' into table school.student;
Query OK, 4 rows affected (0.32 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql>  select * from school.student;
+------+----------+--------+
| id   | name     | sex    |
+------+----------+--------+
|    1 | zhangsan | male   |
|    2 | xiaohua  | female |
|    3 | lisi     | male   |
|    4 | xiaohong | female |
+------+----------+--------+
4 rows in set (0.06 sec)
# 如果导出表时定义了分隔符，那么在导入时也同样需要指定

表的导入和导出只备份表的记录，不会备份表结构。因此需要通过 mysqldump 备份表结构，恢复时先恢复表结构，再导入数据。

途径日暮不赏丶

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql数据库备份

文章目录一、备份介绍为什么需要备份数据？数据的备份类型MySQL备份数据的方式备份策略一、备份介绍为什么需要备份数据？在生产环境中我们数据库可能会遭遇各种各样的不测从而导致数据丢失, 大概分为以下几种：硬件故障软件故障自然灾害黑客攻击误操作 (占比最大)所以, 为了在数据丢失之后能够恢复数据, 我们就需要定期的备份数据, 备份数据的策略要根据不同的应用场景进行定制, 大致有几个参考数值, 我们可以根据这些数值从而定制符合特定环境中的数据备份策略能够容忍丢失多少数据恢复数据需要多长
复制链接

扫一扫

专栏目录