MySQL-5.6+MySQL-Proxy构建主从复制与读写分离 - 数据库编程

proxy]# netstat -antup|grep 4040

tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 1326/mysql-proxy

6）客户端测试连接[Proxy IP 地址]

[root@slave ~]# mysql -uuser -p -h192.168.1.12 --port=4040

Enter password:

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

Your MySQL connection id is 5

Server version: 5.5.25-log Source distribution

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> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

| user |

+--------------------+

7）设置读写分离

[root@Proxy mysql-proxy]# killall mysql-proxy

[root@Proxy mysql-proxy]# netstat -antup |grep 4040

[root@Proxy mysql-proxy]# cd /usr/local/mysql-proxy

[root@Proxy mysql-proxy]# cp lib/mysql-proxy/lua/admin.lua share/doc/mysql-proxy/

——启动mysql-proxy

[root@Proxy mysql-proxy]# mysql-proxy --daemon --log-level=debug \

--log-file=/var/log/mysql-proxy.log --plugins="proxy" \

--proxy-backend-addresses="192.168.1.10:3306"

--proxy-read-only-backend-addresses="192.168.1.11:3306" \

--proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" \

--plugins=admin --admin-username="admin" \

--admin-password="admin" \

--admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

8）启动mysql-proxy

[root@Proxy mysql-proxy]# mysql-proxy --daemon --log-level=debug \

--log-file=/var/log/mysql-proxy.log --plugins="proxy" \

--proxy-backend-addresses="192.168.1.252:3306"

--proxy-read-only-backend-addresses="192.168.1.254:3306" \

--proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" \

--plugins=admin --admin-username="admin" \

--admin-password="admin" \

--admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

9）测试读写分离状态

客户端执行

[root@node1 /]# mysql -uuser -p12345 -h192.168.1.10 --port=4040 -e "select user from mysql.user;"

[root@node2 /]# mysql -uuser -p12345 -h192.168.1.11 --port=4040 -e "create database user1;"

10）优化配置启动脚本，并修连接端口

[root@Proxy mysql-proxy]# vi /etc/sysconfig/mysql-proxy

ADMIN_USER="admin"

ADMIN_PASSWORD="admin"

ADMIN_ADDRESS=""

ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

PROXY_ADDRESS="0.0.0.0:3306"

PROXY_USER="mysql-proxy"

PROXY_OPTIONS="--daemon --log-level=info --log-file="/var/log/mysql-proxy.log"

--plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.1.12:3306

--proxy-read-only-backend-addresses=192.168.1.13:3306

--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

11）添加启动脚本

[root@Proxy mysql-proxy]# vi /etc/init.d/mysql-proxy

#!/bin/bash

# mysql-proxy This script starts and stops the mysql-proxy daemon

# chkconfig: - 78 30

# processname: mysql-proxy

# description: mysql-proxy is a proxy daemon for mysql

# Source function library.

. /etc/rc.d/init.d/functions

prog="/usr/local/mysql-proxy/bin/mysql-proxy"

# Source networking configuration.

if [ -f /etc/sysconfig/network ]; then

. /etc/sysconfig/network

# Check that networking is up.

[ ${

MySQL-5.6+MySQL-Proxy构建主从复制与读写分离(六)