【MySQL】常见问题汇总
猎隼丶止戈 | 246 |
2023-02-18 配置调优
# Copyright (c) 2014, 2015, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Community Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
#
#
# 参考文章：
# 1. 【查阅】mysql配置文件/参数文件重要参数笔录（my.cnf）：https://www.cnblogs.com/gered/p/10979552.html
# 2. mysql的配置文件参数：https://www.cnblogs.com/shizhengquan/p/11249637.html
# 3. MYSQL常用的配置参数优化：https://www.cnblogs.com/FondWang/p/12582844.html
# 4. MySQL慢查询日志总结：https://www.cnblogs.com/kerrycode/p/5593204.HTML
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
default-character-set = utf8
[mysql]
default-character-set = utf8
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
federated
max_connections=5000
skip-host-cache
skip-name-resolve
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp
#skip-grant-tables
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
# 语言设置
# character-set-server：默认服务端字符集
# character-set-server：默认客户端链接字符集情况
# character-set-server：默认服务端排序规则
# skip-character-set-client-handshake：跳过mysql程序起动时的字符参数设置 ，使用服务器端字符集设置（不受client字符集影响，使用sever端字符集）
character-set-server = utf8
init-connect = 'set names utf8'
collation_server = utf8_general_ci
skip-character-set-client-handshake = 0
# log-error：错误日志路径
# long_query_time：慢查询时间，单位s，超过3秒则为慢查询。为0记录所有查询
log-error = /var/log/mysql/error.log
long_query_time = 3
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# max_connections：MySQL的最大连接数，如果服务器的并发连接请求量较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，MySQL回味每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。
# max_connect_errors：是一个MySQL中与安全有关的计数器值，他负责阻止过多尝试失败的客户端以防止暴力破解密码的情况，当超过指定次数，MySQL服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hotos命令清空此host的相关信息。（与性能并无太大的关系）
# back_log：MySQL能够暂存的连接数量，默认值是50。当主要MySQL线程在一个很短时间内得到非常多的连接请求，他就会起作用。如果MySQL的连接数据达到max_connections时，新的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈数量即back_log，如果等待连接的数量超过back_log，将不被接受连接资源。
# thread_cache_size：服务器线程缓存，这个值表示可以重新利用保存在缓存中的线程数量，当断开连接时，那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁（前提时缓存数未达上限）。
max_connections = 2000
max_connect_errors = 500
back_log = 1000
thread_cache_size = 64
# lower_case_table_names：
#
# 0：表名存储为给定的大小和比较是区分大小写的
# 1：表名存储在磁盘是小写的，但是比较的时候是不区分大小写
# 2：表名存储为给定的大小写但是比较的时候是小写的
#
# unix、linux 下 lower_case_table_names 默认值为 0 ，Windows 下默认值是 1 ，Mac OS X 下默认值是 2。
lower_case_table_names = 1
# innodb_buffer_pool_size：
# 对于innodb表来说，innodb_buffer_pool_size 的作用相当于 key_buffer_size 对于MyISAM表的作用一样。
# Innodb使用该参数指定大小的内存来缓冲数据和索引。最大可以把该值设置成物理内存的80%。
#
# innodb_thread_concurrency：此参数用来设置innodb线程的并发数，默认值为0表示不被限制，若要设置则与服务器的CPU核心数相同或是CPU的核心数的2倍。
#
# innodb_buffer_pool_size = 128M
innodb_buffer_pool_size = 2G
innodb_thread_concurrency = 0
# join_buffer_size：用于表示关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
#
# sort_buffer_size：
# 每个需要排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY 或 GROUP BY操作
# sort_buffer_size是一个connection级的参数，在每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存。
# sort_buffer_size：并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统的内存资源。例如：500个连接将会消耗500*sort_buffer_size(2M)=1G
#
# read_rnd_buffer_size：
# MySQL 的随机读（查询操作）缓冲区大小。当按任意顺序读取行时（例如，按照排序顺序），将分配到一个随机都缓冲区。进行排序查询时，MySQL会首先扫描一遍该缓冲区，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但是MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存消耗过大。
# 注：顺序读是根据索引的叶节点数据就能顺序的读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找侍其巷进行数据，而辅助索引和主键所在的数据端不同，因此访问方式是随机的。
#
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
join_buffer_size = 512M
sort_buffer_size = 16M
read_rnd_buffer_size = 16M
# 开启 bin-log 日志
# server-id：mysql 服务ID
# binlog_cache_size：为每个session分配的内存，在事务过程中用来存储二进制日志的缓存，提高记录bin-log的效率。
# max_binlog_cache_size：表示的是binlog能够使用的最大cache内存大小
# max_binlog_size：指定binlog日志文件的大小。不能将变量设置为大于1G或小于4096字节。默认值为1G.在导入大容量的sql文件时，建议关闭，sql_log_bin，否则硬盘扛不住，而且建议定期做删除。
# expire_logs_days：定义了mysql清除过期日志的时间
server-id = 1
log-bin = /var/lib/mysql/mysql-bin
binlog_format = row
binlog_cache_size = 2M
max_binlog_cache_size = 8M
max_binlog_size = 512m
expire_logs_days = 15
[mysqldump]
quick
# max_allowed_packet：根据配置文件限制server接受的数据包大小。
max_allowed_packet = 32M
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
错误解决
utf8mb4 不是编译字符集
异常信息
mysql: Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file
解决方案
场景：由于之前 mysql 中一起使用 utf8 编码，最近由于 emoji 表情的原因，需要使用 utf8mb4 编码
操作如下：
[client]
default-character-set=utf8mb4
[mysqld]
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation_server=utf8mb4_unicode_ci
[mysql]
default-character-set=utf8mb4
修改配置文件成功后，重启动数据库，出现异常。
解决方法，将 /usr/share/mysql/charsets/Index.xml 中的 utf8 编码修改为 utf8mb4 即可。
<!-- 将utf8，改为utf8mb4。这里是复制了一份才修改的！！！ -->
<!-- <charset name="utf8"> -->
<charset name="utf8mb4">
<family>Unicode</family>
<description>UTF-8 Unicode</description>
<alias>utf-8</alias>
<collation name="utf8_general_ci" id="33">
<flag>primary</flag>
<flag>compiled</flag>
</collation>
<collation name="utf8_bin" id="83">
<flag>binary</flag>
<flag>compiled</flag>
</collation>
</charset>
错误日志文件无权访问
异常信息
[ERROR] Could not open file '/var/log/mysql/error.log' for error logging: Permission denied
解决方案
问题分析
出现这个问题，主要是容器中启动 MySQL 服务的是 mysql 用户。而我们宿主机中是没有 mysql 这个用户存在的，所以产生了 Permission denied 这个错误类型。
解决问题
直接进入到 mysql 容器中，查看 MySQL 用户的相关信息
root@319ea9cea30b:/var/log# cat /etc/passwd
root:x:0:0:root:/root:/bin/bash
daemon:x:1:1:daemon:/usr/sbin:/usr/sbin/nologin
bin:x:2:2:bin:/bin:/usr/sbin/nologin
sys:x:3:3:sys:/dev:/usr/sbin/nologin
sync:x:4:65534:sync:/bin:/bin/sync
games:x:5:60:games:/usr/games:/usr/sbin/nologin
man:x:6:12:man:/var/cache/man:/usr/sbin/nologin
lp:x:7:7:lp:/var/spool/lpd:/usr/sbin/nologin
mail:x:8:8:mail:/var/mail:/usr/sbin/nologin
news:x:9:9:news:/var/spool/news:/usr/sbin/nologin
uucp:x:10:10:uucp:/var/spool/uucp:/usr/sbin/nologin
proxy:x:13:13:proxy:/bin:/usr/sbin/nologin
www-data:x:33:33:www-data:/var/www:/usr/sbin/nologin
backup:x:34:34:backup:/var/backups:/usr/sbin/nologin
list:x:38:38:Mailing List Manager:/var/list:/usr/sbin/nologin
irc:x:39:39:ircd:/var/run/ircd:/usr/sbin/nologin
gnats:x:41:41:Gnats Bug-Reporting System (admin):/var/lib/gnats:/usr/sbin/nologin
nobody:x:65534:65534:nobody:/nonexistent:/usr/sbin/nologin
_apt:x:100:65534::/nonexistent:/usr/sbin/nologin
mysql:x:999:999::/home/mysql:/bin/sh
root@319ea9cea30b:/var/log#
/etc/passwd 的文件格式：
用户名:口令:用户标识号:组标识号:注释性描述:主目录:登录Shell
所以，MySQL 容器启动 MySQL 服务是用的 mysql 用户。因此，我们可以在启动容器的时候，直接在容器中去运行一个创建 error.log 的命令即可。
成功案例
目录结构：
[root@iZwz93izbw8wks6an5uggxZ mysql]# ls -l
total 16
drwxr-xr-x 2 root root 4096 Aug 17 18:06 conf
drwxr-xr-x 2 polkitd root 4096 Aug 17 20:37 data
-rw-r--r-- 1 root root 464 Aug 17 20:31 docker-compose.yml
drwxr-xr-x 2 root root 4096 Aug 12 23:56 log
[root@iZwz93izbw8wks6an5uggxZ mysql]# tree
.
|-- conf
| `-- my.cnf
|-- data
|-- docker-compose.yml
`-- log
`-- error.log
3 directories, 3 files
[root@iZwz93izbw8wks6an5uggxZ mysql]#
其中 ，data 是一个空的目录， log 目录 下有一个 error.log 文件， conf 目录下有一个 my.cnf 文件。
my.cnf 配置文件内容：
[mysql]
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
symbolic-links=0
skip-host-cache
skip-name-resolve
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
docker-compose.yml 文件内容：
version: "3"
services:
mysql:
image: mysql:5.7
container_name: mysql
ports:
- 3306:3306
environment:
MYSQL_ROOT_PASSWORD: root
volumes:
- $PWD/conf:/etc/mysql
- $PWD/data:/var/lib/mysql
- $PWD/log/error.log:/var/log/mysql/error.log
command:
- "--default-authentication-plugin=mysql_native_password"
# entrypoint 是重点！！！
entrypoint: bash -c "chown -R mysql:mysql /var/log/mysql && exec /entrypoint.sh mysqld"
restart: always
说明：command 用来指定 mysql 容器启动后默认执行的命令 ，entrypoint 用来启动容器后需要执行的命令
启动容器
利用 docker ps 查看正在运行的容器
[root@iZwz93izbw8wks6an5uggxZ mysql]# ls
conf data docker-compose.yml log
[root@iZwz93izbw8wks6an5uggxZ mysql]# docker-compose up -d
[root@iZwz93izbw8wks6an5uggxZ mysql]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
87ada830457e mysql:5.7 "bash -c 'chown -R m…" About a minute ago Up About a minute 0.0.0.0:3306->3306/tcp, 33060/tcp mysql
[root@iZwz93izbw8wks6an5uggxZ mysql]#
超出行大小限制 65535 字节
异常信息
Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
解决方案
问题分析
这个错误提示是因为 MySQL 数据库中的表的行大小超出了最大限制。在 MySQL 数据库中，表的行大小限制是 65535 字节，这包括数据和元数据的大小。如果表中的行大小超过此限制，则会出现类似的错误提示。
解决问题
为了解决这个问题，您需要缩小表中行的大小。有几种方法可以做到这一点：
-
使用
TEXT 或BLOB 数据类型来存储大文本或二进制数据。如果您的表中包含大量的文本或二进制数据，可以将这些列的数据类型更改为
TEXT 或BLOB，这样它们就不会计入行大小限制。不过，需要注意的是，TEXT 和BLOB 数据类型是有限制的，不适合用于所有情况。 -
将表分解为多个表。
如果您的表包含大量的列，可以将表分解为多个表，每个表只包含部分列。这样，每个表中的行大小就会减小，不再超出限制。
-
删除不必要的列。
如果您的表包含许多不必要的列，可以删除这些列，从而减小行的大小。这样，表中的每行就只包含必要的数据，行大小也会变小。
-
将
VARCHAR 列的长度减小。如果您的表包含许多
VARCHAR 列，可以将这些列的长度减小，从而减小行的大小。这样，每个VARCHAR 列所占用的空间就会变小，行大小也会变小。
推荐指数：
真诚点赞 诚不我欺~