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

最新推荐文章于 2020-05-12 18:39:47 发布

bug--maker

最新推荐文章于 2020-05-12 18:39:47 发布

阅读量671

点赞数

分类专栏： Mysql 文章标签： mysql-proxy mysql-读写分离 Mysql5.6

本文链接：https://blog.csdn.net/qq_36294875/article/details/80335140

版权

Mysql 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

GTID
使用UUID标示符号,并且结合事务号,用来唯一的表示每一个主机上面的事务;
每一个事务的首部都会使用GTID来进行标示;
GTID可以使得追踪和比较复制事务变得非常简单,可以实现从崩溃中快速复制事务的;
假设这样一种场景
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需要添加的配置文件包括:
slave上面需要进行的配置:
服务的配置

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的几个指令
配置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的状态

mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 |      151 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

其中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的连接状态

mysql> SHOW SLAVE HOSTS;
+-----------+--------------+------+-----------+--------------------------------------+
| Server_id | Host         | Port | Master_id | Slave_UUID                           |
+-----------+--------------+------+-----------+--------------------------------------+
|         2 | 172.25.23.20 | 3306 |         1 | ca1a94ef-59e1-11e8-a7a1-52540003aa77 |
+-----------+--------------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

查看master的GTID选项

mysql> SHOW GLOBAL VARIABLES LIKE '%GTID%';
+---------------------------------+------------------------------------------+
| Variable_name                   | Value                                    |
+---------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery     | OFF                                      |
| enforce_gtid_consistency        | ON                                       |
| gtid_executed                   | da804b27-59e1-11e8-a7a1-52540007f36f:1-3 |
| gtid_mode                       | ON                                       |
| gtid_owned                      |                                          |
| gtid_purged                     |                                          |
| simplified_binlog_gtid_recovery | OFF                                      |
+---------------------------------+------------------------------------------+
7 rows in set (0.00 sec)

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

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

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

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.03 sec)

查看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> SHOW GLOBAL VARIABLES LIKE '%commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | ON    |
| binlog_order_commits           | ON    |
| innodb_api_bk_commit_interval  | 5     |
| innodb_commit_concurrency      | 0     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
5 rows in set (0.00 sec)

关闭这个选项

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
编译安装参考
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端口已经启用
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,这里关闭反解主机名成的选项,或者就需要创建两个用户

MySQL [(none)]> SHOW GLOBAL VARIABLES LIKE '%name%';
+-----------------------------+--------------------------+
| Variable_name               | Value                    |
+-----------------------------+--------------------------+
| hostname                    | server10.com             |
| innodb_buffer_pool_filename | ib_buffer_pool           |
| lc_time_names               | en_US                    |
| log_bin_basename            | /mysql/mydata/master-bin |
| lower_case_table_names      | 0                        |
| relay_log_basename          |                          |
| skip_name_resolve           | OFF                      |
+-----------------------------+--------------------------+
7 rows in set (0.02 sec)

打开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是否存在数据库
查看从服务器是否存在数据库hellodb
上面是功能很有可能是因为巧合导致的,因为mysql-proxy需要借助于lua
脚本来实现,需要使用选项来指定lua脚本来实现读写分离,脚本的位置在/usr/local/mysql-proxy/share/doc/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

查看启动日志

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是否正常启动

这里写图片描述

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

[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)

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


[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

这里写图片描述

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

[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)

bug--maker

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql 5.6的GTID以及主从复制以及读写分离

GTID使用UUID标示符号,并且结合事务号,用来唯一的表示每一个主机上面的事务;每一个事务的首部都会使用GTID来进行标示;GTID可以使得追踪和比较复制事务变得非常简单,可以实现从崩溃中快速复制事务的;假设这样一种场景 A作为B和C的master,如果A执行了10个事务宕机,B同步了其中的1--&amp;amp;amp;amp;amp;amp;amp;amp;gt;8个事务,B同步了8,9,10三个事务,那么应该将那个节点提升为主节点;无论...
复制链接

扫一扫