mysql 参数怎么作为表查询 mysql查看参数

持续更新，积累自己对参数的理解

【1】my.cnf参数

[client]
port = 3306
socket = /mysql/data/3306/mysql.sock
default-character-set=utf8
[mysql] #mysql 命令参数auto-rehash　　#可以使用tab键自动补全表名、列名等
default-character-set = utf8
connect-timeout = 10
prompt=\U|\d> #mysql登录上之后，显示用户名与数据库名
[mysqld]　　#mysqld服务启动命令参数#【0】目录文件基本设置
basedir=/mysql/app/mysql　　#mysql基本目录(文件解压所在目录)
datadir=/mysql/data/3306/data　　#mysql数据目录
socket=/mysql/data/3306/mysql.sock　　#sock套接字文件位置
pid-file=/mysql/data/3306/mysql.pid　　#pid文件存放位置
port=3306　　#端口号lower_case_table_names = 1 #【1】表名存储在磁盘是小写的，字符比较的时候不区分大小写。【0】表名存储为给定的大小，比较是区分大小写的 【2】表名存储为给定的大小写但是比较的时候是小写的   linux默认为0，windows下默认是1，mac Os 下默认是2
default-storage-engine=INNODB　　#设置默认存储引擎
max-allowed-packet = 32M　　#备份的时候，很需要调整这个参数
default-timezone = name　　#为MySQL服务器设置一个地理时区(如果它与本地计算机的地理时区不一样)。比如：default-time-zone = '+8:00'
secure_auth = 1 #导出开关，1为开启，0为禁用
secure_file_priv=/tmp #null 为禁用，/path 为指定导出位置(只能导出到这select output file),如果不能用，请查看selinux是否关闭
default_authentication_plugin=mysql_native_password #修改默认加密方式 【8.0以后】
auto_increment_increment控制列中的值的增量值，也就是步长。
auto_increment_offset确定AUTO_INCREMENT列值的起点，也就是初始值。
#语言设置character-set-server=utf8　　#默认服务端字符集
collation_server=utf8_general_ci #默认服务端排序规则
skip-character-set-client-handshake　　#不受client字符集影响，使用sever端字符集
#lanuage=name #用指定的语言显示出错误信息，默认为英语
#通信、权限、执行
autocommit=1　　#是否自动提交事务，5.6这个参数需要注释掉才方便安装初始化
safe-user-create　　#只有在mysql.useri数据库表上拥有insert权限的用户才能使用GRANT命令；这是一种双保险机制(且此用户还必须有with grant option权限)#shared-memory　　#共享内存通信，仅仅适用于windows
#skip-grant-tables　　#跳过权限表，可以免密进入mysql，windows下可能需要结合shared-memory来使用
#skip-host-cache　　#不使用高速缓存区来存放主机名和IP地址的对应关系。
#skip-networking　　#不允许远程TCP连接，只允许通过一个套接字文件(Unix/Linux系统)或通过命名管道(Windows系统)进行本地连接
user=mysql　　#mysqld程序在启动后将在给定UNIX/Linux账户下执行
transaction-isolation=read-committed #事务的隔离级别
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES　　#sql_mode设置group_concat_max_len =99999　　#sql中group_concat的最大字符串长度
#内存管理、优化、查询缓存、工具组件
bulk_insert_buffer_size=10M　　#为一次插入多条新记录的INSERT命令分配的缓存区长度(默认设置是8M)。大数据量插入可以提高到该值
key_buffer_size = 100M　　#用来存放索引区块的RMA值(默认设置是8M)。
join_buffer_size = 2M　　#在参加JOIN操作的数据列没有索引时为JOIN操作分配的缓存区长度(默认设置是128K)。
max_heap_table_size = 96M　　#HEAP数据表的最大长度(默认设置是16M);超过这个长度的HEAP数据表将被存入一个临时文件而不是驻留在内存里
max_connections = 10000　　#MySQL服务器同时处理的数据库连接的最大数量(默认设置是100)。
query_cache_limit = 4M　　#允许临时存放在查询缓存区里的查询结果的最大长度(默认设置是1M)。
query_cache_size = 256M　　#查询缓存区的最大长度(默认设置是0，不开辟查询缓存区)。
query_cache_type = 1　　#查询缓存区的工作模式:0, 禁用查询缓存区; 1，启用查询缓存区(默认设置); 2，”按需分配”模式，只响应SELECT SQL_CACHE命令。
sort_buffer = 300M　　#为排序操作分配的缓存区的长度(默认设置是2M);如果这个缓存区太小，则必须创建一个临时文件来进行排序。
table_cache = 128　　#同时打开的数据表的数量(默认设置是64)。
tmp_table_size = 96M　　#临时HEAP数据表的最大长度(默认设置是32M);超过这个长度的临时数据表将被转换为MyISAM数据表并存入一个临时文件。
table_open_cache=2048 #同事打开表的缓存数，如果过少而连接过多会导致opening table卡顿，show status like '%open%'；查看当前打开的表，flush tables;会清空表缓存
max_allowed_packet = 16M #网络数据包大小，影响比如单条比较大的数据包会插入失败等等，也影响mysqldump等工具使用
tmp_table_size=128M #tmp_table_size选项来增加一张临时表的大小，例如做高级GROUP BY操作生成的临时表。如果超过该大小值，可能会生成MyISAM的硬盘表导致很慢。默认为16M，可调到64-256最佳，线程独占，太大可能内存不够I/O堵塞
max_heap_table_size=128M #最大堆表/内存表大小，常与tmp_table_size一起用，如果它比 tmp_table_size小，则以它为准；
#告警
#log_warnings=1 #告警信息处理； 【默认值】为1；【为0】表示不记录告警信息；【为1】记录告警信息到错误日志；【大于1】表示各类告警信息，例如有关网络故障的信息和重新连接信息写入错误日志。【用于】常用于5.6之前，8.0.3版本已经移除
log-error_verbosity=3 # 信息处理； 【默认值】8.0.3前默认为3,8.0.4后默认为2【参数值】【为1】错误信息 ；【为2】错误信息和告警信息 ；【为3】错误信息、告警信息和通知信息 ；
#mysql用户的加密方式
default_authentication_plugin=mysql_native_password #参考：mysql8无法用navicat连接(mysql8加密方式的坑)
#【1】日志
#错误日志log-error=/mysql/log/3306/mysql.err #如果错误日志文件不存在，mysql启动或者执行flush logs(如果有就会覆盖现有的)时会创建新的日志文件
#binlog二进制日志参数
log_bin = /mysql/log/3306/binlog/mysql-bin #可以使用reset master重建。这里定义的只是一个索引文件，例如这里给/log/binlog，那么会生成binlog.index，而实际存放数据是类似于binlog.00001 之类的，超出范围会继续新建binlog.00002，以此类推
expire_logs_days=10　　　　 #清除binlog文件中超过10天的内容，默认值为0，表示“没有自动删除”。当mysql启动或刷新二进制日志时可能删除该文件
max_binlog_size=1G　　　 #单个binlog文件的最大容量，如果有大事务运行，运行到一半binlog到达最大值了这个时候不会立马新建，而是会持续写到这个文件里，所以有binlog超出该值也正常。
log_bin_index=/mysql/log/3306/binlog/mysql-bin.index　　#如果不写这个，那么默认就是log-bin制定的路径和名字.index
binlog_format=row　　#(如果是复制必须要开行模式,还有statement,mix模式)
binlog_rows_query_log_events=on　　#二进制查询日志事件记录(即在Binlog里面也会记录详细的SQL)
sync_binlog=1 　　#【1】默认1，mysql每次提交事务之前都会将事务记录同步到二进制日志磁盘上，【2】0为自动控制 【3】N为每N个事务提交落地一次磁盘
binlog_row_image=full　　#(full,minimal,noblob)，分别表示binlog中内容全记录，只记录被操作的，和不记录二进制
binlog-do-db = dbname　　#只把给定数据库里的变化情况记入二进制日志文件，其他数据库里的变化情况不记载。如果需要记载多个数据库里的变化情况，就必须在配置文件使用多个本选项来设置，每个数据库一行。
binlog-ignore-db = dbname　　#不把给定数据库里的变化情况记入二进制日志文件。每制指定一个数据库，需要用本参数多另起一行
#slow慢日志
#log-slow-queries=/mysql/log/3306/slow.log　　#开启慢查询日志并制定路径
slow_query_log = 1　　#开启1开启0关闭，如果写了下面的slow_query_log_file参数，这个参数会默认为1
slow_query_log_file=/mysql/log/3306/slow.log　　#开启后指定日志文件路径
long_query_time = 10　　#慢查询判断时间/s,为0记录所有查询，5.5开始支持毫秒级
log_queries_not_using_indexes=on #默认值off，但on时，将没有使用索引的SQL记录到慢查询日志
log_throttle_queries_not_using_indexes=10　　#默认值0，即默认1分钟刷一次。表示每1分钟记录下所有未使用索引的SQL(5.6以后才有此参数)。建议10分钟以上
log_output=FILE　　#默认是FILE(FILE|TABLE|NONE)，table：mysq..slow_log(该表默认是CSV引擎，建议关闭slow_query_log，再把它转成myIsam
min_examined_row_limit=1000 #扫描记录小于改值的SQL不记录到慢查询日志
log_throttle_queries_not_using_indexes #限制每分钟记录没有使用索引SQL语句的次数 5.6开始支持
log-slow-admin-statement=1 #记录管理操作，如ALTER/ANALYZE TABLE
log_slow_slave_statements=1 #在从服务上开启慢查询日志
log_timestamps=1　　　　　　　　#写入时区时间戳信息 5.7支持
slow_launch_time=2 #慢查询超过的执行时间值
#general通用日志
log=/mysql/log/3306/general.log #可以指定通用日志文件，也可以只写一个log(默认路径)
general_log_file = /mysql/log/3306/mysql.log #也可以用这种方式
general_log = 1 #启用通用日志，如果写了log= ，那么这个默认开启，值为1
#relay log 中继日志
relay_log=/mysql/log/3306/relaylog/mysql-relay.log
relay-log-recover=1 #默认为0，开启为1后：当slave从库宕机后，假如relay-log损坏了(I/O thread crash safe)，
#导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log并在文件中删除这部分，并且重新从master上获取日志，这样就保证了relay-log的完整性
relay_log_info_repository = TABLE #默认为file，设置table后：relay-info将写入到 mysql.slave_relay_log_info(该参数为了保护SQL线程安全)
master_info_repository=table = TABLE #默认为file，#设置为table后，master-info将信息保存到mysql.slave_master_info(该参数为了保护IO线程安全)
#【2】innodb 基本设置、表空间文件设置#skip-innodb　　#不加载InnoDB数据表驱动程序–如果用不着InnoDB数据表，可以用这个选项节省一些内存。
#innodb_data_home_dir = path/file　　#InnoDB主目录，所有与InnoDB数据表有关的目录或文件路径都相对于这个路径。在默认的情况下，这个主目录就是MySQL的数据目录。
innodb-file-per-table　　#启用独立表空间
innodb-open-file = 1000　　#InnoDB数据表驱动程序最多可以同时打开的文件数(默认设置是300)。如果使用了innodb-file-per-table选项并且需要同时打开很多数据表的话，这个数字很可能需要加大。
innodb_data_file_path=ibdata1:1G;ibdata2:1G:autoextend:max:5G #系统表空间、数据字典、redo、没有开启独立表空间时的共享表空间，初始化最大1G但可以扩容
innodb_lock_wait_timeout = 50　　#如果某个事务在等待n秒(s)后还没有获得所需要的资源，就使用ROLLBACK命令放弃这个事务。这项设置对于发现和处理未能被InnoDB数据表驱动程序识别出来的死锁条件有着重要的意义。这个选项的默认设置是50s。
innodb_fast_shutdown=1　　#是否以最快的速度关闭InnoDB，默认设置是1，意思是不把缓存在INSERT缓存区的数据写入数据表，那些数据将在MySQL服务器下次启动时再写入 (这么做没有什么风险，因为INSERT缓存区是表空间的一个组成部分，数据不会丢失)。
#把这个选项设置为0反而危险，因为在计算机关闭时，InnoDB驱动程序很可能没有足够的时间完成它的数据同步工作，操作系统也许会在它完成数据同步工作之前强行结束InnoDB，而这会导致数据不完整。
#innodb 日志
innodb_log_group_home_dir = /mysql/data/3306/data　　#用来存放InnoDB日志文件(redo)的目录路径(如ib_logfile0、ib_logfile1等)。在默认的情况下，InnoDB驱动程序将使用 MySQL数据目录作为自己保存日志文件的位置。
innodb_log_files_in_group = 2　　#redo文件的个数，默认为2。innodb以轮转方式依次填写
innodb_flush_log_at_trx_commit=1　　#【1】默认1，每当有事务提交时，mysql都会把内存中的log buffer的数据写入log file并持久化到磁盘上去 【2】0为操作系统自动控制 【3】N为每N个事务提交落地一次磁盘
#innodb_flush_method = x　　#InnoDB日志文件的同步办法(仅适用于UNIX/Linux系统)。这个选项的可取值有两种: fdatasync，用fsync()函数进行同步; O_DSYNC，用O_SYNC()函数进行同步。
innodb_undo_directory = /undolog/ #undo独立存放文件目录，mysql5.6后，innodb_undo_tablespace参数可以独立设置undo 日志，
innodb_undo_logs = 128　　　　　　#单位M，
innodb_undo_tablespaces = 3　　 #开启独立存放undo的参数，这里的值代表是文件个数
#innodb 缓存区的设置和优化innodb_log_buffer_pool_size = 100M　　#缓存池大小(默认设置是8MB)，如果独立DB，建议设置成80%物理内存大小innodb_log_buffer_size = 16M　　#事务日志文件写操作缓存区的最大长度(默认设置是1MB)。
innodb_additional_men_pool_size = 100M　　#为用于内部管理的各种数据结构分配的缓存区最大长度(默认设置是1MB)。innodb_file_io_threads = 4　　#I/O操作(硬盘写操作)的最大线程个数(默认设置是4)。
innodb_thread_concurrency = 8　　#InnoDB驱动程序能够同时使用的最大线程个数(默认设置是8)。
innodb_buffer_pool_load_at_startup = 1 #启动MySQL服务时，MySQL将本地热数据加载到InnoDB缓冲池中预热
innodb_buffer_pool_dump_at_shutdown = 1 #停止MySQL服务时，InnoDB将InnoDB缓冲池中的热数据保存到本地硬盘#
innodb_buffer_pool_dump_pct = 40 #关闭mysql服务时，转储活跃使用的innodb buffer pages的比例，默认25%(即100页热数据转储25页)；配合innodb_buffer_pool_load_at_startup和innodb_buffer_pool_dump_at_shutdown 两个参数同时使用#
innodb_page_size=8096 #innodb页大小，默认为16K，这里设置成了8K
innodb_change_buffer_max_size=25 #innodb的insert buffer/change buff ,默认25%，标识最大占innodb buffer pool size的 25%
innodb_change_buffering=all #inserts、deletes、purges、changes(inserts和deletes)、all(默认)、none。
#复制过滤参数
replicate-do-table = dbname.tablename　　#只对表进行镜像处理，但数据库和数据库表名字里允许出现通配符”%” (例如: test%.%–对名字以”test”开头的所有数据库里的所以数据库表进行镜像处理)。
replicate-do-db = dbname　　#只对这个数据库进行镜像处理，多行进行逗号分隔或全部另起一行
replicate-ignore-db = dbname　　#不对这个数据库进行镜像处理。
#如果从库是要在线复制过滤，stop slave;然后按如下形式操作：
#CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('erp_ivedeng_com.T_BRAND','erp_ivedeng_com.T_DEPARTMENT_FEE_ITEMS');
replicate-ignore-table = dbname.tablename　　#忽略这个数据表进行复制处理。也可以 dbname.%，表示该库下所有表
replicate_wild_do_table=table_name　　　　　　#解决跨库更新问题，需要与 replicate_do_table 同时使用，也可以 dbname.% 表示该库下所有表
replicate-wild-ignore-table = dbname.tabname　　#忽略跨库更新表的复制，需要与 replicate-ignore-db 同时使用，也可以dbname.% 表示该库下所有表
replicate-rewrite-db = db1name > db2name　　#把主控数据库上的db1name数据库镜像处理为从属服务器上的db2name数据库。每个另起一行
#m230渠道
#replicate-wild-do-table=m230:x_uat.%
#replicate-wild-do-table=m230:x_uat_read.%
#replicate-wild-do-table=m230:perf_db.%
report-host = hostname　　#从属服务器的主机名; 这项信息只与SHOW SLAVE HOSTS命令有关–主控服务器可以用这条命令生成一份从属服务器的名单。
slave-compressed-protocol = 1　　#主、从服务器使用压缩格式进行通信–如果它们都支持这么做的话。
slave-skip-errors = n1, n2, …或all　　#即使发生出错代码为n1、n2等的错误，镜像处理工作也继续进行(即不管发生什么错误，镜像处理工作也继续进行)。
#如果配置得当，从属服务器不应该在执行 SQL命令时发生错误(在主控服务器上执行出错的SQL命令不会被发送到从属服务器上做镜像处理);
#如果不使用slave-skip-errors选项，从属服务器上的镜像工作就可能因为发生错误而中断，中断后需要有人工参与才能继续进行。(注意，gtid无法使用)
#半同步复制
plugin_dir=/mysql/app/mysql/lib/plugin/
plugin_load=rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so
#如果是5.7，参数前面加上loose_，如下列，如果是5.6 则直接使用 rpl_semi_sync_master_enabled=1 之类的就好了。
#我这里是5.7就直接做增强半同步了(loseless Semisynchronous )
loose_rpl_semi_sync_master_enabled=1 #MySQL开启主的半同步复制(rpl_semi_sync_master_enabled)
loose_rpl_semi_sync_slave_enabled=1 #MySQL5.6开启从的半同步复制
loose_rpl_semi_sync_master_timeout=5000 #超时5秒，切回异步
#mysql配置插件相关的参数 只能在插件加载之后设置，如果想启动时设置这些参数并且启动后加载插件到配置文件中，可以在参数前加上“loose-”前缀。
rpl_semi_sync_master_wait_for_slave_count=1 #有多少个从返回标识就可以提交：案例：一主多从的情况下只要有1个slave的ACK返回给了主库，就会进行commit
rpl_semi_sync_master_wait_point=AFTER_SYNC #MySQL 5.7的方法，AFTER_SYNC(default，增强半同步) & AFTER_COMMIT(传统半同步)
#GTID 复制
gtid_mode=1　　#开启GTID
enforce_gtid_consistency=1　#on：当发现语句/事务不支持GTID时，返回错误信息　　WARN：发现不支持返回警告　　#off：不检查
log-slave-updates=1 #从库relay log中的重做记录在从库的binlog上记录，如果多个从库在主从切换时建议关掉，避免传输重复日志导致同步错误
#并行复制
slave-parallel-type=LOGICAL_CLOCK #LOGICAL_CLOCK：基于组提交的并行复制方式；DATABASE：兼容MySQL5.6基于 schema级别的并发复制
slave-parallel-workers=4 #4个SQL线程
slave_skip_errors=ddl_exist_errors #跳过DDL错误
slave_preserve_commit_order=1 #Slave 上 commit的顺序保持一致，必须为1，否则可能会有GAP锁产生

【1.1】常规生产my.cnf(5.7)

[client]
user=david
password=88888888[mysqld]
########basic settings########
server-id = 11port= 3306user=mysql
bind_address= 10.166.224.32autocommit= 0character_set_server=utf8mb4
skip_name_resolve= 1max_connections= 800max_connect_errors= 1000datadir= /data/mysql_data
transaction_isolation= READ-COMMITTED
explicit_defaults_for_timestamp= 1join_buffer_size= 134217728tmp_table_size= 67108864tmpdir= /tmp
max_allowed_packet= 16777216sql_mode= "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"interactive_timeout= 1800wait_timeout= 1800read_buffer_size= 16777216read_rnd_buffer_size= 33554432sort_buffer_size= 33554432########log settings########
log_error=error.log
slow_query_log= 1slow_query_log_file=slow.log
log_queries_not_using_indexes= 1log_slow_admin_statements= 1log_slow_slave_statements= 1log_throttle_queries_not_using_indexes= 10expire_logs_days= 90long_query_time= 2min_examined_row_limit= 100########replication settings########
master_info_repository=TABLE
relay_log_info_repository=TABLE
log_bin=bin.log
sync_binlog= 1gtid_mode=on
enforce_gtid_consistency= 1log_slave_updates
binlog_format=row
relay_log=relay.log
relay_log_recovery= 1binlog_gtid_simple_recovery= 1slave_skip_errors=ddl_exist_errors
########innodb settings########
innodb_page_size= 8192innodb_buffer_pool_size=6G
innodb_buffer_pool_instances= 8innodb_buffer_pool_load_at_startup= 1innodb_buffer_pool_dump_at_shutdown= 1innodb_lru_scan_depth= 2000innodb_lock_wait_timeout= 5innodb_io_capacity= 4000innodb_io_capacity_max= 8000innodb_flush_method=O_DIRECT
innodb_file_format=Barracuda
innodb_file_format_max=Barracuda
innodb_log_group_home_dir= /redolog/innodb_undo_directory= /undolog/innodb_undo_logs= 128innodb_undo_tablespaces= 3innodb_flush_neighbors= 1innodb_log_file_size= 4G #undolog/innodb_log_buffer_size= 16777216innodb_purge_threads= 4innodb_large_prefix= 1innodb_thread_concurrency= 64innodb_print_all_deadlocks= 1innodb_strict_mode= 1innodb_sort_buffer_size= 67108864########semisyncreplication settings########
plugin_dir=/usr/local/mysql/lib/plugin
plugin_load= "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"loose_rpl_semi_sync_master_enabled= 1loose_rpl_semi_sync_slave_enabled= 1loose_rpl_semi_sync_master_timeout= 5000[mysqld-5.7]
innodb_buffer_pool_dump_pct= 40innodb_page_cleaners= 4innodb_undo_log_truncate= 1innodb_max_undo_log_size=2G
innodb_purge_rseg_truncate_frequency= 128binlog_gtid_simple_recovery=1log_timestamps=system
transaction_write_set_extraction=MURMUR32
show_compatibility_56=on

【1.2】常规生产mysql5.6

[client]port= 3306socket= /var/lib/mysql/mysql.sock[mysqld]socket= /var/lib/mysql/mysql.sock
port= 3306tmpdir= /tmp/tmpmysql
#character set,5.6version setutf8
character_set_server=utf8
init_connect='set names utf8'skip-character-set-client-handshake=1#deafault engine
default_storage_engine=InnoDB
#loginfo
slow_query_log= 1slow_query_log_file= /data/mysqllog/slowlog/slowquery.loglong_query_time= 2
log-error = /data/mysqllog/errorlog/log-error.logskip-external-locking
log_warnings
back_log= 300# fine tuning
skip-name-resolve
max_connections= 5000max_allowed_packet=32M
max_heap_table_size=128M
key_buffer_size=128M
sort_buffer_size=16M
join_buffer_size=16M
net_buffer_length=8K
read_buffer_size=128M
read_rnd_buffer_size=128M
myisam_sort_buffer_size=8M
thread_cache_size= 16#thread_concurrency= 24table_open_cache= 4096#mysql5.6new feature
explicit_defaults_for_timestamp=true
#*Query Cache Configuration
query_cache_limit=4M
query_cache_size=4096M
query_cache_type= 1tmp_table_size=128M
#innodb settings
innodb_data_home_dir= /data/idbdata
innodb_log_group_home_dir= /data/idbdata/innodb_data_file_path=ibdata1:100M:autoextend
innodb_fast_shutdown= 1innodb_file_per_table= 1innodb_file_io_threads= 4innodb_open_files= 2048innodb_buffer_pool_size=10G
#innodb_additional_mem_pool_size=16M
innodb_thread_concurrency= 16innodb_max_dirty_pages_pct= 90innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit= 2innodb_flush_method=0_DIRECT
innodb_lock_wait_timeout= 10innodb_log_files_in_group= 2innodb_log_file_size=64M
innodb_flush_log_at_trx_commit= 0innodb_autoextend_increment= 256innodb_buffer_pool_instances= 12innodb_concurrency_tickets= 5000innodb_old_blocks_time= 1000innodb_lock_wait_timeout= 50#log-bin settingslog-bin = /data/mysqllog/binlog/master-bin.logexpire_logs_days= 5binlog_cache_size=1M
max_binlog_size=10M[mysqldump]# Donot buffer the whole result set in memory before writing it to#file. Required fordumping very large tables
quick
#quote-names
max_allowed_packet=64M
max_connect_errors=1000
[mysql]no-auto-rehash # faster start ofmysql but no tab completition[isamchk]key_buffer=512M
sort_buffer_size=512M
read_buffer=8M
write_buffer=8M[myisamchk]key_buffer=512M
sort_buffer_size=512M
read_buffer=8M
write_buffer=8M[mysqlhotcopy]interactive-timeout

【1.3】跳过某些参数启动

(1)关闭从库启动：mysqld_safe --skip-slave-start &

(2)跳过权限表：mysqld_safe --skip-grant-tables &

【1.4】生产5.7配置文件参考

#forinnodb
#editedby 2011-03-23
[client]port= 3306#socket=/data/mysqldata/sock/mysql.sock2433
socket= /tmp/mysql.sock
#default-character-set=utf8[mysqld]
user =mysql
bind-address= 10.10.4.180port= 3306socket= /data/mysqldata/sock/mysql.sock2433
lower_case_table_names= 1datadir=/data/mysqldata/data/2433/pid-file= /data/mysqldata/var/2433.pid
tmpdir=/data/mysqllog/2433/tmpdir
#add mysql 5.6innodb_print_all_deadlocks= 1skip-external-locking
skip-name-resolve
key_buffer_size=64M
max_allowed_packet=16M
table_open_cache= 2048sort_buffer_size=2M
read_buffer_size=2M
myisam_sort_buffer_size=64M
thread_cache_size= 12query_cache_size=32M
#thread_concurrency= 8max_binlog_size=20M
wait_timeout=86400interactive_timeout=86400max_connections= 5000max_connect_errors= 99999event-scheduler = onslow_query_log= 1long_query_time= 1slow_query_log_file= /data/mysqllog/2433/slow-query.logexpire_logs_days= 30log_bin= /data/mysqllog/binlog/2433/binlog.bin
log_bin_trust_function_creators=1binlog_format=MIXED
#mysql5.6gtid-mode=onenforce-gtid-consistencylog-slave-updatesreplicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%relay-log = /data/mysqldata/data/2433/relay.binlog-bin-trust-function-creators=1server-id = 17421innodb_file_per_table= 1#
innodb_data_home_dir= /data/mysqldata/innodb/data/2433innodb_data_file_path=ibdata1:1G:autoextend
innodb_log_group_home_dir= /data/mysqldata/innodb/log/2433innodb_buffer_pool_size=4000M
innodb_additional_mem_pool_size=20M
innodb_buffer_pool_instances=3innodb_log_file_size=256M
innodb_log_buffer_size=32M
#innodb_mirrored_log_groups= 1innodb_log_files_in_group= 4innodb_flush_log_at_trx_commit= 0innodb_lock_wait_timeout= 50innodb_thread_concurrency= 8innodb_file_io_threads=4sql_mode=""[mysqldump]quick
max_allowed_packet=16M[mysql]no-auto-rehash
prompt=\U|\d>
[isamchk]key_buffer=256M
sort_buffer_size=256M
read_buffer=2M
write_buffer=2M[myisamchk]key_buffer=256M
sort_buffer_size=256M
read_buffer=2M
write_buffer=2M[mysqlhotcopy]interactive-timeout

【2】相关原理

【2.1】mysql中的WAL 从内存日志到磁盘日志的出发机制

# log buffer => log file触发机制

#(1)强制每1s写一次。

#(2)大于log buffer空间1/2的时候

#(3)commit的时候

#(4)log buffer写到1M的时候

#(5)日志先写机制，后台的脏页写磁盘之前，就先把日志写过来。

【2.2】flush disk 存盘二进制日志信息的流程

写binlog流程如下：

# 数据操作buffer pool> binlog buffer > file system buffer > commit > binlog file在写binlog file之前，commit有3种模式，分别是：0,1，N

sync_binlog=0:mysql不会主动同步Binlog内容到磁盘文件中，而是依赖操作系统刷新文件的机会刷binlog file.一般是1S/次

sync_binlog=1:默认值，mysql主动刷新file system buffer到磁盘上的binlog file中，每1次commit，就主动fsync一次。

sync_binlog=N:非0非1，mysql主动刷新file system buffer到磁盘上的binlog file中，每N次commit，就主动fsync一次。

【2.3】先写redo还是先写binlog？

图一 二阶段提交

1)Storage Engine(InnoDB) transaction prepare阶段：即sql语句已经成功执行并生成xid信息以及redo和undo的内存日志

2)Binary log日志提提交

2.1)  write()将binary log内存日志数据写入文件系统缓存

2.2)  fsync()将binary log 文件系统缓存日志数据永久写入磁盘

3)Storage Engine(InnoDB)内部提交

commit阶段在存储引擎内提交( innodb_flush_log_at_trx_commit控制)使undo和redo永久写入磁盘

处理

一旦步骤2中的操作完成，就确保了事务的提交，即使在执行步骤3时数据库发送了宕机。

即binlog落盘成功,就算redo未落盘成功,那么事务也算是提交成功了.

binlog落盘条件：参数sync_binlog： 0每秒落盘，1每次commit落盘  n 每n个事物落盘

此外需要注意的是，每个步骤都需要进行一次fsync操作才能保证上下两层数据的一致性。步骤2的fsync参数由sync_binlog控制，步骤1的fsync由参数innodb_flush_log_at_trx_commit控制。(双1配置)

两阶段提交:先写redo-buffer再写binlog 并落盘最后落盘redo-buffer.

最终:mysql在落盘日志的时候,先落盘binlog,再落盘redo.

开启Binary log的MySQL在crash recovery时：

当事务在prepare阶段crash，数据库recovery的时候该事务未写入Binary log并且存储引擎未提交，将该事务roll back。

当事务在Binary log日志已经fsync()永久写入二进制日志时crash，但是存储引擎未来得及commit.

此时MySQL数据库recovery的时候将会从二进制日志的Xid(MySQL数据库内部分布式事务XA)中获取提交的信息重新将该事务重做并commit使存储引擎和二进制日志始终保持一致。

先写redo LOG，再写binlog。如果2个有任一失败，就会回滚。

sync_binlog配合另一个参数innodb_flush_log_at_trx_commit;

如果都是1，数据库挂了以后，最多只丢一条一句或一个事务的数据；

show variables like'innodb_flush_log_at_trx_commit';

但会影响性能，只能说在数据要求非常高的场景下使用。

【2.4】mysql行格式

Mysql 目前有4种行格式：Redundant、Compact、Dynamic、Compressed

Version 5.6 已经默认使用 Compact

Version 5.7 默认使用Dynamic

Redundant 是比较老的数据格式，Compressed 不能应用在System data；所以Compact和Dynamic应用较广泛；

规定(只讨论InnoDB)：

1、存储单元为页(page)，16KB(16384B)

2、每页至少需要存两条数据

3、每条记录都会有记录头

4、记录几乎都会有3个隐藏列(rowId，transactionId，rollPointer)

Compact：

变长字段(记录的长度)列表  +  NULL列表  + 记录头信息  + 列值

变长字段(记录的长度)列表：

采用1-2个字节来表示一个字段的长度，逆序；

字段最大长度<= 255字节用1个字节表示；大于255字节，但是实际使用字节<=127，也使用1个字节来表示；其他情况使用2个字节来表示；

char如果是变长字符集(GBK、UTF8)也会需要使用变长字段列表来存储字段使用的长度；

NULL列表：

逆序，存储每条记录中允许为NULL的字段，将实际为NULL的字段用1表示，实际不为NULL的字段用0表示；

每一列不是用一个字节来表示，而是用一个位来表示；

记录头信息：

使用5个字节来表示；

主要包含：该记录是否被删除，记录类型，下一条记录的相对偏移量；

Redundant：

字段长度偏移列表 + 记录头 + 列值

字段长度偏移列表，用来记录每列的结束位置；

真实数据(整条记录)<=127B 用1个字节记录，其他使用2个字节；

是使用1个字节还是2个字节来存储，该信息放在记录头的(1byte_offs_flag属性上)

每个列记录的第一个bit 使用来表示该列是否位NULL；

Dynamic：

与Compact行格式很像，差异在于页面溢出的处理上；

Compressed：

在于Dynamic使用了压缩算法；

页溢出：

因为每页16KB，至少存储两行，所以每行大概有8KB的数据；抛开记录头信息等，大致每列超过768B就会产生页溢出；

Compact：

1、会将溢出的数据单独放入一个页；外加20B存储额外页的信息(plus the 20-byte pointer to the externally stored part)

2、索引可以使用前768B

Dynamic：

2、如果页溢出，则使用20B存储整个列信息(列数据都存储在溢出页上)(with the clustered index record containing only a 20-byte pointer to the overflow page)

3、可以使用前3072B字符的索引(--innodb-large-prefix决定)

总的说来，Redundant使用更多的字节数量来存储一条记录(字段长度偏移列表)，页溢出会使用更多的20B来表示溢出部分(或整列)数据，会使得索引数据更大；