myCat读写分离+传统主从

1 Mycat介绍:

  • mycat是最近很火的一款国人发明的分布式数据库中间件,它是基于阿里的cobar的基础上进行开发的
  • 准备环境:
  • db01主   10.0.0.51
  • db02备   10.0.0.52
  • mycat     10.0.0.53
1
2
3
4
5
6
7
8
9
10
11
12
[root@mycat ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
   Active: inactive (dead) since Sat 2018-01-20 08:41:30 CST; 5 days ago
 Main PID: 1253 (code=exited, status=0/SUCCESS)
 
Jan 20 01:43:52 db03 systemd[1]: Starting firewalld - dynamic firewall daemon...
Jan 20 01:43:56 db03 systemd[1]: Started firewalld - dynamic firewall daemon.
Jan 20 08:41:27 mycat systemd[1]: Stopping firewalld - dynamic firewall daemon...
Jan 20 08:41:30 mycat systemd[1]: Stopped firewalld - dynamic firewall daemon.
[root@mycat ~]# getenforce
Disabled

主从搭建:

  • mysql简易优化配置文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
[root@db01 local]# cat /etc/my.cnf
[client]
socket = /data/mysql.sock
[mysqld]
lower_case_table_names = 1
default-storage-engine = InnoDB
port = 3306
datadir = /data
character-set-server = utf8
socket = /data/mysql.sock
log_bin=mysql-bin                    #主从开启binlog
server_id = 1                        #serverID不能相同
innodb_buffer_pool_size = 200M
slave-parallel-workers = 8
thread_cache_size = 600
back_log = 600
slave_net_timeout = 60
max_binlog_size = 512M
key_buffer_size = 8M
query_cache_size = 64M
join_buffer_size = 2M
sort_buffer_size = 2M
query_cache_type = 1
thread_stack = 192K
innodb_thread_concurrency = 8
connect_timeout = 300
max_connections = 6000
max_allowed_packet = 2048M
max_connect_errors = 6000
wait_timeout = 200000
interactive_timeout = 200000
table_open_cache = 256
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_flush_log_at_trx_commit = 2
slow_query_log = 1
long_query_time = 1
lower_case_table_names = 1
skip-name-resolve

 1.1 mysql传统主从搭建  

  • db01
1
2
3
4
5
6
7
8
mysql> GRANT REPLICATION SLAVE ON *.* TO rep@'10.0.0.%' IDENTIFIED BY '123456';
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      403 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
  • db02
1
mysql> change master to master_host='10.0.0.51',master_user='rep',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=403;

2 mycat的主从切换和读写分离

2.1 什么是读写分离

  •   在数据库集群架构中,让主库负责处理事务性查询,而从库只负责处理select查询,让两者分工明确达到提高数据库整体读写性能。当然,主数据库另外一个功能就是负责将事务性查询导致的数据变更同步到从库中,也就是写操作。

2.2 读写分离的好处

  1. 分摊服务器压力,提高机器的系统处理效率
  2. 读写分离适用于读远比写的场景,如果有一台服务器,当select很多时,update和delete会被这些select访问中的数据堵塞,等待select结束,并发性能并不高,而主从只负责各自的写和读,极大程度的缓解X锁和S锁争用;
  3. 假如我们有1主3从,不考虑上述1中提到的从库单方面设置,假设现在1分钟内有10条写入,150条读取。那么,1主3从相当于共计40条写入,而读取总数没变,因此平均下来每台服务器承担了10条写入和50条读取(主库不承担读取操作)。因此,虽然写入没变,但是读取大大分摊了,提高了系统性能。另外,当读取被分摊后,又间接提高了写入的性能。所以,总体性能提高了,说白了就是拿机器和带宽换性能;
  4. 增加冗余,提高服务可用性,当一台数据库服务器宕机后可以调整另外一台从库以最快速度恢复服务

2.3  搭建mycat

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
#mycat需要安装jdk(方法略)
#mycat下载地址
#https://github.com/MyCATApache/Mycat-download/blob/master/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@mycat src]# pwd
/usr/local/src
[root@mycat src]# tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/
[root@mycat src]# cd ../mycat/
[root@mycat mycat]# cd conf/
[root@mycat conf]# cat schema.xml
<?xml version="1.0"?> 
<!DOCTYPE mycat:schema SYSTEM "schema.dtd"
<mycat:schema xmlns:mycat="http://io.mycat/"
   
    <!-- 逻辑库配置 DB1,DB2是逻辑库并不是真的数据库--
    <schema name="DB1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"/
    <schema name="DB2"  checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2"/
        <!-- 表分片配置在这些 --
         <!--</schema>  -->
       
    <!-- 节点配置  db1,db2才是真正的数据库 --
    <dataNode name="dn1" dataHost="host01" database="db1" /
    <dataNode name="dn2" dataHost="host01" database="db2" /
       
    <!-- 读写分离的配置 --
    <dataHost name="host01" maxCon="1000" minCon="10" balance="1" 
          writeType="0" dbType="mysql" dbDriver="native" switchType="2"  slaveThreshold="100"
        <heartbeat>show slave status</heartbeat> 
           
        <writeHost host="hostM1" url="10.0.0.51:3306" user="root" password="123456"
            <!-- 可以在这配置它对应的多个读库 --
             <readHost host="hostS1" url="10.0.0.52:3306" user="root" password="123456" />
        </writeHost> 
           
        <!--主故障,顶替写节点,主正常是分担读压力--
        <writeHost host="hostS2" url="10.0.0.52:3306" user="root" password="123456" > </writeHost>
    </dataHost> 
       
</mycat:schema> 

2.3.1 修改server.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<user name="root">
        <property name="password">123456</property>
        <property name="schemas">DB1,DB2</property>
 
        <!-- 表级 DML 权限设置 -->
        <!--           
        <privileges check="false">
                <schema name="TESTDB" dml="0110" >
                        <table name="tb01" dml="0000"></table>
                        <table name="tb02" dml="1111"></table>
                </schema>
        </privileges>          
         -->
</user>
 
<user name="user">
        <property name="password">user</property>
        <property name="schemas">DB1,DB2</property>
        <property name="readOnly">true</property>
</user>

2.3.2 启动mycat

1
2
3
4
5
6
7
8
9
10
11
12
13
[root@mycat conf]# echo "export PATH=/usr/local/mycat/bin:$PATH" >> /etc/profile
[root@mycat conf]# mycat console #前台启动是否报错
[root@mycat conf]# mycat start     #启动mycat
[root@mycat conf]# mycat stop     #启动mycat
[root@mycat conf]# ss -lntup|grep java
tcp    LISTEN     0      1      127.0.0.1:32000           *:*                    users:(("java",pid=17930,fd=4))
tcp    LISTEN     0      100      :::9066                 :::*                   users:(("java",pid=17930,fd=72))
tcp    LISTEN     0      50       :::43410                :::*                   users:(("java",pid=17930,fd=54))
tcp    LISTEN     0      50       :::48064                :::*                   users:(("java",pid=17930,fd=56))
tcp    LISTEN     0      50       :::1984                 :::*                   users:(("java",pid=17930,fd=55))
tcp    LISTEN     0      100      :::8066                 :::*                   users:(("java",pid=17930,fd=76))
#9066管理端口
#8066虚拟schema的登陆端口

2.3.3 登录管理端口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[root@db01 ~]# mysql -uroot -p123456 -h 10.0.0.53 -P 9066
mysql> show @@datasource;
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST      | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | hostM1 | mysql | 10.0.0.51 | 3306 | W    |      0 |   10 | 1000 |     131 |         0 |          0 |
| dn1      | hostS2 | mysql | 10.0.0.52 | 3306 | W    |      0 |    1 | 1000 |     119 |         0 |          0 |
| dn1      | hostS1 | mysql | 10.0.0.52 | 3306 | R    |      0 |    8 | 1000 |     126 |         0 |          0 |
| dn2      | hostM1 | mysql | 10.0.0.51 | 3306 | W    |      0 |   10 | 1000 |     131 |         0 |          0 |
| dn2      | hostS2 | mysql | 10.0.0.52 | 3306 | W    |      0 |    1 | 1000 |     119 |         0 |          0 |
| dn2      | hostS1 | mysql | 10.0.0.52 | 3306 | R    |      0 |    8 | 1000 |     126 |         0 |          0 |
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
6 rows in set (0.01 sec)
#使用mysql客户端连接9066管理端口,执行 show @@datasource 可以观察到 READ_LOAD,WRITE_LOAD 两个统计参数的变化:
#这里显示 hostM1 为写节点,hostS1 为读节点:
#hostM1 的 WRITE_LOAD 写入次数
#hostS1 的 READ_LOAD  读出次数

2.3.4 登录虚拟库端口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
[root@db01 ~]# mysql -uroot -p123456 -h 10.0.0.53 -P 8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| DB1      |    #DB1是虚拟库名称
| DB2      |    #DB1是虚拟库名称
+----------+
2 rows in set (0.00 sec)
mysql> use DB1
mysql> create table tb1 (id INT, name VARCHAR(20));
mysql> insert into tb1 values (1, 'guli'), (2, 'xie');
mysql> insert into tb1 values (3, 'ccc'), (4, 'ddd');
mysql> insert into tb1 values (5, 'eee'), (6, 'fff');
mysql> insert into tb1 values (7, 'ggg'), (8, 'hhh');
mysql> select * from tb1;#(多查看几次增加READ_LOAD次数)
[root@db01 ~]# mysql -uroot -p123456 -h 10.0.0.53 -P 9066
mysql> show @@datasource;
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST      | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | hostM1 | mysql | 10.0.0.51 | 3306 | W    |      0 |   10 | 1000 |     222 |         0 |          6 |
| dn1      | hostS2 | mysql | 10.0.0.52 | 3306 | W    |      0 |    1 | 1000 |     217 |        14 |          0 |
| dn1      | hostS1 | mysql | 10.0.0.52 | 3306 | R    |      0 |   10 | 1000 |     232 |        20 |          0 |
| dn2      | hostM1 | mysql | 10.0.0.51 | 3306 | W    |      0 |   10 | 1000 |     222 |         0 |          6 |
| dn2      | hostS2 | mysql | 10.0.0.52 | 3306 | W    |      0 |    1 | 1000 |     217 |        14 |          0 |
| dn2      | hostS1 | mysql | 10.0.0.52 | 3306 | R    |      0 |   10 | 1000 |     232 |        20 |          0 |
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
在主节点不挂的情况下用户不会写入到hostS2

2.4 测试高可用

2.4.1 关闭主库测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
[root@db01 ~]# /etc/init.d/mysqld stop
[root@db01 ~]# mysql -uroot -p123456 -h 10.0.0.53 8066
mysql> use DB2
mysql> create table tb2 (id INT, name VARCHAR(20));
mysql> insert into tb2 values (1, 'aaa'), (2, 'bbb');
mysql> insert into tb2 values (3, 'ccc'), (4, 'ddd');
mysql> insert into tb2 values (5, 'eee'), (6, 'fff');
mysql> select * from tb2;  #(也是多查看几次)
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
|    2 | bbb  |
|    3 | ccc  |
|    4 | ddd  |
|    5 | eee  |
|    6 | fff  |
+------+------+
6 rows in set (0.00 sec)
mysql> show @@datasource;
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST      | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | hostM1 | mysql | 10.0.0.51 | 3306 | W    |      0 |    0 | 1000 |       0 |         0 |          6 |
| dn1      | hostS2 | mysql | 10.0.0.52 | 3306 | W    |      0 |    6 | 1000 |     364 |        44 |          4 |
| dn1      | hostS1 | mysql | 10.0.0.52 | 3306 | R    |      0 |   10 | 1000 |     330 |        20 |          0 |
| dn2      | hostM1 | mysql | 10.0.0.51 | 3306 | W    |      0 |    0 | 1000 |       0 |         0 |          6 |
| dn2      | hostS2 | mysql | 10.0.0.52 | 3306 | W    |      0 |    6 | 1000 |     364 |        44 |          4 |
| dn2      | hostS1 | mysql | 10.0.0.52 | 3306 | R    |      0 |   10 | 1000 |     330 |        20 |          0 |
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
6 rows in set (0.00 sec)
#可以清楚的看见hostM1已经不写入了。hostM2既读数据又写数据

2.4.2 再去DB1逻辑库测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> use DB1
mysql> insert into tb1 values (9, 'abc'), (10, 'cba');
mysql> insert into tb1 values (11, 'abc'), (12, 'cba');
mysql> insert into tb1 values (13, 'abc'), (14, 'cba');
mysql> show @@datasource;
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST      | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | hostM1 | mysql | 10.0.0.51 | 3306 | W    |      0 |    0 | 1000 |       0 |         0 |          6 |
| dn1      | hostS2 | mysql | 10.0.0.52 | 3306 | W    |      0 |    6 | 1000 |     410 |        63 |          8 |
| dn1      | hostS1 | mysql | 10.0.0.52 | 3306 | R    |      0 |   10 | 1000 |     353 |        20 |          0 |
| dn2      | hostM1 | mysql | 10.0.0.51 | 3306 | W    |      0 |    0 | 1000 |       0 |         0 |          6 |
| dn2      | hostS2 | mysql | 10.0.0.52 | 3306 | W    |      0 |    6 | 1000 |     410 |        63 |          8 |
| dn2      | hostS1 | mysql | 10.0.0.52 | 3306 | R    |      0 |   10 | 1000 |     353 |        20 |          0 |
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
#再次查看也不会往数据库hostM1上写入,这时候db1和db2的数据是不一致的。

2.4.3  启动数据库db1,进入查看数据  

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[root@db01 ~]# /etc/init.d/mysqld start
Starting MySQL.... SUCCESS!
[root@db01 ~]# mysql
mysql> show databases;
mysql> use db1
mysql> select * from tb1;
+------+------+
| id   | name |
+------+------+
|    1 | guli |
|    2 | xie  |
|    3 | ccc  |
|    4 | ddd  |
|    5 | eee  |
|    6 | fff  |
|    7 | ggg  |
|    8 | hhh  |
+------+------+
8 rows in set (0.01 sec)
mysql> use db2
mysql> show tables;
Empty set (0.00 sec)

2.4.4  数据库db2,进入查看数据  

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[root@db02 ~]# mysql
mysql> show databases;
mysql> use db1
mysql> show tables;
mysql> select * from tb1;
+------+------+
| id   | name |
+------+------+
|    1 | guli |
|    2 | xie  |
|    3 | ccc  |
|    4 | ddd  |
|    5 | eee  |
|    6 | fff  |
|    7 | ggg  |
|    8 | hhh  |
|    9 | abc  |
|   10 | cba  |
|   11 | abc  |
|   12 | cba  |
|   13 | abc  |
|   14 | cba  |
+------+------+
mysql> use db2
mysql> show tables;
mysql> select * from tb2;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
|    2 | bbb  |
|    3 | ccc  |
|    4 | ddd  |
|    5 | eee  |
|    6 | fff  |
+------+------+

总结:

  • 到此基本演示了 mycat 的主从读写分离功能,配置的前提是已经有一个配置好的 mysql 主从复制架构,mycat 工作于 mysql 主从架构的前端,负责 SQL 语句的分发。
  • 即使在启动db1,写入数据也只会往db2上写入,mycat更牛的地方可能实在分库分表而不是高可用,个人认为更适合Percona-XtraDB-Cluster(因为强一致性)

MySQL 优化操作

  

  

 

posted @   阿进,fighting  阅读(2174)  评论(0编辑  收藏  举报
编辑推荐:
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 现代计算机视觉入门之:什么是图片特征编码
· .NET 9 new features-C#13新的锁类型和语义
阅读排行:
· Sdcb Chats 技术博客:数据库 ID 选型的曲折之路 - 从 Guid 到自增 ID,再到
· 语音处理 开源项目 EchoSharp
· 《HelloGitHub》第 106 期
· Spring AI + Ollama 实现 deepseek-r1 的API服务和调用
· 使用 Dify + LLM 构建精确任务处理应用
点击右上角即可分享
微信分享提示