- 准备机器
- 上传并解压安装包
#上传安装包到/usr/local目录下
mv /home/testuser/p35340956_580_Linux-x86-64-8.0.33.zip /root
#解压安装包
[root@SERVERNAME ~]# unzip p35340956_580_Linux-x86-64-8.0.33.zip
Archive: p35340956_580_Linux-x86-64-8.0.33.zip
extracting: mysql-commercial-8.0.33-linux-glibc2.28-x86_64.tar.gz
extracting: mysql-commercial-8.0.33-linux-glibc2.28-x86_64.tar.gz.asc
extracting: mysql-commercial-8.0.33-linux-glibc2.28-x86_64.tar.gz.md5
extracting: README.txt
#检查md5值是否正确(确保MD5值正确)
[root@SERVERNAME ~]# cat mysql-commercial-8.0.33-linux-glibc2.28-x86_64.tar.gz.md5
3d1b0587a397d71d39283ec925422e7e mysql-commercial-8.0.33-linux-glibc2.28-x86_64.tar.gz
[root@SERVERNAME ~]# md5sum mysql-commercial-8.0.33-linux-glibc2.28-x86_64.tar.gz
3d1b0587a397d71d39283ec925422e7e mysql-commercial-8.0.33-linux-glibc2.28-x86_64.tar.gz
#解压安装包到/usr/local目录下面
tar -zxf mysql-commercial-8.0.33-linux-glibc2.28-x86_64.tar.gz -C /usr/local
修改权限
chown -R mysql.mysql /usr/local/mysql-commercial-8.0.33-linux-glibc2.28-x86_64
创建链接
ln -s /usr/local/mysql-commercial-8.0.33-linux-glibc2.28-x86_64 /usr/local/mysql
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 创建mysql-files目录
- 编辑配置文件
vim /etc/my.cnf
----------
[mysqld]
user = mysql
server_id = 1
port = 3306
datadir = /data/mysql/data
pid-file=/data/mysql/data/mysqld.pid
socket=/data/mysql/data/mysql.sock
log-error = /var/log/mysql/error.log
tmpdir = /data/mysql/tmp
character_set_server = utf8mb4
collation_server = utf8mb4_bin
max_allowed_packet = 73400320
lower_case_table_names = 1
autocommit=on
open_files_limit = 65535
lock_wait_timeout = 3600
skip_name_resolve = 1
max_connections = 3000
connect_timeout = 10
skip-grant-tables = FALSE
log-raw = OFF
gtid_mode=ON
enforce-gtid-consistency=ON
long_query_time = 1
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/others/slow-queries.log
#mysql8.0
#log-bin = /data/mysql/logs/binlog/mysql-bin.log
log_bin = /data/mysql/logs/binlog/mysql-bin #如果后面采用恢复的方式,这里的名字一定要和原来的名字一样
log_timestamps=SYSTEM
binlog_expire_logs_seconds = 259200 #binlog保留时间 默认s
binlog_format = ROW
sync_binlog = 1
log_bin_trust_function_creators=1
# innodb settings #
innodb_data_file_path = ibdata1:1G:autoextend
innodb_file_per_table = 1 #指定是否为每个InnoDB表创建单独的数据文件。
innodb_flush_log_at_trx_commit = 1 #指定每个事务提交时是否将日志缓冲区写入磁盘。
innodb_buffer_pool_size = 64G
innodb_buffer_pool_instances = 4
innodb_redo_log_capacity=5368709120
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_method = O_DIRECT
tmp_table_size=3G
block_encryption_mode=aes-256-ecb
#密码策略
default_authentication_plugin=caching_sha2_password
password_history=5
password_reuse_interval=365
default_password_lifetime=365
[client]
socket=/data/mysql/data/mysql.sock
port=3306
----------
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 创建配置文件中出现的路径并修改owner
- 初始化数据库并获取root临时密码
#初始数据库
[root@SERVERNAME mysql]# cd /usr/local/mysql
[root@SERVERNAME mysql]# ./bin/mysqld --initialize --user=mysql
注意:初始化过程不能报错
#获取root的临时密码
[root@SERVERNAME mysql]# cat /var/log/mysql/error.log |grep password
2024-05-06T15:12:20.966958+08:00 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Nkty7oSDL&Tq
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 创建启动文件并启动数据库
#创建文件
cp support-files/mysql.server /etc/init.d/mysqld
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
#启动数据库
[root@SERVERNAME mysql]# /etc/init.d/mysqld start
Starting MySQL..... SUCCESS!
#链接数据库并修改root密码
[root@SERVERNAME mysql]# mysql -uroot -p
mysql> select user,host from mysql.user;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user user() identified by '********';
Query OK, 0 rows affected (0.00 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 编辑环境变量文件
#编辑全局环境变量
vim /etc/profile
------------
PATH=/usr/local/mysql/bin:$PATH
export PATH
---------
source /etc/profile
#编辑mysql用户环境变量
su - mysql
vi /home/mysql/.bash_profile
----------
PATH=/usr/local/mysql/bin:$PATH:$HOME/.local/bin:$HOME/bin
export PATH
---------
source /home/mysql/.bash_profile
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 开启连接控制
#连接控制-需要在初始化之后开启,不然会报错
[mysqld]
#plugin-load-add=connection_control.so
#connection-control=FORCE_PLUS_PERMANENT
#connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT
#connection_control_failed_connections_threshold=5
#connection_control_min_connection_delay=60000 #单位毫秒,1分钟
#connection_control_max_connection_delay=1920000 #单位毫秒,32分钟
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 安装密码组件
#安装组件
INSTALL COMPONENT 'file://component_validate_password';
#设置参数 --命令方式
SET PERSIST validate_password.length=12;
SET PERSIST validate_password.check_user_name=ON;
#SET PERSIST validate_password.dictionary_file=/usr/local/mysql/mysql-files/passwd_dict;
SET PERSIST validate_password.policy=STRONG;
SET PERSIST validate_password.mixed_case_count=1;
SET PERSIST validate_password.number_count=1;
SET PERSIST validate_password.special_char_count=1;
#设置参数 --配置方式
[mysqld]
validate_password.length=12
validate_password.check_user_name=ON
#validate_password.dictionary_file=/usr/local/mysql/mysql-files/passwd_dict
validate_password.policy=STRONG
validate_password.mixed_case_count=1
validate_password.number_count=1
validate_password.special_char_count=1
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 开启审计
#安装审计
mysql -u root -p < audit_log_filter_linux_install.sql
#编辑my.cnf文件
[mysqld]
#audit配置
audit_log=FORCE_PLUS_PERMANENT
audit_log_format=JSON
audit_log_rotate_on_size=104857600 #100MB
audit_log_strategy=PERFORMANCE
audit_log_file=audit.json
#设置过滤并启用
set @mysql_filter='\
{\
"filter": {\
"class": [\
{\
"name": "connection",\
"event": [\
{ "name": "connect" },\
{ "name": "disconnect" }\
]\
},\
{\
"name": "general",\
"event": {\
"name": "status",\
"log": {\
"and": [\
{\
"or": [\
{"field": { "name": "general_command.str", "value": "Query" }},\
{"field": { "name": "general_command.str", "value": "Execute" }}\
]\
},\
{\
"or": [\
{"field": { "name": "general_sql_command.str", "value": "alter_db" }},\
{"field": { "name": "general_sql_command.str", "value": "create_db" }},\
{"field": { "name": "general_sql_command.str", "value": "drop_db" }},\
{"field": { "name": "general_sql_command.str", "value": "create_role" }},\
{"field": { "name": "general_sql_command.str", "value": "set_role" }},\
{"field": { "name": "general_sql_command.str", "value": "drop_role" }},\
{"field": { "name": "general_sql_command.str", "value": "create_user" }},\
{"field": { "name": "general_sql_command.str", "value": "rename_user" }},\
{"field": { "name": "general_sql_command.str", "value": "alter_user" }},\
{"field": { "name": "general_sql_command.str", "value": "drop_user" }},\
{"field": { "name": "general_sql_command.str", "value": "grant" }},\
{"field": { "name": "general_sql_command.str", "value": "grant_roles" }},\
{"field": { "name": "general_sql_command.str", "value": "revoke" }},\
{"field": { "name": "general_sql_command.str", "value": "revoke_all" }},\
{"field": { "name": "general_sql_command.str", "value": "revoke_roles" }},\
{"field": { "name": "general_sql_command.str", "value": "set_password" }},\
{"field": { "name": "general_sql_command.str", "value": "change_replication_source" }},\
{"field": { "name": "general_sql_command.str", "value": "change_repl_filter" }},\
{"field": { "name": "general_sql_command.str", "value": "slave_start" }},\
{"field": { "name": "general_sql_command.str", "value": "slave_stop" }},\
{"field": { "name": "general_sql_command.str", "value": "group_replication_start" }},\
{"field": { "name": "general_sql_command.str", "value": "group_replication_stop" }},\
{"field": { "name": "general_sql_command.str", "value": "lock_instance" }},\
{"field": { "name": "general_sql_command.str", "value": "unlock_instance" }},\
{"field": { "name": "general_sql_command.str", "value": "alter_instance" }},\
{"field": { "name": "general_sql_command.str", "value": "flush" }},\
{"field": { "name": "general_sql_command.str", "value": "set_option" }},\
{"field": { "name": "general_sql_command.str", "value": "reset" }},\
{"field": { "name": "general_sql_command.str", "value": "kill" }},\
{"field": { "name": "general_sql_command.str", "value": "shutdown" }},\
{"field": { "name": "general_sql_command.str", "value": "restart" }}\
]\
}\
]\
}\
}\
}\
]\
}\
}';
SELECT json_valid(@mysql_filter);
SELECT audit_log_filter_set_filter('mysql_adt',@mysql_filter);
SELECT audit_log_filter_set_user('%', 'mysql_adt');
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 创建备份脚本
#创建目录
mkdir -p /data/jobs
mkdir -p /var/log/mysqlbackup
mkdir -p /data/backup/mysqlbackup
#全备脚本
vi /data/jobs/mysqlbackup_full.sh
----------------
#!/bin/bash
source /home/mysql/.bash_profile
DT=`date +"%Y%m%d"`
target_dir=/data/backup/mysqlbackup
log_dir=/var/log/mysqlbackup
mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=${target_dir}/backup_${DT} --backup-image=${target_dir}/backup_${DT}/full_${DT}.mbi --user=mysqlbackup -p******** --read-threads=3 --process-threads=8 --write-threads=3 --compress backup-to-image >>${log_dir}/backup_${DT}.log 2>&1
var=`echo $?`
if [ $var -eq 0 ];
then
echo `date +"%Y-%m-%d %H:%M:%S"`" backup success"
cd ${target_dir}
cp -r backup_${DT} /mysqlbackup/mysqlbackup/
find ${target_dir} -maxdepth 1 -type d -mtime +7 -name "backup_*" -exec rm -rf {} \;
find /mysqlbackup/mysqlbackup/ -maxdepth 2 -type d -mtime +7 -name "backup_*" -exec rm -rf {} \;
else
echo $DT: "ERROR:backup failed"
fi
----------------
#增量备份脚本
vi /data/jobs/mysqlbackup_inc.sh
----------------
#!/bin/bash
source /home/mysql/.bash_profile
DT=`date +"%Y%m%d"`
YD=`date +%Y%m%d --date="-1 day"`
target_dir=/data/backup/mysqlbackup
log_dir=/var/log/mysqlbackup
mysqlbackup --defaults-file=/etc/my.cnf --incremental=optimistic --incremental-base=dir:${target_dir}/backup_${YD} --backup-dir=${target_dir}/backup_${DT} --backup-image=${target_dir}/backup_${DT}/inc_${DT}.mbi --user=mysqlbackup --password=******** backup-to-image >>${log_dir}/backup_${DT}.log
var=`echo $?`
if [ $var -eq 0 ];
then
echo `date +"%Y-%m-%d %H:%M:%S"`" backup success"
cd ${target_dir}
cp -r backup_${DT} /mysqlbackup/mysqlbackup/
find ${target_dir} -maxdepth 1 -type d -mtime +7 -name "backup_*" -exec rm -rf {} \;
find /mysqlbackup/mysqlbackup/ -maxdepth 2 -type d -mtime +7 -name "backup_*" -exec rm -rf {} \;
else
echo $DT: "ERROR:backup failed"
fi
----------------
#授权
chmod +x /data/jobs/mysqlbackup_full.sh
chmod +x /data/jobs/mysqlbackup_inc.sh
chown -R mysql.mysql /data/jobs
chown -R mysql.mysql /var/log/mysqlbackup
chown -R mysql.mysql /data/backup/mysqlbackup
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 设置定时任务
vim /etc/cron.allow
-----------
mysql #添加mysql用户
-----------
su - mysql
crontab -e
-------
#0 1 * * 6 /bin/sh /data/jobs/mysqlbackup_full.sh >> /var/log/mysqlbackup/mysqlbackup_full.log
#0 1 * * 0,1,2,3,4,5 /bin/sh /data/jobs/mysqlbackup_inc.sh >> /var/log/mysqlbackup/mysqlbackup_inc.log
------------
crontab -l
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 创建用户
#普通创建
create user 'testuser'@'%' identified by '*******';
#严格表准
alter user 'testuser'@'%' password expire interval 365 day password history 5 password reuse interval 365 day password require current failed_login_attempts 12;
#查看用户
select user,host,plugin ,password_expired,password_lifetime,Password_reuse_history,Password_reuse_time, Password_require_current from mysql.user;
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.