This is the 28th day of my participation in the August Challenge

Mycat provides two kinds of functions: library table and read/write table. The following will explain how to implement them respectively.

1. Separate databases and tables

1. Modify the schema. The XML

Copy the following configuration to a file. For variable configurations, see Modify.

<? The XML version = "1.0"? > <! DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <! --> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"> <! -- name: datanode -- name: datanode -- name: datanode -- name: datanode -- name: datanode --> <table name="user" primaryKey=" ID "autoIncrement="true" dataNode="dn1,dn2" rule="mod-long" /> </schema> <! -- dn1 dn2 corresponds to datanode, datahost is the alias database of the following data source: DataNode name="dn1" dataHost="localhost1" database="test" /> <dataNode name="dn2" dataHost="localhost2" database="test" /> <! Datahost name=" localHost1 "maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <! -- can have multi write hosts --> <writeHost host="hostM1" url="localhost:3306" user="root" password="root"> </writeHost> </dataHost> <dataHost name="localhost2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <! -- can have multi write hosts --> <writeHost host="hostS2" URL ="192.168.2.134:3306" user="root" password="123"> </writeHost> </dataHost> </mycat:schema>Copy the code

Effect of 2.

When we insert into the mycat service, we insert data in rotation between the database, DN1 and DN2.

2. Read/write separation

1. Modify the schema. The XML

Copy the following configuration to a file. For variable configurations, see Modify.

<? The XML version = "1.0"? > <! DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"> <! -- auto sharding by id (long) --> <table name="user" primaryKey="id" autoIncrement="true" dataNode="dn1" /> </schema> <dataNode name="dn1" dataHost="localhost1" database="test" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <! -- can have multi write hosts --> <writeHost host="hostM1" url="localhost:3306" user="root" password="root"> <readHost Host ="hostM1" URL ="192.168.2.134:3306" user="root" password="123"> </readHost> </writeHost> </dataHost> </ myCAT :schema>Copy the code

Effect of 2.

Read and write work on different databases.

3. The implied

Mycat is a copy of data from the master database to the slave database and a copy of data from the slave database.

1. The answer

The master library sends the binary log through IO to the slave library relay log, which is stored in OS cache. Because it is IO, it is faster than executing SQL.

2. The principle of replication

  1. The master logs changes to the binary log (called binary log events);
  2. The slave copies the master binary log events to its relay log.
  3. Slave rewrites events in the trunk log and applies the changes to its own data.

3. Detailed explanation of the principle of replication

  1. The first part of the process is for the master to log binary logs. Before each transaction updates the data, the master logs these changes in the second log. MySQL writes transactions serially to the binary log, even though the statements in the transaction are executed across. After the event is written to the binary log, the master notifies the storage engine to commit the transaction.
  2. The next step is for the slave to copy the master’s binary log into its own relay log. First, slave starts a worker thread — the I/O thread. The I/O thread opens a normal connection on the master and starts the binlog dump process. The Binlog dump process reads events from the master’s binary logs. If it has followed the master, it sleeps and waits for the master to generate new events. The I/O thread writes these events to the relay log. SQL slave Threads process the last step of the process. The SQL thread reads the events from the slave log and replays the events to update the slave data to match the data in the master. As long as the thread is consistent with the I/O thread, the relay log is usually in the OS cache, so the overhead of the relay log is minimal.
  3. In addition, there is a worker thread in the master: as with all MySQL connections, opening a connection in the master causes the master to start a thread. The replication process has one important limitation — replication is serialized on the slave, which means that parallel updates on the master cannot be performed in parallel on the slave.

Important: The medium responsible for transferring the various changes between the master and slave servers is the binary change log of the master server, which records the various changes that need to be transferred to the slave server. Therefore, binary logging must be enabled on the primary server. The slave server must have permissions that allow it to connect to the master server and request that the binary change log be transferred to it.