当前位置：首页 > 文章列表 > 数据库 > MySQL > MySQL 配置基础

MySQL 配置基础

来源：SegmentFault 2023-01-16 09:54:39 0浏览收藏

大家好，今天本人给大家带来文章《MySQL 配置基础》，文中内容主要涉及到MySQL、数据库，如果你对数据库方面的知识点感兴趣，那就请各位朋友继续看下去吧~希望能真正帮到你们，谢谢！

MySQL作为一种常见的数据库管理系统(DBMS)，其自身的各种配置项极大的影响了其性能。所以有必要进行了解和学习。

配置学习资源途径

我最近在看《高性能Mysql》，其中第8章讲解了关于配置的很多事项，都值得了解和学习。当然，官网也是最新信息查阅的重要渠道。

官网【可在5.1 TheMySQL Server找到配置解释。可在5.4找到BinLog相关内容。】

MySQL配置

配置文件位置：

一般来说，MySQL服务端配置文件的默认位置是：

/etc/my.cnf

或者

/etc/mysql/my.cnf

【也可以通过

mysqld --verbose --help|grep -A 1 'Default option'

确认配置文件的位置】

和nginx类似，也可以选择分目录进行include，便于管理整体配置结构。（比如
!includedir /etc/my.cnf.d
然后在
my.conf.d
目录里对不同的配置项分文件。

配置修改基础：

1、mysql配置文件是分段的，要注意配置项放在了正确的段里（比如服务器主要用 [mysqld] 这一段

1、副作用：一些配置项会产生副作用，临时修改配置需要非常小心。（比如变更
query\_cache\_size
会立即删除所有查询的缓存，重新构建。
2、变量的值：变量不是越大越好，可能会导致内存交换或者超出地址空间。需要跟进情况设定。

2、配置项有不同的作用域，有的是全局，有的是会话。其次，还有动态变量，可在运行时修改。【也就是说，大部分非动态变量需要重启才能生效】
3、全局变量：修改后对当前会话及已存在的会话均不生效。可通过

SHOW GLOBAL VARIABLES

确认。
4、变量的单位：配置时要注意单位，命令行或者配置文件，可以使用后缀指定单位（比如1M等，但要注意，使用SQL的SET指令时就不能使用单位。
5、配置文件管理：最好使用git来进行版本管理，加上充足的注释。可以避免不少问题。

如何创建一个靠谱的MySQL配置？

1、一个好的配置，不是从学习配置项开始，也不是询问怎么设置或者怎么修改，更不是观察服务器行为和询问哪个配置可以提升性能。
【应该是从理解MySQL内核和行为开始】
2、保证基础配置都正确（比如日志路径，缓存配置，端口号，数据库存储位置等。如非必要，尽量使用默认配置。（默认配置经受过的测试是最多的。
3、优先进行语句优化等其他优化，最后考虑修改配置项。

一个最小配置示例

一般来说，选择尽量少的配置（或者说最小配置），如无必要，不用声明（不声明使用默认值）。

当然一些非常重要的配置项，即使使用默认配置，也最好声明出来。（比如default_storage_engine)

[mysqld]
#GENERAL
datadir                 = /var/lib/mysql
socket                  = /var/lib/mysql/mysql.sock
pid-file                = /var/lib/mysql/mysqld.pid
user                    = mysql
port                    = 3306
default_storage_engine  = InnoDB
skip_name_resolve #关闭域名解析，避免dns导致的请求失败(关闭后要使用IP访问mysql)
#mysql 会做正向和反向dns查询。一旦失败就会拒绝连接。

#InnoDB
innodb_buffer_pool_size = 256M #核心配置，需要根据实际情况调整
innodb_log_file_size    = 50M #需要根据实际情况调整
innodb_file_per_table   = 1 #需要根据实际情况调整
innodb_flush_method     = O_DIRECT #这里是字母O，不是数字0
#LOGGING
log-error               = /var/log/mysql-error.log
slow_query_log          = /var/log/mysql-slow.log
#OTHER
tmp_table_size          = 32M #需要根据实际情况调整
max_heap_table_size     = 32M #需要根据实际情况调整
#query_cache_type       = 0 #mysql 8.0有变动，参数名不是这个
#query_cache_size       = 0 #mysql 8.0有变动，参数名不是这个
max_connections         = 100 #需要根据实际情况调整
thread_cache_size       = 50 #根据其余参数调整线程缓存
table_open_cache        = 10 #根据 opened_tables 调整
open_files_limit        = 65535 #尽量调大，配合内核参数
[client]
socket                  = /var/lib/mysql/mysql.sock
port                    = 3306

配置文件解释：
1、Innodb的配置项：

innodb\_buffer\_pool\_size 【最核心配置，innodb严重依赖缓冲池。
索引、行数据缓存、哈希索引，插入缓存、锁等。必须为innodb配置足够的缓冲池。一般为内存的75%-80%，建议配置的稍低，根据情况增加比例。[ 《高性能MySQL》P343
Innodb任意时刻可以保持打开
```
.idb
```
文件的数量是有限的，由如下两个参数控制：
```
innodb_file_per_table
```
：When innodb_file_per_table is enabled, tables are created in file-per-table tablespaces by default.
```
innodb_open_files
```
：It specifies the maximum number of .ibd files that MySQL can keep open at one time.
也可参考官网：https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

2、MySQL配置项：

线程缓存(
```
thread_cache_size
```
)：
[参数]
```
thread_cache_size
```
：可以保持在缓存中的线程数。
当关闭一个连接时，会将这个连接的线程放入缓存（如果缓存有空间）。下次可以直接读缓存，而非重新建立连接。
[状态]
```
thread_cached
```
：当前已经缓存了的线程数。
[状态]
```
threads_created
```
：每秒创建的新线程数，（如果每秒要创建很多线程，那需要考虑增加缓存大小
[状态]
```
threads_connectd
```
：当前正在连接中的线程数。
线程缓存的配置和上述3个状态高度相关。根据实际情况判断缓存应该配置多少。
PS：灵活利用
```
SHOW STATUS
```
和
```
SHOW VARIABLES
```
查看配置的值，查看当前状态。
表缓存：
[参数]
```
table_open_cache
```
：如果[状态]
```
opened_tables
```
很大，或者一直在增长。考虑增加这个参数。
[参数]
```
table_definition_cache
```
：通常都可以配置的比较高，最好可以缓存下所有表的定义。【除非有上万张表】

配置修改后的生效与确认

了解了基础配置，修改了配置文件。还需要关注配置的生效问题。
有的动态配置修改之后就马上生效。有的全局配置需要重启才能生效。

systemctl restart mysqld

重启后，还需要获取变量值进行确认,是否配置变量符合预期。

如何确认MySQL的配置/状态？

1、

SHOW GLOBAL STATUS

及

SHOW STATUS

查看数据库当前的状态参数。（确认当前运行情况）
2、

SHOW VARIABLES

查看数据库的配置参数。（也可直接进行过滤搜索，确认默认配置值）
【PS：1和2都可通过WHERE等语句进行筛选过滤。比如：

show variables where variable_name like 'inno%pool%size%';

【或者说SHOW语句本质也是在查数据库。只不过是描述数据库本身的数据

3、[shell]

mysqladmin extended-status -ri60

每60秒查看状态变量的增值
具体可参考:https://blog.csdn.net/demonson/article/details/80103204
4、

STATUS

查看MySQL基础信息，包括版本，字符集，socket，线程数等。

自己在学习过程中，遇到的其他问题

0、yum安装mysql的步骤
首先是获取rpm包，通过如下两个连接均可。文件名类似这样

mysql57-community-release-el7-3.noarch.rpm

。
https://dev.mysql.com/downloads/repo/yum/
https://repo.mysql.com/
随后

rpm -ivh xxx.rpm

导入包，

yum makecache

生成缓存。最后

yum install -y mysql-community-{client,libs,common,server}.x86_64

即可完成安装。（一般来说，这4个组件就足够了。）

1、yum安装mysql后，初次登录和密码问题
首先确认

/etc/my.cnf

配置，确认err-log的配置。（一般默认是

/var/log/mysqld.log

）然后查看错误日志，即可发现临时密码。再使用

sudo mysql -uroot -p'tmp_passwd'

即可登录。
参考：https://www.cnblogs.com/array...
其次，临时密码不可用于长期登录，必须修改后才可进行更多操作。如果尝试使用临时密码登陆后操作，会出现如下提示：

You must reset your password using ALTER USER statement before executing this statement.

可以根据mysql版本采取对应的语句进行密码修改。

5.7.6之前：
```
SET PASSWORD = PASSWORD('Newpasswd');
```

5.7.6之后：

ALTER USER USER() IDENTIFIED BY 'Newpasswd';

参考:https://blog.csdn.net/muziljx/article/details/81541896

2、[小结]如何修改配置？修改之后何时生效？如何确认修改后的值是否正确？

修改配置：通过
```
SHOW VARIABLES WHERE xxx
```
确认变量当前数值，确认修改变量是否是动态生效。修改MySQL配置文件并保存（一般是
```
/etc/my.cnf
```
）
何时生效？：动态变量即时生效，全局变量重启生效[对之前的会话和当前会话都不生效]，会话变量不确定（？待确认）
如何确认？：也可通过
```
SHOW VARIABLES
```
和
```
SHOW STATUS
```
加上
```
WHERE
```
等语句进行筛选(前者为配置变量，后者为当前的状态)，确认配置变量是否符合预期。（比如
```
SHOW variables WHERE Variable_name = 'innodb_buffer_pool_size';
```
）

参考：https://blog.csdn.net/demonson/article/details/80103204

3、MySQL内部的help
连接上MySQL命令行之后，可以方便的使用help查找不确定的命令。比如

help create

查看可以创建什么，

help create database

确认怎么创建新库的语法。
创建数据库表的方法也不难：

CREATE TABLE test (
列名  数据类型  限制,
id INTEGER PRIMARY KEY,
);

4、其他小问题

可以通过
```
SELECT database();
```
和
```
STATUS;
```
来确认，自己当前在哪个数据库。
```
SCHEMA
```
和
```
DATBASE
```
在MySQL里是同义词。见https://stackoverflow.com/questions/1219711/mysql-create-schema-and-create-database-is-there-any-difference
```
innodb_flush_method = O_DIRECT
```
这里是字母
```
O
```
不是数字
```
0
```