Shulou(Shulou.com)06/01 Report--

Mycat Learning practice-basic functions of Mycat

Learning mycat mysql

Mycat Learning practice-basic functions of Mycat

1. Mycat high availability-read-write separation

1.1 read-write separation configuration parameters

1.2 heartbeat configuration parameters

2. Mycat high availability-multi-master handoff

2.1 Master-Slave switch configuration parameters

2.2 Master-Slave switch Mark

3 Notes

1. Mycat high availability-read-write separation

1.1 read-write separation configuration parameters

Schema.dataHost parameter balance setting value:

Balance= "0", the read-write separation mechanism is not enabled, and all read operations are sent to the currently available writeHost.

Balance= "1", all readHost and stand by writeHost participate in the load balancing of select statements

To put it simply, when the double master and double slave mode (M1-> S1 Magi M2-> S2, and M1 and M2 are the main and standby for each other)

Under normal circumstances, M2, S1, S2 participates in the load balancing of select statements.

Balance= "2", all read operations are randomly distributed on writeHost and readhost.

Balance= "3". All read requests are randomly distributed to the readhost corresponding to wiriterHost for execution. WriterHost does not bear the read pressure.

The SQL within the transaction, which defaults to the write node and starts with the comment / balance/, gets b according to balance= "1" or "2".

For SQL within a non-transaction, read-write separation is enabled by default according to balance= "1" or "2". If you start with comment / balance/, it will solve the problem.

A scenario where read-write separation is enabled but requires strong consistent real-time data acquisition.

1.2 heartbeat configuration parameters

SwitchType= "1": based on basic table access judgment

Schema.xml

Select user ()

SwitchType= "2": show slave status displays the master-slave synchronization status

Schema.xml

Show slave status

The Mycat heartbeat mechanism determines the status of the current master-slave synchronization and the Seconds_Behind_Master master-slave replication delay by detecting the "Seconds_Behind_Master", "Slave_IO_Running" and "Slave_SQL_Running" fields in the show slave status. When Seconds_Behind_Master > slaveThreshold, the read-write separation filter will filter out the Slave machine.

SwitchType= "3", MyCAT heartbeat check statement is configured as show status like 'wsrep%', enabling read-write separation and switching mechanism of MySQL cluster replication state binding

Schema.xml

Show status like 'wsrep%'

Configuration file: conf/log4j2.xml

Log file: logs/mycat.log

Description: modify the log4j log collection to debug mode, through the log information can be analyzed whether to read and write separate send

Born at that node

2. Mycat high availability-multi-master handoff

2.1 Master-Slave switch configuration parameters

Schema.xml

Select user ()

Multiple writeHost nodes need to be configured

SwitchType attribute

-1 means no automatic switching

1 default value, automatic switching

2 decide whether to switch based on the state of MySQL master-slave synchronization, and the heartbeat statement is show slave status.

3 MySQL galary cluster-based handover mechanism (suitable for cluster) heartbeat statement is show status like 'wsrep%'

2.2 Master-Slave switch Mark

Conf/dnindex.properties

# update#Tue Jul 25 14:20:40 CST 2017dh-01=0

Notes in use:

The premise is to configure at least 2 writeHost

And turn on automatic switching.

If you can't cut automatically, don't cut automatically.

If you can execute it manually, don't automatically.

Data loss problem

The original owner should be obedient after joining.

3 Notes

Mycat provides a solution for unsupported sql, that is, annotations (add an additional piece of code consisting of annotated sql before the sql statement to be executed, so that sql can be executed correctly, which is equivalent to a transparent proxy forwarding of unsupported sql statements. )

The form is:

/ *! mycat: sql=Sql statement * / actually execute Sql

'!' supported by comments Not compatible with mysql single library

Annotations supported'# 'are not compatible with mybatis

New mycat character prefix flag Hintsql: "/ * mycat: /"

Parameter description collates the list:

Reference:

[1] http://mycat.io/

[2] "distributed database architecture and enterprise practice-- based on Mycat middleware"

[3] long GE official course Courseware

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.