关于mysql:技术分享-ARM下中标麒麟系统ky10使用Xtrabackup8025

欢送来到 GreatSQL 社区分享的 MySQL 技术文章，如有疑难或想学习的内容，能够在下方评论区留言，看到后会进行解答

一、需要背景

查问 Percona 官网手册，Xtrabackup 8.0 能够备份 MySQL 8.0 以上。

二、环境筹备

因为在中标麒麟 ky10 零碎上间接编译报 gcc 等谬误，所以须要在 ARM 下筹备 CentOS 零碎。

中标麒麟 ky10 的内核为 4.19，而 CentOS 7 的内核为 3.xx，CentOS 8 的内核为 4.18，故须要在 CentOS 8 的操作系统进行编译，编译实现后拿到中标麒麟 ky10 中应用。

2.1 查看零碎架构及版本

  Shell> cat /etc/redhat-release
    CentOS Linux release 8.1.1911 (Core) 
    Shell> uname -srm
    Linux 4.18.0-147.el8.aarch64 aarch64

2.2 下载源码包

web 下载地址：

shell 操作：

 Shell> cd /root
    Shell>wget https://github.com/percona/percona-xtrabackup/archive/refs/tags/percona-xtrabackup-8.0.25-17.tar.gz

2.3 配置 CentOS 8 的 yum 源

Shell> mkdir /etc/yum.repos.d/repo.bak
    Shell> mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/repo.bak/

    // 查看 dns 是否失常

    Shell> ping baidu.com

    // 批改 dns 地址

    Shell> vim /etc/resolv.conf
    Shell> curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-8.repo
    Shell> sed -i -e '/mirrors.cloud.aliyuncs.com/d' -e '/mirrors.aliyuncs.com/d' /etc/yum.repos.d/CentOS-Base.repo
    Shell> sed -i.bak -e 's|^mirrorlist=|#mirrorlist=|' -e 's|^#baseurl=|baseurl=|' -e 's|http://mirror.centos.org|https://mirrors.aliyun.com|' /etc/yum.repos.d/CentOS-*.repo
    Shell> dnf makecache
    Shell> dnf install lrzsz

三、装置编译依赖

    Shell> dnf install cmake openssl-devel libaio libaio-devel automake autoconf bison libtool ncurses-devel libgcrypt-devel libev-devel libcurl-devel zlib-devel vim-common libarchive git centos-release-stream gcc-toolset-10-gcc-c++

PS: 以上依赖都必须装置，否则 CMake 时会报依赖谬误。

四、编译 Percona Xtrabackup

 Shell> tar xf percona-xtrabackup-percona-xtrabackup-8.0.25-17.tar.gz
    Shell> mv percona-xtrabackup-percona-xtrabackup-8.0.25-17 xtrbackup-8.0.25
    Shell> cd xtrbackup-8.0.25
    Shell>cmake -DWITH_BOOST=./include/boost_1_73_0 -DDOWNLOAD_BOOST=ON -DBUILD_CONFIG=xtrabackup_release -DWITH_MAN_PAGES=OFF -DFORCE_INSOURCE_BUILD=1
    Shell> echo $?
    Shell> make -j4
    Shell> echo $?
    Shell> mkdir /usr/local/xtrbackup-8.0.25
    Shell> make DESTDIR=/usr/local/xtrbackup-8.0.25 install
    Shell> /usr/local/xtrbackup-8.0.25/usr/local
    Shell> tar zcf arm_ky10_xtrabackup-8.0.25.tar.gz

五、在 ARM 下初始化装置 MySQL

应用 ARM 下中标麒麟零碎下的 MySQL 8.0.25，并初始化。

PS：在 ARM 下中标麒麟零碎编译 MySQL 8.0.25 请看之前文章。

5.1 初始化

    Shell> chown -R mysql.mysql /usr/local/mysql/
    Shell> mkdir /data/mysql/3306/{data,log,tmp,conf} -p
    Shell> chown -R mysql.mysql /data1/mysql/
    Shell> cd /data/mysql/3306/conf
    Shell> vim my.cnf
    Shell> /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/3306/conf/my.cnf --initialize

5.2 配置文件

[client]
    socket = /data/mysql/3306/mysql.sock
    default-character-set=utf8
    [mysqld]
    basedir = /usr/local/mysql
    datadir = /data/mysql/3306/data
    port = 3306
    #skip-grant-tables
    socket = /data/mysql/3306/mysql.sock
    user = mysql
    character_set_server=utf8
    lc-messages-dir=/usr/local/mysql/share/english
    plugin_dir=/usr/local/mysql/lib/plugin
    default_authentication_plugin = mysql_native_password
    back_log = 5000
    server-id = 1803306
    log-bin = /data/mysql/3306/log/mysql-bin
    binlog_format = row
    log-error = /data/mysql/3306/log/error.log
    enforce_gtid_consistency = 1
    expire_logs_days=15
    gtid_mode = on
    innodb_buffer_pool_size = 200m
    innodb_change_buffering = all
    innodb_doublewrite = true
    innodb_file_per_table = 1
    innodb_flush_log_at_trx_commit = 1
    innodb_flush_method = O_DIRECT
    innodb_log_buffer_size = 16M
    innodb_log_file_size = 100m
    innodb_log_files_in_group = 4
    innodb_print_all_deadlocks = on
    innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:81920M
    innodb_thread_concurrency = 0
    interactive_timeout = 31536000
    lock_wait_timeout = 600
    log_bin_trust_function_creators = 1
    log_timestamps = SYSTEM
    long_query_time = 10
    lower_case_table_names = 1
    master_info_repository = TABLE
    max_allowed_packet = 16M
    max_connections = 20480
    max_prepared_stmt_count = 1048576
    net_read_timeout = 10000
    net_write_timeout = 10000
    open_files_limit = 80000
    skip_external_locking = 1
    skip_name_resolve = 1
    sort_buffer_size = 2M
    sync_binlog = 1
    table_definition_cache = 5000
    table_open_cache = 5000
    thread_cache_size = 3000
    tmpdir = /data/mysql/3306/tmp
    transaction_isolation = READ-COMMITTED
    wait_timeout = 31536000

5.3 启动 MySQL

    Shell> /usr/local/mysql/bin/mysqld --defaults-file=/data1/mysql/3306/conf/my.cnf &

六、登录并应用 sysbench 压测数据

登录批改明码：

 Shell> cat /data1/mysql/3306/log/error.log |grep pass
    A temporary password is generated for root@localhost: JFbdzuFta1*o

    Shell> /usr/local/mysql/bin/mysql -uroot -p'JFbdzuFta1*o' -S /data1/mysql/3306/mysql.sock -P3306
    mysql> alter user user() identified by‘abc123’;
    Mysql> flush privileges; 

创立用户：

Shell> /usr/local/mysql/bin/mysql -uroot -p'abc123' -S /data1/mysql/3306/mysql.sock -P3306
    mysql> create user pcms@'%' identified by 'pcms@123';
    mysql> grant all privileges on *.* to pcms@'%';
    mysql> flush privileges; 

创立库并应用 sysbench 造数据：

mysql> create database pcms;
    // 应用 sysbench 造数
    Shell> sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=172.16.130.180 --mysql-port=3306 --mysql-user='root' --mysql-password='abc123' --mysql-db='sbtest' --tables=10 --table-size=5000 --threads=200 prepar
    // 查看
    MySQL [sbtest]> select count(*) from sbtest1;

七、备份与复原测试

全量备份测试：

    Shell> /usr/local/xtrabackup/bin/xtrabackup --defaults-file=/data/mysql/3306/conf/my.cnf --host=127.0.0.1 --user=root --password=abc123 --port=3306 --backup --target-dir=/root/backup/

输入信息：

Using server version 8.0.25
    210624 22:08:39 Executing LOCK INSTANCE FOR BACKUP...
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /data/mysql/3306/data
    xtrabackup: open files limit requested 80000, set to 1024000
    xtrabackup: using the following InnoDB configuration:
    xtrabackup:   innodb_data_home_dir = .
    xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup:   innodb_log_group_home_dir = ./
    xtrabackup:   innodb_log_files_in_group = 4
    xtrabackup:   innodb_log_file_size = 104857600
    xtrabackup: using O_DIRECT
    Number of pools: 1
    xtrabackup: inititialize_service_handles suceeded
    210624 22:08:39 Connecting to MySQL server host: 127.0.0.1, user: root, password: set, port: 3306, socket: /data/mysql/3306/mysql.sock
    xtrabackup: Redo Log Archiving is not set up.
    210624 22:08:39 >> log scanned up to (31673954)
    xtrabackup: Generating a list of tablespaces
    xtrabackup: Generating a list of tablespaces
    Scanning './'
    Completed space ID check of 2 files.
    Allocated tablespace ID 2 for sbtest/sbtest8, old maximum was 0
    210624 22:08:42 Backup created in directory '/root/backup/'
    MySQL binlog position: filename 'mysql-bin.000005', position '196', GTID of the last change '4c2b3352-d4f3-11eb-8b55-52540061b4c4:1-47'
    210624 22:08:42 [00] Writing /root/backup/backup-my.cnf
    210624 22:08:42 [00]...done
    210624 22:08:42 [00] Writing /root/backup/xtrabackup_info
    210624 22:08:42 [00]...done
    xtrabackup: Transaction log of lsn (31673954) to (31679741) was copied.
    210624 22:08:43 completed OK!

全量复原测试：

 // 删掉库并将数据库敞开
    Shell> mysql -uroot -p'abc123' -h'127.0.0.1'
    MySQL [(none)]> drop database sbtest;
    MySQL [(none)]> shutdown;
    // 筹备工作
    Shell> /usr/local/xtrabackup/bin/xtrabackup --host=127.0.0.1 --user=root --password=abc123 --port=3306 --prepare --target-dir=/root/backup/
    Shell> mv /data/mysql/3306/data/ /data/mysql/3306/data1
    Shell> mkdir /data/mysql/3306/data
    // 拷贝数据
    Shell> /usr/local/xtrabackup/bin/xtrabackup --host=127.0.0.1 --user=root --password=abc123 --port=3306 --datadir=/data/mysql/3306/data --copy-back --target-dir=/root/backup/

输入信息：

210625 02:34:36 [01] Copying ./performance_schema/keyring_componen_191.sdi to /data/mysql/3306/data/performance_schema/keyring_componen_191.sdi
    210625 02:34:36 [01]...done
    210625 02:34:36 [01] Copying ./ib_buffer_pool to /data/mysql/3306/data/ib_buffer_pool
    210625 02:34:36 [01]...done
    210625 02:34:36 [01] Copying ./xtrabackup_info to /data/mysql/3306/data/xtrabackup_info
    210625 02:34:36 [01]...done
    210625 02:34:36 [01] Copying ./xtrabackup_master_key_id to /data/mysql/3306/data/xtrabackup_master_key_id
    210625 02:34:36 [01]...done
    210625 02:34:36 [01] Copying ./ibtmp1 to /data/mysql/3306/data/ibtmp1
    210625 02:34:36 [01]...done
    210625 02:34:37 [01] Creating directory ./#innodb_temp
    210625 02:34:37 [01] ...done.
    210625 02:34:37 completed OK!
    // 批改目录属性启动数据库

    Shell> chown -R mysql:mysql /data/mysql/3306/data
    Shell> chmod -R 755 /data/mysql/3306/data
    // 启动数据库


    Shell> /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/3306/conf/my.cnf &
    // 检测 

全量复原实现：

    MySQL> select count(*) from sbtest.sbtest1;

八、增量备份及复原

如法炮制，略 ……

Enjoy GreatSQL :)

本文由博客一文多发平台 OpenWrite 公布！