mysql-数据备份
1、测试环境
- 软件版本
- mysql-5.7.28-el7-x86_64.tar
- 系统环境
- 10.1.1.12：CentOS Linux release 7.7.1908 (Core)
- 10.1.1.13：CentOS Linux release 7.7.1908 (Core)
1、搭建测试环境
- 在10.1.1.12和10.1.1.13上分别安装mysql。
- 将10.1.1.12上的mysql当作旧数据库。
- 将10.1.1.13上的mysql当作新数据库。
2、向10.1.1.12的mysql中写入测试数据
- (1)创建测试数据库和表
1 2 3 4 5 6 7 8 | //登录mysql]# /usr/local/mysql/bin/mysql -uroot -p'Apps@123'//创建一个测试数据库mysql> CREATE DATABASE bkdatabase;//创建一个测试数据表mysql> CREATE TABLE bkdatabase.bktable (name char(10), age int); |
- (2)向mysql中写入测试数据
1 2 3 4 5 6 7 8 9 10 11 12 | //写入测试数据mysql> insert into bkdatabase.bktable(name, age) values("hh1", 1), ("hh2", 2), ("hh3", 3);//查看测试数据mysql> select * from bkdatabase.bktable;+------+------+| name | age |+------+------+| hh1 | 1 || hh2 | 2 || hh3 | 3 |+------+------+ |
2、使用XtraBackup备份mysql数据库（全量+增量）
- 下载XtraBackup：https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.27/binary/tarball/percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12.tar.gz
- 说明文档：https://docs.percona.com/percona-xtrabackup/2.4/index.html
- Percona XtraBackup是一个基于MySQL的服务器的开源的热备份工具，在备份期间不会锁定数据库。
- xtrabackup主要包含两个工具：xtrabackup和innobackupex
- xtrabackup2.2及以前两个工具都有，主要使用innobackupex
- xtrabackup2.3开始innobackupex被弃用，但仍然可以使用。xtrabackup8.0开始innobackupex完全被删除，不能再使用。
- xtrabackup2.3开始主要使用xtrabackup。
- Percona xtradabackup 2.4不支持备份MySQL 8.0、Percona Server for MySQL 8.0或Percona XtraDB Cluster 8.0及其以上的版本。对于数据库8.0及其以上的版本，需要使用Percona XtraBackup 8.0进行备份。
- mysql 5.7及其以下的版本，需要使用Percona XtraBackup 8.0以下的版本。
- Percona XtraBackup提供的一组工具有：
- xtrabackup：用于备份MyISAM、InnoDB和XtraDB表的数据库。
- xbcrypt：用于加密和解密备份文件。
- xbstream：允许以流的形式从xbstream格式提取文件。
- xbcloud：
1、XtraBackup 2.4简介
- Percona XtraBackup 2.4可以备份MySQL（5.1、5.5、5.6和5.7）服务器上的InnoDB、XtraDB和MyISAM表中的数据，以及带有XtraDB的Percona服务器。
- xtradabackup 2.4的优点：
- 快速可靠的完成数据库备份（例如，热备份，增量备份，bacula备份等）
- 备份期间不间断的事务处理
- 通过更好的压缩节省磁盘空间和网络带宽
- 自动备份验证
- 更快的恢复时间可延长正常运行时间
2、XtraBackup 8.0简介
- Percona XtraBackup 8.0可以备份MySQL 8.0服务器上的InnoDB、XtraDB、MyISAM和MyRocks表中的数据，也可以备份Percona Server for MySQL with XtraDB、Percona Server for MySQL 8.0和Percona XtraDB Cluster 8.0。
- XtraBackup 8.0优点：
- 快速可靠的完成数据库备份（例如，热备份，增量备份，bacula备份等）
- 备份期间不间断的事务处理
- 通过更好的压缩节省磁盘空间和网络带宽
- 自动备份验证
- 更快的恢复时间可延长正常运行时间
- 时间点恢复
3、全量备份介绍
- (1)全量备份
- 如果目标目录不存在，xtrabackup将创建该目录。如果目录存在且为空，xtrabackup将成功。如果文件存在，它将失败（Xtrabackup不会覆盖现有的文件）。
- 在任何时候取消都是安全的，因为它不会修改数据库。
1 | xtrabackup --backup --target-dir=/data/backups/ |
- (2)准备备份
- 在使用xtrabackup—backup选项进行备份后，首先需要准备它，以便恢复。在准备好数据文件之前，数据文件在时间点上是不一致的，因为它们是在程序运行时的不同时间复制的，并且在此过程中可能已被更改。如果你试图用这些数据文件启动InnoDB，它会检测到损坏并自行崩溃，以防止你在损坏的数据上运行。xtrabackup -—prepare步骤使文件在某一时刻完全一致，这样你就可以在它们上运行InnoDB。
- 可以在任何机器上运行prepare操作。它不需要在原始服务器上，也不需要在要恢复到的服务器上。
1 | xtrabackup --prepare --target-dir=/data/backups/ |
- (3)恢复备份
- 在恢复前，需要先进行准备备份。
- 在恢复备份前，必须停止MySQL服务，并且清空datadir目录。不能恢复数据到正在运行的mysqld实例的datadir中(导入部分备份时除外)。
1 | xtrabackup --copy-back --target-dir=/data/backups/ |
4、增量备份介绍
- (1)增量备份
- 如果要进行增量备份，必须从全量备份开始。
1 2 3 4 5 6 | #全量备份xtrabackup --backup --target-dir=/data/backups/base#增量备份，以全量备份为基础进行第一次增量备份xtrabackup --backup --target-dir=/data/backups/inc1 --incremental-basedir=/data/backups/base#增量备份，以第一次增量备份为基础进行第二次增量备份xtrabackup --backup --target-dir=/data/backups/inc2 --incremental-basedir=/data/backups/inc1 |
- (2)准备备份
- 在合并备份时，除最后一个增量备份外其他所有准备备份都要使用--apply-log-only参数。
- --apply-log-only：不进行回滚操作
1 2 3 | xtrabackup --prepare --apply-log-only --target-dir=/data/backups/basextrabackup --prepare --apply-log-only --target-dir=/data/backups/base --incremental-dir=/data/backups/inc1xtrabackup --prepare --target-dir=/data/backups/base --incremental-dir=/data/backups/inc2 |
- (3)恢复备份
1 | xtrabackup --copy-back --target-dir=/data/backups/base |
2.1、安装XtraBackup
- 因为是mysql 5.7，所以使用XtraBackup 2.4进行备份。
- 在10.1.1.12和10.1.1.13上进行如下操作：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | //(1)下载xtrabackup]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.27/binary/tarball/percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12.tar.gz//(2)解压xtrabackup]# tar zvfx percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12.tar.gz//(3)查看看xtrabackup提供的工具]# ls -l ./percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12/bin/lrwxrwxrwx 1 root root 10 12月 2 02:22 innobackupex -> xtrabackup-rwxr-xr-x 1 root root 10185463 12月 2 02:22 xbcloud-rwxr-xr-x 1 root root 3020 12月 2 02:14 xbcloud_osenv-rwxr-xr-x 1 root root 5299580 12月 2 02:22 xbcrypt-rwxr-xr-x 1 root root 5370640 12月 2 02:22 xbstream-rwxr-xr-x 1 root root 202001891 12月 2 02:22 xtrabackup |
2.2、备份mysql的特殊权限
- 在10.1.1.12进行如下操作：
1 2 3 4 5 6 7 8 9 | //(1)创建一个具有完全备份所需的最低权限的mysql用户。mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'Backup@123';mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';//(2)授予备份用户拥有percona_schema数据库的所有权限mysql> GRANT ALL PRIVILEGES ON percona_schema.* TO 'bkpuser'@'localhost' WITH GRANT OPTION;//(3)重新加载权限mysql> FLUSH PRIVILEGES; |
- 在进行备份的时候使用--history和--incremental-history-name参数，需要使用percona_schema数据库。
- 第一次使用--history参数时，XtraBackup会自动在要备份的MySQL中创建percona_schema数据库。
- 也可以不使用percona_schema数据库，只需要在备份的时候不使用--history和--incremental-history-name参数即可。
2.3、备份数据
- 若不想使用percona_schema数据库，可以将相关命令做如下修改：
- --incremental-history-name参数：要与--incremental参数联合使用，并与--incremental-basedir互斥。
//全量备份 ]# xtrabackup --defaults-file=/usr/local/mysql/conf/my.cnf --socket=/usr/local/mysql/run/mysql.sock --user=bkpuser --password=Backup@123 \ --slave-info --kill-long-query-type=select --kill-long-queries-timeout=20 --rsync \ --backup --target-dir=/data/backups/mysql/20230204-155540-full/ //第一次增量备份 ]# xtrabackup --defaults-file=/usr/local/mysql/conf/my.cnf --socket=/usr/local/mysql/run/mysql.sock --user=bkpuser --password=Backup@123 \ --slave-info --kill-long-query-type=select --kill-long-queries-timeout=20 --rsync \ --backup --target-dir=/data/backups/mysql/20230204-160509-incr/ --incremental-basedir=/data/backups/mysql/20230204-155540-full/ //第二次增量备份 ]# xtrabackup --defaults-file=/usr/local/mysql/conf/my.cnf --socket=/usr/local/mysql/run/mysql.sock --user=bkpuser --password=Backup@123 \ --slave-info --kill-long-query-type=select --kill-long-queries-timeout=20 --rsync \ --backup --target-dir=/data/backups/mysql/20230204-162009-incr/ --incremental-basedir=/data/backups/mysql/20230204-160509-incr/
1、全量备份
- 使用XtraBackup对MySQL进行全量+增量的备份方式，第一份备份必须是全量备份，其余备份是增量备份。
- 全量备份是一次备份周期的起始备份，因此全量备份中不能使用--incremental-history-name参数。
- 在10.1.1.12进行如下操作：
1 2 3 4 5 6 7 | //(1)创建用于存储全量备份的目录]# mkdir -p /data/backups/mysql/$(date "+%Y%m%d-%H%M%S")-full//(2)执行全量备份]# xtrabackup --defaults-file=/usr/local/mysql/conf/my.cnf --socket=/usr/local/mysql/run/mysql.sock --user=bkpuser --password=Backup@123 \ --slave-info --kill-long-query-type=select --kill-long-queries-timeout=20 --rsync \ --backup --target-dir=/data/backups/mysql/20230204-155540-full/ --history=mysql-backup-20230204-155540 |
2、第一次增量备份
- 在10.1.1.12进行如下操作：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | //(1)插入模拟数据mysql> insert into bkdatabase.bktable(name, age) values("hh4", 4), ("hh5", 5), ("hh6", 6);//(2)查看最近一次备份的--history值，用在--incremental-history-name参数中mysql> select name from percona_schema.xtrabackup_history order by end_time desc limit 1;+------------------------------+| name |+------------------------------+| mysql-backup-20230204-155540 |+------------------------------+//(3)创建用于存储第一份增量备份的目录]# mkdir -p /data/backups/mysql/$(date "+%Y%m%d-%H%M%S")-incr//(4)执行第一次增量备份]# xtrabackup --defaults-file=/usr/local/mysql/conf/my.cnf --socket=/usr/local/mysql/run/mysql.sock --user=bkpuser --password=Backup@123 \ --slave-info --kill-long-query-type=select --kill-long-queries-timeout=20 --rsync \ --backup --target-dir=/data/backups/mysql/20230204-160509-incr/ --history=mysql-backup-20230204-160509 \ --incremental --incremental-history-name=mysql-backup-20230204-155540 |
3、第二次增量备份
- 在10.1.1.12进行如下操作：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | //(1)插入模拟数据mysql> insert into bkdatabase.bktable(name, age) values("hh7", 7), ("hh8", 8), ("hh9", 9);//(2)查看最近一次备份的--history值，用在--incremental-history-name参数中mysql> select name from percona_schema.xtrabackup_history order by end_time desc limit 1;+------------------------------+| name |+------------------------------+| mysql-backup-20230204-160509 |+------------------------------+//(3)创建用于存储第一份增量备份的目录]# mkdir -p /data/backups/mysql/$(date "+%Y%m%d-%H%M%S")-incr//(4)执行第一次增量备份]# xtrabackup --defaults-file=/usr/local/mysql/conf/my.cnf --socket=/usr/local/mysql/run/mysql.sock --user=bkpuser --password=Backup@123 \ --slave-info --kill-long-query-type=select --kill-long-queries-timeout=20 --rsync \ --backup --target-dir=/data/backups/mysql/20230204-162009-incr/ --history=mysql-backup-20230204-162009 \ --incremental --incremental-history-name=mysql-backup-20230204-160509 |
2.4、准备备份
1、查看备份文件
- 在10.1.1.12进行如下操作：
1 2 3 4 | ]# ls -lrt /data/backups/mysql/drwxr-xr-x 6 root root 315 2月 4 16:00 20230204-155540-fulldrwxr-xr-x 7 root root 4096 2月 4 16:13 20230204-160509-incrdrwxr-xr-x 7 root root 4096 2月 4 16:25 20230204-162009-incr |
2、准备恢复xtrabackup备份的备份
- 在10.1.1.12进行如下操作：
1 2 3 4 5 6 7 8 | //(1)准备全量备份]# xtrabackup --prepare --apply-log-only --target-dir=/data/backups/mysql/20230204-155540-full//(2)准备第一个增量备份]# xtrabackup --prepare --apply-log-only --target-dir=/data/backups/mysql/20230204-155540-full --incremental-dir=/data/backups/mysql/20230204-160509-incr//(3)准备第二个增量备份]# xtrabackup --prepare --target-dir=/data/backups/mysql/20230204-155540-full --incremental-dir=/data/backups/mysql/20230204-162009-incr |
3、准备恢复二进制日志文件中的数据
- 在10.1.1.12进行如下操作：
1 2 3 4 5 6 7 8 9 | //(1)插入模拟数据mysql> insert into bkdatabase.bktable(name, age) values("hh10", 10), ("hh11", 11), ("hh12", 12);//(2)查看备份文件中的xtrabackup_binlog_info，获取xtrabackup备份到了哪个位置（必须先使用xtrabackup准备备份，才能通过查看完全备份查看，否则要查看最后一个增量备份）]# cat /data/backups/mysql/20230204-155540-full/xtrabackup_binlog_infomysql-bin.000003 7496 aa5097d9-a45c-11ed-ae4b-000c299ac374:1-19//(3)导出二进制日志文件中还没有备份的数据]# /usr/local/mysql/bin/mysqlbinlog --start-position=7496 /usr/local/mysql/logs/mysql-bin.000003 > mysql-bin.sql |
4、将准备好的备份文件复制到10.1.1.13
- 在10.1.1.12进行如下操作：
1 2 | ]# scp -r /data/backups/mysql/20230204-155540-full root@10.1.1.13:/root]# scp mysql-bin.sql root@10.1.1.13:/root |
2.5、恢复备份
- 在10.1.1.13进行如下操作
1、查看准备好的备份文件
1 2 3 | ]# ls -l ./drwxr-xr-x 7 root root 4096 2月 4 17:13 20230204-155540-full-rw-r--r-- 1 root root 6226 2月 4 17:14 mysql-bin.sql |
2、停止mysql服务
1 2 | ]# ps -ef | grep mysql]# kill -9 PID |
3、清空datadir目录
1 | ]# rm -rf /usr/local/mysql/data/* |
4、恢复xtrabackup备份的数据
1 | ]# xtrabackup --defaults-file=/usr/local/mysql/conf/my.cnf --copy-back --target-dir=./20230204-155540-full |
5、启动msyql服务
1 2 3 4 5 | //修改数据目录的属主属组]# chown apps.apps -R /usr/local/mysql/data///启动mysql服务]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/conf/my.cnf & |
6、恢复二进制中没有备份的数据
1 | ]# /usr/local/mysql/bin/mysql -uroot -p'Apps@123' < ./mysql-bin.sql |
3、使用mysqldump备份mysql数据库
- 当数据量比较大时，使用mysqldump完全备份会很慢。建议，数据量较小时使用mysqldump。
3.1、mysqldump命令
1 2 3 4 5 6 | //导出指定的数据表。注意：导出文档中没有创建数据的语句，即还原时要手动创建数据库mysqldump [OPTIONS] database_name [table1] [table2] ... //导出一个或多个数据库。注意：还原时不需要手动创建数据库mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]//导出所有数据库。注意：还原时不需要手动创建数据库mysqldump [OPTIONS] --all-databases [OPTIONS] |
- -B, --databases：导出一个或多个数据库。注意用法上的区别，没有table。所有name参数都被视为数据库名称。'USE db_name;'将包含在输出中。
- -A, --all-databases：导出所有数据库。
- -d, --no-data：只导出数据库表结构，不导出数据。
- -w, --where=name：只导出给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。
- --master-data=[0|1|2]：默认值是0，建议使用2。
- 0：不记录二进制日志文件及事件位置；
- 1：以CHNAGE MASTER TO的方式记录位置，可用于恢复后直接启动从服务器。
- 2：以CHANGE MASTER TO的方式记录位置，但默认会被注释。
- -x, --lock-all-tables：锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables选项。
- -l, --lock-tables：锁定当前导出的数据表，而不是一下子锁定全部数据库下的表。(默认为on;使用--skip-lock-tables禁用。)
- -F, --flush-logs：开始导出之前进行日志滚动。请注意：如果同时导出多个数据库(使用选项--databases或者--all-databases)，将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下，日志将会被刷新一次，相应的所有表同时被锁定。因此，如果想要导出和刷新日志在同一时刻，应该使用--lock-all-tables或者--master-data和--flush-logs。
- --single-transaction：通过在单个事务中导出所有表来创建一致的快照。仅适用于存储在支持多版本的存储引擎中的表(目前只有InnoDB支持);导出不保证与其他存储引擎一致。当——single-transaction导出正在进行时，为了确保导出文件有效(正确的表内容和二进制日志位置)，其他连接不应该使用以下语句:ALTER table、DROP table、RENAME table、TRUNCATE table，因为一致性快照不会与它们隔离。选项自动关闭--lock-all-tables
- -e, --extended-insert：使用具有多个VALUES列的INSERT语法。这样使导出文件更小，并加速导入时的速度。(Defaults to on; use --skip-extended-insert to disable.)
- --insert-ignore：使用INSERT IGNORE语句插入行。
- --replace：使用REPLACE INTO取代INSERT INTO。
- --opt：等同于-add-drop-table、--add-locks、--create-options、--quick、--extended-insert、--lock-tables、--set-charset、--disable-keys。默认开启，可以用--skip-opt禁用。
- -E, --events：导出事件，导出数据库上的事件调度器。
- -R, --routines：导出存储过程和存储函数。
- --triggers：导出触发器。
3.2、使用mysqldump命令进行温备
1 2 3 4 5 6 7 8 9 10 | //备份指定的一个数据库mysqldump -uroot -proot --lock-all-tables --flush-logs --master-data=2 mysql > ./mysql-backup-db.sql//备份指定的数据库中的多个数据表mysqldump -uroot -proot --lock-all-tables --flush-logs --master-data=2 mysql user db > ./mysql-backup-tbs.sql//备份多个数据库mysqldump -uroot -proot --lock-all-tables --flush-logs --master-data=2 --databases mysql performance_schema > ./mysql-backup-dbs.sql//备份所有数据库mysqldump -uroot -proot --lock-all-tables --flush-logs --master-data=2 --all-databases > ./mysql-backup-dball.sql |
- 分步执行温备：
1 2 3 4 5 | flush tables with read lock; #刷新表并施加读锁flush logs; #滚动日志show binary logs; #查看备份时使用的日志文件mysqldump DB_NAME [tb1] [tb2]; #进行备份unlock tables; #释放锁 |
3.3、备份Innodb数据库
1 2 3 4 5 6 7 8 | //对InnoDB的数据库进行温备，必须先锁定表。不能立即做快照，必须等Innodb存储引擎的缓冲区中数据都同步到磁盘mysql> FLUSH TABLES WITH READ LOCK;//查看Innodb存储引擎的状态mysql> show engine innodb status;//InnoDB的热备份：基于MVCC机制。--single-transaction是将隔离级别设置为REPEATABLE READmysqldump -uroot -proot --single-transactio --flush-logs --master-data=2 --databases DB_NAME1 DB_NAME2 > ./mysql-backup-innodb.sql |
1
1 | # # |
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了