MySQL DBA：1.1 安装部署规范

1 常用的数据库高可用

在生产环境中，禁止MySQL数据库单点运行，但是针数据的重要程度及场景，会选择不同的数据库集群，如下图所示（星标为常用的数据库集群）：

其中原生的主从因实现简单、成本低，适用于一些要求不高的场景或可以结合其他高可用场景做数据冗余，比如做延迟从库等，是一个廉价的备份方案；

MHA虽然目前已不维护，同时由于自身也存在一些问题，新的集群不建议使用，但是之前旧的系统中被使用的比较广泛，可做了解；

推荐使用官方的InnoDB Cluster，第三方也有很多类似的优秀解决方案，底层原理和架构与InnoDB Cluster的MGR类似，比如XENON等，但既然官方补齐了这一块的缺失，还是推荐使用官方出品；

其他的技术要么复杂度太高，要么太贵，要么用的比较少，踩坑了自己搞不定，其中MariaDB的Galera Cluster使用的也比较多，PXC是percona公司基于Galera Cluster做的自己的集群，但这两个都是多主的，在生产上尝试过，相对有点矜贵，遇见大的SQL比较容易出问题，还是官方的MGR机灵，可以配置一主多从或多主，可以自由选择，而且提供了一个完整的解决方案：InnoDB Cluster=MySQL router（感知主库与从库，通过不同的端口实现读写分离、负载均衡等）+MySQL shell（管理和部署集群）+MGR（MySQL Group Replication，核心组件，实现组复制，故障切换等）；

综上所述，如果不是使用云的PaaS服务的话，强烈推荐InnoDB Cluster，虽然严格意义上并不能称其就是提供了真正的数据高可用方案（但目前确实是相对最靠谱的），而且仍有很多限制：

仅支持InnoDB表，并且每张表一定要有一个主键，用于做write set的冲突检测（InnoDB已经是OLTP的事实标准存储引擎，每张表有主键也是一个常识要求）；
必须打开GTID特性，二进制日志格式必须设置为ROW，用于选主与write set；
COMMIT可能会导致失败，类似于快照事务隔离级别的失败场景；
目前一个MGR集群最多支持9个节点（基本能满足绝大部分生产的要求）；
不支持外键于save point特性，无法做全局间的约束检测与部分部分回滚；
二进制日志不支持binlog event checksum；

1、要求特别高的：Innodb Cluster + 延迟从库

2、要求较高的：Innodb Cluster

3、要求一般的：主从

后续会专门写一系列MySQL InnoDB Cluster的文档（安装部署、原理、使用、监控等）。

2 目录规范

使用二进制安装，采用下图目录规范：

3 参数规范

[client]
#password = 88888888
port = 3306
socket = /data/mysql_3306/data/mysql.sock
 
[mysql]
socket=/data/mysql_3306/data/mysql.sock
 
[mysqld]
user = mysql
port = 3306
datadir = /data/mysql_3306/data
socket = /data/mysql_3306/data/mysql.sock
pid-file = /data/mysql_3306/data/mysqld.pid
symbolic-links=0
 
########basic settings########
server-id = 89
#bind_address = *
autocommit = 1
character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 1000
max_connect_errors = 100
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
join_buffer_size = 1M
#tmp_table_size = 128M
max_allowed_packet = 64M
sql_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 = 120
wait_timeout = 120
read_buffer_size = 8M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
 
########log settings########
log-error = /data/mysql_3306/log/mysqld.err
slow_query_log = 1
slow_query_log_file = /data/mysql_3306/log/slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements=1
log_slow_slave_statements=1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 15
long_query_time = 1
min_examined_row_limit = 0
 
########replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin=/data/mysql_3306/binlog/mysql_binlog
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
#log_slave_updates = 1
binlog_format = ROW
relay_log=/data/mysql_3306/relaylog/localhost-relay-bin
#relay_log_recovery = 1
#binlog_gtid_simple_recovery = 1
#slave_skip_errors = ddl_exist_errors
 
########innodb settings########
#innodb_page_size = 16K
innodb_buffer_pool_size = 2G
#innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lock_wait_timeout = 5
innodb_io_capacity = 1000
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_log_group_home_dir = /data/mysql_3306/redolog
innodb_log_files_in_group = 3
innodb_log_file_size = 500M
innodb_undo_directory = /data/mysql_3306/undolog
#innodb_undo_logs = 128
innodb_undo_tablespaces = 2
#innodb_flush_neighbors = 1
#innodb_log_buffer_size = 16M
#innodb_thread_concurrency = 0
innodb_print_all_deadlocks = 1
 
########semi sync replication 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 = 1
#loose_rpl_semi_sync_slave_enabled = 1
#loose_rpl_semi_sync_master_timeout = 5000
 
[mysqld-5.7]
#innodb_buffer_pool_dump_pct = 40
#innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
#innodb_max_undo_log_size = 1G
#innodb_purge_rseg_truncate_frequency = 128
log_timestamps=system
show_compatibility_56=on

参数说明：

# 是否支持符号链接，即数据库或表可以存储在my.cnf中指定datadir之外的分区或目录，为0不开启
symbolic-links=0
# 开启binlog或启用主从和集群的时候必须指定，建议值设置为IP+端口，每个节点必须不同
server-id = 89
# mysql监听的ip地址，如果是127.0.0.1，表示仅本机访问，默认*，建议*
#bind_address = *
# 开启自动提交，默认开启，建议开启
autocommit = 1
# 服务器默认字符集，默认latin1，建议utf8mb4
character_set_server=utf8mb4
# 禁用DNS主机名查找，默认关闭，建议开启
skip_name_resolve = 1
# mysql最大连接数，默认151，建议2000
max_connections = 1000
# 最大连接错误次数（默认100），可有效反的防止dos攻击，建议1000
max_connect_errors = 100
# 数据库事务隔离级别，默认REPEATABLE-READ，建议READ-COMMITTED
# 1.READ-UNCOMMITTED(读取未提交内容)级别
# 2. READ-COMMITTED（读取提交内容）
# 3. REPEATABLE-READ(可重读)
# 4.SERIERLIZED(可串行化)
# 默认级别REPEATABLE-READ，Oracle为READ-COMMITTED
transaction_isolation = READ-COMMITTED
# 避免表中有timestamp字段，更新表其他字段时，timestamp字段自动更新为系统时间，默认关闭，建议开启
explicit_defaults_for_timestamp = 1
# 当join是ALL,index,rang或者Index_merge的时候使用的buffer，也被称为FULL JOIN，默认256K，建议1M
join_buffer_size = 1M
# 规定了内部内存临时表的最大值，每个线程都要分配。（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值）如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下，默认16M，根据实际情况调整
#tmp_table_size = 128M
# 服务器和客户端一次传送数据包的过程中最大限制
max_allowed_packet = 64M
# 定义MySQL应支持的sql语法，对数据的校验等，限制一些所谓的“不合法”的操作
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
# 服务器关闭交互式连接前等待活动的秒数，默认28800（8小时），建议根据实际情况设置
interactive_timeout = 120
# 服务器关闭非交互连接之前等待活动的秒数，默认28800（8小时），建议根据实际情况设置
wait_timeout = 120
# 顺序读缓冲区大小，默认128K
read_buffer_size = 8M
# 随机读缓冲区大小，默认256K
read_rnd_buffer_size = 8M
# 是一个connection级参数，排序缓冲区大小，默认256K，建议默认
sort_buffer_size = 8M
# 控制写redo日志的策略（默认1）：
# 0 每秒一次写入OS cache并刷盘到redo logfile：无论是MySQL还是操作系统Crash都可能损失1秒的数据；
# 1 每次事务提交写入OS cache并刷盘到redo logfile：最多损失1个事务（最安全也写性能最低）；
# 2 每次事务提交写入OS cache但OS cache每秒刷盘到redo logfile（MySQL Crash但操作系统未Crash则和1是一样的，如果操作系统Crash则和0是一样的）；
innodb_flush_log_at_trx_commit = 1
 
########log settings########
log-error = /data/mysql_3306/log/mysqld.err
# 开启慢查询日志收集
slow_query_log = 1
# 慢查询日志位置
slow_query_log_file = /data/mysql_3306/log/slow.log
# 记录未使用索引的语句
log_queries_not_using_indexes = 1
# 记录那些慢的optimize table，analyze table和alter table语句
log_slow_admin_statements = 1
# 记录由Slave所产生的慢查询
log_slow_slave_statements=1
# 设定每分钟记录的未使用索引的语句数目，超过后只记录语句数量和花费的总时间，默认0（记录所有慢SQL）
log_throttle_queries_not_using_indexes = 10
# 日志自动过期清理天数，默认0（不自动清除）
expire_logs_days = 15
# 设置超过几秒算慢查询，默认10S
long_query_time = 1
# 对于查询扫描行数小于此参数的SQL，将不会记录到慢查询日志中，默认0（不对扫描行数做限制）
min_examined_row_limit = 0
 
########replication settings########
# 从机保存主节点信息方式，设成file时会生成master.info和relay-log.info2个文件，设成table，信息就会存在mysql.master_slave_info表中。不管是设置的哪种值，都不要移动或者编辑相关的文件和表
master_info_repository = TABLE
# 用于保存slave读取relay log的位置信息，可选值为“FILE”、“TABLE”
relay_log_info_repository = TABLE
# binlog的保存位置，不能指定确定的文件名如mysql-bin.log，只能指定位置和前缀
log_bin=/data/mysql_3306/binlog/mysql_binlog
# 控制写binlog的方式：
# 0，由OS自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
# n，n次事务提交后，将binlog_cache中的数据强制写入binglog
# 默认1，最安全但写性能最差
# 此参数对主库的数据安全无影响，但可能影响从库数据同步的速度和丢失可能
sync_binlog = 1
# 开启GTID
gtid_mode = on
# 强制GTID的一致性
enforce_gtid_consistency = 1
# slave更新是否记入日志，在做双主架构或级联从库时开启，默认OFF
#log_slave_updates = 1
# binlog日志格式，可选值“MIXED”、“ROW”、“STATEMENT”，默认ROW
binlog_format = ROW
# 从库保存同步中继日志的位置
relay_log=/data/mysql_3306/relaylog/localhost-relay-bin
# 当slave从库relay-log损坏，导致部分中继日志未处理，则自动放弃所有未执行的relay-log，并重新从master上获取日志，以保证relay-log的完整性，默认OFF
#relay_log_recovery = 1
# mysql启动或重启时，搜寻GTIDs时是如何迭代使用binlog文件，开启会提升mysql执行恢复的性能，默认ON
#binlog_gtid_simple_recovery = 1
# 跳过指定error类型，设成all跳过所有错误，默认OFF
#slave_skip_errors = ddl_exist_errors
 
########innodb settings########
# 默认16K，建议默认
#innodb_page_size = 16K
# innodb内存缓冲池大小，默认128M，建议服务器内存70%-80%
innodb_buffer_pool_size = 2G
# innodb_buffer_pool_size<1G默认1,大于1G默认8，建议默认
#innodb_buffer_pool_instances = 8
# 启动MySQL服务时，将本地热数据加载到InnoDB缓冲池中，默认开启
innodb_buffer_pool_load_at_startup = 1
# 停止MySQL服务时，将InnoDB缓冲池中的热数据保存到本地硬盘，默认开启
innodb_buffer_pool_dump_at_shutdown = 1
# 事务获取资源超时时间，单位是秒，默认50S
innodb_lock_wait_timeout = 5
# 决定mysql的tps，默认200，一般生产数据库都会做raid10，建议1000
# 一般生产系统保持innodb_io_capacity_max值的一半
innodb_io_capacity = 1000
# 决定mysql的tps，默认2000，建议默认
# 参考：sata/sas：200，sas raid10: 2000，ssd：8000，fusion-io（闪存卡）：25000-50000
innodb_io_capacity_max = 2000
# 
innodb_flush_method = O_DIRECT
# redo log路径
innodb_log_group_home_dir = /data/mysql_3306/redolog
# redo日志组数量，默认2，建议3
innodb_log_files_in_group = 3
# redo日志大小，默认48M，根据系统繁忙程度，经验值500M
innodb_log_file_size = 500M
# undo log路径
innodb_undo_directory = /data/mysql_3306/undolog/
# 回滚段数量，默认128，建议默认
#innodb_undo_logs = 128
# 创建undo文件数量，默认0，写入undo使用系统表空间，即ibdata1，建议设置多个
innodb_undo_tablespaces = 2
# InnoDB存储引擎在刷脏页时，检测该页所在区，如果有脏页则一起刷，将多个IO写操作合并为一个。对于传统机械硬盘建议使用，而对于固态硬盘可以关闭。默认1，建议1
#innodb_flush_neighbors = 1
 
# 事务在内存中的缓冲区大小，一般1秒钟写到磁盘1次，默认16M，建议默认
#innodb_log_buffer_size = 16M
# 并发线程数，取值范围0~1000，默认0（表示无并发检查），建议默认值
#innodb_thread_concurrency = 0
# 死锁日志存放到error_log配置的文件里面，默认OFF，建议开启
innodb_print_all_deadlocks = 1
 
########semi sync replication settings########
# 指定mysql的插件目录，默认basedir/lib
#plugin_dir=/usr/local/mysql/lib/plugin
# 指定载入哪些插件
#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
# 主库上是否开启semisync
#loose_rpl_semi_sync_master_enabled = 1
# 备库是否开启semisync
#loose_rpl_semi_sync_slave_enabled = 1
# 单位毫秒，master在超时之前没有收到确认，将恢复到异步复制
#loose_rpl_semi_sync_master_timeout = 5000
 
[mysqld-5.7]
#innodb_buffer_pool_dump_pct = 40
# 默认与innodb_buffer_pool_instances相关，建议保持默认
#innodb_page_cleaners = 4
# 是否开启在线回收undo log日志文件，支持动态设置，必须innodb_undo_tablespaces>=2
innodb_undo_log_truncate = 1
# 超过这个阀值，触发truncate动作，truncate后空间缩小到10M，默认是1G，建议默认
#innodb_max_undo_log_size = 1G
# 控制回收undo log的频率，指定purge操作被唤起多少次之后释放rollback segments，默认值128, 建议默认
#innodb_purge_rseg_truncate_frequency = 128
# 控制日志的显示时间，5.7.2 之后默认UTC，导致日志中记录时间有时差，修改为SYSTEM解决问题
log_timestamps=system
# 从mysql5.7.6开始information_schema.global_status已经开始被舍弃，为了兼容性，需设置开启，默认关闭
show_compatibility_56=on