# vim /etc/my.cnf
[mysql]
port = 3306
socket = /data/mysql/mysql.sock
prompt = "\u@mysql \R:\m:\s [\d]> "
no_auto_rehash
default_character_set = utf8mb4
[mysqld]
# === Required Settings ===
server_id = 6
basedir = /usr/local/mysql
datadir = /data/mysql
max_allowed_packet = 1G
max_connect_errors = 1000000
pid_file = /data/mysql/mysqld.pid
port = 3306
skip_external_locking
skip_name_resolve = 1
default_time_zone = "+8:00"
socket = /data/mysql/mysql.sock
tmpdir = /data/mysql/tmp
user = mysql
secure_file_priv = /data/mysql/tmp
symbolic-links = 0
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4'
lower_case_table_names = 1
max_heap_table_size = 128M
# === SQL Compatibility Mode ===
# Enable for b/c with databases created in older MySQL/MariaDB versions
# (e.g. when using null dates)
sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# === InnoDB Settings ===
default_storage_engine = InnoDB
innodb_buffer_pool_instances = 4 # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size = 1638M # Use up to 70-80% of RAM
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 32M
innodb_log_file_size = 1G
innodb_stats_on_metadata = 0
innodb_lock_wait_timeout = 50
innodb_page_size = 16k
innodb_lru_scan_depth = 4096
innodb_flush_neighbors = 1
innodb_purge_threads = 4
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_stats_persistent_sample_pages = 500
innodb_autoinc_lock_mode = 2
innodb_online_alter_log_max_size= 4G
innodb_open_files = 4096
#innodb_temp_data_file_path = ibtmp1:64M:autoextend:max:20G # Control the maximum size for the ibtmp1 file
#innodb_thread_concurrency = 4 # Optional: Set to the number of CPUs on your system (minus 1 or 2) to better
# contain CPU usage. E.g. if your system has 8 CPUs, try 6 or 7 and check
# the overall load produced by MySQL/MariaDB.
innodb_read_io_threads = 1
innodb_write_io_threads = 1
#innodb_io_capacity = 1000 # Max is 2000
# === MyISAM Settings ===
# The following 3 options are ONLY supported by MariaDB & up to MySQL 5.7
# Do NOT un-comment on MySQL 8.x+
#query_cache_limit = 4M # UPD
#query_cache_size = 64M # UPD
#query_cache_type = 1 # Enabled by default
key_buffer_size = 32M # UPD
low_priority_updates = 1
concurrent_insert = 2
# === Connection Settings ===
max_connections = 100 # UPD - Important: high no. of connections = high RAM consumption
back_log = 512
thread_cache_size = 48
thread_stack = 512K
interactive_timeout = 7200
wait_timeout = 3600
# For MySQL 5.7+ only (disabled by default)
max_execution_time = 30000 # Set a timeout limit for SELECT statements (value in milliseconds).
# This option may be useful to address aggressive crawling on large sites,
# but it can also cause issues (e.g. with backups). So use with extreme caution and test!
# More info at: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time
# === Buffer Settings ===
innodb_sort_buffer_size = 6710864 # UPD
join_buffer_size = 16M # UPD
read_buffer_size = 8M # UPD
read_rnd_buffer_size = 16M # UPD
sort_buffer_size = 16M # UPD
# === Table Settings ===
# In systemd managed systems like Ubuntu 16.04+ or CentOS 7+, you need to perform an extra action for table_open_cache & open_files_limit
# to be overriden (also see comment next to open_files_limit).
# E.g. for MySQL 5.7, please check: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
# and for MariaDB check: https://mariadb.com/kb/en/library/systemd/
table_definition_cache = 40000 # UPD
table_open_cache = 40000 # UPD
open_files_limit = 65535 # UPD - This can be 2x to 3x the table_open_cache value or match the system's
# open files limit usually set in /etc/sysctl.conf or /etc/security/limits.conf
# In systemd managed systems this limit must also be set in:
# /etc/systemd/system/mysqld.service.d/override.conf (for MySQL 5.7+) and
# /etc/systemd/system/mariadb.service.d/override.conf (for MariaDB)
max_heap_table_size = 128M
tmp_table_size = 128M
# === Search Settings ===
ft_min_word_len = 3 # Minimum length of words to be indexed for search results
# === Logging ===
log_timestamps = SYSTEM
log_error = /data/mysql/logs/error.log
#log_queries_not_using_indexes = 1
long_query_time = 5
slow_query_log = 0 # Disabled for production
slow_query_log_file = /data/mysql/slow.log
min_examined_row_limit = 100
log_bin = /data/mysql/logs/binlog.log
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
expire_logs_days = 7
innodb_log_files_in_group = 3
relay_log = /data/mysql/logs/relay.log
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = ON
enforce_gtid_consistency = TRUE
log-slave-updates = 1
relay_log_recovery = 1
relay_log_purge = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
binlog_format = row
log_slave_updates = ON
slave_skip_errors = ddl_exist_errors
plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN'
validate_password_policy = 1
validate_password_length = 16
validate_password_number_count = 1
validate_password_mixed_case_count = 1
validate_password_special_char_count = 1
validate-password = FORCE_PLUS_PERMANENT
[mysqldump]
# Variable reference
# For MySQL 5.7+: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
# For MariaDB: https://mariadb.com/kb/en/library/mysqldump/
quick
quote_names
max_allowed_packet = 1G
wait_timeout = 180
[client]
port = 3306
socket = /data/mysql/mysql.sock
default_character_set = utf8mb4
需要调整的项
innodb_buffer_pool_size = 1638M # Use up to 70-80% of RAM
innodb_log_file_size = 1G
innodb_thread_concurrency = 0 # 系统上的CPU数量（减1或2）
innodb_read_io_threads = 2 # CPU核数相加
innodb_write_io_threads = 2 # 读多，写少可以设成2:6的比例
innodb_io_capacity = 1000 # sata/sas=200，sas raid10: 2000，ssd硬盘：8000，fusion-io（闪存卡）：25,000-50,000
max_connections = 16384 # UPD - Important: high no. of connections = high RAM consumption
thread_cache_size = 100 # 可以重新利用保存在缓存中线程的数量，1GB内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大，64G 20000
slow_query_log = 0 # Disabled for production
二、服务器参数设置
1、general
datadir # 数据文件存放的目录
socket # mysql.socket表示server和client在同一台服务器，并且使用localhost进行连接，就会使用socket进行连接
pid_file # 存储mysql的pid
port=3306 # mysql服务的端口号
default_storage_engine=InnoDB # mysql存储引擎
skip-grant-tables # 当忘记mysql的用户名密码的时候，可以在mysql配置文件中配置该参数，跳过权限表验证，不需要密码即可登录mysql
2、character
character_set_client # 客户端数据的字符集
character_set_connection # mysql处理客户端发来的信息时，会把这些数据转换成连接的字符集格式
character_set_results # mysql发送给客户端的结果集所用的字符集
character_set_database # 数据库默认的字符集
character_set_server # mysql server的默认字符集
3、connection
max_connections # mysql的最大连接数，如果数据库的并发连接请求比较大，应该调高该值
max_user_connections # 限制每个用户的连接个数
back_log # mysql能够暂存的连接数量，当mysql的线程在一个很短时间内得到非常多的连接请求时，就会起作用，如果mysql的连接数量达到max_connections时，新的请求会被存储在堆栈中，以等待某一个连接释放资源，如果等待连接的数量超过back_log,则不再接受连接资源
wait_timeout # mysql在关闭一个非交互的连接之前需要等待的时长
interactive_timeout # 关闭一个交互连接之前需要等待的秒数
4、log
log_error # 指定错误日志文件名称，用于记录当mysqld启动和停止时，以及服务器在运行中发生任何严重错误时的相关信息
log_bin # 指定二进制日志文件名称，用于记录对数据造成更改的所有查询语句
binlog_do_db # 指定将更新记录到二进制日志的数据库，其他所有没有显式指定的数据库更新将忽略，不记录在日志中
binlog_ignore_db # 指定不将更新记录到二进制日志的数据库
sync_binlog # 指定多少次写日志后同步磁盘
general_log # 是否开启查询日志记录
general_log_file # 指定查询日志文件名，用于记录所有的查询语句
slow_query_log # 是否开启慢查询日志记录
slow_query_log_file # 指定慢查询日志文件名称，用于记录耗时比较长的查询语句
long_query_time # 设置慢查询的时间，超过这个时间的查询语句才会记录日志
log_slow_admin_statements # 是否将管理语句写入慢查询日志
5、cache
key_buffer_size # 索引缓存区的大小（只对myisam表起作用）
sort_buffer_size # 每个需要排序的线程分派该大小的缓冲区
join_buffer_size=2M # 表示关联缓存的大小
query cache
query_cache_limit # 超出此大小的查询将不被缓存
query_cache_min_res_unit # 缓存块最小大小
query_cache_type # 缓存类型，决定缓存什么样的查询，0表示禁用，1表示将缓存所有结果，除非sql语句中使用sql_no_cache禁用查询缓存，2表示只缓存select语句中通过sql_cache指定需要缓存的查询
thread_cache_size
服务器线程缓存，这个值表示可以重新利用保存再缓存中的线程数量，当断开连接时，那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，这个线程将被重新请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值即可
| 参数 | 说明 |
|---|---|
| Threads_cached | 代表当前此时此刻线程缓存中有多少空闲线程 |
| Threads_connected | 代表当前已建立连接的数量 |
| Threads_created | 代表最近一次服务启动，已创建现成的数量，如果该值比较大，那么服务器会一直再创建线程 |
| Threads_running | 代表当前激活的线程数 |
6、INNODB
innodb_buffer_pool_size # 该参数指定大小的内存来缓冲数据和索引，最大可以设置为物理内存的80%
innodb_flush_log_at_trx_commit # 主要控制innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，值分别为0，1，2
innodb_thread_concurrency # 设置innodb线程的并发数，默认为0表示不受限制，如果要设置建议跟服务器的cpu核心数一致或者是cpu核心数的两倍
innodb_log_buffer_size # 此参数确定日志文件所用的内存大小，以M为单位
innodb_log_file_size # 此参数确定数据日志文件的大小，以M为单位
innodb_log_files_in_group # 以循环方式将日志文件写到多个文件中
read_buffer_size # mysql读入缓冲区大小，对表进行顺序扫描的请求将分配到一个读入缓冲区
read_rnd_buffer_size # mysql随机读的缓冲区大小
innodb_file_per_table # 此参数确定为每张表分配一个新的文件
合理设置日志缓存池相关参数
# 具体依据如下：我经常设置为 64-512MB
# 一般来说，日志文件的全部大小，应该足够容纳服务器一个小时的活动内容。
# 首先在业务高峰期，计算出1分钟写入的redo量，然后评估出一个小时的redo量
mysql> pager grep Log #使用page之后，执行的命令只显示 Log 开头的
PAGER set to 'grep Log'
mysql> show engine innodb status\G select sleep(60); show engine innodb status\G;
select UserID , IMEI , regtime,LoginName,PlatID,PlatType,Mobile from register_info where LoginName = '931011910@365you'
Log sequence number 12701144821588
Log flushed up to 12701144603368
1 row in set (0.08 sec)
1 row in set (1 min 0.00 sec)
Log sequence number 12701162651514
Log flushed up to 12701162471514
1 row in set (0.00 sec)
mysql> select (12701162651514-12701144821588)/1024/1024 as MB;
+-------------+
| MB |
+-------------+
| 17.00394249 |
+-------------+
1 row in set (0.00 sec)
Log sequence number，这是写入事务日志的总字节数。所以，现在你可以看到每分钟有多少MB日志写入，通过计算后得到每分钟有17M的日志写入。根据经验法则。通常我们设置redo log size足够大，能够容纳1个小时的日志写入量。
1小时日志写入量=17M * 60=1 020M，由于默认有两个日志重做日志文件ib_logfile0和ib_logfile1。在日志组中的每个重做日志文件的大小一致，并以循环的方式写入。innodb存储引擎先写重做日志文件0，当达到文件的最后时，会切换到重做日志1，并checkpoint。以此循环。
所以我们可以大约设置innodb_log_file_size=512M。注意：在innodb1.2.x版本之前，重做日志文件总的大小不得大于等于4G，而1.2.x版本将该限制扩大到了521G。
评论区