Mycat高可用解决方案一（mysql安装）
Mycat关键特性
关键特性
- 支持SQL92标准
- 支持MySQL、Oracle、DB2、SQL Server、PostgreSQL等DB的常见SQL语法
- 遵守Mysql原生协议，跨语言，跨平台，跨数据库的通用中间件代理。
- 基于心跳的自动故障切换，支持读写分离，支持MySQL主从，以及galera cluster集群。
- 支持Galera for MySQL集群，Percona Cluster或者MariaDB cluster
- 基于Nio实现，有效管理线程，解决高并发问题。
- 支持数据的多片自动路由与聚合，支持sum,count,max等常用的聚合函数,支持跨库分页。
- 支持单库内部任意join，支持跨库2表join，甚至基于caltlet的多表join。
- 支持通过全局表，ER关系的分片策略，实现了高效的多表join查询。
- 支持多租户方案。
- 支持分布式事务（弱xa）。
- 支持XA分布式事务（1.6.5）。
- 支持全局序列号，解决分布式下的主键生成问题。
- 分片规则丰富，插件化开发，易于扩展。
- 强大的web，命令行监控。
- 支持前端作为MySQL通用代理，后端JDBC方式支持Oracle、DB2、SQL Server 、 mongodb 、巨杉。
- 支持密码加密
- 支持服务降级
- 支持IP白名单
- 支持SQL黑名单、sql注入攻击拦截
- 支持prepare预编译指令（1.6）
- 支持非堆内存(Direct Memory)聚合计算（1.6）
- 支持PostgreSQL的native协议（1.6）
- 支持mysql和oracle存储过程，out参数、多结果集返回（1.6）
- 支持zookeeper协调主从切换、zk序列、配置zk化（1.6）
- 支持库内分表（1.6）
- 集群基于ZooKeeper管理，在线升级，扩容，智能优化，大数据处理（2.0开发版）。
- 一个彻底开源的，面向企业应用开发的大数据库集群
- 支持事务、ACID、可以替代MySQL的加强版数据库
- 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群
- 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
- 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
- 一个新颖的数据库中间件产品
- 支持对Mycat、Mysql性能监控
- 支持对Mycat的JVM内存提供监控服务
- 支持对线程的监控
- 支持对操作系统的CPU、内存、磁盘、网络的监控
- 完全实现分布式事务，完全的支持分布式。
- 通过Mycat web（eye）完成可视化配置，及智能监控，自动运维。
- 通过mysql 本地节点，完整的解决数据扩容难度，实现自动扩容机制，解决扩容难点。
- 支持基于zookeeper的主从切换及Mycat集群化管理。
- 通过Mycat Balance 替代第三方的Haproxy，LVS等第三方高可用，完整的兼容Mycat集群节点的动态上下线。
- 接入Spark等第三方工具，解决数据分析及大数据聚合的业务场景。
- 通过Mycat智能优化，分析分片热点，提供合理的分片建议，索引建议，及数据切分实时业务建议。
什么是MYCAT
- 一个彻底开源的，面向企业应用开发的大数据库集群
- 支持事务、ACID、可以替代MySQL的加强版数据库
- 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群
- 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
- 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
- 一个新颖的数据库中间件产品
MYCAT监控
- 支持对Mycat、Mysql性能监控
- 支持对Mycat的JVM内存提供监控服务
- 支持对线程的监控
- 支持对操作系统的CPU、内存、磁盘、网络的监控
目标
低成本的将现有的单机数据库和应用平滑迁移到“云”端，解决数据存储和业务规模迅速增长情况下的数据瓶颈问题。
1.6版本架构
- 完全实现分布式事务，完全的支持分布式。
- 通过Mycat web（eye）完成可视化配置，及智能监控，自动运维。
- 通过mysql 本地节点，完整的解决数据扩容难度，实现自动扩容机制，解决扩容难点。
- 支持基于zookeeper的主从切换及Mycat集群化管理。
- 通过Mycat Balance 替代第三方的Haproxy，LVS等第三方高可用，完整的兼容Mycat集群节点的动态上下线。
- 接入Spark等第三方工具，解决数据分析及大数据聚合的业务场景。
- 通过Mycat智能优化，分析分片热点，提供合理的分片建议，索引建议，及数据切分实时业务建议。
优势
基于阿里开源的Cobar产品而研发，Cobar的稳定性、可靠性、优秀的架构和性能以及众多成熟的使用案例使得MYCAT一开始就拥有一个很好的起点，站在巨人的肩膀上，我们能看到更远。业界优秀的开源项目和创新思路被广泛融入到MYCAT的基因中，使得MYCAT在很多方面都领先于目前其他一些同类的开源项目，甚至超越某些商业产品。
MYCAT背后有一支强大的技术团队，其参与者都是5年以上资深软件工程师、架构师、DBA等，优秀的技术团队保证了MYCAT的产品质量。
MYCAT并不依托于任何一个商业公司，因此不像某些开源项目，将一些重要的特性封闭在其商业产品中，使得开源项目成了一个摆设。
Mycat高可用方案
 
MyCAT 和 TDDL、Amoeba、Cobar 癿架构比较
部署环境
系统版本：
操作系统：CentOS-6.4 操作用户：root/hadoop 系统 IP：192.168.199.101 主机名：mysql-01 配置：4 核、2G 内存
软件版本：
MySQL 版本：mysql-5.7.9 JDK版本：jdk1.7 HAProxy版本：haproxy-1.5.19.tar.gz mycat版本：1.6.5 keepalived版本：
一、服务器配置：
1、配置网络
# vi /etc/sysconfig/network-scripts/ifcfg-eth0
DEVICE=eth0 TYPE=Ethernet ONBOOT=yes NM_CONTROLLED=no BOOTPROTO=static #HWADDR=00:50:56:a1:12:53 IPADDR=192.168.199.101 GATEWAY=192.168.199.2 NETMASK=255.255.255.0 DNS1=8.8.8.8 DNS2=192.168.199.2
2、设置主机名
# vi /etc/sysconfig/network
NETWORKING=yes HOSTNAME=mysql-01
3、设置 IP 与主机名的映射，
如果服务器启动但您无法连接到它，请确保您有一个条目/etc/hosts ，如下所示：
# vi /etc/hosts
127.0.0.1 localhost
127.0.0.1 mysql-01
192.168.199.101 mysql-01
4、两台数据库服务器的的 selinux 都要 disable
（永久关闭 selinux，请修改/etc/selinux/config，将 SELINUX 改为 disabled）
# vi /etc/selinux/config
5、重启操作系统
# reboot
======================================
二、mysql安装 MySQL5.7：
1、使用下面的命令检查是否安装有 MySQL Server:
# rpm -qa | grep mysql
mysql-libs-5.1.73-3.el6_5.x86_64
如果是 CentOS7 以上，请使用以下命令查看：
# rpm -qa | grep mariadb
mariadb-libs-5.5.41-2.el7_0.x86_64
（因为没有 MySQL 服务，因此没必要卸载。mysql-libs 是 MySQL 的必要包）
（如果有的话可通过下面的命令来卸载掉，rpm -e mysql --nodeps //普通删除模）
2、改防火墙设置，打开 3306 端口：
# vi /etc/sysconfig/iptables
增加如下行：
# MySQL
-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
或者关闭防火墙：
（1）即时生效，重启后失效
开启： service iptables start
关闭： service iptables stop 或者systemctl stop firewalld.service
（2）重启后生效
开启： chkconfig iptables on
关闭： chkconfig iptables off
重启防火墙：
# service iptables restart
3、新增 mysql 用户组：
# groupadd mysql
4、新增 mysql 用户，并添加到 mysql 用户组：
# useradd -r -g mysql mysql
5、新建 MySQL 执行文件目录(后面会把编译好的 mysql 程序安装到这个目录)：
# mkdir -p /usr/local/mysql
（-p 参数的作用是：如果最终目录的父目录不存在也会一并创建,编译安装方式才需要手动创建，非编译方式省略此步骤）
6、新建 MySQL 数据库数据文件目录：
# mkdir -p /home/mysql/data
# mkdir -p /home/mysql/logs
# mkdir -p /home/mysql/temp
（注意：上面的 logs 及 temp 目录是为了以后将 MySQL 的数据文件与执行程序文件分离，
如果你打算设置到不同的路径，注意修改对应的执行命令和数据库初始化脚本。正式生产环
境，建议数据目录和日志目录都使用单独的分区来挂载，不同分区属于不同的磁盘或磁盘组。）
7、下载mysql免编译安装包：
wget https://downloads.mysql.com/archives/get/file/mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz
8、解压：
tar -zxvf mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
文件夹重命名：
# cd /usr/local/
# mv mysql-5.7.9-linux-glibc2.5-x86_64/ mysql/
9、增加 PATH 环境变量搜索路径：
# vi /etc/profile
##在 profile 文件末尾增加两行
# mysql env param
PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH
使 PATH 搜索路径立即生效：
# source /etc/profile
10、修改 mysql 目录拥有者为 mysql 用户：
# chown -Rf mysql:mysql /usr/local/mysql # chown -Rf mysql:mysql /home/mysql
11、进入 mysql 执行程序的安装路径：
# cd /usr/local/mysql
执行初始化配置脚本，创建系统自带的数据库和表（注意：路径/home/mysql/data 需要换成你自定定义的数据库存放路径）：
数据初始化变更：
mysq5.7之前：
# scripts/mysql_install_db --user=mysql \ --basedir=/usr/local/mysql \ --datadir=/home/mysql/data
mysq5.7之后：
bin/mysqld --initialize --user=mysql \ --basedir=/usr/local/mysql \
--datadir=/home/mysql/data
记录登录初始化密码： root@localhost: zB<3Ek+e7FU0
12、拷贝配置文件到 /etc目录下：
cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
初始化脚本在/usr/local/mysql/下生成了配置文件 my.cnf，需要更改该配置文件的所有者：
# ls -lah
chown -Rf mysql:mysql /etc/my.cnf
注意：
（1）在启动 MySQL 服务时，会按照一定次序搜索 my.cnf，先在/etc 目录下找，找不到则会搜索 mysql 程序目录下是否有 my.cnf
（2）需要注意 CentOS 6 版操作系统的最小安装完成后，即使没有安装 mysql，在/etc 目录下也会存在一个 my.cnf 文件，建议将此文件更名为其他的名字。
否则该文件会干扰源码安装的 MySQL 的正确配置，造成无法启动。修改/etc/my.cnf 操作如下：
可以：mv /etc/my.cnf /etc/my.cnf.bak
也可以：删除掉/etc/my.cnf 这个文件：rm /etc/my.cnf
如果你需要用于生产环境，不要急着做下面的 mysql 启动操作。建议把上一步骤中 mysql初始化生成的/usr/local/mysql/my.cnf 删除，然后把你优化好的 mysql 配置文件 my.cnf 放到/etc下。（我们这里使用/etc/my.cnf）
13、编辑/etc/my.cnf: # vi /etc/my.cnf
#######################################################################
# 详细参数参考地址：
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
#
#######################################################################
[client]
port = 3306
default-character-set=utf8mb4
socket=/var/lib/mysql/mysql.sock
[mysql]
port = 3306
default-character-set=utf8mb4
socket=/var/lib/mysql/mysql.sock
[mysqld]
######################################################################
# basic settings 基本设置
# 1、lower_case_table_names：库名、表名是否区分大小写。
# 默认为0，设置1，不区分大小写，创建的表、数据库都以小写形式存放磁盘。
# 2、skip-grant-table：跳过数据库权限验证，找回密码时有用
######################################################################
bind-address = 0.0.0.0
port = 3306
lower_case_table_names=1
#skip-grant-tables
#skip-networking
explicit_defaults_for_timestamp=1
table_open_cache = 8000
######################################################################
# character set 编码
######################################################################
character-set-server = utf8mb4
collation-server = utf8mb4_bin
######################################################################
# dir 数据目录
# 有条件的情况下，目录分别挂载到不同的磁盘目录，提高磁盘io对数据库性能影响。
######################################################################
basedir = /usr/local/mysql
datadir = /home/mysql/data
tmpdir = /home/mysql/temp
######################################################################
# time out 超时时间
######################################################################
connect_timeout = 20
wait_timeout=86400
######################################################################
# connection 最大连接个数
######################################################################
max_connections = 500
max_user_connections = 1000
max_connect_errors = 100000
max_allowed_packet = 32M
######################################################################
# innodb 存储引擎
# innodb_buffer_pool_size 一般配置为物理内存的1/2 或2/3
######################################################################
default-storage-engine = INNODB
innodb_buffer_pool_size = 512M
innodb_log_buffer_size = 32M
innodb_log_file_size = 128M
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 64
######################################################################
#slow query 慢查询日志
#
######################################################################
slow-query-log = on
slow-query-log-file = /home/mysql/logs/mysql-slow.log
long_query_time= 2
log_queries_not_using_indexes = on
######################################################################
# log-bin 二进制日志：
# 1、开启log-bin 必须配置server-id
# 2、binlog_ignore_dbL：log-bin 忽略的表
# 3、binlog_format取值：
# STATEMENT：导致日志记录基于语句。
# ROW：导致日志记录基于行。
# MIXED：导致日志记录使用混合格式。
######################################################################
server-id=100
log_bin=mysql-bin
binlog_format = MIXED
sync_binlog = 1
expire_logs_days =7
binlog_cache_size = 128m
max_binlog_cache_size =512m
max_binlog_size =256M
binlog_ignore_db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=performation_schema
binlog_ignore_db=sys
######################################################################
# relay-log 中继日志
#
######################################################################
relay_log = mysql-relay-bin
relay_log_purge = on
relay_log_recovery = on
max_relay_log_size = 512M
######################################################################
# cache 缓存
#
######################################################################
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M
query_cache_min_res_unit = 16k
tmp_table_size = 256M
max_heap_table_size = 256M
key_buffer_size = 256M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
skip_name_resolve=ON
back_log=600
sort_buffer_size=4M
join_buffer_size = 4M
thread_cache_size = 300
######################################################################
# sql mode 在MySQL 5.7的默认SQL模式包括以下模式：
# ONLY_FULL_GROUP_BY,
# STRICT_TRANS_TABLES,
# NO_ZERO_IN_DATE,
# NO_ZERO_DATE，
# ERROR_FOR_DIVISION_BY_ZERO,
# NO_AUTO_CREATE_USER,
# NO_ENGINE_SUBSTITUTION。
######################################################################
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqldump]
quick
max_allowed_packet = 32M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
14、复制服务启动脚本：
# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
赋予可执行权限
chmod 755 /etc/init.d/mysql
或者
chmod +x /etc/init.d/mysql
3、添加服务
chkconfig --add mysql
4、显示服务列表
chkconfig --list
或者
chkconfig --list | grep -i mysql
验证
netstat -na | grep 3306
或者
netstat -apn | grep 3306
15、启动 MySQL 服务：
Starting MySQL.. SUCCESS!
（初次启动会在/usr/local/mysql 目录下生成 mysql.sock 文件）
修改密码：
16、设置 MySQL 开机自动启动服务：
# chkconfig mysql on
存在的问题：
1、mysql 免密码登录直接mysql不行可以加上 -h 127.0.0.1 试一试。
2 、初始密码忘记的情况（打开skip-grant-tables配置）。
设置 MySQL 数据库 root 用户的本地登录密码（初始用户没有密码）：
# mysqladmin -u root password '123456'
[mysqld] skip-grant-tables=1
登录并修改 MySQL 用户 root 的密码：mysql -h 127.0.0.1
修改 root 用户密码：
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; 允许 root 远程登录，设置远程登录密码：123456
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
下面是具体步骤：
[root@mysql-01 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.9
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases; +--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.42 sec)
mysql> select host, user, authentication_string, plugin from mysql.user; +-----------+-----------+-------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+-----------+-------------------------------------------+-----------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | mysql_native_password |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
+-----------+-----------+-------------------------------------------+-----------------------+
2 rows in set (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select host, user, authentication_string, plugin from mysql.user; +-----------+-----------+-------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+-----------+-------------------------------------------+-----------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | mysql_native_password |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| % | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | mysql_native_password |
+-----------+-----------+-------------------------------------------+-----------------------+
3 rows in set (0.00 sec)
mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
[root@mysql-01 ~]#
新密码登录测试： mysql -uroot -p123456
注意：真实生产环境，应用操作不要使用 root 用户。
bind-address：MySQL服务器在单个网络套接字上侦听TCP/IP连接。此套接字绑定到单个地址，但地址可以映射到多个网络接口。
要指定地址，请使用 服务器启动时的 选项，其中包含IPv4或IPv6地址或主机名。如果是主机名，则服务器将名称解析为IP地址并绑定到该地址。
如果主机名解析为多个IP地址，则服务器将使用第一个IPv4地址（如果有），否则使用第一个IPv6地址。
服务器处理不同类型的地址，如下所示：
（1）如果地址是*，则服务器接受所有服务器主机IPv4接口上的TCP/IP连接，如果服务器主机支持IPv6，则接受所有IPv6接口上的TCP/IP连接。
使用此地址允许所有服务器接口上的IPv4和IPv6连接。该值是默认值。
（2）如果地址是0.0.0.0，则服务器接受所有服务器主机IPv4接口上的TCP/IP连接。
（3）如果地址是::，则服务器接受所有服务器主机IPv4和IPv6接口上的TCP/IP连接。
（4）如果地址是IPv4映射地址，则服务器接受IPv4或IPv6格式的该地址的TCP/IP连接。
例如，如果绑定了服务器::ffff:127.0.0.1，则客户端可以使用--host=127.0.0.1或 连接--host=::ffff:127.0.0.1。
如果地址是“常规” IPv4或IPv6地址（例如127.0.0.1或 ::1），则服务器仅接受该IPv4或IPv6地址的TCP/IP连接。
如果绑定到地址失败，服务器将产生错误并且不会启动。
如果要将服务器绑定到特定地址，请确保mysql.user系统表包含具有管理权限的帐户，可用于连接到该地址。否则，您将无法关闭服务器。
例如，如果将服务器绑定到*，则可以使用所有现有帐户连接到该服务器。但是如果绑定服务器 ::1，它只接受该地址上的连接。
在这种情况下，首先要确保 'root'@'::1'该mysql.user表存在于mysql.user表中，这样您仍然可以连接到服务器以将其关闭。
二进制文件参数：
log-bin：该值将用作日志文件的名称。服务器通过向该名称添加数字后缀来按顺序创建二进制日志文件。在MySQL 5.7中，基本名称默认host_name-bin
二进制日志文件的默认位置是数据目录。您可以使用该--log-bin选项指定备用位置，方法是在基本名称中添加前导绝对路径名以指定其他目录。当服务器从二进制日志索引文件中读取条目时，该文件跟踪已使用的二进制日志文件，它会检查条目是否包含相对路径。
设置此选项会导致 log_bin系统变量设置为ON（或1），而不是基本名称。二进制日志文件基本名称和任何指定的路径都可用作 log_bin_basename系统变量。
如果指定 log-bin 选项而不指定a server-id，则不允许启动服务器。
binlog_format：指定二进制日志的格式：
STATEMENT：导致日志记录基于语句。
ROW：导致日志记录基于行。
MIXED：导致日志记录使用混合格式。
SET GLOBAL binlog_format = 'ROW';
SET SESSION binlog_format = 'ROW';
当存在任何临时表时，不建议在运行时切换复制格式，因为临时表仅在使用基于语句的复制时记录，而基于行的复制则不记录临时表。通过混合复制，通常会记录临时表; 用户定义函数（UDF）和函数发生异常 UUID()。
示例：
binlog-format={ROW|STATEMENT|MIXED}
relay-log：用于中继日志的位置和基本名称
示例：
relay_log=mysql-relay-bin
max_relay_log_size：如果非零，则当其大小超过此值时，会自动轮换中继日志。
如果为零，则发生旋转的大小由max_binlog_size的值确定。
示例：
max_relay_log_size = 512M
relay_log_recovery：是否启用了在启动时从主服务器自动恢复中继日志文件; 必须启用崩溃安全从站
示例：
relay_log_recovery = on
relay_log_purge：确定是否清除中继日志
示例：
relay_log_purge = on
slave-max-allowed-packet：可以从复制主服务器发送到从服务器的数据包的最大大小（以字节为单位）; 覆盖max_allowed_packet
连接相关参数
max_connections：允许客户端并发连接的最大数量，默认值是151，一般将该参数设置为500-2000
max_connect_errors：如果客户端尝试连接的错误数量超过这个参数设置的值，则服务器不再接受新的客户端连接。可以通过清空主机的缓存来解除服务器的这种阻止新连接的状态，通过FLUSH HOSTS或mysqladmin flush-hosts命令来清空缓存。这个参数的默认值是100，一般将该参数设置为100000。
interactive_timeout：Mysql关闭交互连接前的等待时间，单位是秒，默认是8小时，建议不要将该参数设置超过24小时，即86400
wait_timeout：Mysql关闭非交互连接前的等待时间，单位是秒，默认是8小时，建议不要将该参数设置超过24小时，即86400
skip_name_resolve：如果这个参数设为OFF，则MySQL服务在检查客户端连接的时候会解析主机名；如果这个参数设为ON，则MySQL服务只会使用IP，在这种情况下，授权表中的Host字段必须是IP地址或localhost。
这个参数默认是关闭的
back_log：MySQL服务器连接请求队列所能处理的最大连接请求数，如果队列放满了，后续的连接才会拒绝。当主要的MySQL线程在很短时间内获取大量连接请求时，这个参数会生效。接下来，MySQL主线程会花费很短的时间去检查连接，然后开启新的线程。这个参数指定了MySQL的TCP/IP监听队列的大小。如果MySQL服务器在短时间内有大量的连接，可以增加这个参数。
文件相关参数sync_binlog：控制二进制日志被同步到磁盘前二进制日志提交组的数量。当这个参数为0的时候，二进制日志不会被同步到磁盘；当这个参数设为0以上的数值时，就会有设置该数值的二进制提交组定期同步日志到磁盘。当这个参数设为1的时候，所有事务在提交前会被同步到二进制日志中，因而即使MySQL服务器发生意外重启，任何二进制日志中没有的事务只会处于准备状态，这会导致MySQL服务器自动恢复以回滚这些事务。这样就会保证二进制日志不会丢失事务，是最安全的选项；同时由于增加了磁盘写，这对性能有一定降低。将这个参数设为1以上的数值会提高数据库的性能，但同时会伴随数据丢失的风险。建议将该参数设为2、4、6、8、16。
expire_logs_days：二进制日志自动删掉的时间间隔。默认值为0，代表不会自动删除二进制日志。想手动删除二进制日志，可以执行 PURGE BINARY LOGS。
示例：
expire_logs_days =7
max_binlog_size：二进制日志文件的最大容量，当写入的二进制日志超过这个值的时候，会完成当前二进制的写入，向新的二进制日志写入日志。这个参数最小值时4096字节；最大值和默认值时1GB。相同事务中的语句都会写入同一个二进制日志，当一个事务很大时，二进制日志实际的大小会超过max_binlog_size参数设置的值。如果max_relay_log_size参数设为0，则max_relay_log_size参数会使用和max_binlog_size参数同样的大小。建议将此参数设为512M。
local_infile：是否允许客户端使用LOAD DATA INFILE语句。如果这个参数没有开启，客户端不能在LOAD DATA语句中使用LOCAL参数。
open_files_limit：操作系统允许MySQL服务打开的文件数量。这个参数实际的值以系统启动时设定的值、max_connections和table_open_cache为基础，使用下列的规则：
- 10 + max_connections + (table_open_cache * 2)
- max_connections * 5
- MySQL启动时指定open_files_limit的值
缓存控制参数binlog_cache_size：在事务中二进制日志使用的缓存大小。如果MySQL服务器支持所有的存储引擎且启用二进制日志，每个客户端都会被分配一个二进制日志缓存。如果数据库中有很多大的事务，增大这个缓存可以获得更好的性能。
Binlog_cache_use和Binlog_cache_disk_use这两个参数对于binlog_cache_size参数的优化很有用。binlog_cache_size参数只设置事务所使用的缓存，非事务SQL语句所使用的缓存由binlog_stmt_cache_size系统参数控制。建议不要将这个参数设为超过64MB，以防止客户端连接多而影响MySQL服务的性能。
max_binlog_cache_size：如果一个事务需要的内存超过这个参数，服务器会报错"Multi-statement transaction required more than 'max_binlog_cache_size' bytes"。这个参数最大的推荐值是4GB，这是因为MySQL不能在二进制日志设为超过4GB的情况下正常的工作。建议将该参数设为binlog_cache_size*2。
binlog_stmt_cache_size：这个参数决定二进制日志处理非事务性语句的缓存。如果MySQL服务支持任何事务性的存储引擎且开启了二进制日志，每个客户端连接都会被分配二进制日志事务和语句缓存。如果数据库中经常运行大的事务，增加这个缓存可以获得更好的性能。
table_open_cache：所有线程能打开的表的数量。
thread_cache_size：MySQL服务缓存以重用的线程数。当客户端断开连接的时候，如果线程缓存没有使用满，则客户端的线程被放入缓存中。如果有客户端断开连接后再次连接到MySQL服务且线程在缓存中，则MySQL服务会优先使用缓存中的线程；如果线程缓存没有这些线程，则MySQL服务器会创建新的线程。如果数据库有很多的新连接，可以增加这个参数来提升性能。如果MySQL服务器每秒有上百个连接，可以增大thread_cache_size参数来使MySQL服务器使用缓存的线程。通过检查Connections和Threads_created状态参数，可以判断线程缓存是否足够。这个参数默认的值是由下面的公式来决定的：8 + (max_connections / 100)
建议将此参数设置为300~500。线程缓存的命中率计算公式为(1-thread_created/connections)*100%，可以通过这个公式来优化和调整thread_cache_size参数。
query_cache_size：为查询结果所分配的缓存。默认这个参数是没有开启的。这个参数的值应设为整数的1024倍，如果设为其他值则会被自动调整为接近此数值的1024倍。这个参数最小需要40KB。建议不要将此参数设为大于256MB，以免占用太多的系统内存。
query_cache_min_res_unit：查询缓存所分配的最小块的大小。默认值是4096(4KB)。
query_cache_type：设置查询缓存的类型。当这个参数为0或OFF时，则MySQL服务器不会启用查询缓存；当这个参数为1或ON时，则MySQL服务器会缓存所有查询结果（除了带有SELECT SQL_NO_CACHE的语句）；当这个参数为2或DEMAND时，则MySQL服务器只会缓存带有SELECT SQL_CACHE的语句。
sort_buffer_size：每个会话执行排序操作所分配的内存大小。想要增大max_sort_length参数，需要增大sort_buffer_size参数。如果在SHOW GLOBAL STATUS输出结果中看到每秒输出的Sort_merge_passes状态参数很大，可以考虑增大sort_buffer_size这个值来提高ORDER BY 和 GROUP BY的处理速度。建议设置为1~4MB。当个别会话需要执行大的排序操作时，在会话级别增大这个参数。
read_buffer_size：为每个线程对MyISAm表执行顺序读所分配的内存。如果数据库有很多顺序读，可以增加这个参数，默认值是131072字节。这个参数的值需要是4KB的整数倍。这个参数也用在下面场景中：
- 当执行ORDER BY操作时，缓存索引到临时文件（不是临时表）中；
- 执行批量插入到分区表中；
- 缓存嵌套查询的执行结果。
read_rnd_buffer_size：这个参数用在MyISAM表和任何存储引擎表随机读所使用的内存。当从MyISAM表中以键排序读取数据的时候，扫描的行将使用这个缓存以避免磁盘的扫描。将这个值设到一个较大的值可以显著提升ORDER BY的性能。然后，这个参数会应用到所有的客户端连接，所有不应该将这个参数在全局级别设为一个较大的值；在运行大查询的会话中，在会话级别增大这个参数即可。
join_buffer_size：MySQL服务器用来作普通索引扫描、范围索引扫描和不使用索引而执行全表扫描这些操作所用的缓存大小。通常，获取最快连接的方法是增加索引。当不能增加索引的时候，使全连接变快的方法是增大join_buffer_size参数。对于执行全连接的两张表，每张表都被分配一块连接内存。对于没有使用索引的多表复杂连接，需要多块连接内存。通常来说，可以将此参数在全局上设置一个较小的值，而在需要执行大连接的会话中在会话级别进行设置。默认值是256KB。
net_buffer_length：每个客户端线程和连接缓存和结果缓存交互，每个缓存最初都被分配大小为net_buffer_length的容量，并动态增长，直至达到max_allowed_packet参数的大小。当每条SQL语句执行完毕后，结果缓存会缩小到net_buffer_length大小。不建议更改这个参数，除非你的系统有很少的内存，可以调整这个参数。如果语句需要的内存超过了这个参数的大小，则连接缓存会自动增大。net_buffer_length参数最大可以设置到1MB。不能在会话级别设置这个参数。
max_allowed_packet：网络传输时单个数据包的大小。默认值是4MB。包信息缓存的初始值是由net_buffer_length指定的，但是包可能会增长到max_allowed_packet参数设置的值。如果要使用BLOB字段或长字符串，需要增加这个参数的值。这个参数的值需要设置成和最大的BLOB字段一样的大小。max_allowed_packet参数的协议限制是1GB。这个参数应该是1024整数倍。
bulk_insert_buffer_size：MyISAM表使用一种特殊的树状缓存来提高批量插入的速度，如INSERT ... SELECT,INSERT ... VALUES (...),(...), ...,对空表执行执行LOAD DATA INFILE。这个参数每个线程的树状缓存大小。将这个参数设为0会关闭这个参数。这个参数的默认值是8MB。
max_heap_table_size：这个参数设置用户创建的MEMORY表允许增长的最大容量，这个参数用来MEMORY表的MAX_ROWS值。设置这个参数对已有的MEMORY表没有影响，除非表重建或执行ALTER TABLE、TRUNCATE TABLE语句。
这个参数也和tmp_table_size参数一起来现在内部in-memory表的大小。如果内存表使用频繁，可以增大这个参数的值。
tmp_table_size：内部内存临时表的最大内存。这个参数不会应用到用户创建的MEMORY表。如果内存临时表的大小超过了这个参数的值，则MySQL会自动将超出的部分转化为磁盘上的临时表。在MySQL 5.7.5版本，internal_tmp_disk_storage_engine存储引擎将作为磁盘临时表的默认引擎。在MySQL 5.7.5之前的版本，会使用MyISAM存储引擎。如果有很多的GROUP BY查询且系统内存充裕，可以考虑增大这个参数。
innodb_buffer_pool_dump_at_shutdown：指定在MySQL服务关闭时，是否记录InnoDB缓存池中的缓存页，以缩短下次重启时的预热过程。通常和innodb_buffer_pool_load_at_startup参数搭配使用。innodb_buffer_pool_dump_pct参数定义了保留的最近使用缓存页的百分比。
innodb_buffer_pool_dump_now：立刻记录InnoDB缓冲池中的缓存页。通常和innodb_buffer_pool_load_now搭配使用。
innodb_buffer_pool_load_at_startup：指定MySQL服务在启动时，InnoDB缓冲池通过加载之前的缓存页数据来自动预热。通常和innodb_buffer_pool_dump_at_shutdown参数搭配使用。
innodb_buffer_pool_load_now：立刻通过加载数据页来预热InnoDB缓冲池，无需重启数据库服务。可以用来在性能测试时，将缓存改成到一个已知的状态；或在数据库运行报表查询或维护后，将数据库改成到一个正常的状态。
MyISAM参数key_buffer_size：所有线程所共有的MyISAM表索引缓存，这块缓存被索引块使用。增大这个参数可以增加索引的读写性能，在主要使用MyISAM存储引擎的系统中，可设置这个参数为机器总内存的25%。如果将这个参数设置很大，比如设为机器总内存的50%以上，机器会开始page且变得异常缓慢。可以通过SHOW STATUS 语句查看 Key_read_requests,Key_reads,Key_write_requests, and Key_writes这些状态值。正常情况下Key_reads/Key_read_requests 比率应该小于0.01。数据库更新和删除操作频繁的时候，Key_writes/Key_write_requests 比率应该接近1。
key_cache_block_size：key缓存的块大小，默认值是1024字节。
myisam_sort_buffer_size：在REPAIR TABLE、CREATE INDEX 或 ALTER TABLE操作中，MyISAM索引排序使用的缓存大小。
myisam_max_sort_file_size：当重建MyISAM索引的时候，例如执行REPAIR TABLE、 ALTER TABLE、 或 LOAD DATA INFILE命令，MySQL允许使用的临时文件的最大容量。如果MyISAM索引文件超过了这个值且磁盘还有充裕的空间，增大这个参数有助于提高性能。
myisam_repair_threads：如果这个参数的值大于1，则MyISAM表在执行Repair操作的排序过程中，在创建索引的时候会启用并行，默认值为1。
InnoDB参数innodb_buffer_pool_size：InnDB存储引擎缓存表和索引数据所使用的内存大小。默认值是128MB。在以InnDB存储引擎为主的系统中，可以将这个参数设为机器物理内存的80%。同时需要注意：
- 设置较大物理内存时是否会引擎页的交换而导致性能下降；
- InnoDB存储引擎会为缓存和控制表结构信息使用部分内存，因而实际花费的内存会比设置的值大于10%；
- 这个参数设置的越大，初始化内存池的时间越长。在MySQL 5.7.5版本，可以以chunk为单位增加或减少内存池的大小。chunk的大小可以通过innodb_buffer_pool_chunk_size参数设定，默认值是128MB。内存池的大小可以等于或是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的整数倍。
innodb_buffer_pool_instances：InnoDB缓存池被分成的区域数。对于1GB以上大的InnoDB缓存，将缓存分成多个部分可以提高MySQL服务的并发性，减少不同线程读缓存页的读写竞争。每个缓存池有它单独的空闲列表、刷新列表、LRU列表和其他连接到内存池的数据结构，它们被mutex锁保护。这个参数只有将innodb_buffer_pool_size参数设为1GB或以上时才生效。建议将每个分成的内存区域设为1GB大小。
innodb_max_dirty_pages_pct：当Innodb缓存池中脏页所占的百分比达到这个参数的值时，InnoDB会从缓存中向磁盘写入数据。默认值是75。
innodb_thread_concurrency：InnoDB存储引擎可以并发使用的最大线程数。当InnoDB使用的线程超过这参数的值时，后面的线程会进入等待状态，以先进先出的算法来处理。等待锁的线程不计入这个参数的值。这个参数的范围是0~1000。默认值是0。当这个参数为0时，代表InnoDB线程的并发数没有限制，这样会导致MySQL创建它所需要的尽可能多的线程。设置这个参数可以参考下面规则：
- 如果用户线程的并发数小于64，可以将这个参数设为0；
- 如果系统并发很严重，可以先将这个参数设为128，然后再逐渐将这个参数减小到96, 80, 64或其他数值，直到找到性能较好的一个数值。
innodb_flush_method：指定刷新数据到InnoDB数据文件和日志文件的方法，刷新方法会对I/O有影响。如果这个参数的值为空，在类Unix系统上，这个参数的默认值为fsync；在Windows系统上，这个参数的默认值为async_unbuffered。在类Unix系统上，这个参数可设置的值如下：
- fsync:InnoDB使用fsync()系统函数来刷新数据和日志文件，fsync是默认参数。
- O_DSYNC:InnoDB使用O_SYNC函数来打开和刷新日志文件，使用fsync()函数刷新数据文件
- littlesync:这个选项用在内部性能的测试，目前MySQL尚不支持，使用这个参数又一定的风险
- nosync:这个选项用在内部性能的测试，目前MySQL尚不支持，使用这个参数又一定的风险
- O_DIRECT:InnoDB使用O_DIRECT（或者directio()在Solaris）函数打开数据文件，使用fsync()刷新数据文件和日志文件
- O_DIRECT_NO_FSYNC:在刷新I/O时，InnoDB使用O_DIRECT方式。
在有RAID卡和写缓存的系统中，O_DIRECT有助于避免InnoDB缓存池和操作系统缓存之间的双重缓存。在InnoDB数据和日志文件放在SAN存储上面的系统，默认值或O_DSYNC方法会对以读为主的数据库起到加速作用。
innodb_data_home_dir：InnoDB系统表空间所使用的数据文件的物理路径，默认路径是MySQL数据文件路径。如果这个参数的值为空，可以在innodb_data_file_path参数里使用绝对路径
innodb_data_file_path：InnoDB数据文件的路径和大小。
innodb_file_per_table：当这个参数启用的时候，InnoDB会将新建表的数据和索引单独存放在.ibd格式的文件中，而不是存放在系统表空间中。当这张表被删除或TRUNCATE时，InnoDB表所占用的存储会被释放。这个设定会开启InnoDB的一些其他特性，比如表的压缩。当这个参数关闭的时候，InnoDB会将表和索引的数据存放到系统表空间的ibdata文件中，这会有一个问题，因为系统表空间不会缩小，这样设置会导致空间无法回放。
innodb_undo_directory：InnoDB undo日志所在表空间的物理路径。和innodb_undo_logs、innodb_undo_tablespaces参数配合，来设置undo日志的路径，默认路径是数据文件路径。
innodb_undo_logs：指定InnoDB使用的undo日志的个数。在MySQL 5.7.2版本，32个undo日志被临时表预留使用，并且这些日志存放在临时表表空间(ibtmp1)中。如果undo日志只存放在系统表空间中，想要额外分配供数据修改事务用的undo日志，innodb_undo_logs参数必须设置为32以上的整数。如果你配置了单独的undo表空间，要将innodb_undo_logs参数设为33以上来分配额外供数据修改事务使用的undo日志。每个undo日志最多可以支持1024个事务。如果这个参数没有设置，则它会设为默认值128。
innodb_undo_tablespaces：undo日志的表空间文件数量。默认，所有的undo日志都是系统表空间的一部分。因为在运行大的事务时，undo日志会增大，将undo日志设置在多个表空间中可以减少一个表空间的大小。undo表空间文件创建在innodb_undo_directory参数指定的路径下，以undoN格式命名，N是以0开头的一系列整数。undo表空间的默认大小为10M。需要在初始化InnoDB前设置innodb_undo_tablespaces这个参数。在MySQL 5.7.2版本，在128个undo日志中，有32个undo日志是为临时表所预留的，有95个undo日志供undo表空间使用。
innodb_log_files_in_group：InnoDB日志组包含的日志个数。InnoDB以循环的方式写入日志。这个参数的默认值和推荐值均是2。日志的路径由innodb_log_group_home_dir参数设定。
innodb_log_group_home_dir：InnoDB重做日志文件的物理路径，重做日志的数量由innodb_log_files_in_group参数指定。如果不指定任何InnoDB日志参数，MySQL默认会在MySQL数据文件路径下面创建两个名为ib_logfile0、ib_logfile1的两个重做日志文件，它们的大小由innodb_log_file_size参数设定。
innodb_log_file_size：日志组中每个日志文件的字节大小。所有日志文件的大小(innodb_log_file_size * innodb_log_files_in_group)不能超过512GB。
innodb_log_buffer_size：InnoDB写入磁盘日志文件所使用的缓存字节大小。如果innodb_page_size参数为32K，则默认值是8MB；如果innodb_page_size参数为64K，则默认值是16MB。如果日志的缓存设置较大，则MySQL在处理大事务时，在提交事务前无需向磁盘写入日志文件。建议设置此参数为4~8MB。
innodb_flush_log_at_trx_commit：当提交相关的I/O操作被批量重新排列时，这个参数控制提交操作的ACID一致性和高性能之间的平衡。可以改变这个参数的默认值来提升数据库的性能，但是在数据库宕机的时候会丢失少量的事务。这个参数的默认值为1，代表数据库遵照完整的ACID模型，每当事务提交时，InnoDB日志缓存中的内容均会被刷新到日志文件，并写入到磁盘。当这个参数为0时，InnDB日志缓存大概每秒刷新一次日志文件到磁盘。当事务提交时，日志缓存不会立刻写入日志文件，这样的机制不会100%保证每秒都向日志文件刷新日志，当mysqld进程宕掉的时候可能会丢失持续时间为1秒左右的事务数据。当这个参数为2时，当事务提交后，InnoDB日志缓存中的内容会写入到日志文件且日志文件，日志文件以大概每秒一次的频率刷新到磁盘。在MySQL 5.6.6版本，InnoDB日志刷新频率由innodb_flush_log_at_timeout参数决定。通常将个参数设为1。
innodb_flush_log_at_timeout：写入或刷新日志的时间间隔。这个参数是在MySQL 5.6.6版本引入的。在MySQL 5.6.6版本之前，刷新的频率是每秒刷新一次。innodb_flush_log_at_timeout参数的默认值也是1秒刷新一次。
innodb_lock_wait_timeout：InnDB事务等待行锁的时间长度。默认值是50秒。当一个事务锁定了一行，这时另外一个事务想访问并修改这一行，当等待时间达到innodb_lock_wait_timeout参数设置的值时，MySQL会报错"ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction"，同时会回滚语句（不是回滚整个事务）。如果想回滚整个事务，需要使用--innodb_rollback_on_timeout参数启动MySQL。在高交互性的应用系统或OLTP系统上，可以减小这个参数来快速显示用户的反馈或把更新放入队列稍后处理。在数据仓库中，为了更好的处理运行时间长的操作，可以增大这个参数。这个参数只应用在InnoDB行锁上，这个参数对表级锁无效。这个参数不适用于死锁，因为发生死锁时，InnoDB会立刻检测到死锁并将发生死锁的一个事务回退。
innodb_fast_shutdown：InnoDB关库模式。如果这个参数为0，InnoDB会做一个缓慢关机，在关机前会做完整的刷新操作，这个级别的关库操作会持续数分钟，当缓存中的数据量很大时，甚至会持续几个小时；如果数据库要执行版本升级或降级，需要执行这个级别的关库操作，以保证所有的数据变更都写入到数据文件。如果这个参数的值是1（默认值），为了节省关库时间，InnoDB会跳过新操作，而是在下一次开机的时候通过crash recovery方式执行刷新操作。如果这个参数的值是2，InnoDB会刷新日志并以冷方式关库，就像MySQL宕机一样，没有提交的事务会丢失，在下一次开启数据库时，crash recovery所需要的时间更长；在紧急或排错情形下，需要立刻关闭数据库时，会使用这种方式停库。
| 
发表于 2021-4-10 10:25:47
