Mysql-服务配置问题

一, 请分析一个 Group By 语句的异常问题原因 ???

• sql_mode: 配置 mysql 处理 sql语句的方式; show variables like 'sql_mode';

  • set [session/global/presist(8.0v)] sql_mode =xxxx``

  • [mysqld 配置文件中配置]: sql_mode = value1, value2, value3

• sql_mode:

  • noly_full_group_by: 对 group_by 聚合操作, 如果出现在 select 中的列, having 或者 order by 子句的非聚合列, 没有在 group by 中出现, 那么这个 sql 的语法检查报错

  • ansi_quotes 禁止使用双引号来引用字符串, 使用单引号表示

  • real_as_float: real 类型作为 float

  • pipes_as_concat: 将 || 视为字符串的连接操作符, 而非 或 运算符

  • strict_trans_tables/strict_all_tables : 在事务存储引擎/所有存储引擎上启用严格模式, 那么这个 sql 语法检查会报错, 宽松的模式下; 在 insert 的时候会出现隐式的转换,

  • error_for_division_by_zero: 不允许 0 作为除数
    show warnings;

  • no_auto_create_user: 在用户不存在时, 不允许 grant 语句自动建立, 在 8.0 之后设置为默认

  • no_zero_in_date/no_zero_date: 日期数据内/日期数据不能含有 0, (严格模式下)

  • no_engine_substitution: 当指定存储引擎不可用时报错(严格模式下)

二, 如何比较系统运行配置文件和配置文件中的配置是否一致呢 ???

• 使用 set 命令配置动态参数, 造成了运行配置文件和配置文件中的不一致

  • set [session | @@session.] system_var_name = expr: 会改变当前的 session 连接

  • set [global | @@global.] system_var_name = expr: 对当前的 session 不受影响, 重启之后会变化

    1. show global variables like 'wait_timeout'

    2. set global wait_timeout=300

  • 8.0v 之后 set [persist | @@persist.] system_var_name = expr: 会生成 mysqld-auto.cnf 文件; 等 mysql 重启的时候;会加载这个文件来同步动态参数

  • 如果上面出现动态配置之后, 下次mysql 重启的时候, 会导致不一致, 必须在 mysql config 中同步配置, 才会保持一致的情况

• 使用 pt-config-diff 工具比较配置文件差异

  pt-config-diff u=root, p= h=localhost /etc/my.cnf 比较差异

三, 举几个 Mysql 中的关键性能参数 ???

常用的性能参数;

1. max_connections: 设置 mysql 允许访问的最大连接数(不要设置的太大, 容易引起内存溢出)

2. interactive_timeout: 设置交互的连接的 timeout 时间

3. wait_timeout: 设置非交互连接的 timeout 的时间

4. max_allowed_packet: 控制 mysql 可以接收的数据包的大小(如果是主从复制)

5. sync_binlog: 表示每写多少次缓冲会向磁盘同步一次 binlog, master 设置为 1;

如下五项为每个连接线程设置的; 如果有100个连接, 那么就是会是 100 陪的大小

6. sort_buffer_size: 表示每个线程使用的排序缓存区的大小(小心设置)

7. join_buffer_size: 设置每个回话所事宜的连接缓冲的大小(不要设置太大)

8. read_buffer_size: 指定了当对一个 myisam 进行表扫描时所分配的读缓存池大小(比如需要临时表)

9. read_rnd_buffer_size: 设置控制索引的缓冲区的大小

10. binlog_cache_size: 设置每个会话用于缓存未提交的事务缓存大小

存储引擎相关

11. innodb_flush_log_at_trx_commit: 0 => 每秒进行一次日志的磁盘刷新, 1 => 每次事务提交会刷新, 2 => 每次事务提交,每秒刷新

12. innodb_buffer_pool_size: 设置 innodb 缓冲池的大小, 应为系统的内存的 75%;

13. innodb_buffer_pool_instances: 实例个数, 总缓冲大小/实例个数

14. innodb_file_per_table: 设置每个表独立使用一个表空间文件