Mycat read-Write separation and master-slave switching

1, the advantages and disadvantages of the sub-database, and why the table can not be the mainstream of the way?
Sub-table: On the server, the advantage is easy to maintain, similar to the table partition, the disadvantage is that on a DB server, can not share Io, load set.
Sub-Library: On multiple servers, the advantage is to share IO, load balancing, the disadvantage is more difficult to maintain, data statistics and jion operation of some difficulty.

The purpose of database segmentation is to share IO, load balance, the sub-table can not achieve the best requirements, so can not become mainstream.
2. Prepare the main library
TAR-XVF mysql-5.6.12.tar.gz
CD mysql-5.6.12

Time CMake. -dcmake_install_prefix=/usr/local/mysql56m1-dmysql_datadir=/home/data/mysql56m1/data-dwith_innobase_storage_ Engine=1-dmysql_unix_addr==/usr/local/mysql56m1/mysql.sock-dmysql_user=mysql-ddefault_charset=utf8-ddefault_ Collation=utf8_general_ci

Time make
Time make Install

Chown-r mysql/home/data/
Chgrp-r mysql/home/data/
Chown-r Mysql/usr/local/mysql

Chown-r mysql/usr/local/mysql56m1
Chgrp-r Mysql/usr/local/mysql

Chgrp-r mysql/usr/local/mysql56m1/
Mkdir-p/home/data/mysql56m1/binlog/
Chown-r mysql.mysql/home/data/mysql56m1/binlog/
Mkdir-p/home/data/mysql5610/binlog/
Chown-r mysql.mysql/home/data/

Cd/usr/local/mysql56m1
Time scripts/mysql_install_db–user=mysql–basedir=/usr/local/mysql56m1–datadir=/home/data/mysql56m1/data– Defaults-file=/usr/local/mysql56m1/my.cnf

CP support-files/mysql.server/etc/init.d/mysql56m1
chmod 700/etc/init.d/mysql56m1
echo "Export path= $PATH:/usr/local/mysql56m1/bin" >>/etc/profile
Source/etc/profile
Chkconfig–add mysql56m1

Service mysql56m1 Start

3, prepare the library

TAR-XVF mysql-5.6.12.tar.gz
CD mysql-5.6.12

Time CMake. -dcmake_install_prefix=/usr/local/mysql56s1-dmysql_datadir=/home/data/mysql56s1/data-dwith_innobase_storage_ Engine=1-dmysql_unix_addr==/usr/local/mysql56s1/mysql.sock-dmysql_user=mysql-ddefault_charset=utf8-ddefault_ Collation=utf8_general_ci

Time make
Time make Install

Chown-r mysql/home/data/
Chgrp-r mysql/home/data/
Chown-r Mysql/usr/local/mysql

Chown-r MYSQL/USR/LOCAL/MYSQL56S1
Chgrp-r Mysql/usr/local/mysql

Chgrp-r mysql/usr/local/mysql56s1/
Mkdir-p/home/data/mysql56s1/binlog/
Chown-r mysql.mysql/home/data/mysql56s1/binlog/
Mkdir-p/home/data/mysql5610/binlog/
Chown-r mysql.mysql/home/data/

Cd/usr/local/mysql56s1
Time scripts/mysql_install_db–user=mysql–basedir=/usr/local/mysql56s1–datadir=/home/data/mysql56s1/data– Defaults-file=/usr/local/mysql56s1/my.cnf

CP SUPPORT-FILES/MYSQL.SERVER/ETC/INIT.D/MYSQL56S1
chmod 700/etc/init.d/mysql56s1
echo "Export path= $PATH:/usr/local/mysql56s1/bin" >>/etc/profile
Source/etc/profile
Chkconfig–add MYSQL56S1
Service MYSQL56S1 Start

4, build the master-slave environment
On one server, the primary 3317 port, from 3327 ports.
On the master repository, add the replication account:
GRANT REPLICATION SLAVE on . To ' repl ' @ ' 10.254.% ' identified by ' MYCATMS ';

Set up replication on 3327 ports on the standby:
Stop slave;
Reset slave;
Change Master to master_user= ' Repl ', master_password= ' Mycatms ', master_host= ' 127.0.0.1 ', master_port=3317, Master_log _file= ' mysql-bin.000003 ', master_log_pos=840;
Start slave;
show slave status\g;

Main Library: 3317 ports

From Library: 3327 ports

Data Synchronization test:

5, data Synchronization test:
Configuration schema.xml:
Then modify the Mycat schema.xml:
Balance is 1: let all readhost and spare writehost participate in the load balancing of select.
Switchtype is 2: Determines whether or not to switch based on the state of MySQL master-slave synchronization.
Heartbeat: The heartbeat Statement of the master-slave switch must be show slave status.

5.0 Data Entry:
Mysql> Explain CREATE TABLE company (ID int. NOT NULL primary key,name varchar (100));
+ ——— –+ ——————————————————————— +
| Data_node | SQL |
+ ——— –+ ——————————————————————— +
| DN1 | CREATE TABLE company (ID int. NOT NULL primary key,name varchar (100)) |
| DN2 | CREATE TABLE company (ID int. NOT NULL primary key,name varchar (100)) |
| DN3 | CREATE TABLE company (ID int. NOT NULL primary key,name varchar (100)) |
+ ——— –+ ——————————————————————— +
3 Rows in Set (0.00 sec)

Mysql> CREATE TABLE Company (ID int. NOT NULL primary key,name varchar (100));
Query OK, 0 rows affected (0.01 sec)

Mysql> explain insert INTO company (Id,name) VALUES (1, ' Alibaba ');
+ ——— –+ ———————————————— –+
| Data_node | SQL |
+ ——— –+ ———————————————— –+
| DN1 | INSERT INTO company (Id,name) VALUES (1, ' Alibaba ') |
| DN2 | INSERT INTO company (Id,name) VALUES (1, ' Alibaba ') |
| DN3 | INSERT INTO company (Id,name) VALUES (1, ' Alibaba ') |
+ ——— –+ ———————————————— –+
3 Rows in Set (0.10 sec)

Mysql> INSERT INTO Company (Id,name) VALUES (1, ' Alibaba ');
Query OK, 1 row Affected (0.00 sec)

Mysql>

5.1 Main Library Validation:
[Email protected]_idc_squid_1_11 logs]#/usr/local/mysql56m1/bin/mysql-uroot-p-p3317–socket=/usr/local/mysql56m1 /mysql.sock-e "SELECT @ @port; select * from Db1.company";
Enter Password:
+--–+
| @ @port |
+--–+
| 3317 |
+--–+
+--+ ——— +
| ID | name |
+--+ ——— +
| 1 | Alibaba |
+--+ ——— +
[[Email Protected]_idc_squid_1_11 logs]#

5.2 Verifying from the library:
[Email protected]_idc_squid_1_11 logs]#/usr/local/mysql56s1/bin/mysql-uroot-p-p3327–socket=/usr/local/mysql56s1 /mysql.sock-e "SELECT @ @port; select * from Db1.company";
Enter Password:
+--–+
| @ @port |
+--–+
| 3327 |
+--–+
+--+ ——— +
| ID | name |
+--+ ——— +
| 1 | Alibaba |
+--+ ——— +
[[Email Protected]_idc_squid_1_11 logs]#

6, read/write separation mode
6.1 Then modify the schema.xml of the Mycat:
Balance is 1: let all readhost and spare writehost participate in the load balancing of select.
Switchtype is 2: Determines whether or not to switch based on the state of MySQL master-slave synchronization.
Heartbeat: The heartbeat Statement of the master-slave switch must be show slave status.

There are configuration read nodes:

Mycat read-Write separation and master-slave switching