MySQL 8.0.22 参考配置

一份适用于 8.0.22 的 MySQL 配置文件，注意看备注，根据实际情况调整，比如 innodb_buffer_pool_size 就要根据自己服务器的实际情况来调整

#
# documentation https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
#
[mysqld]
read-only                       = 0
lower_case_table_names          = 1
user                            = mysql
server_id                       = 1
port                            = 3331
basedir                        = /opt/yidun/mysql
datadir                        = /var/lib/mysql
socket                         = /var/lib/mysql/mysql.sock
log-error                      = /var/lib/mysql/mysqld.log
pid-file                       = /var/lib/mysql/mysqld.pid
default_authentication_plugin  = mysql_native_password
skip_name_resolve              = ON
enforce_gtid_consistency       = ON
gtid_mode                      = ON
log-bin                        = mysql-bin
binlog_format                  = ROW
expire-logs-days               = 7
max_connections                = 4096

# character set
character-set-client-handshake = FALSE
character-set-server           = utf8mb4
collation-server               = utf8mb4_unicode_ci
init_connect                   = 'SET NAMES utf8mb4'
sql_mode                       = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

# 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 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

参数确认

# 这里要确认 ONLY_FULL_GROUP_BY 已去掉
mysql> select @@sql_mode;
+----------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                         |
+----------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


# MySQL 数据库编码确认，这里主要确认 character_set_server 为 utf8mb4
mysql> show variables like '%char%';
+--------------------------+----------------------------------------------------+
| Variable_name            | Value                                              |
+--------------------------+----------------------------------------------------+
| character_set_client     | utf8mb4                                            |
| character_set_connection | utf8mb4                                            |
| character_set_database   | utf8mb4                                            |
| character_set_filesystem | binary                                             |
| character_set_results    | utf8mb4                                            |
| character_set_server     | utf8mb4                                            |
| character_set_system     | utf8                                               |
| character_sets_dir       | /opt/yidun/mysql-8.0.22-el7-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------+
8 rows in set (0.00 sec)

# 确认连接数，默认 151 是不够的，直接开到 4096
mysql> show variables like '%connection%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_connection | utf8mb4            |
| collation_connection     | utf8mb4_unicode_ci |
| max_connections          | 4096               |
| max_user_connections     | 0                  |
| mysqlx_max_connections   | 100                |
+--------------------------+--------------------+
5 rows in set (0.00 sec)

.my.cnf 参考

放在 /root/.my.cnf 文件中，使用比较方便，当然密码不太建议直接贴在配置文件里，不过既然已经是 root(600) 访问权限了，如果这个文件已经被看见了，那其实数据库也保不住了吧？

[client]
user=root
password=your-password
socket = /var/lib/mysql/mysql.sock
default-character-set=utf8mb4

[mysqladmin]
password=your-password
user=root
socket = /var/lib/mysql/mysql.sock