Xtrabackup备份与恢复

最新推荐文章于 2025-10-16 11:47:13 发布

原创最新推荐文章于 2025-10-16 11:47:13 发布 · 261 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #perl #linux

本文详细介绍了如何在Linux系统上部署多个MySQL实例，并通过Xtrabackup进行物理备份与恢复操作。首先，创建用户、解压安装MySQL，配置各实例目录并初始化。接着，安装perl、解决依赖问题，配置my.cnf文件，并启动MySQL服务。然后，修改各个实例的root用户密码，并设置服务开机自启。最后，讲解了Xtrabackup的安装与使用，包括数据库的备份与恢复步骤，确保数据的安全性。

mysql多实例部署

配置用户

# 创建mysql用户
[root@node2 ~]# useradd -r -M -s /bin/nologin mysql

# 解压软件包
[root@node2 ~]# tar xf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@node2 local]# ln -sv mysql-5.7.34-linux-glibc2.12-x86_64/ mysql

# 修改目录属主属组
[root@node2 local]# chown -R mysql.mysql /usr/local/mysql

# 配置环境变量
[root@node2 local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@node2 local]# . /etc/profile.d/mysql.sh

创建各实例存放目录

[root@node2 local]# mkdir -p /opt/data/{3306,3307,3308}
[root@node2 local]# chown -R mysql.mysql /opt/data/
[root@node2 local]# tree /opt/data/
/opt/data/
├── 3306
├── 3307
└── 3308

3 directories, 0 files

初始化实例

# 初始化3306
[root@node2 local]# mysqld --initialize --user mysql --datadir /opt/data/3306
2021-08-28T03:20:38.863813Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-28T03:20:38.997220Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-28T03:20:39.018419Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-28T03:20:39.074265Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: eb0b7234-07ae-11ec-9453-000c29264193.
2021-08-28T03:20:39.074981Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-28T03:20:39.496593Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-28T03:20:39.517690Z 1 [Note] A temporary password is generated for root@localhost: yh?6dfJ56G1!

# 初始化3307
[root@node2 ~]# mysqld --initialize --user mysql --datadir /opt/data/3307
2021-08-28T03:21:07.190436Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-28T03:21:07.303001Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-28T03:21:07.324821Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-28T03:21:07.379027Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: fbea68f9-07ae-11ec-96de-000c29264193.
2021-08-28T03:21:07.379716Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-28T03:21:07.746139Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-28T03:21:07.950807Z 1 [Note] A temporary password is generated for root@localhost: <+#xXs):,5K,

# 初始化3308
[root@node2 ~]# mysqld --initialize --user mysql --datadir /opt/data/3308
2021-08-28T03:21:45.794112Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-28T03:21:45.900389Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-28T03:21:45.921703Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-28T03:21:45.978060Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 12ec27a1-07af-11ec-9998-000c29264193.
2021-08-28T03:21:45.978801Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-28T03:21:46.492363Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-28T03:21:46.767616Z 1 [Note] A temporary password is generated for root@localhost: NRwiy>.ac7zs

安装软件

# 安装perl
[root@node2 ~]# yum -y install perl

# 查看mysql程序所依赖的包
[root@node2 ~]# ldd /usr/local/mysql/bin/mysql
libncurses.so.5 => not found
libtinfo.so.5 => not found

[root@node2 ~]# yum whatprovides libncurses.so.5
[root@node2 ~]# yum whatprovides libtinfo.so.5

# 安装ncurses-compat-libs
[root@node2 ~]# yum -y install ncurses-compat-libs

配置文件

[root@node2 ~]# cat /etc/my.cnf 
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin


[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql.pid
log-error = /var/log/3306.log

[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/3307/mysql.pid
log-error = /var/log/3307.log

[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/3308/mysql.pid
log-error = /var/log/3308.log

# 启动mysql
[root@node2 ~]# mysqld_multi start
[root@node2 ~]# ss -antl
LISTEN       0            80                              *:3306                          *:*          
LISTEN       0            80                              *:3307                          *:*          
LISTEN       0            80                              *:3308

修改mysql密码

# 修改3306
[root@node2 ~]# mysql -uroot -p'pt1WxKP27L+s' -h127.0.0.1 -P3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password = password('123');
Query OK, 0 rows affected, 1 warning (0.00 sec)

# 修改3307
[root@node2 ~]# mysql -uroot -p'IeCo-(w?G2Gk' -h127.0.0.1 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password = password('123');
Query OK, 0 rows affected, 1 warning (0.00 sec)


# 修改3308
[root@node2 ~]# mysql -uroot -p'n=y7haors-cL' -h127.0.0.1 -P3308
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password = password('123');
Query OK, 0 rows affected, 1 warning (0.00 sec)

设置开机自启

[root@node2 support-files]# cp -a mysqld_multi.server  /etc/init.d/mysqld_multi

# 写入环境变量
[root@node2 ~]# vim /etc/init.d/mysqld_multi
export PATH=/usr/local/mysql/bin:$PATH

[root@node2 ~]# chkconfig --add mysqld_multi

Xtraabackup 物理备份

相对于逻辑备份利用查询提取数据中的所有记录，物理备份更直接，拷贝数据库文件和日志来完成备份，因此速度会更快。当然，无论是开源的Mydumper还是官方最新的备份工具(5.7.11的mysqlpump)都支持了多线程备份，所以速度差异可能会进一步缩小，至少从目前生产环境来看，物理备份使用还是比较多的。由于Xtrabackup支持备份innodb表，实际生产环境中我们使用的工具是innobackupex，它是对xtrabackup的一层封装。innobackupex 脚本用来备份非 InnoDB 表，同时会调用 xtrabackup 命令来备份 InnoDB 表，innobackupex的基本流程如下：

开启redo日志拷贝线程，从最新的检查点开始顺序拷贝redo日志
开启ibd文件拷贝线程，拷贝innodb表的数据
ibd文件拷贝结束，通知调用FTWRL，获取一致性位点
备份非innodb表(系统表)和frm文件
由于此时没有新事务提交，等待redo日志拷贝完成
最新的redo日志拷贝完成后，相当于此时的innodb表和非innodb表数据都是最新的
获取binlog位点，此时数据库的状态是一致的
释放锁，备份结束

Xtrabackup安装

# 下载 rpm包
[root@node2 ~]# wget https://repo.percona.com/yum/release/8/RPMS/x86_64/percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm

# 安装
[root@node2 ~]# yum -y install percona-xtrabackup-24-2.4.23-1.el8.x86_64.rpm

[root@node2 ~]# cat /etc/my.cnf
[mysqld]
datadir = /opt/data/3306
port = 3306
socket = /tmp/3306.sock
pid-file = /opt/data/3306/mysql.pid
log-error = /var/log/mysql_3306.log

备份

# 创建class数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

# 备份数据库
[root@node2 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123 --host=127.0.0.1 -P3306 /backup
[root@node2 ~]# ls /backup/
2021-08-28_18-43-26

# 删除数据库
mysql> drop database class;
Query OK, 1 row affected (0.01 sec)

# 恢复（恢复前需删除存放目录下的所有文件）
[root@node2 ~]# innobackupex --apply-log /backup/2021-08-28_18-43-26
[root@node2 ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back -uroot -p123 --host=127.0.0.1 /backup/2021-08-28_18-43-26

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)