mysql8.4.4安装部署+xtrabackup备份
环境:
OS:Centos 7
mysql:8.4.4/8.4.6
xtrabackup:8.4.0-2
1.下载安装介质
我这里下载的是 mysql-8.4.4-linux-glibc2.17-x86_64.tar.xz
查看操作系统的GLIBC库
[root@node1 soft]# strings /lib64/libc.so.6 | grep -E "^GLIBC" | sort -V -r | uniq
GLIBC_PRIVATE
GLIBC_2.17
GLIBC_2.16
GLIBC_2.15
GLIBC_2.14
GLIBC_2.13
GLIBC_2.12
GLIBC_2.11
GLIBC_2.10
GLIBC_2.9
GLIBC_2.8
GLIBC_2.7
GLIBC_2.6
GLIBC_2.5
GLIBC_2.4
GLIBC_2.3.4
GLIBC_2.3.3
GLIBC_2.3.2
GLIBC_2.3
GLIBC_2.2.6
GLIBC_2.2.5
最新的是GLIBC_2.17，我们只能下载GLIBC_2.17版本的MySQL
下载地址：https://dev.mysql.com/downloads/mysql/
xtrabackup版本下载也是类似，找到glibc_2.17版本的下载
下载地址：https://www.percona.com/downloads
2.创建mysql用户和用户组
#groupadd mysql
#useradd -g mysql mysql
#passwd mysql
3.下载解压安装
[root@localhost soft]# tar -xvf mysql-8.4.4-linux-glibc2.17-x86_64.tar.xz
[root@localhost soft]# mv mysql-8.4.4-linux-glibc2.17-x86_64 /opt/mysql8
4.创建相应的目录
[root@rac02 mysql8]# cd /opt/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 /opt/mysql8/audit ##审计目录
5.配置my.cnf配置文件
在conf目录下创建配置文件my.cnf,配置文件内容如下
[mysqld]
port=3306
server-id=3
basedir=/opt/mysql8
datadir=/opt/mysql8/data
socket=/opt/mysql8/mysql.sock ##可以不需要指定，使用默认的/tmp/mysql.sock
max_connections = 1000
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= 32G
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= 10G
##myisam_repair_threads= 1
log_bin_trust_function_creators=1
event_scheduler=1
max_binlog_size=100M
binlog_format=row
log-bin=/opt/mysql8/mysqllog/binlog/binlog.bin
slow_query_log=on
slow_query_log_file=/opt/mysql8/mysqllog/logfile/slow-query.log
long_query_time=1
log_queries_not_using_indexes=on
log-error=/opt/mysql8/mysqllog/logfile/mysql-err.log
binlog_cache_size=4MB
##没有了skip-host-cache参数
##skip-host-cache
skip-name-resolve
##已经没有该参数了expire_logs_days=15
##15*24*60*60=1296000 15天
binlog_expire_logs_seconds=1296000
###skip-slave-start该参数去掉了,使用如下参数skip-slave-start,但是8.0.15还是使用该参数
skip_replica_start
relay-log-index=/opt/mysql8/mysqllog/relaylog/slave-relay-bin.index
relay-log=/opt/mysql8/mysqllog/relaylog/relaylog-binlog
replicate-ignore-db=information_schema,performance_schema,sys
##无该参数slave_net_timeout=60
replica_net_timeout=60
##language=/opt/mysql8/share/english
early-plugin-load=""
explicit_defaults_for_timestamp=true
##无该参数log_slave_updates=1,下面参数替代
log_replica_updates=1
gtid_mode=ON
enforce_gtid_consistency = ON
lower_case_table_names=1 ##需要在初始化的时候加上该参数 --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=/opt/mysql8/secure_file
mysql_native_password=on
[client]
port = 3306
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
6.初始化数据库
root账户下
[root@localhost bin]# cd /opt/mysql8/bin
[root@localhost bin]# ./mysqld --initialize --lower-case-table-names=1 --user=mysql --basedir=/opt/mysql8 --datadir=/opt/mysql8/data
2025-06-27T08:53:35.303377Z 0 [System] [MY-015017] [Server] MySQL Server Initialization - start.
2025-06-27T08:53:35.306358Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2025-06-27T08:53:35.306556Z 0 [System] [MY-013169] [Server] /opt/mysql8/bin/mysqld (mysqld 8.4.4) initializing of server in progress as process 23554
2025-06-27T08:53:35.350635Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-06-27T08:53:40.123894Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-06-27T08:53:53.780747Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: wlg)lMoqq5t>
7.修改目录权限
产生错误日志文件
[root@localhost mha]#echo>/opt/mysql8/mysqllog/logfile/mysql-err.log
[root@localhost mha]# cd /opt
[root@localhost opt]# chown -R mysql:mysql ./mysql8
8.启动
[root@rac02 conf]#/opt/mysql8/bin/mysqld_safe --defaults-file=/opt/mysql8/conf/my.cnf --user=mysql &
启动日志提示:
2025-06-27T09:02:16.757717Z 0 [System] [MY-010116] [Server] /opt/mysql8/bin/mysqld (mysqld 8.4.4) starting as process 2486
2025-06-27T09:02:16.816387Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=2684354560. Please use innodb_redo_log_capacity instead.
2025-06-27T09:02:16.820150Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
准备废弃参数
innodb_log_file_size
innodb_log_files_in_group
建议使用参数:
innodb_redo_log_capacity:(可用逻辑处理器数量/2)GB,最大为 16GB
innodb_redo_log_capacity=2GB
9.登陆数据库修改相应用户密码
[root@rac02 bin]# cd /opt/mysql8/bin
[root@rac02 bin]# ./mysql -h localhost -uroot -P3306 --socket=/opt/mysql8/mysql.sock -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;
10.创建开发用户
尝试使用之前的方式创建用户,发现不支持了
mysql> grant all on *.* to 'hxl'@'%' identified by "mysql";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by "mysql"' at line 1
在mysql8.0创建用户和授权和之前不太一样了，其实严格上来讲，也不能说是不一样, 只能说是更严格, mysql8.0需要先创建用户(创建用户时要带@并指定地址, 则grant授权时的地址就是这个@后面指定的!, 否则grant授权就会报错!)和设置密码,然后才能授权。
mysql> create user 'hxl'@'%' identified with mysql_native_password BY 'mysql';
ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded
grant all privileges on *.* to 'hxl'@'%' with grant option;
正确的方法:
create user 'hxl'@'%' identified by 'mysql';
grant all privileges on *.* to 'hxl'@'%' with grant option;
11.修改用户的密码认证方式(mysql_native_password,不建议)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | hxl | caching_sha2_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)
这里默认的密码插件是caching_sha2_password,而不是mysql_native_password,针对旧版本的navicate是无法连接的
下面尝试修改为mysql_native_password的方式,但不建议,建议是升级navicate版本
修改密码认证方式,确保旧版本的navicate能够登录
mysql> alter user 'hxl'@'%' identified with mysql_native_password by 'mysql';
ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded
这里报错误,配置文件需要加载参数,然后重启
mysql_native_password=on
重启
/opt/mysql8/bin/mysqladmin -h localhost -uroot -pmysql -S /opt/mysql8/mysql.sock shutdown
/opt/mysql8/bin/mysqld_safe --defaults-file=/opt/mysql8/conf/my.cnf --user=mysql &
再次更改
/opt/mysql8/bin/mysql -h localhost -uroot -pmysql -S /opt/mysql8/mysql.sock
mysql> alter user 'hxl'@'%' identified by 'mysql' password expire never;
Query OK, 0 rows affected (0.15 sec)
mysql> alter user 'hxl'@'%' identified with mysql_native_password by 'mysql';
Query OK, 0 rows affected (0.14 sec)
这个时候查看账号插件情况
mysql> select host,user,plugin from mysql.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)
##########################################xtrabackup备份###########################################
/opt/xtrabackup-8.4.0-2/bin/xtrabackup --defaults-file=/opt/mysql8/conf/my.cnf --user=root --socket=/opt/mysql8/mysql.sock --password=mysql -P3306 --no-version-check --backup --target-dir=/opt/xtrabackup_file/
浙公网安备 33010602011771号