linux中的my.cnf,文件位置在/etc/my.cnf
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html [mysqld] #skip-grant-tables # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove the leading "# " to disable binary logging # Binary logging captures changes between backups and is enabled by # default. It's default setting is log_bin=binlog # disable_log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # # Remove leading # to revert to previous value for default_authentication_plugin, # this will increase compatibility with older clients. For background, see: # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin # default-authentication-plugin=mysql_native_password #skip-grant-tables #innodb_force_recovery=6 port=3815 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin=/var/lib/mysql/binlog server-id = 10 #跳过DNS解析 skip-name-resolve = 1 #binlog-do-db=ambient_monitor #binlog-do-db=anbao_cabinet #binlog-do-db=customer_center #binlog-do-db=information_schema #binlog-do-db=monitor #binlog-do-db=mysql #binlog-do-db=performance_schema #binlog-do-db=sys log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid character-set-server=utf8 local-infile=0 skip_symbolic_links=yes #开启sql日志 general_log=1 general_log_file=/var/log/general_sql.log #连接数配置 #设置并发数 innodb_thread_concurrency=128 #连接错误数量 max_connect_errors = 30000 #客户端并发数量 max_connections = 2000 max_user_connections = 2000 #mysql函数参数默认值修改 group_concat_max_len=102400 #max_open_files: 11000 #table_open_cache: 4200 #官方建议 back_log = 50 + (max_connections / 5),封顶数为65535,默认值= max_connections #back_log = 450 #并发情况下打开缓存表 #table_open_cache = 600 #最小缓存表 #table_definition_cache = 700 #为了减少会话之间的争用,可以将opentables缓存划分为table_open_cache/table_open_cache_instances个小缓存 #table_open_cache_instances = 64 #每个线程的堆栈大小 如果线程堆栈太小,则会限制执行复杂SQL语句 #thread_stack = 1M #order by 或group by 时用到 #sort_buffer_size = 4M #inner left right join时用到 #join_buffer_size = 4M #缓存启用的线程数 #thread_cache_size = 60 #MySQL连接闲置超过一定时间后(单位:秒)将会被强行关闭 #MySQL默认的wait_timeout 值为8个小时, interactive_timeout参数需要同时配置才能生效 #interactive_timeout = 1800 #wait_timeout = 180 #Metadata Lock最大时长(秒), 一般用于控制 alter操作的最大时长sine mysql5.6 #执行 DML操作时除了增加innodb事务锁外还增加Metadata Lock,其他alter(DDL)session将阻塞 #lock_wait_timeout = 3600 #内存内部临时表 #tmp_table_size = 32M #max_heap_table_size = 32M #MyISAM 设置 #对MyISAM表起作用,但是内部的临时磁盘表是MyISAM表,也要使用该值。 ##可以使用检查状态值 created_tmp_disk_tables 得知详情 #key_buffer_size = 15M #read_buffer_size = 8M #read_rnd_buffer_size = 4M #bulk_insert_buffer_size = 64M #INNODB 设置 #innodb最大线程数 0=无限制 #innodb_thread_concurrency = 0 #一般设置物理存储的 60% ~ 70% #innodb_buffer_pool_size = 8G #当缓冲池大小大于1GB时,将innodb_buffer_pool_instances设置为大于1的值,可以提高繁忙服务器的可伸缩性 #innodb_buffer_pool_instances = 4 #默认启用。指定在MySQL服务器启动时,InnoDB缓冲池通过加载之前保存的相同页面自动预热。 通常与innodb_buffer_pool_dump_at_shutdown结合使用 #innodb_buffer_pool_load_at_startup = 1 #innodb_buffer_pool_dump_at_shutdown = 1 #InnoDB用于写入磁盘日志文件的缓冲区大小(以字节为单位)。默认值为16MB #innodb_log_buffer_size = 32M #InnoDB日志文件组中每一个文件的大小 #innodb_log_file_size = 2G #是否开启在线回收(收缩)undo log日志文件,支持动态设置,默认开启 #innodb_undo_log_truncate = 1 #当超过这个阀值(默认是1G),会触发truncate回收(收缩)动作,truncate后空间缩小到10M #innodb_max_undo_log_size = 4G #innodb_flush_sync = 0 #innodb_flush_neighbors = 0 #CPU多核处理能力设置 #innodb_write_io_threads = 8 #innodb_read_io_threads = 8 #innodb_purge_threads = 4 #innodb_page_cleaners = 4 #innodb_open_files = 65535 #innodb_max_dirty_pages_pct = 50 #innodb_flush_method = O_DIRECT #innodb_lru_scan_depth = 4000 #innodb_checksum_algorithm = crc32 #在创建InnoDB索引时用于指定对数据排序的排序缓冲区的大小 #innodb_sort_buffer_size = 67108864 #表示每个表都有自已独立的表空间 #innodb_file_per_table = 1 #查询结果分配的内存 #query_cache_size = 256M #缓存所有查询语音 除了 SELECT SQL_NO_CACHE #query_cache_type = 1 #[mysqldump] #quick #sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION