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

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

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';

2、[小结]如何修改配置？修改之后何时生效？如何确认修改后的值是否正确？修改配置：通过SHOW VARIABLES WHERE xxx 确认变量当前数值，确认修改变量是否是动态生效。修改MySQL配置文件并保存(一般是/etc/my.cnf)

何时生效？：动态变量即时生效，全局变量重启生效[对之前的会话和当前会话都不生效]，会话变量不确定(？待确认)

如何确认？：也可通过SHOW VARIABLES和SHOW STATUS加上WHERE等语句进行筛选(前者为配置变量，后者为当前的状态)，确认配置变量是否符合预期。(比如SHOW variables WHERE Variable_name = 'innodb_buffer_pool_size';)

3、MySQL内部的help

连接上MySQL命令行之后，可以方便的使用help查找不确定的命令。比如help create查看可以创建什么，help create database确认怎么创建新库的语法。

创建数据库表的方法也不难：CREATE TABLE test (

列名 数据类型 限制,

id INTEGER PRIMARY KEY,

);

4、其他小问题可以通过SELECT database();和STATUS;来确认，自己当前在哪个数据库。

innodb_flush_method = O_DIRECT这里是字母O不是数字0

小结

本文主要讲述了MySQL配置的基础知识，包括配置文件的位置、配置项的分段([mysqld])、作用域、还有配置变量的查询及当前状态变量确认。

而且对少量配置变量进行了解释。

更多配置项的解释都可通过官网确认。https://dev.mysql.com/doc/refman/5.7/en/