看完这篇都有DBA级别的MySQL备份经验了，再也不害怕删库跑路了-Toy模板网

这篇具有很好参考价值的文章主要介绍了看完这篇都有DBA级别的MySQL备份经验了，再也不害怕删库跑路了。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

在做运维的路上，相信大家会跟数据库经常打交道吧，那么数据库最重要的时候什么呢，那么肯定是备份了，这篇文章主要内容是介绍了，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

查看恢复情况文章来源地址https://www.toymoban.com/news/detail-451447.html

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)

到了这里，关于看完这篇都有DBA级别的MySQL备份经验了，再也不害怕删库跑路了的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！