使用二进制tar包部署mysql企业版数据库
1.准备机器
#关闭防火墙
systemctl stop firewalld
systemctl disable firewalld
#关闭selinux
#安装依赖
yum -y install unzip
yum search libaio
yum install libaio
#在oracle Linux 8或者RHEL8 上安装el7版本或者glibc2.12版本,如果没有/lib64/libtinfo.so.5,执行下面的命令安装
yum install ncurses-compat-libs
2.上传并解压安装包
#上传安装包到/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
3.创建mysql-files目录
cd /usr/local/mysql
mkdir mysql-files
chown -R mysql.mysql mysql-files
chmod 750 mysql-files
4.编辑配置文件
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
----------
5.创建配置文件中出现的路径并修改owner
mkdir -p /data/mysql/data
mkdir -p /data/mysql/logs/{binlog,others}
mkdir -p /var/log/mysql/
mkdir -p /data/mysql/tmp
chown -R mysql.mysql /data/mysql/
chown -R mysql.mysql /var/log/mysql/
chown -R mysql.mysql /data/mysql/tmp
6..初始化数据库并获取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
7.创建启动文件并启动数据库
#创建文件
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)
8.编辑环境变量文件
#编辑全局环境变量
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
9.开启连接控制
#连接控制-需要在初始化之后开启,不然会报错
[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分钟
10.安装密码组件
#安装组件
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
11.开启审计
#安装审计
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');
12. 创建备份脚本
#创建目录
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
13.设置定时任务
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
14. 创建用户
#普通创建
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;
本文来自博客园,作者:DBer_ablewang,转载请注明原文链接:https://www.cnblogs.com/dber-ablewang/p/18267075