mysql数据库三种备份方式 mysql数据备份通常有哪几种方式 _mysql数据库三种备份方式

文章插图

文章插图
一背景
在我们运维数据库的过程中.为了保证数据的安全性及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.478282Z57 [email protected] onusing Socket2021-07-20T02:48:26.478440Z57 Query/*!40100 SET @@SQL_MODE='' */2021-07-20T02:48:26.478531Z57 Query/*!40103 SET TIME_ZONE='+00:00' */2021-07-20T02:48:26.478601Z57 Query/*!80000 SET SESSION information_schema_stats_expiry=0 */2021-07-20T02:48:26.478654Z57 QuerySET SESSION NET_READ_TIMEOUT= 86400, SESSION NET_WRITE_TIMEOUT= 864002021-07-20T02:48:26.478719Z57 QueryFLUSH /*!40101 LOCAL */ TABLES2021-07-20T02:48:26.480218Z57 QueryFLUSH TABLES WITH READ LOCK2021-07-20T02:48:26.480292Z57 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2021-07-20T02:48:26.480341Z57 QuerySTART TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */2021-07-20T02:48:26.480422Z57 QuerySHOW VARIABLES LIKE 'gtid\_mode'2021-07-20T02:48:26.482852Z57 QuerySHOW MASTER STATUS2021-07-20T02:48:26.482920Z57 QueryUNLOCK TABLES可以看到mysqldump首先执行了FTWL全局只读锁.然后获取了数据库的一致性快照.开始备份

2.3 mysqldump使用方法介绍
大家可以通过命令查看帮助:如下
[[email protected] ~]# mysqldump --help基本连接参数:
-u备份用户名-p用户密码-Ssocket位置-hIP地址-Pmysql端口下面重点参数介绍一下.
–tab 将表数据和表结构分别导出.表数据为csv格式

mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock --single-transaction--tab="/tmp" world将world数据库下的所有表结构及表数据备份到/tmp目录下且表数据为csv格式查看:[[email protected] tmp]# ll总用量 4760-rw-r--r-- 1 rootroot1637 7月20 13:51 city.sql-rw-r----- 1 mysql mysql 143565 7月20 13:51 city.txt-rw-r--r-- 1 rootroot1666 7月20 13:51 countrylanguage.sql-rw-r----- 1 mysql mysql18234 7月20 13:51 countrylanguage.txt-rw-r--r-- 1 rootroot2036 7月20 13:51 country.sql-rw-r----- 1 mysql mysql31755 7月20 13:51 country.txt-rw-r--r-- 1 rootroot1763 7月20 13:51 employees.sql-rw-r----- 1 mysql mysql335 7月20 13:51 employees.txt其中sql为表结构语句.txt为表数据

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

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

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

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

–master-data=http://www.mnbkw.com/jxjc/187783/2 备份时自动生成，当前的binlog位置信息.自动开启锁表备份功能，如果开了–single-transaction，可以减少global read lock.
–single-transaction 功能：InnoDB “热备” 。对于InnoDB表不锁表，开启一致性快照备份.使用这个参数会在备份时调整隔离级别为RR同时开启快照备份

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

–flush-logs 在备份开始时刷新一个新的二进制日志文件
特殊对象备份-R -E –triggers 备份存储过程,函数.事件
–max-allowed-packet=128M 默认是24M.最大可以设置1G
–where 导出指定条件的数据

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

单库备份:
[[email protected] ~]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock world --single-transaction>all.sql单表备份:
[[email protected] ~]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock world city --single-transaction>all.sql【mysql数据库三种备份方式 mysql数据备份通常有哪几种方式】最后提供一个生产经常使用的mysqldump完整版

mysqldump -uroot -p123 -A --master-data=http://www.mnbkw.com/jxjc/187783/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备份数据库:

[email protected] ~]# 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

[[email protected] ~]# 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>

开始增量备份:

[[email protected] ~]# xtrabackup --backup --user=root --password=123 --target-dir=/data/backups/inc1 \> --incremental-basedir=/data/backups

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

[[email protected] ~]# cat /data/backups/inc1/xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 2628274to_lsn = 2633017last_lsn = 2633026compact = 0recover_binlog_info = 0[[email protected] ~]#

此时增量备份也已经完成.接下来我们进行数据恢复
首先对全备备份目录操作:
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
四总结:
这篇文章为大家分享了MySQL的逻辑备份及物理备份方式.以及详细的使用方法!