mysql-server-5.7.25的my.cnf

 

 

[client]
port = 3306
socket = /home/work/mysql_3306/tmp/mysql.sock

[mysqld]
user    = work
port = 3306

basedir = /home/work/mysql_3306
datadir = /home/work/mysql_3306/data
tmpdir = /home/work/mysql_3306/tmp
socket = /home/work/mysql_3306/tmp/mysql.sock
pid_file = /home/work/mysql_3306/tmp/mysql.pid
log-error = /home/work/mysql_3306/log/mysql.err
general_log = /home/work/mysql_3306/log/mysql.log
slow_query_log_file = /home/work/mysql_3308/log/slow.log
log-bin = /home/work/mysql_3306/log/mysql-bin
plugin_dir = /home/work/mysql/lib/mysql/plugin

default-time-zone = "+08:00"
thread_handling = pool-of-threads   # 这个参数在windows版本上没有这个参数
thread_pool_stall_limit = 50        # 这个参数在windows版本上没有这个参数
thread_pool_oversubscribe = 20      # 这个参数在windows版本上没有这个参数
performance_schema = 1
log_slave_updates
log_timestamps = SYSTEM
log_warnings
slow_query_log
long_query_time = 0.5
lock_wait_timeout    = 120
show_compatibility_56 = on
sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
table_open_cache_instances = 16 
##################################
enforce_storage_engine    = InnoDB   #在windows上是default-storage-engine=INNODB
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,subquery_materialization_cost_based=on,use_index_extensions=on'
super_read_only = off 
log_slow_slave_statements = on
log_slow_admin_statements = on
log_slow_verbosity = 'full'
slow_query_log_use_global_control = "long_query_time" 
event-scheduler = off 
back_log = 1000 
skip_name_resolve
max_connections = 10240
max_user_connections = 4000
max_connect_errors = 1000 
extra_max_connections = 3  # 这个参数在windows版本上没有这个参数
extra_port = 13308
table_open_cache = 8192 
table_definition_cache = 65535
max_allowed_packet = 64M 
expire_logs_days = 7
max_heap_table_size = 1024M 
read_rnd_buffer_size = 512K 
group_concat_max_len = 1024000
sort_buffer_size = 256K 
read_buffer_size = 64K
join_buffer_size = 128K 
thread_cache_size = 256 
ft_min_word_len = 4 
default-storage-engine = INNODB
thread_stack = 192K 
transaction_isolation = REPEATABLE-READ
tmp_table_size = 1024M 
open_files_limit = 65536 
key_buffer_size = 32M 
bulk_insert_buffer_size = 64M 
myisam_sort_buffer_size = 128M 
myisam_max_sort_file_size = 1G 
myisam_repair_threads = 1 
max_allowed_packet = 64M
max_prepared_stmt_count = 1000000
#-----------------------------------------------------------------
#replication
server-id = 4545454545
binlog_rows_query_log_events = on 
log-slave-updates = 1
relay-log = relay-bin
#这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
#auto_increment_offset = 1           
#这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
# auto_increment_increment = 1   
#####################必须集群内互相兼容，建议全都保持一致并为 row
binlog_format = row

# Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:Slave can not handle replication events with the checksum that master is configured to log; 
# the first event ‘mysql-bin.000001’ at 451, the last event read from ‘./mysql-bin.000001’ at 451, the last byte read from ‘./mysql-bin.000001’ at 120.’
# 这就是binlog_checksum变量设置成none的原因，以免因为主从数据库的版本不一致出现这个错误。
binlog_checksum = none 

binlog_cache_size = 16M 
max_binlog_cache_size = 2G
sync_binlog = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = on
sync_master_info = 10000
sync_relay_log_info = 10000
sync_relay_log = 0
#slave_net_timeout    = 4
#slave-parallel-type = LOGICAL_CLOCK
#slave-parallel-workers = 16
slave_pending_jobs_size_max = 134217728
slave_preserve_commit_order = 0

#################################
## 下面这两个搞不好会导致启动失败，搞清楚原理再使用
#transaction_write_set_extraction = XXHASH64
#binlog_transaction_dependency_tracking = WRITESET
#####################MySQL5.7.7之后，默认改成60秒。该参数定义了从库从主库获取数据等待的秒数，超过这个时间从库会主动退出读取，中断连接，并尝试重连。
#slave_net_timeout=8
###############mysql版本在5.6之后才有该选项。
gtid_mode = on
###############mysql版本在5.6之后才有该选项。
enforce-gtid-consistency = on
#
##############################################################################################半同步复制插件，需要安装。mysql5.7版本之后才有。
##########################################。安装插件在主库上：install plugin rpl_semi_sync_master soname 'semisync_master.so'; --安装 semisync_master.so插件 
##########################################。安装插件在从库上：install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; --安装 semisync_slave.so插件
#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
################################半同步复制插件，需要安装。mysql5.7版本之后才有。
#rpl_semi_sync_master_enabled = on
################################半同步复制插件，需要安装。mysql5.7版本之后才有。
#rpl_semi_sync_slave_enabled = on
################################半同步复制插件，需要安装。mysql5.7版本之后才有。
#rpl_semi_sync_master_wait_for_slave_count = 1
################################半同步复制插件，需要安装。mysql5.7版本之后才有。
#rpl_semi_sync_master_timeout = 1000
################################半同步复制插件，需要安装。mysql5.7版本之后才有。
#rpl_semi_sync_master_wait_point = after_sync

 
#undo
innodb_max_undo_log_size = 1024M 
innodb_undo_log_truncate = on 
innodb_undo_logs = 128
innodb_undo_tablespaces = 3 #只能在初始化的时候设置
innodb_purge_rseg_truncate_frequency = 128

#innodb
innodb_autoextend_increment = 64
innodb_concurrency_tickets = 5000
innodb_old_blocks_time = 1000
innodb_purge_batch_size = 300
innodb_stats_on_metadata = off
innodb_thread_sleep_delay = 10000 
innodb_adaptive_max_sleep_delay = 15000 
innodb_buffer_pool_dump_at_shutdown = on
innodb_buffer_pool_load_at_startup = on
innodb_flush_neighbors = 1
############################nnoDB使用后台线程处理数据页上写 I/O（输入）请求的数量。
innodb_write_io_threads = 8 
############################InnoDB使用后台线程处理数据页上读 I/O（输出）请求的数量。这里输出是输入的两倍。
innodb_read_io_threads = 16
innodb_print_all_deadlocks = on
innodb_buffer_pool_size = 30G
innodb_buffer_pool_instances = 8 
innodb_data_file_path = ibdata1:100M:autoextend # 这个参数在windows版本上没有这个参数
innodb_thread_concurrency = 48 
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 64M
############################该值表示每个redo log文件大小，该值大小约为该mysql实例一个小时产生的日志大小，这里设置为4G，ib_logfile0和ib_logfile1文件都是4G，逻辑上ib_logfile被当成了一个文件。循环写入。
innodb_log_file_size = 4096M 
#############################配置redo log成多个文件，Redo log文件以ib_logfile[number]命名，日志目录通过参数innodb_log_group_home_dir控制，以顺序的方式写入文件文件，写满时则回溯到第一个文件，进行覆盖写。
innodb_log_files_in_group = 3 
###############################控制了 Dirty Page 在 Buffer Pool 中所占的比率。
innodb_max_dirty_pages_pct = 75 
innodb_max_dirty_pages_pct_lwm = 10 
innodb_flush_method = O_DIRECT   # 在windows上不行
innodb_lock_wait_timeout = 10 
#innodb_file_per_table参数必须设置为1，否则xtrabackup工具无法单独备份某一个数据库。
innodb_file_per_table = 1
innodb_purge_threads = 4 
#########################该参数表示缓冲区刷新到磁盘时，刷新脏页数量。普通单个SATA可以设置200，对于SSD可以设置3000到5000，而拥有5个磁盘组成的RAID5可以设置成2000。
innodb_io_capacity = 5000
innodb_open_files = 65535 
innodb_online_alter_log_max_size = 5120M
innodb_sort_buffer_size = 4M
innodb_adaptive_hash_index_parts = 8 
innodb_buffer_pool_chunk_size = 128 
innodb_buffer_pool_dump_pct = 80 
innodb_deadlock_detect = on 
innodb_default_row_format = DYNAMIC 
innodb_fill_factor = 100 
innodb_flush_sync = off 
innodb_log_checksums = on 
innodb_log_write_ahead_size = 8192 
innodb_max_undo_log_size = 1073741824 
innodb_page_cleaners = 4 
innodb_purge_rseg_truncate_frequency = 128 
innodb_temp_data_file_path = ibtmp1:12M:autoextend 
innodb_print_lock_wait_timeout_info = on #在windows上不行


 

 

mysql安装目录配置：

[work@a8-dba-cloud-db00.wh mysql_3306]$ pwd
/home/work/mysql_3306
[work@a8-dba-cloud-db00.wh mysql_3306]$ ll
总用量 760
drwxr-xr-x  2 work work   4096 2月   5 20:10 bin
drwxr-xr-x  2 work work   4096 2月  22 17:12 binlog
drwxr-xr-x 11 work work   4096 2月  22 17:12 data
drwxr-xr-x  2 work work   4096 2月   5 20:10 docs
drwxr-xr-x  2 work work   4096 2月  22 11:24 etc
drwxr-xr-x  3 work work   4096 2月   5 20:10 include
drwxr-xr-x  6 work work   4096 2月   5 20:10 lib
-rw-r--r--  1 work work 283374 12月 16 23:34 LICENSE
-rw-r--r--  1 work work 121462 12月 16 23:34 LICENSE.router
-rw-r--r--  1 work work 283374 12月 16 23:34 LICENSE-test
drwxr-xr-x  2 work work   4096 2月  20 22:01 log
drwxr-xr-x  4 work work   4096 2月   5 20:10 man
-rw-r--r--  1 work work   1622 2月   5 16:41 mysqlrouter-log-rotate
drwxr-xr-x 10 work work   4096 2月   5 20:10 mysql-test
drwxr-xr-x  2 work work   4096 2月   5 12:59 plugin
-rw-r--r--  1 work work    666 12月 16 23:34 README
-rw-r--r--  1 work work    679 12月 16 23:34 README.router
-rw-r--r--  1 work work    666 12月 16 23:34 README-test
drwxrwxr-x  2 work work   4096 2月   5 20:10 run
drwxr-xr-x 28 work work   4096 2月   5 20:10 share
drwxr-xr-x  2 work work   4096 2月   5 20:10 support-files
drwxr-xr-x  2 work work   4096 2月  22 17:12 tmp
drwxr-xr-x  3 work work   4096 2月   5 20:10 var
[work@a8-dba-cloud-db00.wh mysql_3306]$ 

 

 

 

 

常见插件可以不用，但最好也安装上去：

MGR插件：

mysql>install plugin group_replication soname 'group_replication.so';

 

半同步插件：

mysql>install plugin rpl_semi_sync_master soname 'semisync_master.so';

 

 

 

 

 

 

一. innodb_flush_log_at_trx_commit

　是指：ib_logfile的刷新方式（ ib_logfile：记录的是redo log和undo log的信息，是重做日志写入磁盘的过程。用来控制缓冲区中的数据写入到日志文件，以及日志文件数据刷新到磁盘(flush)的操作时机。对这个参数的设置值，可以对数据库在性能与数据安全之间，进行折中。

　　参数值解释：

　　　　当参数是0：日志缓冲数据会，每秒一次地写入到日志文件，并且把日志文件刷新到磁盘操作。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。

　　　　当参数是1：每次事务提交时，日志缓冲被写到日志文件，并且对日志文件做磁盘刷新操作，该模式为系统默认。但由于每次事务都需要进行磁盘I/O，所以也最慢。

　　　　当参数是2：每次事务提交时，日志缓冲被写到日志文件，但不对日志文件做磁盘刷新操作。对日志文件每秒执行一次，刷到磁盘操作。

　　当设置innodb_flush_log_at_trx_commit=1时， 是默认值，也是最安全的设置，但是在这种模式下性能有一定的损失。 如果设置成0或者2 性能会有所改善，但有数据丢失的风险。
　　设置成0则数据库崩溃的时候，那些没有被写入日志文件的事务丢失，最多丢失1秒钟的事务，是最不安全的，但也是效率最高的。
　　设置成2则只是没有刷新到磁盘，但已经写入日志文件，所以只要操作系统没有崩溃， 那么并没有数据丢失, 比设置成0更安全。
　　在mysql官方中为了确保事务的持久性和复制设置的一致性，都是建议将这个参数值设置为1;

　　对于一些数据一致性和完整性要求不高的应用，配置为 2 就足够了；

　　如果为了最高性能，可以设置为 0。

　　有些应用，如支付服务，对一致性和完整性要求很高，所以即使最慢，也最好设置为 1。

参数值	数据安全性	I/O性能
0	安全最差。当数据库崩溃,有丢失1秒钟的事务风险	最优
1	安全最好。无丢失数据	最差
2	安全折中。当操作系统崩溃, 有丢失1秒钟的事务风险	折中

　　1.1 查看日志提交方式

　　SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

　　　　

　　1.2 修改参数值

        　　 还是一样找到my.cnf, 修改参数值

         　　[root@xuegod64 ~]# cd /etc

         　　[root@xuegod64 etc]# vim my.cnf
　　　　

　　　　[root@xuegod64 ~]# systemctl stop mysqld.service

　　　　[root@xuegod64 ~]# systemctl start  mysqld.service

-- 再次查看
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

　　　　

二. sync_binlog　　

　　是二进制Binlog文件， 这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。在MySQL中系统默认的设置是sync_binlog=1。对于“sync_binlog”参数的各种设置的说明如下：

　　1）sync_binlog = 0：当事务提交之后，不主动刷新二进制日志文件的数据到磁盘上，而是由操作系统决定。

      2）sync_binlog = 1：每当事务提交之后，主动刷新二进制日志文件的数据到磁盘上是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。。

　　3）sync_binlog > 1：每向二进制日志文件写入N条SQL或N个事务后，则把二进制日志文件的数据刷新到磁盘上。

 

--  查看binlog写入方式
SHOW VARIABLES LIKE 'sync_binlog';

# 在线更改参数
set global sync_binlog = 1000；

 

　　

　　总结： 在数据安全与性能以日志文件作为出发点时，我认为功能上与sql server 的数据恢复模式比较相像，但实现的思路是不一样的。
　　innodb_flush_log_at_trx_commit和sync_binlog是MySQL innodb引擎的两个重要的参数，其中innodb_flush_log_at_trx_commit是将事务日志从innodb log buffer刷新到磁盘，sync_binlog是将二进制日志文件刷新到磁盘上。
　　innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数，当两个参数都设置为1的时候写入性能最差，
网上也有说将innodb_flush_log_at_trx_commit=2，sync_binlog=500 或1000。有说对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。总体上还是要根据业务来判断，在性能和安全上做个选择。

 

1. innodb_lock_wait_timeout

  mysql 可以自动监测行锁导致的死锁并进行相应的处理，但是对于表锁导致的死锁不能自动监测，所以该参数主要用于，出现类似情况的时候等待指定的时间后回滚。系统默认值是50秒。用户可以根据业务自行设置。生产环境不推荐使用过大的 innodb_lock_wait_timeout 参数值。

-- 查看事务超时时间
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

　　
　　也可以对当前会话进行超时设置如： set innodb_lock_wait_timeout=1000。关于产生死锁的原因，如何查看分析死锁问题， 如何优化尽量避免死锁，请查看"mysql 开发进阶篇 锁问题系列"。

InnoDB 表类型的时候，锁超时时间是通过innodb_lock_wait_timeout：设置锁等待的时间，默认值是50s。

innodb_lock_wait_timeout指的是事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用失败；参数的时间单位是秒，最小可设置为1s(此时需要考虑应用端的频繁异常处理会消耗性能，不能设置过小)，

 

最大可设置1073741824秒以上(再大就会被截断了，不过这样业务一直死循环等待下去而不能将资源使用来做其他的事情也是很浪费的一件事情)。

 

当有锁等待超过了这个时间（50），会报错1205 - Lock wait timeout exceeded; try restarting transaction，来中断事务，并释放锁。

2. innodb_support_xa

         通过该参数设置，是否支持分布式事务。默认值是ON或者1，表示支持分布式事务。
　　 分布事事务分两类：

　　(1)是外部xa事务(支持多实例分布式事务)。

　　(2)是支持内部xa事务（支持binlog和redo_log之间数据一致性）。

如果关闭这个参数，据前辈们说可能会影响到:1是主从复制binlog与redo_log不一致,2是binlog与redo_log事务顺序不一致性。

--  查看是否支持分布式事务
SHOW VARIABLES LIKE 'innodb_support_xa';

　　

3. innodb _log_buffer_size

       这个参数是指日志缓存的大小。默认的设置在中等强度写入负载以及较短事务的情况下，一般都可以满足服务器的性能要求。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值。 如果值设置太高，可能会浪费内存，因为它每秒都会刷新一次，因此无需设置超过1秒所需的内存空间(理解是每1秒刷新后，日志缓存会清空)。通常设置为8~16MB就足够了。系统默认是16M。

--  查看日志缓存空间大小
 SHOW VARIABLES LIKE 'innodb_log_buffer_size';

　　

　　　　16777216.0/1024.0/1024.0=16M

4. innodb_log_file_size
　　这个参数是一个日志组(log group)中每个日志文件的大小,也叫事务日志文件大小。此参数在高写入负载尤其是大数据集的情况下很重要.这个值越大则性能相对越高，但副作用是当系统发生灾难时恢复时间会加大。系统默认是48M。
　　(1) 小日志文件使写入速度更慢，崩溃恢复速度更快。原因是由于事务日志相当于一个写缓冲，而小日志文件会很快的被写满，这时候就需要频繁地刷新到硬盘，速度就慢了。如果产生大量的写操作，会增加checkpoint写的次数，如果不能足够快地刷新数据，那么写性能将会降低，。相反文件空间大，在刷新操作发生之前给你足够的空间来使用。
　　(2) 大日志文件使写入更快，崩溃恢复速度更慢。

--  查看每个日志文件的大小
 SHOW VARIABLES LIKE 'innodb_log_file_size';

　　
　　268435456.0/1024.0/1024.0=256M

5. innodb_log_compressed_pages

         这个参数是指:日志文件页存储压缩。系统默认是ON , 将减少redo log的写入量。

6. innodb_log_checksums

         这个参数是指：写入redo log到文件之前，redo log的每一个block都需要加上checksum校验位，以防止apply损坏redo log。

7.  innodb_log_write_ahead_size

         这个参数是指： redo log写前的块大小。系统默认是8192字节。

8. innodb_log_files_in_group

       这个参数是指：该变量控制日志文件数。默认值为3。日志是以顺序的方式写入。结合innodb_buffer_pool_size设置其大小。一般不用设置。

9. innodb_log_group_home_dir

  　　这个参数是指：日志组所在的路径。

--  所有日志参数如下
 SHOW VARIABLES LIKE 'innodb_log%';

 

 

mysql8版本的配置

my.cnf

cat my.cnf
[client]
port=3306
socket=/home/work/ssd1/mysql-8.0.18-linux-glibc2.12-x86_64/mysql-clinet.sock

[mysqld]

default_password_lifetime=0
#skip-grant-tables
user=work
# disable_ssl
skip_ssl
default_authentication_plugin=mysql_native_password
innodb_buffer_pool_size=5GB
datadir=/home/work/ssd1/mysql-8.0.18-linux-glibc2.12-x86_64/data
basedir=/home/work/ssd1/mysql-8.0.18-linux-glibc2.12-x86_64
log-error=/home/work/ssd1/mysql-8.0.18-linux-glibc2.12-x86_64/log/mysql.err
log-bin = /home/work/ssd1/mysql-8.0.18-linux-glibc2.12-x86_64/log/mysql-bin
binlog_format = ROW
server-id = 123456
slow_query_log_file = /home/work/ssd1/mysql-8.0.18-linux-glibc2.12-x86_64/log/slow.log
long_query_time = 0.5
lock_wait_timeout = 120
pid_file=/home/work/ssd1/mysql-8.0.18-linux-glibc2.12-x86_64/mysql.pid
socket=/home/work/ssd1/mysql-8.0.18-linux-glibc2.12-x86_64/mysql.sock
port=3306

 ###############

 

 expire_logs_days

如果启用了二进制日志，应该打开这个选项，可以让服务器在指定的天数之后清理旧的二进制日志。如果不启用,最终服务器的空间会被耗尽,导致服务器卡住或崩溃。

我们建议把这个选项设置得足够从两个备份之前恢复(在最近的备份失败的情况下)。即使每天都做备份，还是建议留下7~14天的二进制日志。

从我们的经验来看，当遇到一些不常见的问题时，你会感谢有这一两个星期的二进制日志。例如重搭一个
备机再次尝试赶上主库。应该保持足够多的二进制日志，遇到这些情况时可以给自己一些呼吸的空间。

max_allowed_packet

这个设置防止服务器发送太大的包，也会控制多大的包可以被接收。默认值可能太小了，但设置得太大也可能有危险。如果设置得太小，有时复制上会出问题，通常表现为备库不能接收主库发过来的复制数据。你也许需要增加这个设置到16MB或
者更大。这些文档里没有，但这个选项也控制在一个用户定义的变量的最大值，所以如果需要非常大的变量，要小心——如果超过这个变量的大小，它们可能被截断
或者设置为NULL。

max_connect_errors

如果有时网络短暂抽风了，或者应用配置出现错误，或者有另外的问题，如权限，在短暂的时间内不断地尝试连接，客户端可能被列入黑名单，然后将无法连接，直到再次刷新主机缓存。这个选项的默认设置太小了，很容易导致问题。

你也许希望增加这个值，实际上，如果知道服务器可以充分抵御蛮力攻击，可以把这个值设得非常大，以有效地禁用主机黑名单。

skip_name_resolve

这个选项禁用了另一个网络相关和鉴权认证相关的陷阱:DNS查找。DNS是MySQL连接过程中的一个薄弱环节。当连接服务器时，默认情况下，它试图确定连接和使用的主机的主机名，作为身份验证凭据的一部分。

(就是说，你的凭据是用户名，主机名、以及密码——并不只是用户名和密码）但是验证主机来源，服务器需要执行DNS的正向和反向查找。要是DNS有问题就悲剧了，在某些时间点这是必然的事。

当发生这样的情况时，所有事都会堆积起来，最终导致连接超时。为了避免这种情况，我们强烈建议设置这个选项，在验证时关闭DNS查找。

然而，如果这么做,需要把基于主机名的授权改为用IP地址,通配符,或者特定主机名“localhost”,因为基于主机名的账号会被禁用。

sql_mode

这个设置可以接受多种多样的值来改变服务器行为。我们不建议只是为了好玩而改变这个值﹔最好在大多数情况下让 MySQL像MySQL，不要尝试让它的行为像其他数据库服务器。(许多客户端和图形界面工具，除了MySQL还有它们自己的SQL

方言，例如，若修改它用更符合ANSI的SQL，有些操作会没法做。）然而，有些选项值是很有用的,有些在具体情况可能是值得考虑的。建议查看文档中下面这些选项，并且考虑使用它们:STRICT_TRANS_TABLES、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER、NO_AUTO_VALUE_ON_ZERO、NO_ENGINE_SUBSTITUTION、NO_ZERO_DATE、NO_ZERO_IN_DATE和ONLY_FULL_GROUP_BY。
然而，要意识到对已经存在的应用修改这些设置值可不是个好主意，因为这么做可
能让服务器跟应用预期不兼容。人们个经意间与的宜间中PY用V5I GOIIP Y洗项，或者使用聚合函数，这种情况非常常见，例如，看想打开立环撞部署则必须确认最好首先在开发或未上线服务器上做一下测试，一旦要在生产环境部署则必须确认
所有地方都可以工作。

skip_slave_start

这个选项阻止 MySQL试图自动启动复制。因为在不安全的崩溃或其他问题后，启动复制是不安全的，所以需要禁用自动启动，用户需要手动检查服务器，并确定它是安全的之后再开始复制。

slave_net_timeout

这个选项控制备库发现跟主库的连接已经失败并且需要重连之前等待的时间。默认值是一个小时，太长了。设置为一分钟或更短。

sync_master_info、sync_relay_log、sync_relay_log_info

这些选项，在MySQL 5.5以及更新版本中可用，解决了复制中备库长期存在的问题:不把它们的状态文件同步到磁盘，所以服务器崩溃后可能需要人来猜测复制的位置实际上在主库是哪个位置，并且可能在中继日志(Relay Log)里有损坏。这些选项使得备库崩溃后，更容易从崩溃中恢复。这些选项默认是不打开的，因为它们会导致备库额外的fsync()操作，可能会降低性能。如果有很好的硬件，我们建议打开这些选项，如果复制中出现fsync()造成的延时问题，就应该关闭它们。
Percona Server中有一种侵入性更小的方式来做这些工作，即打开innodb_

 

binlog_row_image

 

 

###################################

在使用SSD固态硬盘时，为了避免因SSD的特殊写入方式而导致doublewrite buffer技术失效，可以设置innodb_flush_neighbors参数的值为1。

可以通过在MySQL的配置文件中添加以下配置来设置innodb_flush_neighbors参数的值：

 

[mysqld]

innodb_flush_neighbors = 1

 

该配置表示将innodb_flush_neighbors参数的值设置为1，开启等待机制。这样，在MySQL将数据写入磁盘之前，会先等待doublewrite buffer中的数据全部写入磁盘。这样可以保证数据写入的顺序是正确的，同时也可以避免因为SSD的特殊写入方式导致doublewrite buffer技术失效的问题。

注意，需要重启MySQL服务使该配置生效。另外，对于某些特殊的SSD硬盘，可能需要进行其他的优化配置，以达到更好的性能和可靠性。

 

 

 

现有一台128G的内存、2.6T的SSD固态硬盘、32核的CPU的服务器，想把mysql安装到该服务器上，为了充分利用该台服务器资源，则最佳的mysql详细配置参数是什么？

[mysqld]
# 基础参数
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
log_error = /var/log/mysql/error.log
pid_file = /var/run/mysqld/mysqld.pid
user = mysql
port = 3306
bind-address = 0.0.0.0
skip-name-resolve

# 线程池配置
thread_handling = pool-of-threads
thread_pool_size = 32
thread_pool_max_threads = 1000
thread_pool_idle_timeout = 60

# InnoDB存储引擎配置
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_buffer_pool_size = 96G
innodb_buffer_pool_instances = 32
innodb_flush_log_at_trx_commit = 1
innodb_log_file_size = 2G
innodb_log_buffer_size = 32M
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_lru_scan_depth = 1024
innodb_flush_neighbors = 1
innodb_thread_concurrency = 0
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_purge_threads = 4

# 查询缓存配置
query_cache_type = 0
query_cache_size = 0
query_cache_limit = 0

# 日志配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# 网络连接配置
max_connections = 1000
max_connect_errors = 100000
back_log = 65535
wait_timeout = 600
net_read_timeout = 60
net_write_timeout = 60

# 其他配置
tmp_table_size = 512M
max_heap_table_size = 512M
join_buffer_size = 256M
sort_buffer_size = 256M
table_open_cache = 4096
table_definition_cache = 4096
max_allowed_packet = 256M

 

其中，innodb_buffer_pool_size被设置为96G，占据了该服务器128G内存的大部分，用于缓存表和索引的数据。innodb_log_file_size被设置为2G，用于配置InnoDB存储引擎的redo日志文件大小。

innodb_flush_log_at_trx_commit被设置为1，表示每次事务提交时将redo日志强制刷新到磁盘上。

innodb_flush_method被设置为O_DIRECT，表示使用原始的IO操作，不使用操作系统缓存，避免IO操作竞争。

为了充分利用32核CPU的资源，使用了线程池来管理MySQL的线程。

thread_pool_size被设置为32，表示线程池中最小的线程数；

thread_pool_max_threads被设置为1000，表示线程池中最大的线程数；

thread_pool_idle_timeout被设置为60，表示一个空闲线程等

 

 ############################################################################

max_execution_time （5.7）

max_statement_time（5.6）

 默认值位0

建议值：180000（单位毫秒）

作用：用于在服务端对 select 语句进行超过180S超时时间自动kill掉

 

全局设置
Mysql 5.7 ：set global max_execution_time =180000;

Mysql 5.6 ：set global max_statement_time=180000;

修改完成后，同时需要在my.cnf中增加此配置项，防止重启后失效

 

 

#慢SQL需要配置如下参数

set global log_slow_admin_statements=0;

set global log_queries_not_using_indexes=0;

set global log_timestamps='SYSTEM';

 

 sql_mode =NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
show_compatibility_56 =1

待定参数详解：

stop slave;
set global slave_type_conversions=ALL_NON_LOSSY; #默认是空
set global slave_exec_mode=IDEMPOTENT; # 默认是STRICT
start slave;

 

binlog_row_image：

• 这是MySQL5.6新增的参数，默认值是FULL，在5.7版本默认值也是FULL。
• binlog格式必须为row格式或者mixed格式，不可以是statement格式。
• binlog_row_image参数可以设置三个合法值: FULL、MINIMAL、NOBLOB。

1、full

mysql> show variables like '%row_im%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+
1 row in set (0.01 sec)

mysql> update t2 set uid=99 where name='yayundeng';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 

 

binlog解析如下：

[root@xxx3306]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000010 | tail -n 20
#161210 11:02:32 server id 152  end_log_pos 2043 CRC32 0x3ce8a225       Update_rows: table id 110 flags: STMT_END_F
### UPDATE `test`.`t2`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='gz' /* STRING(20) meta=65044 nullable=1 is_null=0 */
###   @3='yayundeng' /* STRING(20) meta=65044 nullable=1 is_null=0 */
###   @4=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='gz' /* STRING(20) meta=65044 nullable=1 is_null=0 */
###   @3='yayundeng' /* STRING(20) meta=65044 nullable=1 is_null=0 */
###   @4=99 /* INT meta=0 nullable=1 is_null=0 */
# at 2043
#161210 11:02:32 server id 152  end_log_pos 2074 CRC32 0x93619126       Xid = 49
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 

2、minimal

mysql> set  binlog_row_image ='minimal';
Query OK, 0 rows affected (0.00 sec)

mysql> update t2 set uid=100 where name='yayundeng';  
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 

 

[root@xxx 3306]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000010 | tail -n 20
SET TIMESTAMP=1481339144/*!*/;
BEGIN
/*!*/;
# at 2211
#161210 11:05:44 server id 152  end_log_pos 2263 CRC32 0x57948074       Table_map: `test`.`t2` mapped to number 110
# at 2263
#161210 11:05:44 server id 152  end_log_pos 2309 CRC32 0x72114b1f       Update_rows: table id 110 flags: STMT_END_F
### UPDATE `test`.`t2`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
### SET
###   @4=100 /* INT meta=0 nullable=1 is_null=0 */
# at 2309
#161210 11:05:44 server id 152  end_log_pos 2340 CRC32 0x23e45a71       Xid = 52
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 

 

参考：

https://blog.csdn.net/weixin_41561862/article/details/114604508

 

 

 ###############################

 

##################