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

Experimental environment:

Circular replication of three nodes prepared in advance

Master1:192.168.8.57

Master2:192.168.8.58

Master3:192.168.8.59

Oneproxy:192.168.8.63

1. Download oneproxy at http://www.onexsoft.com/

Upload to 192.168.8.63 and decompress to / usr/local

2. Modify the file descriptor 65535

Vi / etc/security/limits.conf

Soft nofile 65535

Hard nofile 65535

Vi / etc/sysctl.conf

Fs.file-max=655350

Net.ipv4.ip_local_port_range=1025 65000

Net.ipv4.tcp_tw_reuse=1

3. Create an encrypted password

[root@oneproxy bin] #. / mysqlpwd mysql

F1F2E852DCCD764DFA90870C3AF7D6C9D71E7E1B

4. Modify demo.sh

Demo.sh in the directory is the first startup script to modify ONEPROXY_HOME

[root@oneproxy oneproxy] # cat demo.sh

# / bin/bash

#

Export ONEPROXY_HOME=/usr/local/oneproxy

Ulimit-c unlimited

# valgrind-- leak-check=full\

${ONEPROXY_HOME} / bin/oneproxy-- defaults-file=$ {ONEPROXY_HOME} / conf/proxy.conf

5. Modify oneproxy.service

Oneproxy.service is a startup script that modifies ONEPROXY_HOME

[root@oneproxy oneproxy] # cat oneproxy.service

#! / bin/bash

# chkconfig:-30 21

# description: OneProxy service.

# Source Function Library

. / etc/init.d/functions

# OneProxy Settings

ONEPROXY_HOME=/usr/local/oneproxy

ONEPROXY_SBIN= "${ONEPROXY_HOME} / bin/oneproxy"

ONEPROXY_CONF= "${ONEPROXY_HOME} / conf/proxy.conf"

ONEPROXY_PID= "${ONEPROXY_HOME} / log/oneproxy.pid"

RETVAL=0

Prog= "OneProxy"

Start () {

Echo-n $"Starting $prog..."

Daemon $ONEPROXY_SBIN-- defaults-file=$ONEPROXY_CONF

RETVAL=$?

Echo

}

Stop () {

Echo-n $"Stopping $prog..."

If [- e ${ONEPROXY_PID}]; then

Daemon kill-INT $(cat ${ONEPROXY_PID})

RETVAL=$?

Fi

Echo

}

Restart () {

Stop

Sleep 1

Start

}

Case "$1" in

Start)

Start

Stop)

Stop

Restart)

Restart

*)

Echo $"Usage: $0 {start | stop | restart}"

RETVAL=1

Esac

Exit $RETVAL

6. Configure OneProxy

Root@oneproxy conf] # cat proxy.conf

[oneproxy]

Keepalive = 1

Event-threads = 1

Log-file = log/oneproxy.log

Pid-file = log/oneproxy.pid

Lck-file = log/oneproxy.lck

# proxy-license = A2FF461456A67F28 D2F6A5AD70C9042D

# proxy-auto-readonly = 1

# proxy-forward-clientip = 1

# proxy-trans-debug = 1

Proxy-address =: 3307

Admin-address =: 4040

Mysql-version = 5.7.18

Proxy-master-addresses.1 = 192.168.8.57:3306@group1

Proxy-master-addresses.2 = 192.168.8.58:3306@group2

Proxy-master-addresses.3 = 192.168.8.59:3306@group3

Proxy-user-list = test/F1F2E852DCCD764DFA90870C3AF7D6C9D71E7E1B@test

# proxy-user-list2 = root/F1F2E852DCCD764DFA90870C3AF7D6C9D71E7E1B@test

# proxy-user-group = test:root/F1F2E852DCCD764DFA90870C3AF7D6C9D71E7E1B@test

Proxy-charset = utf8_bin

Proxy-group-policy.1 = group1:master-only

Proxy-group-policy.2 = group2:master-only

Proxy-group-policy.3 = group3:master-only

Proxy-secure-client.1 = 192.168.8.57

Proxy-secure-client.2 = 192.168.8.58

Proxy-secure-client.3 = 192.168.8.59

# remote-address.1 = 192.168.1.119purl 4041

Proxy-httptitle = OneProxy Monitor

Proxy-httpserver =: 8080

Proxy-part-tables.1 = conf/part.txt

7. Configure part.txt

[root@oneproxy conf] # cat part.txt

[

{

"table": "steven"

"pkey": "id"

"type": "int"

"method": "hash"

"partitions":

[

{"suffix": "_ 0", "group": "group1"}

{"suffix": "_ 1", "group": "group2"}

{"suffix": "_ 2", "group": "group3"}

]

}

]

8. Start oneproxy

[root@oneproxy conf] #. / oneproxy.service start

Starting OneProxy... [OK]

9. Functional test of sub-database and sub-table.

The client connects to port 3307 of OneProxy and creates the steven table under test.

Mysql-utest-pmysql-h292.168.8.63-P3307 test

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 103

Server version: 5.7.18 OneProxy-6.2.0 (OneXSoft)

Copyright (c) 2000, 2017, 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 > create table steven (

Id int (10) unsigned NOT NULL AUTO_INCREMENT

A1 int (10) NOT NULL DEFAULT'0'

A2 int (10) unsigned DEFAULT NULL

A5 int (10) unsigned NOT NULL DEFAULT'0'

A3 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

A4 varchar (200) NOT NULL DEFAULT''

PRIMARY KEY (id)

KEY idx_a1 (A1)

KEY idx_a2 (a2)) ENGINE=InnoDB

10. Insert test data into the steven table

Insert into steven (id,a1,a2,a5,a4) values (1)

Insert into steven (id,a1,a2,a5,a4) values (2pm 2pm 2pm 2m 2')

Insert into steven (id,a1,a2,a5,a4) values (3 recollection, 3 recitation, 3 recitation, 3')

Insert into steven (id,a1,a2,a5,a4) values (4, 4, 4, 4 and 4)

Insert into steven (id,a1,a2,a5,a4) values (5pm, 5pm, 5pm, 5pm)

Insert into steven (id,a1,a2,a5,a4) values (6, 6, 6, 6)

Insert into steven (id,a1,a2,a5,a4) values (7, 7, 7, 7)

Insert into steven (id,a1,a2,a5,a4) values (8, 8, 8, 8 and 8)

Insert into steven (id,a1,a2,a5,a4) values (9, 9, 9, 9, and 9)

11. View the data of each node:

Mysql > show databases

+-+

| | Database |

+-+

| | group3 |

| | group1 |

| | group2 |

+-+

3 rows in set (0.00 sec)

Mysql > use group1

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | oneproxy_replication_timestamp |

| | steven_0 |

| | steven_1 |

| | steven_2 |

+-+

4 rows in set (0.00 sec)

Mysql > select * from steven_0

->

+-+-

| | id | A1 | a2 | A5 | A3 | A4 |

+-+-

| | 3 | 3 | 3 | 3 | 2018-03-07 10:08:43 | 3 |

| | 6 | 6 | 6 | 6 | 2018-03-07 10:08:43 | 6 |

| | 9 | 9 | 9 | 9 | 2018-03-07 10:08:44 | 9 |

+-+-

3 rows in set (0.00 sec)

Mysql > select * from steven_1

->

+-+-

| | id | A1 | a2 | A5 | A3 | A4 |

+-+-

| | 1 | 1 | 1 | 1 | 2018-03-07 10:08:44 | 1 |

| | 4 | 4 | 4 | 4 | 2018-03-07 10:08:44 | 4 |

| | 7 | 7 | 7 | 7 | 2018-03-07 10:08:44 | 7 |

+-+-

3 rows in set (0.00 sec)

Mysql > select * from steven_2

+-+-

| | id | A1 | a2 | A5 | A3 | A4 |

+-+-

| | 2 | 2 | 2 | 2 | 2018-03-07 10:08:43 | 2 |

| | 5 | 5 | 5 | 5 | 2018-03-07 10:08:43 | 5 |

| | 8 | 8 | 8 | 8 | 2018-03-07 10:08:43 | 8 |

+-+-

3 rows in set (0.01sec)

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.