Mysql 5.6的GTID以及主从复制以及读写分离

红太狼 2022-05-23 22:07 152阅读 0赞

*  **GTID**
 *  使用`UUID`标示符号,并且结合事务号,用来唯一的表示每一个主机上面的事务;
 *  每一个事务的首部都会使用`GTID`来进行标示;
 *  `GTID`可以使得追踪和比较复制事务变得非常简单,可以实现从崩溃中快速复制事务的;
 *  假设这样一种场景  
    ![这里写图片描述][70]
 *  `A`作为`B`和`C`的`master`,如果`A`执行了`10`个事务宕机,`B`同步了其中的`1-->8`个事务,`B`同步了`8,9,10`三个事务,那么应该将那个节点提升为主节点;
 *  无论将那个节点提升为新的主节点,新的`master`都应该包含`slave`的所有数据信息,在没有引入`GTID`之前,每个从节点执行事务的情况是无法进行追踪的,但是引入了`GTID`之后,这些就可以追踪到自己没有执行的事务,并且重新进行执行,这些信息可以通过`gtid`的协议来完成,主从服务器可以自动发现从哪个位置开始进行复制;
 *  引入多线程复制:
    
     *  多线程表示的是`SQL thread`;
     *  并且多线程表示的是每个数据库是单个线程进行复制的,所以线程数尽可能和数据库数目保持一致,通过变量`slave-parallel-workers`来进行定义;
 *  `Mysql`官方提供的工具  
    
    
     *  `mysqlreplicate`:关于复制相关的工具,用于快速的启动从服务器,追踪本地已经执行的事务跳过已经执行的事务,快速从没有执行的事务开始
     *  `mysqlrplcheck`:用于快速的验证部署,并且进行简单的故障修复;
     *  `mysqlrplshow`:发现并且显示复制拓扑图;
     *  `mysqlfailover`:故障转移工具,用于快速的提升一个`slave`为`master`;
     *  `mysqlrpladmin`:用于管理某个主机上线或者是下线;
 *  如果需要修改默认的网卡命名规则,可以在这个目录里面进行修改`/etc/udev/rules.d/70-persistent-net.rules`进行修改;
 *  **设置主从架构应该启用的几个选项**
 *  `binlog-format`:表示设置二进制日志的格式,建议使用`row mixed`;
 *  `log-slave-updates`用于标识当`slave`读取中继日志进行数据写入时,是否将这些写操作写入本地的二进制日志中,为了`GTID`复制的安全这一项是需要启用的;
 *  `gtid-mode`:表示是否启用`GTID`的模式;
 *  `enforce-gtid-consistency`:是否强制`GTID`具有一致性,和临时表的创建有关;
 *  `repost-port`:`slave`连接`master`时,需要告知自己的主机名或者`IP`地址以及段口号;
 *  `report-host`:和前一项类似,用于说明端口号;
 *  `sync-master-info`:启用,用于确保信息不丢失,用于确保事务的安全性;
 *  `slave-paralles-workers`:表示启动的`SQL`线程,通常建议少于数据库数目的线程,不建议多于数据库,`0`表示关闭多线程复制;
 *  `binlog-checksum`:主服务器在启动的时候,是否校验本地的校验码;
 *  `master-verify-checksum`:`master`节点校验和;
 *  `slave-sql-verify-checksum`从节点`sql`语句校验和;
 *  `binlog-rows-query-log-events`:用于在二进制日志中记录事件的相关信息,用于降低故障排除的复杂度;
 *  `log-bin`:其用二进制日志,保证复制功能的基本前提;
 *  `server-id`:同一个复制拓扑中所有服务器的`ID`号必须统一;
 *  `master-info-repository`:用于表示`slave logs master status and connection information`是记录在表中,还是文件中;
 *  `master`需要添加的配置文件包括:  
    ![这里写图片描述][70 1]
 *  `slave`上面需要进行的配置:  
    ![这里写图片描述][70 2]
 *  **服务的配置**  
    
    
    > server20.com 172.25.23.20 master  
    > server10.com 172.25.23.10 slave  
    > server\*.com 172.25.23.\* proxy
 *  **配置mysql5.6主从架构**
 *  `master`上面进行的配置
 *  首先是进行编译安装,首先编译的是`cmake`

[root@server10 mysql]# tar -xf cmake-2.8.4.tar.gz 
    [root@server10 mysql]# cd cmake-2.8.4
    [root@server10 mysql]# ./configure
    [root@server10 mysql]# make
    [root@server10 mysql]# make install

*  然后创建`mysql`用户,并且指定数据目录

[root@server10 mysql]# useradd -r mysql
    [root@server10 mysql]# mkdir /mysql/mydata -pv
    [root@server10 mysql]# chown -R mysql.mysql /mysql/mydata/

*  然后编译安装`mysql5.6.40`

[root@server10 mysql]#  cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql >-DMYSQL_DATADIR=/mysql/mydata 
    > -DMYSQL_UNIX_ADDR=/tmp/mysql.sock 
    > -DWITH_MYISAM_STORAGE_ENGINE=1 
    > -DWITH_INNODBASE_STORAGE_ENGINE=1 
    > -DDEFAULT_CHARSET=utf8 
    > -DDEFAULT_COLLATION=utf8_general_ci 
    > -DEXTRA_CHARSETS=all 
    > -DWITH_ARCHIVE_STORAGE_ENGINE=1 
    > -DWITH_BLACKHOLE_STORAGE_ENGINE=1

*  之后执行

[root@server10 mysql]# make && make install

*  在进行软件编译安张之前建议使用`yum groupinstall 'Development tools' -y`来解决可能出现的依赖环境问题;
 *  **进行数据库的初始化工作**
 *  更改文件的属主和属组

[root@server10 mysql]# chown -R root.mysql ./*

*  复制配置文件和启动脚本

[root@server10 mysql]# cp support-files/my-default.cnf /etc/my.cnf 
    [root@server10 mysql]# cp support-files/mysql.server /etc/init.d/mysqld

*  进行数据库的初始化操作

[root@server10 mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/mysql/mydata
    Installing MySQL system tables...2018-05-17 22:48:34 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
    2018-05-17 22:48:34 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
    2018-05-17 22:48:34 0 [Note] ./bin/mysqld (mysqld 5.6.40) starting as process 1525 ...
    2018-05-17 22:48:34 1525 [Note] InnoDB: Using atomics to ref count buffer pool pages
    2018-05-17 22:48:34 1525 [Note] InnoDB: The InnoDB memory heap is disabled

*  启动数据库,并且执行安全安装脚本,设置`root`用户的密码

[root@server10 mysql]# /etc/init.d/mysqld start
    Starting MySQL.Logging to '/mysql/mydata/server20.com.err'.
     SUCCESS! 
    [root@server10 mysql]# ./bin/mysql_secure_installation
    //这步需要设置mysql管理员用户的密码

*  配置不输入密码登陆

[root@server10 ~]# vim ~/.my.cnf 
    添加:
    [mysql]
    host = localhost
    user = root
    password = redhat

*  提供基本的配置文件配置:

datadir = /mysql/mydata
    innodb_file_per_table = 1
    server-id = 1
    socket=/tmp/mysql.sock
    log-bin=master-bin

*  然后重新启动服务;
 *  `slave`和上面进行同样的配置;
 *  不同的是修改`/etc/my.cnf`如下

datadir = /mysql/mydata
    innodb_file_per_table = 1
    server-id = 2
    socket=/tmp/mysql.sock
    log-bin=slave-bin

*  `master`按照`GITD`的方式配置,修改配置文件如下

[root@server10 local]# vim /etc/my.cnf 
    datadir = /mysql/mydata
    innodb_file_per_table = 1
    server-id = 1
    socket=/tmp/mysql.sock
    log-bin=master-bin
    
    binlog-format=ROW
    log-slave-updates=true
    gtid-mod=on
    enforce-gtid-consistency=true
    master-info-repository=TABLE
    relay-log-info-repository=TABLE
    sync-master-info=1
    slave-parallel-workers=2
    binlog-checksum=CRC32
    master-verify-checksum=1
    slave-sql-verify-checksum=1
    binlog-rows-query-log_events=1
    report-port=3306
    report-host=172.25.23.10

*  重新启动`master`

[root@server10 local]# /etc/init.d/mysqld start
    Starting MySQL.. SUCCESS!

*  查看`gtid`的几个指令  
    ![这里写图片描述][70 3]
 *  配置`slave`节点添加以下的指令

datadir = /mysql/mydata
    innodb_file_per_table = 1
    server-id = 2
    socket=/tmp/mysql.sock
    log-bin=slave-bin
    
    binlog-format=ROW
    log-slave-updates=true
    gtid-mode=on
    enforce-gtid-consistency=true
    master-info-repository=TABLE
    sync-master-info=1
    slave-parallel-workers=2
    binlog-checksum=CRC32
    master-verify-checksum=1
    slave-sql-verify-checksum=1
    binlog-rows-query-log_events=1
    report-port=3306
    port=3306
    report-host=172.25.23.20

*  然后启动服务

[root@server20 mysql]# /etc/init.d/mysqld start
    Starting MySQL.. SUCCESS!

*  查看`master`的状态

*  其中`Executed_Gtid_Set`表示执行了的`GTID`集合,用于表示`GTID`已经执行到哪里;
 *  每个`server`唯一的`UUID`
 *  `master`的唯一`ID`:

mysql> SHOW GLOBAL VARIABLES LIKE '%uuid%'; +---------------+--------------------------------------+
    | Variable_name | Value | +---------------+--------------------------------------+
    | server_uuid | da804b27-59e1-11e8-a7a1-52540007f36f | +---------------+--------------------------------------+
    1 row in set (0.00 sec)

*  `slave`的唯一`UUID`:

mysql> SHOW GLOBAL VARIABLES LIKE '%uuid%'; +---------------+--------------------------------------+
    | Variable_name | Value | +---------------+--------------------------------------+
    | server_uuid | ca1a94ef-59e1-11e8-a7a1-52540003aa77 | +---------------+--------------------------------------+
    1 row in set (0.01 sec)

*  `master`创建用户并且进行授权

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'172.25.23.%' IDENTIFIED BY 'replpass';
    Query OK, 0 rows affected (0.27 sec)
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)

*  `SLAVE`连接`master`

mysql> CHANGE MASTER TO MASTER_HOST='172.25.23.10', MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_AUTO_POSITION=1;
    Query OK, 0 rows affected, 2 warnings (1.46 sec)

*  查看`slave`的状态

mysql> SHOW SLAVE STATUS\G;
    *************************** 1. row ***************************
     Slave_IO_State: 
     Master_Host: 172.25.23.10
     Master_User: repluser
     Master_Port: 3306
     Connect_Retry: 60
     Master_Log_File: 
     Read_Master_Log_Pos: 4
     Relay_Log_File: server20-relay-bin.000001
     Relay_Log_Pos: 4
     Relay_Master_Log_File: 
     Slave_IO_Running: No
     Slave_SQL_Running: No
     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: 0
     Relay_Log_Space: 151
     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: NULL
    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: 0
     Master_UUID: 
     Master_Info_File: mysql.slave_master_info
     SQL_Delay: 0
     SQL_Remaining_Delay: NULL
     Slave_SQL_Running_State: 
     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: ca1a94ef-59e1-11e8-a7a1-52540003aa77:1-2
     Auto_Position: 1
    1 row in set (0.00 sec)

*  启动`slave`

mysql> SHOW SLAVE STATUS\G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 172.25.23.10
                      Master_User: repluser
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: master-bin.000002
              Read_Master_Log_Pos: 538
                   Relay_Log_File: server20-relay-bin.000002
                    Relay_Log_Pos: 750
            Relay_Master_Log_File: master-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: 538
                  Relay_Log_Space: 957
                  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: da804b27-59e1-11e8-a7a1-52540007f36f
                 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 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: da804b27-59e1-11e8-a7a1-52540007f36f:1-2
                Executed_Gtid_Set: ca1a94ef-59e1-11e8-a7a1-52540003aa77:1-2,
    da804b27-59e1-11e8-a7a1-52540007f36f:1-2
                    Auto_Position: 1
    1 row in set (0.00 sec)

*  `master`上面查看`slave`的连接状态

*  查看`master`的`GTID`选项

*  在`gtid_executed`的最后`1-3`表示分配个每一个事务的`ID`号码
 *  `master`上面创建数据库,查看`slave`是否已经执行

mysql> CREATE DATABASE mydb;
    Query OK, 1 row affected (0.09 sec)

*  `slave`上面查看是否同步进行创建

*  查看`master`已经执行的事务

mysql> SHOW MASTER STATUS\G;
    *************************** 1. row ***************************
     File: master-bin.000002
     Position: 680
     Binlog_Do_DB: 
     Binlog_Ignore_DB: 
    Executed_Gtid_Set: da804b27-59e1-11e8-a7a1-52540007f36f:1-3
    1 row in set (0.00 sec)

*  查看每个事件关于`GTID`的信息

#180518 21:36:28 server id 1  end_log_pos 586 CRC32 0xee7dba3e  GTID [commit=yes] SET @@SESSION.GTID_NEXT= 'da804b27-59e1-11e8-a7a1-52540007f36f:3[GTID自己提供的事务编号]'/*!*/;
    # at 586
    #180518 21:36:28 server id 1  end_log_pos 680 CRC32 0xfc226632  Query   thread_id=4 exec_time=0 error_code=0
    SET TIMESTAMP=1526650588/*!*/;
    CREATE DATABASE mydb

*  建议的关闭自动提交

*  关闭这个选项

mysql> SET @@autocommit=0;
    Query OK, 0 rows affected (0.00 sec)

*  创建表,查看事务号码是否会改变

mysql> use mydb;
    Database changed
    mysql> CREATE TABLE Ttb1 (id INT);
    Query OK, 0 rows affected (0.64 sec)

*  查看事务号码

mysql> SHOW MASTER STATUS\G;
    *************************** 1. row ***************************
     File: master-bin.000002
     Position: 828
     Binlog_Do_DB: 
     Binlog_Ignore_DB: 
    Executed_Gtid_Set: da804b27-59e1-11e8-a7a1-52540007f36f:1-4[这里已经是4]
    1 row in set (0.00 sec)

*  查看二进制日志

#180518 21:52:28 server id 1  end_log_pos 728 CRC32 0x11abc1a9  GTID [commit=yes] SET @@SESSION.GTID_NEXT= 'da804b27-59e1-11e8-a7a1-52540007f36f:4[更新的日志号码在这里]'/*!*/;
    # at 728
    #180518 21:52:28 server id 1  end_log_pos 828 CRC32 0xe6f65008  Query   thread_id=5 exec_time=1 error_code=0
    use `mydb`/*!*/;
    SET TIMESTAMP=1526651548/*!*/;
    CREATE TABLE Ttb1 (id INT) /*!*/;
    SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog *//*!*/;
    DELIMITER ;
    # End of log file

*  **使用mysql-proxy实现读写请求分离**
 *  实现读写分离的几个软件:
    
     *  `mysql Proxy`:`lua`脚本,用于实现连接路由器,`Query`分析,查询过滤和修改,以及负载均衡和`HA`的高可用;本质上是一个工作引擎,能够调用`lua`脚本写的额外的配置程序,主要的功能是`lua`脚本实现的;本质上是一个框架
     *  `Ameeba(Jave)`:查询路由,查询分析,查询过滤.读写分离,负载均衡,以及`HA`,这个主要是用于实现分库的,并且提供`xml`格式的配置文件;
     *  `Cobar(Java)`: 在`Ameeba`的基础上面进行了大量改进;
     *  `mysql-mm`:用于监控双主集群的数据库状态,防止写操作不一致;
 *  设计要求:
    
    > write server server10.com : 172.25.23.10  
    > read server server20.com: 172.25.23.20  
    > mysql-proxy server21.com: 172.25.23.21
 *  编译安装参考  
    ![这里写图片描述][70 4]
 *  `mysql-proxy`使用通用二进制格式来进行安装;
 *  首先创建用户

[root@server21 ~]# useradd -r mysql-proxy 
    [root@server21 ~]# tar xf mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local/
    [root@server21 local]# ln -sv mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit mysql-proxy
    `mysql-proxy' -> `mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit'

*  提供环境变量

[root@server21 bin]# vim /etc/profile.d/mysql-proxy.sh
    export PATH=$PATH:/usr/local/mysql-proxy/bin
    [root@server21 mysql-proxy]# . /etc/profile.d/mysql-proxy.sh

*  更改文件权限

[root@server21 mysql-proxy]# chown -R root.mysql ./*

*  **认识一下这个命令**
 *  `mysql-proxy`:命令格式分为里两段:
    
     *  `proxy-module`:表示监听模式本身的配置  
        
        
         *  `-P --proxy-address=<host:port>`:表示代理主机的`IP:port`;
         *  `-r, --proxy-read-only-backend-addresses=<host:port>`:表示只读后段的主机信息;
         *  `-b, --proxy-backend-addresses=<host:port>`:表示只写主机的后端信息;
         *  `--proxy-skip-profiling`:表示禁止使用查询分析;
         *  `--proxy-read-timeout`:表示读超时时长;
         *  `--proxy-write-timeout`:表示写超时时长;
         *  `--proxy-connect-timeout`:表示代理连接超时时长;
         *  `-s, --proxy-lua-script=<file>`:`lua`脚本的文件路径;
     *  `Application Options:`  
        
        
         *  `--defaults-file=<file>`:表示默认的配置文件路径,可以写在`my.cnf`里面,使用`mysql-proxy`来进行说明;
         *  `--daemon`:按照守护进程模式运行;
         *  `--user=`:表示运行的用户;
         *  `--pid-file=`:`pid`文件路径;
     *  `mysql-proxy`使用的插件文件路径在`/usr/local/mysql-proxy/lib/mysql-proxy/lua` 以及`/usr/local/mysql-proxy/lib/mysql-proxy/plugins`
 *  按照选项尝试启动`mysql-proxy`

[root@server21 plugins]# mysql-proxy --daemon --log-level=debug --plugins="proxy" --log-file=/var/log/mysql-proxy.log --proxy-backend-addresses="172.25.23.10:3306" --proxy-read-only-backend-addresses="172.25.23.20:3306"

*  查看`mysql-proxy`的启动状态

2018-05-19 11:13:45: (critical) plugin proxy 0.8.3 started
    2018-05-19 11:13:45: (debug) max open file-descriptors = 1024
    2018-05-19 11:13:45: (message) proxy listening on port :4040
    2018-05-19 11:13:45: (message) added read/write backend: 172.25.23.10:3306
    2018-05-19 11:13:45: (message) added read-only backend: 172.25.23.20:3306

*  查看`4040`端口已经启用  
    ![这里写图片描述][70 5]
 *  `master`创建一个用户用于测试登陆是否正常

mysql> GRANT ALL ON *.* TO 'root'@'172.25.23.%'  IDENTIFIED BY 'redhat';
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.06 sec)

*  使用授权的帐号连接`mysql-proxy`服务器

[root@my Downloads]# mysql -uroot -p -h172.25.23.21 --port=4040
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 8
    Server version: 5.6.40-log Source distribution [这一行信息表示连接的是5.6.40的版本]
    
    Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MySQL [(none)]> SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mydb               |
    | mysql              |
    | performance_schema |
    +--------------------+
    4 rows in set (0.01 sec)

*  这个过程可能由于名称解析出错,可能会反解主机名`172.25.23.21`为`server21.com`,这里关闭反解主机名成的选项,或者就需要创建两个用户

*  打开`skip_name_resolve`选项,这个变量是只读变量需要在配置文件中进行修改并且重新启动服务器

MySQL [(none)]> SET @@global.skip_name_resolve=ON;
    ERROR 1238 (HY000): Variable 'skip_name_resolve' is a read only variable

*  `mysql5.6.*`以后的版本会自动隐藏关于授权和密码可能以明文出现的指令;
 *  尝试执行创建数据库的操作,对于执行创建数据库的路由有两点说明:
    
     *  如果路由到`master`上面,`slave`会自动同步写操作,`master`和`slave`都会存在;
     *  如果路由到`slave`上面,那么`slave`上面不存在,`master`上面也不会存在;
 *  连接代理服务器`mysql-proxy`,创建数据库

MySQL [(none)]> CREATE DATABASE hellodb;
    Query OK, 1 row affected (0.05 sec)

*  查看`master`是否存在数据库  
    ![这里写图片描述][70 6]
 *  查看从服务器是否存在数据库`hellodb`  
    ![这里写图片描述][70 7]
 *  上面是功能很有可能是因为巧合导致的,因为`mysql-proxy`需要借助于`lua`  
    脚本来实现,需要使用选项来指定`lua`脚本来实现读写分离,脚本的位置在`/usr/local/mysql-proxy/share/doc/mysql-proxy`底下
 *  接下来重新启动服务并且,传递参数使用选项指定脚本来实现读写分离

*  查看启动日志

2018-05-19 14:04:47: (critical) plugin proxy 0.8.3 started
    2018-05-19 14:04:47: (debug) max open file-descriptors = 1024
    2018-05-19 14:04:47: (message) proxy listening on port :4040
    2018-05-19 14:04:47: (message) added read/write backend: 172.25.23.10:3306
    2018-05-19 14:04:47: (message) added read-only backend: 172.25.23.20:3306

*  使用`admin`并且集合`lua`脚本来实现管理功能
 *  首先编写`/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua`脚本如下

function set_error(errmsg) 
        proxy.response = {
            type = proxy.MYSQLD_PACKET_ERR,
            errmsg = errmsg or "error"
        }
    end
    
    function read_query(packet)
        if packet:byte() ~= proxy.COM_QUERY then
            set_error("[admin] we only handle text-based queries (COM_QUERY)")
            return proxy.PROXY_SEND_RESULT
        end
    
        local query = packet:sub(2)
    
        local rows = { }
        local fields = { }
    
        if query: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 },
            }
    
            for i = 1, #proxy.global.backends do
                local states = {
                    "unknown",
                    "up",
                    "down"
                }
                local types = {
                    "unknown",
                    "rw",
                    "ro"
                }
                local b = 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 down starting at 0
                    b.uuid,              -- the MySQL Server's UUID if it is managed
                    b.connected_clients  -- currently connected clients
                }
            end
        elseif query: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 their state" }
        else
            set_error("use 'SELECT * FROM help' to see the supported commands")
            return proxy.PROXY_SEND_RESULT
        end
    
        proxy.response = {
            type = proxy.MYSQLD_PACKET_OK,
            resultset = {
                fields = fields,
                rows = rows
            }
        }
        return proxy.PROXY_SEND_RESULT
    end

*  然后停止服务:

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

*  使用新的脚本重新启动服务

[root@server21 mysql-proxy]# mysql-proxy --daemon --log-level=debug --plugins="proxy" --log-file=/var/log/mysql-proxy.log --proxy-backend-addresses="172.25.23.10:3306" --proxy-read-only-backend-addresses="172.25.23.20: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

*  查看日志查看服务是否正常启动

2018-05-19 14:45:19: (critical) plugin proxy 0.8.3 started
    2018-05-19 14:45:19: (critical) plugin admin 0.8.3 started
    2018-05-19 14:45:19: (debug) max open file-descriptors = 1024
    2018-05-19 14:45:19: (message) proxy listening on port :4040
    2018-05-19 14:45:19: (message) added read/write backend: 172.25.23.10:3306
    2018-05-19 14:45:19: (message) added read-only backend: 172.25.23.20:3306

*  查看管理端口`4041`是否正常启动

![这里写图片描述][70 8]

*  连接管理端口,查看后段的服务器状况

[root@my Downloads]# mysql -h172.25.23.21 -uadmin -p --port="4041"
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 1
    Server version: 5.0.99-agent-admin
    
    Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    MySQL [(none)]> SELECT * FROM backends;
    +-------------+-------------------+---------+------+------+-------------------+
    | backend_ndx | address           | state   | type | uuid | connected_clients |
    +-------------+-------------------+---------+------+------+-------------------+
    |           1 | 172.25.23.10:3306 | up      | rw   | NULL |                 0 |
    |           2 | 172.25.23.20:3306 | unknown | ro   | NULL |                 0 |
    +-------------+-------------------+---------+------+------+-------------------+
    2 rows in set (0.00 sec)

*  通过写入数据和查询操作可以查看请求被转交给那个服务器执行  
    ![这里写图片描述][70 9]
 *  由于缓存的原因,插入和查询操作建议是不一样的数据
 *  提供默认的服务启动脚本

[root@server21 mysql-proxy]# chkconfig --add 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
    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@server21 mysql-proxy]# vim /etc/sysconfig/mysql-proxy
    ADMIN_PASSWORD="admin"
    ADMIN_ADDRESS=""
    ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
    PROXY_ADDRESS=""
    PROXY_USER="mysql-proxy"
    PROXY_OPTIONS="--daemon --log-level=info --log-file="/var/log/mysql-proxy.log" --plugins=proxy --plugins=admin --proxy-backend-addresses=172.25.23.10:3306 --proxy-read-only-backend-addresses=172.25.23.20:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

*  使用脚本启动服务,并且查看端口

[root@server21 mysql-proxy]# /etc/init.d/mysql-proxy restart

![这里写图片描述][70 10]

*  查看服务的状态是正常的了

[root@my Downloads]# mysql -h172.25.23.21 -uadmin -p --port=4041
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 1
    Server version: 5.0.99-agent-admin
    
    Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    
    MySQL [(none)]> SELECT * FROM backends;
    +-------------+-------------------+---------+------+------+-------------------+
    | backend_ndx | address           | state   | type | uuid | connected_clients |
    +-------------+-------------------+---------+------+------+-------------------+
    |           1 | 172.25.23.10:3306 | up      | rw   | NULL |                 0 |
    |           2 | 172.25.23.20:3306 | unknown | ro   | NULL |                 0 |
    +-------------+-------------------+---------+------+------+-------------------+
    2 rows in set (0.00 sec)

[70]: /images/20220524/98869905e09a4ccca84c69ba25cd7794.png
[70 1]: /images/20220524/d01ebb6fdd17412da83bc2af482e0024.png
[70 2]: /images/20220524/427631e24efc482d8c013bed69218510.png
[70 3]: /images/20220524/dc35fedd9b504628be94a772e220da8d.png
[70 4]: /images/20220524/bdb6fb6a0bd24ffa801bd5f79adffdb6.png
[70 5]: /images/20220524/ad71b5e8c1214f9b9b4b25ce7fb78d97.png
[70 6]: /images/20220524/af62485c218a4ac4be5e821ced2d6fec.png
[70 7]: /images/20220524/c27d06dd16264067bbff94bef0384987.png
[70 8]: /images/20220524/058ba6f936b942b4822d32330273f23c.png
[70 9]: /images/20220524/e6c1b5563e6c482294eb81e74ee91612.png
[70 10]: /images/20220524/b99a67d86da24a328d0d0c410c4b887a.png