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

Read-write separation of Mycat database

Environment:

Client 1.13 ↓ mycat middleware 1.11 ↙ ↘ master host 1.12 slave host 1.101, master host (1.12) configuration

The two hosts must be synchronized in time to deploy the ntp service

Steps:

① configuration my.cnf

[root@192 ~] # vim / etc/my.cnf

[mysqld]... server_id = 1log_bin = mysql-bin

[root@192 ~] # systemctl restart mysqld

② configuration replication user and root user rights

[root@192 ~] # mysql

Mysql > grant replication slave on. To 'myslave'@'192.168.1.%' identified by' 123.com'

Mysql > grant all on. To 'root'@'%' identified by' 123.com'

Mysql > flush privileges

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000001 | 1334 | |

+-+

II. Slave host (1.10) configuration

Steps:

① configuration my.cnf

[root@192 ~] # vim / etc/my.cnf

[mysqld]... server_id = 2relay-log = relay-log-binrelay-log-index = slave-relay-bin.index

[root@192 ~] # systemctl restart mysqld

② configuration synchronization and root permissions

[root@192 ~] # mysql

Mysql > change master to master_host='192.168.1.12',master_user='myslave',master_password='123.com',master_log_file='mysql-bin.000001',master_log_pos=1334

Mysql > start slave

Mysql > show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

....

Mysql > grant all on. To root@'%' identified by '123.com'

Mysql > flush privileges

③ goes back to the master host to create a test library

Mysql > create database test

III. Mycat host (1.11) configuration

Steps:

① install jdk

Select the version that matches the number of bits of the operating system

[root@192 ~] # systemctl stop firewalld

[root@192] # tar xf jdk-7u65-linux-x64.gz-C / usr/src

[root@192 ~] # cd / usr/src

[root@192 src] # mv jdk1.7.0_65/ / usr/local/java

② install mycat

[root@192 src] # wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

[root@192] # tar zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz-C / usr/src

[root@192 ~] # cd / usr/src

[root@192 src] # mv mycat/ / usr/local/

③ loads environment variables

[root@192 ~] # vi / etc/profile

.export PATH=$PATH:/usr/local/java/binexport JAVA_HOME=/usr/local/javaexport MYCAT_HOME=/usr/local/mycatexport PATH=$PATH:/usr/local/mycat/bin

[root@192 ~] # source / etc/profile

④ configuration server.xml

[root@192 ~] # cd / usr/local/mycat/conf/

[root@192 conf] # vim server.xml

. 123.com test user test true

⑤ configuration schema.xml

[root@192 conf] # vim schema.xml

Show slave status

⑥ configuration wrapper.conf

[root@192 conf] # vim wrapper.conf

Wrapper.java.command=/usr/local/java/bin/java

⑦ starts the mycat server

[root@192 logs] # mycat start

[root@192 logs] # tailf wrapper.log # observe the startup log to facilitate troubleshooting

[root@192 logs] # ss-anpt | grep java

LISTEN 01 127.0.0.1 java 32000: users: ("java", pid=40133,fd=4)

LISTEN 0 50:: 50632: users: ("java", pid=40133,fd=51)

LISTEN 0 100:: 9066: users: ("java", pid=40133,fd=69)

LISTEN 0 50:: 33782: users: ("java", pid=40133,fd=53)

LISTEN 0 50:: 1984: users: ("java", pid=40133,fd=52)

LISTEN 0 100: 8066: * users: ("java", pid=40133,fd=73)

IV. The client (1.13) verifies the separation of read and write

Steps:

① logs in to the connection port

[root@192] # mysql-h 192.168.1.11-P 8066-uroot-p123.com

Mysql > show databases

+-+

| | DATABASE |

+-+

| | test |

+-+

1 row in set (0.01 sec)

Mysql > use test

Database changed

Mysql > create table tb (id int)

Query OK, 0 rows affected (0.03 sec)

Mysql > insert into tb values (1)

Query OK, 1 row affected (0.05sec)

Mysql > select * from tb

+-+

| | id |

+-+

| | 1 |

+-+

1 row in set (0.02 sec)

② login management port

[root@192] # mysql-P9066-uroot-p123.com-h 192.168.1.11

Mysql > show @ @ datasource

+-+

| | DATANODE | NAME | TYPE | HOST | PORT | Wamp R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |

+-+

| | dn1 | hostM1 | mysql | 192.168.1.12 | 3306 | W | 0 | 10 | 1000 | 49 | 0 | 2 |

| | dn1 | hostS1 | mysql | 192.168.1.10 | 3306 | R | 0 | 6 | 1000 | 44 | 2 | 0 |

+-+

Observing the load change of the two hosts from the management port, it is found that READ_LOAD is 2 in hostS1 and WRITE_LOAD is 2 in hostM1, indicating that the separation of read and write has been realized.

Mycat simple practice sub-library and sub-table

Reference:

Https://segmentfault.com/a/1190000012054904

Https://blog.csdn.net/kk185800961/article/details/51147029

Environment:

Client ↓ mycat middleware (1.11) ↓ master database (1.12) 1, master database configuration (1.12)

Steps:

① creates database tables in the master database

Mysql > create database db01

Mysql > create database db02

Mysql > create database db03

Mysql > CREATE TABLE users (

Id INT NOT NULL AUTO_INCREMENT

Name varchar (50) NOT NULL default''

PRIMARY KEY (id)

) AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8

Mysql > CREATE TABLE item (

Id INT NOT NULL AUTO_INCREMENT

Value INT NOT NULL default 0

PRIMARY KEY (id)

) AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8

Mysql > CREATE TABLE item_detail (

Id INT NOT NULL AUTO_INCREMENT

Value INT NOT NULL default 0

Name varchar (50) NOT NULL default''

Item_id INT NOT NULL

PRIMARY KEY (id)

Key (item_id)

) AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8

Three tables are each in three libraries, with a total of nine tables and three libraries.

② assigns root segments

Mysql > grant all on. To root@'%' identified by '123.com'

③ turns off firewall or opens port

[root@192 ~] # systemctl stop firewalld

II. Mycat middleware configuration (1.11)

Pre-deployment installation strategy

Steps:

① configuration server.xml

[root@192 ~] # cd / usr/local/mycat/conf/

[root@192 conf] # vim server.xml

. 123.com TESTDB user TESTDB true

② configuration schema.xml

[root@192 conf] # vim schema.xml

Select user ()

③ is configured with rule.xml, which is divided into three pieces by default and needs to be modified.

. Id mod-long. two

④ configuration wrapper.xml

[root@192 conf] # vim wrapper.conf

Wrapper.java.command=/usr/local/java/bin/javawrapper.working.dir=..

⑤ turns off firewall or opens port

[root@192 ~] # systemctl stop firewalld

⑥ starts mycat

[root@192 conf] # mycat console

[root@192 ~] # ss-anpt | grep java

LISTEN 0 100:: 9066: users: ("java", pid=39691,fd=69)

LISTEN 0 50:: 1984: users: ("java", pid=39691,fd=52)

LISTEN 0 100:: 8066: users: ("java", pid=39691,fd=73)

LISTEN 0 50:: 58818: users: ("java", pid=39691,fd=53)

LISTEN 0 50: 46407: * users: ("java", pid=39691,fd=51)

III. Verification

Steps:

① logs in from the client and inserts data

[root@192] # mysql-h 192.168.1.11-P8066-DTESTDB-uroot-p123.com

Mysql > show databases

+-+ +

| | DATABASE |

+-+ +

| | TESTDB |

+-+ +

Mysql > use TESTDB

Mysql > show tables

+-+ +

| | Tables in TESTDB |

+-+ +

| | item |

| | item_detail |

| | users |

+-+ +

Mysql > insert into users (name) values ('')

Mysql > insert into item (id,value) values (1pm 10)

Mysql > insert into item (id,value) values (2meme 20)

Mysql > insert into item_detail (id,value,name,item_id) values; # list all the names, otherwise an error will be reported.

Mysql > insert into item_detail (id,value,name,item_id) values

Mysql > insert into item_detail (id,value,name,item_id) values (3pm, 30pm, 3kkpm, 55th)

Mysql > insert into item_detail (id,value,name,item_id) values

Mysql > select * from users

+-+ +

| | id | name |

+-+ +

| | 1 | |

+-+ +

Mysql > select * from item

+-+ +

| | id | value |

+-+ +

| | 2 | 20 |

| | 1 | 10 |

+-+ +

Mysql > select * from item_detail

+-- +

| | id | value | name | item_id | |

+-- +

| | 1 | 10 | wu | 1 |

| | 3 | 30 | kk | 55 | |

| | 2 | 20 | kk | 2 |

| | 4 | 40 | kk | 66 | |

+-- +

② logs in to the main database to view the storage location of the data table

[root@192] # mysql-uroot-p123.com

Mysql > select * from db01.users

+-+ +

| | id | name |

+-+ +

| | 1 | |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from db02.users

Empty set (0.01sec)

Mysql > select * from db03.users

Empty set (0.01sec)

Mysql > select * from db01.item_detail

Empty set (0.01sec)

Mysql > select * from db02.item

+-+ +

| | id | value |

+-+ +

| | 2 | 20 |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from db03.item

+-+ +

| | id | value |

+-+ +

| | 1 | 10 |

+-+ +

1 row in set (0.00 sec)

Mysql > select * from db01.item_detail

Empty set (0.01sec)

Mysql > select * from db02.item_detail

+-- +

| | id | value | name | item_id | |

+-- +

| | 2 | 20 | kk | 2 |

| | 4 | 40 | kk | 66 | |

+-- +

2 rows in set (0.00 sec)

Mysql > select * from db03.item_detail

+-- +

| | id | value | name | item_id | |

+-- +

| | 1 | 10 | wu | 1 |

| | 3 | 30 | kk | 55 | |

+-- +

2 rows in set (0.00 sec)

By using the hash sharding rule of mycat, in the balanced storage of master data, users is defined in the db01 library, item and item_detail are distributed in the db02,db03 library, and the verification is successful.

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.