mysql的my.cnf的修改

[client]
default-character-set= utf8mb4

[mysql]
default-character-set= utf8mb4

[mysqld]

character_set_server= utf8mb4

port= 3306

datadir = /mnt/mysql/data
socket= /var/lib/mysql/mysql.sock

symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server-id=1
lower_case_table_names=1

#CJ10
max_connections= 10000
max_user_connections= 10000
max_connect_errors=-100
back_log=500
open_files_limit=6200

query_cache_size=512M
query_cache_limit=780M
query_cache_type=0
ft_min_word_len=4
tmp_table_size=32M
 

table_open_cache = 1500
max_prepared_stmt_count=1048576
thread_cache_size=1200

log-bin=mysql-bin

#作为主机的配置
binlog-do-db=master   #要给从机同步的库
binlog-ignore-db=mysql  #不给从机同步的库
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
expire_logs_days=7  #自动清理7天前的log文件，可根据实际修改

slow_query_log = true
long_query_time=0.1
log_queries_not_using_indexes=1
slow_query_log_file=/opt/data/mysql/slow.log
log_queries_not_using_indexes = 50
performance_schema = 0
explicit_defaults_for_timestamp

#CJ08默认
skip-external-locking
#CJ09 禁外连接DNS解析

skip_name_resolve =1
interactive_timeout=600
wait_timeout=600

#CJ18
innodb_lock_wait_timeout = 60
innodb_status_output=1
innodb_status_output_locks=1
innodb_print_all_deadlocks=1

#CJ12 排序缓存
sort_buffer_size =4M
join_buffer_size =10M

#CJ13
read_buffer_size = 4M
read_rnd_buffer_size = 8M
net_buffer_length = 2M
bulk_insert_buffer_size = 16M

innodb_buffer_pool_size = 20G
innodb_buffer_pool_instances=4
key_buffer_size = 1G

innodb_autoextend_increment = 512M

innodb_log_file_size = 512M
innodb_log_files_in_group=3

#CJ04 事务内存缓冲 ，日志缓冲。 大事务 16M

innodb_log_buffer_size = 16M

#buffer pool ,innodb脏页比15%-90%。大swap多，小更新慢。默认90%

innodb_max_dirty_pages_pct = 85
innodb_flush_log_at_trx_commit =2
innodb_write_io_threads = 6
innodb_read_io_threads = 10
innodb_flush_method=0_DIRECT
innodb_file_per_table = 1

innodb_doublewrite=1
binlog_cache_size=64M
max_binlog_cache_size=512M
binlog_stmt_cache_size=64M

expire_logs_days = 1

#CJ14 网络包

max_allowed_packet = 32M

sync_binlog =1
max_binlog_size=512M
innodb_thread_concurrency = 4
innodb_purge_threads = 1

innodb_io_capacity=150
innodb_io_capacity_max=300
innodb_buffer_pool_dump_pct=25
innodb_file_format=Barracuda

innodb_flush_neighbors=0
innodb_large_prefix=1
log_timestamps=system

transaction_isolation=READ-COMMITTED
sql_model=''
secure_file_priv=''
explicit_defaults_for_timestamp = 1