MySQL服务器配置问题

SQL MODE

配置MySQL处理SQL的方式

配置方式

//方法1 命令设置
set [session/global/persist] sql_mode='xxxxxx';
//方法2 修改配置文件my.cnf后重启
[mysqld] sql_mode=xxxxxx

常用的SQL MODE

SQL MODE	说明
ANSI	包含以下四项。
ONLY_FULL_GROUP_BY	对于GROUP BY聚合操作，如果出现在SELECT中的列、HAVING或者ORDER BY子句的非聚合列，没有在GROUP BY 中出现，那么这个SQL语法检测报错。
ANSI_QUOTES	禁止用双引号来引用字符串。
REAL_AS_FLOAT	REAL作为FLOAT的同义词。
PIPES_AS_CONCAT	将双竖线视为字符串的连接操作而非 或 运算符。
TRADITIONAL	包含以下六项。5.6中已为默认。
NO_AUTO_CREATE_USER	GRANT语句执行时若账户不存在也不自动创建账户。仅在5.6和5.7中有用，8.0的GRANT语句已经无自动创建账户的功能。
ERROR_FOR_DIVISION_BY_ZERO	0做除数时，如果未设置此模式，结果为NULL，无警告；如果设置了此模式，结果仍然为NULL，但会产生警告。
STRICT_TRANS_TABLES / STRICT_ALL_TABLES	在事务存储引擎表/所有存储引擎表上启用严格模式。严格模式下，如果SQL类型不匹配、数字超出范围将会检测报错，而不是自动转换（如将字符串转换为0，将过大的数字转为范围边界数值）并仅仅产生警告。
NO_ENGINE_SUBSTITUTION	在严格模式，当指定的存储引擎不可用时报错。在非严格模式，当指定的存储引擎不可用时替换默认存储引擎，但会产生警告。5.8中已为默认。
NO_ZERO_DATE	在严格模式，不接受 '0000-00-00’做为合法日期。在非严格模式，可以接受该日期，但会产生警告。
NO_ZERO_IN_DATE	在严格模式，不接受月或日部分为0的日期。在非严格模式，可以接受该日期，但会产生警告。

配置参数

配置动态参数

使用set命令

//1 会话级别
set [session | @@session.] system_var_name=expr;
//2 全局级别
set [global | @@global.] system_var_name=expr;
//3 全局持久化级别
set [persist | @@global.] system_var_name=expr;

注意： persist是8.0后新加的，效果如同global，且重启后仍然生效。机制为将配置更改写入到data下的mysqld.auto.conf文件中，启动时先读区conf下的my.conf，最后读取mysqld.auto.conf，后面的配置将覆盖前面已有的配置。如果是8.0之前的版本，在set global的同时，需要手动修改my.conf。

比较配置文件

使用percona公司提供的工具pt-config-diff
pt-config-diff u=root,p=,h=localhost /etc/my.conf
注意： 目前还不支持8.0版本。

常用的配置参数

服务器配置参数

参数	说明
max_connections	设置MySQL允许访问的最大连接数量。不要太大，防止内存溢出。
interactive_timeout	设置交互连接sleep的timeout时间。
wait_timeout	设置非交互连接sleep的timeout时间。
max_allowed_packet	控制MySQL可以接收的数据包的大小。主从服务器一定要保持一致。
sync_binlog	表示每写多少次缓存，会向磁盘同步一次binlog。主服务器建议设置1。
sort_buffer_size	设置每个会话使用的排序缓冲区大小。用到排序时才分配，分配一步到位。不能设置过大，需要考虑这是会话级别。
join_buffer_size	设置每个会话使用的连接缓冲区大小。多连一个表就多分配同等大小一块。不能设置过大，需要考虑这是会话级别。
read_buffer_size	设置每个会话当对一个MyISAM进行表扫描时使用的读缓存区大小。注意：InnoDB查询中建立的临时表默认就是MyISAM的表，所以也可能影响InnoDB表查询。不能设置过大，需要考虑这是会话级别。
read_rnd_buffer_size	设置每个会话使用的索引缓冲区的大小。用到索引时才按需分配大小。不能设置过大，需要考虑这是会话级别。
binlog_cache_size	设置每个会话使用的缓存未提交的事务的缓存区大小。不能设置过大，需要考虑这是会话级别。

存储引擎配置参数

参数	说明
innodb_flush_log_at_trx_commit	0：每秒进行一次redo log的磁盘刷新操作。1：每次事务提交进行一次redo log的磁盘刷新操作。2：每次事务提交写入系统缓存，每秒进行一次redo log的磁盘刷新操作。默认为1。
innodb_buffer_pool_size	设置Innodb缓存池的大小，建议为服务器内存的75%。
innodb_buffer_pool_instances	Innodb缓冲池实例个数，每个实例的大小为innodb_buffer_pool_size/innodb_buffer_pool_instances。目的是减少并发冲突，建议单个大小1G~8G。
innodb_file_per_table	设置每个表独立使用一个表空间。5.7中已为默认。