Mysql主从复制和读写分离详情及操作部署

时间  2021-01-21 标签 mysql主从复制 mysql linux

前言

         在实际的生产环境中，如果对mysql数据库的读和写都在一台数据库服务器中操作，无论是在安全性、高可用性，还是高并发等各个方面都是不能满足实际需求的。因此，一般通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。

 

                                                                             Mysql主从复制和读写分离

  • 主从复制

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

Mysql支持的复制类型：

  1. 基于语句的复制：在主服务器上执行的sql语句，在从服务器上会执行同样的语句。Mysql默认采用基于语句的复制，效率比较高，但是有时不能实现精准复制。
  2. 基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
  3. 混合类型的复制：默认采用基于语句的复制，一旦发现基于语句的复制不能精准复制时，就会采用基于行的复制。

  • 主从复制的过程：
  1. 在每个事物更新数据完成之前，master在二进制日志记录这些改变，写入二进制日志完成后，master通知存储引擎提交事物。
  2. Slave将master的binary log复制到其中的中继日志。首先从mysql服务器开始一个工作线程I/O线程，I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master。他会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
  3. Sql从线程处理该过程的最后一步。Sql线程从中继日志中读取事件，并重放其中的事件而更新slave的数据，使其与master的数据一致。
  •   读写分离

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

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

  1. 基于程序代码的内部实现

在代码中根据select、insert进行路由分类，这类方法也是目前生产环境中较为常用的，优点是性能较好，因为在程序代码中实现，不需要增加额外的设备作为硬件开支；缺点是需要研发人员来实现，运维人员无从下手。

     2. 基于中间代理层实现

代理一般位于客户端和服务器之间，代理服务器接收到客户端请求后通过判断后转发到后端数据库。如下有两个常用代理：

Mysql-proxy：其为mysql的开源项目，通过其自带的lua脚本进行sql判断，虽然是mysql官方产品，但是mysql官方并不建议其使用到生产环境中。

Amoeba：由陈思儒开发，该程序由Java语言进行开发。这个软件致力于mysql的分布式数据库前端代理层，它主要为应用层访问mysql的时候充当sql路由功能。Amoeba能够完成多数据源的高可用、负载均衡、数据切片等功能。

 

常用的mysql连接工具：

phpMyAdmin

phpMyAdmin是我们常用的MySQL管理工具之一，它是用PHP开发的基于Web方式架构在网站主机上的MySQL管理工具，支持中文，管理数据库也十分方便。主要缺点在对大数据库的备份和恢复不是十分方便。

Navicat

Navicat是一款桌面版MySQL管理工具，它和微软的SQLServer的管理器很像，简单易用。Navicat的优势在于使用图形化的用户界面，可以让用户管理更加轻松。

                                                                            mysql主从复制

部署环境：

系统环境CentOS release 7_x64

主mysql服务器ip：192.168.253.120

从mysql服务器ip：192.168.253.130

开始部署安装：

Mysql服务器都已经搭建完成

  • 主mysql上：

[[email protected] ~]# vim /etc/my.cnf
server-id=1                                    #server-id的值必须不同
log-bin=mysql-bin                        #开启二进制日志
[[email protected] ~]# systemctl  start mariadb       #开启mysql

登录mysql后进行授权

[[email protected] ~]# mysql      #默认mysql密码为空
MariaDB [(none)]> grant all on *.* to [email protected]'%' identified by 'hxb';
MariaDB [(none)]> flush privileges;     #刷新权限
解释：在master的数据库服务器中建立一个复制的账户，每个slave使用该账户链接master来进行复制，设置所有权限（根据具体情况自定）。上面创建了一个hxb用户，密码是hxb。只允许在所有段的ip地址的登录。

查看master的状态

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      442 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
记住file和position的值，配置slave的时候需要用。

  • 从mysql上配置：

[[email protected] ~]# vim /etc/my.cnf
server-id=2                      #修改server_id，其值必须和master的不同
relay-log=relay-logs       #开启中继日志，可以自定义目录，mysql用户有权限即可

[[email protected] ~]# systemctl  start mariadb

查看中继日志的启动状态：

mysql> show global variables like '%relay%';

连接master服务器：

MariaDB [(none)]> change master to master_host='192.168.253.120',
    -> master_user='hxb',
    -> master_password='hxb',
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=442;
Query OK, 0 rows affected (0.01 sec)

选项解释：

master_host：Master 服务器IP

master_user：Master 服务器授权用户，也就是 Master 前面创建的那个用户

master_password：Master 服务器授权用户对应的密码

master_log_file：Master binlog 文件名

master_log_pos：Master binlog 文件中的 Postion 值

手动启动复制线程：

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

查看状态：

MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.253.120
                  Master_User: hxb
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 442
               Relay_Log_File: relay-logs.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes       #双yes则表示成功

  • 读写分离:

安装jdk

# rpm -ivh jdk-8u20-linux-x64.rpm

# vi /etc/profile

export  JAVA_HOME=/usr/java/jdk1.8.0_20/

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

 

安装amoeba

# mkdir /usr/local/amoeba

# tar zxf amoeba-mysql-binary-2.2.0.tar.gz  -C /usr/local/amoeba/

# /usr/local/amoeba/bin/amoeba

amoeba start|stop  //显示这个表示amoeba安装成功。

配置amoeba读写分离

在master、slave1、slave2、、、、中开放权限给amoeba访问：

mysql> grant all on *.* to  [email protected]'172.18.49.%' identified by '123';

修改amoeba的配置文件：

# cp amoeba.xml amoeba.xml.bak

# vi /usr/local/amoeba/conf/amoeba.xml

修改连接amoeba代理服务器的有用户名和密码。

修改默认的地址池名字，写和读的名字：注意：删掉注释符。

对应主机名

编辑修改dbservers.xml配置文件（对后端mysql服务器的配置）。

# cp dbServers.xml dbServers.xml.bak

# vi dbServers.xml

 

指定主从数据库地址：

都是对应得主机名

之后启动amoeba：

# /usr/local/amoeba/bin/amoeba start&

# netstat -anpt  | grep :8066

联系我们 沪ICP备2021010478号-7