DNS+LVS+LAMP+NFS架构
网络架构图
一、环境准备
操作系统：rocky8.6
mysql: mysql8.0.26
mycat: 1.6.7.4
sersync:2.5.4
shopxo:2.3.0
lvs：
eth0:10.10.10.100 lvs-vip shopxo.magedu.org eth1:192.168.247.200 lvs-dip lvs 2vcpu 2G 100G 10.10.10.101 dns-master 2vcpu 2G 100G 10.10.10.102 dns-slave 2vcpu 2G 100G 192.168.247.201 web01 2vcpu 2G 100G 192.168.247.202 web02 2vcpu 2G 100G 192.168.247.203 NFS 2vcpu 2G 100G 192.168.247.204 NFS-backup 2vcpu 2G 100G 192.168.247.205 mycat 2vcpu 4G 100G 192.168.247.206 mysql-master 2vcpu 2G 100G 192.168.247.207 mysql-slave-01 2vcpu 2G 100G 192.168.247.208 mysql-slave-02 2vcpu 2G 100G
二、配置dns主从
1、主从节点安装dns服务
yum install bind bind-utils -y
[root@dns-master ~]# cat /etc/named.conf // // named.conf // // Provided by Red Hat bind package to configure the ISC BIND named(8) DNS // server as a caching only nameserver (as a localhost DNS resolver only). // // See /usr/share/doc/bind*/sample/ for example named configuration files. // options { // listen-on port 53 { 127.0.0.1; }; // listen-on-v6 port 53 { ::1; }; directory "/var/named"; dump-file "/var/named/data/cache_dump.db"; statistics-file "/var/named/data/named_stats.txt"; memstatistics-file "/var/named/data/named_mem_stats.txt"; secroots-file "/var/named/data/named.secroots"; recursing-file "/var/named/data/named.recursing"; // allow-query { localhost; }; allow-query { any; }; /* - If you are building an AUTHORITATIVE DNS server, do NOT enable recursion. - If you are building a RECURSIVE (caching) DNS server, you need to enable recursion. - If your recursive DNS server has a public IP address, you MUST enable access control to limit queries to your legitimate users. Failing to do so will cause your server to become part of large scale DNS amplification attacks. Implementing BCP38 within your network would greatly reduce such attack surface */ recursion yes; dnssec-enable yes; dnssec-validation yes; managed-keys-directory "/var/named/dynamic"; pid-file "/run/named/named.pid"; session-keyfile "/run/named/session.key"; /* https://fedoraproject.org/wiki/Changes/CryptoPolicy */ include "/etc/crypto-policies/back-ends/bind.config"; }; logging { channel default_debug { file "data/named.run"; severity dynamic; }; }; zone "." IN { type hint; file "named.ca"; }; include "/etc/named.rfc1912.zones"; include "/etc/named.root.key"; [root@dns-maste
[root@dns-master ~]# cat /etc/named.rfc1912.zones // named.rfc1912.zones: // // Provided by Red Hat caching-nameserver package // // ISC BIND named zone configuration for zones recommended by // RFC 1912 section 4.1 : localhost TLDs and address zones // and https://tools.ietf.org/html/rfc6303 // (c)2007 R W Franks // // See /usr/share/doc/bind*/sample/ for example named configuration files. // // Note: empty-zones-enable yes; option is default. // If private ranges should be forwarded, add // disable-empty-zone "."; into options // zone "localhost.localdomain" IN { type master; file "named.localhost"; allow-update { none; }; }; zone "localhost" IN { type master; file "named.localhost"; allow-update { none; }; }; zone "1.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.ip6.arpa" IN { type master; file "named.loopback"; allow-update { none; }; }; zone "1.0.0.127.in-addr.arpa" IN { type master; file "named.loopback"; allow-update { none; }; }; zone "0.in-addr.arpa" IN { type master; file "named.empty"; allow-update { none; }; }; zone "magedu.org" IN { type master; file "magedu.org.zone"; }; [root@dns-master ~]#
[root@dns-master ~]# cd /var/named/
[root@dns-master named]# cp named.localhost magedu.org.zone
[root@dns-master named]# cat magedu.org.zone $TTL 1D @ IN SOA master admin ( 0 ; serial 1D ; refresh 1H ; retry 1W ; expire 3H ) ; minimum NS master master A 10.10.10.101 shopxo A 10.10.10.100 [root@dns-master named]#
启动dns服务
[root@dns-master named]# chown named. magedu.org.zone
[root@dns-master named]# systemctl enable --now named Created symlink /etc/systemd/system/multi-user.target.wants/named.service → /usr/lib/systemd/system/named.service. [root@dns-master named]# systemctl status named ● named.service - Berkeley Internet Name Domain (DNS) Loaded: loaded (/usr/lib/systemd/system/named.service; enabled; vendor preset: disabled) Active: active (running) since Sat 2022-09-10 23:04:27 CST; 19s ago Process: 2311 ExecStart=/usr/sbin/named -u named -c ${NAMEDCONF} $OPTIONS (code=exited, status=0/SUCCESS) Process: 2306 ExecStartPre=/bin/bash -c if [ ! "$DISABLE_ZONE_CHECKING" == "yes" ]; then /usr/sbin/named-checkconf -z "$NAMEDCONF"; else echo "Checking of zone files is> Main PID: 2312 (named) Tasks: 5 (limit: 11188) Memory: 15.3M CGroup: /system.slice/named.service └─2312 /usr/sbin/named -u named -c /etc/named.conf Sep 10 23:04:27 dns-master named[2312]: network unreachable resolving './NS/IN': 2001:500:1::53#53 Sep 10 23:04:27 dns-master named[2312]: network unreachable resolving './DNSKEY/IN': 2001:7fd::1#53 Sep 10 23:04:27 dns-master named[2312]: network unreachable resolving './NS/IN': 2001:7fd::1#53 Sep 10 23:04:27 dns-master named[2312]: network unreachable resolving './DNSKEY/IN': 2001:7fe::53#53 Sep 10 23:04:27 dns-master named[2312]: network unreachable resolving './NS/IN': 2001:7fe::53#53 Sep 10 23:04:27 dns-master named[2312]: network unreachable resolving './DNSKEY/IN': 2001:503:c27::2:30#53 Sep 10 23:04:27 dns-master named[2312]: network unreachable resolving './NS/IN': 2001:503:c27::2:30#53 Sep 10 23:04:28 dns-master named[2312]: network unreachable resolving './DNSKEY/IN': 2001:500:12::d0d#53 Sep 10 23:04:28 dns-master named[2312]: managed-keys-zone: Key 20326 for zone . acceptance timer complete: key now trusted Sep 10 23:04:28 dns-master named[2312]: resolver priming query complete [root@dns-master named]#
slave节点配置
yum install bind bind-utils -y
[root@dns-slave ~]# cat /etc/named.conf // // named.conf // // Provided by Red Hat bind package to configure the ISC BIND named(8) DNS // server as a caching only nameserver (as a localhost DNS resolver only). // // See /usr/share/doc/bind*/sample/ for example named configuration files. // options { // listen-on port 53 { 127.0.0.1; }; // listen-on-v6 port 53 { ::1; }; directory "/var/named"; dump-file "/var/named/data/cache_dump.db"; statistics-file "/var/named/data/named_stats.txt"; memstatistics-file "/var/named/data/named_mem_stats.txt"; secroots-file "/var/named/data/named.secroots"; recursing-file "/var/named/data/named.recursing"; // allow-query { localhost; }; allow-transfer { none; }; /* - If you are building an AUTHORITATIVE DNS server, do NOT enable recursion. - If you are building a RECURSIVE (caching) DNS server, you need to enable recursion. - If your recursive DNS server has a public IP address, you MUST enable access control to limit queries to your legitimate users. Failing to do so will cause your server to become part of large scale DNS amplification attacks. Implementing BCP38 within your network would greatly reduce such attack surface */ recursion yes; dnssec-enable yes; dnssec-validation yes; managed-keys-directory "/var/named/dynamic"; pid-file "/run/named/named.pid"; session-keyfile "/run/named/session.key"; /* https://fedoraproject.org/wiki/Changes/CryptoPolicy */ include "/etc/crypto-policies/back-ends/bind.config"; }; logging { channel default_debug { file "data/named.run"; severity dynamic; }; }; zone "." IN { type hint; file "named.ca"; }; include "/etc/named.rfc1912.zones"; include "/etc/named.root.key"; [root@dns-slave ~]#
[root@dns-slave ~]# cat /etc/named.rfc1912.zones // named.rfc1912.zones: // // Provided by Red Hat caching-nameserver package // // ISC BIND named zone configuration for zones recommended by // RFC 1912 section 4.1 : localhost TLDs and address zones // and https://tools.ietf.org/html/rfc6303 // (c)2007 R W Franks // // See /usr/share/doc/bind*/sample/ for example named configuration files. // // Note: empty-zones-enable yes; option is default. // If private ranges should be forwarded, add // disable-empty-zone "."; into options // zone "localhost.localdomain" IN { type master; file "named.localhost"; allow-update { none; }; }; zone "localhost" IN { type master; file "named.localhost"; allow-update { none; }; }; zone "1.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.ip6.arpa" IN { type master; file "named.loopback"; allow-update { none; }; }; zone "1.0.0.127.in-addr.arpa" IN { type master; file "named.loopback"; allow-update { none; }; }; zone "0.in-addr.arpa" IN { type master; file "named.empty"; allow-update { none; }; }; zone "magedu.com" { type slave; masters { 10.10.10.101;}; file "slaves/magedu.org.slave"; }； [root@dns-slave ~]#
启动dns服务
[root@dns-slave ~]# systemctl enable --now named Created symlink /etc/systemd/system/multi-user.target.wants/named.service → /usr/lib/systemd/system/named.service. [root@dns-slave ~]# systemctl status named ● named.service - Berkeley Internet Name Domain (DNS) Loaded: loaded (/usr/lib/systemd/system/named.service; enabled; vendor preset: disabled) Active: active (running) since Sat 2022-09-10 23:13:23 CST; 4s ago Process: 2187 ExecStart=/usr/sbin/named -u named -c ${NAMEDCONF} $OPTIONS (code=exited, status=0/SUCCESS) Process: 2183 ExecStartPre=/bin/bash -c if [ ! "$DISABLE_ZONE_CHECKING" == "yes" ]; then /usr/sbin/named-checkconf -z "$NAMEDCONF"; else echo "Checking of zone files is> Main PID: 2188 (named) Tasks: 5 (limit: 11188) Memory: 15.5M CGroup: /system.slice/named.service └─2188 /usr/sbin/named -u named -c /etc/named.conf Sep 10 23:13:23 dns-slave named[2188]: network unreachable resolving './NS/IN': 2001:7fd::1#53 Sep 10 23:13:23 dns-slave named[2188]: network unreachable resolving './DNSKEY/IN': 2001:503:ba3e::2:30#53 Sep 10 23:13:23 dns-slave named[2188]: network unreachable resolving './NS/IN': 2001:503:ba3e::2:30#53 Sep 10 23:13:23 dns-slave named[2188]: network unreachable resolving './DNSKEY/IN': 2001:7fe::53#53 Sep 10 23:13:23 dns-slave named[2188]: network unreachable resolving './NS/IN': 2001:7fe::53#53 Sep 10 23:13:23 dns-slave named[2188]: network unreachable resolving './DNSKEY/IN': 2001:500:12::d0d#53 Sep 10 23:13:23 dns-slave named[2188]: network unreachable resolving './NS/IN': 2001:500:12::d0d#53 Sep 10 23:13:23 dns-slave named[2188]: zone shopxo.com/IN: refresh: non-authoritative answer from master 10.10.10.101#53 (source 0.0.0.0#0) Sep 10 23:13:24 dns-slave named[2188]: managed-keys-zone: Key 20326 for zone . acceptance timer complete: key now trusted Sep 10 23:13:24 dns-slave named[2188]: resolver priming query complete [root@dns-slave ~]#
[root@dns-slave named]# rndc reload
server reload successful
[root@dns-slave named]# ls slaves/
magedu.org.slave
[root@dns-slave named]#
三、配置lvs
安装ipvsadm包 yum install ipvsadm -y 开启ip 转发规则 echo "net.ipv4.ip_forward = 1" >> /etc/sysctl.conf sysctl -p 配置lvs规则 ipvsadm -A -t 10.10.10.100:80 -s rr ipvsadm -a -t 10.10.10.100:80 -r 192.168.247.201:80 -m ipvsadm -a -t 10.10.10.100:80 -r 192.168.247.202:80 -m
四、安装配置shopxo+php环境
shopxo版本2.2.9 php版本：7.4 下载安装包：wget https://github.com/gongfuxiang/shopxo/archive/refs/tags/v2.2.9.tar.gz rocky8版本安装php7.4 yum install -y https://mirrors.tuna.tsinghua.edu.cn/remi/enterprise/remi-release-8.rpm yum -y install httpd unzip php74-php.x86_64 php74-php-mysqlnd.x86_64 php74-php-json.x86_64 php74-php-gd.x86_64 php74-php-xml.x86_64 php74-php-pecl-zip.x86_64 systemctl enable --now httpd unzip v2.3.0.zip mv shopxo-v2.3.0/* /var/www/html/ chown -R apache.apache /var/www/html/
web01配置
[root@web01 ~]# tar -xvzf v2.2.9.tar.gz [root@web01 ~]# cp -r shopxo-2.2.9/* /var/www/html/ [root@web01 ~]# chown -R apache.apache /var/www/html/ [root@web01 ~]# ll -h /var/www/html/ total 144K -rw-r--r-- 1 apache apache 963 Sep 11 22:21 admin.php -rw-r--r-- 1 apache apache 710 Sep 11 22:21 api.php drwxr-xr-x 13 apache apache 4.0K Sep 11 22:21 app -rwxr-xr-x 1 apache apache 30K Sep 11 22:21 changelog.txt -rw-r--r-- 1 apache apache 1.3K Sep 11 22:21 composer.json -rw-r--r-- 1 apache apache 65K Sep 11 22:21 composer.lock drwxr-xr-x 2 apache apache 276 Sep 11 22:21 config -rw-r--r-- 1 apache apache 16 Sep 11 22:21 example.env drwxr-xr-x 6 apache apache 82 Sep 11 22:21 extend -rwxr-xr-x 1 apache apache 715 Sep 11 22:21 index.php -rw-r--r-- 1 apache apache 717 Sep 11 22:21 install.php -rw-r--r-- 1 apache apache 1.1K Sep 11 22:21 LICENSE.txt drwxr-xr-x 4 apache apache 240 Sep 11 22:21 public -rwxr-xr-x 1 apache apache 6.9K Sep 11 22:21 README.md drwxr-xr-x 2 apache apache 24 Sep 11 22:21 resources drwxr-xr-x 2 apache apache 24 Sep 11 22:21 rsakeys drwxr-xr-x 2 apache apache 24 Sep 11 22:21 runtime drwxr-xr-x 2 apache apache 42 Sep 11 22:21 sourcecode -rw-r--r-- 1 apache apache 220 Sep 11 22:21 think drwxr-xr-x 13 apache apache 209 Sep 11 22:21 vendor [root@web01 ~]# [root@web01 ~]# scp -r shopxo-2.2.9/ root@192.168.247.202:/root
web02配置
[root@web02 ~]# cp -r shopxo-2.2.9/* /var/www/html/ [root@web02 ~]# systemctl enable --now httpd Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service. [root@web02 ~]# chown -R apache.apache /var/www/html/ [root@web02 ~]# ll -h /var/www/html/ total 144K -rw-r--r-- 1 apache apache 963 Sep 11 22:24 admin.php -rw-r--r-- 1 apache apache 710 Sep 11 22:24 api.php drwxr-xr-x 13 apache apache 4.0K Sep 11 22:24 app -rwxr-xr-x 1 apache apache 30K Sep 11 22:24 changelog.txt -rw-r--r-- 1 apache apache 1.3K Sep 11 22:24 composer.json -rw-r--r-- 1 apache apache 65K Sep 11 22:24 composer.lock drwxr-xr-x 2 apache apache 276 Sep 11 22:24 config -rw-r--r-- 1 apache apache 16 Sep 11 22:24 example.env drwxr-xr-x 6 apache apache 82 Sep 11 22:24 extend -rwxr-xr-x 1 apache apache 715 Sep 11 22:24 index.php -rw-r--r-- 1 apache apache 717 Sep 11 22:24 install.php -rw-r--r-- 1 apache apache 1.1K Sep 11 22:24 LICENSE.txt drwxr-xr-x 4 apache apache 240 Sep 11 22:24 public -rwxr-xr-x 1 apache apache 6.9K Sep 11 22:24 README.md drwxr-xr-x 2 apache apache 24 Sep 11 22:24 resources drwxr-xr-x 2 apache apache 24 Sep 11 22:24 rsakeys drwxr-xr-x 2 apache apache 24 Sep 11 22:24 runtime drwxr-xr-x 2 apache apache 42 Sep 11 22:24 sourcecode -rw-r--r-- 1 apache apache 220 Sep 11 22:24 think drwxr-xr-x 13 apache apache 209 Sep 11 22:24 vendor [root@web02 ~]#
五、安装配置mycat+mysql-MGR数据库集群
5.1、安装mycat
yum install -y java mysql wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz mkdir /apps tar -xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/ echo "PATH=/apps/mycat/bin:$PATH" >> /etc/profile.d/mycat.sh source /etc/profile.d/mycat.sh
5.2、安装mysql MGR集群
mysql-master、mysql-slave-01、mysql-slave-02节点安装mysql-server服务
yum install -y mysql-server
master、slave-01、slave-02添加hosts，否则MGR集群从节点一直报RECOVERING
cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.247.206 mysql-master 192.168.247.207 mysql-slave-01 192.168.247.208 mysql-slave-02
配置my.cnf文件
[root@mysql-master ~]# vi /etc/my.cnf [root@mysql-master ~]# cat /etc/my.cnf # # 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] server-id=206 binlog_checksum=NONE default_authentication_plugin=mysql_native_password gtid_mode=ON enforce_gtid_consistency=ON loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee" loose-group_replication_start_on_boot=OFF loose-group_replication_local_address="192.168.247.206:24901" loose-group_replication_group_seeds="192.168.247.206:24901,192.168.247.207:24901,192.168.247.208:24901" loose-group_replication_bootstrap_group=OFF loose-group_replication_recovery_use_ssl=ON [root@mysql-master ~]# [root@mysql-slave-01 ~]# vi /etc/my.cnf [root@mysql-slave-01 ~]# cat /etc/my.cnf # # 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] server-id=207 binlog_checksum=NONE default_authentication_plugin=mysql_native_password gtid_mode=ON enforce_gtid_consistency=ON loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee" loose-group_replication_start_on_boot=OFF loose-group_replication_local_address="192.168.247.207:24901" loose-group_replication_group_seeds="192.168.247.206:24901,192.168.247.207:24901,192.168.247.208:24901" loose-group_replication_bootstrap_group=OFF loose-group_replication_recovery_use_ssl=ON [root@mysql-slave-01 ~]# [root@mysql-slave-02 ~]# vi /etc/my.cnf [root@mysql-slave-02 ~]# cat /etc/my.cnf # # 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] server-id=208 binlog_checksum=NONE default_authentication_plugin=mysql_native_password gtid_mode=ON enforce_gtid_consistency=ON loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee" loose-group_replication_start_on_boot=OFF loose-group_replication_local_address="192.168.247.208:24901" loose-group_replication_group_seeds="192.168.247.206:24901,192.168.247.207:24901,192.168.247.208:24901" loose-group_replication_bootstrap_group=OFF loose-group_replication_recovery_use_ssl=ON [root@mysql-slave-02 ~]#
启动mysql数据库
systemctl enable --now mysqld
在所有mysql节点配置复制账号及授权密码
SET SQL_LOG_BIN=0; CREATE USER repluser@'192.168.247.%' IDENTIFIED BY 'shopxo'; GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.247.%'; FLUSH PRIVILEGES; SET SQL_LOG_BIN=1;
在所有mysql节点安装插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so'; SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'group_replication'\G show plugins;
操作过程如下
[root@mysql-master ~]# systemctl start mysqld [root@mysql-master ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, 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> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER repluser@'192.168.247.%' IDENTIFIED BY 'shopxo'; Query OK, 0 rows affected (0.01 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.247.%'; Query OK, 0 rows affected (0.01 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 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.03 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> show plugins; +---------------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +---------------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | mysqlx | ACTIVE | DAEMON | NULL | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +---------------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.00 sec) mysql> [root@mysql-slave-01 ~]# systemctl start mysqld [root@mysql-slave-01 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, 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> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER repluser@'192.168.247.%' IDENTIFIED BY 'shopxo'; Query OK, 0 rows affected (0.02 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.247.%'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 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.03 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> show plugins; +---------------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +---------------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | mysqlx | ACTIVE | DAEMON | NULL | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +---------------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.00 sec) mysql> [root@mysql-slave-02 ~]# systemctl start mysqld [root@mysql-slave-02 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, 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> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.01 sec) mysql> CREATE USER repluser@'192.168.247.%' IDENTIFIED BY 'shopxo'; Query OK, 0 rows affected (0.01 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.247.%'; Query OK, 0 rows affected (0.00 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> show plugins; +---------------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +---------------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | daemon_keyring_proxy_plugin | ACTIVE | DAEMON | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CACHED_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SESSION_TEMP_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | TempTable | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | mysqlx_cache_cleaner | ACTIVE | AUDIT | NULL | GPL | | mysqlx | ACTIVE | DAEMON | NULL | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +---------------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.00 sec) mysql>
启动第一个主节点
SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF; SELECT * FROM performance_schema.replication_group_members;
在slave-01、slave-02上启动剩余的所有从节点
CHANGE MASTER TO MASTER_USER='repluser', MASTER_PASSWORD='shopxo' FOR CHANNEL 'group_replication_recovery'; CHANGE REPLICATION SOURCE TO SOURCE_USER='repluser', SOURCE_PASSWORD='shopxo' FOR CHANNEL 'group_replication_recovery'; START GROUP_REPLICATION;
查看集群节点
[root@mysql-master lib]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 8.0.26 Source distribution Copyright (c) 2000, 2021, 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> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 68827af0-31e6-11ed-bfd8-000c29da7455 | mysql-master | 3306 | ONLINE | PRIMARY | 8.0.26 | | group_replication_applier | 6de66a86-31e6-11ed-8ce8-000c2978352c | mysql-slave-01 | 3306 | RECOVERING | SECONDARY | 8.0.26 | | group_replication_applier | 70d18986-31e6-11ed-9397-000c29454d96 | mysql-slave-02 | 3306 | RECOVERING | SECONDARY | 8.0.26 | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 68827af0-31e6-11ed-bfd8-000c29da7455 | mysql-master | 3306 | ONLINE | PRIMARY | 8.0.26 | | group_replication_applier | 6de66a86-31e6-11ed-8ce8-000c2978352c | mysql-slave-01 | 3306 | ONLINE | SECONDARY | 8.0.26 | | group_replication_applier | 70d18986-31e6-11ed-9397-000c29454d96 | mysql-slave-02 | 3306 | ONLINE | SECONDARY | 8.0.26 | +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec) mysql>
六、联调web01、web02、proxysql、mysql-MGR
安装proxysql
wget https://github.com/sysown/proxysql/releases/download/v2.2.0/proxysql-2.2.0-1-centos8.x86_64.rpm yum -y localinstall proxysql-2.2.0-1-centos8.x86_64.rpm systemctl enable --now proxysql
在proxysql节点添加MySQL集群机器
mysql -uadmin -padmin -h 127.0.0.1 -P 6032
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.247.206',3306); insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.247.207',3306); insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.247.208',3306); load mysql servers to runtime; save mysql servers to disk;
在mysql master节点monitor、proxysql账号
CREATE USER 'monitor'@'%' IDENTIFIED BY "shopxo"; CREATE USER 'proxysql'@'%' IDENTIFIED BY "shopxo"; GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%'; GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%';
在proxysql节点设置监控账号密码
set mysql-monitor_username='monitor'; set mysql-monitor_password='shopxo'; insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('proxysql','shopxo',1,10,1);
在mysql集群master节点导入proxysql监控sql
[root@mysql-master ~]# mysql < proxysql-monitor.sql
USE sys; DELIMITER $$ CREATE FUNCTION IFZERO(a INT, b INT) RETURNS INT DETERMINISTIC RETURN IF(a = 0, b, a)$$ CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT) RETURNS INT DETERMINISTIC RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$ CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000)) RETURNS TEXT(10000) DETERMINISTIC RETURN GTID_SUBTRACT(g, '')$$ CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000)) RETURNS INT DETERMINISTIC BEGIN DECLARE result BIGINT DEFAULT 0; DECLARE colon_pos INT; DECLARE next_dash_pos INT; DECLARE next_colon_pos INT; DECLARE next_comma_pos INT; SET gtid_set = GTID_NORMALIZE(gtid_set); SET colon_pos = LOCATE2(':', gtid_set, 1); WHILE colon_pos != LENGTH(gtid_set) + 1 DO SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1); SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1); SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1); IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN SET result = result + SUBSTR(gtid_set, next_dash_pos + 1, LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) - SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1; ELSE SET result = result + 1; END IF; SET colon_pos = next_colon_pos; END WHILE; RETURN result; END$$ CREATE FUNCTION gr_applier_queue_length() RETURNS INT DETERMINISTIC BEGIN RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT Received_transaction_set FROM performance_schema.replication_connection_status WHERE Channel_name = 'group_replication_applier' ), (SELECT @@global.GTID_EXECUTED) ))); END$$ CREATE FUNCTION gr_member_in_primary_partition() RETURNS VARCHAR(3) DETERMINISTIC BEGIN RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >= ((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0), 'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN performance_schema.replication_group_member_stats USING(member_id) where performance_schema.replication_group_members.member_host=@@hostname); END$$ CREATE VIEW gr_member_routing_candidate_status AS SELECT sys.gr_member_in_primary_partition() AS viable_candidate, IF((SELECT (SELECT GROUP_CONCAT(variable_value) FROM performance_schema.global_variables WHERE variable_name IN ('read_only' , 'super_read_only')) != 'OFF,OFF' ), 'YES', 'NO') AS read_only, sys.gr_applier_queue_length() AS transactions_behind, Count_Transactions_in_queue AS 'transactions_to_cert' FROM performance_schema.replication_group_member_stats a JOIN performance_schema.replication_group_members b ON a.member_id = b.member_id WHERE b.member_host IN (SELECT variable_value FROM performance_schema.global_variables WHERE variable_name = 'hostname')$$ DELIMITER ;
在proxysql节点设置读写组
insert into mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) values (10,20,30,40,1,1,0,100); load mysql servers to runtime; save mysql servers to disk; load mysql users to runtime; save mysql users to disk; load mysql variables to runtime; save mysql variables to disk;
在proxysql节点设置读写规则
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',30,1); load mysql servers to runtime; save mysql servers to disk; load mysql users to runtime; save mysql users to disk; load mysql variables to runtime; save mysql variables to disk;
安装web01
[root@web02 ~]# cd /var/www/html/config [root@web02 config]# grep MyISAM shopxo.sql ) ENGINE = MyISAM AUTO_INCREMENT = 250 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '基本配置参数'; ) ENGINE = MyISAM AUTO_INCREMENT = 3450 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '地区'; [root@web02 config]# [root@web01 config]# sed -i 's/MyISAM/InnoDB/g' shopxo.sql [root@web01 config]# grep MyISAM shopxo.sql
安装web02
[root@web02 ~]# cd /var/www/html/config [root@web02 config]# grep MyISAM shopxo.sql ) ENGINE = MyISAM AUTO_INCREMENT = 250 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '基本配置参数'; ) ENGINE = MyISAM AUTO_INCREMENT = 3450 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '地区'; [root@web02 config]# [root@web02 config]# sed -i 's/MyISAM/InnoDB/g' shopxo.sql [root@web02 config]# grep MyISAM shopxo.sql
七、配置nfs、nfs-backup实现sersync+rsync实时备份同步
7.1、在nfs节点下载sersync
wget https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/sersync/sersync2.5.4_64bit_binary_stable_final.tar.gz tar -xf sersync2.5.4_64bit_binary_stable_final.tar.gz mv GNU-Linux-x86/ /usr/local/sersync echo 'PATH=/usr/local/sersync:$PATH' > /etc/profile.d/sersync.sh source /etc/profile.d/sersync.sh
7.2、在nfs节点部署nfs
yum install rsync nfs-utils -y systemctl enable --now nfs-server
cat /etc/exports /nfs-data 192.168.247.0/24(rw,all_squash,anonuid=48,anongid=48) #48是web01、web02节点apache服务id与组id exportfs -r showmount -e
将网站图片、视频动态文件拷贝到nfs目录 [root@web01 ~]#scp -r /var/www/html/public/static/upload/* root@192.168.247.203:/nfs-data
7.3、将nfs挂载在web01、web02
yum install nfs-utils -y echo "192.168.247.203:/nfs-data /var/www/html/public/static/upload/ nfs _netdev 0 0" >> /etc/fstab mount -a
7.4、配置nfs与nfs-backup节点sersync实时同步
yum install rsync rsync-daemon useradd rsync echo "Na0AkbbqXvwscGr2" | passwd --stdin rsync groupadd -g 48 apache useradd -u 48 -g apache apache # id 48 uid=48(apache) gid=48(apache) groups=48(apache) #
7.5、nfs节点配置
[root@nfs ~]# cat /etc/rsyncd.conf
uid = apache gid = apache use chroot = yes max connections = 20 pid file = /var/run/rsyncd.pid log file = /var/log/rsyncd.log [nfs-data] path = /nfs-data comment = webroot share read only = no dont compress = *.gz *.tgz *.zip *.z *.Z *.rpm *.deb *.bz2 auth users = rsync secrets file = /etc/rsync.pas [root@nfs ~]# vi /etc/rsync.pas [root@nfs ~]# [root@nfs ~]# cat /etc/rsync.pas
Na0AkbbqXvwscGr2
[root@nfs ~]# chmod 600 /etc/rsync.pas [root@nfs ~]#
nfs-backup节点配置
[root@nfs-backup /]# mkdir /nfs-data
[root@nfs-backup /]# cat /etc/rsyncd.conf uid = apache gid = apache use chroot = yes max connections = 20 pid file = /var/run/rsyncd.pid log file = /var/log/rsyncd.log [nfs-data] path = /nfs-data comment = webroot share read only = no dont compress = *.gz *.tgz *.zip *.z *.Z *.rpm *.deb *.bz2 auth users = rsync secrets file = /etc/rsyncd.secrets [root@nfs-backup /]# [root@nfs-backup /]# vi /etc/rsync.pas [root@nfs-backup /]# cat /etc/rsync.pas rsync:Na0AkbbqXvwscGr2
[root@nfs-backup /]# chmod 600 /etc/rsync.pas [root@nfs-backup /]#
7.6、配置sersync
[root@nfs ~]# cat /usr/local/sersync/confxml.xml <?xml version="1.0" encoding="ISO-8859-1"?> <head version="2.5"> <host hostip="localhost" port="8008"></host> <debug start="false"/> <fileSystem xfs="false"/> <filter start="false"> <exclude expression="(.*)\.svn"></exclude> <exclude expression="(.*)\.gz"></exclude> <exclude expression="^info/*"></exclude> <exclude expression="^static/*"></exclude> </filter> <inotify> <delete start="true"/> <createFolder start="true"/> <createFile start="false"/> <closeWrite start="true"/> <moveFrom start="true"/> <moveTo start="true"/> <attrib start="false"/> <modify start="false"/> </inotify> <sersync> <localpath watch="/nfs-data"> <remote ip="192.168.247.203" name="nfs-data"/> <!--<remote ip="192.168.8.39" name="tongbu"/>--> <!--<remote ip="192.168.8.40" name="tongbu"/>--> </localpath> <rsync> <commonParams params="-az"/> <auth start="true" users="rsync" passwordfile="/etc/rsync.pas"/> <userDefinedPort start="false" port="874"/><!-- port=874 --> <timeout start="false" time="100"/><!-- timeout=100 --> <ssh start="false"/> </rsync> <failLog path="/tmp/rsync_fail_daemon_log.sh" timeToExecute="60"/><!--default every 60mins execute once--> <crontab start="false" schedule="600"><!--600mins--> <crontabfilter start="false"> <exclude expression="*.php"></exclude> <exclude expression="info/*"></exclude> </crontabfilter> </crontab> <plugin start="false" name="command"/> </sersync> <plugin name="command"> <param prefix="/bin/sh" suffix="" ignoreError="true"/> <!--prefix /opt/tongbu/mmm.sh suffix--> <filter start="false"> <include expression="(.*)\.php"/> <include expression="(.*)\.sh"/> </filter> </plugin> <plugin name="socket"> <localpath watch="/opt/tongbu"> <deshost ip="192.168.138.20" port="8009"/> </localpath> </plugin> <plugin name="refreshCDN"> <localpath watch="/data0/htdocs/cms.xoyo.com/site/"> <cdninfo domainname="ccms.chinacache.com" port="80" username="xxxx" passwd="xxxx"/> <sendurl base="http://pic.xoyo.com/cms"/> <regexurl regex="false" match="cms.xoyo.com/site([/a-zA-Z0-9]*).xoyo.com/images"/> </localpath> </plugin> </head> [root@nfs ~]#
[root@nfs ~]# systemctl enable --now rsyncd [root@nfs ~]# sersync2 -d -r -n 5 -o /usr/local/sersync/confxml.xml [root@nfs-backup ~]# systemctl enable rsyncd
将sersync放到后台运行
screen
sersync2 -d -r -n 5 -o /usr/local/sersync/confxml.xml
7.7、验证是否实时同步
7.8、反向验证，是否符合需求
八、测试lvs调度
解决办法：
将web01、web02原有的网关指向lvs另一个IP地址
[root@web01 ~]# cat network-script.sh #!/bin/bash #read -p "Please enter your IP address: " ip cat >/etc/sysconfig/network-scripts/ifcfg-eth0<<EOF TYPE=Ethernet BOOTPROTO=none DEFROUTE=yes NAME=eth0 DEVICE=eth0 ONBOOT=yes IPADDR=192.168.247.$1 PREFIX=24 GATEWAY=192.168.247.200 EOF nmcli c reload nmcli c down eth0 nmcli c up eth0 [root@web01 ~]# ./network-script.sh 201 Connection 'eth0' successfully deactivated (D-Bus active path: /org/freedesktop/NetworkManager/ActiveConnection/4) Connection successfully activated (D-Bus active path: /org/freedesktop/NetworkManager/ActiveConnection/5) [root@web01 ~]# route -n Kernel IP routing table Destination Gateway Genmask Flags Metric Ref Use Iface 0.0.0.0 192.168.247.200 0.0.0.0 UG 100 0 0 eth0 192.168.247.0 0.0.0.0 255.255.255.0 U 100 0 0 eth0 [root@web01 ~]#
[root@web02 ~]# cat network-script.sh #!/bin/bash #read -p "Please enter your IP address: " ip cat >/etc/sysconfig/network-scripts/ifcfg-eth0<<EOF TYPE=Ethernet BOOTPROTO=none DEFROUTE=yes NAME=eth0 DEVICE=eth0 ONBOOT=yes IPADDR=192.168.247.$1 PREFIX=24 GATEWAY=192.168.247.200 EOF nmcli c reload nmcli c down eth0 nmcli c up eth0 [root@web02 ~]# ./network-script.sh 202 Connection 'eth0' successfully deactivated (D-Bus active path: /org/freedesktop/NetworkManager/ActiveConnection/4) Connection successfully activated (D-Bus active path: /org/freedesktop/NetworkManager/ActiveConnection/5) [root@web02 ~]# route -n Kernel IP routing table Destination Gateway Genmask Flags Metric Ref Use Iface 0.0.0.0 192.168.247.200 0.0.0.0 UG 100 0 0 eth0 192.168.247.0 0.0.0.0 255.255.255.0 U 100 0 0 eth0 [root@web02 ~]#
刷新即可
商城后台管理页面，账号密码admin shopxo
按F5测试lvs rr轮询
九、测试商城域名解析
9.1、在dns-master节点解析商城域名
9.2、windows客服端测试
十、上传商品测试
查看图片存放路径
