Shulou(Shulou.com)05/31 Report--

This article mainly introduces Mycat middleware how to achieve Mysql data slicing, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Architecture diagram:

Machine planning:

IP address hostname role remark 10.4.132.50k8s01mycatpender master

10.4.132.42k8s02master

10.4.132.66k8s03master

Mycat download address: http://dl.mycat.io/1.6.7.3/20190828135747/Mycat-server-1.6.7.3-release-20190828135747-linux.tar.gz

Mysql download address: http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz

1. Download and install Mysql (all three Mysql need to be installed)

[root@k8s01 soft] # wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz

[root@k8s01 soft] # tar xvf mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz-C / usr/local/

[root@k8s01 soft] # cd / usr/local/

[root@k8s01 local] # mv mysql-5.7.27-linux-glibc2.12-x86_64/ mysql-5.7.27

[root@k8s01 local] # chown-R root:root mysql-5.7.27/

[root@k8s01 local] # cd mysql-5.7.27/

[root@k8s01 mysql-5.7.27] # mkdir data

[root@k8s01 mysql-5.7.27] # useradd-r-M-s / bin/nologin mysql

[root@k8s01 mysql-5.7.27] # chown-R mysql:mysql data/

[root@k8s01 mysql-5.7.27] # / bin/mysqld-- initialize-- user=mysql-- basedir=/usr/local/mysql-5.7.27-- datadir=/usr/local/mysql-5.7.27/data

2019-11-02T04:24:41.908404Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-explicit_defaults_for_timestamp server option (see documentation for more details).

2019-11-02T04:24:46.687678Z 0 [Warning] InnoDB: New log files created, LSN=45790

2019-11-02T04:24:47.428823Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2019-11-02T04:24:47.487404Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b42cef88-fd28-11e9-a5cc-000c29ee86d5.

2019-11-02T04:24:47.488204Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2019-11-02T04:24:47.612739Z 1 [Note] A temporary password is generated for root@localhost: 3m Tencent 5yQuan 7Troujc-login password

[root@k8s01 mysql-5.7.27] # cp-a support-files/mysql.server / etc/init.d/mysqld

[root@k8s01 mysql-5.7.27] # chkconfig-- add mysqld

[root@k8s01 mysql-5.7.27] # chkconfig mysqld on

[root@k8s01 mysql-5.7.27] # vim / etc/init.d/mysqld

Basedir=/usr/local/mysql-5.7.27

Datadir=/usr/local/mysql-5.7.27/data

[root@k8s01 mysql-5.7.27] # vim / etc/my.cnf

[mysqld]

Basedir=/usr/local/mysql-5.7.27

Datadir=/usr/local/mysql-5.7.27/data

Socket=/tmp/mysql.sock

Symbolic-links=0

Server_id=10

Binlog_format=ROW

Max_binlog_size=2G

Sync_binlog=1

Binlog_cache_size=64M

Log_bin=bin-log

Log_bin_index=bin-index

[mysqld_safe]

Log-error=/usr/local/mysql-5.7.27/data/mariadb.log

Pid-file=/usr/local/mysql-5.7.27/data/mariadb.pid

[root@k8s01 mysql-5.7.27] # / etc/init.d/mysqld restart

ERROR! MySQL server PID file could not be found!

Starting MySQL.Logging to'/ usr/local/mysql-5.7.27/data/mariadb.log'.

... SUCCESS!

[root@k8s01 mysql-5.7.27] # vim / etc/profile

Export PATH=$PATH:/usr/local/mysql-5.7.27/bin

[root@k8s01 mysql-5.7.27] # mysql- u root-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 3

Server version: 5.7.27

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respectiveowners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > set password=password ('System135')

Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > exit

Bye

[root@k8s01 mysql-5.7.27] # mysql- u root-pSystem135

Mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 6

Server version: 5.7.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > grant all privileges on *. * to repl@'%' identified by '123456'

Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql >

two。 Download and install Mycat

[root@k8s01 soft] # rpm-ivh jdk-8u221-linux-x64.rpm

Warning: jdk-8u221-linux-x64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY

Preparing... # # [100%]

Updating / installing...

1:jdk1.8-2000:1.8.0_221-fcs # # [100%]

Unpacking JAR files...

Tools.jar...

Plugin.jar...

Javaws.jar...

Deploy.jar...

Rt.jar...

Jsse.jar...

Charsets.jar...

Localedata.jar...

[root@k8s01 soft] # tar xvf Mycat-server-1.6.7.3-release-20190828135747-linux.tar.gz-C / usr/local/

[root@k8s01 soft] # cd / usr/local/mycat/conf/

[root@k8s01 conf] #

3. Data is sliced by range

[root@k8s01 conf] # vim schema.xml

[root@k8s01 conf] # vim rule.xml-- Don't move anything else, just modify the following

[root@k8s01 conf] # vim autopartition-long.txt

# range start-end, data node index

# Knights 1000, Manners 10000. -- K means 1000, M means 10,000

0-5-0-- put from 0 to 5 to the first node

5-10-1-- put from 6 to 10 to the second node

10-15-2-put it from 11 to 15 to the third node

[root@k8s01 conf] # vim server.xml-- Mycat login username and password

[root@k8s01 conf] #.. / bin/mycat restart

Stopping Mycat-server...

Stopped Mycat-server.

Starting Mycat-server...

[root@k8s01 conf] # netstat-antulp | grep 8066

Tcp6 0 0: 8066: * LISTEN 46762/java

[root@k8s01 conf] # netstat-antulp | grep 9066

Tcp6 0 0: 9066: * LISTEN 46762/java

[root@k8s01 conf] #

4. Verify that the data is sliced and stored

[root@k8s01 conf] # / usr/local/mysql-5.7.27/bin/mysql-u root-pSystem135-P8066-h 127.0.0.1-- Log in to Mycat data

Query the written data:

The k8s01 node verifies the data:

The k8s02 node verifies the data:

The k8s03 node verifies the data:

5. Data is sliced by date (month)

[root@k8s01 conf] # vim schema.xml

[root@k8s01 conf] # vim rule.xml

[root@k8s01 conf] #.. / bin/mycat restart

Stopping Mycat-server...

Stopped Mycat-server.

Starting Mycat-server...

[root@k8s01 conf] #! net

Netstat-antulp | grep 9066

Tcp6 0 0: 9066: * LISTEN 69040/java

[root@k8s01 conf] # netstat-antulp | grep 8066

Tcp6 0 0: 8066: * LISTEN 69040/java

[root@k8s01 conf] #

6. Verify that the data is sliced and stored

Verify the data:

K8s01 node:

K8s02 node:

K8s03 node:

Error handling:

Mysql > insert into t_wuhan (create_time,name,age) values (2015-04-01 "," huanggang ", 16)

ERROR 1064 (HY000): Can't find a valid data node for specified node index: T_WUHAN-> CREATE_TIME-> 2015-04-01-> Index: 3

Solution:

When writing April data, you will be prompted that the node cannot be found, because several nodes will write the month data. For example, I only have three node nodes, and I can only write January-March data.

7. Data is sliced by enumeration

[root@k8s01 conf] # vim schema.xml [root@k8s01 conf] # vim rule.xml

[root@k8s01 conf] # cat partition-hash-int.txt-- multiple enumerations can be written

Student = 0

Teacher = 1

DEFAULT_NODE=2

[root@k8s01 conf] #

8. Verify that the data is sliced and stored

Verify the data:

K8s01 node:

K8s02 node:

K8s03 node:

Error handling:

Mysql > insert into t_wuhan (id,name,age) values (1, "tong", "student")

ERROR 1064 (HY000): columnValue: student Please check if the format satisfied.

Mysql > insert into t_wuhan (id,name,age) values (1, "tong", 'student')

ERROR 1064 (HY000): columnValue: student Please check if the format satisfied.

Mysql >

Solution:

The type:type value defaults to 0, indicating that the value is an integer. A value of 1 indicates that it is a string.

DefaultNode: the value corresponds to the value of DEFAULT_NODE in the partition-hash-int.txt file.

Thank you for reading this article carefully. I hope the article "how to achieve Mysql data slicing with Mycat Middleware" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.