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)创建测试数据库和表
//登录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中写入测试数据
//写入测试数据 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不会覆盖现有的文件)。
- 在任何时候取消都是安全的,因为它不会修改数据库。
xtrabackup --backup --target-dir=/data/backups/
- (2)准备备份
- 在使用xtrabackup—backup选项进行备份后,首先需要准备它,以便恢复。在准备好数据文件之前,数据文件在时间点上是不一致的,因为它们是在程序运行时的不同时间复制的,并且在此过程中可能已被更改。如果你试图用这些数据文件启动InnoDB,它会检测到损坏并自行崩溃,以防止你在损坏的数据上运行。xtrabackup -—prepare步骤使文件在某一时刻完全一致,这样你就可以在它们上运行InnoDB。
- 可以在任何机器上运行prepare操作。它不需要在原始服务器上,也不需要在要恢复到的服务器上。
xtrabackup --prepare --target-dir=/data/backups/
- (3)恢复备份
- 在恢复前,需要先进行准备备份。
- 在恢复备份前,必须停止MySQL服务,并且清空datadir目录。不能恢复数据到正在运行的mysqld实例的datadir中(导入部分备份时除外)。
xtrabackup --copy-back --target-dir=/data/backups/
4、增量备份介绍
- (1)增量备份
- 如果要进行增量备份,必须从全量备份开始。
#全量备份 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:不进行回滚操作
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base --incremental-dir=/data/backups/inc1 xtrabackup --prepare --target-dir=/data/backups/base --incremental-dir=/data/backups/inc2
- (3)恢复备份
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)下载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)创建一个具有完全备份所需的最低权限的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互斥。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
//全量备份 ]# 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)创建用于存储全量备份的目录 ]# 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)插入模拟数据 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)插入模拟数据 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进行如下操作:
]# ls -lrt /data/backups/mysql/ drwxr-xr-x 6 root root 315 2月 4 16:00 20230204-155540-full drwxr-xr-x 7 root root 4096 2月 4 16:13 20230204-160509-incr drwxr-xr-x 7 root root 4096 2月 4 16:25 20230204-162009-incr
2、准备恢复xtrabackup备份的备份
- 在10.1.1.12进行如下操作:
//(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)插入模拟数据 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_info mysql-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进行如下操作:
]# 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、查看准备好的备份文件
]# 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服务
]# ps -ef | grep mysql ]# kill -9 PID
3、清空datadir目录
]# rm -rf /usr/local/mysql/data/*
4、恢复xtrabackup备份的数据
]# xtrabackup --defaults-file=/usr/local/mysql/conf/my.cnf --copy-back --target-dir=./20230204-155540-full
5、启动msyql服务
//修改数据目录的属主属组 ]# chown apps.apps -R /usr/local/mysql/data/ //启动mysql服务 ]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/conf/my.cnf &
6、恢复二进制中没有备份的数据
]# /usr/local/mysql/bin/mysql -uroot -p'Apps@123' < ./mysql-bin.sql
3、使用mysqldump备份mysql数据库
- 当数据量比较大时,使用mysqldump完全备份会很慢。建议,数据量较小时使用mysqldump。
3.1、mysqldump命令
//导出指定的数据表。注意:导出文档中没有创建数据的语句,即还原时要手动创建数据库 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命令进行温备
//备份指定的一个数据库 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
- 分步执行温备:
flush tables with read lock; #刷新表并施加读锁 flush logs; #滚动日志 show binary logs; #查看备份时使用的日志文件 mysqldump DB_NAME [tb1] [tb2]; #进行备份 unlock tables; #释放锁
3.3、备份Innodb数据库
//对InnoDB的数据库进行温备,必须先锁定表。不能立即做快照,必须等Innodb存储引擎的缓冲区中数据都同步到磁盘 mysql> FLUSH TABLES WITH READ LOCK; //查看Innodb存储引擎的状态 mysql> show engine innodb status; //InnoDB的热备份:基于MVCC机制。--single-transaction是将隔离级别设置为REPEATABLE READ mysqldump -uroot -proot --single-transactio --flush-logs --master-data=2 --databases DB_NAME1 DB_NAME2 > ./mysql-backup-innodb.sql
1
# #