MySQL服务配置文件1、配置文件位置

MySQL服务端的进程mysqld在启动时,会默认按照以下顺序来读取mysql的配置文件:

  • /etc/my.cnf

  • /etc/mysql/my.cnf

  • 编译安装时通过编译选项指定的位置

如果不想让mysqld按照这个顺序读取配置文件,可以通过mysqld的 –defaults-file选项来指定一个自定义的配置文件路径,这样就会只读取指定的配置文件。

2、配置文件组成

mysql的配置文件由若干个块组成,每个块里面包含相关的配置。

[mysqld]

这个块包含MySQL服务器mysqld的配置选项


[mysqld_safe]

这个块用于mysqld_safe脚本的配置选项,mysqld_safe是一个用于安全启动mysqld的脚本。


[client]

这个块包含客户端程序的配置选项,包括mysql命令行工具、mysqldump等


[mysql]

这个块是针对mysql客户端工具的配置


例如:

[mysqld]key = value

说明:

  • 参数和值之间可以有若干个空格

  • 一般值不需要加引号,如果值包含了一些特殊字符或者空格,就需要加引号(单双都可以)


例如:

log_error = "/var/log/mysql/error.log"

3、服务端 基础配置项

  • user:指定运行mysqld进程的用户

  • port:指定mysqld进程的端口号

  • bind-address:指定端口绑定的ip地址,0.0.0.0表示所有地址

  • datadir:指定MySQL的数据目录的位置。这个目录用于存储数据库的实际数据文件,包括表、索引、日志等

  • basedir:指定MySQL应用程序的安装根目录,也就是MySQL的二进制程序、支持文件(如配置文件、脚本等)和库所在的目录。用于告诉MySQL在哪里查找它的二进制执行文件、库文件和其他支持文件

  • tmpdir:指定MySQL用于存储临时文件的目录,例如临时表等文件

  • socket:同一台机器上的客户端和服务器之间进行通信是通过socket(套接字)来进行的,这个选项是用于指定socket文件,客户端默认会通过这个套接字文件进行通信

  • pid-file: 指定MySQL服务器进程ID(PID)文件的位置。当MySQL服务器启动时,它会将自己的进程ID写入到这个文件中

  • log_error:指定错误的具体日志文件

  • character_set_server:指定mysql服务端使用的默认字符集,字符集决定了MySQL服务器如何存储字符串和解释字符数据。不同的字符集支持不同的语言和符号集。一般设置为 utf8mb4

  • collation-server:指定MySQL服务器的默认校对规则。校对规则决定了MySQL如何比较字符,一般设置为utf8mb4_general_ci,表示是一个不区分大小写的校对规则


说明:

[mysqld]配置块中指定的配置项都是mysqld进程的配置项,可以通过 mysqld --verbose --help来查看相关配置项以及默认的值。

如果将这些参数在进程启动的时候在一一指定,这样管理起来就很麻烦。

例如:

mysqld --port=3306 --pid-file=/var/run/mysqld/mysqld.pid  --datadir=/var/lib/mysql

将这些配置都放在配置文件中的[mysqld]配置块中,方便对这些参数进行统一管理和简化启动命令。(在配置文件中,忽略选项的 — 前缀,直接写选项名称)


例如:

[mysqld]user            = mysqlpid-file        = /var/run/mysqld/mysqld.pidsocket          = /var/run/mysqld/mysqld.sockport            = 3306bind-address    = 0.0.0.0basedir         = /usrdatadir         = /var/lib/mysqltmpdir          = /tmplog_error = /var/log/mysql/error.log# 默认字符集为utf8mb4,这是一个支持Unicode的字符集,能够存储任何Unicode字符。character_set_server=utf8mb4collation-server      = utf8mb4_unicode_ci

utf8、utf8mb4、utf8mb3区别:

utf8mb4 和 utf8mb3是MySQL中的两种字符集编码,可以理解为它们都是utf8的变种,utf8mb3相当于utf8换了个名字,utf8mb4支持的Unicode字符范围比utf8更大。

  • utf8mb4:mb4″ 代表 “most bytes 4″,可以支持最多四个字节的 Unicode 字符,从MySQL 5.5.3开始支持utf8mb4

  • utf8mb3:早期版本的 MySQL 中,utf8 实际上是 utf8mb3 的别名,它最多支持三个字节的字符,所以它不能表示所有的 Unicode 字符,特别是一些较新的表情符号和一些特殊的语言字符

4、服务端 调优配置

  • innodb_buffer_pool_size:InnoDB性能优化设置,用于指定InnoDB缓冲池的大小,缓冲池用于缓存数据和索引。一般设置为内存的50%作用

  • max_connections:指定了可以同时打开的最大连接数

  • table_open_cache:用于指定可以同时打开的表的数量

  • thread_cache_size:指定线程缓存的大小,可以减少为新连接创建和销毁线程的开销。

  • max_allowed_packet::这个选项指定了MySQL服务器和客户端之间传输的最大数据包大小。

  • sort_buffer_size:用于指定为排序操作分配的内存缓冲区的大小,用于存储排序查询(如带有 ORDER BY 子句的查询)的中间结果。

  • default_authentication_plugin:( MySQL 8.0 引入的选项)用于指定默认使用的身份验证插件,因为mysql引入了几种新的身份验证插件,但是某些客户端和应用可能还不完全支持新的身份验证方法,所以为了确保兼容性,就需要使用这个选项来修改默认身份验证插件。

  • wait_timeout:设置自动断开连接的时间,如果一个连接超过这个时间没有和mysql服务交互,就会断开连接。一般设置为几个小时或者一天

例如:

[mysqld]  # 基础 配置user            = mysqlpid-file        = /var/run/mysqld/mysqld.pidsocket          = /var/run/mysqld/mysqld.sockport            = 3306bind-address    = 0.0.0.0basedir         = /usrdatadir         = /var/lib/mysqltmpdir          = /tmplog_error = /var/log/mysql/error.logcharacter_set_server=utf8mb4collation-server      = utf8mb4_unicode_ci  # 调优 配置wait_timeout             = 86400innodb_buffer_pool_size  = 4Gmax_connections          = 2000thread_cache_size        = 16max_allowed_packet       = 64Msort_buffer_size         = 200Mdefault_authentication_plugin = mysql_native_password

mysqld的身份验证插件:

早期版本的 MySQL 默认使用的是mysql_native_password身份验证插件。它的主要特点是广泛的兼容性,但相对于新的身份验证方法,它的安全性较低。

在 MySQL 8.0 中,引入了caching_sha2_password作为默认的身份验证插件,提供了更强的安全性。caching_sha2_password使用 SHA-256 加密算法,比mysql_native_password提供更高级的密码加密。

由于一些老旧的客户端和应用程序可能不支持caching_sha2_password插件,MySQL 提供了default_authentication_plugin配置选项,允许数据库管理员指定默认的身份验证插件。

5、客户端 配置

客户端的配置就比服务端配置简单多了,客户端主要关注的是如何连接到MySQL服务器,以及一些基本的客户端行为设置。

  • socket:如果通过UNIX套接字连接,需要指定套接字文件的路径。

  • skip-auto-rehash:禁用自动补全功能。在有大量数据库和表时,这可以加快启动速度。

  • default-character-set:用于指定客户端使用的默认字符集。


例如:

[client]socket=/var/run/mysqld/mysqld.sockdefault-character-set=utf8mb4skip-auto-rehash

客户端和服务端字符集说明:

客户端和服务端的字符集要保持一直,如果不一样可能会导致以下问题:

  • 客户端显示问题:客户端可能无法正确显示从服务器检索的文本数据。这尤其在处理非英文字符时常见。

  • 数据插入问题:向数据库中插入数据时,如果客户端的字符集与数据库或表的字符集不一致,可能会导致插入的数据出现乱码或错误。

6、mysqld_safe 配置

在mysql的配置文件中,[mysqld_safe]配置块是给 mysqld_safe 这个脚本使用的,现在基本上不使用这个配置块了。

(1)mysqld_safe 是一个在早期版本的 MySQL 中用于启动 MySQL 服务器的脚本,提供了额外的安全特性和错误恢复功能。随着 MySQL 的发展,尤其是从 5.7 版本开始,许多这些功能已被集成到 mysqld 本身。

(2)在现代 Linux 系统中,systemd 作为系统服务管理器,提供了对服务的监控、日志管理、自动重启等功能。这些功能的存在确实减少了 mysqld_safe 的必要性。


如果要使用mysql_safe脚本,一般常用的配置项有两个:

  • socket:指定套接字文件位置

  • nice:设置 MySQL 服务器进程的 “nice” 值,一般设置为0,表示正常优先级


如果是使用systemd的service文件,可以这样指定:

[Unit]Description=MySQL Server 5.6.51After=network.target[Service]Type=forkingUser=mysqlGroup=mysqlNice=0  # 指定Nice优先级,这个可以忽略,因为默认Nice就是0PIDFile=/var/run/mysqld/mysqld.pidExecStart=/etc/init.d/mysqld startExecStop=/etc/init.d/mysqld stopExecReload=/etc/init.d/mysqld reloadRemainAfterExit=yes[Install]WantedBy=multi-user.target

MySQL服务单元文件

从2010年起,systemd首先被Fedora 15采用以替换传统的System V init进程。随后许多其他Linux发行版也跟进,例如Ubuntu从其15.04版本起采用systemd,而Red Hat系列则从7版本开始采用。

systemd为服务管理引入了一系列高效的功能特性,大大简化了传统init系统中可能较为复杂和繁琐的管理流程。

例如,在systemd的管理下,管理MySQL服务时可以避免使用传统的mysqld_safe脚本。mysqld_safe是一个专门用于启动和监控MySQL服务器的shell脚本。但在systemd体系中,服务的启动、监控和日志记录等功能都可以通过systemd的配置文件和命令来直接实现,这不仅简化了服务管理,也提高了系统的可维护性和可靠性。

包管理器安装

如果操作系统的初始化进程是systemd进程,通过包管理器安装的mysql服务,会自动配置好service文件的。

二进制安装

二进制安装的话就没有提供支持systemd的service文件,需要自己手动编写。有两种方式:


方式一:二进制软件包里面有一个名为mysql.service的脚本文件,可以直接通过编写systemd的service文件来调用这个脚本,从而实现对mysql服务的管理。


例如:

[Unit]Description=MySQL Server 5.6.51After=network.target[Service]Type=forkingUser=mysqlGroup=mysqlNice=0PIDFile=/var/run/mysqld/mysqld.pidExecStart=/etc/init.d/mysqld startExecStop=/etc/init.d/mysqld stopExecReload=/etc/init.d/mysqld reloadRemainAfterExit=yes[Install]WantedBy=multi-user.target

方式二:自己手动编写service文件


例如:

[Unit]Description=MySQL Community ServerAfter=network.target[Install]WantedBy=multi-user.target[Service]Type=notifyUser=mysqlGroup=mysqlPIDFile=/var/run/mysqld/mysqld.pidPermissionsStartOnly=trueExecStart=/opt/mysql-8.0/bin/mysqldTimeoutSec=infinityRestart=on-failureRuntimeDirectory=mysqldRuntimeDirectoryMode=755LimitNOFILE=10000# Set enviroment variable MYSQLD_PARENT_PID. This is required for restart.Environment=MYSQLD_PARENT_PID=1

编译安装

编译安装的话,如果是Mysql5.7及其以上的版本,编译时通过加 -DWITH_SYSTEMD=1 选项,可以在安装目录下的lib/systemd/system目录下生成service文件,将这个文件拷贝到对应位置就可以实现对mysql服务的管理了。


例如:

# Copyright (c) 2015, 2023, Oracle and/or its affiliates.## This program is free software; you can redistribute it and/or modify# it under the terms of the GNU General Public License, version 2.0,# as published by the Free Software Foundation.## This program is also distributed with certain software (including# but not limited to OpenSSL) that is licensed under separate terms,# as designated in a particular file or component or in included license# documentation.  The authors of MySQL hereby grant you an additional# permission to link the program and your derivative works with the# separately licensed software that they have included with MySQL.## 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, version 2.0, 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## systemd service file for MySQL forking server#[Unit]Description=MySQL ServerDocumentation=man:mysqld(8)Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.htmlAfter=network-online.targetWants=network-online.targetAfter=syslog.target[Install]WantedBy=multi-user.target[Service]User=mysqlGroup=mysqlType=notify# Disable service start and stop timeout logic of systemd for mysqld service.TimeoutSec=0# Execute pre and post scripts as root# hence, + prefix is used# Needed to create system tablesExecStartPre=+/usr/local/mysql/bin/mysqld_pre_systemd# Start main serviceExecStart=/usr/local/mysql/bin/mysqld $MYSQLD_OPTS# Use this to switch malloc implementationEnvironmentFile=-/etc/sysconfig/mysql# Sets open_files_limitLimitNOFILE = 10000Restart=on-failureRestartPreventExitStatus=1# Set enviroment variable MYSQLD_PARENT_PID. This is required for restart.Environment=MYSQLD_PARENT_PID=1PrivateTmp=false