使用Percona XtraBackup实现mysql5.1数据库主从环境搭建

引言

在处理关键业务的数据库时，定期备份是确保数据安全性和业务连续性的必要步骤。Percona XtraBackup 是一个强大的开源工具，专门设计用于对 MySQL 和 MariaDB 数据库进行快速、可靠且无阻塞的备份。本文将指导你通过一系列命令行操作，完成使用 Percona XtraBackup 对 MySQL 数据库的备份、应用日志及数据恢复的全过程。

准备工作

首先，确保你的系统已安装必要的开发工具和依赖项。接下来，我们将下载并构建 Percona XtraBackup。

下载 Percona XtraBackup 的源码包
wget https://github.com/percona/percona-xtrabackup/archive/percona-xtrabackup-2.0.8.tar.gz

解压缩下载的源码包
tar -xvzf percona-xtrabackup-2.0.8.tar.gz

进入解压后的目录
cd percona-xtrabackup-percona-xtrabackup-2.0.8

下载 MySQL 5.1 源码包（用于构建时的兼容性）
wget http://downloads.mysql.com/archives/mysql-5.1/mysql-5.1.59.tar.gz

安装必要的依赖软件包
yum -y install automake libtool ncurses-devel cmake gcc-c++ zlib zlib-devel

构建 Percona XtraBackup，指定其兼容 MySQL 5.1
./utils/build.sh 5.1

将编译好的可执行文件复制到全局可执行路径
cp xtrabackup_51 /usr/bin/xtrabackup
cp innobackupex /usr/bin/innobackupex


备份数据库

现在我们准备好使用 innobackupex 来备份 MySQL 数据库。
开始备份过程，指定数据库配置文件、登录凭据和备份目录

[root@bugzilla bak]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123123 /dbbackup/mysqldata/bak

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

240710 17:53:59 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --
240710 17:53:59 innobackupex: Connected to database with mysql child process (pid=236057)
240710 17:54:05 innobackupex: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".

innobackupex: Using mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
innobackupex: Using mysql server version Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Warning: xtrabackup: ignoring option '--innodb_file_per_table' due to invalid value 'ON'
innobackupex: Created backup directory /dbbackup/mysqldata/bak/2024-07-10_17-54-05
240710 17:54:05 innobackupex: Starting mysql with options: --defaults-file='/etc/my.cnf' --password=xxxxxxxx --user='root' --unbuffered --
240710 17:54:05 innobackupex: Connected to database with mysql child process (pid=236080)
240710 17:54:07 innobackupex: Connection to database server closed

240710 17:54:07 innobackupex: Starting ibbackup with command: xtrabackup --defaults-file="/etc/my.cnf" --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/dbbackup/mysqldata/bak/2024-07-10_17-54-05 --tmpdir=/tmp
innobackupex: Waiting for ibbackup (pid=236086) to suspend
innobackupex: Suspend file '/dbbackup/mysqldata/bak/2024-07-10_17-54-05/xtrabackup_suspended'

Warning: xtrabackup: ignoring option '--innodb_file_per_table' due to invalid value 'ON'
xtrabackup version 2.0.8 for MySQL server 5.1.59 unknown-linux-gnu (x86_64) (revision id: undefined)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /data/mysqldata
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 1073741824
>> log scanned up to (81 3041554630)
[01] Copying ./ibdata1 to /dbbackup/mysqldata/bak/2024-07-10_17-54-05/ibdata1


传输备份

将如下备份的文件传输到备库

[root@bugzilla 2024-07-10_17-54-05]# ll
total 173836908
-rw-r--r-- 1 root root 191 Jul 10 14:41 backup-my.cnf
drwxr-xr-x 2 root root 40960 Jul 10 15:20 BBS
drwxr-xr-x 2 root root 4096 Jul 10 15:19 bms
drwxr-xr-x 2 root root 4096 Jul 10 15:20 bug
drwxr-xr-x 2 root root 4096 Jul 10 15:19 ddbb
drwxr-xr-x 2 root root 4096 Jul 10 15:19 device
drwxr-xr-x 2 root root 12288 Jul 10 15:19 discuz
drwxr-xr-x 2 root root 4096 Jul 10 15:19 fujitsu
drwxr-xr-x 2 root root 12288 Jul 10 15:19 gift
drwxr-xr-x 2 root root 20480 Jul 10 15:20 helpdesk
-rw-r----- 1 root root 178008358912 Jul 10 15:19 ibdata1
drwxr-xr-x 2 root root 4096 Jul 10 15:20 ics_mogami_upgrade_wiki_db
drwxr-xr-x 2 root root 20480 Jul 10 15:20 itms
drwxr-xr-x 2 root root 4096 Jul 10 15:19 Loquat
drwxr-xr-x 2 root root 4096 Jul 10 15:19 mibew
drwxr-xr-x 2 root root 4096 Jul 10 15:19 mrbs
drwxr-xr-x 2 root root 12288 Jul 10 15:19 mysmf
drwxr-xr-x 2 root root 4096 Jul 10 15:20 mysql
drwxr-xr-x 2 root root 4096 Jul 10 15:19 onepiece
drwxr-xr-x 2 root root 4096 Jul 10 15:19 semcc_ril
drwxr-xr-x 2 root root 4096 Jul 10 15:19 SonyODM
drwxr-xr-x 2 root root 36864 Jul 10 15:20 survey
drwxr-xr-x 2 root root 4096 Jul 10 15:19 test
drwxr-xr-x 2 root root 45056 Jul 10 15:20 ultrax
drwxr-xr-x 2 root root 4096 Jul 10 15:20 wiki
drwxr-xr-x 2 root root 4096 Jul 10 15:19 wp
-rw-r--r-- 1 root root 10 Jul 10 15:20 xtrabackup_binary
-rw-r--r-- 1 root root 27 Jul 10 15:19 xtrabackup_binlog_info
-rw-r----- 1 root root 91 Jul 10 15:20 xtrabackup_checkpoints
-rw-r----- 1 root root 333824 Jul 10 15:20 xtrabackup_logfile


应用日志和数据恢复

在备份完成后，下一步是应用二进制日志，这确保了备份数据的完整性。

应用备份期间产生的二进制日志
innobackupex --apply-log /data/dbbak/software/mysqlbak

将备份数据复制回原位置，完成数据恢复
innobackupex --defaults-file=/etc/my.cnf --copy-back /data/dbbak/software/mysqlbak


设置MySQL主从复制

为了提高数据可用性和灾难恢复能力，通常会配置一个或多个从数据库服务器。下面是如何设置从服务器以接收来自主服务器的更新。

## 配置从服务器，指定主服务器的信息
CHANGE MASTER TO
MASTER_HOST='bugzilla',
MASTER_USER='root',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=2433802;


检查从服务器的状态

mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: bugzilla
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 95659261
Relay_Log_File: bugzilla-relay-bin.000002
Relay_Log_Pos: 93225710
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 95659261
Relay_Log_Space: 93225868
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)