Mysql 5.7 架构之主从复制(异步)

一、概述

MySQL 从3.23版本开始提供复制的功能。复制是指将主数据库的DDL和DML 操作通过二进制日志传到复制服务器（也就从库）上，然后从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

MySQL 支持两种复制方式：基于行的复制和基于语句的复制。基于语句的复制（也称为逻辑复制）早在MySQL3.23 版本总就存在，而基于行的复制方式在5.1版本中才被加进来。这两种方式都是通过主主库上记录二进制日志、在备库重放日志的方式来实现异步的数据复制。这意味着，在同一时间点备库上的数据可能与主库存在不一致，并且无法保证主备之间的延迟。一些大的语句可能导致备库产生几秒、几分钟甚至几个小时的延迟。

注意：

由于MySQL 实现的是异步的复制，所以主从库之间存在一定的差距，在从库上进行的查询操作需要考虑到这些数据的差异、一般只有更新不频繁的数据或者对实时要求不高的数据可以通过从库查询，实时性要求高的数据仍需要从主数据库获取

二、复制解决的问题

数据分布
负载均衡
备份
高可用和故障切换
MySQL 升级测试

三、MySQL 复制是如何工作

MySQL 的复制原理大致如下：

mysql主从复制需要三个线程，master（binlog dump thread）、slave（I/O thread 、SQL thread）。

首先，MySQL 主库在事务提交时会把数据变更作为事件Events 记录在二进制日志文件Binlog中；MySQL 主库上的sync_binlog 参数控制Binlog 日志刷新磁盘。
主库推送二进制日志文件Binlog 中的事件到从库的中继日志 Relay Log 重做数据变更操作，通过逻辑复制以此达到主库和从库的数据一致。

MySQL 通过3个线程来完成主从库间的数据复制：其中 Binlog Dump 线程跑在主库上， I/O 线程和SQL线程跑在从库上。当在从库上启动复制（START SLAVE)时，首先创建I/O线程连接主库，主库随后创建Binlog Dump 线程读取数据库事件并发送I/O 线程，I/O线程获取到

开启binlog日志
创建I/O 线程连接主库；
主库创建Binlog Dump线程读取数据事件并发送给I/O线程；
I/O 线程获取到事件数据后更新到从库的中继日志 Relay Log 中；
从库上的SQL 线程读取中继日志Relay Log 中更新数据事件并应用

四、环境介绍

IP地址	角色	OS	软件版本
172.16.59.141	master	3.10.0-1160.45.1.el7.x86_64	5.7.36-log MySQL Community Server (GPL
172.16.59.142	slave01	3.10.0-1160.45.1.el7.x86_64	5.7.36-log MySQL Community Server (GPL
172.16.59.143	slave02	3.10.0-1160.45.1.el7.x86_64	5.7.36-log MySQL Community Server (GPL

五、流程架构图：

六、搭建过程（主从一样）

6.1 获取安装

[root@mysql-master ~]#  wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz
[root@mysql-slave01 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz
[root@mysql-slave02 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz

6.2 创建目录及解压

[root@mysql-master ~]# mkdir -p /app/soft
[root@mysql-master ~]# mkdir -p /data/mysql5_data/logs
[root@mysql-master ~]# mv mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz /app/soft
[root@mysql-master soft]# tar xf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz 
[root@mysql-master soft]# ln -s mysql-5.7.36-linux-glibc2.12-x86_64 mysql5

6.3 创建mysql用户

[root@mysql-master ~]# useradd  mysql -s /sbin/nologin

6.4 修改权限

[root@mysql-master data]# chown mysql:mysql -R mysql5_data/
[root@mysql-master soft]# chown mysql:mysql -R mysql5

6.5 创建my.cnf

[root@mysql-master ~]# vim  /data/mysql5_data/my.cnf
[mysqld]
basedir = /app/soft/mysql5
datadir = /data/mysql5_data/data
socket = /data/mysql5_data/mysql3306.sock
port = 3306
server-id = 1    # server_id 是唯一

#log setting
log_error = /data/mysql5_data/logs/error.log
slow_query_log = 1
long_query_time = 1
slow-query_log_file = /data/mysql5_data/slow_query.log
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 5
min_examined_row_limit = 100
expire_logs_days = 5

#Master-from-set
log-bin = mysql-bin
binlog_format = ROW

#innodb setting
innodb_buffer_pool_size = 64M
innodb_log_buffer_size = 2M
innodb_lock_wait_timeout = 120
innodb_log_file_size = 32M

注意：

slave 的server_id 要比主server_id 要大

6.6 初始化

~]# bin/mysqld --user=mysql --basedir=/app/soft/mysql5/ --datadir=/data/mysql5_data/data --initialize

初始化密码：会随机打印到屏幕上

6.7 启动数据并登陆

~]# bin/mysqld --defaults-file=/data/mysql5_data/my.cnf  --user=mysql &
~]# mysql -S /data/mysql5_data/mysql3306.sock -uroot -p

注意：

第一次登陆mysql需要修改密码：set password=password(‘123456’);

至此master就安装完成，两台 slave 一样执行，唯独my.cnf文件不一样；

七、在主库上创建用户并授权用于复制使用的

root@localhost: [ (none) ]>  create user slave@'%' identified by 'slave123';
root@localhost: [ (none) ]>  grant replication slave on 'slave'@'%';

八、查看master 库上日志文件名及偏移量

root@localhost: [ (none) ]>  show master status \G
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 841
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

九、在两个从库上执行

root@localhost: [ (none) ]>  change master to 
master_host='172.16.59.141',          # 表示实现复制的主机的IP地址
master_port=3306, 										# 表示实现复制的主机的端口号
master_user='slave', 									# 表示实现复制远程登陆主机的用户命
master_password='slave123',					  # 表示实现复制远程登录主机用户的密码
master_log_file='mysql-bin.000003',   # 表示实现复制的binlog 日志文件名
master_log_pos=841;                   # 表示实现复制的binlog 日志文件的偏移量

十、在从库上开启slave进程

root@localhost: [ (none) ]>  start slave;
Query OK, 0 rows affected (0.00 sec)

七、验证：

一、主库上查看是否有slave进程

root@localhost: [ (none) ]>  show processlist;
+----+-------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User  | Host                | db   | Command     | Time | State                                                         | Info             |
+----+-------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+
|  3 | root  | localhost           | NULL | Query       |    0 | starting                                                      | show processlist |
|  4 | slave | 172.16.59.143:41676 | NULL | Binlog Dump |   72 | Master has sent all binlog to slave; waiting for more updates | NULL             |
|  5 | slave | 172.16.59.142:45184 | NULL | Binlog Dump |   59 | Master has sent all binlog to slave; waiting for more updates | NULL             |
+----+-------+---------------------+------+-------------+------+---------------------------------------------------------------+------------------+

二、从库上查看状态

root@localhost: [ (none) ]>  show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.59.141
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 841
               Relay_Log_File: mysql-slave01-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

注意观察：

I/O 线程和SQL线程的状态是yes, 两个线程必须为也开启状态

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

abbott Blog

人生，生命不息，奋斗不止