使用mysql-proxy实现主从同步、读写分离
1.安装mysql
下载安装包
[root@localhost src]#wget http://mirrors.163.com/mysql/Downloads/MySQL-5.6/mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz
解压安装包
[root@localhost src]# tar zxvf mysql-5.6.39-linux-glibc2.12-x86_64.tar.gz
移动安装包
[root@localhost src]# mv mysql-5.6.39-linux-glibc2.12-x86_64 /usr/local/mysql
创建目录和用户
[root@localhost src]#mkdir /data/
[root@localhost src]#useradd mysql
进入mysql目录
[root@localhost src]# cd /usr/local/mysql/
安装mysql
[root@localhost mysql]#./scripts/mysql_install_db --user=mysql --datadir=/data/mysql
修改my.cnf文件
[root@localhost mysql]# vi /etc/my.cnf
拷贝配置文件
[root@localhost mysql]# cp support-files/mysql.server /etc/init.d/mysqld
修改启动文件中的datadir和basedir
[root@localhost mysql]#vi /etc/init.d/mysqld
创建开机启动项
[root@localhost mysql]#chkconfig --add mysqld
启动mysql
[root@localhost mysql]#/etc/init.d/mysqld start
mysql安装需要的一些安装包
[root@homework1 ~]# yum install -y perl
[root@homework1 ~]# yum install -y perl-XML-Dumper.noarch
[root@homework1 ~]# yum install -y autoconf
[root@homework1 ~]# yum install libaio* -y
2.设置mysql主从同步
准备2台虚拟机（最好不要在mysql安装后克隆）
配置主
修改/etc/mycnf 增加server-id和log_bin
[mysqld]
datadir=/data/mysql
socket=/tmp/mysql.sock
server-id=128
log_bin=xiaoqi
更改后重启mysql
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
查看/data/mysql目录下，发现生成了几个新文件
[root@localhost ~]# ll -t /data/mysql/
总用量 110640
-rw-rw----. 1 mysql mysql 50331648 7月 19 14:31 ib_logfile0
-rw-rw----. 1 mysql mysql 12582912 7月 19 14:31 ibdata1
-rw-rw----. 1 mysql mysql 22594 7月 19 14:31 localhost.localdomain.err
-rw-rw----. 1 mysql mysql 5 7月 19 14:31 localhost.localdomain.pid
-rw-rw----. 1 mysql mysql 16 7月 19 14:31 xiaoqi.index
-rw-rw----. 1 mysql mysql 120 7月 19 14:31 xiaoqi.000001
-rw-rw----. 1 mysql mysql 56 6月 26 10:54 auto.cnf
drwx------. 2 mysql mysql 4096 6月 26 10:36 mysql
drwx------. 2 mysql mysql 4096 6月 26 10:36 performance_schema
-rw-rw----. 1 mysql mysql 50331648 6月 26 10:36 ib_logfile1
drwx------. 2 mysql mysql 6 6月 26 10:36 test
准备数据用作演示；备份mysql库
[root@localhost ~]# mysqldump -uroot -p123456 mysql > /tmp/mysql.sql
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# du -sh /tmp/mysql.sql
644K /tmp/mysql.sql
创建一个test2库，把mysql备份的库恢复到test2里面
[root@localhost ~]# mysql -uroot -p123456 -e "create database test2"
Warning: Using a password on the command line interface can be insecure.
把之前备份的库恢复到test2里
[root@localhost ~]# mysql -uroot -p123456 test2 < /tmp/mysql.sql
Warning: Using a password on the command line interface can be insecure.
创建用户，用作同步数据。
[root@localhost ~]# mysql -uroot -p123456
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 5
Server version: 5.6.36-log MySQL Community Server (GPL)
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> grant replication slave on *.* to 'repl'@'192.168.246.78' identified by '123456';
Query OK, 0 rows affected (0.00 sec) 来源ip 密码
锁表操作，无法继续写入
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.27 sec)记住binlog的name和位置
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| xiaoqi.000001 | 2932484 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
配置从
修改/etc/my.cnf增加server-id 要和主不一样
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/tmp/mysql.sock
server-id=77
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
拷贝主上面备份的配置文件；新机器。。所以没有几个库
[root@localhost ~]# scp 192.168.246.111:/tmp/*.sql /tmp/The authenticity of host '192.168.246.99 (192.168.246.99)' can't be established.
ECDSA key fingerprint is 97:43:61:ff:59:85:a5:54:f4:96:f3:6c:b0:49:ec:7b.
Are you sure you want to continue connecting (yes/no)? y
Please type 'yes' or 'no': yes
Warning: Permanently added '192.168.246.99' (ECDSA) to the list of known hosts.
root@192.168.246.99's password:
Permission denied, please try again.
root@192.168.246.99's password:
mysql.sql 100% 774KB 773.7KB/s 00:00
[root@localhost ~]# ls /tmp/
mysql.sock
mysql.sql
systemd-private-7e3f84055419416499eb48ca78be4d87-vmtoolsd.service-aqhNZd
test3.sql
xiaoqi.sql
创建库
mysql> create database test2;
Query OK, 1 row affected (0.28 sec)
mysql> quit
Bye
[root@localhost ~]# mysql -uroot test3 < /tmp/test3.sql
[root@localhost ~]# mysql -uroot test2 < /tmp/mysql.sql
[root@localhost ~]# mysql -uroot xiaoqi < /tmp/xiaoqi.sql
查看主从mysql目录是否一致
在从上面登录mysql
stop slave
mysql> change master to master_host='192.168.246.111', master_user='repl', master_password='123456', master_log_file='xiaoqi.000001', master_log_pos=2932484;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
查看是否成功Slave_IO_Running和Slave_SQL_Running都是yes。
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.246.111
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: xiaoqi.000001
Read_Master_Log_Pos: 656543
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 280
Relay_Master_Log_File: xiaoqi.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 656543
Relay_Log_Space: 457
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 128
Master_UUID: 910b5eeb-99e2-11e8-88fc-000c29a93e96
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
到主机器上解锁表
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)克隆虚拟机容易有bug！！！在虚拟机上浪费2天时间。。
测试主从同步，在主机器上创建一个库test4.
mysql> create database test3;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test2 |
| test4 |
+--------------------+
6 rows in set (0.00 sec)
从机器上也自动生成了test3
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| test2 |
| test4 |
+--------------------+
6 rows in set (0.27 sec)
主从成功
3.用mysql-proxy实现读写分离
环境如下，两台已经实现主从同步的机器，和一台mysql-proxy机器。
mysql-proxy下载地址http://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-Proxy/
[root@localhost src]# ls
mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz nginx-1.8.0.tar.gz
mysql-proxy-0.8.4-linux-el6-x86-32bit.tar.gz php-5.6.32
mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit.tar.gz php-5.6.32.tar.bz2
nginx-1.8.0 安装配置mysql-proxy
解压并移动目录
[root@localhost src]# tar zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@localhost src]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
[root@localhost src]# cd !$
cd /usr/local/mysql-proxy
创建mysql-proxy用户，创建lua目录，log目录。
[root@localhost mysql-proxy]# useradd -r mysql-proxy
[root@localhost mysql-proxy]# ls
bin include lib libexec licenses share
[root@localhost mysql-proxy]# ls bin/
mysql-binlog-dump mysql-myisam-dump mysql-proxy
lua脚本所在位置
[root@localhost mysql-proxy]# ls share/doc/mysql-proxy/
active-queries.lua ro-balance.lua tutorial-resultset.lua
active-transactions.lua ro-pooling.lua tutorial-rewrite.lua
admin-sql.lua rw-splitting.lua tutorial-routing.lua
analyze-query.lua tutorial-basic.lua tutorial-scramble.lua
auditing.lua tutorial-constants.lua tutorial-states.lua
commit-obfuscator.lua tutorial-inject.lua tutorial-tokenize.lua
commit-obfuscator.msc tutorial-keepalive.lua tutorial-union.lua
COPYING tutorial-monitor.lua tutorial-warnings.lua
histogram.lua tutorial-packets.lua xtab.lua
load-multi.lua tutorial-prep-stmts.lua
README tutorial-query-time.lua
[root@localhost mysql-proxy]# mkdir log
[root@localhost mysql-proxy]# mkdir lua
复制读写和管理脚本到lua目录
[root@localhost mysql-proxy]# cp share/doc/mysql-proxy/rw-splitting.lua ./lua
[root@localhost mysql-proxy]# cp share/doc/mysql-proxy/admin-sql.lua ./lua
[root@localhost mysql-proxy]#
创建mysql-proxy配置文件 /etc/mysql-proxy.cnf 其中log选项可以注释掉。容易报错
[root@localhost mysql-proxy]# vi /etc/mysql-proxy.cnf
user=root #运行mysql-proxy用户
admin-username=xiaoqi #主从mysql共有的用户
admin-password=123456 #用户的密码
proxy-address=192.168.246.99:4040 #mysql-proxy运行ip和端口，不加端口，默认4040
proxy-read-only-backend-addresses=192.168.246.112 #指定后端从slave读取数据
proxy-backend-addresses=192.168.246.111 #指定后端主master写入数据
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定读写分离配置文件位置
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua #指定管理脚本
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log #日志位置
log-level=info #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
daemon=true #以守护进程方式运行
keepalive=true #mysql-proxy崩溃时，尝试重启
~ 修改配置文件权限
[root@localhost mysql-proxy]# chmod 660 !$
chmod 660 /etc/mysql-proxy.cnf
修改读写分离文件，数值改为1，方便实验
-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1, 什么时候开始读写分离
max_idle_connections = 1,
is_debug = false
}
配置环境变量
[root@localhost mysql-proxy]# export PATH=$PATH:/usr/local/mysql-proxy/bin/
查看帮助文档
[root@localhost mysql-proxy]# mysql-proxy --help
Usage:
mysql-proxy [OPTION...] - MySQL Proxy
Help Options:
-?, --help Show help options
--help-all Show all help options
--help-proxy Show options for the proxy-module
Application Options:
-V, --version Show version
--defaults-file=<file> configuration file
--verbose-shutdown Always log the exit code when shutting down
--daemon Start in daemon-mode
--user=<user> Run mysql-proxy as user
--basedir=<absolute path> Base directory to prepend to relative paths in the config
--pid-file=<file> PID file in case we are started as daemon
--plugin-dir=<path> path to the plugins
--plugins=<name> plugins to load
--log-level=(error|warning|info|message|debug) log all messages of level ... or higher
--log-file=<file> log all messages in a file
--log-use-syslog log all messages to syslog
--log-backtrace-on-crash try to invoke debugger on crash
--keepalive try to restart the proxy if it crashed
--max-open-files maximum number of open files (ulimit -n)
--event-threads number of event-handling threads (default: 1)
--lua-path=<...> set the LUA_PATH
--lua-cpath=<...> set the LUA_CPATH
--help-all ：获取全部帮助信息；
--proxy-address=host:port ：代理服务监听的地址和端口；
--admin-address=host:port ：管理模块监听的地址和端口；
--proxy-backend-addresses=host:port ：后端mysql服务器的地址和端口；
--proxy-read-only-backend-addresses=host:port ：后端只读mysql服务器的地址和端口；
--proxy-lua-script=file_name ：完成mysql代理功能的Lua脚本；
--daemon ：以守护进程模式启动mysql-proxy；
--keepalive ：在mysql-proxy崩溃时尝试重启之；
--log-file=/path/to/log_file_name ：日志文件名称；
--log-level=level ：日志级别；
--log-use-syslog ：基于syslog记录日志；
--plugins=plugin：在mysql-proxy启动时加载的插件；
--user=user_name ：运行mysql-proxy进程的用户；
--defaults-file=/path/to/conf_file_name ： 默认使用的配置文件路径；其配置段使用[mysql-proxy]标识；
--proxy-skip-profiling ： 禁用profile；
--pid-file=/path/to/pid_file_name ：进程文件名；
作者：Catke
链接：https://www.jianshu.com/p/552b1307dd22
來源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。启动mysql-proxy
[root@localhost mysql-proxy]# mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
[root@localhost mysql-proxy]# ps aux|grep proxy
root 3033 0.0 0.0 37912 876 ? S 15:02 0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
root 3123 0.0 0.0 37912 872 ? S 15:05 0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
root 3146 0.0 0.1 40036 1440 ? S 15:06 0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
root 3158 0.0 0.1 40036 1456 ? S 15:06 0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
root 3160 0.0 0.0 112720 972 pts/0 S+ 15:07 0:00 grep --color=auto proxy
查看监听端口
[root@localhost mysql-proxy]# netstat -lntp|grep proxy
tcp 0 0 192.168.246.99:3306 0.0.0.0:* LISTEN 3146/mysql-proxy
tcp 0 0 192.168.246.99:4040 0.0.0.0:* LISTEN 3158/mysql-proxy
连接mysql-proxy，进行读、写等操作，所有写请求都被分发到主库，所有读请求被分发到从库；
读写有延迟！！！
实际实验很乱。。不熟悉操作方式，浪费太多时间。mysql-proxy使用好像有bug。。很多次才启动成功。也可能是没配置好、
失败操作流程
mysql-proxy提供SysV服务脚本（来源google）
[root@localhost 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/init.d/functions
prog="/usr/local/mysql-proxy/bin/mysql-proxy"
# Source networking configuration.
if [ -f /etc/sysconfig/network ]; then
. /etc/sysconfig/network
fi
# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0
# Set default mysql-proxy configuration.
ADMIN_USER="admin"
ADMIN_PASSWD="admin"
ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
PROXY_OPTIONS="--daemon"
PROXY_PID=/var/run/mysql-proxy.pid
PROXY_USER="mysql-proxy"
# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ]; then
. /etc/sysconfig/mysql-proxy
fi
RETVAL=0
start() {
echo -n $"Starting $prog: "
daemon $prog $PROXY_OPTIONS --pid-file=$PROXY_PID --proxy-address="$PROXY_ADDRESS" --user=$PROXY_USER --admin-username="$ADMIN_USER" --admin-lua-script="$ADMIN_LUA_SCRIPT" --admin-password="$ADMIN_PASSWORD"
RETVAL=$?
echo
if [ $RETVAL -eq 0 ]; then
touch /var/lock/subsys/mysql-proxy
fi
}
stop() {
echo -n $"Stopping $prog: "
killproc -p $PROXY_PID -d 3 $prog
RETVAL=$?
echo
if [ $RETVAL -eq 0 ]; then
rm -f /var/lock/subsys/mysql-proxy
rm -f $PROXY_PID
fi
}
# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
stop
start
;;
condrestart|try-restart)
if status -p $PROXY_PIDFILE $prog >&/dev/null; then
stop
start
fi
;;
status)
status -p $PROXY_PID $prog
;;
*)
echo "Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}"
RETVAL=1
;;
esac
exit $RETVAL修改权限已经增加开机启动
[root@localhost mysql-proxy]# chmod +x /etc/init.d/mysql-proxy
[root@localhost mysql-proxy]# chkconfig --add mysql-proxy
为服务脚本提供配置文件/etc/sysconfig/mysql-proxy 内容如下（来自google）
[root@localhost mysql-proxy]# vi /etc/sysconfig/mysql-proxy
# Options for mysql-proxy
ADMIN_USER="admin" #mysql-proxy的管理账号
ADMIN_PASSWORD="admin"
ADMIN_ADDRESS=""
ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua" #完成mysql-proxy管理功能的脚本
PROXY_ADDRESS=""
PROXY_USER="mysql-proxy" #以什么身份运行mysql-proxy
PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=192.168.246.111:3306 --proxy-read-only-backend-addresses=192.168.246.112:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"编辑admin.lua 内容如下
[root@localhost mysql-proxy]# vi share/doc/mysql-proxy/admin.lua
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
resultset = {
fields = fields,
rows = rows
}
}
return proxy.PROXY_SEND_RESULT
end
mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.246.111:3306" --proxy-read-only-backend-addresses="192.168.246.112: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"
/usr/local/mysql-proxy/bin/mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.246.111:3306" --proxy-read-only-backend-addresses="192.168.246.112: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"
扫一扫
503
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
