MyCat实现读写分离、分库、全局表的代码

环境如下:

成都创新互联公司是专业的临渭区网站建设公司,临渭区接单;提供网站建设、网站制作,网页设计,网站设计,建网站,PHP网站建设等专业做网站服务;采用PHP框架,可快速的进行临渭区网站开发网页制作和功能扩展;专业做搜索引擎喜爱的网站,专业的做网站团队,希望更多企业前来合作!

系统IP主机名服务
Centos 7.5192.168.20.2MySQL01MySQL 5.7.24
Centos 7.5192.168.20.3mysql02MySQL  5.7.24
Centos 7.5192.168.20.3mysql03MySQL  5.7.24
Centos 7.5192.168.20.4mycatMycat

注:主机mysql01和mysql02为主从复制关系,可以参考博文MySQL高可用方案——双主(注:只需要参考博文做出主从效果即可,并不需要双主,也不需要keepalived来做高可用)来搭建。mycat服务请参考博文MyCat部署安装来搭建,这里新增的主机mysql03为一个刚刚搭建好的mysql服务器,可以参考博文Centos部署MySQL 5.7来搭建。

1、mycat实现读写分离

这里没有用到mysql03主机,读写分离的实现比较简单,只需要修改mycat的下面两个配置文件即可。如下:

1)修改server.xml文件
[root@mycat mycat]# pwd
/usr/local/mycat   
[root@mycat mycat]# vim conf/server.xml

[root@mycat mycat]# vim conf/server.xml    
          
                pwd@123   
                test_mycat    
                
        
2)修改schema.xml文件




        
        
            
                
        
                select user()
                
                                
                
                        
                                                
                        
                
        

修改后的完整配置文件如下(为避免冗余,删除了部分注释):

server.xml配置文件内容如下:
[root@mycat mycat]# vim conf/server.xml 




        
        0  
        0  

                2
                0
                0

                1
                1m
                1k

                0
                384m

                true

        
        
                pwd@123
                test_mycat
        
schema.xml配置文件内容如下:
[root@mycat mycat]# vim conf/schema.xml





        
        
        
        
                select user()
                
                
                        
                        
                
        

注:至此,后端数据库上自行创建有相关权限的用户,比如我在上面的datahost字段中指定的writehost和readhost字段的user值为root,则数据库的root账号要有对应的登录权限(默认root用户只允许在localhost登录,所以要自行修改root权限或换成其他有权限的用户)。

3)启动mycat并测试读写分离效果
[root@mycat mycat]# mycat start       #启动mycat
[root@mycat mycat]# mysql -umycat -ppwd@123 -h 192.168.20.4 -P 8066   #登录到mycat
#192.168.20.4为mycat主机IP,mycat监听端口为8066,使用的登录用户为server.xml文件中创建的用户
#创建表并插入一些测试数据
mysql> use test_mycat;
mysql> create table t1 (id int,name varchar(4));
mysql> insert into t1 values(1,'张三'),(2,'李四'),(3,'王五');
#此时,去后端数据库上查看,应该是有上面这些数据的。
#现在可以暂时关闭后端数据库的主从复制,然后再mycat主机上继续插入数据,测试是否可以读到
#关闭主从复制命令:stop slave;
#以下操作还是在mycat主机进行
mysql> insert into t1 values(4,'赵六');    #插入一条数据
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;      #查询不到刚插入的数据,说明读写操作没有在同一台主机上进行
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    3 | 王五   |
+------+--------+
#可以去后端两个数据库分别进行查看数据
#mysql01主机的数据如下(说明写操作是在mysql01):
mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    3 | 王五   |
|    4 | 赵六   |
+------+--------+
4 rows in set (0.00 sec)
#mysql02主机的数据如下:
mysql> select * from t1(说明读操作是在mysql02);
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    3 | 王五   |
+------+--------+
3 rows in set (0.00 sec)

读写分离效果至此结束。(为不影响后面的测试进行,最好恢复mysql01和mysql02主机的主从复制)。

2、mycat实现分库

一个数据库由很多表组成,每个表对应着不同的业务,所谓分库,就是按照业务将表进行分类,分不到不同的数据库上面,这样也就将数据或者说压力分担到不同的库上面,分库的原则:有紧密关联关系的表应该在一个库里,相互没有或者关联关系不大的表可以分到不同的库里。

分库举例:

假设现在有四张表: customer, orders, orders_detail, dict_order_type,每张表都有数百万条数据,那么这四张表如若要实现分库,则可以将customer表单独分离到一个数据库,另外三张表单独在另一个数据库。

1)修改server.xml文件(就改了一下逻辑库名)
[root@mycat mycat]# cat conf/server.xml 



    
    0  
    0  

        2
        0
        0

        1
        1m
        1k

        0
        384m

        true

    
    
        pwd@123
        test_db
    
2)修改schema.xml文件
[root@mycat mycat]# cat conf/schema.xml




    
           
    
    
    
    
        select user()
        
        
            
            
        
    
    
    
        select user()
        
        
        
    
3)重启mycat
[root@mycat mycat]# mycat restart

注:后端指定的真实数据库必须要有test数据库,因为在上面schema.xml文件中的dataNode字段指定的是连接后端的test数据库。

4)测试分库效果
#登录到mycat
[root@mycat mycat]# mysql -umycat -ppwd@123 -h 192.168.20.4 -P 8066
#创建相应的表
mysql> use test_db;
mysql> CREATE TABLE customer(
    -> id_a INT AUTO_INCREMENT,
    -> NAME VARCHAR(200),
    -> PRIMARY KEY(id_a)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE orders(
    -> id_b INT AUTO_INCREMENT,
    -> order_type INT,
    -> customer_id INT,
    -> amount DECIMAL(10,2),
    -> PRIMARY KEY(id_b)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE orders_detail(
    -> id_c INT AUTO_INCREMENT,
    -> detail VARCHAR(2000),
    -> order_id INT,
    -> PRIMARY KEY(id_c)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE dict_order_type(
    -> id_d INT AUTO_INCREMENT,
    -> order_type VARCHAR(200),
    -> PRIMARY KEY(id_d)
    -> );
Query OK, 0 rows affected (0.01 sec)

#登录到后端数据库上,查看表是否按照预计的效果拆分
[root@mycat mycat]# mysql -uroot -p123.com -h 192.168.20.2
mysql> use test;

mysql> show tables;             #可以看到主机mysql01这里有三个表
+-----------------+
| Tables_in_test  |
+-----------------+
| dict_order_type |
| orders          |
| orders_detail   |
+-----------------+
3 rows in set (0.00 sec)

#登录到mysql03上查看
[root@mycat mycat]# mysql -uroot -p123.com -h 192.168.20.21
mysql> use test;

mysql> show tables;     #可以看到这里只有customer表
+----------------+
| Tables_in_test |
+----------------+
| customer       |
+----------------+
1 row in set (0.00 sec)

至此,已经实现了分库,多个表放在了不同的库中(在上面的栗子中,实现的是多个表放在了不同的主机上),但对于通过mycat登录的客户来说,还是一个库,库中有四张表。

3、mycat实现分表

这里对一个新表emp进行拆分。

1)schema.xml文件修改如下(完整配置文件)
[root@mycat mycat]# cat conf/schema.xml




    
        
               
                       select user()                                                                select user()                            
2)rule.xml文件添加以下内容
[root@mycat mycat]# vim conf/rule.xml      #修改rule.xml文件
            
                
                        id    
                        mod_long  
                
        
                        ...................
                                                
        
                
                2    
        
3)重启mycat并登录到mycat
[root@mycat mycat]# mycat restart
4)登录到mycat插入数据进行测试分表效果
[root@mycat mycat]# mysql -umycat -ppwd@123 -h 192.168.20.4 -P 8066
mysql> use test_db;
#创建emp表(后端两个数据库节点上都会有这张表)
mysql> create table emp (
    -> id int,
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.41 sec)
#插入多条数据(对于这种分表,插入数据需要指定列名,否则会报错)
mysql> insert into emp(id,name) values(1,'张三'),(2,'李四'),(3,'王五'),(4,'赵六');
#查询新插入的数据
mysql> select * from emp;    
+------+--------+
| id   | name   |
+------+--------+
|    2 | 李四   |
|    4 | 赵六   |
|    1 | 张三   |
|    3 | 王五   |
+------+--------+
4 rows in set (0.04 sec)
mysql> select * from emp order by id;    #对结果进行排序
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    3 | 王五   |
|    4 | 赵六   |
+------+--------+
4 rows in set (0.01 sec)

#后端mysql01主机查询到该表的数据如下:
mysql> select * from emp;
+------+--------+
| id   | name   |
+------+--------+
|    2 | 李四   |
|    4 | 赵六   |
+------+--------+
2 rows in set (0.00 sec)
#mysql03主机查询到的数据如下:
mysql> select * from emp;          
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    3 | 王五   |
+------+--------+
2 rows in set (0.00 sec)

可以看到数据是平均分散在不同节点上保存的。

但是经过分片的普通表,是无法直接和其他表进行join的。

要想解决多表join的问题,还需要修改schema.xml配置文件,在分表的table字段下添加childTable字段,具体怎么实现,还在研究中。但是可以将分表的类型设置为全局表,这是一种比较简单的实现方式。

3、全局表

分表后,与其他表如何join就成了比较棘手的问题,我们可以选择将分表设置为全局表,这是一个解决join的方法(但不是唯一的方法),但全局表有一定的局限性,如下:

  • 全局表变动不频繁;
  • 数据量总体变化不大;
  • 数据规模不大,很少有超数十万的记录。

若想创建全局表,只需在table字段增加type="global"即可。如下:

1)定义全局表
[root@mycat conf]# cat schema.xml




    
        
               
       
   
                   select user()                                                                select user()                            
2)验证全局表
mysql> drop table emp;    #需要删除之前创建的emp表
Query OK, 0 rows affected (0.03 sec)
#重新创建emp表
mysql> create table emp(
    -> id int,
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.38 sec)
#插入数据测试
mysql>  insert into emp(id,name) values(1,'张三'),(2,'李四'),(3,'王五'),(4,'赵六');
#在mycat上查询插入的数据如下:
mysql> select * from emp;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    3 | 王五   |
|    4 | 赵六   |
+------+--------+
4 rows in set (0.02 sec)
#在后端主机上分别查看emp表中的数据:
#mysql01主机上数据如下:
mysql> select * from emp;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    3 | 王五   |
|    4 | 赵六   |
+------+--------+
4 rows in set (0.00 sec)
#mysql03主机上数据如下:
mysql> select * from emp;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    3 | 王五   |
|    4 | 赵六   |
+------+--------+
4 rows in set (0.00 sec)

网站名称:MyCat实现读写分离、分库、全局表的代码
链接地址:http://pwwzsj.com/article/jhpjpj.html