MySQL主从复制与读写分离

主从复制原理：
重点：两日志（二进制和中继日志），三线程（dump，sql，I/O），复制方向

两日志：
二进制日志  master
中继日志    slaves
二进制日志  ->复制到 中继日志

三线程：
master：dump线程
slave：I/O线程
       SQL线程
dump线程：
    监听本地二进制日志
    记录I/O线程对应的slavve位置
    同步日志给I/O线程。
I/O线程：
    监听master上的dump线程
    将slave信息发送给master（从服务器位置、日志的position（记录位置），超时时间）
    接收master的dump线程传递过来的更新信息，写入relay-log中
SQL线程：
    监听中继日志
    将中继日志中的更新内容执行到中继的数据库中（保证从库与主库执行相同操作）

1、在每个事务更新数据完成之前，Master在二进制日志（Binary log）记录这些改变。写入二进制日志完成后，Master通知存储引擎批交事务

2、Slave将Master复制到其中继日志（Relay log），首先slave开始一个工作线程（I/O），I/O线程在Master上打开一个普通的连接，然后开始Binlog dump process。Binlog dump process从Master的二进制日志中读取事件，如果已经跟上Master，它会睡眠并等待Master产生新的事件，I/O线程将这些事件刺蛾如中继日志。

3、SQL slave thread（SQL从线程）处理该过程的最后一步，SQL线程从中继日志读取事件，并重放其中的事件而更新slave数据，使其与Master中的数据一致，只要该线程与I/O线程保持一致

注：中继日志通常位于Os缓存中，所以中继日志的开销很小

复制过程有一个很重要的限制，即复制在slave上是串行化，也就说Master上的并行更新操作不能再slave上并行操作

二、主从复制实验

2.1、实验环境

mysql-master 192.168.130.15
mysql-slave 192.168.130.200
mysql-slave 192.168.130.201

2.2、主从服务器时间同步

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

#三台服务器都需要做一下

2.3、修改MySQL的配置文件

主服务器

vim /etc/my.cnf  #修改MySQL主配置文件
 
#下面是配置文件内容
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#character-set-server=utf8
#skip-grant-tables
log_bin=master-bin #开启二进制日志文件
log_slave-updates=true #开启从服务器日志同步
server_id=1 #主服务器id为1

systemctl restart mysqld  #重启服务器

#查看mysql目录下是否生成了二进制文件

生成了

从服务器（192.168.130.200，192.168.130.201）

vim /etc/my.cnf

#修改添加一下server-id
server-id=2  
log-bin=master-bin
relay-log=relay-log-bin  #开启中继日志（从主服务器上同步日志文件记录到本地）
relay-log-index=slave-relay-bin.index #建立index索引

2.4、配置MySQL规则

主服务器（192.168.130.15）

mysql -uroot -p

#在同步数据时，从服务器上的io线程是需要到主服务器去获取数据的，所以我们要先给io授权
grant replication slave on *.* to 'myslave'@'192.168.130.%' identified by '123456';
#刷新立即生效
flush privileges;

查看master日志的当前位置

show master status;

从服务器配置（130.200，130.201）

先配置192.168.130.200

使从服务器指向主服务器

#进入数据库执行下面命令
change master to master_host='192.168.130.15',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604;


#master_host是主服务器ip位置
#master_user是指定主服务器用户
#master_password是主服务器密码
#master_log_file和master_log_pos是主服务器开始的二进制日志名和位置（show master status;在主服务器查看）

start slave;  #开启从
show slave status;  #查看slave情况

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.130.15
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 604
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes   #io线程情况
            Slave_SQL_Running: Yes   #sql线程情况
              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: 604
              Relay_Log_Space: 526
              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: d1914554-f6dc-11ec-9a81-000c29705c21
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version:

Slave_IO_Running: Yes #io线程情况
Slave_SQL_Running: Yes #sql线程情况

当这两条是yes时，就成功同步了

另一台服务器也执行相同操作（192.168.130.201）

2.5、测试

在主服务器上创建库，表并添加数据

mysql> create database yy;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| yy |
+--------------------+
6 rows in set (0.00 sec)

mysql> use yy
Database changed
mysql> create table info(id int(5));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into info values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from info;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)

然后我们去从服务器

主从复制到此就配置完成了

遇到的故障问题

#问题:slave_IO_Running:NO
1、网络不通
2、my.cnf配置文件有问题
3、change中配置出错，密码、file文件名、pos偏移量与master不相符
4、防火墙没有关闭

#问题:slave_SQL_Running:NO
1、程序可能在slave上进行了写操作
2、也可能是slave机器重起后，事务回滚造成的.
执行这个：set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

遇到以上报错的时候，最好把从服务器的slave同步关闭

第一种：

这里主要是因为主数据库中原先表中的test库，从数据库中无法识别
我们只需要删除库，并且重新指向新的日志position位置点就行
或者执行（set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;）跳过这个错误操作就行

删除库后

show master status; #查看最新的master的position位置

在从服务器从新change指向这个位置就行

第二种：

这是因为我们删除库时，从数据库没有这个库，从而无法主从复制，导致的报错

同第一种错误

我们只需要删除库，并且重新指向新的日志position位置点就行
或者执行（set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;）跳过这个错误操作就行

三、读写分离原理

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

3.1、什么是读写分离

读写分离，基本原理是让主数据库处理事务性增、删、改操作（insert，update，delete），而从数据库处理SSELECT，而数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的更变同步到集群中的从数据库。

3.2、为什么要读写分离呢

因为数据库的"写"（写10000条数据可能要3分钟）操偶做是比较耗时的。

但是数据库的"读"（读10000条数据可能只要5秒钟）。

所以读写分离，解决的是，数据库的写入，影响了查询的效率。

3.3、什么时候要读写分离

数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用。利用数据库主从同步，再通过读写分离可以分担数据库压力，提高性能。

3.4、主从复制与读写分离

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

四、MySQL读写分离类型

4.1、基于程序代码内部实现

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

MySQL-Proxy。MySQL-Proxy为MySQL开源项目，通过其自带的lua脚本进行SQLpanduan
Atlas。是由奇虎360的Web平台基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能性特性。360内部使用Atlas运行的mysl业务，每天承载的读写请求数达几十亿条。支持事务以及存储过程。
Amoeba。由陈思睿开发，作者曾就职于阿里巴巴。改程序由java语言进行开发，阿里巴巴将其用于生产环境，但是它不支持事务和存储过程。

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

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

五、读写分离实验部署

在做读写分离时，要将上面的主从复制配置完成后再配置读写分离

5.1、读写分离拓扑图

5.2、实验思路

客户端访问代理服务器
代理服务器写入主服务器
主服务器将增删改改写入自己二进制日志
从服务器将主服务器的二进制日志同步至自己中继日志
从服务器重放中继日志到数据库中
客户端读，则代理服务器直接访问从服务器
降低负载，起到负载均衡的作用

5.3、环境准备

主机	IP地址
Master	192.168.130.15
Slave1	192.168.130.200
Slave2	192.168.130.201
Amoeba	192.168.130.10

实验前需要将四台服务器防火墙关闭

systemctl stop firewalld
systemctl disable firewalld
setenforce 0

5.4、jdk环境安装

做读写分离实验卡按必须有一主两从环境

Ameoba服务是基于jdk1.5开发的，所以要使用jdk1.5或1.6版本，不建议使用高版本

cp jdk-6u14-linux-x64.bin /usr/local
cd /usr/local
chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin #安装jdk

这样就表示安装完成了

mv jdk1.6.0_14  jdk1.6  #为jdk文件改名

配置jdk环境变量

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

##下面两条是配置amoeba服务的，一起写入
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin

source /etc/profile  #刷新环境变量
java -version  #查看刷新后的版本

5.5、安装Amoeba软件

这里提前准备好了amoeba的tar包

mkdir /usr/local/amoeba
tar -zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba
chmod -R 755 /usr/local/amoeba/  #为amoeba目录赋权，让其他主机文件也能访问

#测试
/usr/local/amoeba/bin/amoeba
#如果显示amoeba start|stop则成功安装

5.6配置Amoeba读写分离，两个slave读负载均衡

配置前需要给Master、Slave1和Slave2的MySQL上开放权限给Amoeba访问

grant all on *.* to 'amoeba'@'192.168.130.%' identified by '123456';
flush privileges;  #刷新

5.7、配置Ammoeba

修改全局配置文件

#备份配置文件
cd /usr/local/amoeba/conf
cp amoeba.xml amoeba.xml.bak
cp dbServers.xml dbServers.xml.bak

vim amoeba.xml   #修改配置文件

#这里只要修改30行和115行那块的内容，下图有标

<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd">
<amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">

        <proxy>

                <!-- service class must implements com.meidusa.amoeba.service.Service -->
                <service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager">
                        <!-- port -->
                        <property name="port">8066</property>

                        <!-- bind ipAddress -->
                        <!-- 
                        <property name="ipAddress">127.0.0.1</property>
                         -->

                        <property name="manager">${clientConnectioneManager}</property>

                        <property name="connectionFactory">
                                <bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">
                                        <property name="sendBufferSize">128</property>
                                        <property name="receiveBufferSize">64</property>
                                </bean>
                        </property>

                        <property name="authenticator">
                                <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">

                                        <property name="user">amoeba</property>

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

                                        <property name="filter">
                                                <bean class="com.meidusa.amoeba.server.IPAccessController">
                                                        <property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
                                                </bean>
                                        </property>
                                </bean>
                        </property>

                </service>

                <!-- server class must implements com.meidusa.amoeba.service.Service -->
                <service name="Amoeba Monitor Server" class="com.meidusa.amoeba.monitor.MonitorServer">
                        <!-- port -->
                        <!--  default value: random number
                        <property name="port">9066</property>
                        -->
                        <!-- bind ipAddress -->
                        <property name="ipAddress">127.0.0.1</property>
                        <property name="daemon">true</property>
                        <property name="manager">${clientConnectioneManager}</property>
                        <property name="connectionFactory">
                                <bean class="com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory"></bean>
                        </property>

                </service>

                <runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">
                        <!-- proxy server net IO Read thread size -->
                        <property name="readThreadPoolSize">20</property>

                        <!-- proxy server client process thread size -->
                        <property name="clientSideThreadPoolSize">30</property>

                        <!-- mysql server data packet process thread size -->
                        <property name="serverSideThreadPoolSize">30</property>

                        <!-- per connection cache prepared statement size  -->
                        <property name="statementCacheSize">500</property>

                        <!-- query timeout( default: 60 second , TimeUnit:second) -->
                        <property name="queryTimeout">60</property>
                </runtime>

        </proxy>

        <!-- 
                Each ConnectionManager will start as thread
                manager responsible for the Connection IO read , Death Detection
        -->
        <connectionManagerList>
                <connectionManager name="clientConnectioneManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
                        <property name="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property>
                        <!-- 
                          default value is avaliable Processors 
                        <property name="processors">5</property>
                         -->
                </connectionManager>
                <connectionManager name="defaultManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
                        <property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>

                        <!-- 
                          default value is avaliable Processors 
                        <property name="processors">5</property>
                         -->
                </connectionManager>
        </connectionManagerList>

                <!-- default using file loader -->
        <dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader">
                <property name="configFile">${amoeba.home}/conf/dbServers.xml</property>
        </dbServerLoader>

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


                <property name="writePool">master</property>
                <property name="readPool">slaves</property>

                <property name="needParse">true</property>
        </queryRouter>
</amoeba:configuration>

修改另一个数据库配置文件

vim dbServers.xml

#完整配置文件

<?xml version="1.0" encoding="gbk"?>

<!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd">
<amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/">

                <!-- 
                        Each dbServer needs to be configured into a Pool,
                        If you need to configure multiple dbServer with load balancing that can be simplified by the following configuration:
                         add attribute with name virtual = "true" in dbServer, but the configuration does not allow the element with name factoryConfig
                         such as 'multiPool' dbServer   
                -->

        <dbServer name="abstractServer" abstractive="true">
                <factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
                        <property name="manager">${defaultManager}</property>
                        <property name="sendBufferSize">64</property>
                        <property name="receiveBufferSize">128</property>

                        <!-- mysql port -->
                        <property name="port">3306</property>

                        <!-- mysql schema -->
                        <property name="schema">test</property>

                        <!-- mysql user -->
                        <property name="user">amoeba</property>

                        <!--  mysql password -->
                        <property name="password">123456</property>

                </factoryConfig>

                <poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
                        <property name="maxActive">500</property>
                        <property name="maxIdle">500</property>
                        <property name="minIdle">10</property>
                        <property name="minEvictableIdleTimeMillis">600000</property>
                        <property name="timeBetweenEvictionRunsMillis">600000</property>
                        <property name="testOnBorrow">true</property>
                        <property name="testOnReturn">true</property>
                        <property name="testWhileIdle">true</property>
                </poolConfig>
        </dbServer>

        <dbServer name="master"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">192.168.130.15</property>
                </factoryConfig>
        </dbServer>

        <dbServer name="slave1"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">192.168.130.200</property>
                </factoryConfig>
        </dbServer>

        <dbServer name="slave2"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">192.168.130.201</property>
                </factoryConfig>
        </dbServer>

        <dbServer name="slaves" virtual="true">
                <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>
                </poolConfig>
        </dbServer>

</amoeba:dbServers>

启动amoeba服务

/usr/local/amoeba/bin/amoeba start & #后台启动

netstat -napt | grep java  #查看端口

代表amoeba服务成功启动了

5.8、客户端测试读写分离功能

这里的测试端我们另起了一台服务器192.168.130.102

#安装mariadb
yum install mariadb  -y


#远程登陆amoeba服务器
mysql -uamoeba -p123456 -h 192.1168.130.10 -P8066

出现的报错

amoeba服务端的报错

java.lang.Exception: poolName=slaves, no valid pools
	at com.meidusa.amoeba.net.poolable.MultipleLoadBalanceObjectPool.borrowObject(MultipleLoadBalanceObjectPool.java:183)
	at com.meidusa.amoeba.mysql.handler.CommandMessageHandler.startSession(CommandMessageHandler.java:633)
	at com.meidusa.amoeba.mysql.handler.MySqlCommandDispatcher.handleMessage(MySqlCommandDispatcher.java:123)
......

原因：

amoeba的数据库文件第23行，这里默认对应的MySQL库为text，我这里使用的MySQL版本为5.7版本，默认的库为mysql，所以找不到默认的库，我之前搭建的MySQL版本是5.5是不需要修改的，没注意到这一点

所以我们去修改一下就行

vim /usr/local/amoeba/conf/dbServers.xml

重启一下就行

测试一：主从数据库是否可以同步

在客户端中新建表然后去主数据库查看是否同步

MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| yy |
+--------------------+
5 rows in set (0.00 sec)

MySQL [(none)]> use yy
No connection. Trying to reconnect...
Connection id: 2010296347
Current database: *** NONE ***

Database changed
MySQL [yy]> show tables; #这时的yy库中没有任何东西
Empty set (0.01 sec)

MySQL [yy]> create table info(id int(3)); #我们给它创建一个info表
Query OK, 0 rows affected (0.01 sec)

MySQL [yy]> show tables; #查看一下
+--------------+
| Tables_in_yy |
+--------------+
| info |
+--------------+
1 row in set (0.00 sec)

再去主服务器查看

创建成功了，这里证明通过代理可以连入数据库

测试二：读写分离

我们先将两台从服务器的同步关掉

#进入数据库
stop slave;

两台同步全关了

#客户端
show tables;
insert into info values(5); #插入一条数据
select * from info;

当我们将从服务器同步打开

start slave;

然后再看客户端

因为我们读的话只会读取从服务器，主服务器只作为写，这里真正展示了读写的区别

测试三：是否为轮询查询

我们关闭slave2的同步

在客户端添加一条数据，然后查看

IT.cat

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL主从复制与读写分离

在企业应用中，成熟的业务通常数据量都比较大。单台MySQL在安全性、高可用性和高并发方面都无法满足实际的需求。配置多台主从数据库服务器以实现读写分离。
复制链接

扫一扫

专栏目录