使用MyCat实现MySQL的分片处理和读写分离

1.下载MyCat，Mycat-server-1.4-release-20151019230038-linux.tar

2. 解压到/usr/mycat目录下：

    [root@localhost mycat]# tar -xvf  Mycat-server-1.4-release-20151019230038-linux.tar .

3.修改MyCat的配置文件 /usr/mycat/conf/schema.xml

   <? xml version="1.0" ?>

   <! DOCTYPE mycat:schema SYSTEM "schema.dtd" >
   < mycat:schema  xmlns:mycat ="http://org.opencloudb/" >
      < schema  name ="TESTDB"  checkSQLschema ="false"  sqlMaxLimit ="100" >
          < table  name ="user"  primaryKey ="id"  dataNode ="dn1,dn2"  rule ="sharding-by-intfile" />
      </ schema >
     < dataNode  name ="dn1"  dataHost ="master"  database ="db1"   />
     < dataNode  name ="dn2"  dataHost ="master"  database ="db2"   />
       < dataHost  name ="master"  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 ="192.168.8.132:3306"  user ="root"  password ="root" >
                      < readHost  host ="hostS1"  url ="192.168.8.130:3306"  user ="root"  password ="root"   /> 
           </ writeHost >
       </ dataHost >
  </ mycat:schema >

注意，修改balance=1,否则不支持读写分离。

        由于MySQL的root用户，默认只支持localhost或者127.0.0.1访问，通过IP地址无法访问，需要修改，修改方法如下：

        [root@localhost mycat]# mysql -u root -p

        mysql> use mysql;

        mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' identified by 'root' WITH GRANT OPTION;

        Query OK, 0 rows affected (0.03 sec)

        mysql> FLUSH PRIVILEGES;

        Query OK, 0 rows affected (0.07 sec)

      在主从服务器都要运行以上命令。

4.修改rule.xml

注意：role字段只能取值0和1

1    < tableRule  name ="sharding-by-intfile" >
2      < rule >
3        < columns >role </ columns >
4        < algorithm >hash-int </ algorithm >
5      </ rule >
6    </ tableRule >

5.修改配置文件 conf/partition-hash-int.txt

当role取值为0时，路由到数据节点1（ dn1 ）

当role取值为1时，路由到数据节点2（ dn2）

0=0
1=1

6.修改主从复制环境

在 http://www.blogjava.net/jacky9881/archive/2016/02/23/429403.html 搭建主从复制的环境做些修改。

在主服务器上新建数据库db1和db2

[root@localhost mycat]# mysql -u root -p

mysql> create database db1;

Query OK, 1 row affected (0.00 sec)

mysql> create database db2;

Query OK, 1 row affected (0.00 sec)

修改  my.cnf文件

root@localhost mycat]#vi /etc/my.cnf

binlog-do-db=db1
binlog-do-db=db2

重启MySQL服务器

[root@localhost mycat]# service mysqld restart

 

查看主服务状态

[root@localhost mycat]# mysql -u root -p

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000009 |      106 | db1,db2      |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

记住File和Position的值。

修改从服务器

在从服务器上新建数据库db1和db2

[root@localhost mycat]# mysql -u root -p

mysql> create database db1;

Query OK, 1 row affected (0.00 sec)

mysql> create database db2;

Query OK, 1 row affected (0.00 sec)

mysql> slave stop;

Query OK, 0 rows affected (0.00 sec)

mysql>  CHANGE MASTER TO MASTER_HOST='192.168.8.130', MASTER_PORT=3306,

            MASTER_USER='admin', MASTER_PASSWORD='admin',MASTER _LOG_FILE='mysql-bin.000009',MASTER _LOG_POS=106; 

mysql> slave start;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G ;

确认以下信息输出：

 Slave_IO_Running: Yes

 Slave_SQL_Running: Yes

至此，主从复制环境修改成功。

7.结果确认

在主服务器上，重启Mycat服务器，然后连接Mycat服务器。

[root@localhost mycat]# ./bin/mycat restart

Stopping Mycat-server...

Mycat-server was not running.

Starting Mycat-server...

[root@localhost mycat]#mysql -utest -ptest -h192.168.8.132 -P8066 -DTESTDB    

mysql>  create table `user` (`id` int not null auto_increment,`name` varchar (60),`password` varchar (20),`role` int not null,`email` varchar (30),`alertday` int,primary key (`id`));

Query OK, 0 rows affected (0.47 sec)

运行成功，去从服务器的db1和db2，可以看到表已经被创建了，说明主从复制正常。

mysql> use db1;

Database changed

mysql> show tables;

+---------------+

| Tables_in_db1 |

+---------------+

| user          |

+---------------+

1 row in set (0.10 sec)

mysql> use db2;

Database changed

mysql> show tables;

+---------------+

| Tables_in_db2 |

+---------------+

| user          |

+---------------+

1 row in set (0.00 sec)

在主服务器上运行以下命令：

mysql> insert into `user` (`name`,`password`,`role`,`email`,`alertday`) values('admin','admin',0,'xxxx@xxx.com',30);

Query OK, 1 row affected (0.24 sec)

mysql> insert into `user` (`name`,`password`,`role`,`email`,`alertday`) values('admin','admin',1,'xxxx@xxx.com',30);

Query OK, 1 row affected (0.00 sec)

确认分片规则有没有起作用。

在从服务器上运行如下命令，根据结果可以看出，role为0的记录被路由到db1中，role为1的记录被路由到db2中。

mysql> use db1;

Database changed

mysql> select * from user;

+----+-------+----------+------+--------------+----------+

| id | name  | password | role | email        | alertday |

+----+-------+----------+------+--------------+----------+

|  1 | admin | admin    |    0 | xxxx@xxx.com |       30 |

+----+-------+----------+------+--------------+----------+

1 row in set (0.00 sec)

mysql> use db2;

Database changed

mysql> select * from user;

+----+-------+----------+------+--------------+----------+

| id | name  | password | role | email        | alertday |

+----+-------+----------+------+--------------+----------+

|  1 | admin | admin    |    1 | xxxx@xxx.com |       30 |

+----+-------+----------+------+--------------+----------+

1 row in set (0.00 sec)

最后再确认一下读写分离有没有真的实现。

在主服务上运行

mysql> insert into `user` (`name`,`password`,`role`,`email`,`alertday`) values('admin','admin',1,'xxxx@xxx.com',30);

mysql> select * from user;

查看一下log文件

修改conf/log4j.xml,   INFO=>DEBUG

02/29 03:48:05.414  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=11, lastTime=1456746485403, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=true, threadId=1857, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.8.130, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]