MySQL8.0使用XtraBackup在线搭建从库

原创闫建云和恩墨 2023-04-30

2126

概述

XtraBackup作为MySQL最为著名的热备工具大家都应该掌握，现将最近使用一例case存档保留。
数据库大小：整个数据目录300G
数据库版本：MySQL8.0.26
数据库操作系统：Linux
备份时间：35分钟左右
备份后的大小：50G左右

下载XtraBackup软件

https://www.percona.com/software/mysql-database/percona-xtrabackup

软件安装

cd /app/backup/
tar -zxvf percona-xtrabackup-8.0.32-25-Linux-x86_64.glibc2.17-minimal.tar.gz
mv percona-xtrabackup-8.0.32-25-Linux-x86_64.glibc2.17-minimal xtrabackup8032
复制

主库在线全备

--用户赋权:8.0版本MySQL需要具有backup_admin权限
mysql> grant backup_admin  on *.* to root@'%';
mysql> flush privileges;
mysql> exit;

--全备：采用压缩，并行备份，提高备份速度，节省磁盘空间
/app/backup/xtrabackup8032/bin/xtrabackup --backup --compress --compress-threads=8 --target-dir=/app/backup/bk0328/ --user=root --password='password' --host=10.12.66.86 --port=3306 

2023-03-28T22:08:10.186552+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql --log_bin=mysql-bin --server-id=1 --innodb_buffer_pool_size=10G --innodb_data_file_path=ibdata1:50M:autoextend --open_files_limit=1999 
2023-03-28T22:08:10.187000+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --backup=1 --compress --compress-threads=8 --target-dir=/app/backup/bk0328/ --user=root --password=* --host=10.10.96.76 --port=3306 
/app/backup/xtrabackup8032/bin/xtrabackup version 8.0.32-25 based on MySQL server 8.0.32 Linux (x86_64) (revision id: 14f007fb)
Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at - line 754.
BEGIN failed--compilation aborted at - line 754.
2023-03-28T22:08:10.255378+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: 10.10.96.76, user: root, password: set, port: 3306, socket: not set
2023-03-28T22:08:10.282912+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.26
2023-03-28T22:08:10.311853+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2023-03-28T22:08:10.314283+08:00 0 [Note] [MY-011825] [Xtrabackup] uses posix_fadvise().
2023-03-28T22:08:10.314347+08:00 0 [Note] [MY-011825] [Xtrabackup] cd to /var/lib/mysql
2023-03-28T22:08:10.314406+08:00 0 [Note] [MY-011825] [Xtrabackup] open files limit requested 1999, set to 65536
2023-03-28T22:08:10.314517+08:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration:
2023-03-28T22:08:10.314542+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2023-03-28T22:08:10.314559+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:50M:autoextend
2023-03-28T22:08:10.314649+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = ./
2023-03-28T22:08:10.314669+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 2
2023-03-28T22:08:10.314694+08:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 50331648
2023-03-28T22:08:10.317899+08:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2023-03-28T22:08:10.555313+08:00 0 [Note] [MY-012529] [InnoDB] Redo log format is v4. The redo log was created before MySQL 8.0.30.
2023-03-28T22:08:10.556202+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: 10.10.96.76, user: root, password: set, port: 3306, socket: not set
2023-03-28T22:08:10.582245+08:00 0 [Note] [MY-011825] [Xtrabackup] Redo Log Archiving is not set up.
2023-03-28T22:08:10.674549+08:00 0 [Warning] [MY-011825] [Xtrabackup] --compress using quicklz is deprecated and the ability to take backups using this compression algorithm will be removed in a future release. Please use ZSTD or LZ4 instead.
2023-03-28T22:08:10.702340+08:00 0 [Note] [MY-011825] [Xtrabackup] Starting to parse redo log at lsn = 1366066510370
2023-03-28T22:08:10.705317+08:00 0 [Note] [MY-012564] [InnoDB] Recovery parsing buffer extended to 4194304.
2023-03-28T22:08:10.708250+08:00 0 [Note] [MY-012564] [InnoDB] Recovery parsing buffer extended to 8388608.
2023-03-28T22:08:10.795047+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (1366074350033)
2023-03-28T22:08:11.795751+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (1366074350033)
2023-03-28T22:08:12.797487+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (1366074364584)
2023-03-28T22:08:13.798339+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (1366074369472)
2023-03-28T22:08:14.384419+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2023-03-28T22:08:14.384564+08:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2023-03-28T22:08:14.384603+08:00 0 [Note] [MY-012204] [InnoDB] Scanning './'
2023-03-28T22:08:14.592114+08:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 2 files.
2023-03-28T22:08:14.618191+08:00 0 [Warning] [MY-012091] [InnoDB] Allocated tablespace ID 724 for shanghaizp/flight_search_history, old maximum was 0
2023-03-28T22:08:14.799054+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (1366074371773)
2023-03-28T22:08:15.799853+08:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (1366074380042)
......
2023-03-28T22:41:24.576495+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing UNLOCK INSTANCE
2023-03-28T22:41:24.577087+08:00 0 [Note] [MY-011825] [Xtrabackup] All tables unlocked
2023-03-28T22:41:24.577216+08:00 0 [Note] [MY-011825] [Xtrabackup] Compressing ib_buffer_pool to /app/backup/bk0328/ib_buffer_pool.qp
2023-03-28T22:41:24.624366+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Compressing ib_buffer_pool to /app/backup/bk0328/ib_buffer_pool.qp
2023-03-28T22:41:24.629486+08:00 0 [Note] [MY-011825] [Xtrabackup] Backup created in directory '/app/backup/bk0328/'
2023-03-28T22:41:24.629533+08:00 0 [Note] [MY-011825] [Xtrabackup] MySQL binlog position: filename 'mysql-bin.000766', position '196', GTID of the last change '2c70c1b8-0006-11ec-8997-005056b502b0:1-476043,51611b85-319e-11ec-9ee9-005056b502b0:1-450095158'
2023-03-28T22:41:24.629733+08:00 0 [Note] [MY-011825] [Xtrabackup] Compressing /app/backup/bk0328/backup-my.cnf.qp
2023-03-28T22:41:24.629871+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Compressing file /app/backup/bk0328/backup-my.cnf.qp
2023-03-28T22:41:24.702413+08:00 0 [Note] [MY-011825] [Xtrabackup] Compressing /app/backup/bk0328/xtrabackup_info.qp
2023-03-28T22:41:24.702662+08:00 0 [Note] [MY-011825] [Xtrabackup] Done: Compressing file /app/backup/bk0328/xtrabackup_info.qp
2023-03-28T22:41:25.716827+08:00 0 [Note] [MY-011825] [Xtrabackup] Transaction log of lsn (1366066510813) to (1366138213797) was copied.
2023-03-28T22:41:25.971438+08:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!
复制

传输至从库

cd /app/backup 
scp -r bk0328 username@slave_ip:/app/backup/
复制

从库同样也安装好软件，并编辑好my.cnf文件

cd /usr/local/
tar xvf mysql-8.0.26-linux-glibc2.17-x86_64-minimal-rebuild.tar.xz  
mv mysql-8.0.26-linux-glibc2.17-x86_64-minimal-rebuild mysql

vi /etc/my.cnf
/**
[client]
port                                    = 3306
socket                                  = /app/mysql/run/mysql.sock
default-character-set                   = utf8mb4

[mysql]          
port                                    = 3306
socket                                  = /app/mysql/run/mysql.sock
default-character-set                   = utf8mb4
prompt                                  = \\u@\\h:\\d \\r:\\m:\\s >
#no-auto-rehash

[mysqld]
server-id                               = 12345
port                                    = 3306
mysqlx_port                             = 33060
admin_address                           = 127.0.0.1
admin_port                              = 33062
default_storage_engine                  = InnoDB
character_set_server                    = utf8mb4
collation_server                        = utf8mb4_0900_ai_ci
transaction_isolation                   = READ-COMMITTED
max-allowed-packet                      = 512M
max-connections                         = 1000
max-connect-errors                      = 1000000

# only when ssd set blow
#innodb_io_capacity                      = 2000
basedir                                 = /usr/local/mysql
datadir                                 = /app/mysql/data
log_bin                                 = /app/mysql/binlogs/binlog
relay_log                               = /app/mysql/binlogs/relaylog
socket                                  = /app/mysql/run/mysql.sock
mysqlx_socket                           = /app/mysql/run/mysqlx.sock 
pid-file                                = /app/mysql/run/mysql.pid
log_error                               = /app/mysql/logs/error.log
slow_query_log_file                     = /app/mysql/logs/slow.log
tmpdir                                  = /app/mysql/tmp
innodb_buffer_pool_size                 = 16G
join_buffer_size                        = 4M
tmp_table_size                          = 32M
slow_query_log                          = 1
long_query_time                         = 1
 
innodb_log_file_size                   = 1G
innodb_log_files_in_group              = 3

innodb_data_file_path                   = ibdata1:50M:autoextend
innodb_temp_data_file_path              = ibtmp1:500M;ibtmp2:500M:autoextend:max:5120M
innodb_flush_method                     = O_DIRECT
default-time-zone                       = '+8:00'
log_bin_trust_function_creators         = 1
innodb_adaptive_hash_index              = 0
log_timestamps                          = SYSTEM

gtid_mode                               = ON
enforce_gtid_consistency                = ON
lower_case_table_names                  = 1
report_host                             = 'your slave ip'
binlog_expire_logs_seconds              = 259200
slave_parallel_type                     = LOGICAL_CLOCK
slave_parallel_workers                  = 4

read_only                               = ON

-- 建好对应的目录
mkdir -p /app/mysql/{data,run,tmp,logs,binlogs}
chown -R mysql.mysql /app/mysql/

复制

解压备份文件（从库操作）

说明：采用默认压缩方式，需要安装qpress软件，这里已经提前安装好qpress，故不在详细说明。
cd /app/backup/
/app/backup/xtrabackup8032/bin/xtrabackup --decompress --parallel=4 --remove-original --target-dir=/app/backup/bk0328/
复制

应用日志(从库操作)

/app/backup/xtrabackup8032/bin/xtrabackup --prepare --use-memory=2G --target-dir=/app/backup/bk0328/
复制

采用copy-back 或 move-back 或直接拷贝 (从库操作)

mv /app/backup/bk0328/* /app/mysql/data/
--或者
xtrabackup --copy-back --target-dir=/app/backup/bk0328/
复制

赋权(从库操作)

chown -R mysql.mysql /app/mysql/
复制

启动数据库： (从库操作)

/usr/local/mysql/bin/mysqld_safe --user=mysql &
复制

建立复制：(从库操作)

/usr/local/mysql/bin/mysql -uroot -p'password' -S /app/mysql/run/mysql.sock
mysql> show master status\G
mysql> show global variables like '%gtid%';
---- 确认gtid信息和 备份文件xtrabackup_binlog_info中的GTID值一致，如不一致需要进行 set global gtid_purged='xxxxxxx';操作
mysql> RESET MASTER;
mysql> RESET SLAVE ALL;
mysql> SET GLOBAL gtid_purged='<gtid_string_found_in_xtrabackup_binlog_info>';
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST="master_ip", SOURCE_USER="repluser", SOURCE_PASSWORD="password", SOURCE_AUTO_POSITION = 1;
mysql> START REPLICA;
mysql> show replica status\G
复制