MySQL数据库管理系统的优势与应用

[mysqld_safe]

log-error=/data/mysql/mysql3308/logs/mysqld.log

open-files-limit = 65535

[mysqld]

#MySQL Server layer basic setting

server_id = 1

port = 3306

socket = /data/mysql/mysql3308/tmp/mysql.sock

character_set_server= utf8

#init_connect='SET global default_collation_for_utf8mb3=utf8mb3_general_ci'

collation_server = utf8mb3_general_ci

skip-character-set-client-handshake=1

group_concat_max_len = 102400

group_concat_max_len = 102400

lock_wait_timeout = 10

wait_timeout = 400

skip_name_resolve=1

#MySQL Server layer directory setting

basedir = /usr/local/mysql8.0

datadir = /data/mysql/mysql3308/data

tmpdir = /data/mysql/mysql3308/tmp

log-error=/data/mysql/mysql3308/logs/mysqld.log

pid-file = /data/mysql/mysql3308/tmp/mysqld3308.pid

gtid-mode = off

enforce-gtid-consistency = off

#MySQL Server layer connection setting

max_connections = 2000

max_connect_errors = 100000

max_allowed_packet = 32M

back_log = 100

#MySQL Server layer binlog setting

log_bin_trust_function_creators = on

expire_logs_days = 30

binlog_cache_size = 20M

log-bin = /data/mysql/mysql3308/logs/mysql-bin

binlog_format = row

###replication###

log_slave_updates = 1

relay_log_recovery = 1

relay_log_purge = 0

relay_log = /data/mysql/mysql3308/logs/mysql-relay-bin

relay_log_index = /data/mysql/mysql3308/logs/mysql-relay-bin.index

skip-slave-start

slave-net-timeout = 10

#loose_rpl_semi_sync_master_enabled = 1

#loose_rpl_semi_sync_master_wait_no_slave = 1

#loose_rpl_semi_sync_master_timeout = 1000

#loose_rpl_semi_sync_slave_enabled = 1

#slave_skip_errors = 1396

slave_parallel_type = LOGICAL_CLOCK

slave_parallel_workers = 16

master_info_repository = TABLE

relay_log_info_repository = TABLE

slave_pending_jobs_size_max=150M

#MySQL Server layer memory management setting

tmp_table_size = 1024M

max_heap_table_size = 1024M

read_buffer_size = 20M

read_rnd_buffer_size = 50M

sort_buffer_size = 50M

join_buffer_size = 160M

#query_cache_size = 0

thread_stack = 2M

#thread_handling = pool-of-threads

#thread_pool_oversubscribe = 8

#MySQL Server layer transaction management setting

transaction_isolation = READ-COMMITTED

autocommit = ON

log_timestamps=system

#replicate-do-db=cloudusercs_branch

#replicate-do-db=cloudusercs_source

#replicate-do-db=tax_bspt

#replicate-do-db=cloudusercs_source

#replicate-do-db=cloudaccount_0

#replicate-do-db=cloudaccount_1

#replicate-do-db=cloudaccount_2

#replicate-do-db=cloudaccount_3

#replicate-do-db=cloudaccount_4

#replicate-do-db=cloudaccount_5

#replicate-do-db=cloudaccount_6

#replicate-do-db=cloudaccount_7

#replicate-do-db=cloudaccount_8

#replicate-do-db=cloudaccount_9

#replicate-do-db=cloudaccount_a

#replicate-do-db=cloudaccount_b

#replicate-do-db=cloudaccount_c

#replicate-do-db=cloudaccount_d

#replicate-do-db=cloudaccount_e

#replicate-do-db=cloudaccount_f

#MySQL Server layer log related setting

log_error_verbosity = 3

slow_query_log = ON

long_query_time = 5

slow_query_log_file = /data/mysql/mysql3308/logs/mysql.slow

log_output = 'file,table'

#log_output = 'file'

#log_queries_not_using_indexes = 1

#MySQL Server layer other behaviour setting

sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

#optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,
subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=off' ##关闭5.7新特性子查询drived_merge

optimizer_switch = 'derived_merge=off' ##关闭5.7新特性子查询drived_merge

event_scheduler = ON

lower_case_table_names = 1

explicit_defaults_for_timestamp = ON

default-storage-engine = INNODB

##ft_min_word_len = 4

# Statistic

# userstat = ON

# thread_statistics = ON

# MyISAM Engine related setting

key_buffer_size = 32M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 2048M

#myisam_repair_threads = 1

#myisam_recover

#InnoDB Engine related setting

#InnoDB memory management related setting

innodb_buffer_pool_size = 512M

innodb_max_dirty_pages_pct = 90

innodb_sync_array_size = 16

innodb_max_dirty_pages_pct = 90

innodb_sync_array_size = 16

#table open cache related

table_open_cache = 6144

table_open_cache_instances = 16

#innodb_additional_mem_pool_size = 16M ##This variable have been removed MySQL 5.7

#innodb_numa_interleave = 1 ##Only work with Percona 5.6.27 and later

##InnoDB engine I/O related setting

innodb_write_io_threads = 8

innodb_read_io_threads = 8

innodb_flush_method = O_DIRECT

#InnoDB engine File management related setting

innodb_data_file_path = ibdata1:12M:autoextend #10M-->12M, 12M is default values, it is meaningless to set it to 10M

innodb_file_per_table = 1 #this value have been the default value in MySQL 5.7

#InnoDB engine undo log related setting

innodb_undo_directory = /data/mysql/mysql3308/data

#innodb_undo_tablespaces = 4

innodb_purge_batch_size = 5000

innodb_purge_threads = 8

innodb_page_cleaners = 8

#InnoDB engine redo log related setting

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 20M

innodb_log_file_size = 256M

innodb_log_files_in_group = 3

innodb_log_group_home_dir = /data/mysql/mysql3308/logs

#InnoDB engine lock and transaction management setting

innodb_lock_wait_timeout = 60

#InnoDB engine other behaviour setting

#innodb_large_prefix = ON ##this value have been the default value in MySQL 5.7

innodb_strict_mode = ON ##this value have been the default value in MySQL 5.7

innodb_checksum_algorithm = crc32 ##this value have been the default value in MySQL 5.7

innodb_io_capacity = 1000

innodb_io_capacity_max = 2000

default_authentication_plugin=mysql_native_password

[mysqldump]

quick

max_allowed_packet = 160M

character_set_server= utf8

[mysql]

no-auto-rehash

# Only allow UPDATEs and DELETEs that use keys.

#safe-updates

character_set_server= utf8

[myisamchk]

key_buffer_size = 512M

sort_buffer_size = 512M

read_buffer = 8M

write_buffer = 8M

[mysqlhotcopy]

interactive-timeout

/data

port = 3309

interactive-timeout

ELETEs that use keys.

#safe-updates

[myisamchk]

key_buffer_size = 512M

sort_buffer_size = 512M

read_buffer = 8M

write_buffer = 8M

[mysqlhotcopy]

interactive-timeout

/data

port = 3309