40. mycat读写分离配置¶
1.前提¶
#1.mysql5.7多实例
192.168.178.161：3307，3309
#2.mycat
2. 创建用户¶
#原来是视频4.158和4.159之间少了一节，没有讲搭建主从的部分，自己补上重新做一个文件，搭建主从
#161
mysql -S /data/3307/mysql.sock
grant all on *.* to root@'192.168.178.%' identified by '123';
source /root/world.sql
mysql -S /data/3308/mysql.sock
grant all on *.* to root@'192.168.178.%' identified by '123';
source /root/world.sql
#上传SQL脚本
[root@161 ~]# ls
anaconda-ks.cfg autocreatecnf.sh world.sql
#创建用户，导入world库
[root@161 mycat]# mysql -S /data/3307/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
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> grant all on *.* to root@'192.168.178.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>
mysql> source /root/world.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| world |
+--------------------+
5 rows in set (0.00 sec)
#3309，不是3308
[root@161 ~]# mysql -S /data/3309/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 108
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
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> grant all on *.* to root@'192.168.178.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> source /root/world.sql
3. 创建读写分离配置文件¶
cd /application/mycat/conf
mv schema.xml schema.xml.bak
vim 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="world" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.178.161:3307" user="root" password="123">
<readHost host="db2" url="192.168.178.161:3309" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
#161
[root@161 ~]# cd /application/mycat/conf
[root@161 conf]# ls
autopartition-long.txt dbseq - utf8mb4.sql myid.properties sequence_conf.properties server.xml
auto-sharding-long.txt ehcache.xml partition-hash-int.txt sequence_db_conf.properties sharding-by-enum.txt
auto-sharding-rang-mod.txt index_to_charset.properties partition-range-mod.txt sequence_distributed_conf.properties wrapper.conf
cacheservice.properties log4j2.xml rule.xml sequence_http_conf.properties zkconf
dbseq.sql migrateTables.properties schema.xml sequence_time_conf.properties zkdownload
[root@161 conf]#
[root@161 conf]# mv schema.xml schema.xml.bak
[root@161 conf]# vim schema.xml
4. 重启mycat¶
mycat restart
#161
[root@161 conf]# mycat restart
Stopping Mycat-server...
Mycat-server was not running.
Starting Mycat-server...
[root@161 conf]#
5. 添加主机名与IP解析¶
#161
vim /etc/hosts
192.168.178.161 161
#162
vim /etc/hosts
192.168.178.162 162
#161
[root@161 conf]# vim /etc/hosts
[root@161 conf]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.178.161 161
[root@161 conf]#
#162
[root@162 ~]# vim /etc/hosts
[root@162 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.178.162 162
[root@162 ~]#
6. 登录mycat¶
mysql -uroot -p -h 127.0.0.1 -P 8066
#161,登录失败。。。
[root@161 conf]# mysql -uroot -p -h 127.0.0.1 -P 8066
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)
#查看端口，发现没有8066
[root@161 conf]# netstat -tunlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 7116/sshd
tcp6 0 0 :::3306 :::* LISTEN 35125/mysqld
tcp6 0 0 :::3307 :::* LISTEN 62576/mysqld
tcp6 0 0 :::3308 :::* LISTEN 62589/mysqld
tcp6 0 0 :::3309 :::* LISTEN 62596/mysqld
tcp6 0 0 :::3310 :::* LISTEN 62603/mysqld
tcp6 0 0 :::22 :::* LISTEN 7116/sshd
#查看日志
[root@161 conf]# cd ..
[root@161 mycat]# ls
bin catlet conf lib version.txt wrapper.log
[root@161 mycat]# vim wrapper.log
[root@161 mycat]#
[root@161 mycat]# cat wrapper.log
FATAL | wrapper | 2021/04/30 14:37:22 | ERROR: Could not write pid file /application/mycat/logs/mycat.pid: No such file or directory
FATAL | wrapper | 2021/04/30 14:46:16 | ERROR: Could not write pid file /application/mycat/logs/mycat.pid: No such file or directory
#分析，感觉是没有logs目录，手动创建测试
[root@161 mycat]# mkdir /application/mycat/logs
[root@161 mycat]# mycat start
Starting Mycat-server...
[root@161 mycat]# netstat -tunlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 7116/sshd
tcp 0 0 127.0.0.1:32000 0.0.0.0:* LISTEN 72847/wrapper-linux
tcp6 0 0 :::3306 :::* LISTEN 35125/mysqld
tcp6 0 0 :::3307 :::* LISTEN 62576/mysqld
tcp6 0 0 :::3308 :::* LISTEN 62589/mysqld
tcp6 0 0 :::3309 :::* LISTEN 62596/mysqld
tcp6 0 0 :::3310 :::* LISTEN 62603/mysqld
tcp6 0 0 :::22 :::* LISTEN 7116/sshd
#再次测试
[root@161 mycat]# mysql -uroot -p123 -h 127.0.0.1 -P 8066
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)
[root@161 mycat]# mysql -uroot -p123 -h 127.0.0.1 -P 32000
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)
#再次查看新日志
[root@161 mycat]# ls
bin catlet conf hs_err_pid72849.log hs_err_pid72881.log hs_err_pid72908.log hs_err_pid72934.log hs_err_pid72960.log lib logs version.txt wrapper.log
[root@161 mycat]#
[root@161 mycat]#
[root@161 mycat]# cd logs/
[root@161 logs]# ls
wrapper.log
[root@161 logs]# cat wrapper.log
STATUS | wrapper | 2021/04/30 14:58:13 | --> Wrapper Started as Daemon
STATUS | wrapper | 2021/04/30 14:58:13 | Launching a JVM...
ERROR | wrapper | 2021/04/30 14:58:13 | JVM exited while loading the application.
INFO | jvm 1 | 2021/04/30 14:58:13 | OpenJDK 64-Bit Server VM warning: INFO: os::commit_memory(0x0000000715550000, 715849728, 0) failed; error='Cannot allocate memory' (errno=12)
INFO | jvm 1 | 2021/04/30 14:58:13 | #
INFO | jvm 1 | 2021/04/30 14:58:13 | # There is insufficient memory for the Java Runtime Environment to continue.
INFO | jvm 1 | 2021/04/30 14:58:13 | # Native memory allocation (mmap) failed to map 715849728 bytes for committing reserved memory.
INFO | jvm 1 | 2021/04/30 14:58:13 | # An error report file with more information is saved as:
INFO | jvm 1 | 2021/04/30 14:58:13 | # /application/mycat/hs_err_pid72849.log
STATUS | wrapper | 2021/04/30 14:58:17 | Launching a JVM...
ERROR | wrapper | 2021/04/30 14:58:17 | JVM exited while loading the application.
INFO | jvm 2 | 2021/04/30 14:58:17 | OpenJDK 64-Bit Server VM warning: INFO: os::commit_memory(0x0000000715550000, 715849728, 0) failed; error='Cannot allocate memory' (errno=12)
INFO | jvm 2 | 2021/04/30 14:58:17 | #
INFO | jvm 2 | 2021/04/30 14:58:17 | # There is insufficient memory for the Java Runtime Environment to continue.
INFO | jvm 2 | 2021/04/30 14:58:17 | # Native memory allocation (mmap) failed to map 715849728 bytes for committing reserved memory.
INFO | jvm 2 | 2021/04/30 14:58:17 | # An error report file with more information is saved as:
INFO | jvm 2 | 2021/04/30 14:58:17 | # /application/mycat/hs_err_pid72881.log
STATUS | wrapper | 2021/04/30 14:58:22 | Launching a JVM...
ERROR | wrapper | 2021/04/30 14:58:22 | JVM exited while loading the application.
INFO | jvm 3 | 2021/04/30 14:58:22 | OpenJDK 64-Bit Server VM warning: INFO: os::commit_memory(0x0000000715550000, 715849728, 0) failed; error='Cannot allocate memory' (errno=12)
INFO | jvm 3 | 2021/04/30 14:58:22 | #
INFO | jvm 3 | 2021/04/30 14:58:22 | # There is insufficient memory for the Java Runtime Environment to continue.
INFO | jvm 3 | 2021/04/30 14:58:22 | # Native memory allocation (mmap) failed to map 715849728 bytes for committing reserved memory.
INFO | jvm 3 | 2021/04/30 14:58:22 | # An error report file with more information is saved as:
INFO | jvm 3 | 2021/04/30 14:58:22 | # /application/mycat/hs_err_pid72908.log
STATUS | wrapper | 2021/04/30 14:58:26 | Launching a JVM...
ERROR | wrapper | 2021/04/30 14:58:26 | JVM exited while loading the application.
INFO | jvm 4 | 2021/04/30 14:58:26 | OpenJDK 64-Bit Server VM warning: INFO: os::commit_memory(0x0000000715550000, 715849728, 0) failed; error='Cannot allocate memory' (errno=12)
INFO | jvm 4 | 2021/04/30 14:58:26 | #
INFO | jvm 4 | 2021/04/30 14:58:26 | # There is insufficient memory for the Java Runtime Environment to continue.
INFO | jvm 4 | 2021/04/30 14:58:26 | # Native memory allocation (mmap) failed to map 715849728 bytes for committing reserved memory.
INFO | jvm 4 | 2021/04/30 14:58:26 | # An error report file with more information is saved as:
INFO | jvm 4 | 2021/04/30 14:58:26 | # /application/mycat/hs_err_pid72934.log
STATUS | wrapper | 2021/04/30 14:58:30 | Launching a JVM...
ERROR | wrapper | 2021/04/30 14:58:30 | JVM exited while loading the application.
INFO | jvm 5 | 2021/04/30 14:58:30 | OpenJDK 64-Bit Server VM warning: INFO: os::commit_memory(0x0000000715550000, 715849728, 0) failed; error='Cannot allocate memory' (errno=12)
INFO | jvm 5 | 2021/04/30 14:58:30 | #
INFO | jvm 5 | 2021/04/30 14:58:30 | # There is insufficient memory for the Java Runtime Environment to continue.
INFO | jvm 5 | 2021/04/30 14:58:30 | # Native memory allocation (mmap) failed to map 715849728 bytes for committing reserved memory.
INFO | jvm 5 | 2021/04/30 14:58:30 | # An error report file with more information is saved as:
INFO | jvm 5 | 2021/04/30 14:58:30 | # /application/mycat/hs_err_pid72960.log
FATAL | wrapper | 2021/04/30 14:58:30 | There were 5 failed launches in a row, each lasting less than 300 seconds. Giving up.
FATAL | wrapper | 2021/04/30 14:58:30 | There may be a configuration problem: please check the logs.
STATUS | wrapper | 2021/04/30 14:58:30 | <-- Wrapper Stopped
#分析日志：There is insufficient memory for the Java Runtime Environment to continue.感觉是内存不够导致的。
#关闭虚拟机，内存从1G加大到3G测试
#1.启动多实例
[root@161 ~]# systemctl start mysqld3307
[root@161 ~]# systemctl start mysqld3308
[root@161 ~]# systemctl start mysqld3309
[root@161 ~]# systemctl start mysqld3310
[root@161 ~]#
[root@161 ~]#
[root@161 ~]#
[root@161 ~]#
[root@161 ~]# netstat -tunlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 7024/sshd
tcp6 0 0 :::3307 :::* LISTEN 7775/mysqld
tcp6 0 0 :::3308 :::* LISTEN 7782/mysqld
tcp6 0 0 :::3309 :::* LISTEN 7789/mysqld
tcp6 0 0 :::3310 :::* LISTEN 7808/mysqld
tcp6 0 0 :::22 :::* LISTEN 7024/sshd
[root@161 ~]#
#2.启动mycat
[root@161 ~]# mycat start
Starting Mycat-server...
[root@161 ~]# netstat -tunlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 7024/sshd
tcp 0 0 127.0.0.1:32000 0.0.0.0:* LISTEN 8198/java
tcp6 0 0 :::45191 :::* LISTEN 8198/java
tcp6 0 0 :::9066 :::* LISTEN 8198/java
tcp6 0 0 :::3307 :::* LISTEN 7775/mysqld
tcp6 0 0 :::3308 :::* LISTEN 7782/mysqld
tcp6 0 0 :::3309 :::* LISTEN 7789/mysqld
tcp6 0 0 :::3310 :::* LISTEN 7808/mysqld
tcp6 0 0 :::22 :::* LISTEN 7024/sshd
tcp6 0 0 :::1984 :::* LISTEN 8198/java
tcp6 0 0 :::8066 :::* LISTEN 8198/java
tcp6 0 0 :::38470 :::* LISTEN 8198/java
[root@161 ~]#
#3.再次连接测试，登录成功！！！
[root@161 ~]# mysql -uroot -p -h 127.0.0.1 -P 8066
Enter password:
ERROR 1045 (HY000): Access denied for user 'root', because password is error
[root@161 ~]#
[root@161 ~]# mysql -uroot -p123456 -h 127.0.0.1 -P 8066
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.6.29-mycat-1.6.7.5-release-20200422133810 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
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> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
mysql> use TESTDB;
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_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
mysql>
7. 读测试¶
show variables like 'server_id';
show variables like 'server_id';
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
1 row in set (0.09 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
1 row in set (0.02 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
1 row in set (0.00 sec)
8. 写测试¶
begin;
show variables like 'server_id';
show variables like 'server_id';
commit;
show variables like 'server_id';
show variables like 'server_id';
#出现问题，读写都在7号上。。。
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
1 row in set (0.02 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
1 row in set (0.02 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
1 row in set (0.02 sec)
#1.检查server_id
[root@161 ~]# mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
[root@161 ~]# mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 8 |
+---------------+-------+
[root@161 ~]# mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
[root@161 ~]# mysql -S /data/3310/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 10 |
+---------------+-------+
#2.检查配置文件
[root@161 ~]# cat /application/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="world" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.178.161:3307" user="root" password="123">
<readHost host="db2" url="192.168.178.161:3309" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
#分析不出原因。找到了。原来是3309没有创建root：123用户。之前貌似是在3308上创建的。修改第2步。
#找到原因了，视频没有搭建主从复制，自己搭建一下
[root@161 logs]# mysql -S /data/3309/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 93
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
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 |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
mysql>
#再次登录mycat，执行读写测试，成功！！！
[root@161 ~]# mysql -uroot -p123456 -h 127.0.0.1 -P 8066
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 4
Server version: 5.6.29-mycat-1.6.7.5-release-20200422133810 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
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 variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
最后更新:
2022-02-20 08:44:07