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

49次阅读

共计 7542 个字符，预计需要花费 19 分钟才能阅读完成。

欢送来到 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 公布！

正文完
mysql
发表至： mysql
2021-12-09
 0
关于mysql:GreatSQL重磅特性InnoDB并行并行查询优化测试
关于mysql:使用开源实时监控系统-HertzBeat-对-Mysql-数据库监控告警实践
关于mysql:MySQL事务ACID特性
关于mysql:mysql数据库汇总