linux下安装mysql二进制版本(5.7.33) 生产+安装审计
1. 介质准备
mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
该介质可以从官网上进行下载
2. 创建mysql用户和用户组
#groupadd mysql
#useradd -g mysql mysql
#passwd mysql
3. 下载解压二进制文件,解压,进入解压出来的文件
[root@localhost soft]# tar -xvf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
[root@localhost soft]# mv mysql-5.7.33-linux-glibc2.12-x86_64 /opt/mysql5733
[root@localhost soft]# cd /opt/mysql5733
[root@localhost mysql5733]# ls
bin docs include lib LICENSE man README share support-files
4. 在mysql主目录下建立相应的目录
data目录存放数据文件,conf目录存放配置文件
[root@localhost mysql5733]#cd /opt/mysql5733
[root@localhost mysql5733]#mkdir data ##数据文件目录
[root@localhost mysql5733]#mkdir conf ## 配置文件目录
[root@localhost mysql5733]#mkdir -p mysqllog/relaylog ##主从环境relaylog
[root@localhost mysql5733]#mkdir -p mysqllog/logfile ##错误日志文件
[root@localhost mysql5733]#mkdir -p mysqllog/binlog ##binlog文件
[root@localhost mysql5733]#mkdir -p secure_file ##secure_file_priv参数指定路
[root@localhost mysql5733]#mkdir redolog ##redo日志文件
5.修改系统配置
vi /etc/security/limits.conf ,最后面增加两行
* hard nofile 65535
* soft nofile 65535
##生效应用
#sysctl -p
然后退出重新登录
[mysql@localhost logfile]# ulimit -n
65535
修改vi /etc/selinux/config
SELINUX=disabled
5. 在conf目录下创建配置文件my.cnf,配置文件内容如下(直接复制粘贴不要用系统的复制功能)
[mysqld]
port=13306
server-id=1
basedir=/opt/mysql5733
datadir=/opt/mysql5733/data
socket=/opt/mysql5733/mysql.sock
max_connections = 16000
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= 100G ##物理内存的80%
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 ##5.7.39 5.7.42已经没有该参数
log_bin_trust_function_creators=1
event_scheduler=1
max_binlog_size=100M
binlog_format=row
log-bin=/opt/mysql5733/mysqllog/binlog/binlog.bin
slow_query_log=on
slow_query_log_file=/opt/mysql5733/mysqllog/logfile/slow-query.log
long_query_time=1
log_queries_not_using_indexes=on
log-error=/opt/mysql5733/mysqllog/logfile/mysql-err.log
binlog_cache_size=4MB
skip-host-cache
skip-name-resolve
expire_logs_days=15
skip-slave-start
relay-log-index=/opt/mysql5733/mysqllog/relaylog/slave-relay-bin.index
relay-log=/opt/mysql5733/mysqllog/relaylog/relaylog-binlog
replicate-ignore-db=information_schema,performance_schema,sys
slave_net_timeout=60
##language=/opt/mysql5733/share/english
early-plugin-load=""
explicit_defaults_for_timestamp=true
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,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
##secure_file_priv参数
secure_file_priv=/opt/mysql5733/secure_file
##启用ssl连接，客户端需要下载如下三个文件才能通过ssl连接
ssl-ca=/opt/mysql5733/data/ca.pem
ssl-cert=/opt/mysql5733/data/client-cert.pem
ssl-key=/opt/mysql5733/data/client-key.pem
##审计,需要安装审计插件才可用
server_audit_logging=on
server_audit_file_path =/opt/mysql5733/audit
server_audit_file_rotate_size=524288000
server_audit_file_rotations=256
server_audit_file_rotate_now=ON
server_audit_events='connect,table,query_ddl,query_dcl,query_dml_no_select';
##开启使用dblink
federated
##关闭ssl,默认是开启的
skip_ssl
[client]
port = 13306
socket=/opt/mysql5733/mysql.sock
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. 修改mysql目录权限
[root@localhost opt]# chown -R mysql:mysql ./mysql5733
7. 初始化数据库
root@localhost bin]# ./mysqld --initialize --user=mysql --basedir=/opt/mysql5733 --datadir=/opt/mysql5733/data --lc_messages_dir=/opt/mysql5733/share --lc_messages=en_US
2021-04-08T06:52:15.255824Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-04-08T06:52:27.425045Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-04-08T06:52:28.498904Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-04-08T06:52:28.793787Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: fbf83c6c-9836-11eb-8ec4-52540051cd25.
2021-04-08T06:52:28.941653Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-04-08T06:52:30.136173Z 0 [Warning] CA certificate ca.pem is self signed.
2021-04-08T06:52:30.491597Z 1 [Note] A temporary password is generated for root@localhost: Z>YSOb#if5H)
红色为临时密码
5.7之后的版本初始化会自动生成相应的证书
[root@localhost data]# ls -1
auto.cnf
ca-key.pem
ca.pem
client-cert.pem
client-key.pem
ib_buffer_pool
ibdata1
ib_logfile0
ib_logfile1
mysql
performance_schema
private_key.pem
public_key.pem
server-cert.pem
server-key.pem
sys
8. 启动(mysql下启动)
su - mysql
[mysql@localhost bin]$cd /opt/mysql5733/bin
[mysql@localhost bin]$ ./mysqld_safe --defaults-file=/opt/mysql5733/conf/my.cnf --user=mysql &
9. 登陆mysql并修改密码
[mysql@localhost bin]$ ./mysql -h localhost -uroot -P13306 -p -S /opt/mysql5733/mysql.sock
Enter password:
这里输入初始化的密码
修改密码
mysql> set password=password('mysql');
Query OK, 0 rows affected, 1 warning (0.10 sec)
或者
update mysql.user set authentication_string=password('mysql') where user='root' and host = 'localhost';
flush privileges;
删除系统自带用户(看实际情况需要)
mysql> delete from mysql.user where user='mysql.session';
Query OK, 1 row affected (0.06 sec)
mysql> delete from mysql.user where user='mysql.sys';
Query OK, 1 row affected (0.06 sec)
这两个用户在5.7下可以删除掉,但是在8.0不建议删除,show databases,show tables会使用到这些用户,若在5.7删除后,升级到8.0会自动生成这些用户
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| hxl | % |
| repl | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.28 |
+-----------+
1 row in set (0.00 sec)
10. 开启所有日志（看情况需要）
set global general_log=on;
set global log_output='TABLE'
永久生效的话可以将如下配置写到配置文件
[mysqld]
general_log = 1
log_output=TABLE
同时也可以写入到文件(默认是写入到文件的)
[mysqld]
general_log = 1
general_log_file = /opt/mysql5733/all.log
log_output=FILE,TABLE
开启后所有的日志会记录到mysql. general_log
mysql> select * from general_log;
+----------------------------+---------------------------+-----------+-----------+--------------+----------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+---------------------------+-----------+-----------+--------------+----------------------------+
| 2021-04-08 15:24:25.563842 | root[root] @ localhost [] | 5 | 1 | Query | show databases |
| 2021-04-08 15:24:30.730637 | root[root] @ localhost [] | 5 | 1 | Query | SELECT DATABASE() |
| 2021-04-08 15:24:30.730893 | root[root] @ localhost [] | 5 | 1 | Init DB | mysql |
| 2021-04-08 15:24:30.732618 | root[root] @ localhost [] | 5 | 1 | Query | show databases |
11. 开启审计
11.1 找到安全审计插件
可以下载mariadb后,解压找到server_audit.so
我这里下载的mariadb版本是10.4，将该文件上传到服务器的tmp目录
11.2 将server_audit.so 拷贝到mysql插件的路径下
mysql账号下操作
[mysql@localhost plugin]$ cp /tmp/server_audit.so /opt/mysql5733/lib/plugin/
注意权限,若不是mysql权限的话需要修改下权限
cd /opt/mysql5733/lib/plugin
chown mysql:mysql ./server_audit.so
11.3 安装审计插件
[mysql@localhost bin]$ ./mysql -h localhost -uroot -S /opt/mysql5733/mysql.sock -pmysql
INSTALL PLUGIN server_audit SONAME 'server_audit.so';
11.4 开启审计功能
先创建审计存储目录
[root@localhost mysql57]#mkdir -p /opt/mysql5733/audit
[root@localhost mysql57]#chown -R mysql:mysql ./audit/
开启审计
mysql>set global server_audit_logging=on;
mysql>set global server_audit_file_path='/opt/mysql5733/audit';
mysql>set global server_audit_file_rotate_size=524288000;
mysql>set global server_audit_file_rotations=256;
mysql>set global server_audit_file_rotate_now=ON;
mysql>set global server_audit_events='connect,table,query_ddl,query_dcl,query_dml_no_select';
根据实际情况设置审计操作,下面设置不记录insert update delete select
set global server_audit_events='connect,table,query_ddl,query_dcl';
将如下配置添加到初始化文件,避免重启失效(需要重启动)
server_audit_logging=on
server_audit_file_path =/opt/mysql5733/audit
server_audit_file_rotate_size=524288000
server_audit_file_rotations=256
server_audit_file_rotate_now=ON
server_audit_events='connect,table,query_ddl,query_dcl,query_dml_no_select';
根据实际情况设置审计操作,下面设置不记录insert update delete select
server_audit_events='connect,table,query_ddl,query_dcl';
11.5 验证
客户端写入数据到表
insert into tb_test01 values(100,'name1');
查看审计日志文件
[mysql@localhost audit]$ more server_audit.log
可以发现有记录如下的信息
20210408 16:46:59,localhost.localdomain,hxl,192.168.2.84,13,145,QUERY,db_hxl,'insert into tb_test01 values(100,\'name1\')',0
11.6 关闭审计
set global server_audit_logging=off;
11.7 卸载审计插件
mysql> uninstall plugin server_audit;
查看:
mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like 'server_audit%';
mysql> show plugins;
同时需要将审计插件的配置参数从配置文件中剔除掉
说明:
若主从库都启用了审计,在主库上的操作比如(create,drop,altert等)，在从库的审计日志上不会有体现。
