看完这篇都有DBA级别的MySQL备份经验了,再也不害怕删库跑路了

news/2024/10/30 9:33:33/

        在做运维的路上,相信大家会跟数据库经常打交道吧,那么数据库最重要的时候什么呢,那么肯定是备份了,这篇文章主要内容是介绍了,MySQL的几种常见的备份方式,主要是分为两种,第一种是基于MySQL提供的工具来进行备份,还有一种是基于使用第三方工具来进行备份,分别完成完整备份和增量备份。

目录

一,MySQL备份工具

1.MySQL工具完整备份

2.MySQL工具增量备份

二,第三方工具备份

1.xtrabackup工具介绍

2.xtrabackup安装

3.正式环境(xtrabackup完全+xtrabackup增量)

4.innobackupex全库+innobackupex增量备份


一,MySQL备份工具

1.MySQL工具完整备份

1.备份命令

[root@localhost ~]# mysqldump -help
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

2.备份库中单个表

[root@localhost ~]# mysqldump -uroot -p1234.Com test TB_ORDER > /tmp/test-TB_ORDER.sql

3.备份整个库中的所有表

[root@localhost ~]# mysqldump -uroot -p1234.Com  test > /tmp/test-TB_ORDER.sql

4.备份所有的库

[root@localhost ~]# mysqldump -uroot -p1234.Com --all-databaes > /tmp/all.sql 

5.备份一个库多个表

[root@localhost ~]# mysqldump -uroot -p1234.Com itcast tb_app tb_log > /tmp/itcast-tb_app-tb-log.sql

6.备份单个库

[root@localhost ~]# mysqldump -uroot -p1234.Com --databases testdump1 > /tmp/testdump1.sql

2.MySQL工具增量备份

1.查看日志操作

mysql> show variables like '%log_bin%'; #查看二进制日志存放位置和是否开启等
mysql> show master logs;        #查看系统所有二进制文件
mysql> show binlog events in 'mybin.000001';        #查看二进制文件的内容
mysql> flush binary logs;                #刷新日志文件
mysql> reset master;                        #清除二进制文件

2.二进制日志增量备份导出

        因为当做完完整备份后,需要对以后的数据进行备份,所以需要来进行对以后的日志信息来进行备份,可以对整个日志文件来进行备份,也可以针对完整备份日志之后的位置或者时间来进行备份。

1.所有日志导出

[root@localhost ~]# mysqlbinlog /var/lib/mysql/mybin.000001 > /tmp/mybin01.sql

2.日志位置导出

[root@localhost ~]# mysqlbinlog --start-position=7390 --stop-position=8276 /var/lib/mysql/mybin.000003 > /tmp/mybinpos.sql

3.日志时间导出

[root@localhost ~]# mysqlbinlog --start-datetime="23-05-07 16:07:21" --stop-datetime="23-05-08 10:03:24"

4.备份导入

[root@localhost ~]# mysql -uroot -p1234.Com < mybin.sql

二,第三方工具备份

        在第三方工具方面我使用的是xtrabackup,为啥使用它呢,下面我们详细介绍一下

1.xtrabackup工具介绍

        Xtrabackup是由percona开源的免费数据库热备份软件,它能对InnoDB数据库和XtraDB存储引擎的数据库非阻塞地备份(对于MyISAM的备份同样需要加表锁);mysqldump备份方式是采用的逻辑备份,其最大的缺陷是备份和恢复速度较慢,如果数据库大于50G,mysqldump备份就不太适合。

        Xtrabackup安装完成后有4个可执行文件,其中2个比较重要的备份工具是innobackupex、xtrabackup

1)xtrabackup 是专门用来备份InnoDB表的,和mysql server没有交互;

2)innobackupex 是一个封装xtrabackup的Perl脚本,支持同时备份innodb和myisam,但在对myisam备份时需要加一个全局的读锁。

3)xbcrypt 加密解密备份工具

4)xbstream 流传打包传输工具,类似tar

1.Xtrabackup优点

1)备份速度快,物理备份可靠

2)备份过程不会打断正在执行的事务(无需锁表)

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

4)自动备份校验

5)还原速度快

6)可以流传将备份传输到另外一台机器上

7)在不增加服务器负载的情况备份数据

2.Xtrabackup备份原理

        备份开始时首先会开启一个后台检测进程,实时检测mysq redo的变化,一旦发现有新的日志写入,立刻将日志记入后台日志文件xtrabackup_log中,之后复制innodb的数据文件一系统表空间文件ibdatax,复制结束后,将执行flush tables with readlock,然后复制.frm MYI MYD等文件,最后执行unlock tables,最终停止xtrabackup_log

备份成功输出如下提示信息

xtrabackup: Transaction log of lsn (2543172) to (2543181) was copied.

171205 10:17:52 completed OK!

3.Xtrabackup增量备份介绍

xtrabackup增量备份的原理是:

1)、首先完成一个完全备份,并记录下此时检查点LSN;

2)、然后增量备份时,比较表空间中每个页的LSN是否大于上次备份的LSN,若是则备份该页并记录当前检查点的LSN。

增量备份优点:

1)、数据库太大没有足够的空间全量备份,增量备份能有效节省空间,并且效率高;

2)、支持热备份,备份过程不锁表(针对InnoDB而言),不阻塞数据库的读写;

3)、每日备份只产生少量数据,也可采用远程备份,节省本地空间;

4)、备份恢复基于文件操作,降低直接对数据库操作风险;

5)、备份效率更高,恢复效率更高。

2.xtrabackup安装

1.官网下载rpm包

https://www.percona.com/software/mysql-database/percona-xtrabackup

2.上传到本地进行安装

yum localinstall percona-xtrabackup-24-2.4.28-1.el7.x86_64.rpm -y

3.创建完全备份和增量存放文件

mkdir -p /opt/mysqlbackup/{full,inc}
​
full:全备存放的目录;inc:增量备份存放的目录 1)完全备份

3.正式环境(xtrabackup完全+xtrabackup增量)

完整备份命令格式:
xtrabackup --default-file=/etc/my.cnf --user=root --password='1234.Com' --port=3306 --backup --target-dir=/full/full_$(date %Y%m%d)
选项:
--default-file:mysql备份文件
--user:用户名
--password:密码
--post:端口
--backup:备份
--target-dir:备份位置增量备份命令格式:
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=1234.Com --port=3306 --backup --target-dir=/inc/inc_$(date +%F) --incremental-basedir=/full/full_20230519/选项:
--incremental-basedir:上一次完整备份或者是增量备份的文件准备完全还原命令格式:
xtrabackup --default-file=/etc/my.cnf --prepare --user=root --password='1234.Com' --apply-log-only --target-dir=/full/full_20230519/准备增量还原命令格式:
xtrabackup --defaults-file=/etc/my.cnf --prepare --user=root --password='1234.Com' --apply-log-only --target-dir=/full/full_20230519/ --incremental-dir=/inc/inc_2023-05-19/
选项:
--apply-log-only:进行读取日志保证一致性
--incremental-dir:增量备份目录使用基于上一次备份的文件位置开始还原命令格式:
xtrabackup --defaults-file=/etc/my.cnf --prepare --user=root --password=1234.Com --target-dir=/full/full_20230519/

1.完整备份

[root@localhost ~]# xtrabackup --default-file=/etc/my.cnf --user=root --password='1234.Com' --port=3306 --backup --target-dir=/opt/mysqlbackup/full/full_$(date %Y%m%d)
选项:
--default-file:mysql备份文件
--user:用户名
--password:密码
--post:端口
--backup:备份
--target-dir:备份位置

2.进行操作表,然后进行增量备份

mysql> insert into TB_ORDER values(8,'888');
Query OK, 1 row affected (0.01 sec)
​
mysql> insert into TB_ORDER values(9,'999');
Query OK, 1 row affected (0.00 sec)

增量备份

[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --user=root --password=1234.Com --port=3306 --backup --target-dir=/opt/mysqlbackup/inc/inc_$(date +%F) --incremental-basedir=/opt/mysqlbackup/full/full_20230519/
​
选项:
--incremental-basedir:上一次完整备份或者是增量备份的文件

3.删除数据并还原

mysql> delete from TB_ORDER where id=8;

还原数据

#准备完整备份,进行读取日志保证一致性
[root@localhost ~]# xtrabackup --default-file=/etc/my.cnf --prepare --user=root --password='1234.Com' --apply-log-only --target-dir=/opt/mysqlbackup/full/full_20230519/
​
#准备完整备份,进行读取日志保证一致性
[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=root --password='1234.Com' --apply-log-only --target-dir=/opt/mysqlbackup/full/full_20230519/ --incremental-dir=/opt/mysqlbackup/inc/inc_2023-05-19/
选项:
--apply-log-only:进行读取日志保证一致性
--incremental-dir:增量备份目录使用基于上一次备份的文件位置
​
#开始还原
[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=root --password=1234.Com --target-dir=/opt/mysqlbackup/full/full_20230519/
[root@localhost full_20230519]# rsync -rvt --exclude 'xtrbackup' --exclude 'xtrabackup_logfile' ./ /var/lib/mysql/
[root@localhost full_20230519]# chown -R mysql: /var/lib/mysql/
[root@localhost full_20230519]# systemctl restart mysqld

查看效果

[root@localhost full_20230519]# mysql -uroot -p1234.Com
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
​
Database changed
mysql> select * from TB_ORDER;
+---------+--------------+
| id      | title        |
+---------+--------------+
|       6 | 3123         |
|       7 | 3123         |
|       4 | 4            |
|       5 | 5            |
|       8 | 888          |
|       9 | 999          |
|       1 | goods1       |
|       2 | goods2       |
|       3 | goods3       |
| 5000000 | goods5000000 |
+---------+--------------+
10 rows in set (0.00 sec)

4.innobackupex全库+innobackupex增量备份

xtrabackup与innobackupex的区别?

        xtrabackup可以在不加锁的情况下备份innodb数据表,不过此工具不能操作myisam。
innobackupex是一个封装了xtrabackup的脚本,能同时处理innodb和myisam,但在处理myisam时需要加一个读锁。

        按如上的介绍,由于操作myisam时需要加读锁,这会堵塞线上服务的写操作,而innodb没有这样的限制,所以数据库中innodb表类型所占的比例越大,则越有利。实际应用中一般是直接使用innobackupex。

 innobackupex备份格式:

完整备份命令格式:
innobackupex --defaults-file=/etc/my.cnf --user=root --password=1234.Com /dir  --no-timestamp
选项:/dir:完整备份文件存放位置--no-timestamp:文件名不自动生成时间戳增量备份命令格式:
innobackupex --incremental /dir --incremental-basedir=/dump_dir --user=root --password=1234.Com --no-timestamp
选项:/dir:增量备份名/dump_dir:上一次完整备份或增量备份文件名恢复完整备份命令格式:
innobackupex --apply-log --redo-only /dir
选项:
--apply-log:进行读取日志保证一致性
/dir:完整日志备份位置恢复增量备份命令格式:
innobackupex --apply-log --redo-only /full_dir --incremental-dir=/inc_dir
选项:/full_dir:完整备份文件位置/inc_dir:增量备份文件位置恢复备份命令格式:
innobackupex --defaults-file=/etc/my.cnf --user=root --password=1234.Com --copy-back /dir
选项:--copy-back:进行备份dir:完整备份位置

1.完整备份

[root@localhost full_20230519]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=1234.Com /opt/mysqlbackup/full/inno_full$(date +%F) --no-timestamp
​
选项:
--notimestamp:不自动生成时间戳

2.进入录入数据

mysql> insert into TB_ORDER values(10,'10qwe');
Query OK, 1 row affected (0.01 sec)
​
mysql> insert into TB_ORDER values(11,'11qwe');
Query OK, 1 row affected (0.00 sec)

3.再次进行增量备份

mysql> insert into TB_ORDER values(10,'10qwe');
Query OK, 1 row affected (0.01 sec)
​
mysql> insert into TB_ORDER values(11,'11qwe');
Query OK, 1 row affected (0.00 sec)

4.删除数据

mysql> delete from TB_ORDER where id = 10;
Query OK, 1 row affected (0.00 sec)
​
mysql> delete from TB_ORDER where id = 11;
Query OK, 1 row affected (0.00 sec)

5.恢复数据

[root@localhost full_20230519]# innobackupex --apply-log --redo-only /opt/mysqlbackup/full/inno_full2023-05-19/
[root@localhost ~]# innobackupex --apply-log --redo-only /opt/mysqlbackup/full/inno_full2023-05-19/ --incremental-dir=/opt/mysqlbackup/inc/innodb_inc2023-05-19/
#选项:
#--redo-only:用于准备增量备份内容把书数据合并到全备目录,配合inncremental-dir备份目录使用,基于全备份进行第一次增量备份。
​
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# rm -fr /var/lib/mysql/*
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=1234.Com --copy-back /opt/mysqlbackup/full/inno_full2023-05-19/
[root@localhost ~]# chown -R mysql: /var/lib/mysql/
[root@localhost ~]# systemctl start mysqld

查看恢复情况

mysql> select * from TB_ORDER;
+---------+--------------+
| id      | title        |
+---------+--------------+
|      10 | 10qwe        |
|      11 | 11qwe        |
|       6 | 3123         |
|       7 | 3123         |
|       4 | 4            |
|       5 | 5            |
|       8 | 888          |
|       9 | 999          |
|       1 | goods1       |
|       2 | goods2       |
|       3 | goods3       |
| 5000000 | goods5000000 |
+---------+--------------+
12 rows in set (0.00 sec)


http://www.ppmy.cn/news/92285.html

相关文章

YOLOv8与 DeepSORT 多目标检测的案例

这个YoLov8强大在于不用显卡也可以识别的很快&#xff0c;我是用10年前的笔记本 Core i7-4710 CPU8G内存 准备代码环境 git地址&#xff1a;https://github.com/MuhammadMoinFaisal/YOLOv8-DeepSORT-Object-Tracking 官方教程使用以下方法安装所需的环境 pip install -e .[…

MySQL小记——约束、多表查询

目录 约束 常见约束 主键约束 非空约束 唯一约束 自增长约束 非负约束 外键约束之一对多 外键约束之多对多 多表查询 内连接 外连接 左外连接 右外连接 子查询 自查询 case when语句 约束 在MySQL中&#xff0c;约束是对字段规则的一种限制。 常见约束 1.主…

编译原理实验-递归下降语法分析

具体代码已放至Github&#xff08;仅供参考&#xff09;&#xff1a; qxpBlog/Compiler_UESTC: 电子科技大学编译原理实验 (github.com) 具体实验过程如下&#xff1a; 一、实验目的、原理、内容及步骤&#xff1a; &#xff08;1&#xff09;目的&#xff1a;通过本实验加深…

android 12.0Settings去掉二级三级菜单搜索功能

1.概述 在12.0由于客户定制开发需求,需要去掉Settings里面的搜索功能,主页面的搜索功能,在前面的章节已经讲了 这里需要去掉二级三级菜单的搜索功能,需要从搜索功能流程分析去掉搜索功能 2.Settings去掉二级三级菜单搜索功能核心代码 packages/apps/Settings/src/com/and…

tektronix泰克TDS3054数字荧光示波器

tektronix TDS3054是泰克TDS3000系列示波器&#xff0c;它是一种新的图形界面操作模式&#xff0c;称为QuickMenu。这种快速访问的用户界面使得主要的示波器控制访问一个单一的按键。每一个示波器都包含一个在示波器中运行的在线巡览盘。此磁盘提供了产品的操作和功能的概述。 …

今麦郎跻身“我最喜欢中国品牌”榜,致力领航中国品牌发展新范式

在中国经济探寻高质量发展的当下&#xff0c;中国民营企业肩负着推动经济发展的重任。在当前中国经济向上向前的大背景下&#xff0c;展示中国特色、传播中国文化、践行社会责任多位一体的高质量品牌越来越受到重视。但冰冻三尺非一日之功&#xff0c;唯有经历时间考验&#xf…

【Leetcode -605.种花问题 -628.三个数的最大乘积】

Leetcode Leetcode -605.种花问题Leetcode -628.三个数的最大乘积 Leetcode -605.种花问题 题目&#xff1a;假设有一个很长的花坛&#xff0c;一部分地块种植了花&#xff0c;另一部分却没有。可是&#xff0c;花不能种植在相邻的地块上&#xff0c;它们会争夺水源&#xff0…

apache虚拟主机头的实现方式

目录 一&#xff1a;基于不同的IP地址 二&#xff1a;基于不同的端口 三&#xff1a;基于不同的域名 一&#xff1a;基于不同的IP地址 步骤一&#xff1a;修改主配置文件 vim /usr/local/apache/conf/httpd.conf 声明监听不同的IP地址的80端口 Listen 192.168.2.20:80 Listen…