开发者社区> 技术小阿哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

IV 9 MySQL REPLICATION(rw-splitting)

简介:
+关注继续查看

一、相关概念

MySQL REPLICATION(rw-splitting)

mysql5.6引入GTIDmulti thread replication

GTIDglobal transaction identifierare unique identifiers comprising the server UUIDuniversal unique identifier128bit随机符)of the original master and a transaction numberGTID=serverUUID + transaction number

they are automatically generated as aheader for every transaction and written with the transaction to the binary log(在二进制日志中,每一个事务相关语句中都会有GTID号)

GTID make it simple to track and compare replicated transactions between the master and slaves,which in turn enables simple recovery from failures of the masterGTID使得主和从之间比较和追踪复制事务变得简单,而且能从崩溃中快速恢复)

the default InnoDB storage engine must beused with GTID to get the full benefits of HAInnoDB存储引擎实现高可用时必须借助GTID来实现)

 

如图:multi thread replicationmysql中一个库对应一个线程,多线程复制若打开,会在从端启动多个sql_thread,只有在同时复制多个库时才有意义,选项为slave-parallel-workers=NUMBER,从的并行线程数,NUMBER应小于或等于当前数据库的个数,0表示禁用)

wKiom1Z82sDQpnHfAAAz6XZsJNk722.jpg

 

 

ABC三个DB-serverAmasterBC均为slave,要实现高可用HA功能,若A挂掉,则要将BC提升为主(如果将B提升为主,那C要修改它的主为B,之后要从B那复制数据)由于复制默认是异步,BC都有可能落后A,那BC哪个快,一般要将快的那个提升为主(若找B做主,那B上要包含C的全部数据,要求从上的数据不能与主不一致),但就算以快的那个为主,未必C执行过的B都执行过(如A上执行了10个事务,B完成了919C完成了3810C完成的第10B未完成),这样在之前没有GTID时,无法追踪每一个事务,若提升B为主,就要将C的的数据复制到B,再把B提升为主;有了GTID后,每一个事务在写入二进制日志时在事件上明确标明是哪个事务的GTID号(事务的编号),在提升BC时,BC比较对方已完成哪些事务,CB未完成的传给BC再成为B的从,它们将自己协商事务的执行情况,并能快速使DB从崩溃中恢复回来;另使用GTID时,在从上与主连接时,不再使用master_log_filemaster_log_pos

 


提供简明的复制工具utilities for simplifying replicationhttps://launchpad.net/mysql-utilities,要求python2.7,分别用于replicationcheckshowha)常用的有:

#mysqlreplicate(通常用于启动复制进程;若在从端执行过的事务会跳过;也可用于检查存储引擎;enables fast and simple introduction of replication slaves,the mysql replicate utility is used to start the replication processany GTIDs that have already been executed on the slave will be skippedthe utilityalso checks storage engine compatibility


#mysqlrplcheck(检查复制环境是否满足要求,provides simple verification of deployment and fast fault resolutionchecks that the binlog is enabled and displays any configured exceptionschecks slave access and privileges to master and slave connection status


#mysqlrplshowdiscovers and displays the replication topology on demandshows slaves attached to each master and labels each slave with hostname and port number


#mysqlfailover(手动或自动提升一个从为主,enables automatic or manual failover to a slave in the event of an outage to the master


#mysqlrpladmin(手动让master下线,将指定的从提升为主,或加入一个新node使其成为主,if a user needs to take a master offline for scheduled maintenance,mysqlrpladmin can perform a switchover to a specific slave(called the new master)

 


mysql5.6最好在redhat6上使用,若要在5上用要升级并安装很多软件(如python2.4-->python2.7lua等)

 

wKioL1Z82uLDqyItAABTL6-EQJo259.jpg

此种架构在MySQL第七篇中已有说明

mysql-proxy应理解前端用户或app所要执行的语句,若是写路由到master,若是读路由到slave,响应读请求时先缓存至memcached,再响应至前端,memcached是个旁路的缓存(旁路服务器,它本身只是个编程API,前端查询memcached中没有时,得前端自个去找后端slave,经director到达slave,而不是由memcached去找后端slave

注:有些缓存是自己去找后端server,缓存后再返回给前端,而memcached不是

 

实现rw-splitting的应用程序有:mysql-proxyamoebacobar(主要是分库用)

mysql-proxyhttp://downloads.mysql.com/archives/proxy/,它相当于一个轻量级的mysql,存储引擎采用warmhole虫洞(数据的存储格式就是一段lua脚本),mysql-proxy本身并不负责读写分离,而是基于插件化的方式内置了lua引擎,它能调用lua脚本写的配置程序,真正实现rw-splitting的是lua脚本(lua脚本实现query分析、读写分离、LBHA),要想使用mysql-proxy要具备lua脚本的编程能力,mysql-proxy严重依赖脚本,mysql-proxy本身提供了调用特定脚本实现某些功能的框架,lua脚本是嵌入式脚本,现在很多应用程序都嵌入lua脚本实现二次开发功能(强大的可配置功能),如很多网游服务器都内置了lua引擎,很多配置都通过lua脚本实现)

mysql官方认为mysql-proxy不稳定,至今还在13年的0.8.5版本(ftp://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-Proxy/),很多企业已在用但要不停打补丁

amoebajava编写,参考mysql-proxy的实现,提供查询路由、查询分析、查询过滤、读写分离、LBHA,不像mysql-proxy的配置那样严重依赖lua脚本,只需修改*.xml文件(说明master,slave的相关信息,是否分库等)就能实现

cobarjava编写,主要实现分库,建立在amoeba基础上的另一个项目)

 

 

二、操作:

三个node

master:192.168.41.135node1

slave:192.168.41.136node2

proxy-node:192.168.41.134node3

安装包:

mysql-5.6.28-linux-glibc2.5-x86_64.tar.gznode{1,2}

mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gznode3

环境:

[root@node1 ~]# uname -a

Linux node1.magedu.com 2.6.32-358.el6.x86_64#1 SMP Tue Jan 29 11:47:41 EST 2013 x86_64 x86_64 x86_64 GNU/Linux

 

1、  创建逻辑卷,准备数据目录;创建mysql用户;node{1,2}分别安装mysql5.6

node{1,2}-side

[root@node1 ~]# fdisk -l

Disk /dev/sdb: 10.7 GB, 10737418240 bytes

[root@node1 ~]# pvcreate /dev/sdb

 Physical volume "/dev/sdb" successfully created

[root@node1 ~]# vgcreate myvg /dev/sdb

 Volume group "myvg" successfully created

[root@node1 ~]# lvcreate -L 8G -n mylv/dev/myvg

 Logical volume "mylv" created

[root@node1 ~]# lvs

……

 mylv    myvg     -wi-ao--- 8.00g  

[root@node1 ~]# mkfs.ext4 /dev/myvg/mylv

[root@node1 ~]# mkdir /mydata

[root@node1 ~]# mount -t ext4/dev/myvg/mylv /mydata

[root@node1 ~]# ls /mydata

lost+found

[root@node1 ~]# vim /etc/fstab

/dev/myvg/mylv  /mydata         ext4   defaults    0 0

[root@node1 ~]# umount /mydata

[root@node1 ~]# mount -a

[root@node1 ~]# mount(查看是否有以下此行)

/dev/mapper/myvg-mylv on /mydata type ext4(rw)

[root@node1 ~]# useradd -r mysql

[root@node1 ~]# id mysql

uid=498(mysql) gid=498(mysql) =498(mysql)

[root@node1 ~]# chown -R mysql.mysql/mydata/data/

[root@node1 ~]# ll -d /mydata/data

drwxr-xr-x 2 mysql mysql 4096 8  31 18:53 /mydata/data

 

[root@node1 ~]# tar xvf mysql-5.6.28-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

[root@node1 ~]# cd /usr/local/

[root@node1 local]# ln -sv mysql-5.6.28-linux-glibc2.5-x86_64/ mysql

"mysql" ->"mysql-5.6.28-linux-glibc2.5-x86_64/"

[root@node1 local]# cd mysql

[root@node1 mysql]# chown -R root.mysql ./

[root@node1 mysql]#scripts/mysql_install_db --user=mysql --datadir=/mydata/data

[root@node1 mysql]# vim my.cnf5.6初始化后在此目录下就有配置文件,不用将其复制到/etc/下,不过这个文件里是空的,简单编辑下先让服务能启动起来)

[mysqld]

datadir = /mydata/data

innodb_file_per_table = 1

server-id = 1 (从端要设为其它数字,不能与主相同)

socket = /tmp/mysql.sock

log-bin = mysql-bin

[root@node1 mysql]# vim /etc/profile.d/mysql.sh

export PATH=$PATH:/usr/local/mysql/bin

[root@node1 mysql]# . !$

. /etc/profile.d/mysql.sh

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

[root@node1 mysql]# chkconfig --add mysqld

[root@node1 mysql]# chkconfig --list mysqld

mysqld            0:关闭      1:关闭      2:启用      3:启用      4:启用      5:启用      6:关闭

[root@node1 mysql]# ls /mydata/data(注意{ib_logfile0,ib_logfile1}InnoDB的事务日志,这两个文件在mysql5.5.X5M,而在5.6.X48M

ibdata1 ib_logfile0  ib_logfile1  mysql performance_schema  test

[root@node1 mysql]# service mysqld start

Starting MySQL.......                                      [确定]

 

2、  编辑node{1,2}mysql的配置文件,使之可以完成主从复制

node1-side

[root@node1 mysql]# vim my.cnf

log-bin=mysql-bin

server-id=1

datadir=/mydata/data

socket=/tmp/mysql.sock

binlog-format=ROW(官方建议使用这种格式)

gtid-mode=on(启用GTID功能)

enforce-gtid-consistency=1(强制GTID一致性,与记录到二进制日志中的特殊语句有关,如创建临时表等)

report-host=192.168.41.135(每个从在连入主时,必须要告诉master自己是谁,这样在主端用>SHOW SLAVE HOSTS;查看所有与该主机连入的从)

report-port=3306

port=3306

log-slave-updates=1(从是否将写操作记录至本地的二进制日志中,在6上有GTID为保证复制安全此项开启)

slave-parallel-workers=2(启动多线程复制,sql_thread,该值应小于或等于当前数据库的个数)

sync-master-info=1(从端任何一个事务重放replay完要将事件位置和文件名记录到master.info这个文件中,以便下次启动可从该文件处直接获取,确保无信息丢失)

master-info-repository=TABLE(主记录从的相关信息,两种方式TABLEFILE,若用FILE则文件名为master.info,若使用TABLE记录更可靠位置mysql.slave_master_infoDATABASE.TABLE

relay-log-info-repository=TABLE(从记录主的相关信息)

binlog-checksum=CRC32(这三项,启用复制有关的校验功能,用于保证即使服务器故障后,再次启动仍尽可能读到可用数据)

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1(可选,用于二进制日志记录事件更详细信息,可降低故障排队的复杂度,注意若记录信息量大的话会占用磁盘空间的)

[root@node1 mysql]# service mysqld restart

Shutting down MySQL..                                      [确定]

Starting MySQL......                                       [确定]

[root@node1 mysql]# mysql

mysql> SHOW GLOBAL VARIABLES LIKE 'server_uuid';

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

| Variable_name | Value                                |

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

| server_uuid   | 551d1882-a87a-11e5-9824-000c292aaf97 |

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

1 row in set (0.00 sec)

mysql> SHOW MASTER STATUS;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB| Executed_Gtid_Set |

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

| mysql-bin.000001 |      151 |              |                  |                   |

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

1 row in set (0.00 sec)

 

node2-side:(仅改了server-idreport-host

log-bin=mysql-bin

server-id=11

datadir=/mydata/data

socket=/tmp/mysql.sock

binlog-format=ROW

gtid-mode=on

enforce-gtid-consistency=1

report-host=192.168.41.136

report-port=3306

port=3306

log-slave-updates=1

slave-parallel-workers=2

sync-master-info=1

master-info-repository=TABLE

relay-log-info-repository=TABLE

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

[root@node2 mysql]# service mysqld restart

Shutting down MySQL..                                      [确定]

Starting MySQL......                                       [确定]

[root@node2 mysql]# mysql

mysql> SHOW GLOBAL VARIABLES LIKE'server_uuid';

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

| Variable_name | Value                                |

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

| server_uuid   | 1fc81179-a87c-11e5-982f-000c29f6b139 |

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

1 row in set (0.08 sec)

 

 

3、   master上授权;在slave上建立与master的连接;在master创建库在slave中查看有没(验证主从复制是否成功)

node1-side

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.41.%' IDENTIFIED BY 'replpass';

Query OK, 0 rows affected (0.15 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.02 sec)

 

node2-side

mysql> CHANGE MASTER TO MASTER_HOST='192.168.41.135',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_AUTO_POSITION=1;

Query OK, 0 rows affected, 2 warnings (0.35sec)

mysql> START SLAVE;

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

mysql> SHOW SLAVE STATUS\G

*************************** 1. row***************************

               Slave_IO_State: Waiting formaster to send event

                  Master_Host: 192.168.41.135

                  Master_User: repluser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000002

         Read_Master_Log_Pos: 191

               Relay_Log_File:node2-relay-bin.000003

                Relay_Log_Pos: 401

       Relay_Master_Log_File: mysql-bin.000002

            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: 191

              Relay_Log_Space: 1203

              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: 1

                  Master_UUID:551d1882-a87a-11e5-9824-000c292aaf97

            Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

         SQL_Remaining_Delay: NULL

     Slave_SQL_Running_State: Slave has read all relay log; waiting for theslave 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: 551d1882-a87a-11e5-9824-000c292aaf97:1-2

           Executed_Gtid_Set: 551d1882-a87a-11e5-9824-000c292aaf97:1-2

                Auto_Position: 1

1 row in set (0.05 sec)

 

node1-side

mysql> CREATE DATABASE mydb;

Query OK, 1 row affected (0.09 sec)

 

node2-side

mysql> SHOW DATABASES;

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

| Database           |

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

| information_schema |

| mydb               |

| mysql              |

| performance_schema |

| test               |

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

5 rows in set (0.11 sec)

 

node1-side

mysql> show global variables like 'gtid_executed';1-4表示当前已有四个事务执行完,可在master slave分别查看)

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

| Variable_name | Value                                    |

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

| gtid_executed |551d1882-a87a-11e5-9824-000c292aaf97:1-4 |

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

1 row in set (0.00 sec)

mysql> SHOW SLAVE HOSTS;(查看当前建立连接的从)

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

| Server_id | Host           | Port | Master_id | Slave_UUID                           |

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

|       11 | 192.168.41.136 | 3306 |        1 | 1fc81179-a87c-11e5-982f-000c29f6b139 |

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

1 row in set (0.00 sec)

 

 

4master--slave半同步复制:

node1-side

[root@node1 ~]# mysql

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

Query OK, 0 rows affected (0.26 sec)

mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;

Query OK, 0 rows affected (0.03 sec)

mysql> SHOW GLOBAL VARIABLES like 'rpl%';

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

| Variable_name                      | Value    |

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

| rpl_semi_sync_master_enabled       | ON       |

| rpl_semi_sync_master_timeout       | 10000    |

| rpl_semi_sync_master_trace_level   | 32      |

| rpl_semi_sync_master_wait_no_slave |ON       |

| rpl_stop_slave_timeout             | 31536000 |

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

5 rows in set (0.00 sec)

 

注:可将主从端enabled的选项各自写入配置文件,下次重启服务即可生效

 

node2-side

[root@node2 ~]# mysql

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

Query OK, 0 rows affected (0.31 sec)

mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;

Query OK, 0 rows affected (0.09 sec)

mysql> SHOW GLOBAL VARIABLES like 'rpl%';

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

| Variable_name                   | Value    |

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

| rpl_semi_sync_slave_enabled     | ON      |

| rpl_semi_sync_slave_trace_level | 32       |

| rpl_stop_slave_timeout          | 31536000 |

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

3 rows in set (0.14 sec)

mysql> STOP SLAVE io_thread;

Query OK, 0 rows affected (0.14 sec)

mysql> START SLAVE io_thread;

Query OK, 0 rows affected (0.03 sec)

 

node1-side

mysql> CREATE DATABASE testdb;

Query OK, 1 row affected (0.00 sec)

mysql> USE testdb;

Database changed

mysql> CREATE TABLE testtb(id int);

Query OK, 0 rows affected (0.24 sec)

mysql> SHOW STATUS LIKE 'rpl_semi_sync_master_yes_tx';

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

| Variable_name               | Value |

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

| Rpl_semi_sync_master_yes_tx | 4     |

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

1 row in set (0.00 sec)

 

 

5、在proxy-node安装mysql-proxy,并在命令行下启动mysql-proxy

node3-side

[root@node3 ~]# useradd -r mysql-proxy

[root@node3 ~]# id mysql-proxy

uid=498(mysql-proxy) gid=498(mysql-proxy) =498(mysql-proxy)

[root@node3 ~]# tar xvf mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz -C /usr/local/

[root@node3 ~]# cd /usr/local/

[root@node3 local]# ln -sv mysql-proxy-0.8.4-linux-el6-x86-64bit/ mysql-proxy

"mysql-proxy" ->"mysql-proxy-0.8.4-linux-el6-x86-64bit/"

[root@node3 local]# cd mysql-proxy

[root@node3 mysql-proxy]# ls

bin include  lib  libexec licenses  share

[root@node3 mysql-proxy]# ls bin

mysql-binlog-dump  mysql-myisam-dump  mysql-proxy

[root@node3 mysql-proxy]# ls share/doc/mysql-proxy/(此目录下有可用的lua脚本,如rw-splitting.lua

[root@node3 mysql-proxy]# vim /etc/profile.d/mysql-proxy.sh

[root@node3 mysql-proxy]# . !$

. /etc/profile.d/mysql-proxy.sh

 

[root@node3 mysql-proxy]# cd

[root@node3 ~]# mysql-proxy  --help-all

OPTIONS(有proxy-moduleapplicationoptions):

proxy-module

--proxy-address=HOST:PORT-Plistening address:port of the proxy-server (default: :4040)代理服务器地址及端口)

--proxy-read-only-backend-addresses=HOST:PORT-raddress:port of the remote slave-server (default: not set)指定后端哪个server只读)

--proxy-backend-addresses=HOST:PORT-baddress:port of the remote backend-servers (default: 127.0.0.1:3306)指定后端哪个server可读写)

--proxy-lua-script=/PATH/TO/FILE-sfilename ofthe lua script (default: not set)指定lua脚本位置,/usr/local/mysql-proxy/share/doc/mysql-proxy/

--proxy-connect-timeoutconnect timeout in seconds (default: 2.0 seconds)

--proxy-read-timeoutread timeout in seconds (default: 8 hours)

--proxy-write-timeoutwrite timeout in seconds (default: 8 hours)

application options

--defaults-file=/PATH/TO/FILEconfiguration file,可指定默认配置文件,仅读取配置文件中[mysql-proxy]这段,这些OPTIONS都可写到此配置文件中,不加--前缀)

--daemonStart in daemon-mode,以守护进程方式运行,否则会运行在前台)

--plugin-dir=/PATHpath to the plugins,加载某一个目录下所有插件)

--plugins=NAMEplugins toload,加载默认目录下某一个插件,默认路径/usr/local/mysql-proxy/lib/mysql-proxy/plugins,常用的有libadmin.so(远程管理接口,可远程连接至mysql-proxy查看当前后端服务器状况)和libproxy.so,使用时这样写--plugins=admin

--log-level=error|warning|info|messages|debug

--log-file=/PATH/TO/LOGFILElog all messages in a file,使用指定的日志文件记录)

--log-use-sysloglog all messages to syslog,使用系统syslog方式记录,通常放在/var/log/messages中,这两种指定一种即可)

--keepalivetry to restart theproxy if it crashed,若mysql-proxy服务崩溃试图重启)

--max-open-filesmaximum number of open files (ulimit -n),默认1024

--event-threadsnumber of event-handling threads (default: 1)

--lua-path=/PATHset the LUA_PATH,若是rpm包安装不用指默认会找到)

 

[root@node3 ~]# cd /usr/local/mysql-proxy/share/doc/mysql-proxy/

[root@node3 mysql-proxy]# vim rw-splitting.lua(修改以下配置min_idle_connections = ,默认是4max_idle_connections= ,默认8mysql-proxy会检测客户端连接,当没有达到该值时不会读写分离,也就是读操作还在master上,所以改掉马上能看出效果,测试完再改回来默认值,若日志中有报错[network-mysqld.c:1134]: error on a connection (fd: -1 event: 0).closing client connection.可将这两个值调大)


if not proxy.global.config.rwsplit then

   proxy.global.config.rwsplit = {

        min_idle_connections = 1,

        max_idle_connections = 2,

       is_debug = false

   } 

end 


[root@node3 ~]# mysql-proxy --daemon  --log-level=debug  --log-file=/var/log/mysql-proxy.log  --plugins=proxy  --proxy-backend-addresses=192.168.41.135:3306  --proxy-read-only-backend-addresses=192.168.41.136: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.luaaddresses的两个选项,address后面加es,刚没加报错mysql-proxy-cli.c:505:Unknown option;此处的admin.lua脚本需自己添加,安装包里没提供,可在该版本的源码包中提取,内容见下文附录中)

 [root@node3~]# tail /var/log/mysql-proxy.log

2015-11-01 08:04:51: (critical) pluginproxy 0.8.4 started

2015-11-01 08:04:51: (critical) pluginadmin 0.8.4 started

2015-11-01 08:04:51: (debug) max openfile-descriptors = 1024

2015-11-01 08:04:51: (message) proxylistening on port :4040

2015-11-01 08:04:51: (message) addedread/write backend: 192.168.41.135:3306

2015-11-01 08:04:51: (message) addedread-only backend: 192.168.41.136:3306

[root@node3 ~]# netstat -tnlp | grep mysql-proxy

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

tcp       0      0 0.0.0.0:4041                0.0.0.0:*                   LISTEN      4875/mysql-proxy   

 

 

6、测试:

node1-side:(在master/slave上授权帐号,用于mysql-proxy与主从联系,有了mysql-proxy所有SQL语句都经它转发,所以要能连接上后端的主从,主从复制已开启,只在node1上授权即可,在node2查看是否有)

mysql> GRANT ALL ON *.* TO 'root'@'192.168.41.%' IDENTIFIED BY 'root' WITH GRANT OPTION;

Query OK, 0 rows affected (0.17 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.02 sec)

 

node2-side

mysql> SELECT User,Host,Password FROM mysql.user;

……

| root    | 192.168.41.%     |*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |

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

8 rows in set (0.00 sec)

 

node1-side:(在主端创建表并插入数据,这时在主从表上查询数据这是一样的,之后关闭复制功能通过代理再次插入数据,分别在主从上查询,若在主上能查到,通过代理在从上查不到则读写已分离)

mysql> USE mydb;

Database changed

mysql> CREATE TABLE testtb (id int,namechar(10));

Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO testtb VALUES(1,'magedu');

Query OK, 1 row affected (0.07 sec)

 

node2-side

mysql> SELECT * FROM testtb;

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

| id  | name   |

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

|   1 | magedu |

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

1 row in set (0.00 sec)

mysql> STOP SLAVE;

Query OK, 0 rows affected (0.05 sec)

mysql> \q

[root@node2 ~]# mysql -uroot -proot -h192.168.41.134 --port=4040

mysql> USE mydb;

mysql> INSERT INTO testtb VALUES(2,'jowin');

Query OK, 1 row affected (0.05 sec)

 

node1-side

mysql> SELECT * FROM mydb.testtb;

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

| id  | name   |

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

|   1 | magedu |

|   2 | jowin  |

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

2 rows in set (0.00 sec)

 

node2-side:(至少要再开两个客户端用于连接mysql-proxy,第一次连接查询到的是主库,从第二个开始读才分离到slave上)

[root@node2 ~]# mysql -uroot -proot -h192.168.41.134 --port=4040

mysql> SELECT * FROM mydb.testtb;

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

| id  | name   |

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

|   1 | magedu |

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

1 row in set (0.01 sec)

 

[root@node2 ~]# mysql -uadmin -padmin  -h192.168.41.134 --port=4041(连接管理接口查看状态,刚开始状态为unknown,在通过proxy插入数据或查询数据只要两个主机有连接成功,此处状态就变为UP

mysql> select * from backends;

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

| backend_ndx | address             | state | type | uuid |connected_clients |

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

|          1 | 192.168.41.135:3306 | up    |rw   | NULL |                 0 |

|          2 | 192.168.41.136:3306 | up    |ro   | NULL |                 0 |

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

2 rows in set (0.01 sec)

 

 

7、制作启动服务脚本、程序配置文件、服务脚本配置文件

node3-side

[root@node3 ~]# vim /etc/init.d/mysql-proxy

-----------------script start------------------

#!/bin/bash

#

# mysql-proxy This script starts and stopsthe mysql-proxy daemon

#

# chkconfig: - 78 30

# processname: mysql-proxy

# description: mysql-proxy is a proxydaemon 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

fi

 

# Check that networking is up.

[ ${NETWORKING} = "no" ]&& exit 0

 

# Set default mysql-proxy configuration.

PROXY_PID=/var/run/mysql-proxy.pid

 

# 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

------------------------script end----------------

[root@node3 ~]# chmod +x /etc/init.d/mysql-proxy

[root@node3 ~]# ll !$

ll /etc/init.d/mysql-proxy

-rwxr-xr-x 1 root root 2618 11  1 11:25 /etc/init.d/mysql-proxy

 

[root@node3 ~]# vim /etc/sysconfig/mysql-proxy

# Options for 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=debug  --log-file=/var/log/mysql-proxy.log  --plugins=proxy  --proxy-backend-addresses=192.168.41.135:3306  --proxy-read-only-backend-addresses=192.168.41.136: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"


[root@node3 ~]# chkconfig --add mysql-proxy

[root@node3 ~]# chkconfig mysql-proxy on

[root@node3 ~]# chkconfig --list mysql-proxy

mysql-proxy         0:关闭      1:关闭      2:启用      3:启用      4:启用      5:启用      6:关闭

[root@node3 ~]# service mysql-proxy status

mysql-proxy (pid  5407) 正在运行...

 

 

[root@node3 ~]# vim /etc/mysql-proxy.cnf(注意此配置文件权限660,否则服务无法启动,有配置文件了就可在命令行下用选项#mysql-proxy --defaults-file=/etc/mysql-proxy.cnf来启动,后面就不用跟太多选项,若用rpm方式安装此文件会提供,但安装好后无rw-splitting.lua这个脚本文件,可以通过在二进制安装包里提取此脚本文件)

[mysql-proxy]

daemon=true

keepalive=true

log-level=debug

log-file=/var/log/mysql-proxy.logproxy-address=0.0.0.0:3306

plugins=proxy

proxy-backend-addresses=192.168.41.135:3306

proxy-read-only-backend-addresses=192.168.41.136: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

 

 

附:admin.lua脚本内容(通用二进制包里无此脚本,最好在官网下个同版本的源码包,提取出此脚本文件)

[root@node3 ~]# vim  /usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua

----------------script start-------------------

function set_error(errmsg)

         proxy.response = {

                   type = proxy.MYSQLD_PACKET_ERR,

                   errmsg = errmsg or "error"

         }

end

 

function read_query(packet)

         ifpacket:byte() ~= proxy.COM_QUERY then

                   set_error("[admin]we only handle text-based queries (COM_QUERY)")

                   returnproxy.PROXY_SEND_RESULT

         end

 

         localquery = packet:sub(2)

 

         localrows = { }

         localfields = { }

 

         ifquery:lower() == "select * from backends" then

                   fields= {

                            {name = "backend_ndx",

                              type = proxy.MYSQL_TYPE_LONG },

 

                            {name = "address",

                              type = proxy.MYSQL_TYPE_STRING },

                            {name = "state",

                              type = proxy.MYSQL_TYPE_STRING },

                            {name = "type",

                              type = proxy.MYSQL_TYPE_STRING },

                            {name = "uuid",

                              type = proxy.MYSQL_TYPE_STRING },

                            {name = "connected_clients",

                              type = proxy.MYSQL_TYPE_LONG },

                   }

 

                   fori = 1, #proxy.global.backends do

                            localstates = {

                                     "unknown",

                                     "up",

                                     "down"

                            }

                            localtypes = {

                                     "unknown",

                                     "rw",

                                     "ro"

                            }

                            localb = proxy.global.backends[i]

 

                            rows[#rows+ 1] = {

                                     i,

                                     b.dst.name,          -- configured backend address

                                     states[b.state+ 1], -- the C-id is pushed down starting at 0

                                     types[b.type+ 1],   -- the C-id is pushed downstarting at 0

                                     b.uuid,              -- the MySQL Server's UUID if itis managed

                                     b.connected_clients  -- currently connected clients

                            }

                   end

         elseifquery:lower() == "select * from help" then

                   fields= {

                            {name = "command",

                              type = proxy.MYSQL_TYPE_STRING },

                            {name = "description",

                              type = proxy.MYSQL_TYPE_STRING },

                   }

                   rows[#rows+ 1] = { "SELECT * FROM help", "shows this help" }

                   rows[#rows+ 1] = { "SELECT * FROM backends", "lists the backends and theirstate" }

         else

                   set_error("use'SELECT * FROM help' to see the supported commands")

                   returnproxy.PROXY_SEND_RESULT

         end

 

         proxy.response= {

                   type= proxy.MYSQLD_PACKET_OK,

                   resultset= {

                            fields= fields,

                            rows= rows

                   }

         }

         returnproxy.PROXY_SEND_RESULT

end

----------------------script end-------------------

 


本文转自 chaijowin 51CTO博客,原文链接:http://blog.51cto.com/jowin/1728247,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
使用JavaScript给对象修改注册监听器
使用JavaScript给对象修改注册监听器
38 0
使用JavaScript给对象修改注册监听器
使用JavaScript给对象修改注册监听器
31 0
通过Job Committer保证Mapreduce/Spark任务数据一致性
通过对象存储系统普遍提供的Multipart Upload功能,实现的No-Rename Committer在数据一致性和性能方面相对于FileOutputCommitter V1/V2版本均有较大提升,在使用MapRedcue和Spark写入数据到S3/Oss的场景中更加推荐使用。
1210 0
JavaScript与 HTML表单的交互过程,想要学习动态网页但是无从下手的新手看看。
最近开始了 java  web的学习在学习中学到了很多有用的东西 比如说 html 表单如何 和 javascript进行交互的  读完本篇文章后相信初学者会有一个 清晰的理解 。。。  对应下面的代码看我写的问题   。
824 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
MySQL Replication: Latest Developments
立即下载
MongoShake -- Multi Active-Active and Cross-Region Disaster Recoverable MongoDB Service
立即下载
Bringing MySQL Compatibility to HBase using Database Virtualization
立即下载