mycat实现mysql基于GITD实现双主双从读写分离master节点高可用
架构说明10.0.0.18 master节点和10.0.0.22节点互为主
10.0.0.19 10.0.0.18的slave节点
10.0.0.22 master节点和10.0.0.19节点互为主
10.0.0.24 10.0.0.22的slave节点
10.0.0.23 mycat节点
mysql版本8.0.32
系统版本：rocky8.4mysql主从搭建
#搭建双主节点
#搭建第一个主10.0.0.18
#注释掉/etc/my.cnf.d/mysql-server.cnf
cat >/etc/my.cnf.d/mysql-server.cnf<<'EOF'
#
# This group are read by MySQL server.
# Use it for options that only the server (but not clients) should see
#
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/en/server-configuration-defaults.html
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
#
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
#log-error=/var/log/mysql/mysqld.log
#pid-file=/run/mysqld/mysqld.pid
#log-bin=/data/mysql/logbin/mysql-bin
EOF
#配置主节点的my.cat配置
cat >/etc/my.cnf<<'EOF'
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=18
#read-only
general_log
gtid_mode=ON
enforce_gtid_consistency
log-bin=/data/mysql/logbin/mysql-bin
EOF
#创建存放二进制日志的目录
mkdir -p /data/mysql/logbin/
chown -R mysql.mysql /data
#启动数据库
systemctl enable --now mysqld
#配置账号和授权
mysql
create user 'repluser'@'10.0.0.%' identified by '123456';
grant replication slave on *.* to 'repluser'@'10.0.0.%';
#创建mycat使用的账号
create user 'wbiao'@'10.0.0.%' IDENTIFIED BY '123456';
grant ALL ON hellodb.* TO 'wbiao'@'10.0.0.%';
#搭建第二个主10.0.0.22
#注释掉/etc/my.cnf.d/mysql-server.cnf
cat >/etc/my.cnf.d/mysql-server.cnf<<'EOF'
#
# This group are read by MySQL server.
# Use it for options that only the server (but not clients) should see
#
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/en/server-configuration-defaults.html
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
#
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
#log-error=/var/log/mysql/mysqld.log
#pid-file=/run/mysqld/mysqld.pid
#log-bin=/data/mysql/logbin/mysql-bin
EOF
#配置主节点的my.cat配置
cat >/etc/my.cnf<<'EOF'
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=22
#read-only
general_log
gtid_mode=ON
enforce_gtid_consistency
log-bin=/data/mysql/logbin/mysql-bin
EOF
#创建存放二进制日志的目录
mkdir -p /data/mysql/logbin/
chown -R mysql.mysql /data
#启动数据库
systemctl enable --now mysqld
#10.0.0.22指向10.0.0.18
执行change master to
CHANGE MASTER TO
MASTER_HOST='10.0.0.18',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
#开启IO线程和SQL线程
start slave;
#检查状态
show slave status\G
#检查
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| repluser | 10.0.0.%|
| wbiao | 10.0.0.%|
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)
10.0.0.18指向10.0.0.22
执行change master to
CHANGE MASTER TO
MASTER_HOST='10.0.0.22',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
#开启IO线程和SQL线程
start slave;
#检查状态
show slave status\G
#配置10.0.0.18的从节点10.0.0.19
#注释掉/etc/my.cnf.d/mysql-server.cnf
cat >/etc/my.cnf.d/mysql-server.cnf<<'EOF'
#
# This group are read by MySQL server.
# Use it for options that only the server (but not clients) should see
#
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/en/server-configuration-defaults.html
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
#
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
#log-error=/var/log/mysql/mysqld.log
#pid-file=/run/mysqld/mysqld.pid
#log-bin=/data/mysql/logbin/mysql-bin
EOF
#配置从节点10.0.0.19的my.cat配置
cat >/etc/my.cnf<<'EOF'
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=19
read-only
general_log
gtid_mode=ON
enforce_gtid_consistency
log-bin=/data/mysql/logbin/mysql-bin
EOF
#创建存放二进制日志的目录
mkdir -p /data/mysql/logbin/
chown -R mysql.mysql /data
#启动数据库
systemctl enable --now mysqld
#从节点10.0.0.19执行change master to
CHANGE MASTER TO
MASTER_HOST='10.0.0.18',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
#开启IO线程和SQL线程
start slave;
#检查状态
show slave status\G
##配置10.0.0.22的从节点10.0.0.24
#注释掉/etc/my.cnf.d/mysql-server.cnf
cat >/etc/my.cnf.d/mysql-server.cnf<<'EOF'
#
# This group are read by MySQL server.
# Use it for options that only the server (but not clients) should see
#
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/en/server-configuration-defaults.html
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
#
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
#log-error=/var/log/mysql/mysqld.log
#pid-file=/run/mysqld/mysqld.pid
#log-bin=/data/mysql/logbin/mysql-bin
EOF
#配置从节点10.0.0.24的my.cat配置
cat >/etc/my.cnf<<'EOF'
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=24
read-only
general_log
gtid_mode=ON
enforce_gtid_consistency
log-bin=/data/mysql/logbin/mysql-bin
EOF
#创建存放二进制日志的目录
mkdir -p /data/mysql/logbin/
chown -R mysql.mysql /data
#启动数据库
systemctl enable --now mysqld
#从节点10.0.0.24执行change master to
CHANGE MASTER TO
MASTER_HOST='10.0.0.22',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
#开启IO线程和SQL线程
start slave;
#检查状态
show slave status\G
#10.0.0.18导入hellodb的数据库
# mysql <hellodb_innodb.sql
##检查状态
show slave status\G
#所有节点检查数据
select * from hellodb.students;
#双主只能对一个主进行写操作
来源:https://www.cnblogs.com/yeyouqing/p/17426909.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！
页:
[1]