Linux--MySQL主从复制与读写分离

10.1.1 案例概述

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

10.1.2 案例前置知识点

1. MySQL主从复制原理

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

1) MySQL支持的复制类型

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

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

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

2. MySQL读写分离原理

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

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

1)基于程序代码内部实现

       

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

2)基于中间代理层实现

     

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

部署配置实验

实验环境

虚拟机 3台 centos7.9

网卡NAT模式 数量 1

组件包mysql-5.6.36.tar.gz  cmake-2.8.6.tar.gz

设备	IP	备注
Centos01	192.168.161.111	Master     主
Centos02	192.168.161.112	Slave       从
Centos03	192.168.161.114	Amoeba   node3

初始配置

关闭防火墙

Systemctl disable firewalld --now

关闭Selinux

setenforce 0
sed -i 's/SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config

配置IP DNS 网关

nmtui

修改主机名

[root@node1 ~]# hostnamectl set-hostname Master
[root@node1 ~]# bash

MySQL安装

安装所需组件包（可主 从同时操作）

[root@master ~]# yum -y install gcc gcc-c++ ntp vim net-tool ncurses-devel autoconf

上传mysql安装包 mysql-5.6.36.tar.gz  cmake-2.8.6.tar.gz

解压cmakne

[root@master ~]# tar zxf cmake-2.8.6.tar.gz 

编译安装cmake

[root@master ~]# cd cmake-2.8.6/
[root@master cmake-2.8.6]# ./configure && gmake && gmake install

解压mysql

[root@master ~]# tar zxf mysql-5.6.36.tar.gz 

编译安装mysql

[root@master ~]# cd mysql-5.6.36/

[root@master mysql-5.6.36]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all

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

创建mysql组和用户

[root@master mysql-5.6.36]# groupadd mysql
[root@master mysql-5.6.36]# useradd -M -s /sbin/nologin mysql -g mysql

修改mysql安装目录权限  复制默认配置文件

[root@master mysql-5.6.36]# chown -R mysql:mysql /usr/local/mysql/
[root@master mysql-5.6.36]# cp support-files/my-default.cnf /etc/my.cnf

Mysql数据库 初始化

[root@master ~]# cd /usr/local/mysql/


[root@master mysql]# ./scripts/mysql_install_db --user=mysql  --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

添加mysql 到系统服务

[root@master mysql]# cp support-files/mysql.server /etc/init.d/mysqld

[root@master mysql]# chmod +x /etc/init.d/mysqld 

[root@master mysql]# chkconfig --add mysqld

启动MySQL服务

[root@master mysql]# systemctl start mysql

[root@master mysql]# mysqladmin -u root password 'abc.123'

添加mysql操作指令关联系统

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

用户登录测试

[root@master ~]# mysql -u root -p

Mysql 主 从复制（在主服务器上操作）

[root@master mysql]# cat << EOF >> /etc/ntp.conf 
> server 127.127.1.0
> fudge 127.127.1.0 stratum 8
> EOF

[root@master mysql]# systemctl start ntpd

Ntp服务 slave（从）配置

[root@slave ~]# yum -y install ntpdate

[root@slave ~]# ntpdate 192.168.161.111  //主的ip

主服务器 master

修改配置文件

[root@master mysql]# sed -i 's/# server_id = .*/server_id = 11/' /etc/my.cnf
[root@master mysql]# sed -i '/server_id = 11/a\log_bin = master-bin' /etc/my.cnf
[root@master mysql]# sed -i '/log_bin = master-bin/a\log-slave-updates = true' /etc/my.cnf

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

创建授权用户 myslave  123456

mysql> grant replication slave on *.* to 'myslave'@'192.168.161.%' identified by '123456';

mysql> grant replication slave on *.* to 'root'@'192.168.161.%' identified by '123456';

mysql> flush privileges;  //刷新

查看mysql 主节点状态

mysql> show master status;

记录file名字 和 position 编号

从服务器配置 slave

修改配置文件

[root@slave ~]# sed -i 's/# server_id = .*/server_id = 22/' /etc/my.cnf
[root@slave ~]# sed -i '/server_id = 22/a\relay-log = relay-log-bin' /etc/my.cnf
[root@slave ~]# sed -i '/relay-log = relay-log-bin/a\relay-log-index = slave-relay-bin.index' /etc/my.cnf

[root@slave ~]# systemctl restart mysqld.service 

连接主服务器9

mysql> change master to master_host='192.168.161.111',master_user='myslave',master_password='123456',master_log_fille='master-bin.000001',master_log_pos=412;

此处 IP 为主 IP

mysql> start slave;

mysql> show slave status\G;

 

测试，在主 192.168.161.111 中创建数据库bt135

mysql> create database bt135;

mysql> show databases;  查

在从 192.168.161.112 中查询数据库，发现已从 主 复制数据库到了 从 

mysql> show databases;

MySQL读写分离

部署Java 运行环境

上传安装包 jdk-6u14-linux-x64.bin

[root@amoeba ~]# chmod +x jdk-6u14-linux-x64.bin 

[root@amoeba ~]# ./jdk-6u14-linux-x64.bin 

[root@amoeba ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6

添加java 运行环境变量

[root@Amoeba ~]# 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/


[root@Amoeba ~]# java -version

部署amoeba

上传amoeba软件包

创建amoeba主目录

[root@Amoeba ~]# mkdir /usr/local/amoeba

[root@Amoeba ~]# tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/

编辑主配置文件

[root@Amoeba ~]# cd /usr/local/amoeba/
[root@Amoeba amoeba]# vim conf/amoeba.xml 

<property name="user">root</property>            客户端连接的用户,该用户是前面创建授权用户 root 密码为 123456
<property name="password">123456</property>      客户端连接密码

<property name="ipAddress">192.168.161.113</property> 将IP换成本机IP地址
<property name="daemon">true</property>

取消注释
<property name="writePool">server1</property>      写入数据池名称
<property name="readPool">server2</property>        读取数据池名称

编辑数据库连接文件

[root@Amoeba amoeba]# vim conf/dbServers.xml 

<property name="port">3306</property>
<property name="schema">test</property> 		默认库 名称
<property name="user">root</property>          链接数据库用户名
<property name="password">abc-123</property>  链接数据库密码  如有注释要取消


修改ipaddress 127.0.0.1 为mysql 主和从的IP

<dbServer name="server1"  parent="abstractServer">  
	<factoryConfig>
       <!-- mysql ip -->
       <property name="ipAddress">192.168.161.111</property>  修改主MYSQL IP 
    </factoryConfig>
</dbServer>

<dbServer name="server2"  parent="abstractServer">  
	<factoryConfig>
       <!-- mysql ip -->
       <property name="ipAddress">192.168.161.112</property>  修改主MYSQL IP 
    </factoryConfig>
</dbServer>

启动amoeba

[root@amoeba amoeba]# source /etc/profile

[root@Amoeba amoeba]# nohup ./bin/amoeba start 2>&1 &  //如该命令无法启动，可以用以下命令

[root@amoeba amoeba]# bin/amoeba start&

[root@amoeba amoeba]# cat nohup.out 

查看端口

[root@amoeba amoeba]# netstat -anpt

安装mysql客户端

[root@Amoeba amoeba]# yum -y install mariadb

连接amoeba测试

[root@amoeba ~]# mysql -u root -pabc.123 -P8066 -h 192.168.161.114

创建表（在 amoeba 中创建）

MySQL [(none)]> use test

MySQL [test]> create table info (user_name CHAR(16),user_passwd CHAR(48) DEFAULT '',PRIMARY KEY (user_name));

插入数据

MySQL [test]> insert into info(user_name,user_passwd) values('zhangsan',PASSWORD('123456'));

MySQL辅助（从）服务器 停止同步

[root@slave ~]# mysql -u root -p

mysql> stop slave;

再amoeba上插入数据  

MySQL [test]> insert into info(user_name,user_passwd) values('lisi',PASSWORD('123456'));

查询验证（输入是通过在 主 服务器上的 查询是在从服务器上获取的，因 从 服务器上关闭了同步而无法同步amoeba在 主 中插入的lisi用户，可在 主 服务器中查到新加的用户lisi）

MySQL [test]> select * from info;

主服务器查询结果。（求证了插入内容是通过 主 服务器插入的，而查询是通过 从 服务器查的）