--->配置文件样例
配置参数解释：
[client]
#设置字符集
default-character-set = utf8mb4
#设置端口号
port = 3306
#设置socket连接文件位置
socket = /home/mysql/data/mysqldata1/sock/mysql.sock
[mysqldump]
#命令行的 --quick 选项，查询取得记录直接输出，导出大表时会用到
quick
#传输数据包时允许的大小
max_allowed_packet = 2G
#设置字符集
default-character-set = utf8mb4
[mysql]
#不进行自动补全
no-auto-rehash
#显示错误信息
show-warnings
#配置提示信息格式
prompt = "\\u@\\h : \\d \\r:\\m:\\s> "
#设置字符集
default-character-set = utf8mb4
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
#服务器关闭交互式连接前等待的秒数
interactive-timeout
[mysqld_safe]
user = mysql
open-files-limit = 65535
[mysqld]
#支持大内存分页
#large-pages
#*** common parameters ***
#设置默认存储引擎
default-storage-engine = INNODB
#设置服务端字符集
character-set-server = utf8mb4
#跳过mysql程序起动时的字符参数设置
character-set-client-handshake = FALSE
#设置服务器排序规则
collation_server = utf8mb4_unicode_ci
#设置记录日志中显示的时间
log_timestamps = SYSTEM
#运行用户
user = mysql
#设置端口号
port = 3306
#设置socket连接文件位置
socket = /home/mysql/data/mysqldata1/sock/mysql.sock
#设置pid运行文件位置
pid-file = /home/mysql/data/mysqldata1/sock/mysql.pid
#设置数据文件存储目录位置
datadir = /home/mysql/data/mysqldata1/mydata
#设置临时文件目录位置
tmpdir = /home/mysql/data/mysqldata1/tmpdir
#是否支持符号链接，即数据库或表可以存储在my.cnf中指定datadir之外的分区或目录，0 禁用，1 开启
symbolic-links=0
#禁用DNS解析
skip-name-resolve
#跳过外部锁定
skip_external_locking
#设置大小写敏感，0是区分，1是存储小写比较不区分，2是存储给定比较为小写
lower_case_table_names = 1
#设置事件调度器，0关闭，1开启
event_scheduler = 0
#设置MySQL暂时不能应答新请求时在堆栈中临时存储的请求数量
back_log = 512
#设置默认时区
default-time-zone = '+8:00'
#设置最大连接数
max_connections = 3000
#设置连接失败允许次数
max_connect_errors = 99999
#传输数据包的过程中最大允许的数据包大小
max_allowed_packet = 64M
#该参数在多线程复制中起作用，在队列中等待的事件所能占用的最大内存量
slave_pending_jobs_size_max = 128M
#设置用户创建的MEMORY引擎表允许增长的最大大小
max_heap_table_size = 8M
#所有返回的列的总长度大于该值，则只加载排序列到内存，小于该值，则加载所有列到内存
max_length_for_sort_data = 16K
#设置非交互连接超时时间
wait_timeout = 172800
#设置交互连接超时时间
interactive_timeout = 172800
#连接缓冲区大小
net_buffer_length = 8k
#对表顺序扫描的读入缓冲区大小
read_buffer_size = 2M
#随机读缓冲区大小
read_rnd_buffer_size = 2M
#排序缓冲区大小
sort_buffer_size = 2M
#联表查询的缓冲区大小
join_buffer_size = 4M
#每个session分配的事务过程中用来存储二进制日志的缓存大小
binlog_cache_size = 2M
#内存中允许打开表的数量
table_open_cache = 4096
#打开的表的实例数量
table_open_cache_instances = 2
#数据库内部控制总frm文件的数量
table_definition_cache = 4096
#缓存空闲进程的大小
thread_cache_size = 512
#临时表的内存缓存大小
tmp_table_size = 8M
# QC parameters are removed in version 8.0.3
#查询缓存大小
#query_cache_size = 0
#查询缓存设置开关
#query_cache_type = OFF
#*** log related settings ***
#错误日志的存储路径位置
log-error = /home/mysql/data/mysqldata1/log/error.log
#慢SQL的时间限制
long_query_time = 1
#慢日志开关
slow_query_log
#慢日志的存储路径位置
slow_query_log_file = /home/mysql/data/mysqldata1/slowlog/slow-query.log
#从库的binlog日志格式若是statement，执行时间超过设定值，将写入从库的慢查询日志中
log_slow_slave_statements
#记录没有使用索引的SQL
#log_queries_not_using_indexes
#*** replication related settings ***
##for master
#架构集群中主机的唯一ID
server-id = 330607
#二进制日志的存储路径位置
log-bin = /home/mysql/data/mysqldata1/binlog/mysql-bin
#二进制日志格式，格式有STATEMENT(段)，ROW(行)，MIXED(混合)
binlog-format = ROW
#二进制日志中event的校验值
binlog-checksum = CRC32
#二进制日志为ROW模式时，可以记录查看到完整SQL语句
binlog-rows-query-log-events = 1
#二进制日志存储文件大小限制
max_binlog_size = 512M
#二进制日志的保留时间，以天为单位
expire_logs_days = 15
#设置事物执行刷新到盘操作的数量(是事物不是事务)，0表示由操作系统来决定，1表示每事物，大于1则代表具体数值
sync-master-info = 1
#二进制日志同步到磁盘的方式，0不做限制系统控制刷新，>0则是每多少个事务进行刷新
sync_binlog = 1
#主服务器校验，读取二进制日志时是否进行校验
master-verify-checksum = 1
#设置从机如何记录主机状态，file是文件形式，table是表形式
master-info-repository = TABLE
#列值自增量的步长
auto_increment_increment = 2
#自增量初始值设定
auto_increment_offset = 2
## multi thread replication, if crash ,will use this parameter
#从库宕机崩溃，relay log损坏，造成主从不一致的情况下，会重新生成relay log并回退sql的position与io线程一致，重新同步
#relay_log_recovery = 1
# 如果slave存在relay log的gaps，报错
# ERROR 1872(HY000):Slave failed to initialize relay log info structure from the repository的错误，
# 可以使用sync_relay_log = 1来尽量避免。如果不能设置这个参数，需要使用如下操作：
# stop slave; change master to master_auto_position = 1; start slave;
# sync_relay_log = 1
## for slave
#设置relay log的存储路径位置
relay-log = /home/mysql/data/mysqldata1/relaylog/mysql-relay-bin
#设置从机同步位置信息的记录方式，feil是文件，table是表
relay-log-info-repository = TABLE
#当slave从库宕机后，假如relay-log损坏了导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log并且重新从master上获取日志，为了保证relay-log的完整性
relay-log-recovery = 1
#从库复制时可以跳过的错误编号，否则遇到错误会中断复制同步
# slave-skip-errors = 1022,1032,1062,1236
#设置主从复制线程数量
slave-parallel-workers = 4
#设置从库在relay log读取信息时是否校验
slave-sql-verify-checksum = 1
#开启二进制日志，设置是否新人存储函数创建者，0限制非超级权限，1不限制
log_bin_trust_function_creators = 1
#设置二进制日志索引
log-bin-index=mysql-bin.index
#设置从机将其SQL线程执行的更新记录到其自己的二进制日志中
log_slave_updates = 1
#设置从机与主机之间心跳连接的超时时间
slave-net-timeout = 10
#*** MyISAM Specific options ***
#设置索引块缓冲区大小
key_buffer_size = 8M
#设置批量写入数据时临时缓冲区的大小
bulk_insert_buffer_size = 8M
#设置修复索引重建的并行线程数量
myisam_repair_threads = 1
#设置MyISAM存储引擎的恢复模式（OFF、DEFAULT、BACKUP、FORCE、QUICK，多个以逗号分隔）
myisam_recover_options = force
#*** INNODB Specific options ***
#设置innodb共享表空间文件的存储路径位置
innodb_data_home_dir = /home/mysql/data/mysqldata1/innodb_ts
#指定innodb共享表空间的文件（名称:大小:自动扩展）
innodb_data_file_path = ibdata1:2048M:autoextend
#每个innodb引擎的表均使用独立表空间
innodb_file_per_table = 1
#设置innodb层同时打开的文件数量
innodb_open_files = 1024
#设置文件描述符的缓存大小
table_open_cache= = 1024
#以下三个参数在8.0中被移除，默认使用barracuda
#innodb文件格式
#innodb_file_format = barracuda
#innodb文件支持最大的表格式
#innodb_file_format_max = barracuda
#是否开启innodb文件格式检查
#innodb_file_format_check = ON
#是否开启 innodb 严格检查模式
innodb_strict_mode = 1
#控制 innodb 数据文件和 redo log 的打开、刷写模式（fdatasync，O_DSYNC，O_DIRECT）
innodb_flush_method = O_DIRECT
#设置 innodb 中 checksum 的算法
innodb_checksum_algorithm = crc32
#设置向带有 auto_increment 的列写入数据时锁的行为（0 tradition 传统，1 consecutive 连续，2 interleaved 交错）
innodb_autoinc_lock_mode = 2
#Buffer pool options
#设置缓冲池大小
innodb_buffer_pool_size = 2G
#设置保存数据字典信息和其他内部数据结构的内存池的大小
#innodb_additional_mem_pool_size = 16M
#设置缓冲池实例数量
innodb_buffer_pool_instances = 4
#当系统中脏页数量高于该值（百分比），则开始刷新数据到磁盘
innodb_max_dirty_pages_pct = 75
#设置自适应刷新，影响每秒刷新到磁盘的脏页数量
innodb_adaptive_flushing = ON
#是否刷新邻接页
innodb_flush_neighbors = 0
#设置LRU的空闲页数量
innodb_lru_scan_depth = 4096
#设置索引列 merge 对象模式（none，inserts，deletes，changes，purges，all）
innodb_change_buffering = all
#设置页读取到mid位置后，需要等待多久才会被加入到LRU列表的热端（毫秒为单位）
innodb_old_blocks_time = 1000
#控制MySQL数据库关闭时是否保存缓冲池内容，可以缩短数据库启动预热过程，与 innodb_buffer_pool_load_at_startup 一同设置
innodb_buffer_pool_dump_at_shutdown = ON
#控制MySQL数据库启动时是否加载之前保存的缓冲池内容自动预热，与 innodb_buffer_pool_dump_at_shutdown 一同设置
innodb_buffer_pool_load_at_startup = ON
#redo options
#设置 redo log 日志组的存储路径位置
innodb_log_group_home_dir = /home/mysql/data/mysqldata1/innodb_log
#设置日志缓冲区的大小
innodb_log_buffer_size = 128M
#设置日志文件的大小
innodb_log_file_size = 2G
#设置日志组成员数量
innodb_log_files_in_group = 2
#设置日志数据刷新到磁盘的策略（0 每秒写入文件并写入磁盘，1 每事务写入文件并写入磁盘，2 每事务写入文件并每秒写入磁盘）
innodb_flush_log_at_trx_commit = 1
#设置MySQL数据库关闭时对 full purge 和 merge insert buffer 的操作（0 完成所有，1 无需完成但缓冲区脏页需刷新到磁盘，2 只完成写入日志文件其它不需要）
innodb_fast_shutdown = 1
#MySQL 8.0 被移除，内部默认开启XA
#innodb_support_xa = ON
##transaction options
#设置 innodb 的线程数量
innodb_thread_concurrency = 64
#设置锁等待时间，超时则报错
innodb_lock_wait_timeout = 120
#设置事务超时时间，超时则中止并回滚
innodb_rollback_on_timeout = 1
#设置事务隔离级别（读未提交 READ-UNCOMMITTED，读已提交 READ-COMMITTED，可重复读 REPEATABLE-READ，序列化、可串行化 SERIALIZABLE）
transaction_isolation = READ-COMMITTED
##IO options
#是否开启performance_schema系统库，用于收集数据库服务器性能参数
performance_schema = ON
#innodb引擎读线程数量
innodb_read_io_threads = 8
#innodb引擎写线程数量
innodb_write_io_threads = 16
#IO吞吐量，设置缓冲池刷新脏页的数量
innodb_io_capacity = 20000
#是否启用AIO
innodb_use_native_aio = 1
##undo options
#设置undo日志的存储路径位置
innodb_undo_directory = /home/mysql/data/mysqldata1/undo/
#设置undo表空间个数
innodb_undo_tablespaces = 4
#设置是否开启undo日志在线回收
innodb_undo_log_truncate = ON
#设置undo日志的回收进程数量
innodb_purge_threads = 4
#刷新时回收undo日志的数量大小
innodb_purge_batch_size = 512
#这个值表示innodb开始延迟后面的语句更新数据前，可以等待被清除的最大的事务数量
innodb_max_purge_lag = 65536
#GTID
#是否启用gtid模式
gtid-mode = on
#是否只允许能够保障GTID安全，并且能够被日志记录的SQL语句被执行，开启后不允许在事务中使用多个语句
enforce-gtid-consistency = true
#控制器优化开关
#optimizer_switch='mrr=on, mrr_cost_based=off, batched_key_access=on'
#限制super拥护的写权限，为只读模式，保证从库不误写
#super_read_only = on
#设置服务端对timestamp列中的默认值和NULL值的不同处理方法
explicit_defaults_for_timestamp = ON
#设置导入导出限制
secure_file_priv = null
#设置并发复制方式
slave_parallel_type =LOGICAL_CLOCK
#设置刷新脏数据的线程的数量
innodb_page_cleaners = 4
MySQL配置文件样例
生产环境配置样例
[mysqld] datadir=/data/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid character_set_server=utf8mb4 character-set-client-handshake = FALSE collation-server = utf8mb4_unicode_ci log_timestamps=SYSTEM skip_name_resolve = 1 # mysql性能优化 #max_heap_table_size=200M tmp_table_size=200M max_connections=2500 max_connect_errors = 1000 innodb_buffer_pool_size=8G #innodb_additional_mem_pool_size=16M innodb_max_dirty_pages_pct=90 # 修改 InnoDB 为独立表空间模式，每个数据库的每个表都会生成一个数据空间 innodb_file_per_table=1 innodb_open_files=1024 table_open_cache=1024 # InnoDB 的日志相关的优化选项 innodb_log_buffer_size=16M innodb_log_file_size =256M innodb_log_files_in_group=8 innodb_autoextend_increment=128 innodb_flush_log_at_trx_commit=2 # 建议用 GTID 的并行复制，以下是需要主从复制的情况下，相关的设置参数。 gtid_mode = ON binlog_format = mixed log-bin=mysql-bin log-bin-index=mysql-bin.index log-slave-updates=true slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=16 master_info_repository=TABLE relay_log_info_repository=TABLE relay_log_recovery=ON # 其他配置 transaction_isolation = READ-COMMITTED interactive_timeout = 1800 wait_timeout = 1800 expire_logs_days = 5 sync-master-info = 1 slave-parallel-workers = 2 binlog-checksum = CRC32 master-verify-checksum = 1 slave-sql-verify-checksum = 1 binlog-rows-query-log_events = 1 server-id = 1 lower_case_table_names=1 [client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4
/etc/my.cnf 适用于8.0.20
[client] default-character-set = utf8mb4 port = 3306 socket = /home/mysql/data/mysqldata1/sock/mysql.sock [mysqldump] quick max_allowed_packet = 2G default-character-set = utf8mb4 [mysql] no-auto-rehash show-warnings prompt = "\\u@\\h : \\d \\r:\\m:\\s> " default-character-set = utf8mb4 [myisamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] user = mysql open-files-limit = 65535 [mysqld] #large-pages #*** common parameters *** default-storage-engine = INNODB character-set-server = utf8 collation_server = utf8_bin log_timestamps = SYSTEM user = mysql port = 3306 socket = /home/mysql/data/mysqldata1/sock/mysql.sock pid-file = /home/mysql/data/mysqldata1/sock/mysql.pid datadir = /home/mysql/data/mysqldata1/mydata tmpdir = /home/mysql/data/mysqldata1/tmpdir skip-name-resolve skip_external_locking lower_case_table_names = 1 event_scheduler = 0 back_log = 512 default-time-zone = '+8:00' max_connections = 3000 max_connect_errors = 99999 max_allowed_packet = 64M slave_pending_jobs_size_max = 128M max_heap_table_size = 8M max_length_for_sort_data = 16K wait_timeout = 172800 interactive_timeout = 172800 net_buffer_length = 8k read_buffer_size = 2M read_rnd_buffer_size = 2M sort_buffer_size = 2M join_buffer_size = 4M binlog_cache_size = 2M table_open_cache = 4096 table_open_cache_instances = 2 table_definition_cache = 4096 thread_cache_size = 512 tmp_table_size = 8M # QC parameters are removed in version 8.0.3 #query_cache_size = 0 #query_cache_type = OFF #*** log related settings *** log-error = /home/mysql/data/mysqldata1/log/error.log long_query_time = 1 slow_query_log slow_query_log_file = /home/mysql/data/mysqldata1/slowlog/slow-query.log log_slow_slave_statements #log_queries_not_using_indexes #*** replication related settings *** ##for master server-id = 330607 log-bin = /home/mysql/data/mysqldata1/binlog/mysql-bin binlog-format = ROW binlog-checksum = CRC32 binlog-rows-query-log-events = 1 max_binlog_size = 512M expire_logs_days = 15 sync_binlog = 1 master-verify-checksum = 1 master-info-repository = TABLE auto_increment_increment = 2 auto_increment_offset = 2 ## multi thread replication, if crash ,will use this parameter relay_log_recovery = 1 # 如果slave存在relay log的gaps，报错 # ERROR 1872(HY000):Slave failed to initialize relay log info structure from the repository的错误， # 可以使用sync_relay_log = 1来尽量避免。如果不能设置这个参数，需要使用如下操作： # stop slave; change master to master_auto_position = 1; start slave; # sync_relay_log = 1 ## for slave relay-log = /home/mysql/data/mysqldata1/relaylog/mysql-relay-bin relay-log-info-repository = TABLE relay-log-recovery = 1 # slave-skip-errors = 1022,1032,1062,1236 slave-parallel-workers = 4 slave-sql-verify-checksum = 1 log_bin_trust_function_creators = 1 log_slave_updates = 1 slave-net-timeout = 10 #*** MyISAM Specific options *** key_buffer_size = 8M bulk_insert_buffer_size = 8M myisam_repair_threads = 1 myisam_recover_options = force #*** INNODB Specific options *** innodb_data_home_dir = /home/mysql/data/mysqldata1/innodb_ts innodb_data_file_path = ibdata1:2048M:autoextend innodb_file_per_table #以下三个参数在8.0中被移除，默认使用barracuda #innodb_file_format = barracuda #innodb_file_format_max = barracuda #innodb_file_format_check = ON innodb_strict_mode = 1 innodb_flush_method = O_DIRECT innodb_checksum_algorithm = crc32 innodb_autoinc_lock_mode = 2 #Buffer pool options innodb_buffer_pool_size = 2G innodb_buffer_pool_instances = 4 innodb_max_dirty_pages_pct = 75 innodb_adaptive_flushing = ON innodb_flush_neighbors = 0 innodb_lru_scan_depth = 4096 innodb_change_buffering = all innodb_old_blocks_time = 1000 innodb_buffer_pool_dump_at_shutdown = ON innodb_buffer_pool_load_at_startup = ON # MySQL8.0中被移除 # innodb_adaptive_hash_index_partitions = 32 #redo options innodb_log_group_home_dir = /home/mysql/data/mysqldata1/innodb_log innodb_log_buffer_size = 128M innodb_log_file_size = 2G innodb_log_files_in_group = 2 innodb_flush_log_at_trx_commit = 1 innodb_fast_shutdown = 1 #MySQL 8.0 被移除，内部默认开启XA #innodb_support_xa = ON ##transaction options innodb_thread_concurrency = 64 innodb_lock_wait_timeout = 120 innodb_rollback_on_timeout = 1 transaction_isolation = READ-COMMITTED ##IO options performance_schema = ON innodb_read_io_threads = 8 innodb_write_io_threads = 16 innodb_io_capacity = 20000 innodb_use_native_aio = 1 ##undo options innodb_undo_directory = /home/mysql/data/mysqldata1/undo/ innodb_undo_tablespaces = 4 innodb_undo_log_truncate = ON innodb_purge_threads = 4 innodb_purge_batch_size = 512 innodb_max_purge_lag = 65536 #GTID gtid-mode = on enforce-gtid-consistency = true #optimizer_switch='mrr=on, mrr_cost_based=off, batched_key_access=on' #super_read_only = on explicit_defaults_for_timestamp = ON secure_file_priv = null slave_parallel_type =LOGICAL_CLOCK innodb_page_cleaners = 4 #others #disable-partition-engine-check #skip-grant-tables
/etc/my.cnf 适用于5.7
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [client] port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] # # 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 leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # 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 server-id = 1 skip-name-resolve datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock #连接数 back_log = 600 max_connections=1500 max_connect_errors = 6000 open_files_limit = 65535 #缓存 query_cache_limit = 2M query_cache_size = 32M read_buffer_size = 2M read_rnd_buffer_size = 8M key_buffer_size = 256M table_open_cache = 128 thread_cache_size = 16 #日志 #slow_query_log = 1 #long_query_time = 3 #slow_query_log_file=mysql-slow log-bin = mysql-bin max_binlog_cache_size =2M max_binlog_size = 64M expire_logs_days = 7 #字符集 character-set-server = utf8 collation-server = utf8_general_ci lower_case_table_names = 1 #InnoDB innodb_print_all_deadlocks = 1 innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_thread_concurrency = 0 innodb_buffer_pool_instances = 2 innodb_buffer_pool_size = 256M #innodb_buffer_pool_size = 4G innodb_max_dirty_pages_pct = 90 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Recommended in standard MySQL setup sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysqldump] max_allowed_packet = 4M [myisamchk] key_buffer_size = 8M sort_buffer_size = 8M read_buffer = 4M write_buffer = 4M [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
