操作系統環境：centos linux release 7.7.1908 (Core)
基礎環境配置
關閉SeLinux及防火墻
sed -i "s/SELINUX=enforcing/SELINUX=disabled/" /etc/selinux/config
setenforce 0
?
systemctl stop firewalld //停止系統默認的防火墻
systemctl mask firewalld //屏蔽服務(讓它不能啟動)
yum remove -y firewalld //卸載系統自帶的防火墻
安裝運維基礎工具
yum install -y chkconfig net-tools ntsysv mlocate lrzsz wget lsof setuptool
安裝yum源
yum install -y epel-release
wget https://dev.MySQL.com/get/mysql80-community-release-el7-2.noarch.rpm
yum localinstall -y mysql80-community-release-el7-2.noarch.rpm
MySQL8.0安裝
yum install -y bison-devel libaio-devel perl-Data-Dumper
yum install -y mysql-server
systemctl start mysqld //啟動MySQL
systemctl enable mysqld.service //開機自啟動
由于mysql的yum源是在國外，所以，在國內安裝很慢，因此，森哥的做法，是直接下載rpm包回來手工安裝 。如下操作：
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.19-1.el7.x86_64.rpm-bundle.tar
tar -xvf mysql-8.0.19-1.el7.x86_64.rpm-bundle.tar
yum localinstall -y mysql-community-* //使用localinstall會自己補足未安裝的所需要組件
systemctl start mysqld //啟動MySQL
systemctl enable mysqld.service //開機自啟動
修改初始密碼
grep 'temporary password' /var/log/mysqld.log //mysql5.7版本后，初始密碼不再為空，默認隨機生成，可通過該命令查詢
mysql -u root -p //進入mysql，輸入剛剛查到的密碼
alter user 'root'@'localhost' identified with mysql_native_password by '三種或以上的八位字符'; //修改密碼，并將密碼插件更改為mysql_native_password
初始my.cnf配置的內容為：
cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
?
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
?
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
?
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
原則上，上面的這個配置，就可以正常使用了。但有時候為了一些業務場合的應用，就需要考慮對其進行修改，以達到符合實現業務的需求。
mkdir /var/log/mysqld
touch /var/log/mysqld/error.log
chown mysql.mysql /var/log/mysqld -R
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
# server-id = 1 //單MySQL服務下，可以省略不用
datadir=/var/lib/mysql //數據文件所在位置
socket=/var/lib/mysql/mysql.sock //sock文件所在位置
log_error = /var/log/mysqld/error.log //數據庫錯誤日志文件
# skip_name_resolve = 1 //設置這個，說明只能通過IP連接，不用主機名，一般不建議使用
character-set-server=utf8mb4 //數據庫默認字符集，可以支持表情符號等，utf8不支持表情符號。另外utf8mb4是mysql8.0起支持的。
collation-server = utf8mb4_general_ci //數據庫字符集對應一些排序等規則，必須和character-set-server對應
transaction_isolation = READ-COMMITTED //事務隔離級別，默認為可重復讀
init_connect='SET NAMES utf8mb4' //設置client連接mysql時的字符集,防止亂碼
max_connections=500 //最大連接數
max_connect_errors = 1000 //最大錯誤連接數
explicit_defaults_for_timestamp = true //TIMESTAMP如果沒有顯示聲明NOT NULL，允許NULL值
max_allowed_packet = 1024M //SQL數據包發送的大小，如果有BLOB對象建議修改成1G
# interactive_timeout = 1800 //MySQL默認的wait_timeout 值為8個小時, interactive_timeout參數需要同時配置才能生效
# wait_timeout = 1800
tmp_table_size = 134217728 //內部內存臨時表的最大值，超過了這個值將寫入磁盤，系統IO壓力增大
max_heap_table_size = 134217728
# query_cache_size = 0 //禁用mysql的緩存查詢結果集功能，可根據業務需求是否開啟，通常為關閉
# query_cache_type = 0
# slow_query_log = 1 //慢查詢sql日志設置
# slow_query_log_file = /var/log/mysqld/slow.log
# long_query_time = 8 //慢查詢執行的秒數，必須達到此值可被記錄
# log_queries_not_using_indexes = 1 //檢查未使用到索引的sql
# log_throttle_queries_not_using_indexes = 5 //開啟后，記錄慢sql的頻次、每分鐘記錄的條數
# min_examined_row_limit = 100 // 檢索的行數必須達到此值才可被記為慢查詢
# skip-log-bin //關閉binlog功能
binlog_expire_logs_seconds=604800 //binlog日志文件保存的過期時間，過期后自動刪除
innodb_log_file_size=60M
innodb_buffer_pool_size=128M
[client]
default-character-set=utf8mb8
socket=/var/lib/mysql/mysql.sock
[mysqld_safe]
open-files-limit = 8192
log-error=/var/log/mysqld.log
socket=/var/lib/mysql/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid
密碼恢復
有時候，會將root密碼給忘記了，就需要對MySQL進行密碼恢復。
在/etc/my.cnf中，的[mysqld]內，加入如下內容：
skip-grant-table //改為安全模式，無法密碼登陸
重啟mysqld
systemctl restart mysqld
進入重置密碼
mysql -u root -p //空密碼，直接回車
use mysql;
update user set authentication_string='' where user='root'; //先清空密碼
注釋掉my.cnf中的skip-grant-table，再重啟mysqld。然后就可以免密碼進入，再修改密碼。
mysql -u root -p //不用輸入密碼，直接進入
alter user 'root'@'localhost' identified with mysql_native_password by '三種或以上的八位字符'; //修改密碼，并將密碼插件更改為mysql_native_password
現在就恢復正常使用了。
