首页 > 代码库 > Mycat读写分离、主从切换学习(转)

Mycat读写分离、主从切换学习(转)

http://blog.csdn.net/zhanglei_16/article/details/50707487

 

Mycat读写分离、主从切换学习
问题一:分表、分库的优缺点,以及分表无法成为主流
分表方式:是在将一个大表,在一个db库内,分成多个分表,
优点是:分开之后的表,仍然在一个库内,便于查看、管理。
缺点:db只能在一个服务器内,不能解决I/O问题,负载集中

分库方式:将一个大表,分布在多个DB中,每个DB只保留一部分数据,所有数据组合起来才是全库的数据。
优点:优点是分担IO、负载均衡,可以让整个集群性能随机器数扩大,性能水平扩展
缺点:不易维护、数据统计以及jion操作会跨库处理,对性能有影响。

分表无法成为主流原因:目前mysql等数据库随着业务增长,绝大对数的解决方案都是通过增加服务器来水平扩展
提高集群的性能,而分表方式,只能局限在一个服务器上,会有性能瓶颈。

1:环境
192.168.1.21   mycat1
192.168.1.22   mysql1
192.168.1.23   mysql2

2:在mysql1、mysql2上安装mysql
安装Mysql、初始化mysql省略
use mysql
GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "oracle";
update user set Password = password(‘oracle‘) where User=‘root‘;
GRANT replication slave ON *.* TO ‘repluser‘@‘%‘ identified by ‘oracle‘;
flush privileges;
exit;

3:配置mysql复制
在mysql1:
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000003 |      401 |              | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
    
在mysql2:向主库做同步操作,开启复制
change master to master_host=‘192.168.10.22‘,
master_port=3306, master_user=‘repluser‘, 
master_password=‘oracle‘, master_log_file=‘mysql-bin.000003‘,master_log_pos=401;
start slave;

show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.22
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 2095
               Relay_Log_File: mysql2-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

在mysql1创建测试库、用户:
create database testdb1;
create database testdb2;
create database testdb3;
GRANT ALL PRIVILEGES ON testdb1.* TO ‘zhang‘@"%" IDENTIFIED BY "oracle";
GRANT ALL PRIVILEGES ON testdb2.* TO ‘zhang‘@"%" IDENTIFIED BY "oracle";
GRANT ALL PRIVILEGES ON testdb3.* TO ‘zhang‘@"%" IDENTIFIED BY "oracle";
flush privileges;
exit


数据同步测试:
explain create table company(id int not null primary key,name varchar(100));


4:安装、配置mycat
测试思路:
Mysql高可用和读写分离的几个配置点:
writeType="0"   --写操作只写入一个节点


balance=0,1,2,3  --是否启用读写分离。
0:为不启用读写分离;1:为启用读写分离,只有非写入主库的其他库分担读请求,常用配置;
2:为所有读操作都随机在主从节点分发;3:为只在readhost上分发读操作负载,不在writehost上分担负载;


switchType="-1,1,2,3"  --是否启用主从切换
-1:表示不启用主从切换;1:为默认值,自动切换;2:基于主从同步的状态,决定是否切换,与show slave status心跳对应;
3:基于多住galary集群切换,与show status like ‘wsrep%‘对应;


<heartbeat>show slave status</heartbeat>  --心跳检测语句,一般为select user();
如果是switchType=2,基于主从同步状态决定是否切换,则心跳设为show slave status;
如果是switchType=3,则心跳设为show status like ‘wsrep%‘;


<writeHost host="mysql1" url="192.168.10.22:3306" user="root" password="oracle">
<readHost host="mysql2" url="192.168.10.23:3306" user="root" password="oracle" weight="1" />
</writeHost>
<writeHost host="mysql2" url="192.168.10.23:3306" user="root" password="oracle">
</writeHost>
--具体写服务器的设置,每个host都对应一个DB,需要设置连接mysql DB的URL、用户名和密码,主机的名称都不能相同;
对于主机设置的数量需要事先规划好。
情况一:对于不使用mycat做高可用和读写分离的情况,只需要设置一个写主机writeHost即可;
情况二:对于使用mycat做读写分离的情况,可以设置一个writeHost作为写节点,设置一个对应的读节点readHost,
采用balance=3的方式进行读写分离;也可以设置多个writeHost,只在第一个writeHost上写入,其他都根据负载均衡读请求;
情况一:对于使用mycat做高可用的情况,必须设置两个、或两个以上的writeHost节点,这样才可以切换;
高可用可以根据不同情况设置不同的心跳,主从切换方式。


从物理上讲,mycat中定义的writeHost和readHost,在物理上,都是只有写请求的那个writeHost是对应Master数据库的,
其他的writeHost和readHost都是对应slave节点的。
一般实际应用中,也是将读写分离和高可用结合起来使用,将物理上的slave设置为writeHost,即可实现读写分离,也可以高可用。
mycat中switchType=1,2,3 这三种情况,分别适用于不同的场景,设置为1,自动切换,是指mycat在屋里master宕机时,才会主从切换。

配置mycat:
vi schema.xml

       <schema name="db1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
       </schema>       
       <schema name="db2" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2">
       </schema>
       <schema name="db3" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn3">
       </schema>
        <dataNode name="dn1" dataHost="mysql1" database="db1" />
        <dataNode name="dn2" dataHost="mysql1" database="db2" />
        <dataNode name="dn3" dataHost="mysql1" database="db3" />


        <!--######### TESTDB脢戮脻麓 ########-->
        <dataHost name="mysql1" maxCon="1000" minCon="10" balance="1"
                writeType="0" dbType="mysql" dbDriver="native" switchType="2"  slaveThreshold="100">
                <heartbeat>show slave status</heartbeat>
                <writeHost host="mysql1" url="192.168.10.22:3306" user="root" password="oracle">
                </writeHost>


                <writeHost host="mysql2" url="192.168.10.23:3306" user="root" password="oracle">
                </writeHost>
        </dataHost>
</mycat:schema>


vi server.xml
        <user name="root">
                <property name="password">oracle</property>
                <property name="schemas">db1,db2,db3</property>
        </user>


[root@mycat1 logs]# tail -800 wrapper.log 
STATUS | wrapper  | 2016/02/21 04:36:45 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2016/02/21 04:36:45 | Launching a JVM...
INFO   | jvm 1    | 2016/02/21 04:36:46 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2016/02/21 04:36:46 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2016/02/21 04:36:46 | 
INFO   | jvm 1    | 2016/02/21 04:36:46 | log4j 2016-02-21 04:36:46 [./conf/log4j.xml] load completed.
INFO   | jvm 1    | 2016/02/21 04:36:47 | MyCAT Server startup successfully. see logs in logs/mycat.log


[root@mycat1 logs]# netstat -an|grep 9066
tcp        0      0 :::9066                     :::*                        LISTEN


[root@mycat1 logs]# tail -800 mycat.log
......
2/21 04:42:17.174  DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel 
MySQLConnection [id=9, lastTime=1456000937137, user=root, schema=db1, old shema=db1, borrowed=true, 
fromSlaveDB=false, threadId=30, charset=utf8, txIsolation=0, autocommit=true, attachment=null, 
respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/21 04:42:17.175  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel 
MySQLConnection [id=11, lastTime=1456000937173, user=root, schema=db1, old shema=db1, borrowed=true, 
fromSlaveDB=false, threadId=4, charset=latin1, txIsolation=3, autocommit=true, attachment=null, 
respHandler=null, host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]


测试验证读写分离模式:
[root@mycat1 conf]# mysql -uroot -poracle -h192.168.10.21 -P8066
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.8-mycat-1.5-alpha-20151221110028 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2013, 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 databases;
+----------+
| DATABASE |
+----------+
| db1      |
| db2      |
| db3      |
+----------+
3 rows in set (0.02 sec)


use db1;
create table company(id int not null primary key,name varchar(100));
insert into company(id,name) values(1,‘alibaba‘);
查看mycat.log:
02/21 06:28:07.085  DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=5, schema=db1, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=db1]insert into company(id,name) values(1,‘alibaba‘)
02/21 06:28:07.085  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=5, schema=db1, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=db1]insert into company(id,name) values(1,‘alibaba‘), route={
   1 -> dn1{insert into company(id,name) values(1,‘alibaba‘)}
} rrs 
02/21 06:28:07.086  DEBUG [$_NIOREACTOR-0-RW] (MySQLConnection.java:445) -con need syn ,total syn cmd 2 commands SET names latin1;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection [id=1, lastTime=1456007287085, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=24, charset=latin1, txIsolation=0, autocommit=true, attachment=dn1{insert into company(id,name) values(1,‘alibaba‘)}, respHandler=SingleNodeHandler [node=dn1{insert into company(id,name) values(1,‘alibaba‘)}, packetId=0], host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
02/21 06:28:07.124  DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=1, lastTime=1456007287072, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=24, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{insert into company(id,name) values(1,‘alibaba‘)}, respHandler=SingleNodeHandler [node=dn1{insert into company(id,name) values(1,‘alibaba‘)}, packetId=0], host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]


连接mycat进行select操作:
select * from company;
查看mycat.log:
02/21 06:31:17.261  DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection[id=5, schema=db1, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=db1]select * from company
02/21 06:31:17.265  DEBUG [$_NIOREACTOR-0-RW] (EnchachePool.java:76) -SQLRouteCache  miss cache ,key:db1select * from company
02/21 06:31:17.265  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=5, schema=db1, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=db1]select * from company, route={
   1 -> dn1{select * from company}
} rrs 
02/21 06:31:17.265  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -select read source mysql2 for dataHost:mysql1
02/21 06:31:17.267  DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=11, lastTime=1456007477250, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=4, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{select * from company}, respHandler=SingleNodeHandler [node=dn1{select * from company}, packetId=5], host=192.168.10.23, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
经过多次观察,select * from company都去读从库了;


查看管理信息:
[root@mycat1 conf]# mysql -uroot -poracle -h192.168.10.21 -P9066
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.8-mycat-1.5-alpha-20151221110028 MyCat Server (monitor)
Copyright (c) 2000, 2013, 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 @@heartbeat;
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME   | TYPE  | HOST          | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| mysql1 | mysql | 192.168.10.22 | 3306 |       1 |     0 | idle   |       0 | 5,12,10      | 2016-02-21 18:12:07 | false |
| mysql2 | mysql | 192.168.10.23 | 3306 |       1 |     0 | idle   |       0 | 1,41,15      | 2016-02-21 18:12:07 | false |
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
2 rows in set (0.04 sec)


mysql> show @@backend;
+------------+------+---------+---------------+------+--------+----------+---------+-------+--------+----------+------------+--------+----------+---------+------------+
| processor  | id   | mysqlId | host          | port | l_port | net_in   | net_out | life  | closed | borrowed | SEND_QUEUE | schema | charset  | txlevel | autocommit |
+------------+------+---------+---------------+------+--------+----------+---------+-------+--------+----------+------------+--------+----------+---------+------------+
| Processor0 |   13 |      32 | 192.168.10.22 | 3306 |  36280 |  1391408 |   13288 | 47798 | false  | false    |          0 | db1    | utf8:33  | 0       | true       |
| Processor0 |    8 |      26 | 192.168.10.22 | 3306 |  36270 |  1451598 |   13860 | 48998 | false  | false    |          0 | db1    | utf8:33  | 0       | true       |
| Processor1 |    5 |      28 | 192.168.10.22 | 3306 |  36272 |   226963 |    2222 | 48998 | false  | false    |          0 | db2    | utf8:33  | 0       | true       |
| Processor1 |    9 |      30 | 192.168.10.22 | 3306 |  36274 |  1449316 |   13983 | 48998 | false  | false    |          0 | db1    | latin1:8 | 3       | true       |
| Processor1 |    1 |      24 | 192.168.10.22 | 3306 |  36268 |  1449316 |   13964 | 48998 | false  | false    |          0 | db1    | latin1:8 | 3       | true       |
| Processor1 |   14 |      33 | 192.168.10.22 | 3306 |  36281 |  1368258 |   13068 | 47198 | false  | false    |          0 | db1    | utf8:33  | 0       | true       |
| Processor2 |   10 |      23 | 192.168.10.22 | 3306 |  36267 |  1451598 |   13860 | 48998 | false  | false    |          0 | db1    | utf8:33  | 0       | true       |
| Processor2 |   15 |      34 | 192.168.10.22 | 3306 |  36282 |  1347423 |   12870 | 46598 | false  | false    |          0 | db1    | utf8:33  | 0       | true       |
| Processor3 |   12 |      31 | 192.168.10.22 | 3306 |  36279 |  1433078 |   13684 | 48698 | false  | false    |          0 | db1    | utf8:33  | 0       | true       |
| Processor3 |    7 |      27 | 192.168.10.22 | 3306 |  36271 |   226963 |    2222 | 48998 | false  | false    |          0 | db2    | utf8:33  | 0       | true       |
| Processor3 |   11 |       4 | 192.168.10.23 | 3306 |  60806 | 13061320 |  107991 | 48998 | false  | false    |          0 | db1    | latin1:8 | 3       | true       |
+------------+------+---------+---------------+------+--------+----------+---------+-------+--------+----------+------------+--------+----------+---------+------------+
11 rows in set (0.01 sec)


现在停止主库,看看还能否正常提供服务:
[root@mysql1 mysql]# /etc/init.d/mysql stop
Shutting down MySQL............[  OK  ]


查看mycat.log:
02/21 18:22:58.508   INFO [$_NIOConnector] (SQLJob.java:111) -can‘t get connection for sql :show slave status
02/21 18:22:58.508   INFO [$_NIOConnector] (PhysicalDatasource.java:373) -not ilde connection in pool,create new connection for mysql1 of schema db1
02/21 18:22:58.510   INFO [$_NIOConnector] (AbstractConnection.java:458) -close connection,reason:java.net.ConnectException: Connection refused ,MySQLConnection [id=0, lastTime=1456050178505, user=root, schema=db1, old shema=db1, borrowed=false, fromSlaveDB=false, threadId=0, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/21 18:22:58.510   INFO [$_NIOConnector] (SQLJob.java:111) -can‘t get connection for sql :show slave status
02/21 18:22:58.510   INFO [$_NIOConnector] (PhysicalDatasource.java:373) -not ilde connection in pool,create new connection for mysql1 of schema db1
02/21 18:22:58.511   INFO [$_NIOConnector] (AbstractConnection.java:458) -close connection,reason:java.net.ConnectException: Connection refused ,MySQLConnection [id=0, lastTime=1456050178505, user=root, schema=db1, old shema=db1, borrowed=false, fromSlaveDB=false, threadId=0, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/21 18:22:58.511   INFO [$_NIOConnector] (SQLJob.java:111) -can‘t get connection for sql :show slave status
02/21 18:22:58.511   INFO [$_NIOConnector] (PhysicalDatasource.java:373) -not ilde connection in pool,create new connection for mysql1 of schema db1
02/21 18:22:58.513   INFO [$_NIOConnector] (AbstractConnection.java:458) -close connection,reason:java.net.ConnectException: Connection refused ,MySQLConnection [id=0, lastTime=1456050178505, user=root, schema=db1, old shema=db1, borrowed=false, fromSlaveDB=false, threadId=0, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]


再次连接mycat,看看是否还能正常进行操作:
[root@mycat1 conf]# mysql -uroot -poracle -h192.168.10.21 -P8066
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.8-mycat-1.5-alpha-20151221110028 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2013, 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 databases;
+----------+
| DATABASE |
+----------+
| db1      |
| db2      |
| db3      |
+----------+
3 rows in set (0.01 sec)


mysql> use db1;
Database changed
mysql> select * from company;
+----+---------+
| id | name    |
+----+---------+
|  1 | alibaba |
+----+---------+
1 row in set (0.00 sec)


mysql> insert into company(id,name) values(2,‘jingdong‘);
Query OK, 1 row affected (0.09 sec)


mysql> select * from company;
+----+----------+
| id | name     |
+----+----------+
|  1 | alibaba  |
|  2 | jingdong |
+----+----------+
2 rows in set (0.02 sec)
可以看出,还可以正常操作。


再查看管理信息:
[root@mycat1 conf]# mysql -uroot -poracle -h192.168.10.21 -P9066
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.8-mycat-1.5-alpha-20151221110028 MyCat Server (monitor)


Copyright (c) 2000, 2013, 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 @@heartbeat;
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME   | TYPE  | HOST          | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| mysql1 | mysql | 192.168.10.22 | 3306 |      -1 |     0 | idle   |       0 | 2,1,3        | 2016-02-21 18:29:58 | false |
| mysql2 | mysql | 192.168.10.23 | 3306 |       1 |     0 | idle   |       0 | 10,4,18      | 2016-02-21 18:29:58 | false |
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
2 rows in set (0.01 sec)


mysql> show @@backend;
+------------+------+---------+---------------+------+--------+----------+---------+-------+--------+----------+------------+--------+----------+---------+------------+
| processor  | id   | mysqlId | host          | port | l_port | net_in   | net_out | life  | closed | borrowed | SEND_QUEUE | schema | charset  | txlevel | autocommit |
+------------+------+---------+---------------+------+--------+----------+---------+-------+--------+----------+------------+--------+----------+---------+------------+
| Processor0 |   16 |       5 | 192.168.10.23 | 3306 |  60959 |       93 |      66 |   270 | false  | false    |          0 | db3    | utf8:33  | 0       | true       |
| Processor1 |   17 |       6 | 192.168.10.23 | 3306 |  60960 |    33756 |     483 |   270 | false  | false    |          0 | db1    | latin1:8 | 3       | true       |
| Processor2 |   18 |       7 | 192.168.10.23 | 3306 |  60961 |       93 |      66 |   270 | false  | false    |          0 | db2    | utf8:33  | 0       | true       |
| Processor3 |   11 |       4 | 192.168.10.23 | 3306 |  60806 | 13320587 |  110199 | 50071 | false  | false    |          0 | db1    | latin1:8 | 3       | true       |
+------------+------+---------+---------------+------+--------+----------+---------+-------+--------+----------+------------+--------+----------+---------+------------+
4 rows in set (0.00 sec)


源主库恢复后,现在主从角色对换了,需要进行如下操作:
恢复原主节点的操作:
先停止源从节点的从复制操作,在启动原主节点的从复制操作
在原来的从库,也就是从现在的主库:
mysql> stop slave;
Query OK, 0 rows affected (0.09 sec)


并将DB1库导出:
/usr/local/mysql/bin/mysqldump -uroot -poracle --master-data=http://www.mamicode.com/2 --single-transaction -R -B db1 > /opt/db1.sql
并将/opt/db1.sql传送到原来的主库。


在原来的主库上:
[root@mysql1 mysql]# /etc/init.d/mysql start
Starting MySQL......[  OK  ]


mysql> drop table company;
Query OK, 0 rows affected (0.09 sec)


mysql> source /opt/db1.sql;
......


mysql> change master to master_host=‘192.168.10.23‘,
    -> master_port=3306, master_user=‘repluser‘, 
    -> master_password=‘oracle‘, master_log_file=‘mysql-bin.000003‘,master_log_pos=324;
Query OK, 0 rows affected, 2 warnings (0.24 sec)


mysql> start slave;
Query OK, 0 rows affected (0.06 sec)


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.23
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 324
               Relay_Log_File: my3306-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
已经恢复正常。


再次登录mycat,进行操作:
[root@mycat1 conf]# mysql -uroot -poracle -h192.168.10.21 -P8066
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.5.8-mycat-1.5-alpha-20151221110028 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2013, 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 databases;
+----------+
| DATABASE |
+----------+
| db1      |
| db2      |
| db3      |
+----------+
3 rows in set (0.00 sec)


查看mycat.log
02/21 18:54:34.371  DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:56) -ServerConnection [id=10, schema=db1, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=db1]select * from company
02/21 18:54:34.371  DEBUG [$_NIOREACTOR-3-RW] (EnchachePool.java:76) -SQLRouteCache  miss cache ,key:db1select * from company
02/21 18:54:34.372  DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:113) -ServerConnection [id=10, schema=db1, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=db1]select * from company, route={
   1 -> dn1{select * from company}
} rrs 
02/21 18:54:34.372  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDBPool.java:452) -select read source mysql1 for dataHost:mysql1
02/21 18:54:34.375  DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=24, lastTime=1456052074371, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=1, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{select * from company}, respHandler=SingleNodeHandler [node=dn1{select * from company}, packetId=6], host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/21 18:54:34.375  DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=24, lastTime=1456052074371, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=1, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
02/21 18:54:36.670  DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:56) -ServerConnection [id=10, schema=db1, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=db1]select * from company
02/21 18:54:36.671  DEBUG [$_NIOREACTOR-3-RW] (EnchachePool.java:76) -SQLRouteCache  miss cache ,key:db1select * from company
02/21 18:54:36.671  DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:113) -ServerConnection [id=10, schema=db1, host=192.168.10.21, user=root,txIsolation=3, autocommit=true, schema=db1]select * from company, route={
   1 -> dn1{select * from company}
} rrs 
02/21 18:54:36.671  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDBPool.java:452) -select read source mysql1 for dataHost:mysql1
02/21 18:54:36.673  DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=24, lastTime=1456052076671, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=1, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{select * from company}, respHandler=SingleNodeHandler [node=dn1{select * from company}, packetId=6], host=192.168.10.22, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
发现select都在原来的主库上了。

Mycat读写分离、主从切换学习(转)