返回首页
当前位置: 主页 > 网络编程 > Php实例教程 >

MySQL配置的“修改条令”

时间:2017-12-04 21:50来源:知行网www.zhixing123.cn 编辑:麦田守望者

如果让你在某种环境上安装配置MySQL,你会怎么做?安装后,直接copy修改示例配置文件,应该是大多数人的做法,但强烈建议不要怎么做。首先,示例配置文件有非常多注释掉的配置项,它可能会诱使你打开一个你并不了解的配置,这些注释还不一定准确。其次,MySQL的一些配置对于现代化的硬件和工作负载来说,有点过时了。

MySQL有非常多的配置项可以修改,但大多数情况下,你都不应该随便修改它,因为错误或者没用的配置导致的潜在风险非常大,而且还很难定位问题。确保基本配置正确,然后小心诊断问题,确认问题恰好可以通过某个配置项解决,紧接着再修改这个配置吧。

其实,创建一个好的配置,最快方法不是从学习配置项开始,也不是问哪个配置项应该怎么设置或者怎么修改开始,更不是从检查服务器行为和询问哪个配置项可以提升性能开始。最好是从理解MySQL内核和行为开始,然后利用这些知识来指导你配置MySQL。

那下面就从理解MySQL配置的工作原理开始吧。

MySQL配置的工作原理

MySQL从哪儿获得配置信息:命令行参数和配置文件。类Unix系统中,配置文件一般位于 /etc/my.cnf 或者 /etc/mysql/my.cnf。在启动时,可以通过命令行参数指定配置文件的位置,当然命令行中也可以指定其它参数,服务器会读取配置文件的内容,删除所有注释和换行,然后和命令行选项一起处理。

任何打算长期使用的配置项都应该写入配置文件,而不是在命令行中指定。一定要清楚的知道MySQL使用的配置文件位置,在修改时不能想当然,比如,修改了/etc/my.cnf的配置项,但MySQL实际并未使用这个配置文件。如果你不知道当前使用的配置文件路径,可以尝试:

root@msc3:~# which mysqld

/usr/sbin/mysqld

root@msc3:~# /usr/sbin/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 ~/.my.cnf

通常读取跟它同名的分段部分,比如许多客户端程序读取client部分。服务器通常读取mysqld这一段,一定要确认配置项放在了文件正确的分段中,否则配置是不会生效的。

MySQL每一个配置项均使用小写,单词之间用下划线或者横线隔开,虽然我们常用的分隔符是下划线,但如果在命令行或者配置文件中见到如下配置,你要知道,它们其实是等价的:

# 配置文件

max_connections=5000

max-connections=5000

# 命令行

/usr/sbin/mysqld –max_connections=5000

/usr/sbin/mysqld –max-connections=5000

配置项可以有多个作用域:全局作用域、会话作用域(每个连接作用不同)、对象作用域。很多会话级配置项跟全局配置相等,可以认为是默认值,如果改变会话级配置项,它只影响改动的当前连接,当连接关闭时,所有的参数变更都会失效。下面有几个示例配置项:

  • query-cache-size 全局配置项
  • sort-buffer-size 默认全局相同,但每个线程里也可以设置
  • join-buffer-size 默认全局,且每个线程也可以设置。但若一个查询中关联多张表,可以为每个关联分配一个关联缓存(join-buffer),所以一个查询可能有多个关联缓冲。

配置文件中的变量(配置项)有很多(但不是所有)可以在服务器运行时修改,MySQL把这些归为动态配置变量:

# 设置全局变量,GLOBAL和@@global作用是一样的

set   GLOBAL   sort-buffer-size  = <value>

set   @@global.sort-buffer-size := <value>

# 设置会话级变量,下面6种方式作用是一样的

# 即:没有修饰符、SESSION、LOCAL等修饰符作用是一致的

set  SESSION   sort-buffer-size  = <value>

set  @@session.sort-buffer-size := <value>

set          @@sort-buffer-size  = <value>

set  LOCAL     sort-buffer-size  = <value>

set     @@ocal.sort-buffer-size := <value>

set            sort-buffer-size  = <value>

# set命令可以同时设置多个变量,但其中只要有一个变量设置失败,所有的变量都未生效

SET GLOBAL sort-buffer-size = 100, SESSION sort-buffer-size = 1000;

SET GLOBAL max-connections = 1000, sort-buffer-size = 1000000;

动态的设置变量,MySQL关闭时这些变量都会失效。如果在服务器运行时修改了变量的全局值,这个值对当前会话和其他任何已经存在的会话都不起效果,这是因为会话的变量值是在连接创建时从全局值初始化而来的。注意,在配置修改后,需要确认是否修改成功。

你可能注意到,上面的示例中,有些使用“=”,有些使用“:=”。对于set命令本身来说,两种赋值运算符没有任何区别,在命令行中使用任一运算符符,均可以生效。而在其他语句中,赋值运算符必须是“:=”,因为在非set语句中“=”被视为比较运算符。具体可以参考如下示例:stackoverflow

https://stackoverflow.com/questions/3888735/mysql-set-user-variable-from-result-of-query

// @exp 表示用户变量,上面的示例均是系统变量

// 错误

set @user = 123456;

set @group = select GROUP from USER where User = @user;

select * from USER where GROUP = @group;

// 正确

SET @user := 123456;

SELECT @group := `group` FROM user WHERE user = @user;

SELECT * FROM user WHERE `group` = @group;

有一些配置使用了不同的单位,比如table-cache变量指定表可以被缓存的数量,而不是表可以被缓存的字节数。而key-buffer-size则是以字节为单位。

还有一些配置可以指定后缀单位,比如1M=1024*1024字节,但需要注意的是,这只能在配置文件或者作为命令行参数时有效。当使用SQL的SET命令时,必须使用数字值1048576或者1024*1024这样的表达式,但在配置文件中不能使用表达式。

小心翼翼地配置MySQL

我们们常常动态地修改配置,但请务必小心,因为它们可能导致数据库做大量耗时的工作,从而影响数据库的整体性能。比如从缓存中刷新脏块,不同的刷新方式对I/O的影响差别很大(后文会具体说明)。最好把一些好的习惯作为规范合并到工作流程中去,就比如:

习惯1:不要通配置的名称来一个变量的作用

因为它可能跟你想象的完全不一样。比如:

  • read-buffer-size:当MySQL需要顺序读取数据时,如无法使用索引,其将进行全表扫描或者全索引扫描。这时,MySQL按照数据的存储顺序依次读取数据块,每次读取的数据块首先会暂存在缓存中,当缓存空间被写满或者全部数据读取结束后,再将缓存中的数据返回给上层调用者,以提高效率。
  • read-rnd-buffer-size:和顺序读取相对应,当MySQL进行非顺序读取(随机读取)数据块的时候,会利用这个缓冲区暂存读取的数据。比如:根据索引信息读取表数据、根据排序后的结果集与表进行Join等。总的来说,就是当数据块的读取需要满足一定的顺序的情况下,MySQL 就需要产生随机读取,进而使用到read-rnd-buffer-size参数所设置的内存缓冲区。

这两个配置都是在扫描MyISAM表时有效,且MySQL会为每个线程分配内存。对于前者,MySQL只会在查询需要使用时才会为该缓存分配内存,并且一次性分配该参数指定大小的全部内存,而后者同样是需要时才分配内存,但只分配需要的内存大小而不是参数指定的数值,max-read-rnd-buffer-size(实际上没有这个配置项)这个名字更能表达这个变量的实际含义。

习惯2:不要易在全局修改会话级别的配置

对于某些会话级别的设置,不要轻易地在全局增加它们的值,除非你确认这样做是对的。比如:sort-buffer-size,该参数控制排序操作的缓存大小,MySQL只会在查询需要做排序操作时才会为该缓冲分配内存,一旦需要排序,就会一次性分配指定大小的内存,即使是非常小的排序操作。因此在配置文件中应该配置的小一些,然后在某些查询需要排序时,再在连接中把它调大。比如:

SET @@seession.sort-buffer-size := <value>

— 执行查询的sql

SET @@seession.sort-buffer-size := DEFAULT #恢复默认值

# 可以将类似的代码封装在函数中方便使用。

习惯3:配置变量,并不是值越大越好

配置变量时,并不是值越大越好,而且如果设置的值太高,可能更容易导致内存问题。在修改完成后,应该通过监控来确认变量的修改对服务器整体性能的影响。

习惯4范注,版本控制

在配置文件中写好注释,可能会节省自己和同事大量的工作,一个更好的习惯是把配置文件置于版本控制之下。

说完了好习惯,再来说说不好的习惯。

习惯1:根据一些“比率”来调

一个经典的按“比率”调优的经验法则是,缓存的命中率应该高于某个百分比,如果命中率过低,则应该增加缓存的大小。这是非常错误的意见,大家可以仔细思考一下:缓存的命中率跟缓存大小有必然联系吗?(分母变大,值就变大了?)除非确实是缓存太小了。关于MyISAM键缓冲命中率,下文会详细说明。

习惯2:随便使用优脚本

尽量不要使用调优脚本!不同的业务场景、不同的硬件环境对MySQL的性能要求是不一样的。比如有些业务对数据的完整性要求较高,那么就一定要保证数据不丢失,出现故障后可恢复数据,而有些业务却对数据的完整性要求没那么高,但对性能要求更高。因此,即使是同一个变量,在这两个不同场景下,其配置的值也应该是不同的。那你还能放心的使用网上找到的脚本吗 ?

注:本小节示例的几个配置项,仅用于举例说明,并不代表它们有多么重要,请根据实际应用场景配置它们。就比如sort-buffer-size,你真的需要100M内存来缓存10行数据?

给你一个基本的MySQL配置

前面已经说到,MySQL可配置性太强,看起来需要花很多时间在配置上,但其实大多数配置的默认值已经是最佳的,最好不要轻易改动太多的配置,你甚至不需要知道某些配置的存在。这里有一个最小的示例配置文件,可以作为服务器配置文件的一个起点,其中有一些配置项是必须的。本节将为你详细剖析每个配置有何作用?为什么要配置它?怎么确定合适的值?

[mysql]

# CLIENT #

port                           = 3306

socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #

user                           = mysql

port                           = 3306

default-storage-engine         = InnoDB

socket                         = /var/lib/mysql/mysql.sock

pid-file                       = /var/lib/mysql/mysql.pid

# DATA STORAGE #

datadir                        = /var/lib/mysql/

# MyISAM #

key-buffer-size                = 32M

myisam-recover                 = FORCE,BACKUP

# SAFETY #

max-allowed-packet             = 16M

max-connect-errors             = 1000000

# BINARY LOGGING #

log-bin                        = /var/lib/mysql/mysql-bin

expire-logs-days               = 14

sync-binlog                    = 1

# LOGGING #

log-error                      = /var/lib/mysql/mysql-error.log

log-queries-not-using-indexes  = 1

slow-query-log                 = 1

slow-query-log-file            = /var/lib/mysql/mysql-slow.log

# CACHES AND LIMITS #

tmp-table-size                 = 32M

max-heap-table-size            = 32M

query-cache-type               = 0

query-cache-size               = 0

max-connections                = 500

thread-cache-size              = 50

open-files-limit               = 65535

table-definition-cache         = 4096

table-open-cache               = 10240

# INNODB #

innodb-flush-method            = O_DIRECT

innodb-log-files-in-group      = 2

innodb-log-file-size           = 256M

innodb-flush-log-at-trx-commit = 1

innodb-file-per-table          = 1

innodb-buffer-pool-size        = 12G

分段

MySQL配置文件的格式为集中式,通常会分成好几部分,可以为多个程序提供配置,如[client]、[mysqld]、[mysql]等等。MySQL程序通常是读取与它同名的分段部分。

  • [client] 客户端默认设置内容
  • [mysql] 使用mysql命令登录MySQL数据库时的默认设置
  • [mysqld] 数据库本身的默认设置

例如服务器mysqld通常读取[mysqld]分段下的相关配置项。如果配置项位置不正确,该配置是不会生效的。

GENERAL

首先创建一个用户MySQL来运行mysqld进程,请确保这个用户拥有操作数据目录的权限。设置默认端口为3306,有时为了安全,可能会修改一下。默认选择InnoDB存储引擎,在大多数情况下是最好的选择。但如果默认是InnoDB,却需要使用MyISAM存储引擎,请显式地进行配置。许多用户认为其数据库使用了某种存储引擎但实际上却使用的是另外一种,就是因为默认配置的问题。

接着设置数据文件的位置,这里把pid文件和socket文件放到相同的位置,当然也可以选择其它位置,但要注意的是不要将socket文件和pid文件放到MySQL编译的默认位置,因为不同版本的MySQL,这两个文件的默认路径可能会不一致,最好明确地设置这些文件的位置,以免版本升级时出现问题。

说明:在类UNIX系统下本地连接MySQL可以采用UNIX域套接字方式,这种方式需要一个套接字(socket)文件,即配置中的mysql.sock文件。

当MySQL实例启动时,会将自己的进程ID写入一个文件中——该文件即为pid文件。该文件可由参数pid-file控制,默认位于数据库目录下,文件名为主机名.pid。

DATA STORAGE

datadir用于配置数据文件的存储位置,没有什么好说的。

顶一下
(0)
0%
踩一下
(0)
0%
标签(Tag):数据库 MYSQL mysql数据库
------分隔线----------------------------
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
验证码:点击我更换图片
猜你感兴趣