概述
目录
一、 安装前准备
1.1 关闭centos7系统的防火墙和Selinux
1.2 修改系统内核参数
1.3 环境配置安装包准备
1.4 mysql用户配置
1.5 删除默认的mysql和mariadb
二、 数据库安装
2.1 安装目录
2.2 解压文件包并修改名称
2.3 配置文件my.cnf ---具体信息可以草考 标准my cnf文档
2.4 配置环境变量
2.5 初始化数据库
2.6 mysql自动启动设置用Systemctl start mysqld启动
2.7 登录数据库并修改密码
2.8 配置主从
2.9 状态检查
2.10 配置双主
2.11 双主测试
三、 Keepalived安装
3.1 安装相关的系统环境
3.2 源码编译安装
3.3 Master 主机上的keepalived配置
3.4 bakcup主机上的keepalived配置
3.5 配置checkmysql脚本
3.6 启动keepalived
3.7 验证登录
3.8 漂移验证
3.9 配置调整
一、 安装前准备
1.1 关闭centos7系统的防火墙和Selinux
查看防火墙状态和关闭防火墙
Root用户启停，所属为mysql用户。
systemctl status firewalld
systemctl stop firewalld
getenforce 永久关闭Selinux(需重启)
vim /etc/selinux/config
SELINUX=disabled
SELINUXTYPE=targeted
1.2 修改系统内核参数
# 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;
+------------------+----------+--------------+------------------+-------------------+
| 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>
2.9 状态检查
mysql> show slave statusG
看到两个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;
+------------------+----------+--------------+------------------+-------------------+
| 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 statusG
看到两个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_insertG
#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
fi
以上配置文件中，另外一种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
fi
发现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日志文件
【方法二】通过设置binlog过期的时间，使系统自动删除binlog文件
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 0 |
+------------------+-------+
mysql> set global expire_logs_days = 30; #设置binlog多少天过期
最后
以上就是个性黑裤为你收集整理的mysql数据库高可用-双主集群+keepalived部署，简易mysql部署一、 安装前准备二、 数据库安装三、 Keepalived安装的全部内容，希望文章能够帮你解决mysql数据库高可用-双主集群+keepalived部署，简易mysql部署一、 安装前准备二、 数据库安装三、 Keepalived安装所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
![[MySQL]-双主+keepalived实现高可用[MySQL]-双主+keepalived实现高可用一、环境介绍二、实战操作三、总结](/uploads/reation/bcimg4.png)
发表评论 取消回复