Mysql 配置文件和目录的合理分配

记得给权限呀， 权限呀， 都是泪

多实例 

 

单实例

 

 

 配置文件

[client]
port                           = 3301
socket                         = /data/mysql3301/data/mysqld3301.sock


[mysqld_safe]
socket                         = /data/mysql3301/data/mysqld3301.sock
nice                           = 0
#flush-caches
numa-interleave


[mysql]
port                           = 3301
socket                         = /data/mysql3301/data/mysqld3301.sock
no_auto_rehash
default_character_set          = utf8
prompt                         = \u@\h:\p:[\d]>

[mysqldump]
quick
quote-names
max_allowed_packet             = 16M


[mysqld]
# GENERAL #
user                           = mysqldb
port                           = 3301
default_storage_engine         = InnoDB
socket                         = /data/mysql3301/data/mysqld3301.sock
pid_file                       = /data/mysql3301/data/mysqld3301.pid
server-id                      = 6501

# Charset #
init_connect                   = 'SET collation_connection = utf8mb4_unicode_ci'
init_connect                   = 'SET NAMES utf8mb4'
character-set-server           = utf8mb4
character-set-client           = utf8mb4
collation-server               = utf8mb4_unicode_ci
skip-character-set-client-handshake
explicit_defaults_for_timestamp=true

# MyISAM #
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000
skip_name_resolve
innodb                         = FORCE

# DATA STORAGE #
datadir                        = /data/mysql3301/data

# BINARY LOGGING #
log_bin                        = /data/mysql3301/binlog/mysql-bin
log_bin_index                  = /data/mysql3301/binlog/mysql-bin.index
expire_logs_days               = 7
sync_binlog                    = 1
binlog_format                  = ROW
binlog_cache_size = 4M
max_binlog_size = 1G 
max_binlog_cache_size = 100M

# CACHES AND LIMITS #
tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 0
query_cache_size               = 0
max_connections                = 5000
max_user_connections           = 5000
thread_cache_size              = 100
open_files_limit               = 65536
table_definition_cache         = 10240
table_open_cache               = 10240
thread_handling                = pool-of-threads
thread_pool_oversubscribe      = 10

# INNODB #
innodb_flush_method             = O_DIRECT
innodb_log_files_in_group       = 2
innodb_log_file_size            = 512M
innodb_log_buffer_size          = 64M
innodb_flush_log_at_trx_commit  = 2
innodb_file_per_table           = 1
innodb_open_files               = 1024
innodb_buffer_pool_size         = 1
#innodb_buffer_pool_populate    = 1
innodb_additional_mem_pool_size = 32M
innodb_print_all_deadlocks      = 1
innodb_log_group_home_dir       = /data/mysql3301/innolog
innodb_data_home_dir            = /data/mysql3301/innodata

# REPLICATION #
skip_slave_start               = 1
#replicate-wild-ignore-table    = mysql.%
replicate-wild-ignore-table    = information_schema.%
replicate-wild-ignore-table    = performance_schema.%
relay_log                      = /data/mysql3301/relaylog/relay-bin
relay-log-index                = /data/mysql3301/relaylog/relay-bin.index
sync_master_info               = 1
sync_relay_log                 = 1
sync_relay_log_info            = 1
read_only                      = 0

# LOGGING #
log_error                      = /data/mysql3301/log/mysql-error.log
log_queries_not_using_indexes  = 1
long_query_time                = 1
slow_query_log                 = 1
slow_query_log_file            = /data/mysql3301/log/mysql-slow.log
general_log                    = OFF
general_log_file               = /data/mysql3301/log/mysql-general.log

 

[client]
port                           = 3301
socket                         = /data/mysql8.0/mysql.sock


[mysqld_safe]
socket                         = /data/mysql8.0/mysql.sock
nice                           = 0
#flush-caches
numa-interleave


[mysql]
port                           = 3301
socket                         = /data/mysql8.0/mysql.sock
no_auto_rehash
default_character_set          = utf8
prompt                         = \u@\h:\p:[\d]>

[mysqldump]
quick
quote-names
max_allowed_packet             = 16M


[mysqld]
#skip-grant-tables 
default_authentication_plugin = mysql_native_password
# GENERAL #
user                           = mysql
port                           = 3301
default_storage_engine         = InnoDB
socket                         = /data/mysql8.0/mysql.sock
#pid_file                       = /data/mysql8.0/mysqld.pid
pid_file            = /data/mysql8.0/mysqld.pid
server-id                      = 6501

# Charset #
init_connect                   = 'SET collation_connection = utf8mb4_unicode_ci'
init_connect                   = 'SET NAMES utf8mb4'
character-set-server           = utf8mb4
#character-set-client           = utf8mb4
collation-server               = utf8mb4_unicode_ci
skip-character-set-client-handshake
explicit_defaults_for_timestamp=true

# MyISAM #
key_buffer_size                = 32M
#myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000
skip_name_resolve
innodb                         = FORCE

# DATA STORAGE #
datadir                        = /data/mysql8.0

# BINARY LOGGING #
log_bin                        = /data/mysql8.0/mysql-bin
log_bin_index                  = /data/mysql8.0/mysql-bin.index
expire_logs_days               = 7
sync_binlog                    = 1
binlog_format                  = ROW
binlog_cache_size = 4M
max_binlog_size = 1G 
max_binlog_cache_size = 100M

# CACHES AND LIMITS #
tmp_table_size                 = 32M
max_heap_table_size            = 32M
# query_cache_type               = 0
# query_cache_size               = 0
max_connections                = 5000
max_user_connections           = 5000
thread_cache_size              = 100
open_files_limit               = 65536
table_definition_cache         = 10240
table_open_cache               = 10240
#thread_handling                = pool-of-threads
# thread_pool_oversubscribe      = 10

# INNODB #
innodb_flush_method             = O_DIRECT
innodb_log_files_in_group       = 2
innodb_log_file_size            = 512M
innodb_log_buffer_size          = 64M
innodb_flush_log_at_trx_commit  = 2
innodb_file_per_table           = 1
innodb_open_files               = 1024
innodb_buffer_pool_size         = 1
#innodb_buffer_pool_populate    = 1
# innodb_additional_mem_pool_size = 32M
innodb_print_all_deadlocks      = 1
innodb_log_group_home_dir       = /data/mysql8.0
innodb_data_home_dir            = /data/mysql8.0

# REPLICATION #
skip_slave_start               = 1
#replicate-wild-ignore-table    = mysql.%
replicate-wild-ignore-table    = information_schema.%
replicate-wild-ignore-table    = performance_schema.%
relay_log                      = /data/mysql8.0/relay-bin
relay-log-index                = /data/mysql8.0/relay-bin.index
sync_master_info               = 1
sync_relay_log                 = 1
sync_relay_log_info            = 1
read_only                      = 0

# LOGGING #
log_error                      = /data/mysql8.0/mysqld.log
log_queries_not_using_indexes  = 1
long_query_time                = 1
slow_query_log                 = 1
#slow_query_log_file            = /data/mysql8.0/mysql-slow.log
general_log                    = OFF
#general_log_file               = /data/mysql8.0/mysql-general.log