0%

MySQL数据库引擎InnoDB物理文件备份

发表于 更新于 分类于 阅读次数： 1 Disqus：
本文字数： 4.9k 阅读时长 ≈ 8 分钟

MySQL数据库引擎InnoDB物理文件备份

Percona XtraBackup（简称PXB）是 Percona 公司开发的一个用于 MySQL 数据库物理热备的备份工具，支持 MySQL（Oracle）、Percona Server 和 MariaDB。

XtraBackup

操作步骤

1. 生成SQL

  • 生成丢弃表空间的SQL
  • 生成导入表空间的SQL
  • 导出表结构SQL&移除表结构SQL中的表外键
  • 更改数据库名
1
2
3
4
5
6
7
8
9
10
-- mysql 使用 --secure-file-priv 参数启用，需查看允许导出的目录。
-- SHOW VARIABLES LIKE "secure_file_priv";

# 生成丢弃表空间的SQL
mysql> select concat('alter table ',table_schema,'.',TABLE_NAME , ' discard tablespace', ';') from  information_schema.tables where TABLE_SCHEMA = 'school_last1' into outfile '/var/lib/mysql-files/discard.sql';
Query OK, 75 rows affected (0.05 sec)

# 生成导入表空间的SQL
mysql> select concat('alter table ',table_schema,'.',TABLE_NAME , ' import tablespace', ';') from  information_schema.tables where TABLE_SCHEMA = 'school_last1' into outfile '/var/lib/mysql-files/import.sql';
Query OK, 75 rows affected (0.00 sec)

导出 school_last1 数据库中所有表的结构

1
$ mysqldump -uroot -proot -d school_last1 > school_last1-table-structure.sql

删除表外键（在后期删除表空间时有影响）

1
2
3
4
# 查看表外键
# grep "CONSTRAINT.*RESTRICT" school_last1-table-structure.sql
$ sed -i -e ":a;N;s/,\n/,,/g;$!ba;" school_last1-table-structure.sql
$ sed -i -e "s/,,\s*CONSTRAINT.*RESTRICT//g;s/,,/,\n/g" school_last1-table-structure.sql

修改数据库名

1
2
$ sed -i -e "s/school_last1/school/g" discard.sql
$ sed -i -e "s/school_last1/school/g" import.sql

2. 备份指定数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# on 10.10.16.10
$ curl https://repo.percona.com/percona/yum/release/centos/7/RPMS/x86_64/percona-xtrabackup-80-8.0.27-19.1.el7.x86_64.rpm
$ yum install -y percona-xtrabackup-80-8.0.27-19.1.el7.x86_64.rpm
$ xtrabackup --backup --target-dir=/tmp/mysql \
					--datadir=/home/htdocs/mysql
					--user=root \
					--password=root \
					--host=127.0.0.1 \
					--port=3307 \
					--databases=school_last1 \
					--use-memory=4G
					
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --datadir=/home/htdocs/mysql
xtrabackup: recognized client arguments: --backup=1 --target-dir=/tmp/mysql --user=root --password=* --host=127.0.0.1 --port=3307 --databases=school_last1 --use-memory=4G
xtrabackup version 8.0.27-19 based on MySQL server 8.0.27 Linux (x86_64) (revision id: 50dbc8dadda)
230912 17:32:31  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=127.0.0.1;port=3307' as 'root'  (using password: YES).
...
xtrabackup: Transaction log of lsn (79075768667) to (79076184216) was copied.
230912 17:37:57 completed OK!
$ xtrabackup --apply-log-only --prepare --export --target-dir=/tmp/mysql

xtrabackup 工具参数：

  • –backup 将备份保存到 target-dir

  • —target-dir 指定备份目录路径

  • –datadir 数据库物理路径

  • –use-memory 导出时可使用的内存限制，默认：100MB

  • –databases 指定要备份数据库名

mysqldump 导出的sql文件近 6GB，xtrabackup 备份用时不到 6 分钟。

1
2
3
4
5
6
7
8
# on 10.10.16.10
$ mv school_last1-table-structure.sql import.sql discard.sql /tmp/mysql/
$ tar czvf school_last1.tar.gz /tmp/mysql
$ ls -lah
-rw-r--r--.  1 root root  3.4G 9月  13 09:52 school_last1.tar.gz
$ du -d 1 -h
25G	./mysql
$ scp /tmp/school_last1.tar.gz c79user@10.10.51.81:/tmp/

3. 还原数据库表结构

1
2
# on 10.10.51.81
$ mkdir /data1/mysql-bk && tar zxvf /tmp/school_last1.tar.gz -C /data1/mysql-bk
1
2
3
4
5
6
# on 10.10.51.81
mysql> create database school default character set utf8mb4 collate utf8mb4_general_ci;
mysql> use school;
mysql> source /data1/mysql-bk/tmp/mysql/school_last1-table-structure.sql;
# 删除表空间
mysql> source /data1/mysql-bk/tmp/mysql/discard.sql;

4. 迁移数据库表物理文件

1
2
3
# on 10.10.51.81
$ mv /data1/mysql-bk/tmp/mysql/school_last1/* /data1/mysql/data/school/
$ chown -R mysql:mysql /data1/mysql/data/school
1
2
3
# on 10.10.51.81
# 导入表空间
mysql> source /data1/mysql-bk/tmp/mysql/import.sql;

5. 还原表外键

1
2
3
4
5
6
# on 10.10.51.81
alter table qrtz_blob_triggers add CONSTRAINT `qrtz_blob_triggers_ibfk_1` FOREIGN KEY (`sched_name`, `trigger_name`, `trigger_group`) REFERENCES `qrtz_triggers` (`sched_name`, `trigger_name`, `trigger_group`) ON DELETE RESTRICT ON UPDATE RESTRICT;
alter table qrtz_cron_triggers add CONSTRAINT `qrtz_cron_triggers_ibfk_1` FOREIGN KEY (`sched_name`, `trigger_name`, `trigger_group`) REFERENCES `qrtz_triggers` (`sched_name`, `trigger_name`, `trigger_group`) ON DELETE RESTRICT ON UPDATE RESTRICT;
alter table qrtz_simprop_triggers add CONSTRAINT `qrtz_simprop_triggers_ibfk_1` FOREIGN KEY (`sched_name`, `trigger_name`, `trigger_group`) REFERENCES `qrtz_triggers` (`sched_name`, `trigger_name`, `trigger_group`) ON DELETE RESTRICT ON UPDATE RESTRICT;
alter table qrtz_simple_triggers add CONSTRAINT `qrtz_simple_triggers_ibfk_1` FOREIGN KEY (`sched_name`, `trigger_name`, `trigger_group`) REFERENCES `qrtz_triggers` (`sched_name`, `trigger_name`, `trigger_group`) ON DELETE RESTRICT ON UPDATE RESTRICT;
alter table qrtz_triggers add CONSTRAINT `qrtz_triggers_ibfk_1` FOREIGN KEY (`sched_name`, `job_name`, `job_group`) REFERENCES `qrtz_job_details` (`sched_name`, `job_name`, `job_group`) ON DELETE RESTRICT ON UPDATE RESTRICT;

6. 清理

1
2
3
4
5
6
# on 10.10.51.81
rm -rf /tmp/school_last1.tar.gz
rm -rf /data1/mysql-bk

# on 10.10.16.10
rm -rf /tmp/school_last1.tar.gz /tmp/mysql

总结

通过上述方式备份及还原数据库时，支持热备及热还原。中途并未暂停及重启过位于 10.10.16.10 及 10.10.51.81 上的数据库。