#实验环境,关闭selinux和防火墙时间同步；
mycat-server 192.168.6.137#内存建议2G以上
mysql-master 192.168.6.132 MySQL 8.0 或者Mariadb 10.3.17
mysql-slave 192.168.6.130 MySQL 8.0 或者Mariadb 10.3.17
任意一台mysql客户机
#创建主从数据库
[root@mysql-slave ~]#yum -y install mysql-server
[root@mysql-master ~]#yum -y install mysql-server
#修改配置文件
#master上的my.cnf
[root@mysql-master ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id = 32
log-bin
#slave上的my.cnf
[mysqld]
server-id = 30
read-only=ON #只读
systemctl restart mysqld #重启mysql
#master上创建复制账号
[root@mysql-master ~]#mysql
mysql> show master status; #要先定位二进制文件在创建账号
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 156 | | | |
+---------------+----------+--------------+------------------+-------------------+
mysql> create user repluser@'192.168.6.%' identified '123456'; #创建账号
mysql>GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.6.%'; #加权限
#Slave上操作
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.6.132',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='binlog.000001',
-> MASTER_LOG_POS=156;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
#主从数据库搭建完成
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.6.132
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 12782
Relay_Log_File: centos8-relay-bin.000002
Relay_Log_Pos: 12946
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 12782
Relay_Log_Space: 13156
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 32
Master_UUID: 4788903b-a10c-11ec-94c5-000c29b5257d
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
#在MySQL代理服务器192.168.6.137安装mycat并启动和maridb客户端；
[root@mycat ~]#yum -y install java mysql
[root@mycat ~]#wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-
1.6.7.6-release-20210303094759-linux.tar.gz
[root@mycat ~]##mkdir /apps ;tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/
#配置环境变量
[root@mycat ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh ;source /etc/profile.d/mycat.sh
#在mycat 服务器上修改server.xml文件配置Mycat的连接信息
[root@mycat ~]#vim /apps/mycat/conf/server.xml
#或者删除注释,并修改下面行的8066改为3306
<property name="serverPort">3306</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> #--> 删除#后面此部分
.....
<user name="root"> #连接Mycat的用户名
<property name="password">123456</property> #连接Mycat的密码
<property name="schemas">TESTDB</property> #数据库名要和schema.xml相对应
#注意大小写空格都会导致mycat启动失败
#修改schema.xml实现读写分离策略
[root@mycat ~]# vim /apps/mycat/conf/schema.xml
#最终文件内容
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> </schema>
<dataNode name="dn1" dataHost="localhost1" database="hellodb" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host1" url="192.168.6.132:3306" user="root" password="123456">
<readHost host="host2" url="192.168.6.130:3306" user="root" password="123456"/>
</writeHost>
</dataHost>
</mycat:schema>
#在后端主服务器创建用户并对mycat授权
mysql> create user root@'192.168.6.%' identified '123456';
mysql>GRANT ALL ON *.* TO 'root'@'10.0.0.%';
mysql>create database mycat;
#重新启动mycat
[root@mycat ~]# mycat restart
[root@mycat ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 1 127.0.0.1:32000 *:*
LISTEN 0 100 [::]:3306 [::]:*
LISTEN 0 100 [::]:9066 [::]:*
LISTEN 0 50 [::]:33646 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
LISTEN 0 50 [::]:1984 [::]:*
LISTEN 0 50 [::]:33382 [::]:*
#在客户端上面连接mycat
[root@localhost ~]#mysql -uroot -p123456 -h 192.168.6.137 TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [TESTDB]>
MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
#在客户端查询
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 30 | #30为slaveID
+-------------+
1 row in set (0.00 sec)
#写入
MySQL [TESTDB]> update teachers set age=@@server_id where tid=5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [TESTDB]> select *from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | xinye | 32 | M | #32是主数据库ID号
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
MySQL [TESTDB]>
浙公网安备 33010602011771号