Mycat-based MySQL master-slave and read-write separation configuration detailed and examples

1, do not use Mycat hosting MySQL master server , simple use the following configuration:

<datanodename= "DN1" datahost= "Localhost1" database= "DB1"/>

<datanodename= "DN2" datahost= "Localhost1" database= "DB2"/>

<datanodename= "Dn3" datahost= "Localhost1" database= "db3"/>

<datahost name= "Localhost1" maxcon= "+" mincon= "balance=" 0 "

Writetype= "0" dbtype= "MySQL" dbdriver= "native" switchtype= "1" slavethreshold= ">"

<writehosthost= "hostm" url= "10.1.176.158:3306" user= "root"

password= "123456" >

</writeHost>

</dataHost>

2 , using Mycat managed master-slave switchover , use the following configuration:

<datahostname= "Localhost1" maxcon= "mincon=" balance= "2"

writetype= "0" dbtype= "MySQL" dbdriver= "native" switchtype= "2" slavethreshold= ">"

show slave status

<!--can have multi write hosts--

<writehosthost= "hostM1" url= "10.1.176.158:3306" user= "root"

password= "123456" >

</writeHost>

<writehosthost= "hostS1" url= "10.1.176.78:3306" user= "root"

password= "Www.com.workssys"/>

</dataHost>

(1) Note that if there is only one writehost, the master hangs up, and the reading cannot be used. Writetype=0, write operation only send the first writehost, happened to switch, the original from change to main, all write operations in the original from the execution, if not two-way copy of the original master data is old. The switch interval is 5 heartbeat time, the default is 10 * 5=50 seconds (the first 10 seconds is not responding, the latter may become 15 seconds to detect, and then 20 seconds, and finally really did not respond to think of hanging), Heartbeat configuration in Server.xml, using the Datanodeheartbeatperiod parameter: the interval between all read and write libraries in the backend, the default is 10 seconds.

(2) Note, after switching to restart the original master, if the main never sync, error: Slave_sql_running:no

After the data is guaranteed to be consistent, execute:

mysql> slave stop;

Query OK, 0 rows Affected (0.00 sec)

Mysql> set GLOBAL sql_slave_skip_counter=1;

Query OK, 0 rows Affected (0.00 sec)

mysql> slave start;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show Slave Status\g

(2) After switching, Mycat will mark the new master-slave state, the master-slave state in the/conf/dnindex.properties file, the normal situation is the original from the main, master-slave Swap, modify the Schema.xml file. However, if you want to restore the original master, you need to perform the switch command of the 9066 Port Mycat management tool to toggle the data source:

Switch @ @datasource Name:index

The name in the Datahost configured in Name:schema.

The Writehost index of the datahost configured in Index:schema, that is, in the order of configuration from top to bottom, starting at 0, that is, the first is Writehost 0 and the other is 1.

When you switch the data source, the connection to all the connection pools in the original data source is closed and a new connection is created from the fresh data source, at which point the Mycat service is unavailable.

The Dnindex.properties file records the current active writer.

<datahostname= "blog" maxcon= "mincon=" balance= "0"

Writetype= "0" dbtype= "MySQL" dbdriver= "native" >

<writehosthost= "Master" url= "127.0.0.1:3306" user= "root" password= "root" ></writeHost>

<writehosthost= "Master2" url= "127.0.0.1:3306" user= "Root1" password= "root" </writeHost>

</dataHost>

Mysql> Show @ @datasource;

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

| DATANODE | NAME | TYPE | HOST | PORT | w/r | ACTIVE | IDLE | SIZE | EXECUTE |

+----------+---------+-------+----------------+------+------+--------+------+--|blog | Master | MySQL | 121.40.121.133 | 3306 | W | 0 | 10 | 100 | 16 |

|blog | Master2 | MySQL | 127.0.0.1 | 3306 | W | 0 | 0 | 100 | 0 |

+----------+---------+-------+----------------+------+------+--------+------+--2rows in Set (0.00 sec)

mysql> switch @ @datasource blog:1;

Query OK, 1 row affected (1min 0.05 sec)

(3) After switching, see which one is performing the library operation

Show @ @heartbeat, show @ @backend

Observe the Rs_code field

(4) After the main sudden outage, the recovery sequence:

1) Check whether the data is synchronized, human synchronization of data, the synchronization of data, note that the master-slave synchronization state must be at the end of the main operation, and then the master will be synchronized to the slave; but once the operation is performed from that end, the master returns to the slave, such as performing the following operation, which is very dangerous.

2) Restore the master node

3) in DB from the switch, stop the original from the node from the copy operation slave Stop, and then start the primary node from the copy operation slave start

4) to the new slave server execute the following statement to see if the master-slave status is normal, first manually start the new slave, because the Skip-slave-start parameter is added

Slave start

Show Slavestatus \g

5) Perform insert validation, at which point the operation ends

6 Note: Mycat do not restart, heartbeat will always be detected, in the DB master-slave swap good without error prompts, restore the heartbeat will return to normal

7) Note: If you need to restore the original configuration, perform a switch operation, but it is not recommended (the switch operation is slow, and there seems to be a problem, does not guarantee that the execution is correct). Switch to check the log before the main heartbeat is back to normal, normal to cut.

Mysql-utest-ptest-hlocalhost-p9066-dtestdb

Switch @ @datasource localhost1:0

8 Note: At present, master and slave are through show slave status, when the main outage, after the switch, from change to primary, the original master changed from, this time show slave status will occur error, because the original master did not open slave, It is not recommended to use the switch operation directly, but instead in db.

(5) Special Note: The schema of each datahost of the host property value must be unique, or the master-slave in all datahost in all the phenomenon of switching, as shown below is not correct, when a group of switches, the other group also switch.

<datahostname= "Userdbhost" maxcon= "mincon=" balance= "2" writetype= "0" dbtype= "MySQL" dbdriver= "native" Switchtype= "2" slavethreshold= ">"

<writehosthost= "hostM1" url= "172.16.224.204:3306" user= "root" password= "root123"/>

<writehosthost= "hostS1" url= "172.16.227.129:3306" user= "root" password= "root123"/>

</dataHost>

<datahostname= "Orderdbhost" maxcon= "mincon=" balance= "2" writetype= "0" dbtype= "MySQL" dbdriver= "native" Switchtype= "2" slavethreshold= ">"

<writehosthost= "hostM1" url= "172.16.224.205:3306" user= "root" password= "root123"/>

<writehosthost= "hostS1" url= "172.16.225.249:3306" user= "root" password= "root123"/>

</dataHost>

(6) Note: Back up the full database and the incremental database every day from the end of the main

(7) Note: Modify the Schema.xml to operate under Linux, otherwise there is a coding error
(8) After modifying the schema.xml, do not need to execute the switch command, restart Mycat automatically reply to the original state

(9) After adding a table or modifying Schema, redeploy order: 1. Modify Mycat schema;2. Update mysql;3. Deploy new Apps

3, Mycat 1.4 support MySQL master-slave replication state binding read-write separation mechanism, so that read more secure and reliable

<datanodename="dn1" datahost="Localhost1" database="db1"/>

<datanodename="DN2" datahost="Localhost1" database="db2"/>

<datanodename="Dn3" datahost="Localhost1" database="db3"/>

<datahostname="Localhost1" maxcon=" mincon="balance= "1"

writetype="0"dbtype="MySQL"dbdriver="native" switchtype="2" slavethreshold=">"

show slave status

<writehosthost= "hostm" url="10.1.176.158:3306" user="root"

password="123456">

<readhosthost="HostS" url="10.1.176.78:3306" user="root"

password="123456"/>

</writeHost>

</dataHost>

(1) Set balance= "1" and writetype= "0"

1) Balance parameter setting:

Load balancing type, there are currently 3 types of values:

<1>balance= "0", does not open the read-write separation mechanism, all read operations are sent to the currently available writehost.

<2> balance= "1", all readhost and stand by writehost participate in load balancing of SELECT statements, simply speaking, when dual master dual slave mode (M1->S1,M2->S2, and M1 and M2 are mainly prepared for each other) , normally, the M2,S1,S2 participates in load balancing of the SELECT statement.

<3>balance= "2", all read operations are randomly distributed on the Writehost, Readhost.

2) writetype parameter setting:

<1> writetype= "0", all write operations are sent to the first writehostof the configuration, and the first one hangs to the second writehost that is still alive. If all hung up, then tragedy, which, write which, it is likely that the data inconsistent; so don't let the mycat back after the database has been restored.

<2>writetype= "1", all write operations are sent randomly to the configured writehost.

<3>writetype= "2", not realized.

"Readhost belongs to Writehost, meaning that it gets synchronized data from that writehost, so when the writehost it belongs to is down, it will no longer participate in the read-write separation, which is" not working, "because at this point, its data is" unreliable The Based on this consideration, currently in the Mycat 1.3 and 1.4 versions, if you want to support MySQL's standard configuration for a master-and-Slave, and when the primary node is down, the data can be read from the node, it needs to be configured in Mycat to two writehost and set banlance=1. ”

(2) Set switchtype= "2" and slavethreshold= "100"

The Mycat heartbeat Check statement is configured as show slave status, and two new attributes are defined on Datahost: switchtype= "2" and slavethreshold= "100", This means that the read-write separation and switching mechanism of the MySQL master-slave replication state binding is turned on. Mycat heartbeat mechanism by detecting "seconds_behind_master", "slave_io_running", "slave_sql_running" in Show slave status Three fields to determine the state of the current master-slave synchronization and the Seconds_behind_master master-slave replication delay. “

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Mycat-based MySQL master-slave and read-write separation configuration detailed and examples