Mysql的主从架构模式，是很多企业广泛使用，并且大家所广为熟知的一种架构模式，这是DBA所应该熟练掌握的技能。
• 1.mysql主从复制主要用途
○ a.用于备份，避免影响业务
○ b.实时灾备，用于故障切换
○ c.读写分离，提供查询服务
• 2.mysql主从复制存在的问题
○ a.主库宕机后, 数据可能丢失
○ b.主库写压力大, 复制可能会延时
• 3.mysql主从复制解决方法
○ a.半同步复制、或者全同步复制. 要求: Mysql5.7版本
○ b.并行复制, 解决从库复制延迟的问题. 建议: 5.7版
• 4.mysql主从复制原理
1.在主库上把将更改DDL DML DCL记录到二进制日志Binary Log中。
2.备库I/O线程将主库上的二进制日志复制到自己的中继日志Relay Log中
3.备库SQL线程读取中继日志中的事件，将其重放到备库数据库之上。
1.MySQL数据库传统复制
数据库传统主从复制如下, 如需实现多从, 将以下实验从案例在配置一次即可
操作系统
/etc/hosts文件解析
192.168.70.160 master1
192.168.70.161 Slave1Master配置
//1.配置文件/etc/my.cnf
log-bin
server-id=160
//2.重启mysql服务生效
[root@Master ~]# systemctl restart mysqld
//3.模拟线上数据库
mysql> create database linuxdb;
mysql> use linuxdb;
mysql> create table t1(id int, name varchar(20));
mysql> insert into t1 values (1,'tt');
mysql> insert into t1 values (2,'ttt');
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | tt |
| 2 | ttt |
+------+------+
//4.授权, 允许能够远程连接的主机(replicaiton)
mysql> grant replication slave, replication client on *.*
to 'rep'@'192.168.70.%' identified by 'Rep123.com';
//5.导出当前数据
[root@Master ~]# mysqldump -uroot -pBgx123.com \
--all-databases \
--single-transaction \
--master-data=1 \
--flush-logs > /root/db-$(date +%F)-all.sql
//6.将备份文件传送至Slave
[root@Master ~]# scp /root/db-2018-05-10-all.sql root@slave1:/rootSlave配置
//1.检查是否能使用远程账户登录
[root@slave ~]# mysql -hmaster -urep -pRep123.com
//2.修改配置文件/etc/my.cnf, 从需开启binlog
server-id=161
//3.重启mysql数据库服务
[root@slave ~]# systemctl restart mysqld
//4.导入数据,追master的bin_log
[root@Slave ~]# mysql -uroot -p'Bgx123.com' -e "source /root/db-2018-05-10-all.sql"
//5.指向Master,无需指定binlogfile和pos
mysql> change master to
master_host='master1',
master_user='rep',
master_password='Rep123.com';
//6.启动slave角色
mysql> start slave;
//7.查看角色是否同步
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master1
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Master1-bin.000003
Read_Master_Log_Pos: 589
Relay_Log_File: Slave1-relay-bin.000004
Relay_Log_Pos: 613
Relay_Master_Log_File: Master1-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes2.MySQL数据库Gtid复制
数据库Gtid主从复制如下, 如需实现多从, 将以下实验从案例在配置一次即可
/etc/hosts文件解析
192.168.70.160 master1
192.168.70.161 Slave1注意:如果实验过传统主从复制, 请重置Slave数据库,文章后续会介绍
Master配置
//1.配置文件/etc/my.cnf
log-bin
server-id=160
gtid_mode = ON
enforce_gtid_consistency=1
//2.重启mysql服务生效
[root@Master ~]# systemctl restart mysqld
//3.模拟线上数据库
mysql> create database linuxdb;
mysql> use linuxdb;
mysql> create table t1(id int, name varchar(20));
mysql> insert into t1 values (1,'tt');
mysql> insert into t1 values (2,'ttt');
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | tt |
| 2 | ttt |
+------+------+
//4.授权, 允许能够远程连接的主机(replicaiton)
mysql> grant replication slave, replication client on *.*
to 'rep'@'192.168.70.%' identified by 'Rep123.com';
//5.导出当前数据
[root@Master ~]# mysqldump -uroot -pBgx123.com \
--all-databases \
--single-transaction \
--master-data=1 \
--flush-logs > /root/db-$(date +%F)-all.sql
//6.将备份文件传送至Slave
[root@Master ~]# scp /root/db-2018-05-10-all.sql root@slave1:/rootSlave配置
//1.重新初始化环境
[root@Slave1 ~]# systemctl stop mysqld
[root@Slave1 ~]# rm -rf /var/lib/mysql/*
[root@Slave1 ~]# chown -R mysql.mysql /var/lib/mysql
[root@Slave1 ~]# systemctl start mysqld
//2.检查是否能使用远程账户登录
[root@slave ~]# mysql -hmaster -urep -pRep123.com
//3.修改配置文件/etc/my.cnf
server-id=161
gtid_mode = ON
enforce_gtid_consistency=1
[可选,保存连接信息至表中]
#log-info-repository=TABLE
#relay-log-intp-repository=TABLE
//4.重启mysql数据库服务
[root@slave ~]# systemctl restart mysqld
//5.导入数据,追master的bin_log
[root@Slave ~]# mysql -uroot -p'Bgx123.com' -e "source /root/db-2018-05-10-all.sql"
//6.指向Master,gtid自动协商同步
mysql> change master to
master_host='master1',
master_user='rep',
master_password='Rep123.com',
master_auto_position=1;
//7.启动slave角色
mysql> start slave;
//8.查看角色是否同步
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master1
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Master1-bin.000003
Read_Master_Log_Pos: 589
Relay_Log_File: Slave1-relay-bin.000004
Relay_Log_Pos: 613
Relay_Master_Log_File: Master1-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: YesMySQL数据库双主复制
注意: 数据库双主M-M架构, 需要基于gtid的主从架构演变
Gtid M-M复制, 基于主从模式,演变为双主
//Master1
1.修改配置
log -bin
server-id=1
gtid_mode = ON
enforce_gtid_consistency=1
2.重启mysql数据库
systemctl restart mysqld
3.授权
4.导出数据库//Master2
1.修改配置
log -bin
server-id=2
gtid_mode = ON
enforce_gtid_consistency=1
2.重启mysql数据库
systemctl restart mysqld
3.授权
给master1授权，由于之前授权针对网段
4.恢复Master1数据, 保持主从同步
master1和master2数据必须保持一致
在master1上执行 change to master --> mater2
在master2上执行 change to master -->master13.MySQL数据库多源复制
数据库M-M-S-S
[root@Master1 ~]# cat /etc/hosts
192.168.70.160 master1
192.168.70.161 master2
192.168.70.162 slave2
192.168.70.163 slave3Master1
1.修改配置
log -bin
server_id=160
gtid_mode = ON
enforce_gtid_consistency=1
2.重启mysql服务器
systemctl restart mysqld
3.授权(建议网段)
mysql> grant replication slave,replication client on *.* to rep@'192.168.70.%' identified by 'Rep123.com';
mysql> flush privileges;
4.导出对应的数据
mysqldump -uroot -p'Bgx123.com' \
--all-databases \
--master-data=1 \
--single-transaction \
--flush-logs > /root/$(date +%F)-mysql-all.sql
5.分发对应的数据
scp --> master2
scp --> slave1
scp --> slave2Master2
1.修改配置
server_id=161
log_bin
gtid_mode = ON
enforce_gtid_consistency = 1
2.重启
3.导入数据
[root@Master2 ~]# mysql -uroot -pBgx123.com -e "reset master;"
[root@Master2 ~]# mysql -uroot -pBgx123.com < /root/2018-05-10-mysql-test.sql
4.验证远程账户是否可用
1.防火墙
2.账户密码
5.登陆数据库,清理从库的二进制日志
mysql> reset master;
6.指定master
mysql> change master to
master_host='master1',
master_user='rep',
master_password='Rep123.com',
master_auto_position=1;
7.启动 slave 角色
mysql > start slave;
8.查看角色状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master1
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Master1-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: Master2-relay-bin.000002
Relay_Log_Pos: 371
Relay_Master_Log_File: Master1-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master1
mysql> flush privileges;
mysql>change master to
master_host='master2',
master_user='rep',
master_password='Rep123.com',
master_auto_position=1;
2.启动slave角色
mysql > start slave; #
3.查看slave状态
mysql > show slave status\G
双主OKSlave1和Slave2一致
1. 修改配置
server_id=162
gtid_mode = ON
enforce_gtid_consistency = 1
master-info-repository=TABLE
relay-log-info-repository=TABLE
2.重启数据库
[root@slave2 ~]# systemctl restart mysqld
3. 初始化数据库 导入数据
[root@slave2 ~]# mysql -uroot -p'Bgx123.com' -e "reset master;"
[root@slave2 ~]# mysql -uroot -p'Bgx123.com' < /root/2018-05-10-mysql-test.sql
4.清理二进制日志文件
reset master;
//指向Master1
mysql> change master to
master_host='master1',
master_user='rep',
master_password='Rep123.com',
master_auto_position=1 for channel 'master1-channel';
//指向Master2
mysql> change master to
master_host='master2',
master_user='rep',
master_password='Rep123.com',
master_auto_position=1 for channel 'master2-channel';
5.mysql > start slave;
6.mysql > show slave status\G
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master1
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Master1-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: slave2-relay-bin-master1@002dchannel.000002
Relay_Log_Pos: 371
Relay_Master_Log_File: Master1-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> show slave status\G
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master1
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Master1-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: slave2-relay-bin-master1@002dchannel.000002
Relay_Log_Pos: 371
Relay_Master_Log_File: Master1-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes4.MysQL数据库读写分离
1.部署MyCat
//Mycat依赖于Java环境
[root@Mycat ~]# yum install -y java
[root@Mycat ~]# java -version
openjdk version "1.8.0_161"
OpenJDK Runtime Environment (build 1.8.0_161-b14)
OpenJDK 64-Bit Server VM (build 25.161-b14, mixed mode)
//下载MyCat
[root@Mycat ~]# mkdir /soft/
[root@Mycat ~]# tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /soft/
[root@Mycat ~]# wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz2.配置应用程序连接MyCat账户密码
[root@Mycat ~]# vim /soft/mycat/conf/server.xml
#注意:底下有user用户需注释
<!-- 应用连接mycat账户-->
<user name="blog">
<!-- 应用连接mycat密码-->
<property name="password">123456</property>
<!-- 针对哪个库进行授权 -->
<property name="schemas">blog</property>
</user>
<user name="www">
<property name="password">123456</property>
<property name="schemas">www</property>
</user>3.配置Mycat连接后端数据库
balance负载均衡类型
1.balance="0"不开启读写分离机制,所有读操作都发送到当前可用的writeHost上。
2.balance="1", 所有的主机都参与select语句的负载均衡, 但写语句还是由writeHost
3.balance="2"所有读操作都随机的在writeHost、readhost上分发。
[root@Mycat ~]# cat /soft/mycat/conf/schema.xml
[root@Mycat conf]# cat schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!--链接mycat后的显示名称 -->
<schema name="blog" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> </schema>
<schema name="www" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2"> </schema>
<!--dateNode数据节点 dataHost相当于主机池-->
<dataNode name="dn1" dataHost="dn1pool" database="blog" />
<dataNode name="dn2" dataHost="dn2pool" database="www" />
<!--定义blog资源池限制-->
<dataHost name="dn1pool" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<!--健康检查-->
<heartbeat>select user()</heartbeat>
<writeHost host="Master" url="192.168.70.160:3306" user="blog" password="Bgx123.com">
<readHost host="Slave1" url="192.168.70.161:3306" user="blog" password="Bgx123.com" />
<readHost host="Slave2" url="192.168.70.162:3306" user="blog" password="Bgx123.com" />
</writeHost>
</dataHost>
<!--定义www资源池限制-->
<dataHost name="dn2pool" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<!--健康检查-->
<heartbeat>select user()</heartbeat>
<!--定义www库读写主机,可以定义多个-->
<writeHost host="Master" url="192.168.70.160:3306" user="www" password="Bgx123.com">
<readHost host="Slave1" url="192.168.70.161:3306" user="www" password="Bgx123.com" />
<readHost host="Slave2" url="192.168.70.162:3306" user="www" password="Bgx123.com" />
</writeHost>
</dataHost>
</mycat:schema>4.配置MySQL相关信息, 均在MySQL-Master上操作
mysql> create databse blog;
mysql> create databse www;
mysql> create table blog.b(id int);
mysql> create table www.w(id int);
mysql> grant all on www.* to www@'192.168.70.%' identified by 'Bgx123.com';
mysql> grant all on blog.* to blog@'192.168.70.%' identified by 'Bgx123.com';5.启动MyCat中间件
[root@Mycat ~]# /soft/mycat/bin/mycat start
Starting Mycat-server...
[root@Mycat conf]# lsof -i :8066
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
java 5352 root 76u IPv6 52537 0t0 TCP *:8066 (LISTEN)
[root@Mycat conf]# lsof -i :9066
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
java 5352 root 72u IPv6 52535 0t0 TCP *:9066 (LISTEN)6.测试Mycat中间件
//需要开启日志为deblog可查看详细分离情况
[root@Mycat ~]# vim /soft/mycat/conf/log4j2.xml
<asyncRoot level="debug" includeLocation="true">
[root@Mycat ~]# tail -f /soft/mycat/log/mycat.log |grep "222222"
//连接Mycat使用不规则语句插入与查询, 便于查看策略
mysql> insert into blog.b valuse (222222);
mysql> select * from blog.b where id = '222222';5.MySQL数据库架构演变
文档更新时间: 2019-02-06 17:05 作者：李延召
