MySQL主从复制&读写分离

绝地灬酷狼 2023-10-05 16:35 2阅读 0赞

#### 文章目录 ####

*  前言
 *  一、MySQL主从复制
 *   *  1.1 原因
     *  1.2 解决办法
     *  1.3MySQL主从复制的类型
     *  1.4 主从复制图示
 *  二、MySQL读写分离
 *   *  2.1 原理
     *  2.2 读写分离的必要性
     *  2.3 主从复制+读写分离
     *  2.4 示例图分析
 *  三、主从复制实例
 *   *  3.1 环境
     *  3.2 自动同步时间
     *  3.3 配置Master MySQL
     *  3.4 配置Slave MySQL
     *  3.5 验证主从同步
 *  四、主从复制+读写分离示例
 *  4.1: 搭建amoeba服务

## 前言 ##

生产环境中，数据库服务器不可能只有一台，而多台数据库的存在必然要用到主从复制同步。

## 一、MySQL主从复制 ##

### 1.1 原因 ###

在企业网站中，后端MySQL数据库只有一台时，可能会有以下问题：

1.  遇到单点故障，服务不可用
2.  无法处理大量的高并发数据请求
3.  数据丢失将会造成很大损失

### 1.2 解决办法 ###

1.  增加MySQL数据库服务器，对数据进行备份，形成主备
2.  确保主备MySQL数据库服务器数据是一样的
3.  主服务器宕机了，备份服务器继续工作，数据有保障  
    并且MySQL主从复制与读写分离密切相关

### 1.3MySQL主从复制的类型 ###

*  基于语句的复制（默认）

在主服务器上执行的语句，从服务器执行同样的语句

*  基于行的复制

把改变的内容复制到从服务器

*  混合类型的复制

一旦发现基于语句无法精确复制时，就会采用基于行的复制

### 1.4 主从复制图示 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NDgwMDIw_size_1_color_FFFFFF_t_70]  
**Master**

*  启用binlog日志

**Slave**

*  Slave\_IO: 复制master主机 binlog日志文件里的SQL命令到本机的relay-log文件里。
 *  Slave\_SQL: 执行本机relay-log文件里的SQL语句，实现与Master数据一致。
 *  slave端的IO线程连上master端，请求同步
 *  master端返回给slave端，bin log文件名和位置信息
 *  IO线程把master端的bin log内容依次写到slave端relay-log里，并把master端的bin-log文件名和位置记录到master.info里。
 *  salve端的sql线程，检测到relay log中内容更新，就会解析relay log里更新的内容，并执行这些操作

## 二、MySQL读写分离 ##

### 2.1 原理 ###

*  读写分离就是只在主服务器上写，只在从服务器上读
 *  主数据库处理事务性査询，而从数据库处理 select査询
 *  数据库复制被用来把事务性査询导致的变更同步到集群中的从数据库
 *  由MySQL代理面向客户端提供服务
 *  收到SQL写请求时，交给master服务器处理
 *  收到SQL读请求时，交给slave服务器处理

### 2.2 读写分离的必要性 ###

*  在企业应用中，在大量的数据请求下，单台数据库将无法承担所有的读写操作

配置多台数据库服务器以实现读写分离  
读写分离建立在主从复制的基础上

### 2.3 主从复制+读写分离 ###

*  通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力
 *  Amoeba:是一个以MySQL为底层数据存储，并对应用提供MySQL协议接口的proxy，外号变形虫
 *  读取请求发送给从服务器时，采用轮询调度算法（可在amoeba的dbservers.xml配置文件中修改）
 *  主服务器DOWN掉，我们会采用MHA解决（暂时没用到）

**涉及的账号权限：**

1.  主从同步账号
2.  节点服务器开放调度账号
3.  Amoeba代理账号

### 2.4 示例图分析 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NDgwMDIw_size_1_color_FFFFFF_t_70 1]

## 三、主从复制实例 ##

### 3.1 环境 ###

3台MySQL数据库：  
Master：192.168.100.10  
Slave1：192.168.100.11  
Slave2：192.168.100.12  
要求Master能够联网，从ntp.aliyun.com更新时间。  
两台Slave从Master处更新时间。  
使用Crontab计划任务每30秒更新一次时间，确保主从时间一致。

### 3.2 自动同步时间 ###

*  Master上需要安装ntp，用来给两台slave同步时间，还需要安装ntpdate从ntp.aliyun.com获取同步时间
 *  slave1，slave2只需要安装ntpdate从Master处更新时间即可。
 *  三台服务器均已编译安装MySQL  
    **Master服务器**

1.  安装ntp、ntpdate

[root@master ~]# rpm -q ntp ntpdate	      '//查询ntp和ntpdate是否安装'
    ntp-4.2.6p5-25.el7.centos.2.x86_64
    ntpdate-4.2.6p5-25.el7.centos.2.x86_64

1.  更新时间

[root@master ~]# ntpdate ntp.aliyun.com
    28 Dec 14:01:33 ntpdate[73315]: adjust time server 203.107.6.88 offset 0.398983 sec

1.  编辑ntp配置文件

[root@master ~]# vi /etc/ntp.conf
    17: restrict 192.168.100.0 mask 255.255.255.0 nomodify notrap  //修改为local network
    '//删除21-24行，添加以下配置'
    21: server 127.127.1.0
    22: fudge 127.127.1.0 stratum 8

1.  重启ntpd服务

systemctl restart ntpd
    netstat -anptu | grep ntpd

![在这里插入图片描述][20201228232030140.png]  
5. 使用crontab计划任务

crontab -e
    */30 * * * * /usr/sbin/ntpdate ntp.aliyun.com  //每30秒同步一次时间

**Slave服务器**

ntpdate 192.168.100.10

![在这里插入图片描述][20201228232534263.png]

crontab -e
    */30 * * * * /usr/sbin/ntpdate 192.168.100.10

### 3.3 配置Master MySQL ###

[root@master ~]# vi /etc/my.cnf 	//在mysqld下面配置
    ...
    server_id =1            '主服务器id'
    log_bin=master_bin		 '设置二进制日志名'
    log_slave_updates=true	   '//允许从库更新'

mysql> grant replication slave on *.* to 'myslave'@'192.168.100.%' identified by '123456';
       '//192.168.100.网段的从服务器拥有复制权限，可以使用myslave身份123456密码复制所有的库和表'
    mysql> flush privileges;   '//刷新'
    Query OK, 0 rows affected (0.01 sec)     '//查看当前位置id，非常重要，之后不得有更改数据库操作'
    
    mysql> show master status;
    +-------------------+----------+--------------+------------------+-------------------+
    | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +-------------------+----------+--------------+------------------+-------------------+
    | master_bin.000001 |      604 |              |                  |                   |
    +-------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    '//File: master_bin.000001  Position: 604  '

### 3.4 配置Slave MySQL ###

*  两台从服务器除了server-id不一样，其它配置都一样

[root@slave02 ~]# vim /etc/my.cnf
    server-id = 2                 '主从服务器id不能一样，另一台从服务器为3'
    relay_log=relay_log_bin            '设置二进制日志名'
    relay_log_index=slave_relay_bin.index     '从服务器更新二进制日志'

systemctl restart mysqld

mysql> change master to change master to master_host='192.168.100.10',master_user='myslave',master_password='123456',master_log_file='master_bin.000001',master_log_pos=604;
    '//在从服务器中添加主服务器，登录用户，密码，日志文件，位置id''这里一定要和上文主从授权的账号密码一致'
    mysql> start slave;       '//开启主从同步'
    mysql> show slave status\G  '//查看主从同步情况'
    ......
    Slave_IO_Running: Yes    'IO运行正常'
    Slave_SQL_Running: Yes   'SQL运行正常'
    
    'slave01按照同样方法修改,注意端口号不要相同'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NDgwMDIw_size_1_color_FFFFFF_t_70 2]

### 3.5 验证主从同步 ###

*  Master创建库和表

mysql> create database school;
    mysql> use school;
    mysql> create table cd(id int(4),name varchar(50));
    mysql> insert into cd values(1,'zhangsan'),(2,'lisi');
    mysql> select * from cd;
    +------+----------+
    | id   | name     |
    +------+----------+
    |    1 | zhangsan |
    |    2 | lisi     |
    +------+----------+
    2 rows in set (0.00 sec)

*  Slave上查看

主从复制成功。

## 四、主从复制+读写分离示例 ##

## 4.1: 搭建amoeba服务 ##

'三台mysql服务器添加权限和用户开放给amoeba访问'
    mysql> grant all on *.* to test@'192.168.100.%' identified by '123.com';
    mysql> flush privileges;

//上传jdk-6u14-linux-x64.bin amoeba-mysql-binary-2.2.0.tar.gz 到/opt
    [root@amoeba opt]# cp jdk-6u14-linux-x64.bin /usr/local/
    [root@amoeba opt]# cd /usr/local/
    [root@amoeba local]# chmod +x jdk-6u14-linux-x64.bin
    [root@amoeba local]# ./jdk-6u14-linux-x64.bin     '执行jdk程序，一直空格'
    Do you agree to the above license terms? [yes or no]
    yes                        '这里输入yes等待安装成功即可'
    
    [root@amoeba local]# mv jdk1.6.0_14/ jdk1.6
    [root@amoeba local]# 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
    [root@amoeba local]# source /etc/profile
    [root@amoeba local]# mkdir /usr/local/amoeba
    [root@amoeba local]# cd /opt
    [root@amoeba opt]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
    [root@amoeba opt]# chmod -R 755 /usr/local/amoeba/
    [root@amoeba opt]# /usr/local/amoeba/bin/amoeba    '查看是否安装成功'
    amoeba start|stop     '安装成功'
    [root@amoeba opt]# cd /usr/local/amoeba/conf 
    [root@amoeba conf]# vim amoeba.xml    '配置文件中需要修改的行'
    '此段设置的是客户端通过amoeba用户和123456密码访问amoeba服务器'
    //第30行修改
     30        <property name="user">amoeba</property>   //客户访问amoeba账户
     31 
     32        <property name="password">123456</property>  //使用123456密码访问amoeba服务器'
    //第115行修改
    //开启读写功能池设定'
    115         <property name="defaultPool">master</property>   //修改默认地址池为master
    116 
    117         	'//117行取消注释'
    118         <property name="writePool">master</property>      //修改写为master
    119         <property name="readPool">slaves</property>       //修改读为slaves
    120                     //120行删除-->注释符号
                    
    [root@amoeba conf]# vim dbServers.xml     '编辑数据库配置文件'
     22           
     23          <property name="schema">mysql</property>      '/*amoeba默认读取的库将test修改为mysql;'
             '23行test修改为mysql' 5.5版本这里是test,原本有这个库,5.7没有自带这个test库,所以要修改,不修改报错*/'
     24 
     //'设置amoeba访问mysql数据库的用户和密码',这里要与上一个三个mysql代码块授权账号密码一致
     
     25           
     26           <property name="user">test</property>   '//26行修改用户名test'
     27 
     28                           '//28行-30行取消注释'              
     29           <property name="password">123.com</property>  '//29行修改密码123.com'
    
    
    45         <dbServer name="master"  parent="abstractServer">   '//45行主mysql服务器名称修改为master'
     46                 <factoryConfig>
     47                         
     48                         <property name="ipAddress">192.168.100.150</property>   '48行//修改主服务器IP'
     49                 </factoryConfig>
     50         </dbServer>
     51 
     52         <dbServer name="slave1"  parent="abstractServer">    '//52行修改从服务器1的名称'
     53                 <factoryConfig>
     54                         
     55                         <property name="ipAddress">192.168.100.160</property>  '//修改从服务器1的地址'
     56                 </factoryConfig>
     57         </dbServer>
     58 '将上面从服务器配置的7行复制下来修改名称slave2和ip地址'
     59         <dbServer name="slave2"  parent="abstractServer">      '//修改从服务器2的名称'
     60                 <factoryConfig>
     61                         
     62                         <property name="ipAddress">192.168.100.170</property>   '//修改从服务器1的地址
     63                 </factoryConfig>
     64         </dbServer>
     65 '//修改数据库从服务器池'
     66         <dbServer name="slaves" virtual="true">    //修改服务器的名称为slaves'
     67                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
     68                         
     69                         <property name="loadbalance">1</property>
     70 
     71                         
     72                         <property name="poolNames">slave1,slave2</property>   '//添加两个从服务器名称slave1,slave2'
     73                 </poolConfig>
     74         </dbServer>
    [root@amoeba conf]# /usr/local/amoeba/bin/amoeba start &  '启动amoeba服务'
    [1] 10675
    [root@amoeba conf]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
    2020-08-27 00:33:50,480 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
    log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf
    2020-08-27 00:33:50,626 INFO  net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.
    2020-08-27 00:33:50,627 INFO  net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:15974.
    [root@amoeba ~]# netstat -ntap | grep 8066
    tcp6       0      0 :::8066                 :::*                    LISTEN      11060/java 
    //8066端口一定要开启

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NDgwMDIw_size_1_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201228143434382.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NDgwMDIw,size_1,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NDgwMDIw_size_1_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201228165328472.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NDgwMDIw,size_1,color_FFFFFF,t_70
[20201228232030140.png]: https://img-blog.csdnimg.cn/20201228232030140.png
[20201228232534263.png]: https://img-blog.csdnimg.cn/20201228232534263.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NDgwMDIw_size_1_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201229001609879.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NDgwMDIw,size_1,color_FFFFFF,t_70