安装MySQL实现组复制
#应用ansible批量安装msyql
[root@easzlab-deploy-01 ansible]#ansible-playbook install_mysql.yaml
#配置hots文件
[root@MYSQL-Zabbix-01 ~]#cat /etc/hosts
10.0.0.180 MYSQL-Zabbix-01
10.0.0.181 MYSQL-Zabbix-02
10.0.0.182 MYSQL-Zabbix-03
#配置MySQL组复制文件
[root@MYSQL-Zabbix-01 ~]#cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE
server-id=180
gtid_mode=ON
enforce_gtid_consistency=ON
loose-group_replication_group_name="c2c8fe3f-d470-4f3e-b7f7-9783be82a1c9"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="10.0.0.180:24901"
loose-group_replication_group_seeds="10.0.0.180:24901,10.0.0.181:24901,10.0.0.182:24901"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_recovery_use_ssl=ON
[client]
socket=/data/mysql/mysql.sock
[root@MYSQL-Zabbix-02 ~]#cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE
server-id=181
gtid_mode=ON
enforce_gtid_consistency=ON
loose-group_replication_group_name="c2c8fe3f-d470-4f3e-b7f7-9783be82a1c9"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="10.0.0.181:24901"
loose-group_replication_group_seeds="10.0.0.180:24901,10.0.0.181:24901,10.0.0.182:24901"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_recovery_use_ssl=ON
[client]
socket=/data/mysql/mysql.sock
[root@MYSQL-Zabbix-03 ~]#cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE
server-id=182
gtid_mode=ON
enforce_gtid_consistency=ON
loose-group_replication_group_name="c2c8fe3f-d470-4f3e-b7f7-9783be82a1c9"
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="10.0.0.182:24901"
loose-group_replication_group_seeds="10.0.0.180:24901,10.0.0.181:24901,10.0.0.182:24901"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_recovery_use_ssl=ON
[client]
socket=/data/mysql/mysql.sock
#安装组复制插件(此处三台机器操作相同)
[root@MYSQL-Zabbix-01 ~]#mysql -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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>
mysql> SET SQL_LOG_BIN=0
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER repluser@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.03 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.04 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'group_replication'\G;
*************************** 1. row ***************************
PLUGIN_NAME: group_replication
PLUGIN_VERSION: 1.1
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: GROUP REPLICATION
PLUGIN_TYPE_VERSION: 1.4
PLUGIN_LIBRARY: group_replication.so
PLUGIN_LIBRARY_VERSION: 1.10
PLUGIN_AUTHOR: Oracle Corporation
PLUGIN_DESCRIPTION: Group Replication (1.1.0)
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
1 row in set (0.00 sec)
#主节点操作
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (1.46 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
#另外两个从节点操作
mysql> CHANGE MASTER TO MASTER_USER='repluser', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 5 warnings (0.02 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.42 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | e3b647d8-492b-11ed-8198-52540055073e | MYSQL-Zabbix-01 | 3306 | ONLINE | PRIMARY | 8.0.28 | XCom |
| group_replication_applier | e3bb5c6e-492b-11ed-a1a6-5254007c9d6c | MYSQL-Zabbix-02 | 3306 | ONLINE | SECONDARY | 8.0.28 | XCom |
| group_replication_applier | e3cd0e72-492b-11ed-8174-525400486193 | MYSQL-Zabbix-03 | 3306 | ONLINE | SECONDARY | 8.0.28 | XCom |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.01 sec)
#!!!!!!!注意zabbix数据库导入必须关闭组复制,否则会出现导入报错,必须要分开单独每个数据库都导入一次,清理二进制日志再开启组复制功能
[root@Zabbix5-server-01 ~]#scp /usr/share/doc/zabbix-server-mysql*/create.sql.gz 10.0.0.180:
[root@Zabbix5-server-01 ~]#scp /usr/share/doc/zabbix-server-mysql*/create.sql.gz 10.0.0.181:
[root@Zabbix5-server-01 ~]#scp /usr/share/doc/zabbix-server-mysql*/create.sql.gz 10.0.0.182:
[root@MYSQL-Zabbix-01 ~]#zcat create.sql.gz | mysql -uzabbix -pzabbix -h10.0.0.180 zabbix
[root@MYSQL-Zabbix-02 ~]#zcat create.sql.gz | mysql -uzabbix -pzabbix -h10.0.0.181 zabbix
[root@MYSQL-Zabbix-03 ~]#zcat create.sql.gz | mysql -uzabbix -pzabbix -h10.0.0.182 zabbix
#!!!!!!!MYCAT(zabbix不支持MYCAT和Proxysql)
#!!!!!!!zabbix数据库存在无主键的表不适用于mysql组复制的多主模式
MYSQL搭配keepalived
#主节点
[root@MYSQL-Zabbix-01 keepalived]#cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
root@ssss.com
}
#notification_email_from 985347841@qq.com
#smtp_server 127.0.0.1
#smtp_connect_timeout 30
router_id MYSQL-Zabbix-02
#vrrp_skip_check_adv_addr
#vrrp_strict
#vrrp_garp_interval 0
#vrrp_gna_interval 0
#vrrp_mcast_group4 230.6.6.6
}
#指定检测脚本
vrrp_script check_mysqld_server{
script "/usr/bin/killall -0 mysqld"
interval 1
weight -30
fall 2
rise 2
timeout 2
}
include /etc/keepalived/conf.d/*.conf
[root@MYSQL-Zabbix-01 keepalived]#cat /etc/keepalived/conf.d/vip_mysql.conf
vrrp_instance VI_1 {
state MASTER
interface enp1s0
virtual_router_id 66
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 123456
}
virtual_ipaddress {
10.0.0.202/24 dev enp1s0 label enp1s0:1
}
track_script {
check_mysqld_server
}
}
#从节点
[root@MYSQL-Zabbix-02 keepalived]#cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
root@ssss.com
}
#notification_email_from 985347841@qq.com
#smtp_server 127.0.0.1
#smtp_connect_timeout 30
router_id MYSQL-Zabbix-02
#vrrp_skip_check_adv_addr
#vrrp_strict
#vrrp_garp_interval 0
#vrrp_gna_interval 0
#vrrp_mcast_group4 230.6.6.6
}
#指定检测脚本
vrrp_script check_mysqld_server{
script "/usr/bin/killall -0 mysqld"
interval 1
weight -30
fall 2
rise 2
timeout 2
}
include /etc/keepalived/conf.d/*.conf
[root@MYSQL-Zabbix-02 keepalived]#cat /etc/keepalived/conf.d/vip_mysql.conf
vrrp_instance VI_1 {
state BACKUP
interface enp1s0
virtual_router_id 66
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 123456
}
virtual_ipaddress {
10.0.0.202/24 dev enp1s0 label enp1s0:1
}
track_script {
# check_mysqld_server
}
}
[root@MYSQL-Zabbix-01 keepalived]#ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: enp1s0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
link/ether 52:54:00:55:07:3e brd ff:ff:ff:ff:ff:ff
inet 10.0.0.180/24 brd 10.0.0.255 scope global enp1s0
valid_lft forever preferred_lft forever
inet 10.0.0.202/24 scope global secondary enp1s0:1
valid_lft forever preferred_lft forever
inet6 fe80::5054:ff:fe55:73e/64 scope link
valid_lft forever preferred_lft forever
安装Zabbix
[root@easzlab-deploy-01 ansible]#ansible-playbook install_zabbix.yaml
[root@Zabbix5-server-01 keepalived]#egrep -v "^#|^$" /etc/zabbix/zabbix_server.conf
SourceIP=10.0.0.201
LogFile=/var/log/zabbix/zabbix_server.log
LogFileSize=0
PidFile=/run/zabbix/zabbix_server.pid
SocketDir=/run/zabbix
DBHost=10.0.0.202
DBName=zabbix
DBUser=zabbix
DBPassword=zabbix
SNMPTrapperFile=/var/log/snmptrap/snmptrap.log
Timeout=4
AlertScriptsPath=/usr/lib/zabbix/alertscripts
ExternalScripts=/usr/lib/zabbix/externalscripts
FpingLocation=/usr/bin/fping
Fping6Location=/usr/bin/fping6
LogSlowQueries=3000
StatsAllowedIP=127.0.0.1
[root@Zabbix5-server-01 keepalived]#egrep -v "^//|^$" /etc/zabbix/web/zabbix.conf.php
<?php
$DB['TYPE'] = 'MYSQL';
$DB['SERVER'] = '10.0.0.202';
$DB['PORT'] = '0';
$DB['DATABASE'] = 'zabbix';
$DB['USER'] = 'zabbix';
$DB['PASSWORD'] = 'zabbix';
$DB['SCHEMA'] = '';
$DB['ENCRYPTION'] = true;
$DB['KEY_FILE'] = '';
$DB['CERT_FILE'] = '';
$DB['CA_FILE'] = '';
$DB['VERIFY_HOST'] = false;
$DB['CIPHER_LIST'] = '';
$DB['DOUBLE_IEEE754'] = true;
$ZBX_SERVER = '10.0.0.201';
$ZBX_SERVER_PORT = '10051';
$ZBX_SERVER_NAME = 'zabbix';
$IMAGE_FORMAT_DEFAULT = IMAGE_FORMAT_PNG;
[root@Zabbix5-server-01 keepalived]#egrep -v "^#|^$" /etc/zabbix/zabbix_agent2.conf
PidFile=/run/zabbix/zabbix_agent2.pid
LogFile=/var/log/zabbix/zabbix_agent2.log
LogFileSize=0
Server=127.0.0.1,10.0.0.201
ServerActive=127.0.0.1
Hostname=10.0.0.170
Include=/etc/zabbix/zabbix_agent2.d/*.conf
ControlSocket=/tmp/agent.sock
Zabbix的keepalived配置实现高可用
[root@Zabbix5-server-01 keepalived]#apt -y install keepalived
#主节点
[root@Zabbix5-server-01 keepalived]#cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
root@ssss.com
}
#notification_email_from 985347841@qq.com
#smtp_server 127.0.0.1
#smtp_connect_timeout 30
router_id Zabbix5-server-01
#vrrp_skip_check_adv_addr
#vrrp_strict
#vrrp_garp_interval 0
#vrrp_gna_interval 0
#vrrp_mcast_group4 230.6.6.6
}
#指定检测脚本
vrrp_script check_zabbix_server{
script "/usr/bin/killall -0 zabbix_server"
interval 1
weight -30
fall 2
rise 2
timeout 2
}
include /etc/keepalived/conf.d/*.conf
[root@Zabbix5-server-01 keepalived]#cat /etc/keepalived/conf.d/vip_zabbix.conf
vrrp_instance VI_1 {
state MASTER
interface enp1s0
virtual_router_id 66
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 123456
}
virtual_ipaddress {
10.0.0.201/24 dev enp1s0 label enp1s0:1
}
#notify_master "systemctl start zabbix-server"
#notify_backup "systemctl stop zabbix-server"
track_script {
check_zabbix_server
}
}
#从节点
[root@Zabbix5-server-02 ~]#cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
root@ssss.com
}
#notification_email_from 985347841@qq.com
#smtp_server 127.0.0.1
#smtp_connect_timeout 30
router_id Zabbix5-server-02
#vrrp_skip_check_adv_addr
#vrrp_strict
#vrrp_garp_interval 0
#vrrp_gna_interval 0
#vrrp_mcast_group4 230.6.6.6
}
#指定检测脚本
vrrp_script check_zabbix_server{
script "/usr/bin/killall -0 zabbix_server"
interval 1
weight -30
fall 2
rise 2
timeout 2
}
include /etc/keepalived/conf.d/*.conf
[root@Zabbix5-server-02 ~]#cat /etc/keepalived/conf.d/vip_zabbix.conf
vrrp_instance VI_1 {
state BACKUP
interface enp1s0
virtual_router_id 66
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 123456
}
virtual_ipaddress {
10.0.0.201/24 dev enp1s0 label enp1s0:1
}
#notify_master "systemctl start zabbix-server"
#notify_backup "systemctl stop zabbix-server"
track_script {
#check_zabbix_server
}
}
#注意主节点设置zabbix开机自启动,从节点关闭zabbix的开机自启动
