1. # For advice on how to change settings please see
    2. # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
    4. [mysqld]
    5. datadir = /var/lib/mysql
    6. socket = /var/lib/mysql/mysql.sock
    7. log-error = /var/log/mysqld.log
    8. pid-file = /var/run/mysqld/mysqld.pid
    9. character-set-server = utf8mb4
    11. symbolic-links = 0
    13. # 开启binlog
    14. server-id = 1
    15. log_bin = /var/lib/mysql/mysql-bin
    16. log_bin_index = /var/lib/mysql/mysql-bin.index
    17. max_binlog_size = 512M
    18. # 多少次事务提交后,将binlog刷新到磁盘 默认为0,表示由系统控制
    19. sync_binlog = 0
    20. # 当本机作为slave时,是否将master的binlog记录到本地binlog(便于多级主从)
    21. log_slave_updates = on
    22. # master 复制信息存储方式 file/table
    23. master-info-repository = file
    24. # relay log 策略 保证主从一致性
    25. relay_log_recovery = on
    26. # slave 进程随mysql服务启动而启动
    27. skip-slave-start = on
    29. # 允许sql导入本地文件
    30. local_infile = on
    31. # 数据库内部 资源/等待监控
    32. performance_schema = off
    34. # 开启慢日志
    35. long_query_time = 2
    36. slow-query-log = on                                                                                                                 
    37. slow-query-log-file = /var/lib/mysql/slow-query.log
    38. # 慢日志存储方式 file/table
    39. log_output = file
    40. # 记录 未使用index且速度慢的查询
    41. log-queries-not-using-indexes = on
    43. # 设置事务模式 不产生GTID事务,为slave时,接收GTID事务和匿名事务
    44. gtid-mode = OFF_PERMISSIVE
    45. # 设置最大连接数 DBInstanceMemory/12M
    46. max_connections = 1000
    47. # 终止空闲时间超过60秒 的连接
    48. wait_timeout = 60
    49. # 单个主机错误连接操作 100次 锁定主机
    50. max_connect_errors = 100
    51. # 密码过期策略 永不过期
    52. default_password_lifetime = 0
    54. # 默认存储引擎
    55. default_storage_engine = InnoDB
    56. # timestamp时间格式策略 字段可以为null时,插入null会记录null,而不是当前时间
    57. explicit_defaults_for_timestamp = 1
    59. # 此值为 max_connections*每次查询的表数量 不宜过大
    60. table_open_cache = 2046
    61. table_open_cache_instances = 16
    62. # 线程创建时候分配的内存大小 SQL越复杂/存储过程递归越多 此值响应增加
    63. thread_stack = 256K
    65. # innodb缓冲池大小 innodb_buffer_pool_size = DBInstanceMemory * 3/4
    66. innodb_buffer_pool_size = 1G
    67. # 此值需要通过业务需求调整
    68. # 内存和表越多分配值越大 https://dev.mysql.com/doc/refman/5.7/en/server-configuration.html
    69. read_buffer_size = 1M
    70. # 开启独立表空间
    71. innodb_file_per_table = 1
    72. # innodb 数据刷新到磁盘方式
    73. innodb_flush_method = O_DIRECT
    74. # 事务在内存中缓冲大小(2-8M)
    75. innodb_log_buffer_size = 8M
    76. # 单个日志文件大小
    77. innodb_log_file_size = 256M
    78. # 事务提交后的回收udo页的线程
    79. innodb_purge_threads = 1
    81. [client]
    82. default-character-set = utf8mb4
    83. [mysql]
    84. default-character-set = utf8mb4