目录
2.3 配置文件my.cnf ---具体信息可以草考 标准my cnf文档
2.6 mysql自动启动设置用Systemctl start mysqld启动
查看防火墙状态和关闭防火墙
Root用户启停，所属为mysql用户。
systemctl status firewalld
systemctl stop firewalld
getenforce 永久关闭Selinux(需重启)
vim /etc/selinux/config
SELINUX=disabled
SELINUXTYPE=targeted
# 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 #重点关注这项
环境配置以及准备安装包如下
系统：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段，不然可能会出现网络问题）
useradd mysql –d /home/mysql
主机用户：具有sudo权限的mysql
针对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
/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 *
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
请根据实际环境进行参数配置
配置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
PATH=$PATH:$HOME/.local/bin:$HOME/bin:/mysql/db/bin
export PATH
执行如下脚本初始化数据库
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
但是不是一劳永逸的，需要每次登录都这样。
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 显示服务列表
————————————————
mysql -uroot -p (启动的时候可以看到初始密码)
mysql> alter user root@'localhost' identified by "mysql#123";
flush privileges;
以上步骤针对node2再来一遍，注意my.cnf中需要修改的项目，比如server-id
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;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| 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>
mysql> show slave status\G
看到两个YES，代表主主成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
原备节点（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;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| 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
赋予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; //删除存储过程
（必须是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
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
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
}
}
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
}
}
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
fi
以上配置文件中，另外一种ip的配置方法：
unicast_src_ip 192.168.188.149 #本机ip
unicast_peer {
192.168.188.134 #另外一台机器的ip
}
以上配置的是keepalived的非抢占模式，用到了关键字nopreempt
shell> sudo systemctl enable keepalived.service #设置开机自动启动
shell> sudo systemctl start keepalived #启动服务
shell> sudo systemctl stop keepalived #停止服务
shell> sudo systemctl restart keepalived #重启服务
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
做测试验证，是否可以实现高可用
将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
fi
发现123.log文件中一直被添加文本，说明检查一直在执行。
systemctl stop mysqld
ip a查询ip发现地址已经漂移
调整参数优化
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日志文件
【方法二】通过设置binlog过期的时间，使系统自动删除binlog文件
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 0 |
+------------------+-------+
mysql> set global expire_logs_days = 30; #设置binlog多少天过期