技术分享 | ARM下中标麒麟系统ky10使用Xtrabackup-8.0.25 mysql数据库sql

欢迎来到 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下载地址：

技术分享 | ARM下中标麒麟系统ky10使用Xtrabackup-8.0.25

文章图片

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下中标麒麟系统ky10使用Xtrabackup-8.0.25】使用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*oShell> /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 & // 检测

全量恢复完成：