MYSQL MM HA安装配置及调优

Centos 7安装

系统安装过程不再演示​

主机环境信息：​

Roles​

Hostname​

IP Address​

Node1​

mysql01​

192.168.137.201​

Node2​

mysql02​

192.168.137.202​

Keepalived VIP​

192.168.137.205​

Mysql 8.0.25安装

配置yum源​

下载mysql8源并安装

[root@mysql01 ~]# wget -i -c https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
[root@mysql01 ~]# yum -y install mysql80-community-release-el7-3.noarch.rpm

安装Mysql 8.0.25​

[root@mysql01 ~]# yum provides mysql-community-server
[root@mysql01 ~]# yum -y install mysql-community-server-8.0.25-1.el7.x86_64

​

启动服务​

配置开机自启，并启动mysqld服务​

[root@mysql01 ~]# systemctl enable --now mysqld

修改密码​

获取初始密码​

[root@mysql01 ~]# grep "password" /var/log/mysqld.log
2021-06-11T16:27:57.560348Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: &F=lL&WXd2*P
[root@mysql02 ~]# grep "password" /var/log/mysqld.log
2021-06-11T16:27:58.439900Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: &-dkI8HcK69H

​

修改密码并配置​

[root@mysql01 ~]# mysql_secure_installation

​

Master-Master配置

数据库配置文件​

mysql01，相关配置部分如下所示​

[root@mysql01 ~]# cat /etc/my.cnf
server-id = 1 #任意自然n,只需要保证两台mysql主机不重复就可以
log-bin=mysql-bin #开启二进制日志
auto_increment_increment=2 #步进值auto_imcrement 。一般有n台主mysql就填n
auto_increment_offset=1 #起始值，一般填写第n台主机mysql.此为第一台mysql
binlog_expire_logs_seconds=604800 #binglog失效日期，默认30天，此设置为7天
#binlog-ignore=mysql #忽略mysql库，可以不填写
#binlog-ignore=infomation_schema #忽略information_schema库，一般不填写

​

mysql02，相关配置部分如下所示​

[root@mysql02 ~]# cat /etc/my.cnf
server-id = 2
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=2
binlog_expire_logs_seconds=604800
#binlog-ignore=mysql
#binlog-ignore=infomation_schema
#replicate-do-db=test

​

重启mysqld服务​

[root@mysql01 ~]# systemctl restart mysqld
[root@mysql02 ~]# systemctl restart mysqld

Node1主从配置​

Node1配置​

创建复制用户​

mysql> create user 'repl'@'192.168.137.202' identified with mysql_native_password by 'replP@ssw0rd';
mysql> grant replication slave on *.* to 'repl'@'192.168.137.202';
mysql> flush privileges;

​

检查账户权限​

mysql> show grants for 'repl'@'192.168.137.202';
+------------------------------------------------------------+
| Grants for repl@192.168.137.202 |
+------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `repl`@`192.168.137.202` |
+------------------------------------------------------------+

​

查看master状态，记录二进制文件名及位置信息​

show master status;

Example:​

Node2配置​

登录mysql02数据库，执行同步语句​

mysql> change master to
-> master_host='192.168.137.201',
-> master_user='repl',
-> master_password='replP@ssw0rd',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=869;

​

启动slave同步进程​

mysql> start slave;

检查slave状态，无error信息，则配置成功​

Node2主从配置​

Node2配置​

创建复制用户​

mysql> create user 'repl'@'192.168.137.201' identified with mysql_native_password by 'replP@ssw0rd';
mysql> grant replication slave on *.* to 'repl'@'192.168.137.201';
mysql> flush privileges;

​

检查账户权限​

mysql> show grants for 'repl'@'192.168.137.201';
+------------------------------------------------------------+
| Grants for repl@192.168.137.201 |
+------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `repl`@`192.168.137.201` |
+------------------------------------------------------------+

​

查看master状态，记录二进制文件名及位置信息​

show master status;

Example:​

Node1配置​

登录mysql01数据库，执行同步语句​

mysql> change master to
-> master_host='192.168.137.202',
-> master_user='repl',
-> master_password='replP@ssw0rd',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=869;

​

启动slave同步进程​

mysql> start slave;

检查slave状态，无error信息，则配置成功​

测试​

在mysql01创建db_mysql01​

mysql> create database db_mysql01;

在mysql02查看​

在mysql02创建db_mysql02​

mysql> create database db_mysql02;

在mysql01查看​

Keepalived配置

时间同步配置​

Node1和Node2安装chrony服务​

[root@mysql01 ~]# yum install chrony -y
[root@mysql01 ~]# systemctl enable --now chronyd

​

keepalived安装​

[root@mysql01 ~]# yum install keepalived -y
[root@mysql02 ~]# yum install keepalived -y

​

版本检查​

配置文件​

Node1配置​

编辑keepalived配置文件 /etc/keepalived/keepalived.conf​

! Configuration File for keepalived

vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 51
priority 101
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.137.205/24 dev ens33 label ens33:1
}
track_interface {
ens33 weight 5
}
}

​

Node2配置​

编辑keepalived配置文件 /etc/keepalived/keepalived.conf​

! Configuration File for keepalived

vrrp_instance VI_1 {
state BACKUP
interface ens33
virtual_router_id 51
priority 101
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.137.205/24 dev ens33 label ens33:1
}
track_interface {
ens33 weight 5
}
}

​

启动服务​

[root@mysql01 ~]# systemctl enable --now keepalived.service
[root@mysql02 ~]# systemctl enable --now keepalived.service

​

检查状态​

[root@mysql01 etc]# ip -brief address show
ens33 UP 192.168.137.201/24 192.168.137.205/24 fe80::a64b:f185:75fa:ecca/64
[root@mysql02 ~]# ip -br a
ens33 UP 192.168.137.202/24 fe80::a64b:f185:75fa:ecca/64 fe80::b269:4392:30e8:f3f1/64

​

Haproxy配置

Haproxy安装​

[root@mysql01 ~]# yum install -y haproxy
[root@mysql02 ~]# yum install -y haproxy

​

创建心跳检查用户​

在MySQL中创建一个没有权限的hacheck用户，密码为空。Haproxy使用这个帐户对MySQL数据库进行心跳检测​

mysql> use mysql;
mysql> INSERT INTO user (User,Host,ssl_cipher,x509_issuer,x509_subject) values ('hacheck',%,'','','');
mysql> flush privileges;

​

配置文件​

Node1和Node2均使用相同配置​

修改haproxy配置文件，/etc/haproxy/haproxy.cfg​

global
user haproxy
group haproxy
defaults
mode http
log global
retries 2
timeout connect 3000ms
timeout server 10m
timeout client 10m

listen STATS
bind 0.0.0.0:8888
stats enable
stats hide-version
stats uri /stats
stats refresh 10s
stats auth admin:password

listen DATABASE-NODES
bind 0.0.0.0:2727
mode tcp
option mysql-check user hacheck

server node1 192.168.137.201:3306 check
server node2 192.168.137.202:3306 check

​

启动服务​

[root@mysql01 ~]# systemctl enable --now haproxy
[root@mysql02 ~]# systemctl enable --now haproxy

​

检查状态​

配置keepalived通过检查haproxy服务状态进行failover​

vrrp_script chk_haproxy {
script "killall -0 haproxy" # check the haproxy process
# script "pidof haproxy"
interval 2 # every 2 seconds
weight 2 # add 2 points if OK
}

track_script {
chk_haproxy
}

​

Example：​

连接测试​

Failover测试

haproxy故障​

当前服务状态​

服务​

节点​状态​

keepalived​

haproxy​

mysqld​

VIP​

Node1​

UP​

UP​

UP​

UP​

Node2​

UP​

UP​

UP​

模拟停止Node1节点haproxy服务​

服务​

节点​状态​

keepalived​

haproxy​

mysqld​

VIP​

Node1​

UP​

UP --> DOWN​

UP​

UP ↓​

Node2​

UP​

UP​

UP​

VIP将failover至Node2​

服务​

节点​状态​

keepalived​

haproxy​

mysqld​

VIP​

Node1​

UP​

DOWN​

UP​

Node2​

UP​

UP​

UP​

UP​

测试结果：haproxy单点故障，不影响mysql正常提供服务​

重新启动故障haproxy服务，VIP不会跳回至Node1​

keepalived故障​

当前服务状态​

服务​

节点​状态​

keepalived​

haproxy​

mysqld​

VIP​

Node1​

UP​

UP​

UP​

Node2​

UP​

UP​

UP​

UP​

模拟停止Node2节点keepalived服务​

服务​

节点​状态​

keepalived​

haproxy​

mysqld​

VIP​

Node1​

UP​

UP​

UP​

Node2​

UP --> DOWN​

UP​

UP​

UP ↓​

VIP将failover至Node1​

服务​

节点​状态​

keepalived​

haproxy​

mysqld​

VIP​

Node1​

UP​

UP​

UP​

UP​

Node2​

DOWN​

UP​

UP​

测试结果：keepalived单点故障，不影响mysql正常提供服务​

重新启动故障keepalived服务，VIP不会跳回至Node2​

mysql故障​

当前服务状态​

服务​

节点​状态​

keepalived​

haproxy​

mysqld​

VIP​

Node1​

UP​

UP​

UP​

UP​

Node2​

UP​

UP​

UP​

模拟停止Node1节点mysqld服务​

服务​

节点​状态​

keepalived​

haproxy​

mysqld​

VIP​

Node1​

UP​

UP​

UP --> DOWN​

UP​

Node2​

UP ​

UP​

UP​

测试结果：haproxy检测到mysql状态异常，将Node1踢出，不影响mysql正常提供服务​

​

开启 GTID复制

配置文件​

已完成主从同步可直接编辑my.cnf开启Gtid模式​

gtid-mode=ON
enforce-gtid-consistency=1
log-slave-updates=1

​

Example：​

重启服务​

[root@mysql01 ~]# systemctl restart mysqld.service
[root@mysql02 ~]# systemctl restart mysqld.service

​

检查复制情况​

mysql> show variables like '%gtid%';

开启SSL复制

默认情况下，mysql 包安装会在安装时在数据目录中创建 SSL 文件。 如果您想使用不同的自签名证书，可按照以下指引进行创建。​

https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-rsa-files.html​

Node1及Node2使用同一份证书文件​

[root@mysql01 ~]# rsync -avz /var/lib/mysql/*.pem 192.168.137.202:/var/lib/mysql/

​

配置文件​

Node1和Node2编辑my.cnf，增加以下参数​

ssl=on
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem

​

重启服务​

[root@mysql01 ~]# systemctl restart mysqld.service
[root@mysql02 ~]# systemctl restart mysqld.service

​

连接测试​

客户端使用ssl连接测试​

[root@nginx ~]# mysql -uadmin -p -P2727 -h 192.168.137.205 --ssl-cert=client-cert.pem --ssl-key=client-key.pem -e '\s'

同步设置​

Node2配置​

mysql> stop slave;
mysql> CHANGE MASTER TO
-> MASTER_HOST = '192.168.137.201',
-> MASTER_USER = 'repl',
-> MASTER_PASSWORD = 'replP@ssw0rd',
-> MASTER_SSL = 1,
-> MASTER_SSL_CA = '/var/lib/mysql/ca.pem',
-> MASTER_SSL_CERT = '/var/lib/mysql/client-cert.pem',
-> MASTER_SSL_KEY = '/var/lib/mysql/client-key.pem',
-> MASTER_AUTO_POSITION = 1;
mysql> start slave;

​

Node1配置​

mysql> stop slave;
mysql> CHANGE MASTER TO
-> MASTER_HOST = '192.168.137.202',
-> MASTER_USER = 'repl',
-> MASTER_PASSWORD = 'replP@ssw0rd',
-> MASTER_SSL = 1,
-> MASTER_SSL_CA = '/var/lib/mysql/ca.pem',
-> MASTER_SSL_CERT = '/var/lib/mysql/client-cert.pem',
-> MASTER_SSL_KEY = '/var/lib/mysql/client-key.pem',
-> MASTER_AUTO_POSITION = 1;
mysql> start slave;

​

检查状态​

Semisync半同步配置

加载semisync插件​

Node1及Node2上执行​

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> show variables like '%semi%';

​

Example：​

​​rpl_semi_sync_master_timeout=10000​​

表示主库在某次事务中，如果等待时间超过10秒，则降级为普通模式，不再等待备库。如果主库再次探测到备库恢复了，则会自动再次回到semisync模式。​

​​rpl_semi_sync_master_wait_point=AFTER_SYNC​​

AFTER_SYNC工作流程：​

客户端提交一个事务，master将事务写入binlog并刷新到磁盘，发送到slave，master等待slave反馈。​

slave接收master的binlog，写到本地的relaylog里。发送确认信息给master。​

当接收到slave回馈，master提交事务并返回结果给客户端。这样就保证了主从数据一致。​

开启semisync ​

Node1及Node2上执行​

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

​

并修改my.cnf，添加以下配置​

rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled = 1

​

Example：​

生效半同步机制​

Node1及Node2上执行​

mysql> stop slave;start slave;

​

调优

Mysql参数​

InnoDB调优​

innodb_buffer_pool_size=8G # 不超过物理内存70%​

innodb_buffer_pool_instances=4 # 缺省值为1,内存较大时可结合考虑设定,提高性能.​

#innodb_buffer_pool_chunk_size=innodb_buffer_pool_size/innodb_buffer_pool_instances​

innodb_buffer_pool_chunk_size=2G ​

innodb_file_per_table=1 # 缺省打开,独立表空间方式效率和性能较好​

innodb_flush_method=O_DIRECT # O_DIRECT可较好地降低IO​

#缺省值为4,innodb_read_io_threads + innodb_write_io_threads = 可用CPU核数​

innodb_write_io_threads=4 ​

innodb_read_io_threads=4​

#主要在有频繁写操作时所进行的优化选项,具体跟磁盘性能的数据相关联​

innodb_io_capacity=2000​

innodb_io_capacity_max=6000​

innodb_thread_concurrency=0 # 建议关闭​

innodb_autoinc_lock_mode=2 # 提高性能​

innodb_log_file_size=256M # 上限值为4G,建议取值256M兼顾性能和恢复的速度​

innodb_log_buffer_size=16M # 事务在内存中的缓冲,如有大量事务可考虑增加此值​

innodb_flush_logs_at_trx_commit=1 # 在对于最后一秒数据高可用要求不高的情况下,可设定为2追求最好性能​

缓存调优​

tmp_table_size=256M # 默认为16M,建议调整​

max_heap_table_size=128M # 默认为16M,建议调整​

read_buffer_size=1M # 读入缓冲区大小,缺省值1/8M,由于是connection级别调整,不要过大​

read_rnd_buffer_size=1M # 随机缓冲区大小,缺省值1/4M,由于是connection级别调整,不要过大​

thread_cache_size=64 # 客户端口连接后所放置的线程缓冲,缺省值为9,建议稍微调大​

join_buffer_size=1M # 缺省值1/8M,由于是connection级别调整,不要过大​

sort_buffer_size=1M # 缺省值1/8M,由于是connection级别调整,不要过大​

连接层调优​

max_connections=10000 # 连接数,根据交易笔数设置，相当于并发数​

max_connect_errors=1000 # 错误连接数,超过将禁止host的连接请求​

connect_timeout=10 # 连接超时​

max_user_connections=0 # 单用户连接数最大值​

wait_timeout=120 # 请求的最大连接时间,非交互方式的连接如果超过此时间,状态为sleep,MYSQL会主动进行切断​

back_log=256 # 控制当连接数达到max_connections,有多少连接可以暂放堆栈中，等待当前连接资源释放​

slow log设置​

slow_query_log = 1 ​

slow_query_log_file = /var/log/mysql-slow.log ​

long_query_time = 10​

可逐渐降低long_query_time值，分析瓶颈​

Keepalived参数​

[root@mysql01 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

vrrp_script chk_haproxy {
# script "killall -0 haproxy" # check the haproxy process
script "pidof haproxy"
interval 2 # every 2 seconds
weight 2 # add 2 points if OK
}

vrrp_instance VI_1 {
# nopreempt
state MASTER
interface ens33
virtual_router_id 51
priority 101
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.137.205/24 dev ens33 label ens33:1
}
track_script {
chk_haproxy
}
track_interface {
ens33 weight 5
}
}

​

Haproxy参数​

事务持久性​

单主模型​

读写分离

持续更新...