"Novice Novice as-mysql Proxy server settings and cluster"

MySQL Proxy Server Setup (read/write separation)

Lab Environment:

Physical connection between all hosts is normal

the database service on the 2 database server is functioning correctly, and a library with the same name as the same table is established during the period, but the content is different to verify

Stop The database service on the MySQL proxy server

2 Yeyue users on the database server can access themselves from all hosts on the network and have full access to all tables of their own library password 123

Grant all on * * to [e-mail protected] "%" identified by "123";

Second, configure the proxy server

1 . Verify authorized user rights on 2 database servers

Mysql-h192.168.1.10-uyeyue-p123

Mysql-h192.168.1.1-uyeyue-p123

2.Install The software that provides MySQL agent service

[Email protected] ~]# Rpm-qa | grep LUA

Lua-5.1.4-4.1.el6.x86_64

[Email protected] ~]#

Tar-zxvfmysql-proxy-0.8.3-linux-rhel5-x86-64bit.tar.gz

MV Mysql-proxy-0.8.3-linux-rhel5-x86-64bit/usr/local/mysqlproxy

cd/usr/local/mysqlproxy/

Bin executable Command

Include Lib Libexec licenses the library file and header files that are called when the program runs

share/doc/mysql-proxy/ script file directory

CD share/doc/mysql-proxy/

chmod +x Rw-splitting.lua

Main command Options

-p Specifies the IP address that the agent listens on , Port

-r Specifies the IP address of the read server , Port

-B Specifies the IP address of the write server , Port

- s specifies the path to the Lua script file

--keeplive Automatically restarts this process if the process crashes

./mysql-proxy-p 192.168.13.17:3306-r192.168.13.15:3306-b 192.168.13.20:3306-s/usr/local/ Mysql-proxy-0.8.3-linux-rhel5-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua-- running a proxy server

when the proxy server is running, you can specify multiple R and b. The run order takes precedence when used.

start the MySQL Agent service

The two database server MySQL service is shut down before booting to ensure the port is open.

Cd/usr/local/mysqlproxy/bin

./mysql-proxy-p 192.168.1.100:3306-r 192.168.1.10:3306-b 192.168.1.1:3306-s/usr/local/mysqlproxy/share /doc/mysql-proxy/rw-splitting.lua &

To see if it started successfully

Netstat-utnalp | grep:3306

stop The MySQL Proxy service

Pkill-9 Mysql-proxy

Third, the function of testing the MySQL agent service

when the number of concurrent connections is <=4 , the read and write operations are sent to the database server responsible for writing (only the content of the write server can be seen).

when the number of concurrent connections is >4, the user 's read and write requests are differentiated, and the first distinction will remain differentiated.

(You can see the contents of the read and write server)

Vim Rw-splittiing.lua-- Modify the number of concurrent connections files

Min_idle_connections = 4,

the client accesses the data through the MySQL Proxy service.

Mysql-h192.168.1.100-uyeyue-p123

Mysql>

INSERT into PROXYDB.A values (1000);

SELECT * from PROXYDB.A;

Build MySQL Cluster

Objective: To achieve single point of failure and data backup function

Host roles in the cluster:

Client Accessing Data

interface for SQL node users to access data

SQL Statements that perform user actions

(NDBD)   Data Node records in the storage table

Management Node MGM manages all hosts in the cluster

Mgm_1-- management level

Sql_10 sql_20-- providing access services to users

Ndbd_30 ndbd_40

-- storage point of data

providing data storage services using cluster software mysql-cluster

Management Process running on the administrative host

loads the configuration file when the process is started, determining the roles of each host in the cluster.

Config.ini

The MySQL process runs on the SQL node

The MySQL service is provided

load configuration file When starting process /ETC/MY.CNF

The NDBD process runs on the data node

load the configuration file when the process starts, set the storage location for the data

/etc/my.cnf

First, public configuration

1.install software that provides cluster functionality on all hosts

Service MySQL Stop

Mv/etc/my.cnf/etc/my.cnf.bak

Rpm-e--nodeps mysql-test mysql-sharedmysql-shared-compat mysql-embedded mysql-server MySQL-client MySQL-devel

Tar-xvfmysql-cluster-gpl-7.3.3-1.el6.x86_64.rpm-bundle.tar

RPM-UVH mysql-cluster-*.rpm

Second, configuration management host (192.168.1.1) running the management process

Vim/etc/config.ini-- configuration file is self-created, name is custom, but to end with INI

[NDBD Default] defining the public configuration of data nodes

[NDB_MGMD] define configuration parameters for the management node

[NDBD] defining configuration parameters for data nodes

[Mysqld] defining configuration parameters for SQL nodes

[Email protected] ~]# Cat/etc/config.ini

[NDBD Default]

noofreplicas=2

datamemory=80m

indexmemory=18m

[NDB_MGMD]

Nodeid=1

hostname=192.168.1.1

Datadir=/var/log/mysql-cluster--The file is built for your own creation, and below.

[NDBD]

Nodeid=30

hostname=192.168.1.30

Datadir=/var/lib/mysql-cluster/data

[NDBD]

Nodeid=40

hostname=192.168.1.40

Datadir=/var/lib/mysql-cluster/data

[Mysqld]

nodeid=10

hostname=192.168.1.10

[Mysqld]

Nodeid=20

hostname=192.168.1.20

[Email protected] ~]#

Third, configure the data node (192.168.1.30 192.168.1.40)

Run the ndbd process /etc/my.cnf

Mkdir-p/var/lib/mysql-cluster/data

Vim/etc/my.cnf

[Mysqld]

Datadir=/var/lib/mysql-cluster/data

ndb-connectstring=192.168.1.1-- specifying the management host

Ndbcluster

[Mysql_cluster]

ndb-connectstring=192.168.1.1

: Wq

iv. Configuring The SQL node (192.168.1.10 192.168.1.20)

running the MySQL database service /ETC/MY.CNF

Vim/etc/my.cnf

[Mysqld]

Ndbcluster

Default-storage-engine=ndbcluster

[Mysql_cluster]

ndb-connectstring=192.168.1.1

: Wq

Iv. Start-up service

1

[Email protected] ~]# iptables-f

-- shut down the firewall

[Email protected] ~]# Getenforce

-- View selinux status

Disabled

[Email protected] ~]#

2 start the management process on the management host

Ndb_mgmd-f/etc/config.ini

Netstat-untlap | grep:1186

Ndb_mgm

Ndb_mgm> Show

3 Start The data process on the NDBD node /etc/my.cnf

ndbd

Pgrep NDBD

Pkill-9 NDBD

4 start the mysqld process on the SQL node /etc/my.cnf

/var/lib/mysql/ Storage Table Structure directory

Service MySQL Restart

viewing the status of all hosts 192.168.1.1 the management host

Ndb_mgm> Show

Cluster Configuration

---------------------

[NDBD (NDB)] 2node (s)

Id=30 @192.168.1.30 (mysql-5.6.14 ndb-7.3.3, nodegroup:0, *)

Id=40 @192.168.1.40 (mysql-5.6.14 ndb-7.3.3, nodegroup:0)

[NDB_MGMD (MGM)] 1 node (s)

Id=1 @192.168.1.1 (mysql-5.6.14 ndb-7.3.3)

[Mysqld (API)] 2 node (s)

id=10 @192.168.1.10 (mysql-5.6.14 ndb-7.3.3)

Id=20 @192.168.1.20 (mysql-5.6.14 ndb-7.3.3)