当前位置: 58彩票app下载 > 关于计算机 > 正文

ssl加密复制以及读写分离,6读写分离

时间:2019-12-05 06:33来源:关于计算机
随笔介绍的是透过在RedHat6.4的陆拾伍人系统平台mysql5.6的主从复制、基于GTID复制、半联合、ssl加密甚至读写分离这么些配置利用在分娩条件中是实用的,拿来和我们享用一下 Mysql-5.5主从

随笔介绍的是透过在RedHat6.4的陆拾伍人系统平台mysql5.6的主从复制、基于GTID复制、半联合、ssl加密甚至读写分离这么些配置利用在分娩条件中是实用的,拿来和我们享用一下

Mysql-5.5主从落实合营、半联手、过滤、基于ssl安全复制、以致mysql-proxy达成mysql-5.6读写分离

Mysql5.6主从复制介绍

案例拓扑图

Slave服务器通过IO线程连接Master,并恳请从钦命钦点的日志开首复制,Master重回其Binary Log 文件的名目以致在 BinaryLog 中之处及内容消息,Slave端将复制过来的日记音讯保存到温馨的连结日志中,然后在把中继日志中的事件音讯一条一条的在本地施行贰回,达成数据在本土的存放

图片 1

主从复制简构图

**创设mysql数据目录及经过顾客并设置mysql  

图片 2

**成立mysql服务进程的顾客,提供mysql数据存放目录,并校勘数据的目录。    

Mysql5.6半合伙复制介绍

[[email protected] ~]# mkdir -pv /mydata/data
mkdir: created directory `/mydata'
mkdir: created directory `/mydata/data'
[[email protected] ~]# useradd -r mysql
[[email protected] ~]# chown -R mysql.mysql /mydata/data

在大器晚成主多从形式下,Master与Slave端之间只达成贰个Slave的大器晚成道复制与主从复制机制同样);即提交业务的线程会被锁定,直到最少一个Slave收到那一个事情,事务的平地风波在被提交到存款和储蓄引擎之后才被发送到Slave上

将从英特网下载的mysql二进制包复制到从服务端。

Mysql5.6生机勃勃主多从方式下半同步简构图

 

图片 3

[[email protected] ~]# scp mysql-5.5.28-linux2.6-i686.tar.gz 172.16.20.7:/root/
The authenticity of host '172.16.20.7 (172.16.20.7)' can't be established.
RSA key fingerprint is 0a:0b:2f:67:c7:29:af:79:fe:2f:64:51:ca:01:1d:b0.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.16.20.7' (RSA) to the list of known hosts.
[email protected]'s password:
mysql-5.5.28-linux2.6-i686.tar.gz                                                    100%  172MB   6.9MB/s   00:25

规律部分这里就以分批次的张开介绍了,下边大家就先一同来看风姿浪漫看Mysql复制的兑现进度

此地以主服务端安装二进制源码包mysql。

Mysql5.6主从复制

[[email protected] ~]# tar xf mysql-5.5.28-linux2.6-i686.tar.gz -C /usr/local/
[[email protected] ~]# cd /usr/local/
[[email protected] local]#ln -sv  mysql-5.5.28-linux2.6-86.i386 mysql
[[email protected] local]# cd mysql
[[email protected] mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data
WARNING: The host 'master.yangyaru.com' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables...
OK
Filling help tables...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h master.yangyaru.com password 'new-password'
Alternatively you can run:
./bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl
Please report any problems with the ./bin/mysqlbug script!

Master服务器筹划

给mysql服务提供一个开行服务脚本并开机自动运行。

创建mysql用户

[[email protected] mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[[email protected] mysql]# chkconfig --add mysqld

# groupadd -g 3306 mysql

将mysql的命令增加到PATH变量中不分轩轾读下脚本。

# useradd -g 3306 -u 3306 mysql

[[email protected] mysql]# vim /etc/profile.d/mysql.sh
export PATH=$PATH:/usr/local/mysql/bin
[[email protected] mysql]# .  /etc/profile.d/mysql.sh

创制数量目录

给mysql提供一个构造文件并布置mysql配置文件

# mkdir /mydata/data -pv

[[email protected] mysql]# cp support-files/my-large.cnf /etc/my.cnf
[[email protected] ~]# cat /etc/my.cnf
添加如下几行,其余与的不动
datadir = /mydata/data
innodb_file_per_table = 1
log-bin=master-bin
log_bin_index = master_bin.index
binlog_format=mixed
server-id = 6

# cd /mydata/

起步mysql服务器进度。

# chown mysql.mysql data –R

[[email protected] ~]# service mysqld start

配置Mysql5.6

Starting MySQL................                             [  OK  ]   
查阅进度是还是不是已运营。

# tar  xf mysql-5.6.10-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

图片 4

# cd /usr/local/

间接在命令行提醒符下连接上mysql测下是还是不是能够连接上mysql,假若不得以再张开四个尖峰测验。   
总是上mysql授权172.16.20.7同意复制数据。

# ln -sv mysql-5.6.10-linux-glibc2.5-x86_64mysql

[[email protected] ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.5.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2012, 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 'yangyaru'@'172.16.20.7' identified by 'yangyaru';
Query OK, 0 rows affected (0.09 sec)
mysql>flush privileges;
mysql>exit

# chown .mysql * -R

从服务端的相干配置比相当多和主服务端的构造相似这里,小编只列出不平等的布置。   
从服务端的铺排文件    

初始化mysql

[[email protected] ~]# cat /etc/my.cnf
添加如下几行,其余与的不动
datadir = /mydata/data
innodb_file_per_table = 1
relay_log=relay_log
relay_bin_index = relay_log.index
binlog_format=mixed
server-id = 7

# scripts/mysql_install_db --user=mysql--datadir=/mydata/data

查阅下主服务端今后二进制日志的文件和地方。   
图片 5
开启主服务端授权的帐号连接到主服务器端并展开复制。

有备无患服务脚本

[[email protected] ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.5.28 MySQL Community Server (GPL)
Copyright (c) 2000, 2012, 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> CHANGE MASTER TO  MASTER_HOST = '172.16.20.6', MASTER_PORT = 3306, MASTER_USER='yangyaru',MASTER_PASSWORD='yangyaru', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=340;
Query OK, 0 rows affected (0.17 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> q
Bye

# cp support-files/mysql.server/etc/init.d/mysqld

查阅下当前大家的从服务器端记录二进制数据文件和position地方。

# chkconfig --add mysqld

mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.20.6
Master_User: yangyaru
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 340
Relay_Log_File: relay_log.000002
Relay_Log_Pos: 254
Relay_Master_Log_File: master-bin.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: 340
Relay_Log_Space: 404
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: 6
1 row in set (0.03 sec)

最初化后会自动在当前目录下开创三个my.cnf配置文件,直接改进就可以

可以看看日前是Read_Master_Log_Pos: 340。

改良配置文件

到此不难的为主mysql服务配置就已经达成^_^。

#vim my.cnf

上边临此布置测量检验下,在主服务器端成立二个yangyarudb的数据库。看下从服务器上是还是不是复制。

加上源委是:

图片 6

binlog-format=ROW
log-bin=master-bin.log
log-slave-updates=true
gtid-mode=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
server-id=1
report-port=3306
port=3306
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=master.magedu.com

图片 7

加多情形变量

当今对大家的布置做下延长。

# vim /etc/profile.d/mysql.sh

风度翩翩、主从服务器读写抽离实现负载均衡。

内容是

相同大家从劳动器端是只承受客商的读须求的,主服务端担当写央求的。那么配置下呢!

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

率先查看下从服务器端的只读形式是或不是展开。

# . /etc/profile.d/mysql.sh

mysql> show global variables like 'read%';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| read_buffer_size     | 1048576 |
| read_only            | OFF     |
| read_rnd_buffer_size | 4194304 |
+----------------------+---------+
3 rows in set (0.00 sec)

启动mysql

张开我们的只读情势有二种

# mysql

生机勃勃种是在mysql服务器端的全局形式下安插,然则在大局格局下安插mysql重启之后就能失效;

图片 8

生龙活虎种是在/etc/my.cnf配置文件中布置,那个布局是永远生效;这里咱们接收第三种。

在从服务器上举行mysql的设置与主服务器相近,这里就不再过多的汇报了

给/etc/my.cnf配置文件增加后生可畏行如下:

其间从服务器的my.cnf配置文件增多内容为

[[email protected] ~]# vim /etc/my.cnf 

binlog-format=ROW
log-slave-updates=true
gtid-mode=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
server-id=11
report-port=3306
port=3306
log-bin=mysql-bin.log
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=slave.magedu.com

read_only = ON

在主服务器上授权具备复制权限的客户

然后重启运营下大家的mysql服务器。

mysql> GRANT REPLICATION SLAVE ON *.* [email protected] IDENTIFIED BY 'test';

[[email protected] ~]# service mysqld restart

mysql> FLUSH PRIVILEGES;

Shutting down MySQL......                                  [  OK  ]

查看主服务器状态新闻

Starting MySQL..................                           [  OK  ]

图片 9

进去mysql服务测量检验下是不是运维只读方式。   
图片 10

在从服务器上接连到主服务器

主服务器端实践了写操作,日志文件立时联合到从服务器,为了保障事业的完整性。能够在

mysql> CHANGE MASTER TOMASTER_HOST='172.16.51.20',

从劳动器端配置文件的[mysqld]段中增多skip_slave_start = 1来落实从服务器的mysql服务在运维时候绝不自行运营从服务线程。

  -> MASTER_USER='test',

二、实现中央服务器半协同。

  -> MASTER_PASSWORD='test',

半一只的概念:主服务器端实行了写操作,必得往从服务器端复制大器晚成份,能力给顾客端重临提交状态。

  -> MASTER_LOG_FILE='master-bin.000001',

那边大家需求在主服务器端安装semisync_master.so从服务器端安装semisync_slave.so

->MASTER_LOG_POS=919;

具体步骤如下:

翻看连接后从服务器状态

主服务器端:

图片 11

mysql> install plugin rpl_semi_sync_master SONAME 'semisync_master.so';

初叶从服务器服务

Query OK, 0 rows affected (0.36 sec)

mysql> start slave;

图片 12
安装好现在看下是或不是运营若无运转大家启用下。    
图片 13

情形新闻查阅

主服务器端开启semi_sync效用,并安装等待时候为3秒。

图片 14

mysql> set global rpl_semi_sync_master_enabled =1;

在主服务器上创制连锁数据开展测量检验

Query OK, 0 rows affected (0.02 sec)

图片 15

mysql> set global rpl_semi_sync_master_timeout = 3000 ;

在从服务器上查看

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUSG

图片 16

图片 17

从服务器端:

翻看从服务器上享有数据库

mysql> install plugin rpl_semi_sync_slave  SONAME 'semisync_slave.so';

图片 18

Query OK, 0 rows affected (0.28 sec)

如上内容介绍的是mysql5.6不依赖GTID达成的复制,下边大家来三头看黄金年代看基于GTID完毕的mysql复制

图片 19

这一次实验是在上述试验成功现在做,这里大器晚成度把前面全体的连锁消息都已删除,重新开展的初阶化才开展上面包车型地铁实验

从劳动器端开启semi_sync功能。

基于GTID实现的mysql复制介绍

mysql> set global rpl_semi_sync_slave_enabled =1;

mysql5.6基于GTID达成复制简构图

Query OK, 0 rows affected (0.03 sec)

图片 20

图片 21

GTID是什么

检验收下从主服务端:   
图片 22

由服务器的UUID全局唯黄金时代标志,126个人随机字符串,并构成专业的ID号组合成七个唯风流罗曼蒂克的标记某多少个主机上某贰个政工的代表码mysql5.6的二进制日志中binlog在各种事务语句记录是在职业的首部都会记录相关联的GTID;优点是GTID追踪,比较复制业务越来越简便易行,而且能够落到实处从崩溃中快捷张开恢复

半联合复制是假使从服务端没有拉开的话,主服务端第叁回会延迟3秒中然后提交,之后主服务端会下落延迟不再等待从服务端。从服务端开启之后在追逐上主服务端让后在贯彻半一块。

行事机制

福如东海演示下:

A为主BC为从时,BC复制速度会差别,则在A故障的那一刻,BC上的数量也是有例外,那个时候B若为二个新的MASTE福特Explorer通过GTID的措施完毕的话正是在C上一些但B上平昔不的都先要复制到B上,然后把B进步为主的,然后再把C成为B的从,GTID功效会把记录在二进制中各类业务中首部都记录三个GTID号;那时B可向C发出通报告知C已经达成了怎么工作,有了GTID后,每个业务中都有其有关业务的音信;实现了服务器两端自动的意识各自在对方上贫乏什么没有须要再举行手动内定的职位、钦命文件举行理并答复制

1、先关闭从服务端。

相关命令介绍

mysql> stop slave;

mysqlreplicate:复制相关工具可以快捷运营从服务器,扶持从服务器检查本地全数以前在地面实践过的事体,通过追踪GTID推断,跳过那几个推行过的职业,连忙从未推行过的作业早先

Query OK, 0 rows affected (0.02 sec)

mysqlrplcheck:兼具复制与检查的成效,用于落到实处轻便的求证布署,并促成高效故障杀绝快速故障修复的连锁功效)检查binlog是不是被启用了并出示相关的陈设信息

2、主服务端成立四个semidb数据库

mysqlrplshow:开掘并呈现复制拓扑图以树状图的花样展现是或不是是多级复制,已经基本服务器的个数等连锁音讯)

图片 23

msyqlfailover:故障转移工具火速的通过手动依然机关将四个slave转变来master)获取另一个slave中本身不具有的事情

图片 24

msyqlrpladmin:调节管理工科具手动的让一个slave上线,下线)

3、从服务端运行服务。

在主服务器上的my.cnf配置

mysql> start slave;

binlog-format=ROW
log-bin=master-bin.log                       
log-slave-updates=true                  
gtid-mode=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
server-id=1
report-port=3306
port=3306
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=master.magedu.com

Query OK, 0 rows affected (0.03 sec)   
mysql> show slave statusG    
图片 25

从服务器上my.cnf配置文件增加的源委

三、percona-toolkit是二个特意对msyql主从服务的田间处理的一个工具。这里大家能够设置下利用下。

binlog-format=ROW
log-slave-updates=true
gtid-mode=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
server-id=11
report-port=3306
port=3306
log-bin=master-bin
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=slave.magedu.com

主导服务都安装percona-toolkit

初始mysql服务器,在大旨服务器查看是或不是运维gtid效率

主服务端下载安装下

图片 26

[[email protected] ~]# yum -y --nogpgcheck localinstall percona-toolkit-2.2.2-1.noarch.rpm 

将172.16.51.20master.magedu.com)定义为主服务器

从服务端从主服务端复制过来安装:

开创账户

[[email protected] ~]# scp percona-toolkit-2.2.2-1.noarch.rpm 172.16.20.7:/root/

mysql> GRANT REPLICATION SLAVE ON *.* [email protected] IDENTIFIED BY 'test';

[email protected]'s password: 

mysql> FLUSH PRIVILEGES;

percona-toolkit-2.2.2-1.noarch.rpm                                                     100% 1632KB 816.0KB/s   00:02 

让172.16.51.21slave.magedu.com)定义为从接二连三到主服务器上

设置之后会给大家转变比相当多以pt早先的命令行工具这个命令,如pt-slave-delay:这一个命令行工具,他是非常让大家的从劳动端比主服务端慢一点的的安装。

mysql> CHANGE MASTER TOMASTER_HOST='172.16.51.20',MASTER_USER='test',MASTER_PASSWORD='test',MASTER_AUTO_POSITION=1;

那些命令大家不豆蔻梢头生龙活虎做牵线了哈:

启动slave

四、基于ssl加密的艺术完结主从复制。

mysql> START SLAVE;

Mysql的主从复制是真心诚意传送的,但在生育条件中大家的基本服务器不可能在同三个机房或在同三个局域网中,这时我们的多少传输料定需求用到ssl啦。Ssl的落到实处其实很简短的,如下操作步骤。

翻开slave状态信息

主服务器端自创成立CA,并为主服务器端和从劳动器端颁发CA。   

mysql> show slave statusG;

[[email protected] ~]#vim /etc/pki/tls/openssl.conf  #如果你的系统平台是5.8的就需要该这些
将dir = ../../CA 更改为:dir = /etc/pki/CA
[[email protected] ~]# (umask 077;openssl genrsa -out /etc/pki/CA/private/cakey.pem 1024)
[[email protected] ~]# openssl req -new -x509 -key /etc/pki/CA/private/cakey.pem -out /etc/pki/CA/cacert.pem -days 3650  #CA 的相关信息这里我们根据自己的实际情况填写。
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:HENAN
Locality Name (eg, city) [Default City]:ZHENGZHOU
Organization Name (eg, company) [Default Company Ltd]:magedu
Organizational Unit Name (eg, section) []:tech
Common Name (eg, your name or your server's hostname) []:ca.magedu.com
Email Address []:caadmin.magedu.com
[[email protected] ~]# mkdir /etc/pki/CA/{certs,newcerts,crl}  #如果你的系统平台是5.8需要创建这些文件。
[[email protected] ~]# touch /etc/pki/CA/index.txt
[[email protected] ~]# echo 01 > /etc/pki/CA/serial

图片 27

主干服务器端都创设密钥和申请文件,步骤相仿这里以主服务端为例。   

在主服务器上创建叁个数据库举办测量试验

[[email protected] ~]#mkdir /usr/local/mysql/ssl
[[email protected] ~]#(umask 077;openssl genrsa -out  /usr/local/mysql/ssl/mysql.key 1024)
[[email protected] ~]#openssl req -new -key /usr/local/mysql/ssl/mysql.key -out  /usr/local/mysql/ssl/mysql.csr -days 365 #申请书的内容根据自己的实际情况填写。
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:HENAN
Locality Name (eg, city) [Default City]:ZHENGZHOU
Organization Name (eg, company) [Default Company Ltd]:magedu
Organizational Unit Name (eg, section) []:tech
Common Name (eg, your name or your server's hostname) []:master.yangyaru.com
Email Address []:master.yangyaru.com
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

mysql> CREATE DATABASE magedudb1;

主服务器端的CA证书颁发:

在从服务器上证实

[[email protected] ~]#openssl ca -in /usr/local/mysql/ssl/mysql.csr -out /mydata/data/ssl/mysql.crt -days 365
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 1 (0x1)
Validity
Not Before: May 19 23:47:49 2013 GMT
Not After : May 19 23:47:49 2014 GMT
Subject:
countryName               = CN
stateOrProvinceName       = HENAN
organizationName          = magedu
organizationalUnitName    = tech
commonName                = master.yangyaru.com
emailAddress              = master.yangyaru.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
9B:42:46:CF:1D:83:56:7D:04:37:CB:40:89:A2:07:EC:C7:9D:C2:0D
X509v3 Authority Key Identifier:
keyid:07:7C:CF:69:74:1D:4D:D8:09:7A:3C:D9:F3:07:B6:46:40:E0:47:0C
Certificate is to be certified until May 19 23:47:49 2014 GMT (365 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated

图片 28

从服务器端创制申请书之后将申请书发送到CA让CA签订。   

查看数据库

[[email protected] ~]#scp  /usr/local/mysql/ssl/mysql.csr  172.16.20.6:/tmp
The authenticity of host '172.16.20.6 (172.16.20.6)' can't be established.
RSA key fingerprint is 0a:0b:2f:67:c7:29:af:79:fe:2f:64:51:ca:01:1d:b0.
Are you sure you want to continue connecting (yes/no)? y
Please type 'yes' or 'no': yes
Warning: Permanently added '172.16.20.6' (RSA) to the list of known hosts.
[email protected]'s password:
mysql.csr            100%  708     0.7KB/s   00:00
[[email protected] ~]#openssl ca -in  /tmp/mysql.csr -out /tmp/mysql.crt -days 365
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 2 (0x2)
Validity
Not Before: May 19 23:51:08 2013 GMT
Not After : May 19 23:51:08 2014 GMT
Subject:
countryName               = CN
stateOrProvinceName       = HENAN
organizationName          = magedu
organizationalUnitName    = tech
commonName                = slave.yangyaru.com
emailAddress              = slave.yangyaru.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
A8:DB:F3:88:F1:25:A0:02:B6:F5:49:EE:67:2B:C1:BE:66:B9:E8:A7
X509v3 Authority Key Identifier:
keyid:07:7C:CF:69:74:1D:4D:D8:09:7A:3C:D9:F3:07:B6:46:40:E0:47:0C
Certificate is to be certified until May 19 23:51:08 2014 GMT (365 days)
Sign the certificate? [y/n]:yes
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
[[email protected] ~]# scp  /tmp/mysql.crt  172.16.20.7:/tmp
[email protected]'s password:
mysql.crt                                       100% 3239     3.2KB/s   00:00
[[email protected] ~]# mv  /tmp/mysql.crt  /mydata/data/ssl

图片 29

将CA文件按拷贝到从服务端证书所在目录    
[[email protected] ~]# scp  /etc/pki/CA/cacert.pem 172.16.20.7:/mydata/data/ssl/ 

查看Slave主机

[email protected]'s password: 

图片 30

cacert.pem               100% 1070     1.0KB/s   00:00   
留意:生成的证书和密钥的属组和属主都要改成mysql客商。    
从这之后,证书颁发成功!!

ssl加密复制达成

下边配置基本服务器让他们帮衬ssl功效。   
第生龙活虎看下ssl功能的开启状态,若无拉开就开启。    
Master:    
主服务器端配置开启ssl功用    
[[email protected] ~]#vim /etc/my.cnf 

ssl加密复制是临蓐条件中对数据安全性的三个保持,配置进度也比较轻松,以前在头里的篇章中都有对ssl加密完毕存详实介绍这里就不再过多废话了

累积如下几行,其他不动   
ssl  #张开服务

预备私有CA

ssl_ca = /mydata/data/ssl/cacert.pem   #点名下CA文件所在的职位在此   
ssl_cert = /mydada/data/ssl/mysql.crt   #证件文件的职务    
ssl_key = /usr/local/mysql/ssl/mysql.key #密钥所在的地点

# mkdir ca

重启服务,查看其变量的情况。

# cd ca

[[email protected]er ~]#service mysqld restart
mysql>show variables like ‘%ssl%’;
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| have_openssl  | YES                       |
| have_ssl      | YES                       |
| ssl_ca        | /mydata/data/ssl/cacert.pem    |
| ssl_capath    |                                |
| ssl_cert      | /mydata/data/ssl/mysql.crt     |
| ssl_cipher    |                                |
| ssl_key       | /usr/local/mysql/ssl/mysql.key |
+---------------+--------------------------------+
7 rows in set (0.00 sec)

# (umask 077;openssl genrsa 2048 >ca-key.pem)

成立一个帐号让从服务器端能够接连不断到主服务器端复制数据,不过要加多上基于密钥认证才得以。

# openssl req -new -x509 -nodes -days 3665-key ca-key.pem -out ca-cert.pem

mysql>grant replication slave on *.*  to  'yangyaru'@'172.16.20.7'  identified by 'yangyaru'  require ssl;   
mysql>flush privileges;

为master服务器生成证书

查看主服务器端未来二进制的公文和所在的职责记下它,等会从服务端连接直接从这边初始复制。   
图片 31
slave:    
布置mysql配置文件:    
[[email protected] ~]# vim /etc/my.cnf    
增进如下几行,其他不动    
ssl  #拉开服务

# openssl req -newkey rsa:2048 -days 3665-nodes -keyout master-key.pem -out master-req.pem

ssl_ca = /mydata/data/ssl/cacert.pem   #点名下CA文件所在的职分在这里   
ssl_cert = /mydada/data/ssl/mysql.crt   #证书文件的岗位    
ssl_key = /usr/local/mysql/ssl/mysql.key #密钥所在的职位

# openssl rsa -in master-key.pem -out master-key.pem

因为大家事情发生前接连过主服务器端所以那边大家做下校勘设置。

# openssl x509 -req -in master-req.pem-days 3665 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -outmaster-cert.pem

mysql> change master to
-> master_host='172.16.20.6',
-> master_user='yangyaru',
-> master_password='yangyaru',
-> master_log_file='master-bin.***',
-> master_log_pos=***,
-> master_ssl=1,
-> master_ssl_ca='/etc/pki/CA/cacert.pem',
-> master_ssl_cert='/mydata/data/ssl/mysql.crt',
-> master_ssl_key='/mydata/data/ssl/mysql.key';
mysql>start slave;

纠正属主属组

假设现身上面米色的呈现就认证,大家的配置已经生效。

# chown mysql.mysql -R *

mysql>show slave statusG

# chmod 600 *

mysql数据复制的过滤。   
诚如我们不提出主服务器端过滤数据,它会使我们二进制日志不完全,所以大家做试验演示也是不再主服务器端操作只在从服务端举办过滤。)    
m’syql从服务器只复制主服务器的magedudb那些库。    
mysql>show global variables like ‘binlog-%’;    
[[email protected] ~]#vim  /etc/my.cnf

为从服务器生成证书

replicate-do-db =  discuzdb 

# openssl req -newkey rsa:2048 -days 3665-nodes -keyout slave-key.pem -out slave-req.pem

[[email protected] ~]#service mysqld restart

# openssl x509 -req -in slave-req.pem -days3665 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out slave-cert.pem

在从服务器检查下是还是不是   
mysql>show slave statusG;

将CA证书、签定的slave端证书、master证书及私钥传送到slave服务器上

在主服务端创造数据库。   
mysql>create database magedudb;    
mysql>create database discuzdb;

# scp ca-cert.pem slave-key.pemslave-cert.pem  master-cert.pem  master-key.pem 172.16.51.21:/ssl/

在从服务器端看下结果。

校勘证书属主属组

mysql>show databases;

# cd /ssl

#############################################################################

# chown mysql.mysql -R *

MySQL 5.6引进的GTID(Global Transaction IDs卡塔尔(英语:State of Qatar)使得其复制功能的布局、监察和控制及管理变得更加的轻巧落到实处,且更为健康。 

# chmod 600 *

要在MySQL 5.6中使用复制作用,其劳动配置段[mysqld]中于少应该定义如下选项:

编写制定master服务器my.cnf配置文件

1、log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host:用于运转GTID及满足从属的其它部须要求;

# vim /usr/local/mysql/my.cnf              ---增加内容如下

2、master-info-repository、relay-log-info-repository:启用此两项,可用于落到实处在崩溃时保险二进制及从服务器安全的作用;

ssl

3、sync-master-info:启用之可确认保证无新闻遗失;

ssl-ca = /ca/ca-cert.pem

4、slave-paralles-workers:设定从服务器的SQL线程数;0代表关闭八线程复制作而成效;

ssl-cert = /ca/master-cert.pem

5、binlog-checksum、master-verify-checksum、slave-sql-verify-checksum:启用复制有关的富有校验成效;

ssl-key = /ca/master-key.pem

6、binlog-rows-query-log-events:启用之可用于在二进制日志记录事件相关的消息,可降低故障消释的复杂度;

编纂slave服务器my.cnf配置文件

7、log-bin:启用二进制日志,那是保险复制作用的基本前提;

# vim /usr/local/mysql/my.cnf            ---增加内容如下

server-id:同二个复制拓扑中的全部服务器的id号必得惟生机勃勃;   
8、binlog-format:二进制日志的格式,有row、statement和mixed几连串型;

ssl

注意:当设置隔断等第为READ-COMMITED必得设置二进制日志格式为ROW,今后MySQL官方以为STATEMENT这些曾经不再符合继续运用;但mixed类型在私下认可的事体隔绝等级下,恐怕会招致基本数据区别等;

ssl-ca = /ssl/ca-cert.pem

mysql-5.6的配备示范:

ssl-cert = /ssl/slave-cert.pem

1、配置中央节点的服务配置文件

ssl-key = /ssl/slave-key.pem

1.1、配置master节点:

重启mysql服务器查看ssl作用是不是启用

[mysqld]
binlog-format=ROW
log-bin=master-bin.log
log-slave-updates=true
gtid-mode=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
server-id=1
report-port=3306
port=3306
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=master.magedu.com

在master服务器查看SSl成效开启状态

1.2、配置slave节点:

mysql> show variables like '%ssl%';

[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=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
server-id=11
report-port=3306
port=3306
log-bin=mysql-bin.log
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=slave.magedu.com

图片 32

2、成立复制顾客

在slave服务器查看SSl功能开启状态

mysql> GRANT REPLICATION SLAVE ON *.* TO [email protected] IDENTIFIED BY 'yangyaru';

mysql> show variables like '%ssl%';

表达:172.16.20.7是从节点服务器;假使想三遍性授权更加多的节点,能够活动依据需求校正;

图片 33

3、为备节点提供伊始数据集

SSL加密复制验证达成

锁定主表,备份主节点上的数目,将其复苏至从节点;若无启用GTID,在备份时索要在master上选拔show master status命令查看二进制日志文件名称及事件地点,以便前面运行slave节点时行使。

Master服务器授权

4、运转从节点的复制线程

mysql> GRANT FILE,SELECT,REPLICATION SLAVEON*.* TO 'test11'@'%' IDENTIFIED BY 'test11' REQUIRE SSL;

大器晚成旦启用了GTID效率,则使用如下命令:

mysql> FLUSH PRIVILEGES;

mysql> CHANGE MASTER TO MASTER_HOST='master.yangyaru.com', MASTER_USER='yangyaru', MASTER_PASSWORD='yangyaru', MASTER_AUTO_POSITION=1;

Slave服务器连接

没启用GTID,需求利用如下命令:

# mysql -utest11 -ptest11 -h172.16.51.20--ssl-cert=/ssl/master-cert.pem --ssl-key=/ssl/master-key.pem

slave> CHANGE MASTER TO MASTER_HOST='172.16.100.6',

翻看意况消息

-> MASTER_USER='yangyaru',

mysql> status;

-> MASTER_PASSWORD='yangyaru',

图片 34

-> MASTER_LOG_FILE='master-bin.000003',

ssl加密复制相关安排就完事了,具体的落到实处与GTID主从复制验证相似

-> MASTER_LOG_POS=1174;

Mysql5.6半二只安顿

落到实处半联合复制

在主服务器上安装插件

1、分别在宗旨节点上设置相关的插件

mysql> INSTALL PLUGINrpl_semi_sync_master SONAME 'semisync_master.so';

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

修改my.cnf配置文件—增多一下挑选

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

rpl_semi_sync_master_enabled=ON

2、启用半同台复制

在从服务器上安装的插件

在master上的配置文件中,增加

slave> INSTALL PLUGINrpl_semi_sync_slave SONAME 'semisync_slave.so';

rpl_semi_sync_master_enabled=ON

改正从服务器配置文件加多一下接受

在最少二个slave节点的配置文件中增加

rpl_semi_sync_slave_enabled=ON

rpl_semi_sync_slave_enabled=ON

再也启航mysql服务器

从今未来重新起动mysql服务就能够以预知效。

验证半同步效用是不是展开

照旧,也得以mysql服务上动态运维其相关功用:

mysql> CREATE DATABASE magedudb3;

master> SET GLOBAL rpl_semi_sync_master_enabled = ON;

mysql> SHOW GLOBAL STATUS LIKE 'rpl%';

slave> SET GLOBAL rpl_semi_sync_slave_enabled = ON;

图片 35

slave> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

Mysql-5.6读写抽离达成

3、确认半后生可畏并成效已经启用

读写分离达成宗旨机制

master> CREATE DATABASE magedudb;

基本的规律是让主数据库处总管务性查询,而从数据库管理SELECT查询。数据库复制被用来把事务性查询导致的改善同步到集群中的从数据库;主服务器对向从服务器必要的乞请爆发的I/o会超级多。能够实现多种复制;完成机制是在主服务器为从服务器产生二个历程,有一个独自的服务器器为从服务器提供读进度便是多级复制方式;单独提供查询进度服务器接纳black hole存款和储蓄引擎

master> SHOW STATUS LIKE 'Rpl_semi_sync_master_yes_tx';

Mysql-5.6完毕读写分离简构图

slave> SHOW DATABASES; 

图片 36

拓展:

Mysql-proxy配置选项介绍

动用mysql proxy完毕mysql主从服务读写分离功用。   
此处的系列平台为rhel6.4 32人系统,由此就以mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz为例。    
设置配备mysql-proxy:    
[[email protected] ~]# tar xf mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz -C /usr/local

--proxy-address=host:port                       ————代理服务监听的地点和端口;

[[email protected] ~]# cd /usr/local

--admin-address=host:port                       ————管理模块监听的地址和端口;

[[email protected] ~]# ln -sv mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit  mysql-proxy   
为mysql-proxy提供SysV服务脚本    

--proxy-backend-addresses=host:port             ————后端mysql服务器的地址和端口;

[[email protected] ~]vim /etc/rc.d/init.d/mysql-proxy
#!/bin/bash
#
# mysql-proxy This script starts and stops the mysql-proxy daemon
#
# chkconfig: - 78 30
# processname: mysql-proxy
# description: mysql-proxy is a proxy daemon for mysql
# Source function library.
. /etc/rc.d/init.d/functions
prog="/usr/local/mysql-proxy/bin/mysql-proxy"
# Source networking configuration.
if [ -f /etc/sysconfig/network ]; then
. /etc/sysconfig/network
fi
if [ -f /etc/sysconfig/mysql-proxy ]; then
. /etc/sysconfig/mysql-proxy
fi
# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0
# Set default mysql-proxy configuration.
ADMIN_USER=${ADMIN_USER:-admin}
ADMIN_PASSWD=${ADMIN_PASSWD:-""}
PROXY_OPTIONS=${PROXY_OPTIONS:="--daemon"}
PROXY_USER=${PROXY_USER:-"mysql-proxy"}
ADMIN_ADDRESS="${ADMIN_ADDRESS:-0.0.0.0:4040}"
PROXY_ADDRESS="${PROXY_ADDRESS:-0.0.0.0:4041}"
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 --user=$PROXY_USER --admin-username="$ADMIN_USER" --admin-lua-script="$ADMIN_LUA_SCRIPT" --admin-password="$ADMIN_PASSWORD" --admin-address="$ADMIN_ADDRESS" --proxy-address="$PROXY_ADDRESS"
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
[[email protected] ~]# chmod +x /etc/rc.d/init.d/mysql-proxy
[[email protected] ~]# chkconfig --add mysql-proxy

--proxy-read-only-backend-addresses=host:port   ————后端只读mysql服务器的地址和端口;

为服务脚本提供配置文件:

--proxy-lua-script=file_name                    ————实现mysql代理功效的Lua脚本;

[[email protected] ~]# vim  /etc/sysconfig/mysql-proxy
# Options for mysql-proxy
ADMIN_USER="admin"
ADMIN_PASSWORD=""
ADMIN_ADDRESS=""
PROXY_ADDRESS=""
PROXY_USER="mysql-proxy"
PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog"
其中最后一行,需要按实际场景进行修改,例如:
PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=172.16.20.6:3306 --proxy-read-only-backend-addresses=172.16.20.7:3306  --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
说明:
--proxy-address=host:port ———— 代理服务监听的地址和端口;
--admin-address=host:port ———— 管理模块监听的地址和端口;
--proxy-lua-script=file_name ———— 完成mysql代理功能的Lua脚本;
提供续写分离脚本,这个脚本mysql-proxy-0.8.3提供,所以这里我们只需要复制到对应的读取脚本的位置即可。
[[email protected] ~]# vim  /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
--[[ $%BEGINLICENSE%$
Copyright (c) 2007, 2012, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License as
published by the Free Software Foundation; version 2 of the
License.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
02110-1301  USA
$%ENDLICENSE%$ --]]
---
-- a flexible statement based load balancer with connection pooling
--
-- * build a connection pool of min_idle_connections for each backend and maintain
--   its size
-- *
--
--
local commands    = require("proxy.commands")
local tokenizer   = require("proxy.tokenizer")
local lb          = require("proxy.balance")
local auto_config = require("proxy.auto-config")
--- config
--
-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 4,
max_idle_connections = 8,
is_debug = false
}
end
---
-- read/write splitting sends all non-transactional SELECTs to the slaves
--
-- is_in_transaction tracks the state of the transactions
local is_in_transaction       = false
-- if this was a SELECT SQL_CALC_FOUND_ROWS ... stay on the same connections
local is_in_select_calc_found_rows = false
---
-- get a connection to a backend
--
-- as long as we don't have enough connections in the pool, create new connections
--
function connect_server()
local is_debug = proxy.global.config.rwsplit.is_debug
-- make sure that we connect to each backend at least ones to
-- keep the connections to the servers alive
--
-- on read_query we can switch the backends again to another backend
if is_debug then
print()
print("[connect_server] " .. proxy.connection.client.src.name)
end
local rw_ndx = 0
-- init all backends
for i = 1, #proxy.global.backends do
local s        = proxy.global.backends[i]
local pool     = s.pool -- we don't have a username yet, try to find a connections which is idling
local cur_idle = pool.users[""].cur_idle_connections
pool.min_idle_connections = proxy.global.config.rwsplit.min_idle_connections
pool.max_idle_connections = proxy.global.config.rwsplit.max_idle_connections
if is_debug then
print("  [".. i .."].connected_clients = " .. s.connected_clients)
print("  [".. i .."].pool.cur_idle     = " .. cur_idle)
print("  [".. i .."].pool.max_idle     = " .. pool.max_idle_connections)
print("  [".. i .."].pool.min_idle     = " .. pool.min_idle_connections)
print("  [".. i .."].type = " .. s.type)
print("  [".. i .."].state = " .. s.state)
end
-- prefer connections to the master
if s.type == proxy.BACKEND_TYPE_RW and
s.state ~= proxy.BACKEND_STATE_DOWN and
cur_idle < pool.min_idle_connections then
proxy.connection.backend_ndx = i
break
elseif s.type == proxy.BACKEND_TYPE_RO and
s.state ~= proxy.BACKEND_STATE_DOWN and
cur_idle < pool.min_idle_connections then
proxy.connection.backend_ndx = i
break
elseif s.type == proxy.BACKEND_TYPE_RW and
s.state ~= proxy.BACKEND_STATE_DOWN and
rw_ndx == 0 then
rw_ndx = i
end
end
if proxy.connection.backend_ndx == 0 then
if is_debug then
print("  [" .. rw_ndx .. "] taking master as default")
end
proxy.connection.backend_ndx = rw_ndx
end
-- pick a random backend
--
-- we someone have to skip DOWN backends
-- ok, did we got a backend ?
if proxy.connection.server then
if is_debug then
print("  using pooled connection from: " .. proxy.connection.backend_ndx)
end
-- stay with it
return proxy.PROXY_IGNORE_RESULT
end
if is_debug then
print("  [" .. proxy.connection.backend_ndx .. "] idle-conns below min-idle")
end
-- open a new connection
end
---
-- put the successfully authed connection into the connection pool
--
-- @param auth the context information for the auth
--
-- auth.packet is the packet
function read_auth_result( auth )
if is_debug then
print("[read_auth_result] " .. proxy.connection.client.src.name)
end
if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then
-- auth was fine, disconnect from the server
proxy.connection.backend_ndx = 0
elseif auth.packet:byte() == proxy.MYSQLD_PACKET_EOF then
-- we received either a
--
-- * MYSQLD_PACKET_ERR and the auth failed or
-- * MYSQLD_PACKET_EOF which means a OLD PASSWORD (4.0) was sent
print("(read_auth_result) ... not ok yet");
elseif auth.packet:byte() == proxy.MYSQLD_PACKET_ERR then
-- auth failed
end
end
---
-- read/write splitting
function read_query( packet )
local is_debug = proxy.global.config.rwsplit.is_debug
local cmd      = commands.parse(packet)
local c        = proxy.connection.client
local r = auto_config.handle(cmd)
if r then return r end
local tokens
local norm_query
-- looks like we have to forward this statement to a backend
if is_debug then
print("[read_query] " .. proxy.connection.client.src.name)
print("  current backend   = " .. proxy.connection.backend_ndx)
print("  client default db = " .. c.default_db)
print("  client username   = " .. c.username)
if cmd.type == proxy.COM_QUERY then
print("  query             = "        .. cmd.query)
end
end
if cmd.type == proxy.COM_QUIT then
-- don't send COM_QUIT to the backend. We manage the connection
-- in all aspects.
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
}
if is_debug then
print("  (QUIT) current backend   = " .. proxy.connection.backend_ndx)
end
return proxy.PROXY_SEND_RESULT
end
-- COM_BINLOG_DUMP packet can't be balanced
--
-- so we must send it always to the master
if cmd.type == proxy.COM_BINLOG_DUMP then
-- if we don't have a backend selected, let's pick the master
--
if proxy.connection.backend_ndx == 0 then
proxy.connection.backend_ndx = lb.idle_failsafe_rw()
end
return
end
proxy.queries:append(1, packet, { resultset_is_needed = true })
-- read/write splitting
--
-- send all non-transactional SELECTs to a slave
if not is_in_transaction and
cmd.type == proxy.COM_QUERY then
tokens     = tokens or assert(tokenizer.tokenize(cmd.query))
local stmt = tokenizer.first_stmt_token(tokens)
if stmt.token_name == "TK_SQL_SELECT" then
is_in_select_calc_found_rows = false
local is_insert_id = false
for i = 1, #tokens do
local token = tokens[i]
-- SQL_CALC_FOUND_ROWS + FOUND_ROWS() have to be executed
-- on the same connection
-- print("token: " .. token.token_name)
-- print("  val: " .. token.text)
if not is_in_select_calc_found_rows and token.token_name == "TK_SQL_SQL_CALC_FOUND_ROWS" then
is_in_select_calc_found_rows = true
elseif not is_insert_id and token.token_name == "TK_LITERAL" then
local utext = token.text:upper()
if utext == "LAST_INSERT_ID" or
utext == "@@INSERT_ID" then
is_insert_id = true
end
end
-- we found the two special token, we can't find more
if is_insert_id and is_in_select_calc_found_rows then
break
end
end
-- if we ask for the last-insert-id we have to ask it on the original
-- connection
if not is_insert_id then
local backend_ndx = lb.idle_ro()
if backend_ndx > 0 then
proxy.connection.backend_ndx = backend_ndx
end
else
print("   found a SELECT LAST_INSERT_ID(), staying on the same backend")
end
end
end
-- no backend selected yet, pick a master
if proxy.connection.backend_ndx == 0 then
-- we don't have a backend right now
--
-- let's pick a master as a good default
--
proxy.connection.backend_ndx = lb.idle_failsafe_rw()
end
-- by now we should have a backend
--
-- in case the master is down, we have to close the client connections
-- otherwise we can go on
if proxy.connection.backend_ndx == 0 then
return proxy.PROXY_SEND_QUERY
end
local s = proxy.connection.server
-- if client and server db don't match, adjust the server-side
--
-- skip it if we send a INIT_DB anyway
if cmd.type ~= proxy.COM_INIT_DB and
c.default_db and c.default_db ~= s.default_db then
print("    server default db: " .. s.default_db)
print("    client default db: " .. c.default_db)
print("    syncronizing")
proxy.queries:prepend(2, string.char(proxy.COM_INIT_DB) .. c.default_db, { resultset_is_needed = true })
end
-- send to master
if is_debug then
if proxy.connection.backend_ndx > 0 then
local b = proxy.global.backends[proxy.connection.backend_ndx]
print("  sending to backend : " .. b.dst.name);
print("    is_slave         : " .. tostring(b.type == proxy.BACKEND_TYPE_RO));
print("    server default db: " .. s.default_db)
print("    server username  : " .. s.username)
end
print("    in_trans        : " .. tostring(is_in_transaction))
print("    in_calc_found   : " .. tostring(is_in_select_calc_found_rows))
print("    COM_QUERY       : " .. tostring(cmd.type == proxy.COM_QUERY))
end
return proxy.PROXY_SEND_QUERY
end
---
-- as long as we are in a transaction keep the connection
-- otherwise release it so another client can use it
function read_query_result( inj )
local is_debug = proxy.global.config.rwsplit.is_debug
local res      = assert(inj.resultset)
local flags    = res.flags
if inj.id ~= 1 then
-- ignore the result of the USE <default_db>
-- the DB might not exist on the backend, what do do ?
--
if inj.id == 2 then
-- the injected INIT_DB failed as the slave doesn't have this DB
-- or doesn't have permissions to read from it
if res.query_status == proxy.MYSQLD_PACKET_ERR then
proxy.queries:reset()
proxy.response = {
type = proxy.MYSQLD_PACKET_ERR,
errmsg = "can't change DB ".. proxy.connection.client.default_db ..
" to on slave " .. proxy.global.backends[proxy.connection.backend_ndx].dst.name
}
return proxy.PROXY_SEND_RESULT
end
end
return proxy.PROXY_IGNORE_RESULT
end
is_in_transaction = flags.in_trans
local have_last_insert_id = (res.insert_id and (res.insert_id > 0))
if not is_in_transaction and
not is_in_select_calc_found_rows and
not have_last_insert_id then
-- release the backend
proxy.connection.backend_ndx = 0
elseif is_debug then
print("(read_query_result) staying on the same backend")
print("    in_trans        : " .. tostring(is_in_transaction))
print("    in_calc_found   : " .. tostring(is_in_select_calc_found_rows))
print("    have_insert_id  : " .. tostring(have_last_insert_id))
end
end
---
-- close the connections if we have enough connections in the pool
--
-- @return nil - close connection
--         IGNORE_RESULT - store connection in the pool
function disconnect_client()
local is_debug = proxy.global.config.rwsplit.is_debug
if is_debug then
print("[disconnect_client] " .. proxy.connection.client.src.name)
end
-- make sure we are disconnection from the connection
-- to move the connection into the pool
proxy.connection.backend_ndx = 0
end

--daemon                                        ————以守护进程情势运维mysql-proxy;

重启下服务就能够。上边包车型客车测量试验自身就不写了哈。^_^

--keepalive                                     ————在mysql-proxy崩溃时尝试重启之;

本文出自 “杨亚茹yyr” 博客,请必得保留此出处

--log-file=/path/to/log_file_name               ————日志文件名称;

案例拓扑图 650卡塔尔(英语:State of Qatar) this.width=650;" title="iiiiiiiiiiiiiii...

--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               ————进度文件名;

安装mysql-proxy

# useradd -r mysql-proxy

# tar xf mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz-C /usr/local/

# cd /usr/local/

# ln -sv mysql-proxy-0.8.3-linux-glibc2.3-x86-32bitmysql-proxy

增多蒙受变量

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

丰富原委为

exportPATH=$PATH:/usr/local/mysql-proxy/bin

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

为mysql-proxy提供SysV服务脚本以致配备文件mysql-proxy 由于剧本配置文件过长所以就上传到附属类小零器件了

累积实践权限,加进服务调控列表

# cd /etc/init.d/

# chmod +x mysql-proxy

# chkconfig --add mysql-proxy

# chkconfig --level 2345 mysql-proxy on

连带配置文件已经计划实现,未来就可运营mysql-proxy,然后接受另意气风发客商端这里的顾客端IP为172.16.51.77)进行三番两回测量检验

# mysql -uadmin -padmin -h172.16.51.23--port=4041

mysql> select * from backends;

图片 37

读写分离测验

在担任读写的服务器上进行远程顾客授权

mysql> GRANT ALL ON *.* TO 'root'@'%'IDENTIFIED BY 'redhat';

mysql> flush privileges;

在客商端172.16.51.77打开二次写操作

# mysql -uroot -predhat -h172.16.51.23 -e'create database chuangjian;'

# mysql -uadmin -padmin -h172.16.51.23--port=4041

图片 38

在客商端172.16.51.77進展二回读操作

# mysql -uroot -predhat -h172.16.51.23 -e'select user,password from mysql.user;'

图片 39

172.16.51.20服务器辅助读写,所以在率先次开展询问时172.16.51.21服务器只怕响应不到,所以就再一次的开展了询问测量检验有不周的地点还望见谅哦)

正文出自 “邓俊阳的Blog” 博客,请必得保留此出处

...

编辑:关于计算机 本文来源:ssl加密复制以及读写分离,6读写分离

关键词:

  • 上一篇:没有了
  • 下一篇:没有了