在实际的生产环境中，如果对数据库的读和写都在同一个数据库服务器中操作，无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，一般来说都是通过主从复制(Master-Slave)来同步数据，再通过读写分离来提升数据库并发负载能力的方案来进行部署与实施。
一、MySQL 主从复制原理
MySQL的主从复制和 MySQL的读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离。
1.MySQL 支持的复制类型
（1）基于语句的复制：
在主服务器上执行的 SQL语句，在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制，效率比较高。
配置：binlog_format = STATEMENT
（2）基于行的复制：
把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
配置：binlog_format = ROW
（3）混合类型的复制：
默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。
配置：binlog_format = MIXED
2.主从复制的工作过程
- 在每个事务更新数据完成之前，Master将这些改变记录进二进制日志。写入二进制日志完成后，Master通知存储引擎提交事务。
- Slave 将 Master 的 Binary log 复制到其中继日志(Relay log)。首先，Slave 开始一个工作线程--I/0 线程，I/O 线程在 Master 上打开一个普通的连接，然后开始 Binlogdump process。Binlog dump process 从 Master 的二进制日志中读取事件，如果已经跟上Master，它会睡眠并等待 Master 产生新的事件。I/0 线程将这些事件写入中继日志。
- SQL slave thread(SQL 从线程)处理该过程的最后一步。SQL 线程从中继日志读取事件，并重放其中的事件而更新 Slave 数据，使其与 Master 中的数据保持一致。只要该线程与 I/O 线程保持一致，中继日志通常会位于 OS 的缓存中，所以中继日志的开销很小。复制过程有一个很重要的限制，即复制在 Slave 上是串行化的，也就是说 Master 上的并行更新操作不能在 Slave 上并行操作。
二、主从复制案例
1.关闭所有服务器的firewalld
[root@localhost ~]# setenforce 0
[root@localhost ~]# systemctl stop firewalld
2.建立时间同步环境
[root@localhost ~]# yum -y install ntp
[root@localhost ~]# vi /etc/ntp.conf
添加：
server 127.127.1.0
fudge 127.127.1.0 stratum 8
[root@localhost ~]# systemctl restart ntpd
[root@localhost ~]# systemctl enable ntpd
3.在从节点上进行时间同步
[root@localhost ~]# yum -y install ntp
[root@localhost ~]# ntpdate 192.168.10.102
4.在所有服务器上安装mysql数据库
步骤略
5.配置mysql master主服务器
[root@localhost ~]# vi /etc/my.cnf
在[mysqld]模块中修改或添加：
server-id=11 ##修改
log-bin=master-bin ##修改
log-slave-updates=true ##添加（可不用添加）
binlog-format = MIXED
binlog_cache_size = 1M#日志缓存的大小expire_logs_days=3 #自动过期清理日志的天数
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -u root -p
mysql> grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by '123456' ;
mysql> flush privileges;
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 337 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
6.从服务器的配置
[root@localhost ~]# vi /etc/my.cnf
在[mysqld]模块中修改或添加：
server-id = 22 ##修改，值不能和其他mysql服务器重复
relay-log=relay-log-bin ##添加（可不指定）
relay-log-index=slave-relay-bin.index ##添加（可不指定）
log-slave-updates=true #Slave可以是其他 Slave 的 Master，从而扩散 Master 的更新
binlog-ignore-db=test #不记录指定的数据库的二进制日志 replicate-ignore-db=test #设置不需要同步的库
注释：
--relay-log=name 中继日志的文件的名字在启动时需要检查relay log index 文件中的relay log信息，此处定义该索引文件的名字
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -u root -p
mysql> change master to master_host='192.168.10.101',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=337;
注释： Slave 的 IO 线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的Relay Log文件(relay-log-bin.xxxxxx)的最末端，并将读取到的Master端的master-bin的文件名和位置记录到master- info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个master-bin的哪个位置开始往后的日志内容，请发给我
mysql> start slave;
mysql> show slave status\G##注意后面不要加分号
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.102
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 411
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 284
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: 411
Relay_Log_Space: 461
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: 11
Master_UUID: 167be460-3d4d-11e8-ad42-000c29ae7f64
Master_Info_File: /usr/local/mysql/data/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:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
备注：如果后面加了分号，显示的最后一行会提示ERROR: No query specified，当然，这没有任何影响。
7.验证主从复制
（1）在主从服务器上分别查询数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
（2）在主服务器上创建数据库
mysql> create database db_test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_test |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
（3）在从服务器上再次查询数据库，可以看到从服务器上也有了db_test数据库了
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_test |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
三、扩展：主主复制
如果需要将一个slave1服务器作为另一台slave2的master，
1.在slave1上修改my.cnf
在[mysqld]模块添加
server-id=11
log-bin=master-bin
log-slave-updates=true
并重启mysql
2.在slave1上执行以下命令创建一个授权用户，用于在slave2上链接slave1
mysql> grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by '123456' ;
mysql> flush privileges;
mysql> show master status;
3.show出来的信息做为slave2上连接slave1时的参数
重启Mysql服务不会影响主从关系
四、MySQL读写分离原理
简单来说，读写分离就是只在主服务器上写，只在从服务器上读。基本的原理是让主数据库处理事务性查询，而从数据库处理select 查询。数据库复制被用来把主数据库上事务性查询导致的变更同步到集群中的从数据库。
目前较为常见的 MySQL 读写分离分为两种：
1.基于程序代码内部实现
在代码中根据 select、insert 进行路由分类,这类方法也是目前生产环境应用最广泛的。优点是性能较好，因为在程序代码中实现，不需要增加额外的设备作为硬件开支;缺点是需要开发人员来实现，运维人员无从下手。
2.基于中间代理层实现
代理一般位于客户端和服务器之间,代理服务器接到客户端请求后通过判断后转发到后端数据库，有两个代表性程序：
（1）MySQL-Proxy
MySQL-Proxy 为 MySQL 开源项目，通过其自带的 lua 脚本进行SQL 判断，虽然是 MySQL 官方产品，但是 MySQL 官方并不建议将 MySQL-Proxy 用到生产环境。
（2）Amoeba
由陈思儒开发，作者曾就职于阿里巴巴。该程序由 Java 语言进行开发,阿里巴巴将其用于生产环境。它不支持事务和存储过程。
经过上述简单的比较，通过程序代码实现 MySQL 读写分离自然是一个不错的选择，但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的Java 应用，如果在程序代码中实现读写分离对代码改动就较大。所以，像这种应用一般会考虑使用代理层来实现。
五、搭建MySQL读写分离
Amoeba(变形虫)项目开源框架于 2008 年发布一款 Amoeba for MySQL 软件。这个软件致力于 MySQL的分布式数据库前端代理层，它主要为应用层访问 MySQL 的时候充当SQL路由功能，并具有负载均衡、高可用性、SQL过滤、读写分离、可路由到相关的目标数据库、可并发请求多台数据库。通过 Amoeba 能够完成多数据源的高可用、负载均衡、数据切片的功能，目前 Amoeba 已在很多企业的生产线上使用，其版本可在官网进行下载。
1.在主机amoeba上安装java环境
[root@localhost ~]# chmod +x jdk-6u14-linux-x64.bin
[root@localhost ~]# ./jdk-6u14-linux-x64.bin
[root@localhost ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6
[root@localhost ~]# vi /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:$JAVA_HOME/bin
export AMOEBA_HOME=/usr/local/amoeba/
export PATH=$PATH:$AMOEBA_HOME/bin
[root@localhost local]# source /etc/profile
[root@localhost local]# java -version ##查询版本，确定java安装成功
2.安装并配置amoeba
[root@localhost local]# mkdir /usr/local/amoeba
[root@localhost ~]# tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@localhost ~]# chmod -R 755 /usr/local/amoeba/
[root@localhost ~]# /usr/local/amoeba/bin/amoeba
amoeba start|stop ##有此提示表示成功
3.配置amoeba读写分离
（1）在三个mysql服务器中开放权限给amoeba访问
只在master中即可，会复制到slave中
mysql> grant all on *.* to 'test'@'192.168.10.%' identified by '123.com';
（2）在amoeba上配置amoeba.xml文件
[root@localhost amoeba]# systemctl stop firewalld
[root@localhost ~]# cd /usr/local/amoeba/conf
[root@localhost conf]# vi amoeba.xml
修改红色部分，此处设置的是mysql客户端连接amoeba时用的账号和密码
<property name="authenticator">
<bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
<property name="user">amoeba</property> ##30行
<property name="password">123456</property> ##32行
<property name="filter">
<bean class="com.meidusa.amoeba.server.IPAccessController">
<property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
</bean>
</property>
</bean>
</property>
。。。。。。。。略。。。。。。。
<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
<property name="ruleLoader">
<bean class="com.meidusa.amoeba.route.TableRuleFileLoader">
<property name="ruleFile">${amoeba.home}/conf/rule.xml</property>
<property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>
</bean>
</property>
<property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
<property name="LRUMapSize">1500</property>
<property name="defaultPool">master</property> ##115行
<property name="writePool">master</property> ##118行
<property name="readPool">slaves</property> ##119行此处的注释去掉
<property name="needParse">true</property>
</queryRouter>
（3）编辑dbServer.xml文件
[root@localhost conf]# vi dbServers.xml
修改（注意去掉注释），slave2的复制一个slave1
<!-- mysql user -->
<property name="user">test</property> ##26行
<property name="password">123.com</property> ##29行，去掉注释符
</factoryConfig>
。。。。。。。。。略。。。。。。。。。。
<dbServer name="master" parent="abstractServer"> ##45行
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.1.101</property> ##48行
</factoryConfig>
</dbServer>
<dbServer name="slave1" parent="abstractServer"> ##52行
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.1.102</property> ##55行
</factoryConfig>
</dbServer>
<dbServer name="slave2" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.1.103</property>
</factoryConfig>
</dbServer>
<dbServer name="slaves" virtual="true"> ##59行
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
<property name="loadbalance">1</property>
<!-- Separated by commas,such as: server1,server2,server1 -->
<property name="poolNames">slave1,slave2</property> ##65行
</poolConfig>
</dbServer>
（4）启动amoeba软件
[root@localhost ~]# cd /usr/local/amoeba/
[root@localhost amoeba]# bin/amoeba start&
注：当在前台运行某个作业时,终端被该作业占据;而在后台运行作业时,它不会占据终端。可以使用&命令把作业放到后台执行
[root@localhost amoeba]# netstat -anpt | grep java
如果能看到8066和3306两个端口号，证明amoeba是正常开启的。
4.测试
（1）在client上
[root@localhost yum.repos.d]# yum -y install mariadb
[root@localhost yum.repos.d]# mysql -u amoeba -p 123456 -h 192.168.10.104 -P 8066
Enter password: ##密码：123456
（2）在master服务器上创建表
mysql> stop slave;
MySQL [test]> use auth
MySQL [auth]> create table users (id int(10),name char(20));
（3）在两个slave服务器上
mysql> stop slave;
（4）在master服务器上
mysql> insert into users values ('1','zhangsan');
（5）在slave1上
mysql> use auth;
mysql>insert into users values ('2','zhangsan');
（6）在slave2上
mysql> use auth;
mysql>insert into users values ('3','zhangsan);
（7）在client上查询三次
mysql> use auth;
mysql> select * from users;
对比三次的输出，验证读操作，发现没有在master写入的数据，而slave上写的能查到
（8）在client上
mysql> use auth;
mysql>insert into users values ('4','zhangsan');
mysql> select * from users; ##发现在client上查询不到自己写的数据
（9）在master上
mysql> select * from users; ##能查到在client上写入的数据，说明写操作在master上
（10）在slave上
mysql> select * from users; ##发现没有数据，说明写入的操作是在master上
797
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
