centos8
服务器共三台
client 10.0.0.88 mariadb-10.4.24
mycat-server 10.0.0.18 #内存建立2G以上
mysql-master 10.0.0.28 mariadb-10.4.24
mysql-master 10.0.0.38 mariadb-10.4.24
关闭防火墙和SELinux
systemctl stop firewalld
setenforce 0
时间同步
1、实现主从复制
修改master和slave上配置文件
master
[root@master ~]#cat /etc/my.cnf
[mysqld]
datadir=/data/mysql/data
pid-file=/data/mysql/mysqll.pid
server-id=28 添加ID编号
log-bin=/data/mysql/mysql-bin 开启二进制日志并指定路径
relay_log=/data/mysql/logs/mysql_relay.log
[client]
socket=/tmp/mysql.sock
[root@master ~]#cd /data/ #mkdir /data/mysql
[root@master /data]#ll
total 0
drwxrwx--- 5 mysql mysql 59 May 15 20:23 mysql #确认目录属性为 修改命令chown -R mysql.mysql /data/mysql
[root@master /data]#systemctl restart mysqld #重启服务
lave
[root@slave ~]#cat /etc/my.cnf
[mysqld]
datadir=/data/mysql/data
pid-file=/data/mysql/mysqll.pid
server-id=38
relay_log=/data/mysql/logs/mysql_relay.log
[client]
socket = /tmp/mysql.sock
[root@slave ~]#systemctl restart mysqld
(2)master上创建复制用户
[root@master ~]#mysql -uroot -p
Enter password:
MariaDB [(none)]> create user repluser@'10.0.0.%' identified by '123456'; 创建复制用户
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%'; 授权
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 351 |
| mysql-bin.000002 | 915 |
+------------------+-----------+
2 rows in set (0.000 sec)
(3)slave上执行
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.28',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=915;
Query OK, 0 rows affected (0.006 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.28
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 915
Relay_Log_File: mysql_relay.000002
Relay_Log_Pos: 555
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
…………………….
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
主从复制检查
主节点加载数据库
MariaDB [(none)]> source /root/hellodb_innodb.sql
MariaDB [hellodb]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
从节点同布
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
2、在MySQL代理服务器10.0.0.18安装mycat并启动
[root@mycat ~]#yum -y install java
#确认安装成功
[root@mycat ~]#java -version
openjdk version "1.8.0_312"
OpenJDK Runtime Environment (build 1.8.0_312-b07)
OpenJDK 64-Bit Server VM (build 25.312-b07, mixed mode)
(1)下载工具,创建目录 , 解压缩
[root@mycat ~]#wget http://dl.mycat.org.cn/1.6.7.6/20211221142218/Mycat-server-1.6.7.6-release-20211221142218-linux.tar.gz
--2022-05-15 21:59:05-- http://dl.mycat.org.cn/1.6.7.6/20211221142218/Mycat-server-1.6.7.6-release-20211221142218-linux.tar.gz
Resolving dl.mycat.org.cn (dl.mycat.org.cn)… 210.51.26.184
Connecting to dl.mycat.org.cn (dl.mycat.org.cn)|210.51.26.184|:80… connected.
HTTP request sent, awaiting response… 200 OK
Length: 27570028 (26M) [application/octet-stream]
Saving to: ‘Mycat-server-1.6.7.6-release-20211221142218-linux.tar.gz’
Mycat-server-1.6.7.6-release- 100%[================================================>] 26.29M 9.72MB/s in 2.7s
2022-05-15 21:59:08 (9.72 MB/s) - ‘Mycat-server-1.6.7.6-release-20211221142218-linux.tar.gz’ saved [27570028/27570028]
[root@mycat ~]#mkdir /apps/
[root@mycat ~]#tar xf Mycat-server-1.6.7.6-release-20211221142218-linux.tar.gz -C /apps/
(2)配置环境变量
[root@mycat ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@mycat ~]#source /etc/profile.d/mycat.sh
(3)启动mycat
[root@mycat ~]#mycat start #启动前内容调为大于2G,否则启动不了
Starting Mycat-server…
[root@mycat ~]#
[root@mycat ~]#tail -f /apps/mycat/logs/wrapper.log #查看日志,是否运行成功
STATUS | wrapper | 2022/05/15 22:02:30 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/05/15 22:02:30 | Launching a JVM…
INFO | jvm 1 | 2022/05/15 22:02:31 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/05/15 22:02:31 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/05/15 22:02:31 |
INFO | jvm 1 | 2022/05/15 22:02:32 | MyCAT Server startup successfully. see logs in logs/mycat.log
[root@mycat ~]#ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 1 127.0.0.1:32000 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 50 *:43575 *:*
LISTEN 0 50 *:1984 *:*
LISTEN 0 128 *:8066 默认监听8066端口 *:*
LISTEN 0 50 *:43369 *:*
LISTEN 0 128 *:9066 *:*
3、在mycat 服务器上修改server.xml文件配置Mycat的连接信息
vim /apps/mycat/conf/server.xml 找到以下内容取消注释
<!--
<property name="serverPort">8066</property>
<property name="managerPort">9066</property>
<property name="idleTimeout">300000</property>
<property name="authTimeout">15000</property>
<property name="bindIp">0.0.0.0</property>
<property name="dataNodeIdleCheckPeriod">300000</property> 5 \* 60 \* 1000L; //连接空闲检查
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
修改serverport端口为3306
修改mycat用户root登录面膜为atech
重启观察日志是否成功
[root@mycat ~]#mycat restart
Stopping Mycat-server…
Stopped Mycat-server.
Starting Mycat-server…
备份文件 schema.xml
[root@mycat ~]#cp /apps/mycat/conf/schema.xml{,.back}
[root@mycat ~]#vim /apps/mycat/conf/schema.xml
[root@mycat ~]#mycat restart #重启程序,注意观察日志是否成功
Stopping Mycat-server…
Stopped Mycat-server.
Starting Mycat-server…
[root@mycat ~]#tail -f /apps/mycat/logs/wrapper.log
STATUS | wrapper | 2022/05/15 22:02:30 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/05/15 22:02:30 | Launching a JVM…
INFO | jvm 1 | 2022/05/15 22:02:31 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/05/15 22:02:31 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/05/15 22:02:31 |
INFO | jvm 1 | 2022/05/15 22:02:32 | MyCAT Server startup successfully. see logs in logs/mycat.log
STATUS | wrapper | 2022/05/15 22:08:53 | TERM trapped. Shutting down.
STATUS | wrapper | 2022/05/15 22:08:54 | <-- Wrapper Stopped
STATUS | wrapper | 2022/05/15 22:08:55 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/05/15 22:08:55 | Launching a JVM…
INFO | jvm 1 | 2022/05/15 22:08:55 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/05/15 22:08:55 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/05/15 22:08:55 |
INFO | jvm 1 | 2022/05/15 22:08:56 | MyCAT Server startup successfully. see logs in logs/mycat.log
STATUS | wrapper | 2022/05/15 22:11:37 | TERM trapped. Shutting down.
STATUS | wrapper | 2022/05/15 22:11:38 | <-- Wrapper Stopped
STATUS | wrapper | 2022/05/15 22:11:39 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/05/15 22:11:39 | Launching a JVM…
INFO | jvm 1 | 2022/05/15 22:11:40 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/05/15 22:11:40 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/05/15 22:11:40 |
INFO | jvm 1 | 2022/05/15 22:11:41 | MyCAT Server startup successfully. see logs in logs/mycat.log
wrapper.log 日志观察
vim编辑 schema.xml 修改内容
4、在后端主服务器创建用户并对mycat授权
master
MariaDB [hellodb]> create database mycat;
Query OK, 1 row affected (0.000 sec)
MariaDB [hellodb]> create user root@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> grant all on *.* to root@'10.0.0.%'
-> ;
Query OK, 0 rows affected (0.000 sec)
从服务器同步检查
MariaDB [mysql]> select user,host from user;
+-------------+---------------------------+
| User | Host |
+-------------+---------------------------+
| root | 10.0.0.% |
| magedu | 192.168.1.0/255.255.255.0 |
| mariadb.sys | localhost |
| mysql | localhost |
| root | localhost |
+-------------+---------------------------+
5 rows in set (0.001 sec)
5、主从节点都开启通用日志
MariaDB [mysql]> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | OFF |
+---------------+-------+
1 row in set (0.001 sec)
MariaDB [mysql]> set global general_log=on;
Query OK, 0 rows affected (0.000 sec)
MariaDB [mysql]> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | ON |
+---------------+-------+
1 row in set (0.000 sec)
6客户端登录
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 28 |
+-------------+
1 row in set (0.001 sec)
MySQL [TESTDB]> select @@hostname;
+------------+
| @@hostname |
+------------+
| master.org |
+------------+
1 row in set (0.001 sec)
MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.001 sec)
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 28 |
+-------------+
1 row in set (0.002 sec)
MySQL [TESTDB]> select @@hostname;
+------------+
| @@hostname |
+------------+
| master.org |
+------------+
1 row in set (0.001 sec)
MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.002 sec)
MySQL [TESTDB]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.002 sec)
手机扫一扫
移动阅读更方便
你可能感兴趣的文章