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