MySQL: configuration

Client:

# https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html
[client]
pipe  # 只能使用pipe方式连接
socket=verbatim  # /tmp/mysql.sock  pipe名字, 由server的socket指定
host=localhost
port=3306
user=root
password=coalesce

default_character_set=utf8mb4
shared_memory_base_name=juke  # 连接不成功, 则使用TCP连接
loose_no_such_option


[mysql]
auto_rehash=true
auto_vertical_output=false
binary_as_hex=true
column_names=true
column_type_info=false
comments=true
compress=true
connect_timeout=2
database=information_schema
delimiter=;
init-command=set @engines:=(select count(*) from information_schema.engines),@var=abs(-55),@tables:=(select count(*) from information_schema.tables)
line-numbers=true
max_allowed_packet=16M
no_beep=true
reconnect=true
prompt='\U:\h \v [\D] [\C \p] [\d]\c\T> '
show_warnings=true


[mysqldump]
quick=true


[mysqld]
named_pipe=1
socket=verbatim  # The Pipe the MySQL Server will use
shared_memory=1
shared_memory_base_name=juke

 

 

Server:

# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[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 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

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

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


# miscellaneous
lower_case_table_names=1
log_timestamps=system
skip_name_resolve
tmpdir=/data/tmpdir  # read only 只能修改配置文件
key_buffer_size=16M
sql_mode=TRADITIONAL


# connection
port=3306
max_allowed_packet=128M


# replication
# create user 'replication'@'%' identified by 'hoist';
# grant replication slave on *.* to 'replication'@'%',只需有此权限读取binlog
# master innodb_flush_log_at_trx_commit=1 每次提交事务都会立即将事务刷盘
# 确保master & slave在复制的起始position之前完全一致,记下master开始复制前binlog的position
server_id=201
read_only=1  # super权限 & SQL线程仍可写
#skip_slave_start
report_host=172.16.10.201
report_port=3306
relay_log_recovery=1
slave_exec_mode=idempotent
slave_parallel_type=logical_clock  # 组提交的并行复制方式,default database,基于库的并行复制方式
slave_parallel_workers=1
slave_preserve_commit_order=1
#super_read_only=1


# GTID
enforce_gtid_consistency=1
gtid_mode=on


# filter database and table
#replicate_wild_do_table=cjml.cjml%
#replicate_ignore_db=mysql


# transaction
transaction_isolation=read-committed


# log
slow_query_log=1
long_query_time=5
binlog_format=row
binlog_cache_size=15M
sync_binlog=0  # master sync_binlog=1 每写一次binlog都刷盘,slave尽快复制
log_bin=binlog  # read only,只能修改配置文件,用来设置binlog的文件名,log_bin_basename不能设置,log_bin_index可设置
relay_log=relay-bin
general_log=0  # global variables, sql_log_off (global & session),新链接session继承global,关闭后,链接信息依然记录于general_log
binlog_expire_logs_seconds=259200  # 默认select 2592000/60/60/24=30天,修改为3天
#sql_log_bin  # session, 并且不能在配置文件中配置
#log_slave_updates  # relay-bin的内容同步到slave本地的binlog中
skip_log_bin  # 要放在最后


# character set & collation
character_set_server=utf8mb4
collation_server=utf8mb4_general_ci


# timeout
net_read_timeout=1800
net_write_timeout=1800
wait_timeout=1800
interactive_timeout=1800


# default
default_authentication_plugin=mysql_native_password
default_storage_engine=innodb


# innodb
innodb_buffer_pool_size=22G
innodb_buffer_pool_instances=24
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_log_buffer_size=100M
innodb_flush_log_at_trx_commit=1
innodb_write_io_threads=4
innodb_read_io_threads=4
innodb_max_dirty_pages_pct=85
innodb_flush_method=O_DIRECT  # InnoDB buffer pool绕过fs cache访问磁盘,redo log依旧使用fs cache
innodb_io_capacity=40000
innodb_adaptive_flushing=on
innodb_read_ahead_threshold=64  # maximum 64
innodb_random_read_ahead=1  # 同一extent中连续13个page在buffer pool中出现,innodb会将该extent剩余page读到buffer pool
innodb_parallel_read_threads=16
innodb_dedicated_server=1
innodb_stats_persistent_sample_pages=16  # analyze table更新cardinality时,每次需要采样的页数, innodb_stats_persistent=1时才会生效,提高统计信息,执行计划的准确性,增加analyze table时间
#innodb_log_files_in_group=3  # ib_logfile0 ib_logfile1 ib_logfile2
#innodb_log_file_size=48M  # 48M
#innodb_page_size=16K  # row => page => extent(区) => segment => tablespace non-modifiable
#innodb_undo_tablespaces=2 Deprecated InnoDB always creates 2 undo tablespaces to start with,use create undo tablespace
#innodb_force_recovery=6  # 开启此参数,不能 initialize


# buffer
read_buffer_size=256M
sort_buffer_size=256M
join_buffer_size=256M
tmp_table_size=50M
max_heap_table_size=50M
max_length_for_sort_data=65536  # default 4096
thread_cache_size=20


# select into outfile, load data infile
#secure_file_priv=/var/lib/mysql-files  # null(all prohibited),''(all granted) read only