青岛实训 8月21号 day33

1.设置主从从mysql57服务器

1）配置主数据库
 [root@msater_5 ~]# systemctl stop filewalld
 [root@msater_5 ~]# setenforce 0
 [root@msater_5 ~]# systemctl disable filewalld
 [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 ~]# 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:16.036321Z 0 [Warning] CA certi2024-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
 [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 'li'@'%' identified by 'li';
 Query OK, 0 rows affected (0.00 sec)
 mysql> grant all on *.* to 'li'@'%';
 uery 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
 [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@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
 [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
4）同步data
 [root@msater_5 ~]# service mysql57 stop
 [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=3310
 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@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=3310
 log-error=/usr/local/mysql/data/err.log
 relay-log=/usr/local/mysql/data/relaylog
 character_set_server=utf8mb4
 server-id=12
 [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
7）主服务器锁表
 [root@msater_5 ~]# mysql -proot
 mysql> select user,host from mysql.user;
 +---------------+-----------+
 | user          | host      |
 +---------------+-----------+
 | slave         | %         |
 | li            | %         |
 | 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>  change master to master_host="10.0.0.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@slave01_5 ~]# mysql -uroot -proot
 mysql>  change master to master_host="10.0.0.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 eleme.t_user values(1,'超级管理员','admin','admin','超级管理员'),(2,'普通用户','guest','guest','普通用户');
 Query OK, 2 rows affected (0.00 sec)
 Records: 2  Duplicates: 0  Warnings: 0
 mysql> select * from eleme.t_user;
 +----+-----------------+----------+----------+-----------------+
 | id | name            | username | password | remark          |
 +----+-----------------+----------+----------+-----------------+
 |  1 | 超级管理员      | admin    | admin    | 超级管理员      |
 |  2 | 普通用户        | guest    | guest    | 普通用户        |
 +----+-----------------+----------+----------+-----------------+
 2 rows in set (0.00 sec)

5.使用mycat为三台数据库设置负载均衡（读写分离）

1）前期jdk环境部署
 [root@mycat ~]# systemctl stop firewalld
 [root@mycat ~]# systemctl disable firewalld
 [root@mycat ~]# ls
 anaconda-ks.cfg             Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
 jdk-8u192-linux-x64.tar.gz  v
 [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/
 [root@mycat ~]# ls /usr/local/jdk/
 bin             lib          src.zip
 COPYRIGHT       LICENSE      THIRDPARTYLICENSEREADME-JAVAFX.txt
 include         man          THIRDPARTYLICENSEREADME.txt
 javafx-src.zip  README.html
 jre             release
 [root@mycat ~]# sed -i '$aexport JAVA_HOME=/usr/local/jdk' /etc/profile
 [root@mycat ~]# source /etc/profile
 [root@mycat ~]# $JAVA_HOME
 -bash: /usr/local/jdk: 是一个目录
 [root@mycat ~]# sed -i '$aexport PATH=$PATH:$JAVA_HOME/bin' /etc/profile
 [root@mycat ~]# source /etc/profile
 [root@mycat ~]# $PATH
 -bash: /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/local/jdk/bin: 没有那个文件或目录
 [root@mycat ~]# javac -version 
 javac 1.8.0_192
2）修改server.xml配置文件
 [root@mycat ~]# vim /usr/local/mycat/conf/server.xml
 93         <user name="li" defaultAccount="true">
 94                 <property name="password">li</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 配置文件

4）启动服务
 [root@mycat ~]# /usr/local/mycat/bin/mycat start
 Starting Mycat-server...
 [root@mycat ~]# netstat -lnput | grep 8066
 tcp6       0      0 :::8066                 :::*                    LISTEN      1336/java   

6.客户端测试mycat

7.部署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.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://10.0.0.11:8066/eleme
    username: li
    password: li
 [root@java01 ~]# java -jar eleme_server-0.0.1-SNAPSHOT.jar 
2）浏览器访问：10.0.0.12:8080
3）java02
 [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.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://10.0.0.11:8066/eleme
    username: li
    password: li
 [root@java01 ~]# java -jar eleme_server-0.0.1-SNAPSHOT.jar 
4）浏览器访问：10.0.0.13:8080

8.部署LVS-nat模式

1）lvs
 [root@lvs ~]# ifconfig ens33:0 10.0.0.100 broadcast 10.0.0.100 netmask 255.255.255.255 up
 [root@lvs ~]# route add -host 10.0.0.100 dev ens33:0
 [root@lvs ~]# yum -y install ipvsadm
 [root@lvs ~]# ipvsadm -C
 [root@lvs ~]# ipvsadm -A -t 10.0.0.100:8080 -s rr
 [root@lvs ~]# ipvsadm -a -t 10.0.0.100:8080 -r 10.0.0.12 -g
 [root@lvs ~]# ipvsadm -a -t 10.0.0.100:8080 -r 10.0.0.13 -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
   TCP  10.0.0.100:8080 rr
     -> 10.0.0.12:8080            Route   1      0          1         
     -> 10.0.0.13:8080            Route   1      0          1    
2）java01
 [root@java01 ~]# ifconfig lo:0 10.0.0.100 broadcast 10.0.0.100 netmask 255.255.255.255 up
 [root@java01 ~]# route add -host 10.0.0.100 dev lo:0
 [root@java01 ~]# echo 1 > /proc/sys/net/ipv4/conf/lo/arp_ignore
 [root@java01 ~]# echo 2 > /proc/sys/net/ipv4/conf/lo/arp_announce
 [root@java01 ~]# echo 1 > /proc/sys/net/ipv4/conf/all/arp_ignore
 [root@java01 ~]# echo 2 > /proc/sys/net/ipv4/conf/all/arp_announce
3）java02
 [root@java01 ~]# ifconfig lo:0 10.0.0.100 broadcast 10.0.0.100 netmask 255.255.255.255 up
 [root@java01 ~]# route add -host 10.0.0.100 dev lo:0
 [root@java01 ~]# echo 1 > /proc/sys/net/ipv4/conf/lo/arp_ignore
 [root@java01 ~]# echo 2 > /proc/sys/net/ipv4/conf/lo/arp_announce
 [root@java01 ~]# echo 1 > /proc/sys/net/ipv4/conf/all/arp_ignore
 [root@java01 ~]# echo 2 > /proc/sys/net/ipv4/conf/all/arp_announce
4）浏览器访问：10.0.0.100:8080(外网地址)