mysql数据库高可用-双主集群+keepalived部署，简易mysql部署

# vim /etc/sysctl.conf

net.ipv4.tcp_timestamps=1

net.ipv4.tcp_tw_recycle=1

net.ipv4.tcp_tw_reuse=1

vm.min_free_kbytes=1024000

net.ipv4.tcp_timestamps=1

net.ipv4.tcp_tw_recycle=1

net.ipv4.tcp_tw_reuse=1

vm.min_free_kbytes=1024000

net.ipv4.tcp_timestamps=1

net.ipv4.tcp_tw_recycle=1

net.ipv4.tcp_tw_reuse=1

vm.min_free_kbytes=1024000

net.core.somaxconn = 3000

net.core.netdev_max_backlog = 65535

net.ipv4.tcp_max_syn_backlog = 65535

net.ipv4.tcp_fin_timeout = 10

net.core.wmem_max = 16777216

net.core.rmem_max = 16777216

net.ipv4.tcp_keepalive_time = 120

net.ipv4.tcp_keepalive_intvl = 30

net.ipv4.tcp_keepalive_probes = 3

vm.swappiness = 0

sysctl -p 生效

# vim /etc/security/limits.conf

* hard nproc 1024000

* soft nproc 1024000

* hard nofile 1024000

* soft nofile 1024000

* hard stack 20240

* soft stack 10240

重启生效

# vim /etc/security/limits.d/20-nproc.conf

* soft nproc 100000

重启生效

检查是否生效

ulimit -a

open files (-n) 1024000 #重点关注这项

1.3 环境配置安装包准备

环境配置以及准备安装包如下

系统：Centenos7.6

mysql版本 mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

keepalived版本 keepalived-2.0.13.tar.gz

修改hosts文件

Mysqldb1

Mysqldb2

node1 ：192.168.188.149 10.228.204.163

node2 ：192.168.188.134 10.228.204.164

mysqlvip 192.168.188.111 10.228.204.176（最好是同一ip段，不然可能会出现网络问题）

1.4 mysql用户配置

useradd mysql –d /home/mysql

主机用户：具有sudo权限的mysql

1.5 删除默认的mysql和mariadb

针对node1操作：

查看

rpm -qa | grep -i mysql

rpm -qa | grep mariadb

删除（查出来的一个个全删了）

sudo rpm -e mariadb-libs-5.5.56-2.el7.x86_64 –nodeps

[root@localhost ~]# rpm -qa | grep -i mysql

[root@localhost ~]# rpm -qa | grep mariadb

rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64 --nodeps

rpm -e mariadb-server-5.5.60-1.el7_5.x86_64 --nodeps

rpm -e mariadb-5.5.60-1.el7_5.x86_64 --nodeps

rpm -e perl-DBD-MySQL-4.023-6.el7.x86_64 --nodeps

rpm -e qt-mysql-4.8.7-2.el7.x86_64 --nodeps

rpm -e akonadi-mysql-1.9.2-4.el7.x86_64 --nodeps

[root@localhost ~]#

rpm -qa | grep -i mysql

rpm -qa | grep mariadb

二、数据库安装

2.1 安装目录

/mysql/db/ –数据库软件目录

/mysql/data/ --数据存放目录

/mysql/log/ --日志存放目录

[root@localhost ~]# mkdir /mysql/db

[root@localhost ~]# mkdir /mysql/log

[root@localhost ~]# mkdir /mysql/data/data1

[root@mysqldb2 db]# chown mysql:mysql *

[root@mysqldb2 db]# chmod 775 *

2.2 解压文件包并修改名称

unzip mysql-5.7.39-linux-glibc2.12-x86_64.zip

gunzip mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz

mv mysql-5.7.39-linux-glibc2.12-x86_64 mysql

创建对应的文件以及目录

run

chown mysql:mysql mysql.log

2.3 配置文件my.cnf ---具体信息可以草考标准my cnf文档

请根据实际环境进行参数配置

配置my.cnf文件(在/etc/my.cnf下)

[mysqld]

port=3306

basedir=/mysql/db

datadir=/mysql/data/data1

language=/mysql/db/share/english

server-id=2

log-bin=/mysql/log/mysql-bin

symbolic-links=0

default-storage-engine=INNODB

max_connections=2000

max_connect_errors=10

default_authentication_plugin=mysql_native_password

expire_logs_days=15

max_binlog_szie=100M

lower_case_table_names = 1

event_scheduler=1

secure_file_priv=''

sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

character_set_client=utf8

character_set_connection=utf8

character_set_database=utf8

character_set_results=utf8

character_set_server=utf8

innodb_undo_directory= /data/mysql/undologs

innodb_undo_tablespaces=5 --5.7 分离undo

max_connections = 1000

max_user_connections = 1000

expire_logs_days=15

[mysqld_safe]

log-error=/mysql/log/mysql.log

pid-file=/mysql/db/run/mysql.pid

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

2.4 配置环境变量

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/mysql/db/bin

export PATH

2.5 初始化数据库

执行如下脚本初始化数据库

mysqld --initialize --user=mysql --datadir=/mysql/data

mysqldb1

[root@mysqldb1 bin]# ./mysqld --initialize --user=mysql --basedir=/mysql/db --datadir=/mysql/data/data1

2022-08-19T06:25:22.170986Z 0 [Warning] The syntax '--language/-l' is deprecated and will be removed in a future release. Please use '--lc-messages-dir' instead.

2022-08-19T06:25:22.171086Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2022-08-19T06:25:22.171171Z 0 [Warning] Using pre 5.5 semantics to load error messages from /mysql/db/share/english/.

2022-08-19T06:25:22.171174Z 0 [Warning] If this is not intended, refer to the documentation for valid usage of --lc-messages-dir and --language parameters.

2022-08-19T06:25:22.447884Z 0 [Warning] InnoDB: New log files created, LSN=45790

2022-08-19T06:25:22.496303Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2022-08-19T06:25:22.558813Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b45ff8e3-1f87-11ed-a694-005056ae5686.

2022-08-19T06:25:22.559831Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2022-08-19T06:25:22.757839Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.

2022-08-19T06:25:22.757855Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.

2022-08-19T06:25:22.758535Z 0 [Warning] CA certificate ca.pem is self signed.

2022-08-19T06:25:22.886094Z 1 [Note] A temporary password is generated for root@localhost: ?y/)1kW5Pi8F

mysqld --defaults-file=/home/mysql/.my.cnf --initialize --user=mysql(这种方式作为参考)

/mysql/db/文件夹下创建logs文件夹和run文件夹

mkdir logs

mkdir run

mysqldb2

[root@mysqldb2 bin]# ./mysqld --initialize --user=mysql --basedir=/mysql/db --datadir=/mysql/data/data1

2022-08-19T07:51:31.186755Z 0 [Warning] The syntax '--language/-l' is deprecated and will be removed in a future release. Please use '--lc-messages-dir' instead.

2022-08-19T07:51:31.186844Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2022-08-19T07:51:31.186936Z 0 [Warning] Using pre 5.5 semantics to load error messages from /mysql/db/share/english/.

2022-08-19T07:51:31.186939Z 0 [Warning] If this is not intended, refer to the documentation for valid usage of --lc-messages-dir and --language parameters.

2022-08-19T07:51:31.520922Z 0 [Warning] InnoDB: New log files created, LSN=45790

2022-08-19T07:51:31.573263Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2022-08-19T07:51:31.635205Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: bd626fe6-1f93-11ed-a487-005056aec4e4.

2022-08-19T07:51:31.636892Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2022-08-19T07:51:31.921156Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.

2022-08-19T07:51:31.921176Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.

2022-08-19T07:51:31.921950Z 0 [Warning] CA certificate ca.pem is self signed.

2022-08-19T07:51:32.021235Z 1 [Note] A temporary password is generated for root@localhost: cOdnwi9.S/62

启动mysql数据库

mysqld_safe --user=mysql &

启动时候出现错误：

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/usr/local/mysql-5.7.26-linux-glibc2.12-x86_64/data/mysql.sock`' (2)

原因：

由于mysql的socket文件是由mysqld服务启动时创建的，如果mysqld服务未正常启动，socket文件自然也不会被创建，当然会找不到socket文件了。如果确认mysql服务正常运行，还提示文章标题的此错误，那就是“/etc/my.cnf”配置文件的问题了。解决办法是修改“/etc/my.cnf”配置文件，在配置文件中添加“[client]”选项和“[mysql]”选项，并使用这两个选项下的“socket”参数值，与“[mysqld]”选项下的“socket”参数值，指向的socket文件路径完全一致。

在保证mysql启动起来的情况之下也可以这样启动：

mysql -u root -p -S /usr/local/mysql-5.7.26-linux-glibc2.12-x86_64/data/mysql.sock

但是不是一劳永逸的，需要每次登录都这样。

2.6 mysql自动启动设置用Systemctl start mysqld启动

3.MySQL启动关闭添加到 /etc/init.d/mysqld

cp /mysql/db/support-files/mysql.server /etc/init.d/mysqld （ /data/mysql为安装目录）

chmod +x /etc/init.d/mysqld 赋予可执行权限

chkconfig --add mysqld 添加服务

chkconfig --list 显示服务列表

————————————————

2.7 登录数据库并修改密码

mysql -uroot -p (启动的时候可以看到初始密码)

mysql> alter user root@'localhost' identified by "mysql#123";

flush privileges;

以上步骤针对node2再来一遍，注意my.cnf中需要修改的项目，比如server-id

2.8 配置主从

mysql -uroot -pmysql#123

T3e447f7$

B5s592l5$

主节点（192.168.188.149）

CREATE USER 'sync'@'%' IDENTIFIED WITH mysql_native_password BY 'sync#123456';

GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%';

flush privileges;

show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 | 997 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

备节点（192.168.188.134）

CHANGE MASTER TO MASTER_HOST='10.228.204.163',MASTER_USER='sync', MASTER_PASSWORD='sync#123456',MASTER_PORT=3306,MASTER_LOG_FILE=' mysql-bin.000011', MASTER_LOG_POS=747; (每个逗号之间没有空格)

start slave;

# 停止 stop slave

# 重置 reset slave

mysql> START SLAVE;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.228.204.163

Master_User: sync

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000011

Read_Master_Log_Pos: 747

Relay_Log_File: mysqldb2-relay-bin.000014

Relay_Log_Pos: 960

Relay_Master_Log_File: mysql-bin.000011

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 747

Relay_Log_Space: 1383

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: b45ff8e3-1f87-11ed-a694-005056ae5686

Master_Info_File: /mysql/data/data1/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

mysql>

2.9 状态检查

mysql> show slave status\G

看到两个YES，代表主主成功

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

2.10 配置双主

原备节点（10.228.204.164）

创建同步用户

CREATE USER 'sync'@'%' IDENTIFIED WITH mysql_native_password BY 'sync#123456';

GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%';

flush privileges;

show master status;

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 | 997 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

原主节点（10.228.204.163）

CHANGE MASTER TO MASTER_HOST='10.228.204.163',MASTER_USER='sync',MASTER_PASSWORD='sync#123456',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000011',MASTER_LOG_POS=747;

start slave;

# 停止 stop slave

# 重置 reset slave

mysql> show slave status\G

看到两个YES，代表主主成功

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

2.11 双主测试

赋予root用户远程访问（为了使用root用户远程访问数据库）

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' identified by 'mysql#123'; (这步创建了一个root@%用户)

flush privileges;

进行测试，查看数据是否可以同步，

//创建表

#1. 准备表

//增

create table s1(

id int primary key,

name varchar(20),

gender char(6),

email varchar(50)

);

#2. 创建存储过程，实现批量插入记录

delimiter $$ #声明存储过程的结束符号为$$

create procedure auto_insert()

BEGIN

declare i int default 1;

while(i<100000)do

insert into s1 values(i,'bob','male',concat('bob',i,'@163.com'));

set i=i+1;

end while;

END$$ #$$结束

delimiter ; #重新声明分号为结束符号

#3. 查看存储过程

show create procedure auto_insert\G

#4. 调用存储过程

call auto_insert();

//查看存储过程

show procedure status;

drop procedure auto_insert; //删除存储过程

三、 Keepalived安装

3.1 安装相关的系统环境

（必须是root用户或者sudo用户）

yum -y install gcc openssl-devel openssl ipvsadm

yum -y install libnl libnl-devel （支持ipv6）

tar -xvf keepalived-2.0.13.tar.gz

3.2 源码编译安装

cd keepalived-2.0.13

./configure --prefix=/usr/local/keepalived --安装到/usr/local/keepalived

make && make install

cd /etc

mkdir keepalived

cd /usr/local

cp -r keepalived-2.0.13/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf

cp –r keepalived/etc/init.d /etc/rc.d/init.d/keepalived

cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/keepalived

3.3 Master 主机上的keepalived配置

keepalived.conf 配置内容：＃清空默认内容，直接采用下面配置

sudo vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs { #全局配置标识，表明这个区域{}是全局配置

notification_email {

xxx@xxx #表示发送通知邮件时邮件源地址是谁

}

script_user root #运行健康检查脚本的用户或者组

enable_script_security

notification_email_from xxx@xxx #表示keepalived在发生诸如切换操作时需要发送email通知，以及email发送给哪些邮件地址，邮件地址可以多个，每行一个notification_email_from xxx@xxx

smtp_server 127.0.0.1 #表示发送email时使用的smtp服务器地址，这里可以用本地的sendmail来实现

smtp_connect_timeout 30 #连接smtp连接超时时间

router_id host-10-228-204-163 #机器标识

vrrp_skip_check_adv_addr

vrrp_garp_interval 0

vrrp_gna_interval 0

}

vrrp_script chk_mysql_port { #检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等

script "/root/chk_mysql.sh" #这里通过脚本监测

interval 2 #脚本执行间隔，每2s检测一次

weight -5 #脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级 -5

fall 2 #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间）

rise 1 #检测1次成功就算成功。但不修改优先级

}

vrrp_instance VI_1 {

state BACKUP

nopreempt #不抢占 MASTER

interface ens33 #主机网卡

mcast_src_ip 10.228.204.163 #主机ip

virtual_router_id 1 #路由器标识，MASTER和BACKUP必须是一致的

priority 100 #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后，就可以将VIP资源再次抢回来

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

10.228.204.51

}

track_script {

chk_mysql_port

}

3.4 bakcup主机上的keepalived配置

vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

notification_email {

xxx@xxxx

}

script_user root

enable_script_security

notification_email_from xxx@xxxx

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id host-10-228-204-164

vrrp_skip_check_adv_addr

vrrp_garp_interval 0

vrrp_gna_interval 0

}

vrrp_script chk_mysql_port {

script "/root/chk_mysql.sh"

interval 2

weight -5

fall 2

rise 1

}

vrrp_instance VI_1 {

state BACKUP

nopreempt

interface ens33

mcast_src_ip 10.228.204.164

virtual_router_id 1

priority 90

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

10.228.204.51

}

track_script {

chk_mysql_port

}

3.5 配置checkmysql脚本

cd /root

vi chk_mysql.sh

#!/bin/bash

counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)

if [ "${counter}" -eq 0 ]; then

systemctl stop keepalived.service

以上配置文件中，另外一种ip的配置方法：

unicast_src_ip 192.168.188.149 #本机ip

unicast_peer {

192.168.188.134 #另外一台机器的ip

}

以上配置的是keepalived的非抢占模式，用到了关键字nopreempt

3.6 启动keepalived

shell> sudo systemctl enable keepalived.service #设置开机自动启动

shell> sudo systemctl start keepalived #启动服务

shell> sudo systemctl stop keepalived #停止服务

shell> sudo systemctl restart keepalived #重启服务

3.7 验证登录

mysql -h192.168.188.111 -P3306 -uroot -p123456 赋予root用户远程访问（为了使用root用户远程访问数据库，前面这步需要设置）（也可用dbeaver远程登录验证）

mysql -h192.168.188.149 -P3306 -uroot -p123456

mysql -h192.168.188.134 -P3306 -uroot -p123456

做测试验证，是否可以实现高可用

3.8 漂移验证

将163主机的mysqld关闭查看vip是否漂移。

#!/bin/bash

counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)

echo “check mysqld pid”>>/root/123.log

if [ "${counter}" -eq 0 ]; then

systemctl stop keepalived.service

发现123.log文件中一直被添加文本，说明检查一直在执行。

systemctl stop mysqld

ip a查询ip发现地址已经漂移

3.9 配置调整

调整参数优化

lower_case_table_names = 1

event_scheduler=1

secure_file_priv=''

sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

character_set_client=utf8

character_set_connection=utf8

character_set_database=utf8

character_set_results=utf8

character_set_server=utf8

show variables like '%lower_case_table_names%';

show variables like '%event_scheduler%';

show variables like '%secure_file_priv%';

show variables like '%sql_mode%';

systemctl restart mysqld

systemctl restart keepalived.service

max_connections

最大并发连接数。当MySQL的并发连接达到这个设定值时，新的连接将会被拒绝。当发现MySQL有能力处理更多的并发的时候，建议调大这个值，相应给服务器带来更高的负载(CPU/IO/内存)。默认值:100 参考设置:900

back_log

TCP/IP连接队列的最大值。当MySQL同时有大量连接请求的时候，MySQL会尝试用当前现有的线程处理这些请求，如果处理不过来MySQL会把连接先放到一个队列里面，然后起新的线程处理。这个过程会很快，但是并发请求很多的话，需要调高这个值，否则新来的连接请求会被拒绝。在一次压测的时候发现客户端返回大量的“Lost connection to MySQL”，就是因为back_log的默认值太小导致的。增大这个值会增大CPU负载并消耗更多的内存。默认值50，参考设置200

skip-name-resolve

关闭反向域名解析。MySQL默认会对每个客户端连接作反向域名解析，强烈建议关闭反向域名解析。关闭的方法是在my.cnf里面加一行skip-name-resolve

innodb_file_per_table

独立表空间。共享表空间指某一个数据库的所有表数据、索引全部放在一个文件中，默认这个共享表空间的文件路径在data目录下，默认的文件名为ibdata1初始化为10M。独立表空间指每一个表都会生成独立的.frm表描述文件来存储，还有一个.ibd文件。其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。

如果使用innodb，强烈建议打开这个设置，否则所有的innodb表共享一个文件，并且这个文件的大小不会因为表数据的减少而减小，时间长了会把磁盘搞爆。

max_connect_errors

当客户端连接服务端超时(超过connect_timeout)，服务端就会给这个客户端记录一次error，当出错的次数达到max_connect_errors的时候，这个客户端就会被锁定。除非执行FLUSH HOSTS命令。默认值10，参考设置1844674407370954751（能设多大，设多大）

connect_timeout

连接超时的秒数。默认值5，参考设置15

slave_net_timeout

MySQL主从复制的时候，当Master和Slave之间的网络中断，但是Master和Slave无法察觉的情况下（比如防火墙或者路由问题）。Slave会等待slave_net_timeout设置的秒数后，才能认为网络出现故障，然后才会重连并且追赶这段时间主库的数据。默认是3600秒，相信一个小时之后，黄花菜都凉了。默认值3600，参考设置30

lower_case_table_names

忽略表名大小写，否则使用mycat时会报错 lower_case_table_names=1

wait_timeout

连接过期秒数。当一个连接sleep超过wait_timeout秒后，MySQL服务端会中断这个连接。这个值设置的过长有可能会导致大量的sleep链接占用系统资源，过小会导致“MySQL has gone away”的错误。默认值28800 不变

key_buffer

主键缓存。如果发现有大量的slow log，可以尝试调高这个值，相应会带来更高的内存开销。

table_cache

给经常访问的表分配的内存。调大这个值，一般情况下可以降低磁盘IO, 但是相应会占用更多的内存。

BINLOG删除策略

【方法一】手动清理binlog

purge master logs before'2016-09-01 17:20:00'; //删除指定日期以前的日志索引中binlog日志文件

purge master logs to'mysql-bin.000022'; //删除指定日志文件的日志索引中binlog日志文件