linux下安装mysql8.0(二进制方式)+xtrabackup+审计插件(Percona-Server)
环境
OS:Centos 7
mysql:8.0.43
xtrabackup:8.0.35
glibc2.17版本
审计插件获取:Percona-Server-8.0.42-33-Linux.x86_64.glibc2.28-minimal.tar.gz
查看GLIBC库
[root@localhost 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
1.下载安装介质
官网下载
我这里下载的是8.0.43
mysql-8.0.43-linux-glibc2.17-x86_64.tar.xz
注意这里是glibc2.17(centos 7系统默认的最高版本)
2.创建mysql用户和用户组
#groupadd mysql
#useradd -g mysql mysql
#passwd mysql
3.下载解压安装
[root@localhost soft]# tar -xvf mysql-8.0.43-linux-glibc2.17-x86_64.tar.xz
[root@localhost soft]# mv mysql-8.0.17-linux-glibc2.12-x86_64 /opt/mysql8
4.创建相应的目录
[root@node1 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.初始化数据库
root账户下
[root@localhost bin]# ./mysqld --initialize --lower-case-table-names=1 --user=mysql --basedir=/opt/mysql8 --datadir=/opt/mysql8/data
2019-09-05T08:25:17.210466Z 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.
2019-09-05T08:25:17.210613Z 0 [System] [MY-013169] [Server] /opt/mha/mysql8/bin/mysqld (mysqld 8.0.17) initializing of server in progress as process 25951
2019-09-05T08:25:38.288278Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: vew-DWj=q4r<
2019-09-05T08:25:49.555673Z 0 [System] [MY-013170] [Server] /opt/mha/mysql8/bin/mysqld (mysqld 8.0.17) initializing of server has complete
这里初始化密码为vew-DWj=q4r<
若不想要初始化密码,不需要密码的话可以采用如下方法初始化话
./mysqld --initialize-insecure --user=mysql --basedir=/opt/mha/mysql8 --datadir=/opt/mha/mysql8/data
6.配置文件
[root@node1 conf]# more my.cnf
[mysqld]
port=13306
server-id=3
basedir=/opt/mysql8
datadir=/opt/mysql8/data
socket=/opt/mysql8/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= 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=/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-name-resolve
##已经没有该参数了expire_logs_days=15
##3*24*60*60=259200 3天
binlog_expire_logs_seconds=259200
##skip_replica_start
##skip-slave-start
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
replica_net_timeout=60
##language=/opt/mysql8/share/english
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=/opt/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
7.生成错误日志文件
[root@node1 opt]# echo>/opt/mysql8/mysqllog/logfile/mysql-err.log
7.修改目录权限
[root@localhost mha]# cd /opt
[root@localhost opt]# chown -R mysql:mysql ./mysql8
8.启动
(在root用户下执行)
[root@node1 opt]# /opt/mysql8/bin/mysqld_safe --defaults-file=/opt/mysql8/conf/my.cnf --user=mysql &
9.登陆数据库修改相应用户密码
[mysql@localhost bin]$ ./mysql -h localhost -uroot -P13306 -S /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@localhost bin]$ ./mysql -h localhost -uroot -P13306 -S /opt/mysql8/mysql.sock -pmysql
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.43 |
+-----------+
1 row in set (0.00 sec)
11.创建开发用户
在mysql8.0创建用户和授权和之前不太一样了，其实严格上来讲，也不能说是不一样, 只能说是更严格, mysql8.0需要先创建用户(创建用户时要带@并指定地址, 则grant授权时的地址就是这个@后面指定的!, 否则grant授权就会报错!)和设置密码,然后才能授权。
create user 'hxl'@'%' identified by 'mysql';
grant all privileges on *.* to 'hxl'@'%' with grant option;
采用5.7之前的办法创建用户会报错
mysql> grant all privileges on *.* to 'hxl01'@'%' 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
创建完账号后查看，账号使用的加密方式
mysql> create user 'hxl'@'%' identified by 'mysql';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all privileges on *.* to 'hxl'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)
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)
从启动日志看,mysql已经不建议使用mysql_native_password的验证方式了
2025-08-13T08:57:13.341321Z 0 [System] [MY-010931] [Server] /opt/mysql8/bin/mysqld: ready for connections. Version: '8.0.43' socket
: '/opt/mysql8/mysql.sock' port: 3306 MySQL Community Server - GPL.
2025-08-13T08:58:58.526270Z 8 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is depr
ecated and will be removed in a future release. Please use caching_sha2_password instead'
##############################xtrabackup备份##############################
1.下载安装介质
percona-xtrabackup-8.0.35-31-Linux-x86_64.glibc2.17.tar.gz
下载地址：https://www.percona.com/downloads
注意centos7系统需要下载glibc2.17版本的
2.备份
/opt/xtrabackup-8.0.35-31/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/
#######################################安装审计插件#############################
1.下载Percona Server for MySQL
Percona-Server-8.0.42-33-Linux.x86_64.glibc2.28-minimal.tar.gz
2.解压获取audit_log.so
cp audit_log.so /opt/mysql8/lib/plugin/
chown -R mysql:mysql /opt/mysql8
3.安装插件
/opt/mysql8/bin/mysql -h localhost -uroot -P3306 -S /opt/mysql8/mysql.sock -p
mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';
ERROR 1126 (HY000): Can't open shared library '/opt/mysql8/lib/plugin/audit_log.so'
(errno: 11 /lib64/libstdc++.so.6: version `CXXABI_1.3.9' not found(required by /opt/mysql8/lib/plugin/audit_log.so))
解决办法查看这里
https://i.cnblogs.com/posts/edit-done;postId=19036338;isPublished=true
4.配置文件添加如下参数
plugin-load = audit_log.so
audit_log_file = /opt/mysql8/audit/audit.log
audit_log_format = CSV
audit_log_policy = LOGINS
audit_log_handler = FILE
audit_log_rotate_on_size = 1048576
##记录所有的日志
audit_log_policy = ALL
audit_log:设置为ON表示开启审计日志记录
audit_log_format:指定审计日志的格式
audit_log_policy:指定审计策略，这里我们选择了ALL，表示记录所有操作
audit_log_file:指定日志文件的路径和文件名，这里我们将日志写入到 audit.log 文件中
其中 audit_log_policy 的取值有：
ALL - all events will be logged
LOGINS - only logins will be logged
QUERIES - only queries will be logged
NONE - no events will be logged
5.重启数据库
/opt/mysql8/bin/mysqladmin -h localhost -uroot -P3306 -S /opt/mysql8/mysql.sock -p shutdown
/opt/mysql8/bin/mysqld_safe --defaults-file=/opt/mysql8/conf/my.cnf --user=mysql &
6.查看插件安装情况
mysql> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%';
+-------------+----------------+---------------+-------------+---------------------+----------------+------------------------+------------------------------------+--------------------+----------------+-------------+
| PLUGIN_NAME | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR | PLUGIN_DESCRIPTION | PLUGIN_LICENSE | LOAD_OPTION |
+-------------+----------------+---------------+-------------+---------------------+----------------+------------------------+------------------------------------+--------------------+----------------+-------------+
| audit_log | 0.2 | ACTIVE | AUDIT | 4.1 | audit_log.so | 1.11 | Percona LLC and/or its affiliates. | Audit log | GPL | ON |
+-------------+----------------+---------------+-------------+---------------------+----------------+------------------------+------------------------------------+--------------------+----------------+-------------+
1 row in set (0.00 sec)
mysql> SHOW variables LIKE 'audit%';
+-----------------------------+---------------+
| Variable_name | Value |
+-----------------------------+---------------+
| audit_log_buffer_size | 1048576 |
| audit_log_exclude_accounts | |
| audit_log_exclude_commands | |
| audit_log_exclude_databases | |
| audit_log_file | audit.log |
| audit_log_flush | OFF |
| audit_log_format | OLD |
| audit_log_handler | FILE |
| audit_log_include_accounts | |
| audit_log_include_commands | |
| audit_log_include_databases | |
| audit_log_policy | ALL |
| audit_log_rotate_on_size | 0 |
| audit_log_rotations | 0 |
| audit_log_strategy | ASYNCHRONOUS |
| audit_log_syslog_facility | LOG_USER |
| audit_log_syslog_ident | percona-audit |
| audit_log_syslog_priority | LOG_INFO |
+-----------------------------+---------------+
18 rows in set (0.01 sec)
mysql> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME LIKE '%audit%'\G
*************************** 1. row ***************************
PLUGIN_NAME: audit_log
PLUGIN_VERSION: 0.2
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: AUDIT
PLUGIN_TYPE_VERSION: 4.1
PLUGIN_LIBRARY: audit_log.so
PLUGIN_LIBRARY_VERSION: 1.11
PLUGIN_AUTHOR: Percona LLC and/or its affiliates.
PLUGIN_DESCRIPTION: Audit log
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
1 row in set (0.00 sec)
浙公网安备 33010602011771号