Mysql - 配置和命令大全（my.cnf）

目录

服务端配置：

慢日志相关

Profile性能监控【后续在infomation_schema中的 profiling数据表中查看】

innodb相关

客户端配置：

---

服务端配置：

    show processlist; 显示数据库链接列表状态；

    datadir = /var/lib/mysql；数据文件目录；

    pid-file = /var/run/mysqld/mysqld.pid，进程id号；

    log-error = /var/log/mysqld.log；错误日志文件路径；

    max_connection：控制允许链接到mysql的最大线程数，默认151；

    back_log：TCp链接请求排队等待栈，并发数据大时可以调高；

    query_cache_type：off/on 缓存是否开启；

    query_cache_size = 0：缓存大小，可以设置如：128M；

    show status like ‘%Qcache%’  查询缓存相关配置；

    binlog_format = ROW；binlog日志格式，可选项有 STATEMENT、ROW、MIXED，也可以通过命令设置：set global binlog_format = ‘ROW’

    show variables like 'binlog_format'；显示当前数据库的binlog格式；

    SHOW BINARY LOGS；查询binlog的文件列表，等同于主从中的主节点执行：show master logs；

    show slave hosts；显示所有的从节点信息列表；

    join_buffer_size：join buffer的最大值，每次线程查询前都要，重置查询的buffer；

    max_length_for_sort_data：最大的排序字段长度控制，用于控制排序是走全字段排序还是 row_id 排序；

    sort_buffer_size：最大的缓存排序（快速排序）内存，超过后需要使用文件排序；

    read_rnd_buffer_size：Mutil-Range Read 读取时的缓存数据，缓存数据越大里面排序的字段阅读，近似顺序读的性能越高；

    net_buffer_length：默认是16K，tcp写入数据时的缓存大小；

    kill query 线程id；终止线程中的执行语句，可以通过上面的 show processlist查看线程id号；

    kill [connextion] 线程id；杀死链接，connection是缺省值，一般不写；

    key_buffer_size：设置MyISAM存储引擎，key buffer 缓存的大小；

    analyze table 表名：重新手动触发索引统计信息；

    show index from 表名：查询索引统计信息，基数，采用率，索引类型等等；

    alter table a engine = InnoDB；inplace 重建表命令；

    sql_safe_updates = ON； 开启后delete、update操作一定要带where条件，防止误删数据操作；    

慢日志相关

    long_query_time： 当查询时间超过多少秒才会被记录为慢查询，默认值为 10，可以设置为 0~10，可以指定设置为微秒；

    slow_query_log： 慢查询日志开关，默认值是 0 表示关闭， 设置为 1 表示开启；

    slow_query_log_file：host_name-slow.log，定义慢日志的保存地址，默认是主机名-slow.log；

    log-short-format：ON表示激活，会在慢日志中记录少量日志信息；

    log_slow_admin_statements：默认值off，on表示写入慢查询的日志包括：ALTER TABLE、ANALYZE TABLE、CHECK TABLE、CREATE INDEX、DROP INDEX、OPTIMIZE TABLE、REPAIR TABLE；

    log_queries_not_using_indexes：是否记录未使用索引的SQL；

    log_output：日志输出的地址，默认值为FILE，即指向slow_query_log_file配置的文件地址， 可选值有TABLE、FILE、NONE；

    show VARIABLES like '%slow_query_log%'， 查询slow_query_log配置项；

    show VARIABLES like '%slow_query_log_file%'， 查询slow_query_log_file配置项；

    show VARIABLES like '%long_query_time%'， 查询long_query_time配置项；

    show VARIABLES like '%log_queries_not_using_indexes%'， 查询log_queries_not_using_indexes配置项；

    show VARIABLES like '%log_output%'， 查询log_output配置项；

Profile性能监控【后续在infomation_schema中的 profiling数据表中查看】

    show variables like 'profiling'; 查看性能监控开关；

    set profiling = 'ON'; 打开当前会话的性能监控开关， 一般只有查询特点sql执行时才开启；

    show profiles; 查看当前会话有哪些 profile性能监控；

    show profile； 查看最近一个的性能监控；

    show profile [cpu, block io ... ] for query 12; 根据 show profiles 获取的查询性能的id编号，比如这里的12， 查询 cpu、block.io的执行时间；

innodb相关

    innodb_change_buffer_max_size = 50：change buffer在buffer pool中的最大占比，默认25%，最大50%；

    innodb_log_files_in_group：redo log配置日志文件数量；

    innodb_log_file_size：redo log 每个日志文件的大小；一般配置4个 1G大小的redo 日志；

    innodb_page_size：innodb缓存页（B+树的最小结构）的大小，默认为16k， 可设置的参数有： 64KB，32KB，16KB（默认），8KB和4KB。

    innodb_log_buffer_size：undo 日志缓存区大小，默认为 8M；

    show engine innodb status; 查询innodb引擎的使用情况；

    innodb_flush_log_at_trx_commit = 1：redo log事务持久化的格式，建议设计成1，每次都持久化；

    sync_binlog = 1：binlog 持久化控制参数，建议设置成1，异常重启binlog 不丢失数据；

    binlog_group_commit_sync_delay：组提交执行fsync() 延迟的微妙数，延迟时间越长批量数据越多，磁盘io越少性能越高。

    binlog_group_commit_sync_no_delay_count：组提交执行fsync的批量数，同上；

    show variables like 'transaction_isolation'；查看默认的事务隔离级别；

    SELECT @@tx_isolation; 查看当前的事务隔离级别；

    start transaction with consistent snapshot;开启事务，马上创建视图，一般会等到需要时才创建视图；

    set global transaction isolation level repeatable read;设置全局的事务隔离级别；

    set session transaction isolation level 【事务隔离级别】；设置会话级别的事务隔离级别；

    select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60； 查看数据库的长事务

    innodb_lock_wait_timeout = 50s； innodb行锁等待超时时间，默认为50s，不能设置太短，否则影响其他正常业务当成死锁；

    innodb_deadlock_detect = on；开启死锁检查；

    innodb_change_buffering = all；那些操作可以使用change buffer，all全部 | none | inserts | deletes | updates(新增和修改操作) | purges（缓存在后台发生物理删除操作）

    innodb_doublewrite = 0；启用后（默认），InnoDB将所有数据存储两次，首先存储到 doublewrite缓冲区中，然后存储到实际 数据文件中；

    innodb_flush_method = O_DIRECT；默认值为O_DIRECT（可以设置O_DIRECT_NO_FSYNC），双写缓冲器直接调用操作系统fsync()方法，双写有一次io操作（批量）顺序写入缓冲区；

    innodb_buffer_pool_size：引擎的缓存池大小，一般为物理内存的 60% - 80%；

    innodb_buffer_pool_instances：IBP（InnoDB Buffer Pool）的个数，Linux系统，如果 innodb_buffer_pool_size 大小超过 1GB，innodb_buffer_pool_instances 值就默认为 8；否则，默认为 1；

    innodb_read_io_threads：io线程中的一部分，负责数据业务的读相关的操作，默认4个线程；

    innodb_write_io_threads：io线程的一部分，负责数据库写相关的业务操作，默认4个线程；

    innodb_read_ahead_threshold = 56， 线性预读的页数，默认56页，可以设置 0~64页；

    show variables like 'innodb_read_ahead_threshold'; 查看线性预读的大小；

    innodb_buffer_pool_chunk_size = 128M; 缓存池块儿的大小，默认是128M；

    SHOW GLOBAL STATUS LIKE 'Com_select'; 读取数量

    SHOW GLOBAL STATUS WHERE Variable_name IN ('Com_insert', 'Com_update', 'Com_replace', 'Com_delete'); 写入数量， 主要用于均衡上面的读写线程数量设置   

    SHOW ENGINE INNODB STATUS：查询innpdb引擎的内存使用明细

    innodb_old_blocks_time：mysql缓存页使用的LRU，old区升级为young区的时间判断。默认值1000，单位毫秒；

    innodb_file_per_table = on/off：on表示每个表的数据存储在一个 .ibd的单独文件中，off表示表数据存放在系统表空间中，和数据字典存放在一起。Mysql 5.6.6后的默认值为on；

    innodb_flush_neighbors = 0; 0表示刷脏页时只刷自己，1 表示刷脏页时连同邻居一起刷；

    innodb_max_dirty_pages_pct：脏页比例，默认为 75%；

    innodb_io_capacity：磁盘io能力，参数默认是200，单位是页；

    fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest ；测试磁盘读写能力；    

    innodb_stats_persistent = ON：是否持久化表和索引相关的统计信息，这样在重启mysql之后可以反序列化回来；

    innodb_adaptive_hash_index = on；innodb自适应hash索引的开关，默认开启；

    innodb_adaptive_hash_index_parts = 8；Mysql 5.7版本后，innodb 自适应hash索引是分区的，默认为 8个分区，最大支持512个分区；

 

客户端配置：

    connect_timeout：指的是“连接过程中”的等待时间；

    wait_timeout：指的是“连接完成后，使用过程中”的等待时间；wait_timeout是非交互式连接的空闲超时；

    interactive_timeout是交互式连接的空闲超时。执行时间不计入空闲时间。这两个超时设置得是否一样要看情况；