必须要熟悉的--------------MySQL主从复制与读写分离

最新推荐文章于 2024-09-20 21:26:51 发布

来云朋

最新推荐文章于 2024-09-20 21:26:51 发布

阅读量212

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/Laiyunpeng666/article/details/109183779

版权

MySQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

MySQL主从复制与读写分离

为什么要进行读写分离

在企业应用中，成熟的业务通常数据量比较大
单台mysql在安全性、高可用性高并发方面都无法满足实际的需求
配置多条主从数据库服务器以实现读写分离

1、MySQL主从复制原理

首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离

1.1MySQL支持的复制类型

◆基于语句的复制。在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用
语句的复制，效率比较高。
◆基于行的复制，把改变的内容复制过去，而不是吧命令在从服务器上执行一遍。
◆混合类型的复制，默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制

1.2复制的过程

◆在每个事物更新数据完成之前，Master在二进制日志记录这些改变。写入二进制日志完成后，Master通知存储
引擎提交事物
◆Slave将Master的Binary log 复制到中继日志，首先Slave开始一个工作线程–I/O线程，I/O线程在Master上开
一个普通的连接，然后开始Binlog dump process ,Binlog dump process从Master的二进制日志中读取事件，如果
已经更上Master，它会睡眠并等待Master产生的新事件。I/O线程将这些日志写入中继日志
◆SQL Slave thread (SQL从线程)处理该过程的最后一步，SQL线程从中继日志读取事件，并重放其的事件而更新
Slave数据，使其与Master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，
所以中继日志的开销很小。

2、MySQL读写分离原理

简单来说，主服务器写，从服务器读，基本的原理是让主数据库处理事务性查询，二从数据库处理Select查询
数据库复制被用来把事务性查询导致的变更同步到群集中的从数据库。
目前比较常见的MySQL读写分离分为二种：

2.1、基于程序代码内部实现

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

2.2、基于中间代理层实现

代理一般位于客户端和服务器之间，代理服务器接受到客户端的请求后通过判断后转发到后端数据库，有二个代表性程序
◆MySQL-Proxy。 ----MySQL的开源项目，通过自带的lua脚本进行SQL判断，MySQL官方不建议用这个在生产环境中
◆Amoeba（变形虫） ----由陈思儒开发，曾就职于阿里巴巴，该程序由Java语言开发，阿里将其用于生产环境
但是它不支持事物和存储过程。

主从复制实验

确认环境

iptables -F
setenforce 0
hostnamectl set-hostname master
su

实验规划

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

amoeba centos-7.6-X86_64 20.0.0.89 amoeba-mysql-3.0.5-RC-distribution.zip
jdk-8u144-linux-x64.tar.gz
Master centos-7.6-X86_64 20.0.0.114 mysql-boost-5.7.20.tar.gz
Slave1 centos-7.6-X86_64 20.0.85 mysql-boost-5.7.20.tar.gz
Slave2 centos-7.6-X86_64 20.0.0.87 mysql-boost-5.7.20.tar.gz
客户机 centos-7.6-X86_64 20.0.0.86 mysql-boost-5.7.20.tar.gz

给三（master 、 slave1 、slave2）个服务器安装mysql

直接将软降传到/opt 然后执行安装脚本

#!/bin/bash
yum -y install \
ncurses \
ncurses-devel \
bison \
gcc \
gcc-c++ \
expect \
cmake

useradd -s /sbin/nologin  mysql

P1=$(find / -name mysql-boost-*.gz)
read -p "核对软件名称是否正确（yes|no）：" T

if [ $T = yes ]
then
tar zvxf $P1 -C /opt
else
exit
fi

P2=$(find /opt/ -name mysql-[0-9].*[0-9])
cd $P2

cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8  \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1

make -j3 && make install

chown -R mysql:mysql /usr/local/mysql/

echo "
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
" >/etc/my.cnf

chown mysql:mysql /etc/my.cnf

echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
echo 'export PATH' >> /etc/profile
source /etc/profile
#ln -s /usr/local/mysql/bin/ /usr/local/bin/
#ln -s /usr/local/mysql/lib/ /usr/local/bin/
cd /usr/local/mysql/

/usr/local/mysql/

bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
systemctl enable mysqld
systemctl start mysqld
systemctl status mysqld

/usr/bin/expect <<EOF
spawn /usr/local/mysql/bin/mysqladmin -uroot -p password
expect {
"Enter" {send "\r";exp_continue}
"password" {send "abc123\r";exp_continue}
"password" {send "abc123\r"}
}
expect eof
EOF

echo "请执行source /etc/profile完成优化"

全部同步阿里云时间

yum -y install ntp ntpdate
server 127.127.1.0
fudge 127.127.1.0 stratum 8
service ntpd restart
systemctl restart ntpd
systemctl enable ntpd

date -R
ntpdate ntp1.aliyun.com

从服务上

yum -y install ntpdate
ntpdate ntp1.aliyun.com

更改配置主服务器的

vi /etc/my.cnf
[mysqld]
server-id = 22   //这个要不同
relay-log = relay-log-bin   	//开启中继日志
relay-log-index = slave-relay-bin.index		//定义relay-log位置和名称加个索引

systemctl restart mysqld 		//别忘

mysql -uroot -p
>GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'20.0.0.%' IDENTIFIED BY 'abc123';   	//允许从服务器复制我的 允许20段的都都可以去用myslave这个用户 用户密码是abc123
>flush privileges;
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000004 |      599 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
##记住599这个数

从服务器配置

mysql> change master to master_host='20.0.0.114',master_user='myslave',master_password='abcc123',master_log_file='master-bin.000005',master_log_pos=599;    //
Query OK, 0 rows affected, 2 warnings (0.01 sec)

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 20.0.0.114
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000005
          Read_Master_Log_Pos: 599
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000005
             Slave_IO_Running: Yes	//说明io读写没问题
            Slave_SQL_Running: Yes	//说明sql线程没问题
              Replicate_Do_DB:

测试主从复制情况
主服务新建一个库在从的也能看到这个库说明了主从复制没问题
这里可能会遇到为问题 io线程是no

             Slave_IO_Running: No
            Slave_SQL_Running: Yes
问题分析 ： 只用两种可能会这样 一是网络
[root@localhost ~]# ping 20.0.0.85
PING 20.0.0.85 (20.0.0.85) 56(84) bytes of data.
64 bytes from 20.0.0.85: icmp_seq=1 ttl=64 time=0.264 ms
64 bytes from 20.0.0.85: icmp_seq=2 ttl=64 time=0.532 ms
说明网络没有问题
另一种就是授权的原因
首先去检查vi /etc/my.cnf  确保都是正确的
然后就去主服务器从新授权注意的是   file和position可能会改变
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000005 |      599 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
然后去从的也授权  注意要先stop slave
问题解决

读写分离

1、暗转java环境
首先将需要用的文件二进制包放到这个/opt目录把amoeba也放到/opt目录下
然后

chmod +x chmod +x jdk-6u14-linux-x64.bin    //给执行权限
./jdk-6u14-linux-x64.bin		//直接执行 一直按回车到最后输入yes   然后在选择否

在这里插入图片描述

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:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin

source /etc/profile
mkdir /usr/local/amoeba
tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod -R 755 /usr/local/amoeba/
[root@localhost amoeba]# /usr/local/amoeba/bin/amoeba
amoeba start|stop    		//说明amoeba安装没问题

在三台mysql上都开启运行amoeba的权限

mysql -uroot -p
grant all on *.* to test@'20.0.0.%' identified by '123.com';

回到amoeba上设置

cd /usr/local/amoeba/conf/
vi amoeba.xml
省略
30行
<property name="user">amoeba</property>
32行
   <!--  mysql password -->
                        <property name="password">123.com</property>    //注意注释别忘了去掉
117行掉注释
                <property name="LRUMapSize">1500</property>
                <property name="defaultPool">master</property>
                <property name="writePool">master</property>
                <property name="readPool">slaves</property>
                <property name="needParse">true</property>

另一个配置文件

vim dbServers.xml 

42行主服务器地址池
<dbServer name="master"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.114</property>
                </factoryConfig>
        </dbServer>

52行从服务器主机名
<dbServer name="slaves1"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.85</property>
                </factoryConfig>
        </dbServer>
        <dbServer name="slaves2"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.86</property>
                </factoryConfig>
        </dbServer>
尾行
        <dbServer name="slaves" virtual="true">
                <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>
                </poolConfig>
        </dbServer>

测试

客户机直接用amoeba装一个mysql测试

[root@localhost conf]# /usr/local/amoeba/bin/amoeba start&
[1] 22289				//开启了java日志
[root@localhost conf]# jobs
[1]+  Running                 /usr/local/amoeba/bin/amoeba start &

yum -y install mariadb*
systemctl start mariadb.service 
mysql_secure_installation
y    设置密码
n
n
n
y

[root@localhost conf]# netstat -ntap |grep 8066
tcp6       0      0 :::8066       

在客户端的mysql上写个数据
[root@localhost ~]# mysql -uamoeba -p -h 127.0.0.1 -P8066		//端口后是大P
MySQL [(none)]> use school
Database changed
MySQL [school]> create table lai(id int(4),name varchar(20));
Query OK, 0 rows affected (0.02 sec)
MySQL [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| lai              |
+------------------+
1 row in set (0.01 sec)

先将之前的两个从的断掉
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
在到amoeba上去插入数据
use school；
MySQL [school]> insert into lai values('1','peng');
Query OK, 1 row affected (0.04 sec)

select * from lai    //看不到因为从的断了没法读

到主的上面可以看到

mysql> select * from lai;
+------+------+
| id   | name |
+------+------+
|    1 | peng |
+------+------+
1 row in set (0.00 sec)

在从的服务器上写个数据然后去

mysql> create table yun (id int(5),name varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into yun values('2','xw');
Query OK, 1 row affected (0.02 sec)

另一台上
mysql> create table yun (id int(5),name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into yun values('3','xq');
Query OK, 1 row affected (0.01 sec)

客户机上能看到轮询效果

MySQL [school]> select * from yun;
+------+------+
| id   | name |
+------+------+
|    3 | xq   |
+------+------+
1 row in set (0.00 sec)
MySQL [school]> select * from yun;
+------+------+
| id   | name |
+------+------+
|    2 | xw   |
+------+------+
1 row in set (0.00 sec)