posts - 36, comments - 30, trackbacks - 0, articles - 3
不讲原理,直接上步骤:
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]



Feedback

# re: 使用MyCat实现MySQL的分片处理和读写分离  回复  更多评论   

2016-03-11 11:12 by mycat
珠三角地区
关注这方面技术的同学可以加QQ群479189837讨论

只有注册用户登录后才能发表评论。


网站导航: