Mysql 配置的工作原理

可能有时候我们会问，“我的服务器有50 GB内存，12核CPU，怎样配置最好？” 很遗憾，问题没这么简单，MySQL 服务器的配置应该符合它的工作负载，数据，以及应用需求，并不仅仅看硬件的情况。通常只需要把基本的项配置正确，应该将更多的时间花费在 schema 的优化，索引，以及查询设计上。

一、查找配置文件

为 Mysql 服务器创建一个靠谱的配置文件过程。
MySQL是从命令行参数和配置文件获得配置信息。
在类 UNIX 系统中，配置文件的位置一般在 /etc/my.conf 或者 /etc/mysql/my.conf 中。

一定要清楚的知道服务器配置文件的位置！有时候我们尝试修改配置文件但是不生效，因为修改的并不是服务器读取的文件。不过我们可以用下面的命令来找出正在运行的mysql的配置文件的位置。

➜  ~ which mysqld
/usr/local/bin/mysqld
➜  ~ /usr/local/bin/mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
➜  ~

二、语法、作用域和动态性

1.语法

配置项设置都使用小写，单词之间用下划线或横线隔开。下面的例子是等价的，并且可能在命令行和配置文件中都看到这两种格式：

/usr/local/bin/mysqld --auto-increment-offset=5
/usr/local/bin/mysqld --auto_increment_offset=5

在这里我们建议使用一种固定的风格。

2.配置项作用域

配置项可以有多个作用域。有些设置是服务器级的（全局作用域），有些对每个连接是不同的（会话作用域），剩下的一些是对象级的。

许多会话级变量跟全局变量相等，可以认为是默认值。如果改变会话级变量，它只影响改动的当前连接，当连接关闭时所有的参数变更都会失效。

举例：

• query_cache_size 变量是全局的
• sort_buffer_size 变量默认是全局相同的，但每个线程里也可以设置

另外，除了在配置文件中设置变量，有很多变量（但不是所有）也可以在服务器运行时修改。MySQL 把这些归为动态配置变量。

SET sort_buffer_size = 2000;

如果动态的设置变量，要注意 MySQL 关闭时可能丢失这些设置，如果想保持这些设置，还是需要修改配置文件。

如果在服务器运行时修改了变量的全局值，这个值对当前会话和其他任何已经存在的会话都不起效果，这是因为会话的变量值是在连续创建时从全局值初始化来的。在每次变更后，应该检查 SHOW_GLOBAL_VARIABLES 的输出,确认已经按照期望变更了。

3.设置变量的副作用

动态设置变量可能导致意外的副作用，例如从缓冲中刷新脏块。务必小心那些可以在线更改的设置，因为它们可能导致数据库做大量的工作。

常用的变量：

key_buffer_size      键缓冲区 
table_cache_size     表可以被缓存的数量
thread_cache_size    线程缓存
query_cache_size     查询缓存
read_buffer_size     
sort_buffer_size      排序操作缓存分配内存

三、创建MySQL配置文件

正如我们前面提到的，没有一个适合所有场景的“最佳配置文件”，比如，对一台有18GB 内存和 12 块硬盘的 4 路CPU服务器，不会有一个相应的“最佳配置文件”。应该开发自己的配置，因为即使是一个好的起点，也依赖于具体是如何使用服务器的。

实际上MySQL 的可配置性太强也可以说是一个弱点，看起来好像需要花费很多的时间在配置上，其实大多数配置的默认值已经是最佳配置了，所以，不要改动太多配置。
下面就是这个基础配置文件：

[mysqld]
#GENERAl
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock 
pid_file=/var/lib/mysql/mysql.pid
user=mysql
port = 3306 
default_storage_engine=InnoDB
#INNODB
innodb_buffer_pool_size=<value>
innodb_log_file_size=<value>
innodb_file_per_table=1
innodb_flush_method=0_DIRECT
#LOGGING
log_error=/var/lib/mysql/mysql-error.log
slow_query_log=/var/lib/mysql/mysql-slow.log
#OTHER
tmp_table_size=32M
max_heap_table_size=32M
max_connections=<value>
open_files_limit=65535
socket=/var/lib/mysql/mysql.sock

# Replication Master Server (default) 
# binary logging is required for replication 
log-bin=mysql-bin

# binary logging format - mixed recommended 
binlog_format=mixed

四、配置内存使用

配置MySQL正确使用内存量对高性能事关重要。MySQL内存消耗可以分为两类：
可以控制的内存和不可控制的内存。无法控制MySQL服务器运行、解析查询，以及其内部管理所消耗的内存，但是为特定目的而使用多少内存则有很多参数可以控制。

可以按下面的步骤来配置内存：

1. 确定可以使用的内存上限。
2. 确定每个连接MySQL需要使用多少内存，例如排序缓冲和临时表。
3. 确定操作系统需要多少内存才够用。包括同一台机器上其他程序使用的内存，如定时任务。
4. 把剩下的内存全部给 MySQL 缓存，例如 INNODB 的缓存池，这样做很有意义。