my.cnf详解

[TOC]

[client]

  • port = 3306
  • socket = /tmp/mysql_3306.sock
  • default-character-set = utf8mb4
  • password=''

[mysqld]

Basic Configure

  • server-id = 4363306
  • port = 3306
  • user = mysql
  • basedir = /opt/mysql
  • datadir = /data/mysql_3306/var
  • tmpdir = /data/mysql_3306/tmp
  • socket = /tmp/mysql_3306.sock
  • skip-external-locking = 1
  • skip-name-resolve = 1 #禁止MySQL对外部连接进行DNS解析,而只能用ip地址
  • default-storage-engine = InnoDB
  • character-set-server = utf8mb4
  • collation-server = utf8mb4_unicode_ci
  • wait_timeout = 1500 #关闭非交互连接之前等待活动的秒数,默认28800秒(8h)
  • interactive_timeout = 1500 #关闭交互式连接前等待活动的秒数,默认28800秒(8h)
  • connect_timeout = 60 #.连接超时时间
  • back_log = 1000
  • lock_wait_timeout = 600
  • event_scheduler = 1 #默认启用事件功能
  • lower_case_table_names = 1 #是否区分大小写，0区分/1不区分
  • explicit_defaults_for_timestamp
  • sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION #设置SQL服务器模式，用来定义MySQL应支持哪些SQL语法，默认值是空值
  • pid-file = /data/mysql_3306/tmp/pid_mysql.pid
  • default_password_lifetime = 0
  • log_timestamps = system
  • show_compatibility_56 = on
  • symbolic-links = 0
  • secure_file_priv = /tmp
  • default-time-zone = '+08:00'
  • skip_ssl
  • disable-partition-engine-check
  • userstat = on
  • thread_statistics = on

Binlog

  • log-bin = /data/mysql_3306/binlog/mysql-bin #.配置binlog日志路径,默认datadir
  • binlog_format = ROW #.设置binlog日志格式
  • binlog-rows-query-log-events = 1 #默认为0(off),只对binlog为row格式有效,启用后会将把sql语句输出到binlog,就像注释一样,这对dba使用mysqlbinlog -vv分析binlog将非常有用.
  • binlog_cache_size = 8M #基于会话的、所有未提交（uncommitted）的二进制日志缓存文件大小，默认大小为32KB
  • max_binlog_cache_size = 1G #缓冲池中的二进制日志文件大小.基于事务,不能过大,而过小会导致写临时文件.
  • expire_logs_days = 8 #超过x天的binlog删除
  • sync_binlog = 1 #刷binlog的方式,直接影响mysql的性能和完整性,0高性能/1高安全/n兼顾性能和安全.
  • binlog_row_image = full #默认full(在binlog为row格式下)会记录所有的行信息,而minimal只记录要修改列的记录,大大减少了binlog所占用的磁盘/网络/内存资源.
  • log_bin_trust_function_creators = 1
  • binlog_stmt_cache_size = 8M

以下这4个参数可使用zlib中的ISO-3309 CRC-32算法对binlog/relaylog进行checksum校验,以防止日志出错.

  • binlog-checksum = CRC32
  • slave_allow_batching = 1
  • master-verify-checksum = 1
  • slave-sql-verify-checksum = 1

Replication

  • slave_net_timeout = 60
  • skip-slave-start = 1 #.禁用之后，slave进程会随着mysql启动而启动
  • log_slave_updates = 1 #链式主从(A->B->C)，允许从库B写入binlog
  • slave_preserve_commit_order = 1
  • relay-log = /data/mysql_3306/binlog/repl-bin
  • relay-log-index = relay-bin.index
  • relay_log_purge = 1
  • binlog_gtid_simple_recovery = 1
  • slave_skip_errors = 1032,1062 #定义复制过程中从库可以自动跳过的错误号
  • read_only = 1 #限制普通用户对从库的更新操作，但是 对于 super 权限的超级用户依然可以更新
  • master-connect-retry = 600 #用来设置在和主库连接丢失的时候，重试的时间间隔，默认60秒

关于gtid的几个参数：

  • gtid-mode = on #.是否开启gtid功能
  • enforce-gtid-consistency = on #.强制gtid一致性

解决多主自增长ID重复的几个参数：

  • auto_increment_increment = 2
  • auto_increment_offset = 1

为了避免master.info和中继日志崩溃，在容忍额外的fsync()带来的开销的情况下推荐如下3个参数:

  • sync_relay_log = 1
  • sync_relay_log_info = 1
  • sync_master_info = 1

[不推荐].跳过或只同步某个db的事务

  • replicate-ignore-table = guild.mirdb_bak #从库：忽略特定表的复制

[推荐].跳过或只同步某个db的事务

  • replicate_wild_do_table = testdb.%
  • replicate_wild_ignore_table = mysql.%

开启基于库的多线程复制的5个配置(默认是0,不开启,最大并发数为1024个线程).

  • slave_parallel_workers = 8
  • slave_parallel_type = LOGICAL_CLOCK
  • master_info_repository = TABLE
  • relay_log_info_repository = TABLE
  • relay_log_recovery = 1

ppd内部使用的报表服务器：

  • report-host = 10.114.8.30
  • report-port = 3403

实测发现 5.7.13 不支持如下参数：

  • rpl_semi_sync_master_enabled = 1
  • rpl_semi_sync_slave_enabled = 1
  • rpl_semi_sync_master_timeout = 1000
  • rpl_semi_sync_master_wait_no_slave = 1

Error log

  • log-error = /data/mysql_3306/error/mysql_run.err

General log

  • general_log_file = /data/mysql_3306/tmp/mysql_general.log

Slow log

  • slow_query_log = 1 #是否启用慢查询日志，默认0不启用
  • long_query_time = 3 #慢查询阀值时间，单位为秒，默认超过10s的查询才写日志
  • log_output = file #默认为FILE表示将日志存入文件,改为TABLE则会将日志写入mysql.slow_log表
  • slow_query_log_file = /data/mysql_3306/slow/mysql_slow.log #开启慢查询日志， 记录查询过长的语句以便优化
  • log_queries_not_using_indexes = 1 #是否记录没有使用索引的查询，而不考虑执行时间的长短，默认0不记录
  • log_throttle_queries_not_using_indexes = 10 #限制每分钟记录的不使用索引的查询数,超过这个数目后只记录语句数量和花费的总时间,只有log_queries_not_using_indexes=1才有效
  • log_slow_admin_statements = 1 #是否记录执行缓慢的管理SQL(即OPTIMIZE、ANALYZE、ALTER和其他管理命令语句)，默认0不记录
  • log_slow_slave_statements = 1 #是否记录从库上执行的慢查询语句，默认0不记录
  • min_examined_row_limit = 100 #扫描行数小于该值的查询则不记入慢查询日志，默认0

Per_thread_buffer

  • max_connections = 3000 #.所有用户同时打开的最大连接数,默认151
  • max_user_connections = 2000 #.每个用户同时打开的最大连接数
  • max_connect_errors = 10000 #.针对某个IP连接中断与mysql服务连接的次数
  • max_allowed_packet = 512M #.服务器发送和接受的最大包长度
  • slave_max_allowed_packet = 1024M
  • slave_pending_jobs_size_max = 600M
  • table_open_cache = 20000
  • table_definition_cache = 20000
  • sort_buffer_size = 2M
  • read_buffer_size = 2M
  • join_buffer_size = 2M
  • tmp_table_size = 64M
  • max_heap_table_size = 64M
  • query_cache_type = 0
  • query_cache_size = 0
  • bulk_insert_buffer_size = 32M #限制每个进程中缓冲池大小，不要大于key_buffer_size
  • thread_cache_size = 400
  • thread_stack = 256K
  • read_rnd_buffer_size = 2M
  • thread_handling = pool-of-threads

当Too many connections时，dba可以通过这个管理端口来登录(percona5.5及以后才支持)：

  • extra_max_connections = 10
  • extra_port = 13306

MyISAM

  • key_buffer_size = 64M #索引块的缓冲区大小，只针对mysiam

InnoDB

  • innodb_data_home_dir = /data/mysql_3306/iblog #表空间的保存目录. 默认datadir
  • innodb_log_group_home_dir = /data/mysql_3306/iblog
  • innodb_data_file_path = ibdata1:1G:autoextend
  • innodb_temp_data_file_path = ibtmp1:100M:autoextend
  • innodb_buffer_pool_size = 9837936K #保存索引和原始数据的缓冲池大小，建议为物理内存的50-60%，默认8M，仅对innodb生效
  • innodb_buffer_pool_instances = 8
  • innodb_log_file_size = 512M #日志文件的大小，更大的设置可以提高性能，但也会增加故障恢复所需的时间
  • innodb_log_files_in_group = 3 #为提高性能，MySQL可以以循环方式将日志文件写到多个文件。默认2，推荐3
  • innodb_log_buffer_size = 16M #日志文件所用的内存大小，更大设置可以提高性能，但意外故障将会丢失数据
  • innodb_flush_log_at_trx_commit = 1 #log buffer刷新到磁盘的方式，默认1最安全、0性能最好、其他值则折衷
  • innodb_lock_wait_timeout = 60 #InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。默认值是50秒
  • innodb_read_io_threads = 8
  • innodb_write_io_threads = 8
  • innodb_page_cleaners = 8
  • innodb_max_dirty_pages_pct = 75
  • innodb_io_capacity = 2000
  • innodb_flush_method = O_DIRECT
  • innodb_file_per_table = 1 #设置innodb引擎独立表空间，建议启用，否则所有数据库InnoDB引擎的表数据将存储在一个共享空间（ibdata1）中
  • transaction_isolation = READ-COMMITTED #设定默认的事务隔离级别
  • innodb_strict_mode = 1
  • innodb_open_files = 400000
  • innodb_buffer_pool_dump_at_shutdown = ON
  • innodb_buffer_pool_load_at_startup = ON
  • innodb_print_all_deadlocks = 1
  • innodb_sort_buffer_size = 64M
  • innodb_undo_directory = /data/mysql_3306/iblog
  • innodb_max_undo_log_size = 2G
  • innodb_undo_log_truncate = 1
  • innodb_undo_tablespaces = 3
  • innodb_rollback_segments = 128
  • innodb_purge_threads = 8
  • innodb_purge_batch_size = 300
  • innodb_file_io_threads = 4 #指定InnoDB表可用的文件I／O线程数，建议在非Windows平台中设为4
  • innodb_adaptive_hash_index = 0 #建议禁用自适应哈希索引，避免高并发导致的mysql服务重启

tokudb

  • tokudb_cache_size = 9837936K
  • tokudb_directio = on
  • tokudb_data_dir = /data/mysql_3306/var
  • tokudb_log_dir = /data/mysql_3306/iblog
  • tokudb_tmp_dir = /data/mysql_3306/tmp
  • tokudb_commit_sync = OFF
  • tokudb_fsync_log_period = 1000
  • innodb_use_native_aio = off

audit

  • audit_log_policy = ALL
  • audit_log_format = JSON
  • audit_log_file = /data/mysql_3306/tmp/audit.log
  • audit_log_rotate_on_size = 1024M
  • audit_log_rotations = 50

performance-schema

  • performance-schema-instrument = 'memory/%=ON'
  • performance-schema-instrument = 'wait/lock/metadata/sql/mdl=ON'
  • performance-schema-instrument = 'statement/%=ON'
  • performance-schema-consumer-events-statements-current = ON
  • performance-schema-consumer-events-statements-history = ON
  • performance-schema-consumer-events-statements-history-long = ON
  • performance-schema-consumer-statements-digest = ON
  • performance_schema_digests_size = 40000
  • performance_schema_max_table_instances = 40000
  • performance_schema_max_sql_text_length = 4096
  • performance_schema_max_digest_length = 4096

Security

  • local-infile = 0 #.设置为0可禁用客户端load data local infile命令
  • skip_symbolic_links = yes #.禁用符号链接以防止各种安全风险

[mysql]

  • auto-rehash
  • pager = more
  • show-warnings
  • default-character-set = utf8mb4
  • skip-name-resolve #禁止MySQL对外部连接进行DNS解析,而只能用ip地址

[mysqldump]

  • quick
  • single-transaction
  • max_allowed_packet = 1G #.服务器发送和接受的最大包长度
  • default-character-set = utf8mb4

[myisamchk]

  • key_buffer_size = 64M
  • sort_buffer_size = 2M
  • read_buffer = 2M
  • write_buffer = 2M

[mysqld_safe]

  • open_files_limit = 400000

附录

Copyright © www.sqlfans.cn 2023 All Right Reserved更新时间： 2023-12-13 17:59:02

results matching ""

    No results matching ""