mysql主从复制与读写分离

在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。

二. mysql支持的复制类型

1 STATEMENT

基于语句的复制。在服务器上执行sql语句，再从服务器上执行同样的语句，mysql默认采用基于语句的复制，执行效率高。

2 ROW

基于行的复制。把改变的内容复制过去，而不是把命令再从服务器上执行一遍。

3 MIXED

混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

三. 主从复制的工作过程

mysql主服务器数据跟新写入二进制日志binlog，接着msql从服务器上的I/O线程向主库请求二进制事件，
mysql主服务器启动dump线程向从库发送二进制事件，mysql从服务器写入到中继日志中，sql线程读取中继日志中的二进制事件，
接析成sql语句在本地重放，使得从库数据和主句保持一致。

四. MySQL读写分离原理

读写分离就是只在主服务器上写，只在从服务器上读。基本的原理是让主数据库处理事务性操作，而从数据库处理select查询。数据库复制被用来把主数据库上事务性操作导致的变更同步到集群中的从数据库。

1 目前较为常见的MySQL读写分离

1.1 基于程序代码内部实现

在代码中根据select、insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。
优点是性能较好，因为在程序代码中实现，不需要增加额外的设备为硬件开支; 缺点是需要开发人员来实现，运维人员无从下手。
但是并不是所有的应用都适合在程序代码中实现读写分离,像一些大型复杂的Java应用, 如果在程序代码中实现读写分离对代码改动就较大

1.2 基于中间代理层实现

代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有以下代表性程序。

MySQL-Proxy。MySQL-Proxy为MySQL开源项目，通过其自带的lua脚本进行SQL判断。
Atlas。是由奇虎360的web平台部基础架构团队开发维护的一个基于MySQI协议的数据中间层项目。它是在mysql -proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysq1业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。
Amoeba。由陈思儒开发，作者曾就职于阿里巴巴。该程序由Java语言进行开发，阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。
Mycat。是一款流行的基于Java语言编写的数据库中间件，是一个实现了MySq1协议的服务器，其核心功能是分库分表。配合数据库的主从模式还可以实现读写分离。

由于使用MySQL Proxy 需要写大量的Lua脚本，这些Lua并不是现成的，而是需要自己去写。这对于并不熟悉MySQL Proxy 内置变量和MySQL Protocol的人来说是非常困难的。

Amoeba是一个非常容易使用、可移植性非常强的软件。因此它在生产环境中被广泛应用于数据库的代理层，

五. 搭建MySQL主从复制

1 Mysq1主从服务器时间同步

1.1 主服务器设置

yum install ntp -y

vim /etc/ntp.conf

##--末尾添加--##

server 127.127.200.0 #设置本地是时钟源，注意修改网段

fudge 127.127.200.0 stratum 8 #设置时间层级为8 (限制在15内)

service ntpd start

1.2 从服务器设置

yum install ntp ntpdate -y

service ntpd start

/usr/sbin/ntpdate 192.168.200.100

#进行时间同步

crontab -e

*/30 * * * * /usr/sbin/ntpdate 192.168.200.100

5.2 主服务器的mysq1配置

vim /etc/my.cnf

server-id = 11

log-bin=mysql-bin #添加，主服务器开启二进制日志

binlog_format = MIXED

log-slave-updates=true #添加，允许slave从master复制数据时可以写入到自己的二进制日志 expire_logs_days = 7 #设置二进制日志文件过期时间，默认值为0，表示logs不过期 max_binlog_size = 500M #设置二进制日志限制大小，如果超出给定值，日志就会发生滚动，默认值是1GB

# 在进行n次事务提交以后，Mysql将执行一次fsync的磁盘同步指令。将缓冲区数据刷新到磁盘。

# 为0的话由Mysql自己控制频率。

sync_binlog=1

# 为0的话，log buffer将每秒一次地写入log file中并且刷新到磁盘。

# mysqld进程崩溃会丢失一秒内的所有事务。

# 为1的话，每次事务log buffer会写入log file并刷新到磁盘。（较为安全）

# 在崩溃的时候,仅会丢失一个事务。

# 为2的话，每次事务log buffer会写入log file，但一秒一次刷新到磁盘 innodb_flush_logs_at_trx_commit=1

# 阻止从库崩溃后自动启动复制，给一些时间来修复可能的问题，

# 崩溃后再自动复制可能会导致更多的问题。并且本身就是不一致的

skip_slave_start=1

systemctl restart mysqld

mysql -u root -pabc123

#给从服务器授权

GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.200.%' IDENTIFIED BY '123456';

FLUSH PRIVILEGES;

show master status;

##如显示以下

2.1 mysql双一设置

mysql的"双1验证"指的是innodb_flush_log_at_trx_commit和sync_binlog两个参数设置，这两个是是控制MySQL磁盘写入策略以及数据安全性的关键参数。下面从参数含义，性能，安全角度阐述两个参数为不同的值时对db性能,数据的影响。

参数意义

innodb_flush_log_at_trx_commit:

如果innodb_flush_log_at_trx_commit设置为0: log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行,该模式下，在事务提交的时候,不会主动触发写入磁盘的操作;
如果innodb_flush_log_at_trx_commit设置为1:每次事务提交时MySQL都会把log buffer的数据写入log file, 并且flush(刷到磁盘)中去;
如果innodb_flush_log_at_trx_commit设置为2:每次事务提交时MySQL都会把log buffer的数据写入log file,但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次flush(刷到磁盘)操作。

注意:由于进程调度策略问题,这个"每秒执行一次flush(刷到磁盘)操作"并不是保证100%的"每秒"。

sync_binlog：

sync_binlog 的默认值是0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。
当sync_binlog =N (N>0) , MySQL在每写N次二进制日志binary log时, 会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

注意:如果启用了autocommit,那么每一个语句statement就会有一-次写操作;否则每个事务对应一个写操作。

安全

当innodb_flush_log_at_trx_commit和sync_binlog 都为1时是最安全的，在mysqld 服务崩溃或者服务器主机craph的情况下，binary log只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得，双1会导致频繁的io操作，因此该模式也是最慢的一种方式。

当innodb_flush_log_at_trx_commit设置为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。

当innodb_flush_log_at_trx_commit设置为2,只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。

"双1设置"适合数据安全性要求非常高，而且磁盘I/O写能力足够支持业务，比如订单,交易,充值,支付消费系统。

双1模式下，当磁盘I0无法满足业务需求时比如11.11活动的压力。推荐的做法是innodb_flush_log_at_trx_commit=2，sync_binlog=N (N为500或1000)且使用带蓄电池后备电源的缓存cache,防止系统断电异常。

推荐使用：

innodb_flush_logs_at_trx_commit=2

sync_binlog=500 //性能会较快

innodb_flush_logs_at_trx_commit=1

sync_binlog=1 //较为安全

5.3 从服务器的mysql配置

vim /etc/my.cnf

server-id = 22 #修改，注意id与Master的不同，两个slave的id也要不同

relay-log=relay-log-bin #添加，开启中继日志，从主服务器上同步日志文件记录到本地

relay-log-index=slave-relay-bin.index #添加，定义中继日志文件的位置和名称，一般和relay-log在同一目录

relay_log_recovery = 1 #选配项

#当slave 从库宕机后，假如relay-log 损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的 relay-log，并且重新从master 上获取日志，这样就保证了relay-log 的完整性。

#默认情况下该功能是关闭的，将 relay_log_recovery 的值设置为1时，可在 slave从库上开启该功能，建议开启。

systemctl restart mysqld

mysql -u root -pabc123

CHANGE master to master_host='192.168.200.100',master_user='myslave' ,master_password='123456',master_log_file='mysql-bin.000006', master_log_pos=1054;

#配置同步，注意master_log_file 和master_log_pos的值要与Master查询的一致

start slave; #启动同步，如有报错执行reset slave;

show slave status\G #查看Slave状态

-------------------------------------------------------------------------------

##确保IO和SQL线程都是Yes，代表同步正常。

Slave_IO_Running: Yes

#负责与主机的io通信

Slave_SQL_Running: Yes

#负责自己L的slave mysq1进程

#一般Slave_IO_Running: No的可能性:

#1. 网络不通

#2. my.cnf配置有问题

#3. 密码、file文件名、pos偏移量不对

#4. 防火墙没有关闭

六. 搭建MySQL读写分离

1 实验环境

master服务器: 192.168.200.100 安装mysql5.7

Slave1服务器: 192.168.200.101 安装mysql5.7

Slave2服务器: 192.168.200.102 安装mysql5.7

Amoeba服务器: 192.168.200.120 安装jdk1.6、Amoeba

客户端服务器： 192.168.200.130 安装mysql

2 实验步骤

2.1 master服务器配置

mysql -u root -pabc123

#授权给amoeba服务器

GRANT ALL ON *.* TO 'amoeba'@'192.168.200.%' IDENTIFIED BY '123456';

2.2 Slave服务器配置

mysql -u root -pabc123

#授权给amoeba服务器

GRANT ALL ON *.* TO 'amoeba'@'192.168.200.%' IDENTIFIED BY '123456';

2.3 Amoeba服务器配置

将amoeba-mysql-binary-2.2.0.tar.gz、jdk-6u14-linux-x64.bin上传到/opt目录下

配置 jbk1.6.0_14

cp /opt/jdk-6u14-linux-x64.bin /usr/local

cd /usr/local

chmod +x jdk-6u14-linux-x64.bin //添加可执行权限

./jdk-6u14-linux-x64.bin //执行软件，全部空格，输入yes

vim /etc/profile //修改环境变量

export JAVA_HOME=/usr/local/jdk1.6.0_14

export JRE_HOME=$JAVA_HOME/jre

export CLASSPATH=.:$JAVA_HOME/lib:$JRE_HOME/lib

export PATH=$JAVA_HOME/bin:$JRE_HOME/bin:$PATH

source /etc/profile

java -version

安装amoeba

cd /usr/local

mkdir amoeba

cd /opt

tar xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba

cd /usr/local/amoeba

vim /etc/profile

export AMOEBA_HOME=/usr/local/amoeba

export PATH=$JAVA_HOME/bin:$JRE_HOME/bin:$AMOEBA_HOME/bin:$PATH

source /etc/profile

./bin/amoeba

配置amoeab.xml文件

cd /usr/local/amoeba/conf

cp amoeba.xml amoeba.xml.bak //备份一下

vim amoeba.xml

##---30行登录amoeba的账户、密码----##

<property name="user">root</property> ##可以自行设置

<property name="password">abc123</property>

##--115行--##

<property name="defaultPool">master</property>

##--118--去除注释##

<property name="writePool">master</property> ##写，指定服务器池别名

##--119--##

<property name=" readPool">slaves</property> ##读，指定服务器池别名

配置db.Servers.xml文件

cp dbServers.xml dbServers.xml.bak ##备份

vim dbServers.xml

##--23--注释掉这一行，这一行是默认进入test库##



<property name="ipAddress">192.168.200.100</property>

</factoryConfig>

</dbServer>

<dbServer name="slave1" parent=" abst ractServer"> #设置slave1

<factoryConf1g>



<property name="ipAddress">192.168.200.101</property>

</factoryConf1g>

</dbServer>

<dbServer name="slave2" parent="abst ractServer"> #设置slave2

<factoryConfig>



<property name="ipAddress">192.168.200.102</property>

</factoryConfig>

</dbServer>

##--65行--##

<dbServer name="slaves" virtual="true"> ##地址池别名也需要修改

<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">



<property name="loadbalance">1/property> ##可以选择调度策略



<property name="poolNames">slave1, slave2</property> ##设置slave服务

</poolConfig>

</dbServer>

启动amoeba服务

cd /usr/local/amoeba/bin

./amoeba start & //把amoeba服务放在后台运行

netstat -lntp | grep :8066 //查看amoeba服务端口

2.4 服务端测试

远程登录amoeba服务器

mysql -u can -pabc123 -h 192.168.200.120 -P 8066

use can

show tables;

create table can01(id int,name char(50));

insert into can01 values (1,'zhangsan');

select * from can01;