Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MyCAT read and write separation of MySQL     - Source install Python3.4 on CentOS (Linux)

- Use Docker / LXC quickly launch a desktop system (Linux)

- How to install MySQL on Linux Dock (Database)

- Linux kernel network subsystem analysis (Programming)

- Nginx + Tomcat static and dynamic load balancing and separation configuration essentials under Linux (Server)

- The formatted Linux hard drive and mount (Linux)

- Using IntelliJ IDEA 13 integrated development environment to build Android (Linux)

- Quagga use authentication on strengthening BGP session security (Linux)

- Linux Defensive / mitigate DDOS attacks (Linux)

- Oracle Incident Packaging Service (Database)

- DM9000 timing settings (Programming)

- awk Programming Model (Programming)

- Installing PHP Memcache extension under Linux (Server)

- Spring declarative transaction management (Programming)

- Linux loopback adapter Driven Design (Programming)

- libnet list of functions (Programming)

- How the program is executed (Programming)

- CentOS / Linux SELinux Close (Linux)

- C ++ constant definition (Programming)

- Linux Network Security: nmap port scanning software (Linux)

 
         
  MyCAT read and write separation of MySQL
     
  Add Date : 2018-11-21      
         
         
         
  Before MySQL middleware appears, from the main cluster for MySQL, if you want to achieve read and write separation, usually at the end to achieve the program, so that creates a problem, that is, the degree of coupling databases and programs too, if I address database change, then I have to end programs to be modified, if the database does not accidentally hang up, it also means that the program is not available, and that for many applications, and can not accept.

MySQL introduced middleware can be a good end of the program and database decoupling, so that the program can focus on the end of the address database middleware, the underlying database without having to know how to provide service.

As the current hot MySQL middleware, MyCAT achieve MySQL master naturally separated from the cluster of the proper meaning of literacy, its configuration is quite simple.

Here, I used three instances from the main MySQL Cluster to verify MyCAT read and write separation function, in fact, a master-slave to meet, reason with three, is to verify MyCAT of fragmentation.

Cluster is composed as follows:

The role of the host name Host IP

master mysql-server1 192.168.244.145

slave mysql-server2 192.168.244.146

slave mysql-server3 192.168.244.144

Here, use or test Travelrecord table.

First, edit the configuration file MyCAT schema.xml, configuration information about dataHost follows:

< 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 ->
                                         password = "123456">
                < / WriteHost>
                < WriteHost host = "hostS1" url = "192.168.244.146:3306" user = "root"
                        password = "123456" />
                < WriteHost host = "hostS2" url = "192.168.244.144:3306" user = "root"
                        password = "123456" />
< / DataHost>
Inside, there are two parameters need to pay attention, balance and switchType.

Wherein, balance refers to the type of load balancing, the current value of four kinds:

1. balance = "0", do not open separate read and write mechanism, all reads are sent to the currently available writeHost.

2. balance = "1", all readHost and stand by writeHost participate in load balancing select statement, simply put, when double-double master-slave mode (M1-> S1, M2-> S2, and M1 and M2 mainly among equipment), under normal circumstances, M2, S1, S2 are participating in load balancing select statement.

3. balance = "2", all reads are random in writeHost, distributed on readhost.

4. balance = "3", distributed to wiriterHost corresponding readhost all read requests random execution, writerHost does not bear the pressure reading

switchType refers to the switching mode, the current value also has four:

1. switchType = '- 1' means no automatic switch

2. switchType = '1' default, meaning automatic switching

3. switchType = '2' based on MySQL master to decide whether to switch from the status of the synchronization, heartbeat statement for the show slave status

4. switchType = '3' on the switching mechanism of MySQL galary cluster (for clusters) (1.4.1), heartbeat statement show status like 'wsrep%'.

Therefore, the profile of the balance = "1" meant as stand by writeHost of hostS1 and hostS2 will participate in select statement load balancing, which achieved a separate read and write from the master, switchType = '- 1' means that when the Lord hang up when no automatic switch that hostS1 and hostS2 not promoted to master, still provides read-only capabilities. This avoids the possibility of the data is read into the slave, after all, a simple master-slave MySQL Cluster does not allow data to be read into the slave, unless configured a dual master.

Verify separate read and write

Below to verify,

Create Travelrecord table

create table travelrecord (id bigint not null primary key, user_id varchar (100), traveldate DATE, fee decimal, days int);
Insert data

mysql> insert into travelrecord (id, user_id, traveldate, fee, days) values (1, @@ hostname, 20160101,100,10);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> insert into travelrecord (id, user_id, traveldate, fee, days) values (5000001, @@ hostname, 20160102,100,10);
Query OK, 1 row affected, 1 warning (0.01 sec)
Here, with a tricky way, that user_id insert the host name of the current instance, which can read and write directly observed and MyCAT whether separation of fragmentation. Able to do so because of my current version of MySQL statement-based replication -5.6.26 default, if it is row-based replication, then this method is not desirable.

Query data

mysql> select * from travelrecord;
+ --------- + ------------ + --------------- + ------ + --- --- +
| Id | user_id | traveldate | fee | days |
+ --------- + ------------ + --------------- + ------ + --- --- +
| 1 | mysql-server2 | 2016-01-01 | 100 | 10 |
| 5000001 | mysql-server3 | 2016-01-02 | 100 | 10 |
+ --------- + ------------ + --------------- + ------ + --- --- +
2 rows in set (0.01 sec)

mysql> select * from travelrecord;
+ --------- + ------------ + --------------- + ------ + --- --- +
| Id | user_id | traveldate | fee | days |
+ --------- + ------------ + --------------- + ------ + --- --- +
| 5000001 | mysql-server3 | 2016-01-02 | 100 | 10 |
| 1 | mysql-server2 | 2016-01-01 | 100 | 10 |
+ --------- + ------------ + --------------- + ------ + --- --- +
2 rows in set (0.02 sec)

mysql> select * from travelrecord;
+ --------- + ------------ + --------------- + ------ + --- --- +
| Id | user_id | traveldate | fee | days |
+ --------- + ------------ + --------------- + ------ + --- --- +
| 5000001 | mysql-server3 | 2016-01-02 | 100 | 10 |
| 1 | mysql-server3 | 2016-01-01 | 100 | 10 |
+ --------- + ------------ + --------------- + ------ + --- --- +
2 rows in set (0.01 sec)

mysql> select * from travelrecord;
+ --------- + ------------ + --------------- + ------ + --- --- +
| Id | user_id | traveldate | fee | days |
+ --------- + ------------ + --------------- + ------ + --- --- +
| 5000001 | mysql-server3 | 2016-01-02 | 100 | 10 |
| 1 | mysql-server3 | 2016-01-01 | 100 | 10 |
+ --------- + ------------ + --------------- + ------ + --- --- +
2 rows in set (0.01 sec)

mysql> select * from travelrecord;
+ --------- + ------------ + --------------- + ------ + --- --- +
| Id | user_id | traveldate | fee | days |
+ --------- + ------------ + --------------- + ------ + --- --- +
| 1 | mysql-server2 | 2016-01-01 | 100 | 10 |
| 5000001 | mysql-server2 | 2016-01-02 | 100 | 10 |
+ --------- + ------------ + --------------- + ------ + --- --- +
From the above output, we can draw the following points:

First, the configuration has been read and write separation data is not read out the master node.

Two, MyCAT random distribution is not based on the statement that a select statement to query one of the nodes, in addition to a select statement to query another node. It is for the distribution sheet, with the results of a select statement is different dataNode returned.

Moreover, from MyCAT log you can also get separate read and write information, of course, that MyCAT log level is debug. Logs Relevant information is as follows:

Verify mater hanging, slave also provides reading functionality
For master-slave MySQL Cluster, our needs are linked to the master, slave also provides reading functionality.

Below to test

First, turn off the main man-made library

[Root @ mysql-server1 ~] # /etc/init.d/mysqld stop

Login MyCAT

[Root @ mysql-server1 ~] # mysql -utest -ptest -h127.0.0.1 -P8066 -DTESTDB

Insert data

mysql> insert into travelrecord (id, user_id, traveldate, fee, days) values (10000001, @@ hostname, 20160103,100,10);
ERROR 1184 (HY000): Connection refused
mysql> select * from travelrecord;
+ --------- + ------------ + --------------- + ------ + --- --- +
| Id | user_id | traveldate | fee | days |
+ --------- + ------------ + --------------- + ------ + --- --- +
| 1 | mysql-server2 | 2016-01-01 | 100 | 10 |
| 5000001 | mysql-server3 | 2016-01-02 | 100 | 10 |
+ --------- + ------------ + --------------- + ------ + --- --- +
2 rows in set (0.02 sec)
Visible data can not be inserted, but does not affect the read data.

So far, MyCAT achieve read and write separation MySQL deployment testing is completed.

to sum up:

1. In fact, the beginning of the configuration is readHost node configured as follows:

 < 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 = "123456">
                        < -! Can have multi read hosts ->
                < ReadHost host = "hostS1" url = "192.168.244.146:3306" user = "root" password = "123456" />
                < / WriteHost>
< / DataHost>
But in this way there is a problem that the master hung up after, slave can not provide services, in violation of the master-slave MySQL Cluster mind.

2. If the transaction mode is turned on, the set autocommit = 0, then go read within the transaction is master node, rather than from the node.
     
         
         
         
  More:      
 
- History of the most complex validation e-mail address regular expression (Linux)
- Valgrind * not * leak check tool (Linux)
- How to test your MongoDB application upgrade? (Database)
- Linux System Getting Started Learning: After starting in Ubuntu or Debian, enter the command line (Linux)
- Check the Linux server performance with ten or so commands in a minute (Server)
- Linux Network Programming - raw socket instance: MAC header message analysis (Programming)
- Reported too many open files Linux solutions (Server)
- Redis configuration file interpretation (Database)
- Linux, modify / retrieve the root password (Linux)
- Linux (RHEL5) general optimization system security (Linux)
- Deb package installation method under ubuntu (Linux)
- After Oracle 11g dataguard failover rebuild the archive logs are not applied to be NO problem (Database)
- When Linux virtual machine to another copy of the operating system, a static IP NAT mode Invalid (Linux)
- Kubernetes cluster deployment (Server)
- Linux operating system security settings initial understanding (Linux)
- MySQL DATE_FORMAT () function (Database)
- Oracle create user authorization and in PLSQL (Database)
- Linux regex sed detailing (Linux)
- Based on Python: OpenCV simple image manipulation (Programming)
- CentOS 7 Change Hostname (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.