mysql8安装(详细)+mcafee审计
环境:
OS:Centos 7
DB:8.0.28
1.下载安装介质
官网下载
我这里下载的是8.0.28
mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz
2.创建mysql用户和用户组
#groupadd mysql
#useradd -g mysql mysql
#passwd mysql
3.下载解压安装
[root@localhost soft]# tar -xvf mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz
[root@localhost soft]# mv mysql-8.0.28-linux-glibc2.12-x86_64 /home/middle/mysql8
4.创建相应的目录
[root@rac02 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 ##审计目录
5.配置my.cnf配置文件
在conf目录下创建配置文件my.cnf,配置文件内容如下
[mysqld]
port=13306
server-id=3
basedir=/home/middle/mysql8
datadir=/home/middle/mysql8/data
socket=/home/middle/mysql8/mysql.sock ##可以不需要指定，使用默认的/tmp/mysql.sock
max_connections = 10000
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=512M
innodb_log_files_in_group= 5
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=/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=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=/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
##无该参数slave_net_timeout=60
replica_net_timeout=60
##language=/home/middle/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=/home/middle/mysql8/secure_file
##使用mysql5.7的验证方式
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
6.初始化数据库
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
2022-03-03T07:25:30.725362Z 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.
2022-03-03T07:25:30.725477Z 0 [System] [MY-013169] [Server] /home/middle/mysql8/bin/mysqld (mysqld 8.0.28) initializing of server in progress as process 9234
2022-03-03T07:25:30.751117Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-03-03T07:25:31.925262Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-03-03T07:25:34.223533Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: #h*SXgejt4,q
若不想要初始化密码,不需要密码的话可以采用如下方法初始化话
./mysqld --initialize-insecure --lower-case-table-names=1 --user=mysql --basedir=/home/middle/mysql8 --datadir=/home/middle/mysql8/data
7.修改目录权限
[root@localhost mha]# cd /home/middle
[root@localhost opt]# chown -R mysql:mysql ./mysql8
8.启动
(在mysql用户下执行)
[root@rac02 conf]# echo>/home/middle/mysql8/mysqllog/logfile/mysql-err.log
[root@localhost mha]# cd /home/middle
[root@localhost opt]# chown -R mysql:mysql ./mysql8
[root@rac02 conf]#/home/middle/mysql8/bin/mysqld_safe --defaults-file=/home/middle/mysql8/conf/my.cnf --user=mysql &
9.登陆数据库修改相应用户密码
[root@rac02 bin]# cd /home/middle/mysql8/bin
[root@rac02 bin]# ./mysql -h localhost -uroot -P13306 --socket=/home/middle/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 with mysql_native_password BY 'mysql';
flush privileges;
重新登录
[root@rac02 bin]#./mysql -h localhost -uroot -P13306 --socket=/home/middle/mysql8/mysql.sock -p
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28 |
+-----------+
1 row in set (0.00 sec)
10.删除无关的账号(该步骤去掉，否则show databases show tables无法使用，还要重新创建用户)
mysql> delete from mysql.user where user='mysql.infoschema';
Query OK, 1 row affected (0.05 sec)
mysql> delete from mysql.user where user='mysql.sys';
Query OK, 1 row affected (0.02 sec)
mysql> delete from mysql.user where user='mysql.session';
Query OK, 1 row affected (0.04 sec)
这三个账号是锁住了的，不可登录
ERROR 3118 (HY000): Access denied for user 'mysql.sys'@'localhost'. Account is locked.
[root@host134 bin]# ./mysql -h localhost -umysql.infoschema -P13306 -p
Enter password:
ERROR 3118 (HY000): Access denied for user 'mysql.infoschema'@'localhost'. Account is locked.
[root@host134 bin]# ./mysql -h localhost -umysql.sys -P13306 -p
Enter password:
ERROR 3118 (HY000): Access denied for user 'mysql.sys'@'localhost'. Account is locked.
可以对其进行修改密码
alter user 'mysql.infoschema'@'localhost' identified with mysql_native_password BY 'mysql';
alter user 'mysql.session'@'localhost' identified with mysql_native_password BY 'mysql';
alter user 'mysql.sys'@'localhost' identified with mysql_native_password BY 'mysql';
10.创建开发用户
在mysql8.0创建用户和授权和之前不太一样了，其实严格上来讲，也不能说是不一样, 只能说是更严格, mysql8.0需要先创建用户(创建用户时要带@并指定地址, 则grant授权时的地址就是这个@后面指定的!, 否则grant授权就会报错!)和设置密码,然后才能授权。
create user 'hxl'@'%' identified with mysql_native_password BY 'mysql';
grant all privileges on *.* to 'hxl'@'%' with grant option;
mysql_native_password:说明兼容新老版本的认证方式,若不想加这句,需要在配置文件my.cnf中加上default_authentication_plugin=mysql_native_password
使用新创建的用户登录:
mysql> select user();
+---------------+
| user() |
+---------------+
| hxl@localhost |
+---------------+
1 row in set (0.02 sec)
11.尝试使用xtrabackup2.4备份mysql8.0
root@rac02 opt]# mkdir -p /tmp/xtrabackup_file8
root@rac02 opt]#yum install perl
root@rac02 opt]#yum install 'perl(Data::Dumper)'
root@rac02 opt]#yum -y install perl-Digest-MD5
root@rac02 opt]#yum install perl-DBD-MySQL
[root@rac02 bin]# /opt/xtrabackup-2.4.7/bin/innobackupex --defaults-file=/home/middle/mysql8/conf/my.cnf --user=root --password=mysql -P23306 --socket=/home/middle/mysql8/mysql.sock /tmp/xtrabackup_file8
220303 03:08:07 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
220303 03:08:07 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=23306;mysql_socket=/home/middle/mysql8/mysql.sock' as 'root' (using password: YES).
220303 03:08:07 version_check Connected to MySQL server
220303 03:08:07 version_check Executing a version check against the server...
220303 03:08:07 version_check Done.
220303 03:08:07 Connecting to MySQL server host: localhost, user: root, password: set, port: 23306, socket: /home/middle/mysql8/mysql.sock
Error: Unsupported server version: '8.0.28'. Please report a bug at https://bugs.launchpad.net/percona-xtrabackup
提示不支持的版本,说明xtrabckup2.4不能用于备份mysql8.0了.
遇到的问题:
mysql> show databases;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
原因是之前的步骤删除了账号:mysql.infoschema
解决办法:
mysql> create user 'mysql.infoschema'@'localhost' identified by 'mysql';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all privileges on *.* to 'mysql.infoschema'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
安装mcafee审计插件
mysql 8.0.28的需要安装的插件版本是:audit-plugin-mysql-8.0-1.1.12-999-linux-x86_64.zip
具体安装步骤请参考:
https://www.cnblogs.com/hxlasky/p/17628940.html
下面是简略的安装过程
1.解压安装
[root@host134 soft]# unzip audit-plugin-mysql-8.0-1.1.12-999-linux-x86_64.zip
[root@host134 soft]#cp /soft/audit-plugin-mysql-8.0-1.1.12-999/lib/libaudit_plugin.so /data/middle/mysql8/lib/plugin/
[root@host134 soft]#chown -R mysql:mysql /data/middle/mysql8/lib/plugin/libaudit_plugin.so
2.安装插件
[root@host134 10.11.3]# /data/middle/mysql8/bin/mysql -h localhost -uroot -pmysql -S /data/middle/mysql8/mysql.sock
mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
Query OK, 0 rows affected (8.11 sec)
3.在线启用
set global audit_json_file = on;
set global audit_record_cmds="create_index,drop_index,create_db,drop_db,create_user,drop_user,create_table,drop_table,alter_user,alter_db,alter_table,alter_tablespace,grant,revoke,revoke_all,truncate,quit,connect"; ##ddl
set global audit_json_log_file='/data/middle/mysql8/audit/mysql-audit.json'; ##该配置需要写到配置参数中才能生效
4.写入my.cnf配置文件
[root@localhost ~]# vi /data/middle/mysql8/conf/my.cnf
添加[mysqld]段以下内容
audit_json_file = on
plugin-load=AUDIT=libaudit_plugin.so
audit_record_cmds="create_index,drop_index,create_db,drop_db,create_user,drop_user,create_table,drop_table,alter_user,alter_db,alter_table,alter_tablespace,grant,revoke,revoke_all,truncate,quit,connect"
audit_json_log_file='/data/middle/mysql8/audit/mysql-audit.json'
audit_uninstall_plugin=1
5.重启数据库
/data/middle/mysql8/bin/mysqladmin -h localhost -uroot -P13306 -S /data/middle/mysql8/mysql.sock -p shutdown
/data/middle/mysql8/bin/mysqld_safe --defaults-file=/data/middle/mysql8/conf/my.cnf --user=mysql &
6.查看
/data/middle/mysql8/bin/mysql -h localhost -uroot -P13306 -S /data/middle/mysql8/mysql.sock -p
show variables like '%audit_record_cmds%';
show variables like '%audit%';
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？