MySQL主从复制与读写分离

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

运城ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景,ssl证书未来市场广阔!成为创新互联的ssl证书销售渠道,可以享受市场价格4-6折优惠!如果有意向欢迎电话联系或者加微信:028-86922220(备注:SSL证书合作)期待与您的合作!


    MySQL 主从复制原理

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

         MySQL 支持的复制类型

1)基于语句的复制。在主服务器上执行的 SQL 语句,在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制,效率较高。

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

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


MySQL 读写分离原理

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

        基于中间代理从实现,代理一般位于客户端和服务器之间,代理服务器接到客户端请求通过判断后转发到后端数据库。


实验环境

本案例使用五台服务器模拟搭建,具体拓扑图如下:

MySQL 主从复制与读写分离


主机                   操作系统                      IP地址                            主要软件

Master             Centos 7  x86_64           192.168.213.170         mysql-5.5.24.tar.gz

Slave 1              Centos 7  x86_64           192.168.213.168        mysql-5.5.24.tar.gz

Slave 2              Centos 7  x86_64           192.168.213.171        mysql-5.5.24.tar.gz

Amoeba           Centos 7  x86_64            192.168.213.166        jdk-6u14-linux-x64.bin

                                                                                                             amoeba-mysql-binary-2.2.0.tar.gz

客户端                  Centos 7  x86_64           192.168.213.172

所以需要的软件包可通过此链接到百度云盘下载:

链接:https://pan.baidu.com/s/1Wv65-ZmAIasN3EPAzB8J7Q 密码:5q4b

搭建MySQL 主从复制

一. 建立时间同步,在主节点上搭建时间同步服务器。

检查是否安装 NTP.

[root@master ~]# rpm -q ntp
ntp-4.2.6p5-25.el7.centos.2.x86_64

配置 NTP  (在末行插入)

[root@master ~]# vim /etc/ntp.conf

server 127.127.213.0                                      //本地是时钟源
fudge 127.127.100.0 stratum 8                    //设置时间层级为8

启动 NTP ,关闭防火墙和安全性增强功能

[root@master ~]#service ntpd start
Redirecting to /bin/systemctl start ntpd.service
[root@master ~]#systemctl stop firewalld.service
[root@master ~]#setenforce 0

二.  在从节点服务器上进行时间同步 (节点服务器 slave01  和 slave02  配置相同)

检查是否安装 NTP.

[root@slave01 ~]# rpm -q ntp
ntp-4.2.6p5-25.el7.centos.2.x86_64

启动 NTP ,关闭防火墙和安全性增强功能

[root@slave01 ~]# service ntpd start
Redirecting to /bin/systemctl start ntpd.service
[root@slave01 ~]#  systemctl stop firewalld.service
[root@slave01 ~]# setenforce 0

与主服务器进行时间同步

[root@slave01 ~]# /usr/sbin/ntpdate 192.168.213.170                             //与主服务器进行时间同步
  7 Sep 10:10:30 ntpdate[4940]: the NTP socket is in use, exiting

三.   安装 MySQL 数据库,三个服务 器 master 、 slave01 、slave 02  都要安装

安装编译环境

yum install gcc gcc-c++ make cmake ncurses-devel bison libaio-devel –y

添加 mysql 用户并加入 mysql  组

[root@master LNMP]# useradd  -s /sbin/nologin mysql            //创建 mysql 用户                 
[root@master LNMP]# mkdir /usr/local/mysql                           //创建 mysql 目录

解压 MySQL 软件包,配置安装mysql

[root@master LNMP]# tar zxvf mysql-5.5.24.tar.gz -C /opt/

[root@master mysql-5.5.24]# cmake  \
> -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \                   //mysql  软件安装位置
> -DDEFAULT_CHARSET=utf8 \                                                     //默认字符集
> -DDEFAULT_COLLATION=utf8_general_ci \                            //默认字符校验
> -DWITH_EXTRA_CHARSETS=all \                                               //额外的编码,请使用 all
> -DSYSCONFDIR=/etc \
> -DMYSQL_DATADIR=/home/mysql/ \
> -DMYSQL_UNIX_ADDR=/home/mysql/mysql.sock \
> -DWITH_MYISAM_STORAGE_ENGINE=1 \                                 //存储引擎
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \
> -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
> -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
> -DENABLED_LOCAL_INFILE=1 \                                                     //启用本地加载数据
> -DWITH_SSL=system \
> -DMYSQL_TCP_PORT=3306 \                                                         //mysql 的端口默认时3306
> -DENABLE_DOWNLOADS=1 \
> -DWITH_SSL=bundled

编译及编译安装(此过程时间会长一些)

[root@master mysql-5.5.24]# make && make install

修改 mysql  安装目录的所有者,所属组

[root@master mysql-5.5.24]# chown -R mysql.mysql /usr/local/mysql

修改环境变量

[root@master mysql-5.5.24]# echo "PATH=$PATH:/usr/local/mysql/bin/" >> /etc/profile

[root@master mysql]# source /etc/profile                         //刷新环境变量

创建修改 my.cnf 配置文件

[root@master mysql-5.5.24]# cd /usr/local/mysql/
[root@master mysql]# ls
bin      data  include         lib  mysql-test  scripts  sql-bench
COPYING  docs  INSTALL-BINARY  man  README      share    support-files

[root@master mysql]# cp support-files/my-medium.cnf /etc/my.cnf
cp:是否覆盖"/etc/my.cnf"? yes
[root@master mysql]# cp support-files/mysql.server /etc/init.d/mysqld

[root@master mysql]# chmod 755 /etc/init.d/mysqld                      //添加执行权限
[root@master mysql]# chkconfig --add /etc/init.d/mysqld               //将 mysql  添加为系统使用
[root@master mysql]# chkconfig mysqld --level 35 on

初始化数据库

[root@master mysql]# /usr/local/mysql/scripts/mysql_install_db \
> --user=mysql \                                        //指定数据库管理员
> --ldata=/var/lib/mysql \
> --basedir=/usr/local/mysql \               //指定mysql 软件安装位置
> --datadir=/home/mysql                        //指定mysql 数据库安装位置

在配置文件中指定安装位置

[root@master mysql]# vim /etc/init.d/mysqld

basedir=/usr/local/mysql
datadir=/home/mysql

创建软连接

[root@master mysql]# ln -s /var/lib/mysql/mysql.sock /home/mysql/mysql.sock

启动数据库

[root@master mysql]# service mysql start
Redirecting to /bin/systemctl start mysql.service
Warning: mysql.service changed on disk. Run 'systemctl daemon-reload' to reload units.
[root@master mysql]# systemctl daemon-reload
[root@master mysql]# service mysql start
Redirecting to /bin/systemctl start mysql.service
[root@master mysql]# netstat -ntap | grep 3306                                          //查看3306端口
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      56229/mysqld

给数据库 root 用户设置登录密码,并登录 mysql 数据库

[root@master mysql]# mysqladmin -u root password 'abc123'
[root@master mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3

四.   在三台 服务器的 mysql 安装完成后,分别对其进行配置。

(1)mysql 主服务器配置

[root@master mysql]# vim /etc/my.cnf

server-id       = 11                             //唯一标识,三台服务器的 server-id  不能一样

log-bin=mysql-bin                            //主服务器的日志文件
log-slave-updates=true                   //从服务器的二进制文件

重启mysql 服务

[root@master mysql]# systemctl restart mysqld.service

授权从服务器

[root@master mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.213.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)               //授权所有从服务器复制权限,进行二进制的日志权限管理

mysql> FLUSH PRIVILEGES;                                      //刷新
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;                                   //查看主服务器状态
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      1086|              |                  |               //文件名及位置者两个参数记录下来,配置同步时会使用
+-------------------+----------+--------------+------------------+                 注意:如果对数据库 、表、数据 进行编辑,文件名和位置会变化
1 row in set (0.00 sec)

(2)mysql 从服务器 slave 01 配置

[root@slave01 mysql]# vim /etc/my.cnf

server-id       = 22
relay-log=relay-log-bin                 //IO线程通过读取日志要放入relay-log 中
relay-log-index=slave-relay-bin.index            //索引日志位置

重启mysql 服务

[root@slave01 mysql]# systemctl restart mysqld.service

mysql> change master to master_host='192.168.213.170',master_user='myslave',master_password=''123456',master_log_file='master-bin.000001',master_log_pos=1086;              //按主服务器结果更改命令中 master_log_file 和 master_log_pos 的参数  
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;                                    //启动slave
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;                  //查看slave状态

Slave_IO_Running: Yes                         //读取与重放日志两个参数一定为 Yes
   Slave_SQL_Running: Yes             

(3)mysql 从服务器 slave 02 配置

[root@slave01 mysql]# vim /etc/my.cnf

server-id       = 22
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index

重启mysql 服务

[root@slave01 mysql]# systemctl restart mysqld.service

mysql> change master to master_host='192.168.213.170',master_user='myslave',master_password=''123456',master_log_file='master-bin.000001',master_log_pos=1086;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

Slave_IO_Running: Yes

  Slave_SQL_Running: Yes

五.   验证主从同步

在 主服务器上创建一个库,看从服务器上能否同步

mysql> create database school;
Query OK, 1 row affected (0.00 sec)

在从服务器查看数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| #mysql50#.mozilla  |
| mysql              |
| performance_schema |
| schoo              |
| school             |
| test               |
+--------------------+

搭建 MySQL 读写分离

          Amoeba(变形虫)项目开源框架与2008年发布了一款 Amoeba  for  MySQL 软件。这个软件致力于 MySQL 的分布式数据库前段代理层,它主要为应用层访问 MySQL 是充当 SQL 路由,并具有负载均衡、高可用性、SQL过滤、读写分离、可路由到相关目标数据库、可并发请求多台数据库。通过 Amoeba 能够完成多数据源的高可用、负载均衡、数据切片的功能。

1.  在Amoeba 上安装 Java 环境。

因为 Amoeba 是基于jdk 1.5 开发的,所以官方推荐使用jdk 1.5 或1.6 版本

[root@bogon Y2C]#systemctl stop firewalld.service                                      #关闭防护墙

[root@bogon Y2C]#setenforce 0

[root@bogon Y2C]# cp jdk-6u14-linux-x64.bin /usr/local/                           #将压缩包复制到 /usr/local/ 目录下

[root@bogon Y2C]# ./jdk-6u14-linux-x64.bin                                                 #根据提示输入yes,按回车键完成即可

Do you agree to the above license terms? [yes or no]

Press Enter to continue.....

[root@bogon Y2C]# mv jdk1.6.0_14/ /usr/local/jdk1.6                              #将压缩包剪切到/usr/local/
[root@bogon Y2C]# 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@bogon Y2C]# source /etc/profile                 #重新读取系统环境变量

2 .  安装并配置 Amoeba 软件

[root@bogon Y2C]# mkdir /usr/local/amoeba                                            #创建amoeba 安装目录
[root@bogon Y2C]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/              #解压amoeba 软件包

[root@bogon Y2C]# chmod -R 755 /usr/local/amoeba/                     #递归修改权限
[root@bogon Y2C]# /usr/local/amoeba/bin/amoeba                          #查看amoeba 安装状态
amoeba start|stop                   //显示此内容说明 Amoeba 安装成功

3.  配置Amoeba 读写分离,两个 Slave 读负载均衡

(1)在 Master 、slave1、slave2   数据库中开放权限给 Amoeba 访问

mysql> grant all on *.* to test@'192.168.213.%' identified by '123.com';
Query OK, 0 rows affected (0.00 sec)

(2)编辑 amoeba.xml  配置文件

[root@bogon Y2C]#cd /usr/local/amoeba/conf/

[root@bogon Y2C]#vim amoeba.xml

30                                         amoeba              #在30行作用 ,设置客户端连接anoeba 的账户
31
32                                         123456      #设置客户端连接amoeba 的密码

115                 master                          #编辑默认地址池为名称 master
116
117                
118                 master                           #关闭注释,写入池名称为 master
119                 slaves                            #关闭注释,读入池名称为 slaves

(3)编辑 dbServers.xml 配置文件

[root@bogon Y2C]#cd /usr/local/amoeba/conf/

[root@bogon Y2C]#vim dbServers.xml

   26                     test                 #编辑连接数据库用户
  27                        
  28                        
  29                         123.com        #编辑连接数据库账户密码

   45                           #指定 master 服务器
  46                
  47                        
  48                         192.168.213.170      #指定主服务器的IP 地址

                                                #指定 slave 1 服务器
  53                
  54                        
  55                         192.168.213.168      #指定slave 1 服务器的IP

59                                   #指定 slave 2 服务器
60                
61
62                         192.168.213.171    #指定slave 1 服务器的IP 地址
63                

64        

                                                  #设置群集名称 slaves
  66                
  67                        
  68                         1
  69
  70