1.设置主从从mysql57服务器
(1)配置主数据库
[root@msater_5 ~]# systemctl stop firewalld [root@msater_5 ~]# setenforce 0 [root@msater_5 ~]# systemctl disable firewalld Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service. Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service. [root@msater_5 ~]# ls anaconda-ks.cfg mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz [root@msater_5 ~]# tar -xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz [root@msater_5 ~]# ls anaconda-ks.cfg mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz mysql-5.7.44-linux-glibc2.12-x86_64 [root@msater_5 ~]# cp -r mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql [root@msater_5 ~]# rm -rf /etc/my.cnf [root@msater_5 ~]# mkdir /usr/local/mysql/mysql-files [root@msater_5 ~]# useradd -r -s /sbin/nologin mysql [root@msater_5 ~]# chown mysql:mysql /usr/local/mysql/mysql-files/ [root@msater_5 ~]# chown 750 /usr/local/mysql/mysql-files/ [root@msater_5 ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql 2024-08-21T09:39:14.991434Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2024-08-21T09:39:15.126969Z 0 [Warning] InnoDB: New log files created, LSN=45790 2024-08-21T09:39:15.150963Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2024-08-21T09:39:15.214887Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 3ac52ccc-5fa1-11ef-98a1-000c29374e2c. 2024-08-21T09:39:15.215926Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2024-08-21T09:39:16.034519Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher. 2024-08-21T09:39:16.034537Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher. 2024-08-21T09:39:16.036321Z 0 [Warning] CA certificate ca.pem is self signed. 2024-08-21T09:39:16.384066Z 1 [Note] A temporary password is generated for root@localhost: RV3DtuxCi;A- [root@msater_5 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql57 [root@msater_5 ~]# vim /usr/local/mysql/my.cnf [mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/data socket=/tmp/mysql.sock port=3306 log-error=/usr/local/mysql/data/db01-master.err log-bin=/usr/local/mysql/data/binlog server-id=10 character_set_server=utf8mb4 [root@msater_5 ~]# service mysql57 start Starting MySQL.Logging to '/usr/local/mysql/data/db01-master.err'. SUCCESS! [root@msater_5 ~]# /usr/local/mysql/bin/mysql -p Enter password: mysql> alter user 'root'@'localhost' identified by 'root'; Query OK, 0 rows affected (0.00 sec) mysql> create user 'zzz'@'%' identified by 'zzz'; Query OK, 0 rows affected (0.00 sec) mysql> grant all on *.* to 'zzz'@'%'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> create user 'slave'@'%' identified by 'slave'; Query OK, 0 rows affected (0.00 sec) mysql> grant replication slave on *.* to 'slave'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
(2)配置slave01数据库
[root@slave01_5 ~]# ls anaconda-ks.cfg mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz [root@slave01_5 ~]# systemctl stop firewalld [root@slave01_5 ~]# systemctl disable firewalld Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service. Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service. [root@slave01_5 ~]# setenforce 0 [root@slave01_5 ~]# tar -xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz [root@slave01_5 ~]# cp -r mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql [root@slave01_5 ~]# rm -rf /etc/my.cnf [root@slave01_5 ~]# mkdir /usr/local/mysql/mysql-files [root@slave01_5 ~]# useradd -r -s /sbin/nologin mysql [root@slave01_5 ~]# chown mysql:mysql /usr/local/mysql/mysql-files/ [root@slave01_5 ~]# chown 750 /usr/local/mysql/mysql-files/ [root@slave01_5 ~]# yum -y install rsync
(3)配置slave02数据库
[root@slave02_5 ~]# ls anaconda-ks.cfg mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz [root@slave02_5 ~]# scp mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz 192.168.2.57:/root The authenticity of host '192.168.2.57 (192.168.2.57)' can't be established. ECDSA key fingerprint is SHA256:E2ARFFif/HyOpjlCgDRoPqYSl2OL4PwdcX1h9cPRJiY. ECDSA key fingerprint is MD5:35:b0:cd:3b:e0:fa:10:4a:22:5e:94:aa:b7:5c:e2:79. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.2.57' (ECDSA) to the list of known hosts. root@192.168.2.57's password: mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz 100% 663MB 85.5MB/s 00:07 [root@slave02_5 ~]# scp mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz 192.168.2.58:/root The authenticity of host '192.168.2.58 (192.168.2.58)' can't be established. ECDSA key fingerprint is SHA256:E2ARFFif/HyOpjlCgDRoPqYSl2OL4PwdcX1h9cPRJiY. ECDSA key fingerprint is MD5:35:b0:cd:3b:e0:fa:10:4a:22:5e:94:aa:b7:5c:e2:79. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.2.58' (ECDSA) to the list of known hosts. root@192.168.2.58's password: mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz 100% 663MB 83.1MB/s 00:07 [root@slave02_5 ~]# systemctl stop firewalld [root@slave02_5 ~]# systemctl disable firewalld [root@slave02_5 ~]# setenforce 0 [root@slave02_5 ~]# tar -xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz [root@slave02_5 ~]# cp -r mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql [root@slave02_5 ~]# rm -rf /etc/my.cnf [root@slave02_5 ~]# mkdir /usr/local/mysql/mysql-files [root@slave02_5 ~]# useradd -r -s /sbin/nologin mysql [root@slave02_5 ~]# chown mysql:mysql /usr/local/mysql/mysql-files/ [root@slave02_5 ~]# chown 750 /usr/local/mysql/mysql-files/ [root@slave02_5 ~]# yum -y install rsync
(4)同步data
[root@msater_5 ~]# service mysql57 stop Shutting down MySQL.... SUCCESS! [root@msater_5 ~]# rm -rf /usr/local/mysql/data/auto.cnf [root@msater_5 ~]# yum -y install rsync [root@msater_5 ~]# rsync -av /usr/local/mysql/data root@192.168.2.58:/usr/local/mysql/ [root@msater_5 ~]# rsync -av /usr/local/mysql/data root@192.168.2.59:/usr/local/mysql/
(5)修改slave01配置文件
[root@slave01_5 ~]# ls /usr/local/mysql/ bin docs lib man README support-files data include LICENSE mysql-files share [root@slave01_5 ~]# vim /usr/local/mysql/my.cnf [mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/data socket=/tmp/mysql.sock port=3306 log-error=/usr/local/mysql/data/err.log relay-log=/usr/local/mysql/data/relaylog character_set_server=utf8mb4 server-id=11 [root@slave01_5 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql57 [root@slave01_5 ~]# sed -i '$aexport PATH=$PATH:/usr/local/mysql/bin' /etc/profile [root@slave01_5 ~]# source /etc/profile
(6)修改slave02配置文件
[root@slave02_5 ~]# ls /usr/local/mysql bin docs lib man README support-files data include LICENSE mysql-files share [root@slave02_5 ~]# vim /usr/local/mysql/my.cnf [mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/data socket=/tmp/mysql.sock port=3306 log-error=/usr/local/mysql/data/err.log relay-log=/usr/local/mysql/data/relaylog character_set_server=utf8mb4 server-id=12 [root@slave02_5 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql57 [root@slave02_5 ~]# sed -i '$aexport PATH=$PATH:/usr/local/mysql/bin' /etc/profile [root@slave02_5 ~]# source /etc/profile
(7)主服务器锁表
[root@msater_5 ~]# mysql -proot 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 3 Server version: 5.7.44-log MySQL Community Server (GPL) Copyright (c) 2000, 2023, 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> select user,host from mysql.user; +---------------+-----------+ | user | host | +---------------+-----------+ | slave | % | | zzz | % | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ 5 rows in set (0.00 sec) mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000002 | 154 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
(8)配置主从服务
[root@slave01_5 ~]# mysql -uroot -proot 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 2 Server version: 5.7.44 MySQL Community Server (GPL) Copyright (c) 2000, 2023, 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> change master to master_host="192.168.2.57",master_user="slave",master_password="slave",master_log_file="binlog.000002",master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.57 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000002 Read_Master_Log_Pos: 154 Relay_Log_File: relaylog.000002 Relay_Log_Pos: 317 Relay_Master_Log_File: binlog.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes
(9)配置主从从服务
[root@slave02_5 ~]# mysql -proot mysql> change master to master_host="192.168.2.57",master_user="slave",master_password="slave",master_log_file="binlog.000002",master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.57 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000002 Read_Master_Log_Pos: 154 Relay_Log_File: relaylog.000002 Relay_Log_Pos: 317 Relay_Master_Log_File: binlog.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes
2.在mysql中添加eleme数据库设置为utf8mb4
[root@msater_5 ~]# mysql -proot mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) mysql> create database if not exists eleme charset utf8mb4; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | eleme | | mysql | | performance_schema | | sys | | test | +--------------------+ 6 rows in set (0.00 sec)
3.添加表t_user(master)
mysql> use eleme; Database changed mysql> create table t_user( -> id int primary key auto_increment, -> name varchar(45) not null, -> username varchar(45) not null, -> password varchar(45) not null, -> remark varchar(45) -> ); Query OK, 0 rows affected (0.01 sec) mysql> show tables; +-----------------+ | Tables_in_eleme | +-----------------+ | t_user | +-----------------+ 1 row in set (0.00 sec)
4.添加2行记录(master)
mysql> insert into t_user (id,name,username,password,remark)values(1,"超级管理员","adm"in","admin","超级管理员"); Query OK, 1 row affected (0.01 sec) mysql> insert into t_user (id,name,username,password,remark)values(2,"普通用户","guest,","guest","普通用户"); Query OK, 1 row affected (0.00 sec) mysql> select * from t_user; +----+-----------------+----------+----------+-----------------+ | id | name | username | password | remark | +----+-----------------+----------+----------+-----------------+ | 1 | 超级管理员 | admin | admin | 超级管理员 | | 2 | 普通用户 | guest | guest | 普通用户 | +----+-----------------+----------+----------+-----------------+ 2 rows in set (0.00 sec)
5.远程登录工具查看表数据:
6.使用mycat为三台数据库设置负载均衡(读写分离)
(1)前期环境部署
[root@mycat ~]# ls anaconda-ks.cfg Mycat-server-1.6.5-release-20180122220033-linux.tar.gz jdk-8u192-linux-x64.tar.gz [root@mycat ~]# tar -xf jdk-8u192-linux-x64.tar.gz [root@mycat ~]# tar -xf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz [root@mycat ~]# cp -r jdk1.8.0_192/ /usr/local/jdk [root@mycat ~]# cp -r mycat/ /usr/local/mycat [root@mycat ~]# sed -i '$aexport JAVA_HOME=/usr/local/jdk' /etc/profile [root@mycat ~]# source /etc/profile [root@mycat ~]# sed -i '$aexport PATH=$PATH:$JAVA_HOME/bin' /etc/profile [root@mycat ~]# source /etc/profile [root@mycat ~]# javac -version javac 1.8.0_192 [root@mycat ~]# /usr/local/mycat/bin/mycat console
(2)修改server.xml配置文件
[root@mycat ~]# vim /usr/local/mycat/conf/server.xml 93 <user name="haha" defaultAccount="true"> 94 <property name="password">haha</property> 95 <property name="schemas">eleme</property> # 注释 107 <!-- 108 <user name="user"> 109 <property name="password">user</property> 110 <property name="schemas">TESTDB</property> 111 <property name="readOnly">true</property> 112 </user> 113 -->
(3)修改schema.xml 配置文件
1.设置schema标签 添加dataNode="xxx" name=eleme
2.修改dataHost标签
[root@mycat ~]# vim /usr/local/mycat/conf/schema.xml 1 <?xml version="1.0"?> 2 <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 3 <mycat:schema xmlns:mycat="http://io.mycat/"> 4 5 <schema name="eleme" dataNode="dn1" checkSQLschema="false" sqlMaxLimit="10 0"> 6 </schema> 7 <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743" 8 /> --> 9 <dataNode name="dn1" dataHost="localhost1" database="eleme" /> 10 <!-- <dataNode name="dn2" dataHost="localhost1" database="db2" /> 11 <dataNode name="dn3" dataHost="localhost1" database="db3" />--> 12 <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" /> 13 <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" /> 14 <dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" /> 15 <dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> --> 16 <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" 17 writeType="0" dbType="mysql" dbDriver="native" switchTyp e="1" slaveThreshold="100"> 18 <heartbeat>select user()</heartbeat> 19 <!-- can have multi write hosts --> 20 <writeHost host="hostM1" url="192.168.2.57:3306" user="haha" 21 password="haha"> 22 <!-- can have multi read hosts --> 23 <readHost host="hostS2" url="192.168.2.58:3306" user="haha " password="haha" /> 24 <readHost host="hostS3" url="192.168.2.59:3306" user="haha " password="haha" /> 25 </writeHost> 26 <!-- 27 <writeHost host="hostS1" url="localhost:3316" user="root" 28 password="123456" /> 29 --> 30 <!-- <writeHost host="hostM2" url="localhost:3316" user="root" pas sword="123456"/> --> 31 </dataHost>
(4)启动服务
[root@mycat ~]# /usr/local/mycat/bin/mycat start Starting Mycat-server... [root@mycat ~]# netstat -lntup | grep 8066 tcp6 0 0 :::8066 :::* LISTEN 1916/j
7.客户端测试mycat
[root@client ~]# cd /usr/local/mysql/bin [root@client bin]# ./mysql -h192.168.2.19 -P8066 -uhaha -phaha 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 83 Server version: 5.6.29-mycat-1.6.5-release-20180122220033 MyCat Server (OpenCloundDB) Copyright (c) 2000, 2023, 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> show databases; +----------+ | DATABASE | +----------+ | eleme | +----------+ 1 row in set (0.01 sec) mysql> use eleme; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-----------------+ | Tables_in_eleme | +-----------------+ | t_user | +-----------------+ 1 row in set (0.01 sec) mysql> select * from t_user; +----+-----------------+----------+----------+-----------------+ | id | name | username | password | remark | +----+-----------------+----------+----------+-----------------+ | 1 | 超级管理员 | admin | admin | 超级管理员 | | 2 | 普通用户 | guest | guest | 普通用户 | +----+-----------------+----------+----------+-----------------+ 2 rows in set (0.01 sec) mysql>
8.部署java17环境
(1)java01
[root@java01 ~]# ls anaconda-ks.cfg jdk-17_linux-x64_bin.tar.gz [root@java01 ~]# tar -xf jdk-17_linux-x64_bin.tar.gz [root@java01 ~]# ls anaconda-ks.cfg jdk-17.0.12 jdk-17_linux-x64_bin.tar.gz [root@java01 ~]# cp -r jdk-17.0.12/ /usr/local/jdk [root@java01 ~]# vim /etc/profile export JAVA_HOME=/usr/local/jdk export PATH=$PATH:$JAVA_HOME/bin [root@java01 ~]# source /etc/profile [root@java01 ~]# javac --version javac 17.0.12 [root@java01 ~]# firewall-cmd --add-port=8080/tcp --permanent success [root@java01 ~]# firewall-cmd --reload success [root@java01 ~]# ls anaconda-ks.cfg application_(1).yml eleme_server-0.0.1-SNAPSHOT.jar jdk-17.0.12 jdk-17_linux-x64_bin.tar.gz [root@java01 ~]# mv application_\(1\).yml application.yml [root@java01 ~]# ls anaconda-ks.cfg application.yml eleme_server-0.0.1-SNAPSHOT.jar jdk-17.0.12 jdk-17_linux-x64_bin.tar.gz [root@java01 ~]# vim application.yml server: port: 8080 spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://192.168.2.19:8066/eleme username: haha password: haha [root@java01 ~]# java -jar eleme_server-0.0.1-SNAPSHOT.jar
(2)浏览器访问:192.168.2.11:8080
(3)java02
[root@java02 ~]# ls anaconda-ks.cfg eleme_server-0.0.1-SNAPSHOT.jar jdk-17_linux-x64_bin.tar.gz application.yml [root@java02 ~]# tar -xf jdk-17_linux-x64_bin.tar.gz [root@java02 ~]# cp -r jdk-17.0.12/ /usr/local/jdk [root@java02 ~]# vim /etc/profile [root@java02 ~]# source /etc/profile [root@java02 ~]# javac -version javac 17.0.12 [root@java02 ~]# vim application.yml server: port: 8080 spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://192.168.2.19:8066/eleme username: haha password: haha [root@java02 ~]# java -jar eleme_server-0.0.1-SNAPSHOT.jar
(4)浏览器访问:192.168.2.12:8080
9.部署LVS-nat模式
(1)lvs
[root@lvs ~]# ifconfig ens33:0 192.168.2.150 broadcast 192.168.2.121 netmask 255.255.255.255 up [root@lvs ~]# route add -host 192.168.2.150 dev ens33:0 [root@lvs ~]# yum -y install ipvsadm [root@lvs ~]# ipvsadm -C [root@lvs ~]# ipvsadm -A -t 192.168.2.150:8080 -s rr [root@lvs ~]# ipvsadm -a -t 192.168.2.150:8080 -r 192.168.2.11 -g [root@lvs ~]# ipvsadm -a -t 192.168.2.150:8080 -r 192.168.2.12 -g [root@lvs ~]# systemctl stop firewalld [root@lvs ~]# systemctl disable firewalld Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service. Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service. [root@lvs ~]# setenforce 0 [root@lvs ~]# systemctl stop firewalld [root@lvs ~]# ipvsadm -Ln IP Virtual Server version 1.2.1 (size=4096) Prot LocalAddress:Port Scheduler Flags -> RemoteAddress:Port Forward Weight ActiveConn InActConn TCP 192.168.2.150:8080 rr -> 192.168.2.11:8080 Route 1 0 1 -> 192.168.2.12:8080 Route 1 0 1
(2)java01
[root@java01 ~]# ifconfig lo:0 192.168.2.150 broadcast 192.168.2.150 netmask 255.255.255.255 up [root@java01 ~]# route add -host 192.168.2.150 dev lo:0 [root@java01 ~]# vim arp.sh [root@java01 ~]# source arp.sh
(3)java02
[root@java02 ~]# ifconfig lo:0 192.168.2.150 broadcast 192.168.2.150 netmask 255.255.255.255 up [root@java02 ~]# route add -host 192.168.2.150 dev lo:0 [root@java02 ~]# vim arp.sh [root@java02 ~]# source arp.sh