从入门到精通 | MySQL知识整理（中）

三、基本配置

配置选项

1.预编译的选项:

在生成RPM包时指定的选项;
在源码安装时指定的选项;

2.命令行选项:

可以在启动服务器(mysqld)时在命令行上指定启动选项;
默认情况下,服务器在运行时使用其配置变量的预编译值;但是,如果默认值不适合环境,则可添加运行时选项,让服务器使用其他值来执行以下操作:

指定重要的目录和文件的位置;
控制服务器写入的日志文件;
覆盖服务器与性能相关的变量的内置值(即,控制最大同时连接数以及缓冲区和高速缓存的大小);
在服务器启动时启用或禁用预编译的存储引擎通过使用命令行选项或选项文件,或者使用两者的组合,可以指定服务器启动时的运行时选项(以更改其配置和行为);

命令行选项优先于选项文件中的任何设置

3.配置文件选项:在配置文件my.cnf中指定的启动选项;最常用的方式;4.查看相关帮助mysqld –verbose –help;

使用选项文件的原因

1.将选项放在文件中后,不需要每次启动服务器时都在命令行上指定选项;对于复杂的选项(如用于配置InnoDB表空间的选项),这样做更加方便,并且更不容易出错;2.如果所有服务器选项都在一个选项文件中,则可概览服务器的配置情况;3.MySQL程序可以访问多个选项文件中的选项,要创建或修改某个选项文件,必须拥有该文件的写入权限;客户机程序仅需要读取访问权限;

选项文件组

1.选项文件中的选项按组进行组织,每个组前面有一个为组命名的[group-name]行,通常,组名称是选项组适用的程序的类别或名称;选项组示例包括:2.[client]:用于指定适用于所有客户机程序的选项;[client]组的一个常见用途是指定连接参数,因为在一般情况下,不管使用什么客户机程序,都要建立到同一个服务器的连接;3.[mysql]和[mysqldump]:分别用于指定适用于mysql和mysqldump客户机的选项;此外,也可以单独指定其他客户机选项;4.[server]:用于指定同时适用于mysqld和mysqld_safe服务器程序的选项;5.[mysqld],[mysqld-5.6],[mysqld56]和[mysqld_safe]:用于指定适用于不同服务器版本或启动方法的选项;

编写选项文件

1.要创建或修改某个选项文件,最终用户必须拥有该文件的写入权限;服务器本身仅需要读取访问权限;服务器读取选项文件,但不创建或修改选项文件;2.如何在选项文件中写入一个选项:

使用长选项格式(像命令行上使用的那样),但省略前导短划线;
如果某个选项取值,则允许在等号两则加空格( = );此规则不适用于在命令行上指定的选项,eg:指定默认选项文件;

3.在幻灯片上的示例中,请注意以下方面:

[client]:此组中的选项适用于所有标准客户机;

host:指定服务器主机名;compress:指示客户机/服务器协议对通过网络发送的通信使用压缩;

[mysql]:此组中的选项仅适用于mysql客户机;

show-warnings:指示MySQL在每条语句后显示任何当前警告;

mysql客户机同时使用[client]和[mysql]组中的选项,因此将使用显示的全部三个选项;

选项文件位置

1.Linux:

（1）./etc/my.cnf;
（2）./etc/mysql/my.cnf;
（3）./usr/local/mysql/etc/my.cnf;
（4）.~/.my.cnf;
（5）.如果设置了MYSQL_HOME环境变量,则将搜索$MYSQL_HOME/my.cnf文件;

2.Windows:

（1）.C:\目录下的my.ini和my.cnf;
（2）.C:\Windows(或C:\WinNT)目录;
（3）.C:\Program Files\MySQL\MySQL Server 目录;

3.MySQL命令行程序会在MySQL安装目录中搜索选项文件;

选项文件中的启动选项

要在选项文件中指定服务器选项,需要在[mysqld]或[server]组下指示特定选项;1.日志记录:可以通过启用所需日志的类型为服务器启用日志记录;比如:

（1）.general_log # 常规查询日志;
（2）.log-bin # 二进制日志;
（3）.slow_query_log # 慢速查询日志;

2.默认存储引擎:可以使用–default-storage-engine选项指定不同于InnoDB的默认存储引擎;3.系统变量:可以通过设置服务器系统变量值来定制服务器;

（1）.max_connections=200 # 增加允许的最大连接数;（2）.innodb_buffer_pool_instances=4 # 增加InnoDB缓冲池数的默认值;

4.共享内存:在Windows上默认不启用;可以使用shared-memory选项来启用命名管道支持;5.命名管道:要启用命名管道支持,使用enable-named-pipe选项;

样例选项文件

1.Linux:

对于RPM安装,样例选项文件在/usr/share/mysql中;
对于TAR文件安装,样例文件在MySQL安装目录下的share目录中;

2.Windows:选项文件位于MySQL安装目录(my.ini)中;3.如果多次指定一个选项(不管是在同一个选项文件中,还是在多个选项文件中),则最后出现的选项值优先;

（1）.–defaults-file=:使用指定位置的选项文件;
（2）.–defaults-extra-file=:使用指定位置的其他选项文件;
（3）.–no-defaults:忽略所有选项文件;

显示选项文件中的选项

1.# my_print_defaults –defaults-file=/usr/local/mysql/my.cnf client mysql mysqld;
2.# mysql –print-defaults:貌似打印不出来;

遮蔽验证选项

1.建议不要使用mysql -uroot -poracle形式在命令行上指定口令;缺点:可以通过history命令查看到密码;

2.为方便起见,可以将口令放在[client]选项组中,但口令以纯文本方式存储,对选项文件有读取访问权限的任何人都能轻易地看到;

3.利用mysql_config_editor实用程序,可以将验证凭证存储在加密的登录文件.mylogin.cnf中;在Linux和UNIX上,该文件位置是当前用户的主目录;MySQL客户机程序以后可以读取该文件以获取用于连接到MySQL服务器的验证凭证;TIPS:加密方法是可逆的,因此不应假设凭证对任何有文件读取特权的人都是安全的;相反,该功能使得避免使用纯文本凭证变得更容易;

4.文件说明:.mylogin.cnf登录文件的未加密格式由选项组组成,类似于其他选项文件;.mylogin.cnf中的每个选项组称为“登录路径”,仅允许一组有限的选项:主机,用户和口令;可将登录路径视为一组值,可以指示服务器主机以及用于服务器验证的凭证;

登录路径

1.创建登录路径:

mysql_config_editor set –login-path=admin –host=localhost –user=root –password;

如果调用mysql_config_editor时不使用–login-path选项,则将使用[client]登录路径;默认情况下,所有标准客户机都使用此登录路径;

（1）.查看生成的文件:ll ~/.mylogin.cnf;
（2）.登录:mysql –login-path=admin;

2.以纯文本格式查看单个登录路径:

mysql_config_editor print –login-path=admin;

3.以纯文本格式查看所有登录路径:

mysql_config_editor print –all;

4.删除登录路径:

mysql_config_editor remove –login-path=admin;

服务器系统变量

1.查看所有参数的默认值和读取选项文件之后的值:

mysqld –verbose –help;

2.查看所有参数的默认值和忽略任何选项文件中的设置:

mysqld –no-defaults –verbose –help;

3.查看变量值,没有其他启动选项:

SHOW GLOBAL VARIABLES;

动态系统变量

1.MySQL维护了两种包含系统变量的作用域:

GLOBAL变量影响服务器的整体操作;
SESSION变量影响其对单个客户机连接的操作;
变量存在于任一作用域中,也可同时存在于两个作用域中;

2.变量及其作用域的示例包括:

（1）.仅全局:key_buffer_size,query_cache_size;
（2）.全局和会话:sort_buffer_size,max_join_size;
（3）.仅会话:timestamp,error_count;

3.在更改变量值时,适用以下几点:

设置会话变量不需要任何特殊特权,但客户机只能更改自己的会话变量,不能更改其他任何客户机的会话变量;
LOCAL和@@local是SESSION和@@session的同义词;
如果不指定GLOBAL或SESSION,则当会话变量存在时,SET将更改会话变量;会话变量不存在时,将产生错误;

TIPS:修改的全局参数不会在选项文件中反应出来,需要手动修改,以便下次启动后生效;与Oracle对比;

显示动态系统变量

设定特定的变量时要注意赋值的类型;— 结构化系统变量;1.MySQL支持一种结构化变量类型,该变量类型可以指定控制键高速缓存操作的参数;键高速缓存结构化变量具有以下组件:

（1）.key_buffer_size;
（2）.key_cache_block_size;
（3）.key_cache_division_limit;
（4）.key_cache_age_threshold;

2.要引用结构化变量实例的组件,可使用复合名称:

instance_name.component_name;

3.相关文档:

http://dev.mysql.com/doc/refman/5.6/en/structured-system-variables.html。

服务器状态变量

1.LOCAL是SESSION的同义词;

2.如果没有修饰符,则默认值为SESSION;

3.SHOW STATUS

示例:mysql> SHOW GLOBAL STATUS;

SQL模式

SQL模式由控制查询处理某些方面的可选值组成,设置了相应的SQL模式后,客户机就可以对以下项目进行某种程度的控制:

1.输入数据:SQL模式可用于指示服务器对接受输入数据的宽容度;

2.标准SQL符合性:SQL模式可用于启用或禁用与标准SQL符合性相关的行为;

3.兼容性:SQL模式可用于改进与其他数据库系统的兼容性;

设置SQL模式

1.可以使用–sql-mode选项设置服务器启动时的默认SQL模式;

2.单个客户机可按自己的要求在选项文件内配置SQL模式;

3.如果没有修饰符,则SET将更改会话SQL模式;调用SET语句时可以带一个空字符串来清除当前SQL模式,也可以带一个或多个模式名称(用逗号分隔);

4.如果值为空或者包含多个模式名称,则必须将值放在引号中;如果值包含一个模式名称,则引号可有可无;SQL 模式值不区分大小写;

5.查看当前的sql_mode模式:

使用SELECT语句检查当前的sql_mode设置:
SELECT @@sql_mode;
查看系统变量:
SHOW VARIABLES LIKE ‘sql_mode’;

例子:1.使用单个模式值设置SQL模式:

SET sql_mode = ANSI_QUOTES; SET sql_mode = ‘TRADITIONAL’;

2.使用多个模式名称设置SQL模式:

SET sql_mode = ‘IGNORE_SPACE,ANSI_QUOTES,NO_ENGINE_SUBSTITUTION’;

常用SQL模式

1.STRICT_TRANS_TABLES,STRICT_ALL_TABLES:
没有这些模式,MySQL将接受缺少,超出范围或格式不正确的值;

启用 STRICT_TRANS_TABLES时将为事务表设置“严格模式”;

也可在默认的my.cnf文件中启用;启用STRICT_ALL_TABLES时将为所有表设置严格模式; 

2.TRADITIONAL:启用此SQL模式可对输入数据值施加类似于其他数据库服务器的限制;

在此模式下,使用GRANT语句可创建要求指定口令的用户;

3.IGNORE_SPACE:默认情况下,必须调用函数名称与后接括号间没有空格的函数;

启用此模式后,允许存在此类空格,并使函数名称成为保留字;

4.ERROR_FOR_DIVISION_BY_ZERO:
默认情况下,除数为零时将产生结果NULL,
在启用此模式的情况下插入数据时,除数为零将导致出现警告,
在严格模式下将出现错误;(这个模式之后会被废弃掉)

5.ANSI:使用此组合模式将使MySQL服务器变得更加“类似于ANSI”;
即,此模式支持的行为更像标准SQL,如ANSI_QUOTES和PIPES_AS_CONCAT;

6.NO_ENGINE_SUBSTITUTION:如果在创建或更改表时指定了不可用的存储引擎,除非启用了此模式,否则MySQL 将替换默认存储引擎;
这是默认的SQL模式;

日志文件

1.错误日志(error log):

记录MySQL启动,关闭和运行时产生的重大的错误的信息;
如果mysqld警告一个表需要自动的检查或者修复,也会记录一个错误日志;
可以使用–log-error=file_name选项来指定错误日志文件,如果没有指定,则系统默认在data目录下生产一个hostname.err的文件;也可以在配置文件中配置log_error变量;
主要是由mysqld_safe脚本可创建错误日志,并在启动服务器时将其输出重定向到该错误日志;
查询log_error的位置:
>show variables like ‘log_error’;

2.常规查询日志(general query log):

3.慢查询日志(slow query log):

调优时使用,记录超出指定时间的sql语句;
慢查询日志主要记录执行时间超过long_query_time变量指定时间的sql语句,这个时间不包括获得锁的时间,只包含执行时间,系统默认时间是1s(long_query_time=1.0000);
使用–slow-query-log=0|1选项和–slow_query_log_file=file_name选项指定(在mysql5.1.6版本可以指定mysqld的–long-slow-queries=file_name选项启动);
命令行参数:–log-slow-queries=file_name;指定慢查询日志文件
系统变量:

slow_query_log:开启慢查询功能,
set global slow_query_log = [0|OFF|1|ON];
slow_query_log_file:指定慢查询日志文件,系统默认是在data目录下的hostname-slow.log文件;
long_query_time:指定查询的最大时间,
set global long_query_time=n;
log_queries_not_using_indexes:不用索引的慢查询,默认是功能关闭的;
查看某个表是否有索引:> show index from t;
打开此功能:>set global log_queries_not_using_indexes =1;
查看变量
:>show variables like ‘log_queries_not_using_indexes’;

log_output变量值:

①.FILE:保存到文件中;
②.TABLE:保存到表中;SET GLOBAL log_output = ‘TABLE’;
③.清空日志表:TRUNCATE mysql.slow_log;

分析慢日志文件的工具:mysqldumpslow,查看具体的帮助是用mysqldumpslow –help;

4.二进制日志(binary log):

记录所有对数据库更新和潜在的更新语句(一个delete语句,但是没有影响的行),语句以事件(event)的方式存储,同时也包含了更新语句执行的时间信息;
它不记录那些不修改任何数据的语句,如果想要记录所有的语句,可以使用query log;
它主要的目的是在做还原操作时尽可能全的更新数据库,因为它包含在一次备份后的所有更新操作.它同样被用在master replication server中作为一个记录发送给slave servers;
打开二进制日志会损失1%的性能,但是它带来的好处远远超过这些;
（查看二进制日志是否打开,需要查看log_bin参数是否是ON:
>show variables like ‘log_bin’;
命令行参数

①.–log-bin=filename:记录二进制日志文件的位置,尽量指定路径名,如果不指定的话则保存在数据目录;
②.–log-bin-index=file:记录二进制日志文件索引的位置,保存了日志文件名;
③.–max_binlog_size:单个文件最大多少;
④.–binlog-do-db=db_name:哪个数据库使用,只有这个数据库使用;
⑤.–binlog-ignore-db=db_name:哪个数据库不使用,只有这个数据库不使用;

系统变量

①.log_bin:日志的位置;
②.binlog_cache_size:二进制日志缓存大小,是每一个连接进来的线程分配的大小,不是整个服务器的大小;
③.max_binlog_cache_size:最大缓存大小;
④.max_binlog_size:单个文件最大大小,超过此大小则再分配一个文件,但是一个事务必须在一个文件中,所以可能会稍大点;
⑤.binlog_cache_use:当前连接使用的binlog缓存的事务的数量,使用show status like ‘binlog_cache_use’查看(show status命令显示了所有连接到mysql服务器的状态值);
⑥.binlog_cache_disk_use:如果binlog_cache_use不够用,则在磁盘上缓存,应该尽量避免;
⑦.binlog_do_db:设置master-slave时使用;
⑧.binlog-ignore-db:设置哪个数据库不记录日志;
⑨.sync_binlog:缓存与硬盘的同步频率(commit多少下同步一次,0表示服务器自动控制);
⑩.binlog_format:二进制日志的格式;

查看当前二进制文件的名称和大小,show binary/master logs;
如果不指定二进制日志文件的位置,默认存放在data文件夹下,日志文件是:mysql-bin.xxxxxx,索引文件是mysql-bin.index;
如果要切换日志的话,执行flush logs命令;
初始化二进制日志系统,从新生成:reset master命令;
删除某个日志文件:
purge binary logs [before ‘datetime’ / to ‘log_name’] 删除指定日期之前的和删除指定文件之前的日志文件;
设置日志文件的失效期:
参数为–expire_logs_days,set global expire_log_days=n,N天前的日志自动删除;
二进制日志的格式

①.查看格式:show [global] variables like ‘binlog_format’;
②.设置日志格式:set [global] binlog_format = statement|row|mixed;
③.查看binlog中的事件:show binlog events in ‘mysql-bin.000002’ from 0;
④.使用mysqlbinlog程序打开;

5.审计日志(audit log):

用于记录企业版基于策略的审计信息;审计日志是作为企业版插件提供的;
由–audit-log选项和audit_log_file选项来控制;
审计过程会不断写入审计日志,直到将该插件删除,或者通过audit_log_policy=NONE 选项设置关闭审计;
在服务器启动时使用audit_log=FORCE_PLUS_PERMANENT作为选项,可以防止删除该插件;

补充:6.InnoDB重做日志(innodb redo log);

与innodb数据引擎相关;
用来实现灾难恢复(crash recovery),突然断电会导致innodb表空间中的数据没有写到磁盘上,通过执行redo log能够重新执行这些操作来恢复数据;
提升innodb的i/o性能,innodb引擎把数据和索引都载入到内存中的缓冲池中,如果每次休息数据和索引都需要更新到磁盘,必定会增加i/o请求,而且因为每次更新的位置都是随机的,磁头需要频繁的定位导致效率很低,所以innodb每处理完一个事务后只添加一条日志log,另外有一个线程负责智能的读取日志文件并批量更新到磁盘上,实现最高效的磁盘写入;
系统变量:

①.innodb_log_buffer_size:日志缓冲区的大小;
②.innodb_log_file_size:日志文件的大小;
③.innodb_log_files_in_group:一组日志中有几个文件:
- 文件名为ib_logfileX(X从0开始一次增加);
- 先关闭数据库服务:>mysqladmin shutdown(mysql.server stop);- 把data目录下的ib_logfile文件移动走:>mv ib_logfile /tmp;
- 在配置文件中添加innodb_log_files_in_group=n的参数;
- 启动数据库服务:>mysqld –defaults-file=./my.cnf –user=mysql(mysql.server start);
- 可以查看error log文件观察启动过程;
④.innodb_log_group_home_dir:日志存放的性对路径(相对于$MYSQL_HOME/mysql/data目录,即datadir目录);- 关闭服务器;- 在配置文件中添加此参数,并指定路径;- 启动服务器;

innodb_flush_log_at_trx_commit:根据不同的数据安全级别去设定.

innodb_os_log_written:写入到文件日志的数据量,使用show status查询;
innodb_os_log_fsyncs:写入到磁盘的次数,使用show status查询;

日志文件用法列表

二进制日志记录

1.二进制日志包含描述数据库更改(如创建数据库或更改表数据)的“事件”;二进制日志还包含可能做出更改的语句的事件(例如,没有匹配行的DELETE);该日志还包含有关每条更新语句所用时间的信息;

2.二进制日志有两个重要用途:复制和数据恢复;

3.MySQL使用日志传送复制解决方案;使用日志传送系统时,可以将主系统上发生的所有数据更改存储在二进制日志中,然后通过从系统检索这些数据更改,并根据接收到的这些日志文件执行更改;

4.可以实时下载日志文件并执行内容;即,只要生成日志文件事件,就将其发送到连接的从系统供执行;由于网络传播存在延迟,从系统可能需要几秒到几分钟(最坏的情况)时间来接收更新;在理想的情况下,延迟会在一秒以内;

5.发生以下事件之一时,二进制日志会轮转:

重新启动MySQL服务器;
达到允许的最大大小(max_binlog_size);
发出了FLUSH LOGS SQL命令;

6.二进制日志独立于存储引擎,不管使用的存储引擎是哪个(即InnoDB或MyISAM),MySQL复制都会工作;

二进制日志记录格式

1.基于语句的二进制日志记录:

包含实际SQL语句
包括DDL(CREATE,DROP等)和DML(UPDATE,DELETE等)语句;
相对较小的文件保存磁盘空间和网络带宽;
并非所有复制的语句都会在远程计算机上正确重放;
要求主系统和从系统上复制的表和列完全相同(或者符合多个限制条件);

2.基于行的二进制日志记录:

指示对单个表行的影响情况;
正确重放所有语句,即使对于在使用基于语句的日志记录时未正确复制的功能导致的更改也是如此;

3.按如下方式设置格式:

SET [GLOBAL|SESSION] BINLOG_FORMAT=[row|statement|mixed|default];

注:使用mixed选项可让MySQL选取最适合单个事件的格式,MySQL通常会使用基于语句的二进制日志,但在需要时可恢复到基于行的复制;

列出二进制日志文件

1.SHOW BINARY LOGS语句可以列出当前日志文件和文件大小;
2.SHOW MASTER STATUS语句可以显示下一个事件的主状态;需要SUPER或REPLICATION CLIENT特权;

查看二进制日志内容

1.方式1:show binlog events in ‘mysql-bin.000002’ from 0;2.方法2:mysqlbinlog mysql-bin.xxxxxx;

删除二进制日志

1.默认情况下,不会删除旧的日志文件;2.根据存在时间删除日志:

要在二进制日志轮转过程中自动删除存在时间多于指定天数的任何二进制日志,可使用expire_logs_days设置;
也可以在选项文件中配置expire_logs_days:

[mysqld]expire_logs_days=7

PURGE BINARY LOGS BEFORE now() – INTERVAL 3 day;

4.根据文件名删除日志:

PURGE BINARY LOGS TO ‘mysql-bin.000010’;

配置企业审计

1.要安装audit_log插件:

（1）.方法1:使用INSTALL PLUGIN语法:INSTALL PLUGIN audit_log SONAME ‘audit_log.so’;
（2）.方法2:在服务器启动时设置plugin-load选项:[mysqld]plugin-load=audit_log.so

2.默认情况下,装入该插件就会启用日志记录;将选项audit-log设置为OFF可禁用日志记录;3.要防止在运行时删除该插件,可设置以下选项:

audit-log=FORCE_PLUS_PERMANENT;

4.日志文件命名为audit.log,默认情况下位于服务器数据目录中;要更改该文件的名称或位置,可在服务器启动时设置 audit_log_file系统变量;5.

要平衡遵从性和性能,可使用audit_log_strategy选项在SYNCHRONOUS,ASYNCHRONOUS,SEMISYNCHRONOUS和 PERFORMANCE之间进行选择;

6.如果将audit_log_rotate_on_size设置为某个大于0的数字,则当日志文件大小超出了该数量的4KB数据块大小时,将轮转日志文件;

审计日志文件

1.每个审计记录的TIMESTAMP采用UTC格式;2.NAME属性代表事件类型;例如,“Connect”表示登录事件,“Quit”表示客户机断开连接,“Shutdown”表示服务器关闭;3.“Audit”和“NoAudit”表示审计开始和停止的点;4.STATUS属性提供命令状态;这与MySQL命令SHOW ERRORS显示的Code值相同;5.有些属性仅在特定的事件类型中出现;例如,“Connect”事件包括诸如HOST,DB,IP和USER之类的属性;“Query”事件包括SQLTEXT属性;补充:审计过滤工具;