暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 使用Percona XtraBackup实现mysql5.1数据库主从环境搭建

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

原创 伟鹏 2024-07-12
115

引言

在处理关键业务的数据库时，定期备份是确保数据安全性和业务连续性的必要步骤。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)
墨力计划mysqlperconamysql主从配置
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论