架构说明
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.4
mysql主从搭建
#搭建双主节点 #搭建第一个主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 #[mysqld] #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 # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] 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 #[mysqld] #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 # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] 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 statusG #检查 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 statusG #配置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 #[mysqld] #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 # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] 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 statusG ##配置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 #[mysqld] #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 # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] 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 statusG #10.0.0.18导入hellodb的数据库 [root@10 ~]# mysql hellodb_innodb.sql ##检查状态 show slave statusG #所有节点检查数据 select * from hellodb.students; #双主只能对一个主进行写操作
mycat搭建10.0.0.23
#安装java环境 yum -y install java #创建安装目录和解压 mkdir -p /apps tar -xf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /app #配置环境变量 [root@10 ~]# echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh [root@10 ~]# . /etc/profile.d/mycat.sh [root@10 ~]# echo $PATH /apps/mycat/bin:/usr/share/Modules/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin #配置mycat实现读写分离主从高可用 #修改schema.xml的配置文件 #balance="2",所有读操作都随机的在writeHost、readhost上分发。 #balance="1",全部的readHost与stand by writeHost参与select语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与M2互为主备) #writeType属性负载均衡类型,目前的取值有3种: #1.writeType="0", 所有写操作发送到配置的第一个writeHost,第一个挂了切到还生存的第二个writeHost,重新启动后已切换后的为准,切换记录在配置文件中:dnindex.properties. #2.writeType="1",所有写操作都随机的发送到配置的writeHost,1.5以后废弃不推荐。 #3.writeType="2",不执行写操作 [root@10 conf]# cat schema.xml <?xml version="1.0"?> "schema.dtd"> "http://io.mycat/"> "wbiao" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> "dn1" dataHost="localhost1" database="hellodb" /> "localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> select user() "host1" url="10.0.0.18:3306" user="wbiao" password="123456"> "host2" url="10.0.0.19:3306" user="wbiao" password="123456" /> "host3" url="10.0.0.22:3306" user="wbiao" password="123456"> "host4" url="10.0.0.23:3306" user="wbiao" password="123456" /> #server.xml配置文件 [root@10 conf]# cat server.xml <?xml version="1.0" encoding="UTF-8"?> "server.dtd"> "http://io.mycat/"> "nonePasswordLogin">0 "ignoreUnknownCommand">0 "useHandshakeV10">1 "removeGraveAccent">1 "useSqlStat">0 "useGlobleTableCheck">0 "sqlExecuteTimeout">300 "sequenceHandlerType">1 "sequnceHandlerPattern">(?:(s*nexts+values+fors*MYCATSEQ_(w+))(,|)|s)*)+ "subqueryRelationshipCheck">false "sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler "processorBufferPoolType">0 "serverPort">3306 "handleDistributedTransactions">0 "useOffHeapForMerge">0 "memoryPageSize">64k "spillsFileBufferSize">1k "useStreamOutput">0 "systemReserveMemorySize">384m "useZKSwitch">false "strictTxIsolation">false "parallExecute">0 "serverBacklog">2048 "root" defaultAccount="true"> "password">qwe123 "schemas">wbiao "defaultSchema">wbiao "user"> "password">user "schemas">wbiao "readOnly">true "defaultSchema">wbiao #启动mycat [root@10 conf]# mycat start Starting Mycat-server... 监控日志 [root@10 ~]# tail -F /apps/mycat/logs/wrapper.log STATUS | wrapper | 2023/05/24 02:44:41 | Wrapper Stopped STATUS | wrapper | 2023/05/24 02:44:45 | --> Wrapper Started as Daemon STATUS | wrapper | 2023/05/24 02:44:45 | Launching a JVM... INFO | jvm 1 | 2023/05/24 02:44:46 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org INFO | jvm 1 | 2023/05/24 02:44:46 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved. INFO | jvm 1 | 2023/05/24 02:44:46 | INFO | jvm 1 | 2023/05/24 02:44:48 | MyCAT Server startup successfully. see logs in logs/mycat.log #链接测试 [root@10 conf]# mysql -uroot -pqwe123 -h10.0.0.23 #读测试 mysql> select @@hostname; +------------+ | @@hostname | +------------+ | 10.0.0.24 | +------------+ 1 row in set (0.08 sec) mysql> select @@hostname; +------------+ | @@hostname | +------------+ | 10.0.0.22 | +------------+ 1 row in set (0.00 sec) mysql> select @@hostname; +------------+ | @@hostname | +------------+ | 10.0.0.19 | +------------+ 1 row in set (0.01 sec) mysql> select @@hostname; +------------+ | @@hostname | +------------+ | 10.0.0.19 | +------------+ 1 row in set (0.01 sec) mysql> select @@hostname; +------------+ | @@hostname | +------------+ | 10.0.0.24 | +------------+ 1 row in set (0.01 sec) mysql> select @@hostname; +------------+ | @@hostname | +------------+ | 10.0.0.22 | +------------+ 1 row in set (0.00 sec) #写测试 mysql> update teachers set name=@@hostname where tid=5; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | 10.0.0.18 | 93 | F | +-----+---------------+-----+--------+ 4 rows in set (0.00 sec) mysql> update teachers set name=@@hostname where tid=3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | 10.0.0.18 | 77 | F | | 4 | 10.0.0.18 | 93 | F | +-----+---------------+-----+--------+ 4 rows in set (0.00 sec) #关闭10.0.0.18的mysql [root@10 ~]# systemctl stop mysqld #mycat做主的高可用的写测试 mysql> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | 10.0.0.18 | 77 | F | | 4 | 10.0.0.18 | 93 | F | +-----+---------------+-----+--------+ 4 rows in set (0.00 sec) mysql> update teachers set name=@@hostname where tid=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | 10.0.0.22 | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | 10.0.0.18 | 77 | F | | 4 | 10.0.0.18 | 93 | F | +-----+---------------+-----+--------+ 4 rows in set (0.01 sec) mysql> update teachers set name=@@hostname where tid=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from teachers; +-----+-----------+-----+--------+ | TID | Name | Age | Gender | +-----+-----------+-----+--------+ | 1 | 10.0.0.22 | 45 | M | | 2 | 10.0.0.22 | 94 | M | | 3 | 10.0.0.18 | 77 | F | | 4 | 10.0.0.18 | 93 | F | +-----+-----------+-----+--------+ 4 rows in set (0.00 sec)