环境:
OS:kylin V11
mysql:8.0.41 glibc2.28版
查看系统glibc版本
[root@localhost ~]# ldd --version
ldd (GNU libc) 2.38
Copyright (C) 2023 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
Written by Roland McGrath and Ulrich Drepper.
[root@localhost ~]# 或是
[root@localhost ~]# getconf -a | grep glibc
GNU_LIBC_VERSION glibc 2.38
1.查看操作系统
[root@localhost soft]# uname -a
Linux localhost.localdomain 6.6.0-32.7.v2505.ky11.x86_64 #1 SMP PREEMPT_DYNAMIC Fri Jul 4 13:31:43 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux
[root@localhost soft]# more /etc/os-release
NAME="Kylin Linux Advanced Server"
VERSION="V11 (Swan25)"
ID="kylin"
VERSION_ID="V11"
PRETTY_NAME="Kylin Linux Advanced Server V11 (Swan25)"
ANSI_COLOR="0;31"
2.获取mysql安装介质
mysql-8.0.41-linux-glibc2.28-x86_64.tar.xz
3.创建mysql用户和用户组
[root@localhost soft]#groupadd mysql
[root@localhost soft]#useradd -g mysql mysql
[root@localhost soft]#passwd mysql
4.创建安装主目录
[root@localhost soft]#mkdir -p /home/middle/
5.下载解压安装
[root@localhost soft]# tar -xvf mysql-8.0.41-linux-glibc2.28-x86_64.tar.xz
[root@localhost soft]# mv mysql-8.0.41-linux-glibc2.28-x86_64 /home/middle/mysql8
6.创建相应的目录
[root@node1 mysql8]# cd /home/middle/mysql8
[root@localhost mysql8]# mkdir data ##数据文件目录
[root@localhost mysql8]# mkdir conf ## 配置文件目录
[root@localhost mysql8]# mkdir -p mysqllog/relaylog ##主从环境relaylog
[root@localhost mysql8]# mkdir -p mysqllog/logfile ##错误日志文件
[root@localhost mysql8]# mkdir -p mysqllog/binlog ##binlog文件
[root@localhost mysql8]# mkdir -p secure_file ##secure_file_priv参数指定路
[root@localhost mysql8]#mkdir /home/middle/mysql8/audit ##审计目录
7.初始化数据库
root账户下
[root@localhost bin]# cd /home/middle/mysql8/bin
[root@localhost bin]# ./mysqld --initialize --lower-case-table-names=1 --user=mysql --basedir=/home/middle/mysql8 --datadir=/home/middle/mysql8/data
[root@localhost bin]# ./mysqld --initialize --lower-case-table-names=1 --user=mysql --basedir=/home/middle/mysql8 --datadir=/home/middle/mysql8/data
2025-09-08T07:50:54.579489Z 0 [System] [MY-013169] [Server] /home/middle/mysql8/bin/mysqld (mysqld 8.0.41) initializing of server in progress as process 5828
2025-09-08T07:50:54.589338Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-09-08T07:50:55.318426Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-09-08T08:07:04.043849Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: hOEdhhf2?Mru
8.准备配置文件
[root@node1 conf]# more my.cnf
[mysqld]
port=13306
server-id=1
basedir=/home/middle/mysql8
datadir=/home/middle/mysql8/data
max_connections = 5000
character_set_server=utf8mb4
collation-server=utf8mb4_general_ci
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
interactive_timeout=86400
wait_timeout=86400
skip-external-locking
key_buffer_size= 128M
max_allowed_packet=32M
##query_cache_size=32M
read_buffer_size=2M
sort_buffer_size=1M
join_buffer_size= 128M
innodb_file_per_table= 1
innodb_open_files= 5000
innodb_buffer_pool_size= 4G
innodb_write_io_threads= 16
innodb_read_io_threads= 16
innodb_thread_concurrency = 0
innodb_purge_threads= 1
innodb_flush_log_at_trx_commit= 2
innodb_log_buffer_size=16M
##准备废弃innodb_log_file_size和innodb_log_files_in_group,使用innodb_redo_log_capacity代替
##innodb_log_file_size=512M
##innodb_log_files_in_group= 5
innodb_redo_log_capacity = 2GB
innodb_max_dirty_pages_pct= 90
innodb_lock_wait_timeout= 120
bulk_insert_buffer_size= 64M
myisam_sort_buffer_size=64M
myisam_max_sort_file_size= 1G
##myisam_repair_threads= 1
log_bin_trust_function_creators=1
event_scheduler=1
max_binlog_size=100M
binlog_format=row
log-bin=/home/middle/mysql8/mysqllog/binlog/binlog.bin
slow_query_log=on
slow_query_log_file=/home/middle/mysql8/mysqllog/logfile/slow-query.log
long_query_time=1
log_queries_not_using_indexes=on
log-error=/home/middle/mysql8/mysqllog/logfile/mysql-err.log
binlog_cache_size=4MB
##skip-host-cache
skip-name-resolve
##已经没有该参数了expire_logs_days=7
##7*24*60*60=604800 7天
binlog_expire_logs_seconds=604800
##skip_replica_start
##skip-slave-start
skip_replica_start
relay-log-index=/home/middle/mysql8/mysqllog/relaylog/slave-relay-bin.index
relay-log=/home/middle/mysql8/mysqllog/relaylog/relaylog-binlog
replicate-ignore-db=information_schema,performance_schema,sys
replica_net_timeout=60
early-plugin-load=""
explicit_defaults_for_timestamp=true
log_replica_updates=1
##log_slave_updates=1
gtid_mode=ON
enforce_gtid_consistency = ON
lower_case_table_names=1
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO'
secure_file_priv=/home/middle/mysql8/secure_file
##default_authentication_plugin=mysql_native_password
authentication_policy=mysql_native_password[client]
port = 13306
default-character-set = utf8mb4[mysqldump]
quick
max_allowed_packet = 32M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
9.生成错误日志文件
[root@localhost conf]# echo>/home/middle/mysql8/mysqllog/logfile/mysql-err.log
10.修改目录权限
[root@localhost mha]# cd /home/middle
[root@localhost opt]# chown -R mysql:mysql ./mysql8
11.启动
(在root用户下执行)
[root@node1 opt]# /home/middle/mysql8/bin/mysqld_safe --defaults-file=/home/middle/mysql8/conf/my.cnf --user=mysql &
12.登陆数据库修改相应用户密码
[root@localhost middle]# /home/middle/mysql8/bin/mysql -h localhost -uroot -P13306 -p
mysql> select version();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
提示修改密码
alter user 'root'@'localhost' identified by 'mysql';
flush privileges;
13.重新登录
[root@localhost data]# /home/middle/mysql8/bin/mysql -h localhost -uroot -P13306 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.41 MySQL Community Server - GPLCopyright (c) 2000, 2025, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.41 |
+-----------+
1 row in set (0.00 sec)
14.创建数据库
mysql> create database db_test;
Query OK, 1 row affected (0.02 sec)mysql> show create database db_test;
15.创建开发用户
create user 'hxl'@'%' identified by 'mysql';
grant all privileges on db_test.* to 'hxl'@'%' with grant option;创建完成后查看加密方式:
mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | hxl | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)
16.做成开机自启动
设置开机自启动
在/etc/systemd/system目录下创建 mysql.service 文件
root下操作
vi /etc/systemd/system/mysql.service
[Unit]
Description=mysql
[Service]
User=mysql
LimitNOFILE=100000
LimitNPROC=100000
ExecStart=/home/middle/mysql8/bin/mysqld_safe --defaults-file=/home/middle/mysql8/conf/my.cnf
[Install]
WantedBy=multi-user.target
注意如下两个参数不能设置太小，否则mysql会不停重启动
LimitNOFILE=100000
LimitNPROC=100000
设置开机自启
systemctl enable mysql
systemctl daemon-reload
尝试启动
systemctl start mysql
systemctl status mysql
systemctl stop mysql
systemctl status mysql
可以的话进行系统reboot,看是否会自动启动
##############################xtrabackup备份##############################
1.获取介质
percona-xtrabackup-8.0.35-34-Linux-x86_64.glibc2.28.tar.gz
下载地址
https://www.percona.com/downloads
注意我们这里需要下载glibc2.28版本的,与mysql版本保持一致
2.解压安装
[root@localhost soft]# tar -xvf percona-xtrabackup-8.0.35-34-Linux-x86_64.glibc2.28.tar.gz
[root@localhost soft]# mv percona-xtrabackup-8.0.35-34-Linux-x86_64.glibc2.28 /opt/xtrabackup-8.0.35-34
3.备份
/opt/xtrabackup-8.0.35-34/bin/xtrabackup --defaults-file=/home/middle/mysql8/conf/my.cnf --user=root --socket=/tmp/mysql.sock --password=mysql -P13306 --no-version-check --backup --target-dir=/opt/xtrabackup_file/
/opt/xtrabackup-8.0.35-34/bin/xtrabackup --defaults-file=/home/middle/mysql8/conf/my.cnf --user=root --password=mysql --socket=/tmp/mysql.sock -P13306 --stream=tar /tmp|gzip >/tmp/xtrabackup_20250908.tar.gz
3.1 压缩备份
/opt/xtrabackup-8.0.35-34/bin/xtrabackup --defaults-file=/home/middle/mysql8/conf/my.cnf --user=root --password=mysql -P13306 --socket=/tmp/mysql.sock --compress --no-version-check --backup --target-dir=/opt/xtrabackup_file/mybak_`date "+%Y%m%d"`
解压
/opt/xtrabackup-8.0.35-34/bin/xtrabackup --decompress --parallel=5 --remove-original --target-dir=/opt/xtrabackup_file/mybak_20250908
3.2 mysql8.0 流式备份
xtrabackup --backup -u root -H 127.0.0.1 -P3306 -p123456 --stream=xbstream --target-dir=/bak/full > /bak/full/backup.xbstream
/opt/xtrabackup-8.0.35-34/bin/xtrabackup --defaults-file=/home/middle/mysql8/conf/my.cnf --user=root --password=mysql -P13306 --socket=/tmp/mysql.sock --stream=xbstream --no-version-check --backup --target-dir=/opt/xtrabackup_file>/opt/xtrabackup_file/mybackup.xbstream
解压流式备份
[root@xxx ~]# mkdir -p /opt/xtrabackup_file/mybackup
[root@xxx ~]# /opt/xtrabackup-8.0.35-34/bin/xbstream -x --parallel=10 -C /opt/xtrabackup_file/mybackup < /opt/xtrabackup_file/mybackup.xbstream
3.3 流+压缩备份
/opt/xtrabackup-8.0.35-34/bin/xtrabackup --defaults-file=/home/middle/mysql8/conf/my.cnf --user=root --password=mysql -P13306 --socket=/tmp/mysql.sock --no-version-check --backup --compress --stream=xbstream --target-dir=/opt/xtrabackup_file>/opt/xtrabackup_file/mybackup.xbstream
流解压
mkdir -p /opt/xtrabackup_file/mybackup
/opt/xtrabackup-8.0.35-34/bin/xbstream -x --parallel=10 -C /opt/xtrabackup_file/mybackup < /opt/xtrabackup_file/mybackup.xbstream
常规解压
/opt/xtrabackup-8.0.35-34/bin/xtrabackup --decompress --parallel=5 --remove-original --target-dir=/opt/xtrabackup_file/mybackup