Mycat simply realizes the separation of reading and writing and the separation of libraries and tables.
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.
