mysql主从复制和读写分离

一、主从复制

1、MySQL主从复制的几个同步模式

1）、异步复制（Asynchronous replication）

默认模式：又称异步模式，主库在更新完事务之后会立即把结果返回给从服务器，并不关心从库是否接受到，以及从库是否处理成功。

网络问题可能没有同步，或者是其他因素的影响导致同步失败。

优点：效率高

2）、全同步复制（Fully synchronous replication）

主库在更新完事务之后，立即把结果返回给从库，所有的从库执行完毕之后才能继续下一个同步。

优点：安全，性能受到影响

3）、半同步复制（Semisynchronous replication）

介乎于异步与全同步之间，主库更新完事务之后，也是同步到从库，同步完成之后有一个等待时间，等待时间是tcp/ip的往返时间5毫秒左右。

特点：既在一定程度上保证了效率，也在一定程度上保证了数据的完整性。

2、架构模式

高可用架构一般都是奇数台，一主两从、二主三从

特点：主可以复制到从，从不可以复制到主

3、主从复制的延迟怎么解决

1、网络问题，防火墙原因

2、硬件设备问题cpu，内存，磁盘出了问题

3、配置文件写错了

4、mysqld服务配置方面

如果从安全的角度上考虑的话，就要设置双一设置

vim /etc/my.cnf
-------------------

追求安全性的双一设置：

innodb_flush_log_at_trx_commit=1
0: 事务提交时不刷新事务日志，而是每秒进行一次日志刷新。这可以提高性能，
但在发生故障时可能会导致数据丢失。

1: 默认值。每次事务提交时都会刷新事务日志，以确保事务持久性。
这提供了最高级别的数据安全性，但可能会影响性能。

2: 事务提交时将事务日志写入操作系统缓存，但不进行物理刷新。
这提供了一定的数据安全性和性能。

sync_binlog=1
0: 二进制日志写入操作系统缓存，但不进行物理刷新。这提供了最高性能，
但在发生故障时可能会导致数据丢失。

1: 默认值。每次事务提交时，将二进制日志刷新到磁盘，以确保日志的持久性。
这提供了较高级别的数据安全性。

N: 每次事务提交时，将二进制日志刷新到磁盘，但最多每 N 个事务执行一次物理刷新。
这在某些情况下可以提高性能，但在发生故障时可能会导致少量数据丢失。


追求性能化设置：
sync_binlog=0:
设置为0表示二进制日志（binary log）写入操作系统缓存，但不进行物理刷新。
这提供了较高的性能，但在系统崩溃或断电时可能会导致未刷新的二进制日志数据的丢失。

innodb_flush_log_at_trx_commit=2:
设置为2表示事务提交时将事务日志写入操作系统缓存，但不进行物理刷新。
这提供了一定的性能提升，但在系统崩溃或断电时可能会导致未刷新的事务日志数据的丢失。

logs-slave-updates=0:
如果设置为0，表示不将从库的更新写入二进制日志。
这对于避免在主从复制中引入循环复制（circular replication）很有用。

增大 innodb_buffer_pool_size:
innodb_buffer_pool_size 控制 InnoDB 存储引擎的缓冲池大小。增大这个值可以提高 InnoDB 的性能，
因为更多的数据和索引可以缓存在内存中，减少了对磁盘的访问次数。但要注意，
设置得太大可能导致系统内存不足。

4、主从复制实现 

实现基于mysql的二进制日志，根据主库的二进制文件的标志位，实现主和从的同步。

架构：三台服务器

Master 服务器： 192.168.10.61

Slave1 服务器： 192.168.10.62

Slave2 服务器： 192.168.10.63

1）、关闭防火墙以及安全机制

systemctl stop firewalld.service
setenforce 0

2）、时间同步

yum -y install ntpdate -y
ntpdate ntp.aliyun.com

3）、 配置主

打开二进制功能

[root@localhost ~]# vim /etc/my.cnf
-----------------------------------
log-bin=master-bin
binlog_format=MIXED
log-slave-updates=ture

三台库上都创建用户myslave

mysql -u root -p
create user 'myslave'@'192.168.10.%' identified with mysql_native_password by '123456';
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.10.%';
FLUSH PRIVILEGES;

 进入主数据库查看状态，获取Position

4）、对从数据库进行操作

对从库1进行配置

vim /etc/my.cnf

server-id = 2
#服务器id不能一致
relay-log = relay-log-bin
#从服务器上获取二进制日志的开头，开启从库的二进制日志
relay-log-index = slave-relay-bin.index
#二进制日志的索引文件的名称
relay_log_recovery = 1
#配置从服务器在启动时是否执行二进制日志的恢复操作（和主库同步），1表示开启

systemctl restart mysqld

对从库2进行配置 

vim /etc/my.cnf

server-id = 3
#服务器id不能一致
relay-log = relay-log-bin
#从服务器上获取二进制日志的开头，开启从库的二进制日志
relay-log-index = slave-relay-bin.index
#二进制日志的索引文件的名称
relay_log_recovery = 1
#配置从服务器在启动时是否执行二进制日志的恢复操作（和主库同步），1表示开启

systemctl restart mysqld

5）、 检查 判断主从同步是否正常

进入两个从数据库，查看IO 从库和主机的读写通信是否正常

show slave status\G

二、读写分离

1、读写分离概述

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

2、 MySQL 读写分离分两种方式

1）基于程序代码内部实现 在代码中根据 select、insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。

优点是性能较好，因为在程序代码中实现，不需要增加额外的设备为硬件开支；

缺点是需要开发人员来实现，运维人员无从下手。

但是并不是所有的应用都适合在程序代码中实现读写分离， 像一些大型复杂的Java应用，如果在程序代码中实现读写分离对代码改动就较大。

2）基于中间代理层实现 代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库， 有以下代表性程序。 MySQL-Proxy。MySQL-Proxy 为 MySQL 开源项目，通过其自带的 lua 脚本进行SQL 判断。

3、读写分离的实现

读写分类在主从复制的基础之上实现的

架构：三台服务器

Master 服务器： 192.168.10.61

Slave1 服务器： 192.168.10.62

Slave2 服务器： 192.168.10.63

Amoeba 服务器：192.168.10.10 jdk1.6、Amoeba

客户端 服务器：192.168.10.20 mysql

1）、关闭防火墙以及安全机制

systemctl stop firewalld.service
setenforce 0

2）、时间同步

yum -y install ntpdate -y
ntpdate ntp.aliyun.com

3）、 主服务器的mysql配置

vim /etc/my.cnf
server-id = 1
log-bin=master-bin							#添加，主服务器开启二进制日志
binlog_format = MIXED
log-slave-updates=true						#添加，允许slave从master复制数据时可以写入到自己的二进制日志

systemctl restart mysqld

创建用户

CREATE USER 'myslave'@'192.168.10.%' IDENTIFIED WITH mysql_native_password BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.10.%';		
#给从服务器授权，能够从主服务器获取日志更新

FLUSH PRIVILEGES;

 进入主数据库查看状态，获取Position

show master status;
//如显示以下
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |    603   |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

#File 列显示日志名，Position 列显示偏移量

4）、从服务器的mysql配置

vim /etc/my.cnf
server-id = 2								#修改，注意id与Master的不同，两个Slave的id也要不同
relay-log=relay-log-bin						
relay-log-index=slave-relay-bin.index
relay_log_recovery = 1
systemctl restart mysqld

进入数据库

mysql -u root -p123456

CHANGE master to master_host='192.168.10.61',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=3738;					
#配置同步，注意 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				#负责自己的slave mysql进程

5）、验证主从复制效果

主服务器上进入执行 create database db_test;

去从服务器上查看 show databases;

6）、Amoeba服务器配置

安装 Java 环境

因为 Amoeba 基于是 jdk1.5 开发的，所以官方推荐使用 jdk1.5 或 1.6 版本，高版本不建议使用。

cd /opt/
cp jdk-6u14-linux-x64.bin /usr/local/
cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin
//按yes，按enter

mv jdk1.6.0_14/ /usr/local/jdk1.6

vim /etc/profile
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin

source /etc/profile
java -version

 7）、安装 Amoeba软件

mkdir /usr/local/amoeba
tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod -R 755 /usr/local/amoeba/
/usr/local/amoeba/bin/amoeba
//如显示amoeba start|stop说明安装成功

 8）、配置 Amoeba读写分离，两个 Slave 读负载均衡

#先在Master、Slave1、Slave2 的mysql上开放权限给 Amoeba 访问

CREATE USER 'amoeba'@'192.168.233.%' IDENTIFIED WITH mysql_native_password BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'amoeba'@'192.168.233.%';

GRANT ALL PRIVILEGES ON *.* TO 'amoeba'@'192.168.233.%';

flush privileges;

#再回到amoeba服务器配置amoeba服务：
cd /usr/local/amoeba/conf/

cp amoeba.xml amoeba.xml.bak
vim amoeba.xml									#修改amoeba配置文件
--30行--
<property name="user">amoeba</property>

--32行-- 
<property name="password">123456</property>

--115行--
<property name="defaultPool">master</property>

--117-去掉注释-
<property name="writePool">master</property>
<property name="readPool">slaves</property>

cp dbServers.xml dbServers.xml.bak
vim dbServers.xml								#修改数据库配置文件
--23行--注释掉  作用：默认进入test库 以防mysql中没有test库时，会报错
<!-- <property name="schema">test</property> -->

--26--修改
<property name="user">amoeba</property>

--28-30--去掉注释
<property name="password">123456</property>

--45--修改，设置主服务器的名Master
<dbServer name="master"  parent="abstractServer">

--48--修改，设置主服务器的地址
<property name="ipAddress">192.168.233.21</property>

--52--修改，设置从服务器的名slave1
<dbServer name="slave1"  parent="abstractServer">

--55--修改，设置从服务器1的地址
<property name="ipAddress">192.168.233.22</property>

--58--复制上面6行粘贴，设置从服务器2的名slave2和地址
<dbServer name="slave2"  parent="abstractServer">
<property name="ipAddress">192.168.233.23</property>

--65行--修改
<dbServer name="slaves" virtual="true">

--71行--修改
<property name="poolNames">slave1,slave2</property>


/usr/local/amoeba/bin/amoeba start &					#启动Amoeba软件，按ctrl+c 返回

查看端口

netstat -anpt | grep java                            #查看8066端口是否开启，默认端口为TCP 8066 

9）、 验证读写分离

yum install -y mariadb-server mariadb
systemctl start mariadb.service

#在主从服务器上开启查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

在客户端服务器上测试：
mysql -u amoeba -p123456 -h 192.168.10.20 -P8066		
//通过amoeba服务器代理访问mysql ，在通过客户端连接mysql后写入的数据只有主服务会记录，然后同步给从--从服务器

在主服务器上：
use db_test;
create table test (id int(10),name varchar(10),address varchar(20));

在两台从服务器上：
stop slave;											#关闭同步
use db_test;
//在slave1上：
insert into test values('1','zhangsan','this_is_slave1');

//在slave2上：
insert into test values('2','lisi','this_is_slave2');

//在主服务器上：
insert into test values('3','wangwu','this_is_master');

//在客户端服务器上：
use db_test;
select * from test;		//客户端会分别向slave1和slave2读取数据，显示的只有在两个从服务器上添加的数据，没有在主服务器上添加的数据

insert into test values('4','qianqi','this_is_client');		//只有主服务器上有此数据

//在两个从服务器上执行 start slave; 即可实现同步在主服务器上添加的数据
start slave;

三、总结

1、主从同步复制原理

从库生成一个I/O线程，一个SQL线程，I/O线程去请求主库的二进制（bunkog）日志文件，SQL线程将获取到的日志文件解析成具体操作，从而实现主从操作的一致，最终实现数据的一致。

2、读写分离使用什么方式

amoeba 代理 mycat 代码 sql_proxy 通过amoeba代理服务器，实现只在主服务器上写，只在从服务器上读； 主数据库处理事务性查询，从数据库处理select 查询； 数据库复制被用来把事务查询导致的变更同步的集群中的从数据库

3、如何查看主从同步状态是否成功

在从服务器上内输入 show slave status\G 查看主从信息查看里面有IO线程的状态信息，还有master服务器的IP地址、端口事务开始号。 当 Slave_IO_Running和Slave_SQL_Running都是YES时 ，表示主从同步状态成功

4、如果I/O不是yes呢，你如何排查？

首先排查网络问题，使用ping 命令查看从服务器是否能与主服务器通信

再查看防火墙和核心防护是否关闭（增强功能）

接着查看从服务slave是否开启 两个从服务器的server-id 是否相同导致只能连接一台 master_log_file master_log_pos的值跟master值是否一致

5、show slave status能看到哪些信息（比较重要）

O线程的状态信息 master服务器的IP地址、端口、事务开始的位置

最近一次的错误信息和错误位置

最近一次的I/O报错信息和ID

最近一次的SQL报错信息和id

6、主从复制慢（延迟）会有哪些可能？怎么解决？

主服务器的负载过大，被多个睡眠或 僵尸线程占用 导致系统负载过大，从库硬件比主库差，导致复制延迟 主从复制单线程，如果主库写作并发太大，来不及传送到从库，就会到导致延迟 慢sql语句过多 网络延迟

mysql主从复制

若主从版本不一致，从的版本一定要高于主，保证可以向下兼容 因为若主的版本更新，低版本的从无法兼容的。