MySQL主从复制与读写分离

精选原创

当前无昵称 2024-07-01 12:27:45 博主文章分类：数据库管理与高可用 ©著作权

文章标签 mysql 服务器 MySQL 文章分类 云服务云计算

©著作权归作者所有：来自51CTO博客作者当前无昵称的原创作品，请联系作者获取转载授权，否则将追究法律责任

1、MySQL主从复制原理

MySQL的主从复制和 MySQL的读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离。

(1)MySQL 支持的复制类型

①基于语句的复制。在主服务器上执行的 SQL语句，在从服务器上执行同样的语句。

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

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

(2)复制的工作过程

MySQL主从复制与读写分离_mysql

① 在每个事务更新数据完成之前，Master 将这些改变记录进二进制日志。写入二进制日志完成后，Master通知存储引擎提交事务。

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

③ SQL slave thread(SQL 从线程)处理该过程的最后一步。SQL 线程从中继日志读取事件，并重放其中的事件而更新 Slave 数据，使其与 Master 中的数据保持一致。只要该线程与 I/0 线程保持一致，中继日志通常会位于 OS 的缓存中，所以中继日志的开销很小。

2.MySQL 读写分离原理

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

MySQL主从复制与读写分离_服务器_02

目前较为常见的MySQL读写分离分为两种：

（1）基于程序代码内部实现：

在代码中根据 select、insert 进行路由分类,这类方法也是目前生产环境应用最广泛的。优点是性能较好，因为在程序代码中实现。

（2）基于中间代理层实现：

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

3、案例环境

实验拓扑结构图如下所示：

MySQL主从复制与读写分离_MySQL_03

master：192.168.10.101

slave1:192.168.10.102

slave2:192.168.10.103

amoeba：192.168.10.104

client：192.168.10.105

1、实现思路

(1)安装 MySQL 数据库;

(2)配置 MySQL 主从复制;

(3)安装并配置 Amoeba；

(4)客户端测试读写分离。

2、搭建MySQL主从复制

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
log-slave-updates=true     #Slave可以是其他 Slave 的 Master，从而扩散 Master 的更新
binlog-ignore-db=test      #不记录指定的数据库的二进制日志  replicate-ignore-db=test #设置不需要同步的库
binlog_cache_size = 1M#日志缓存的大小expire_logs_days=3       #自动过期清理日志的天

系统默认采用基于语句的复制类型

1：基于语句的复制。在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。配置：

binlog_format = STATEMENT

2：基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍，从 MySQL 5.0开始支持，配置：

binlog_format = ROW

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

binlog_format = MIXED

然后重启数据库。

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

其中 File 列显示日志名，Position 列显示偏移量，这两个值在后面配置从服务器的时候需要。Slave 应从该点上进行新的更新。

6：从服务器的配置

[root@localhost ~]# vi /etc/my.cnf
在[mysqld]模块中修改或添加：
server-id       = 22            ##修改，值不能和其他mysql服务器重复
relay-log=relay-log-bin            ##添加（可不指定）
relay-log-index=slave-relay-bin.index    ##添加（可不指定）
[root@localhost ~]# systemctl restart mysqld

--relay-log=name 中继日志的文件的名字

在启动时需要检查relay log index 文件中的relay log信息，此处定义该索引文件的名字

7：登录MySQL,实现同步

[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的哪个位置开始往后的日志内容。

8：启动同步，查看Slave状态，确保以下两个值为YES。

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

8：验证主从复制

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

3、搭建MySQL读写分离

Amoeba（变形虫）这个软件致力于MySQL的分布式数据库前端代理层，它主要为应用层访问MySQL的时候充当SQL路由功能，并具有负载均衡，高可用性，SQL过滤，读写分离，可路由到相关的目标数据库，可并发请求多台数据库。

1.：在主机 Amoeba 上安装 Java 环境

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

[root@localhost ~]# chmod +x jdk-6u14-linux-x64.bin
[root@localhost ~]# ./jdk-6u14-linux-x64.bin 按照提示按Enter键即可。
[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安装成功
java version "1.6.0 14"
Java(TM) SE Runtime Environment (build 1.6.0 14-b08)
Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)

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 读写分离，两个 Slave 读负载均衡

（1）在三个mysql服务器中开放权限给amoeba访问（只在master中即可，会复制到slave中）

mysql> grant all on . to ‘test’@'192.168.10.%' identified by '123.com';

（2）在amoeba上配置amoeba.xml文件

修改以下内容，此处设置的是mysql客户端连接amoeba时用的账号和密码

[root@localhost amoeba]# systemctl stop firewalld
[root@localhost ~]# cd /usr/local/amoeba/conf
[root@localhost conf]# vi amoeba.xml
<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="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>

（3）编辑dbServer.xml文件

[root@localhost conf]# vi dbServers.xml
修改（注意去掉注释），slave2的复制一个slave1
     <!-- mysql user -->
                  <property name="user">test</property>         ##26行

     <!-- mysql password -->              
                  <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>

若无 slave2 段，则手动添加：

<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 软件，其默认端口为tcp 8066

[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 zang values ('2','zhangsan');

（6）：在slave2上

mysql> use auth;
mysql>insert into zang 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上

上一篇：MySQL存储引擎MyISAM和InnoDB

下一篇：LVS负载均衡群集

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯