一、系統準備
1、RHEL關閉防火墻
systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl status firewalld.service2、關閉Selinux
##重啟后生效
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
setenforce 0
##重啟后檢查
getenforce3、/etc/hosts解析（示例如下）
vim /etc/hostname
mysql8vim /etc/hosts
192.168.11.11 mysql84、掛載Yum源
mount /dev/cdrom /mnt
mkdir /etc/yum.repos.d/bak -p
mv /etc/yum.repos.d/* /etc/yum.repos.d/bak
cat<<EOF>/etc/yum.repos.d/local.repo
[local]
name=local
baseurl=file:///mnt
enabled=1
gpgcheck=0
EOF5、安裝Yum包
yum -y install wget cmake gcc gcc-c++ numactl autoconf ncurses ncurses-devel libaio-devel openssl openssl-devel perl-devel perl-JSON.noarch vim-enhanced-7.4.160-5.el7.x86_64
yum -y install lvm2 rz vim6、清理系統環境
Centos7或者RHEL7版本的系統默認自帶安裝了MariaDB，需要先清理
[root@mysql8 ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.52-1.el7.x86_64
[root@mysql8 ~]# yum list installed | grep mariadb
mariadb-libs.x86_64 1:5.5.52-1.el7 @anaconda
[root@mysql8 ~]# yum -y remove mariadb-libs.x86_64
[root@mysql8 ~]# yum list installed | grep mariadb
[root@mysql8 ~]# rpm -qa |grep mariadb7、確定時區與時間是否正確
[root@localhost tmp]# date
Fri Oct 21 23:59:28 CST 2022
[root@localhost tmp]# date +"%Y-%m-%d %H:%M:%S"
2022-10-21 23:53:35
[root@localhost tmp]# timedatectl | grep -i "Time zone"
Time zone: Asia/Shanghai (CST, +0800)如果不對，修改如下
[root@localhost ~]# timedatectl set-timezone Asia/Shanghai
[root@localhost ~]# date -s '2023-05-16 10:05:30'8、設置Mysql系統賬戶資源限制
vim /etc/security/limits.conf
mysql soft nproc 2047
mysql hard nproc 16384
mysql soft nofile 1024
mysql hard nofile 65536二、MySQL安裝
1、創建數據庫用戶，創建實例所需目錄
root 用戶操作：
mkdir -p /mysql/app/mysql3306
mkdir -p /mysql/conf/
mkdir -p /mysql/data/mysql3306
mkdir -p /mysql/data/mysql3306/data/
mkdir -p /mysql/data/mysql3306/pid/
mkdir -p /mysql/data/mysql3306/socket/
mkdir -p /mysql/data/mysql3306/log/
mkdir -p /mysql/data/mysql3306/binlog/
mkdir -p /mysql/data/mysql3306/relaylog/
mkdir -p /mysql/data/mysql3306/slowlog/
mkdir -p /mysql/data/mysql3306/tmp/
mkdir -p /mysql/data/mysql3306/errlog/
mkdir -p /mysql/data/mysql3306/generallog/檢查系統原有的Mysql用戶和組，刪除mysql用戶重新添加
userdel mysql
groupadd mysql
useradd -g mysql mysql
chown -R mysql:mysql /mysql
echo Mysql12# | passwd --stdin mysql2、上傳軟件包并解壓安裝程序包
md5值驗證，保證下載到的軟件包無破損木馬
md5sum mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
0bb9fd978d8b122d7846efc37884c0bb mysql-8.0.33-linux-glibc2.12-x86_64.tar.xzcd /mysql/app/
tar xvf /soft/mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz -C /mysql/app/
mv /mysql/app/mysql-8.0.33-linux-glibc2.12-x86_64 /mysql/app/mysql8.0.33
chown -R mysql:mysql /soft
chown -R mysql:mysql /mysql
chown -R mysql:mysql /data
chown -R mysql:mysql /backup3、配置Mysql用戶環境變量
cat >> /home/mysql/.bash_profile << "EOF"
MYSQL_HOME=/mysql/app/mysql8.0.33
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$MYSQL_HOME/bin:/mysql/app/mysql-shell/bin:/mysql/app/mysqlrouter/bin
EOF
source /home/mysql/.bash_profile
which mysql4、創建參數文件
計算方式
innodb_buffer_pool_size = 服務器內存 * 70% = innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances su - mysql
vim /mysql/conf/my3306.cnf
[client]
socket = /mysql/data/mysql3306/socket/mysql.sock
port=3306
[mysqld]
#server configurationn
user = mysql
server_id = 100
port=3306
default_storage_engine= InnoDB
disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,ARCHIIIVE,MEMORY
basedir=/mysql/app/mysql8.0.33
datadir=/mysql/data/mysql3306/data/
socket=/mysql/data/mysql3306/socket/mysql.sock
pid-file=/mysql/data/mysql3306/pid/mysqld.pid
max_allowed_packet = 128M
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
lower_case_table_names = 1
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
default-time_zone='+8:00'
authentication_policy=mysql_native_password # 加此參數可遠程登陸
# connection #
interactive_timeout = 300
wait_timeout = 300
lock_wait_timeout = 300
max_user_connections = 800
skip_name_resolve = 1
max_connections = 3000
max_connect_errors = 1000
# log settings #
slow_query_log = ON
long_query_time = 2
log_queries_not_using_indexes = 1
slow_query_log_file = /mysql/data/mysql3306/slowlog/slow3306.log
binlog_expire_logs_seconds = 604800
binlog_gtid_simple_recovery = 1
sync_binlog = 1
binlog_format = ROW
log_error = /mysql/data/mysql3306/errlog/err3306.log
log_error_verbosity = 3
log_bin = /mysql/data/mysql3306/binlog/mysql_bin
log_bin_index = /mysql/data/mysql3306/binlog/mysql_binlog.index
general_log_file = /mysql/data/mysql3306/generallog/general.log
log_slow_admin_statements = 1
min_examined_row_limit = 100
log_throttle_queries_not_using_indexes = 1000
innodb_flush_log_at_trx_commit=1
log_timestamps = system
log_replica_updates=1
# innodb settings #
innodb_buffer_pool_size = 22G
innodb_buffer_pool_chunk_size = 128M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_redo_log_capacity = 200M
innodb_io_capacity = 5000
innodb_io_capacity_max = 10000
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_buffer_pool_dump_pct = 40
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size=40M
innodb_open_files = 4096
#單機模式不要添加#
# replication settings #
gtid_mode = ON
enforce_gtid_consistency = ON
plugin_dir=/mysql/app/mysql8.0.33/lib/plugin/5、數據庫初始化
su - mysql
mysqld --defaults-file=/mysql/conf/my3306.cnf --initialize --user=mysql --basedir=/mysql/app/mysql8.0.33 --datadir=/mysql/data/mysql3306/data/6、啟動MySQL
mysqld_safe --defaults-file=/mysql/conf/my3306.cnf --user=mysql &7、第一次登錄MySQL
需要到 err3306.log 去查詢臨時生成密碼
[mysql@mysql8 ~]$ cat /mysql/data/mysql3306/errlog/err3306.log | grep password
2022-09-19T08:24:49.938088Z 0 [Note] [MY-010309] [Server] Auto generated RSA key files through --sha256_password_auto_generate_rsa_keys are placed in data directory.
2022-09-19T08:24:49.938148Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory.
2022-09-19T08:24:49.941785Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: e5Xl>+apoP?g
2022-09-19T08:25:47.675052Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --sha256_password_auto_generate_rsa_keys as key files are present in data directory.
2022-09-19T08:25:47.675084Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory.mysql -uroot -p'yyy=SB-3f/hf' -P 3306 -S /mysql/data/mysql3306/socket/mysql.sock
mysql> select version();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.8、修改用戶密碼
alter user root@'localhost' identified by 'Okdd3adacxaf##';9、配置root可遠程登陸
create user root@'%' identified by 'Okdd3adacxaf##';
grant all privileges on *.* to root@'%' with grant option;
flush privileges;使用如下語句創建 root 用戶是無法通過 navicat 等客戶端登錄的，由于從 MySQL8 開始，身份驗證插件發生改變，默認的 “caching_sha2_password” 不允許遠程登錄，故需將此插件修改為 “mysql_native_password” 便可登錄。
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| root | % | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Okdd3adacxaf##';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| root | % | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)10、設置socket軟連接
[mysql@node1 ~]$ ln -s /mysql/data/mysql3306/socket/mysql.sock /tmp/mysql.sock
[mysql@node1 ~]$ mysql -uroot -p'Okdd3adacxaf##'mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.33 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
三、設置開機自啟動
1、設置配置
[root@node1 system]# vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description = MySQL Server
Documentation = man:mysqld(8)
Documentation = http://dev.mysql.com/doc/refman/en/using-systemd.html
After = network.target
After = syslog.target
[Install]
WantedBy = multi-user.target
[Service]
User=mysql
Group=mysql
Type=notify
Timeout=0
ExecStart=/mysql/app/mysql8.0.33/bin/mysqld --defaults-file=/mysql/conf/my3306.cnf $MYSQLD_OPTS
EnvironmentFile=-/etc/sysconfig/mysql
LimitNOFILEE=65536
Restart=on-failure
RestartPreventExitStatus=1
Environment=MYSQLD_PARENT_PID=1
PrivateTmp=false2、執行命令讓配置生效
systemctl daemon-reload3、mysql服務相關命令
啟動
systemctl start mysqld關閉
systemctl stop mysqld查看
systemctl status mysqld4、設置MySQL為開機自啟動
[root@node1 ~]# systemctl enable mysqld
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.四、備份
1、邏輯備份
crontab -e
0 1 * * * /backup/backup.sh > /tmp/backup_$(date +"\%Y-\%m-\%d").log[root@mysql backup]# cat backup.sh
#!/bin/sh
# File: /data/backup/mysql_backup.sh
# Database info
DB_USER="root"
DB_PASS="Okdd3adacxaf##"
DB_HOST="192.168.11.11"
DB_PORT="3306"
BIN_DIR="/mysql/app/mysql8.0.33/bin"
BCK_DIR="/backup"
DATE=`date +%Y%m%d_%H`
#REMOTE_SERVER="remote_server_address"
#REMOTE_DIR="/db_backup/mysql"
# 創建備份目錄
#if [ ! -d $BACKUP_DIR ]; then
# mkdir -p $BACKUP_DIR
#fi
echo '開始備份。。。。。。。。。。。。。。'
$BIN_DIR/mysqldump -u$DB_USER -h$DB_HOST -p$DB_PASS -P$DB_PORT -F --flush-privileges --single-transaction --max_allowed_packet=256M --set-gtid-purged=OFF -B lowdata -S /data/mysql/mysql3306/tmp/mysql3306.sock > $BCK_DIR/mysqlbackup_data_$DATE.sql;
echo '結束備份。。。。。。。。。。。。。。'
#上傳備份文件到遠程服務器
#scp $BACKUP_DIR/$DB_NAME-$(date +%Y%m%d).tar.gz $REMOTE_SERVER:$REMOTE_DIR
#刪除過期文件
find /backup -mtime +7 -name "*.sql" -exec rm -f {} \;
find /backup -mtime +7 -name "mysqlbackup_data_$DATE.sql" -exec rm -f {} \;這篇文章雜揉了 強哥的MySQL數據庫安裝 + MySQL DBA精英實戰課 + MySQL實戰，寫出來的一篇文章，感覺可以當我的MySQL安裝傳家寶了
最后修改時間：2023-08-24 16:11:55
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
