MySQL主从复制与读写分离
MySQL使用主从复制与读写分离的原因
1、在企业应用中，成熟的业务通常数据量都比较大
2、单台MySQL在安全性、高可用性和高并发方面都无法满足实际的需求
（myisam基于表级锁定；innodb基于行级锁定 --存储引擎不能满足并发读、写的需求）
3、配置多台主从数据库服务器以实现读写分离
MySQL主从复制原理
MySQL的复制类型
基于语句的复制
基于行的复制
混合类型的复制（语句、行、日志文件）
MySQL读写分离原理
1、只在主服务器上写，只在从服务器上读
2、主数据库处理事务性查询，从数据库处理select查询
3、数据库复制用于将事务性查询的变更同步到集群中的从数据库
4、复制的基本过程如下：
- Master将用户对数据库更新的操作以二进制格式保存到Binary Log日志文件中
- Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容
- Master接收来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置
- Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清除的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”
- SlaveSql进程检测到relay-log中新增了内容后，会马上解析relay-log的内容称为在Master端真实执行时候的那些可执行的内容，并在自身执行
MySQL主从复制读写分离步骤
环境
主库
20.0.0.11
从库
20.0.0.12
20.0.0.13
amoeba
20.0.0.15
客户机
20.0.0.16
时间同步 (所有设备时间保持一致)
主从复制
主库
[root@server1 ~]# vi /etc/my.cnf #数据库配置文件
server-id = 1 #服务器id，每个服务器不能相同
log_bin=master-bin #主服务器日志文件
log_slave_updates=true #允许中继日志读取主服务器的二进制日志
[root@server1 ~]# systemctl restart mysqld.service
[root@server1 ~]# ls /usr/local/mysql/data/ #查看生成的二进制日志文件
auto.cnf ib_logfile1 master-bin.index
ib_buffer_pool ibtmp1 mysql
ibdata1 lcx performance_schema
ib_logfile0 master-bin.000001 #日志文件 sys
[root@server1 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by '123456'; #为所有服务器所有库授权
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges; #更新权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'myslave'@'20.0.0.%';
+--------------------------------------------------------+
| Grants for myslave@20.0.0.% |
+--------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'20.0.0.%' |
+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql> mysql> show master status; #查看主的状态，记下position的值(同步的位置)
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 1119 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从库（20.0.0.12、20.0.0.13）
[root@server1 ~]# vi /etc/my.cnf #修改从服务器主配置文件参数
server-id = 11 #三台数据库id不同
relay_log=relay-log-bin #从主服务器上同步日志文件记录到本地中继日志
relay_log_index=slave-relay-bin.index #定义中继日志的索引
[root@server1 ~]# systemctl restart mysqld.service
[root@server1 ~]# mysql -uroot -p #登陆MySQL从服务器
mysql> change master to master_host='20.0.0.11',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=1119; #添加要同步数据的master服务器的账号、二进制日志文件、开始同步的位置（position）
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave; #启动从服务器
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G #查看从服务器状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 20.0.0.11
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 1119
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes #此项需为yes
Slave_SQL_Running: Yes #此项需为yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1119
Relay_Log_Space: 526
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: af0fbd40-464c-11eb-a010-000c29463cae
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
读写分离（amoeba服务器）
安装jdk环境（需要java环境）
[root@amoeba ~]# tar zxvf jdk-8u91-linux-x64.tar.gz
[root@amoeba ~]# mv jdk1.8.0_91/ /usr/local/java
[root@amoeba ~]# vi /etc/profile.d/java.sh #环境文件最后添加
export JAVA_HOME=/usr/local/java #设置java根目录
export PATH=$PATH:$JAVA_HOME/bin #在PATH环境变量中添加JAVA根目录下的bin子目录
[root@server1 ~]# source /etc/profile.d/java.sh
[root@server1 ~]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/java/bin
[root@server1 ~]# java -version
openjdk version "1.8.0_131"
OpenJDK Runtime Environment (build 1.8.0_131-b12)
OpenJDK 64-Bit Server VM (build 25.131-b12, mixed mode)
下载amoeba
wget https://nchc.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zip
部署amoeba
[root@server1 ~]# unzip amoeba-mysql-3.0.5-RC-distribution.zip
[root@server1 ~]# mv amoeba-mysql-3.0.5-RC/ /usr/local/amoeba
[root@server1 ~]# chmod -R 755 /usr/local/amoeba/
[root@server1 ~]# vim /usr/local/amoeba/jvm.properties
32 #JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize =16m -XX:MaxPermSize=96m"
33 JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"
在三台MySQL上给amoeba授权
mysql> grant all on *.* to 'test'@'20.0.0.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
修改amoeba主配置文件
[root@amoeba ~]# vi /usr/local/amoeba/conf/amoeba.xml
28 <property name="user">amoeba</property> #修改root为amoeba
29
30 <property name="password">123456</property> #添加数据库登陆密码
83 <property name="defaultPool">master</property> #修改multiPool为master
84
85 <!-- #注释，需删除
86 <property name="writePool">master</property> #主库写权限
87 <property name="readPool">slaves</property> #从库读权限
88 --> #注释，需删除
修改dbServers.xml 文件
[root@server1 ~]# vi /usr/local/amoeba/conf/dbServers.xml
22 <!-- mysql schema --> #注释行
23 <property name="schema">mysql</property> #MySQL5.7版本没有默认的test库，所以修改为mysql（5.7默认的数据库）
24
25 <!-- mysql user --> #注释行
26 <property name="user">test</property>
27 <!-- mysql password --> #注释行
28 <property name="password">123456</property>
#配置三台MySQL服务器主机名和地址
43 <dbServer name="master" parent="abstractServer"> #修改server1为master
44 <factoryConfig>
45 <!-- mysql ip -->
46 <property name="ipAddress">20.0.0.11</property> #修改为主库master的IP地址
47 </factoryConfig>
48 </dbServer>
49
50 <dbServer name="slave1" parent="abstractServer"> #修改为slave1
51 <factoryConfig>
52 <!-- mysql ip -->
53 <property name="ipAddress">20.0.0.12</property> #修改为从库1的IP
54 </factoryConfig>
#添加下面slave2模块
<dbServer name="slave2" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">20.0.0.13</property>
</factoryConfig>
57 <dbServer name="slaves" virtual="true">
58 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
59 <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
60 <property name="loadbalance">1</property>
61
62 <!-- Separated by commas,such as: server1,server2,server1 -->
63 <property name="poolNames">slave1,slave2</property> #修改为slave1，slave2
64 </poolConfig>
65 </dbServer>
[root@server1 ~]# /usr/local/amoeba/bin/launcher #启动anoeba
………………
2020-12-28 22:50:03 [INFO] Project Name=Amoeba-MySQL, PID=15094 , System shutdown ....
^C
#强行结束
[root@server1 ~]# netstat -anpt | grep 8066
tcp6 0 0 :::8066 :::* LISTEN 15051/java
效果验证
客户机
[root@server1 ~]# yum -y install mariadb*
[root@server1 ~]# mysql -uamoeba -p123456 -h 20.0.0.15 -P8066 #登录到amoeba服务器
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 201047493
Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA Source distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
主库
新建库、表
mysql> create database abc;
Query OK, 1 row affected (0.00 sec)
mysql> create table aaa(id int(2), name char(32), age int(3));
Query OK, 0 rows affected (0.01 sec)
从库1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc | #可以看到新建的库
| mysql |
| performance_schema |
| sys |
| test2 |
+--------------------+
6 rows in set (0.00 sec)
mysql> use abc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| aaa | #新建的表
+---------------+
1 row in set (0.00 sec)
从库2
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use abc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| aaa |
+---------------+
1 row in set (0.00 sec)
从库1关闭服务
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
主库添加数据
mysql> insert into aaa values(1,'zhangsan',19);
Query OK, 1 row affected (0.00 sec)
从库1查看数据为空
mysql> select * from aaa;
Empty set (0.00 sec)
从库2正常复制数据
mysql> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
+------+----------+------+
1 row in set (0.00 sec)
从库1开启服务后，自动同步数据
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
+------+----------+------+
1 row in set (0.00 sec)
关闭两台从库服务
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
向从库中写入数据
#从库1
mysql> insert into aaa values(2,'wangwu',20);
Query OK, 1 row affected (0.00 sec)
mysql> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
| 2 | wangwu | 20 |
+------+----------+------+
2 rows in set (0.00 sec)
#从库2
mysql> insert into aaa values(3,'chenqi',21);
Query OK, 1 row affected (0.00 sec)
mysql> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
| 3 | chenqi | 21 |
+------+----------+------+
2 rows in set (0.00 sec)
客户端查看数据库
MySQL [abc]> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
| 2 | wangwu | 20 |
+------+----------+------+
2 rows in set (0.00 sec)
MySQL [abc]> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
| 3 | chenqi | 21 |
+------+----------+------+
2 rows in set (0.00 sec)
#发现访问的数据为轮询机制
在客户端上写入数据
MySQL [abc]> insert into aaa values(5,'zhaosi',22);
Query OK, 1 row affected (0.00 sec)
只有主库会记录
mysql> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
| 5 | zhaosi | 22 |
+------+----------+------+
2 rows in set (0.00 sec)
开启同步
mysql> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
| 5 | zhaosi | 22 |
+------+----------+------+
2 rows in set (0.00 sec)
在开启同步后，主服务器上的数据会同步到各从服务器上中，但从服务器上的自己增加的数据不会同步，只会本地保存
客户端访问依然保持轮询
MySQL [abc]> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
| 3 | chenqi | 21 |
| 5 | zhaosi | 22 |
+------+----------+------+
3 rows in set (0.01 sec)
MySQL [abc]> select * from aaa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 19 |
| 2 | wangwu | 20 |
| 5 | zhaosi | 22 |
+------+----------+------+
3 rows in set (0.00 sec)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
