主要添加这句：sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

    1. [client]
    2. port    = 3306
    3. socket  =/var/lib/mysql/mysqld.sock
    5. [mysql]
    6. no-auto-rehash
    8. [mysqld]
    9. port    = 3306
    10. datadir = /var/lib/mysql
    11. socket  = /var/lib/mysql/mysqld.sock
    12. log-error = /var/lib/mysql/error.log
    13. pid-file = /var/lib/mysql/mysqld.pid
    15. skip_name_resolve = 1
    16. default_time_zone = "+8:00"
    17. character-set-server = utf8mb4
    18. collation-server = utf8mb4_general_ci
    19. init_connect='SET NAMES utf8mb4'
    20. lower_case_table_names = 1
    21. transaction_isolation = READ-COMMITTED
    22. explicit_defaults_for_timestamp = true
    23. open_files_limit    = 65535
    24. max_connections = 300
    25. max_connect_errors = 600
    26. back_log = 110
    27. table_open_cache = 600
    28. table_definition_cache = 700
    29. table_open_cache_instances = 64
    30. thread_stack = 512K
    31. external-locking = FALSE
    32. max_allowed_packet = 128M
    33. sort_buffer_size = 4M
    34. join_buffer_size = 4M
    35. thread_cache_size = 20
    37. interactive_timeout = 1800
    38. wait_timeout = 1800
    40. lock_wait_timeout = 3600
    42. tmp_table_size = 64M
    43. max_heap_table_size = 64M
    45. server-id = 110
    47. log-bin=mysql-bin
    49. binlog_format = ROW
    50. binlog_row_image = FULL
    51. binlog_expire_logs_seconds = 1209600
    52. master_info_repository = TABLE
    53. relay_log_info_repository = TABLE
    54. log_slave_updates
    55. relay_log_recovery = 1
    56. slave_skip_errors = ddl_exist_errors
    57. innodb_flush_log_at_trx_commit = 1
    58. sync_binlog = 1
    60. binlog_cache_size = 4M
    61. max_binlog_cache_size = 2G
    62. max_binlog_size = 1G
    63. gtid_mode = on
    64. enforce_gtid_consistency = 1
    66. key_buffer_size = 15M
    67. read_buffer_size = 8M
    68. read_rnd_buffer_size = 4M
    69. bulk_insert_buffer_size = 64M
    71. innodb_thread_concurrency = 0
    72. innodb_buffer_pool_size = 8G
    73. innodb_buffer_pool_instances = 4
    74. innodb_buffer_pool_load_at_startup = 1
    75. innodb_buffer_pool_dump_at_shutdown = 1
    76. innodb_data_file_path = ibdata1:1G:autoextend
    77. innodb_log_buffer_size = 32M
    78. innodb_log_files_in_group = 3
    79. innodb_log_file_size = 2G
    80. innodb_undo_log_truncate = 1
    81. innodb_max_undo_log_size = 4G
    82. innodb_io_capacity = 4000
    83. innodb_io_capacity_max = 8000
    84. innodb_flush_sync = 0
    85. innodb_flush_neighbors = 0
    86. innodb_write_io_threads = 8
    87. innodb_read_io_threads = 8
    88. innodb_purge_threads = 4
    89. innodb_page_cleaners = 4
    90. innodb_open_files = 65535
    91. innodb_max_dirty_pages_pct = 50
    92. innodb_flush_method = O_DIRECT
    93. innodb_lru_scan_depth = 4000
    94. innodb_checksum_algorithm = crc32
    95. innodb_lock_wait_timeout = 20
    96. innodb_rollback_on_timeout = 1
    97. innodb_print_all_deadlocks = 1
    98. innodb_sort_buffer_size = 67108864
    99. innodb_autoinc_lock_mode = 1
    100. innodb_file_per_table = 1
    101. innodb_online_alter_log_max_size = 4G
    102. sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    103. #--###########################-- innodb性能设置 结束 --##########################################
    105. [mysqldump]
    106. quick
    107. max_allowed_packet = 128M