当前位置：首页 > 生活百科

mysql数据库备份方法有几种（mysql数据库备份方式）

栏目：生活百科日期：2024-12-17浏览：0

一背景

在我们运维数据库的过程中.为了保证数据的安全性及PTIR的恢复.都会对数据库进行备份.mysql中我们一般使用的备份方式就两种.一种是逻辑备份.另一种是物理备份.接下来我们一起了解一下两种备份方式的实现及原理.

二 mysql逻辑备份

mysql中我们经常使用的逻辑备份方式是mysql官方的mysqldump.当然也有开源的mydumper 但是不建议使用.为了数据的安全性.接下来我们了解一下mysqldump的使用及原理

2.1 mysqldump介绍:

基于SQL（create database ,create table , insert into）语句的备份。

使用场景： 100G以内，比较常用的就是逻辑备份。

针对InnoDB表可以实现非锁定备份。原理上是通过MVCC中的快照技术进行备份。

针对非InnoDB表，是启用了锁表备份，FTWRL（global read lock）。

优点：自带工具，不需要单独安装；文本形式存储，便于查看处理；压缩比较高，节省空间。

缺点：备份时间较长。恢复时间更长（4-6倍）。

2.2 mysqldump的执行原理

首先看一下我们执行mysqldump发生了什么2021-07-20T02:48:26.478282Z   57 Connectroot@localhost on  using Socket2021-07-20T02:48:26.478440Z   57 Query/*!40100 SET @@SQL_MODE='' */2021-07-20T02:48:26.478531Z   57 Query/*!40103 SET TIME_ZONE='+00:00' */2021-07-20T02:48:26.478601Z   57 Query/*!80000 SET SESSION information_schema_stats_expiry=0 */2021-07-20T02:48:26.478654Z   57 QuerySET SESSION NET_READ_TIMEOUT= 86400, SESSION NET_WRITE_TIMEOUT= 864002021-07-20T02:48:26.478719Z   57 QueryFLUSH /*!40101 LOCAL */ TABLES2021-07-20T02:48:26.480218Z   57 QueryFLUSH TABLES WITH READ LOCK2021-07-20T02:48:26.480292Z   57 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2021-07-20T02:48:26.480341Z   57 QuerySTART TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */2021-07-20T02:48:26.480422Z   57 QuerySHOW VARIABLES LIKE 'gtid\_mode'2021-07-20T02:48:26.482852Z   57 QuerySHOW MASTER STATUS2021-07-20T02:48:26.482920Z   57 QueryUNLOCK TABLES可以看到mysqldump首先执行了FTWL全局只读锁.然后获取了数据库的一致性快照.开始备份

2.3 mysqldump使用方法介绍

大家可以通过命令查看帮助:如下

&[root@db02 ~]# mysqldump --help

基本连接参数:

-u   备份用户名-p   用户密码-S   socket位置-h   IP地址-P   mysql端口

下面重点参数介绍一下.

&–tab 将表数据和表结构分别导出.表数据为csv格式

mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock --single-transaction  --tab="/tmp" world将world数据库下的所有表结构及表数据备份到/tmp目录下且表数据为csv格式查看:&[root@db02 tmp]# ll总用量 4760-rw-r--r-- 1 root  root    1637 7月  20 13:51 city.sql-rw-r----- 1 mysql mysql 143565 7月  20 13:51 city.txt-rw-r--r-- 1 root  root    1666 7月  20 13:51 countrylanguage.sql-rw-r----- 1 mysql mysql  18234 7月  20 13:51 countrylanguage.txt-rw-r--r-- 1 root  root    2036 7月  20 13:51 country.sql-rw-r----- 1 mysql mysql  31755 7月  20 13:51 country.txt-rw-r--r-- 1 root  root    1763 7月  20 13:51 employees.sql-rw-r----- 1 mysql mysql    335 7月  20 13:51 employees.txt其中sql为表结构语句.txt为表数据

&–no-data, -d 只导出表结构.不导出表数据

&[root@db02 tmp]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock --single-transaction  --no-data world &>world.sql&[root@db02 tmp]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock --single-transaction  -d world &>world.sql只导出world库下所有表的表结构

只导出表数据.不导出表结构:

&[root@db02 ~]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock -t world city --single-transaction&>all.sql

&–master-data=2 备份时自动生成，当前的binlog位置信息.自动开启锁表备份功能，如果开了&–single-transaction，可以减少global read lock.

&–single-transaction 功能：InnoDB “热备”。对于InnoDB表不锁表，开启一致性快照备份.使用这个参数会在备份时调整隔离级别为RR同时开启快照备份

2021-07-20T02:48:26.478282Z   57 Connectroot@localhost on  using Socket2021-07-20T02:48:26.478440Z   57 Query/*!40100 SET @@SQL_MODE='' */2021-07-20T02:48:26.478531Z   57 Query/*!40103 SET TIME_ZONE='+00:00' */2021-07-20T02:48:26.478601Z   57 Query/*!80000 SET SESSION information_schema_stats_expiry=0 */2021-07-20T02:48:26.478654Z   57 QuerySET SESSION NET_READ_TIMEOUT= 86400, SESSION NET_WRITE_TIMEOUT= 864002021-07-20T02:48:26.478719Z   57 QueryFLUSH /*!40101 LOCAL */ TABLES2021-07-20T02:48:26.480218Z   57 QueryFLUSH TABLES WITH READ LOCK2021-07-20T02:48:26.480292Z   57 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2021-07-20T02:48:26.480341Z   57 QuerySTART TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

&–flush-logs 在备份开始时刷新一个新的二进制日志文件

特殊对象备份-R -E &–triggers 备份存储过程,函数.事件

&–max-allowed-packet=128M 默认是24M.最大可以设置1G

&–where 导出指定条件的数据

&[root@db02 ~]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock scsales_prd T_HM_ROOMNO_VIEW_LOG_bak1 --where="pkid&<10010"&>all.sql

单库备份:

&[root@db02 ~]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock world --single-transaction&>all.sql

单表备份:

&[root@db02 ~]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock world city --single-transaction&>all.sql

最后提供一个生产经常使用的mysqldump完整版

mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers --max-allowed-packet=128M --flush-logs&>/opt/full.sql

以上就是对mysql的逻辑备份方式mysqldump的总结.

三物理备份方式介绍

说到物理备份方式.在MySQL8.0之前我们可以选择的物理备份工具都是PXB 从MySQL8017版本之后我们多了一种选择.那就是MySQL自带的clone plguin

3.1 PXB介绍

PXB是percona开源出来的一个物理备份工具.可以实现innodb的热备.实现原理是利用了mysql的crash recovery原理.

首先介绍xtrabackup的实现原理:

1.执行备份命令之后pxb先获取到当前的lsn.然后开始拷贝redo文件.同时开启一个后台线程监控redo的实时变化同步到拷贝目录.2.开始先拷贝innodb表数据.然后执行FLUSH TABLES WITH READ LOCK 和LOCK TABLES FOR BACKUP拷贝非innodb表数据.当这些拷贝完成之后.再拷贝其他数据文件3.然后执行LOCK BINLOG FOR BACKUP开始获取二进制日志的位置点及Exec_Gtid_Set4.此时xtrabackup完成对redo的拷贝并释放二进制日志的锁

使用xtrabackup备份数据库:

root@slowquery ~]# xtrabackup --backup --user=root --password=123 --target-dir=/data/backups/xtrabackup: recognized server arguments: --datadir=/data/3306/data --server-id=7 --log_bin=/data/3306/binlog/mysql-bin --open_files_limit=65535 --innodb_buffer_pool_size=4096M --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=32M --innodb_log_file_size=128M --innodb_log_files_in_group=3 --innodb_flush_log_at_trx_commit=1

使用xtrabackup进行增量备份

增量备份的原理:

xtrabackup会对比全备下的LSN号码.然后进行增量拷贝

查看全备目录下记录的lsn.后边我们查看增量的LSN

&[root@slowquery ~]# cat /data/backups/xtrabackup_checkpointsbackup_type = full-backupedfrom_lsn = 0to_lsn = 2628274last_lsn = 2628283compact = 0recover_binlog_info = 0

模拟增量数据mysql&> create database increment;Query OK, 1 row affected (0.00 sec)mysql&> use increment;Database changedmysql&> create table increment(id int,name varchar(30));Query OK, 0 rows affected (0.01 sec)mysql&> insert into increment values(1,'lzm');Query OK, 1 row affected (0.01 sec)mysql&>

开始增量备份:

&[root@slowquery ~]# xtrabackup --backup --user=root --password=123 --target-dir=/data/backups/inc1 \&; --incremental-basedir=/data/backups

查看增量目录下的LSN号码对比全量目录下的LSN号码

&[root@slowquery ~]# cat /data/backups/inc1/xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 2628274to_lsn = 2633017last_lsn = 2633026compact = 0recover_binlog_info = 0&[root@slowquery ~]#

此时增量备份也已经完成.接下来我们进行数据恢复

首先对全备备份目录操作:

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/

然后应用增量日志

xtrabackup --prepare --apply-log-only --target-dir=/data/backups --incremental-dir=/data/backups/inc1这里注意.如果增量不止一个的话.后边的增量和全备合并的时候不需要加--apply-log-only

停止现在的数据库并把数据目录清除:

pkill mysqldrm -rf /data/3306/data/*

将全备目录下的文件复制到数据目录:

xtrabackup --copy-back --target-dir=/data/backups/  修改权限chown -R mysql:mysql /data/ 启动mysql/etc/init.d/mysqld start

连接进入数据库查看数据是否恢复

mysql&> show databases;+--------------------+| Database           |+--------------------+| information_schema || inc1               || increment          || mysql              || performance_schema || sys                |+--------------------+6 rows in set (0.00 sec)mysql&> use increment;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql&> show tables;+---------------------+| Tables_in_increment |+---------------------+| increment           |+---------------------+1 row in set (0.00 sec)mysql&> select * from increment;+------+------+| id   | name |+------+------+|    1 | lzm  |+------+------+1 row in set (0.00 sec)

可以看到我们的增量数据也已经恢复了

关于8017之后加入的clone plguin的备份方式请查看我之前的文章MySQL 8.0新特性之clone plugin