面试官：如何基于 MySQL 主从模式搭建上万并发的系统架构？

目录：

一、主从复制基本概念

二、MySQL主从复制介绍

三、主从搭建配置

四、MySQL主从复制常见问题分析

一、主从复制基础概念

在了解主从复制之前必须要了解的就是数据库的二进制日志(binlog),主从复制架构大多基于二进制日志进行。

1.1 二进制日志管理说明

二进制日志在哪？如何设置位置和命名？

在my.cnf文件中使用 log-bin = 指定；命名规则为 mysql-bin.000000 （后为6位数字）

二进制日志位置：

mysql> show variables like '%log_bin%' ;
+---------------------------------+-----------------------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------------------+
| log_bin | ON |
| log_bin_basename | /application/mysql/data/mysql-bin |
| log_bin_index | /application/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------------------+
6 rows in set (0.06 sec)

日志命名：

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2979 |
| mysql-bin.000002 | 120 |
+------------------+-----------+
2 rows in set (0.00 sec)

二进制日志记录什么？二进制日志中记录的是一个个完成的事件

二进制日志格式是怎样的？推荐使用row格式

查看当前使用的日志格式：

mysql> show variables like '%format%';
+--------------------------+-------------------+
| Variable_name | Value |
+--------------------------+-------------------+
| binlog_format | ROW |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| time_format | %H:%i:%s |
+--------------------------+-------------------+
8 rows in set (0.00 sec)

二进制日志如何滚动？每次重启都会刷新日志，也可以通过命令进行刷新 reset master;

二进制日志用来干嘛？备份恢复，起始点的备份恢复

二进制日志的操作命令？查看都有哪些二进制日志

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 2979 |
| mysql-bin.000002 | 167 |
| mysql-bin.000003 | 120 |
+------------------+-----------+
3 rows in set (0.00 sec)

查看当前使用的二进制日志文件：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

binlog*相关详情参照：*

http://www.cnblogs.com/clsn/p/8087678.html#_label6

1.2 mysql传统备份方式和缺陷

1. 二进制日志备份

2. mysqldump

3. 1. 必须有数据库服务器完成逻辑工作，需要更多地cpu周期

   2. 逻辑备份还原速度慢：需要MySQL加载和解释语句、转化存储格式、重建引擎

4. xtrabackup

5. 1. 文件大
   2. 不总是可以跨平台、操作系统和MySQL版本

1.3 MySQL主从复制能为我们做什么

• 高可用
• 辅助备份
• 分担负载

二、MySQL主从复制介绍

2.1 复制技术

作用：

1. 保证数据安全(异机实时备份)

2. 保证服务持续运行（宕机接管）

主从复制实现基本原理

1. 自带功能，复制是 MySQL 的一项功能，允许服务器将更改从一个实例复制到另一个实例。

2. 主服务器将所有数据和结构更改记录到二进制日志中。

3. 从属服务器从主服务器请求该二进制日志并在本地应用其内容。即通过把主库的binlog传送到从库，从新解析应用到从库。

2.2 复制架构

mysql复制的应用常见场景：

应用场景1：从服务器作为主服务器的实时数据备份

应用场景2：主从服务器实现读写分离，从服务器实现负载均衡

应用场景3：把多个从服务器根据业务重要性进行拆分访问

传统的 MySQL复制提供了一种简单的主–从复制方法，有一个主，以及一个或多个从。

主节点执行和提交事务，然后将它们（异步地）发送到从节点，以重新执行（在基于语句的复制中）或应用（在基于行的复制中）。

这是一个 shared-nothing 的系统，默认情况下所有 server 成员都有一个完整的数据副本。

（图）MySQL 异步复制

还有一个半同步复制，它在协议中添加了一个同步步骤。

这意味着主节点在提交时需要等待从节点确认它已经接收到事务。只有这样，主节点才能继续提交操作。

（图）MySQL 异步复制

在上面的两个图片中，可以看到传统异步 MySQL 复制协议（以及半同步）的图形展示。

蓝色箭头表示在不同 server 之间或者 server 与 client 应用之间的信息交互。

2.3 MySQL主从复制原理介绍

复制过程：

1. 开启binlog日志，通过把主库的binlog传送到从库，从新解析应用到从库。

2. 复制需要3个线程（dump、io、sql）完成，5.6从库多个sql。

3. 复制是异步的过程。主从复制是异步的逻辑的SQL语句级的复制。

复制前提：

1. 主服务期一定要打开二进制日志

2. 必须两台服务器（或者是多个实例）

3. 从服务器需要一次数据初始化

4. • 如果主从服务器都是新搭建的话，可以不做初始化
   • 如果主服务器已经运行了很长时间，可以通过备份将主库数据恢复到从库

6. 主库必须要有对从库复制请求的用户。

7. 从库需要有relay-log设置，存放从主库传送过来的二进制日志：

8. • show variables like ‘%relay%’;

9. 在第一次的时候，从库需要change master to 去连接主库。

10. change master信息需要存放到master.info中 ：

11. • show variables like ‘%master_info%’;

12. 从库怎么知道，主库发生了新的变化?通过relay-log.info记录的已经应用过的relay-log信息。

13. 在复制过程中涉及到的线程

14. 1. 从库会开启一个IO thread(线程)，负责连接主库，请求binlog，接收binlog并写入relay-log。

    2. 从库会开启一个SQL thread(线程)，负责执行relay-log中的事件。

    3. 主库会开启一个dump thrad(线程)，负责响应从IO thread的请求。

主从怎么实现的？

1. 通过二进制日志

2. 至少两台（主、从）

3. 主服务器的二进制日志“拿”到从服务器上再运行一遍。

4. 通过网络连接两台机器，一般都会出现延迟的状态。也可以说是异步的。

2.4 执行原理–第一次开启主从过程

1. 从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件：

2. 1. （user、password、port、ip）

   2. 并且让从库知道，二进制日志的起点位置（file名 position号）

   3. start slave

3. 从库的IO和主库的dump线程建立连接

4. 从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求

5. 主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程

6. 从库IO线程接收binlog evnets，并存放到本地relay-log中，传送过来的信息，会记录到master.info中。

7. 从库SQL线程应用relay-log，并且把应用过的记录到relay-log.info,默认情况下，已经应用过的relay会自动被清理purge。

到此为止，一次主从复制就完成

一旦主从运行起来：就不需要手工执行change master to，因为信息都会被存放到master.info（user、password、port、ip，上次获取过的binlog信息file和position）中。

详细的mysql replication 过程

三、 主从搭建配置

本次主从搭建使用mysql多实例进行实验。多实例配置参考文档进行配置：

http://www.cnblogs.com/clsn/p/8038964.html#_label8

3.1 多实例数据库slave配置

系统环境说明：

[root@db02 ~]# cat /etc/redhat-release
CentOS release 6.9 (Final)
[root@db02 ~]# uname -r
2.6.32-696.el6.x86_64
[root@db02 ~]# /etc/init.d/iptables status
iptables: Firewall is not running. # 注意：务必关闭防火墙（iptables selinux）
[root@db02 ~]# getenforce
Disabled
[root@db02 ~]# mysql --version
mysql Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using EditLine wrapper

1、启动多实例数据库

[root@db02 ~]# /data/3306/mysql start
Starting MySQL...
[root@db02 ~]# /data/3307/mysql start
Starting MySQL...

2、配置文件说明：

master 配置文件说明：

[root@db02 ~]# cat /data/3306/my.cnf
[client]
port = 3306
socket = /data/3306/mysql.sock

[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /application/mysql
datadir = /data/3306/data
log-bin = /data/3306/mysql-bin
server-id = 6 # server id 不能相同
skip_name_resolve = 0 # 跳过域名解析参数

[mysqld_safe]
log-error=/data/3306/mysql_3306.err
pid-file=/data/3306/mysqld.pid

slave 配置文件说明：

[root@db02 ~]# cat /data/3307/my.cnf
[client]
port = 3307
socket = /data/3307/mysql.sock

[mysqld]
user = mysql
port = 3307
socket = /data/3307/mysql.sock
basedir = /application/mysql
datadir = /data/3307/data
log-bin = /data/3307/mysql-bin
server-id = 7 # server id 不能相同
skip_name_resolve = 0 # 跳过域名解析参数
read_only = 1 # 从库只读 （非root用户 ）

[mysqld_safe]
log-error=/data/3307/mysql_3307.err
pid-file=/data/3307/mysqld.pid

3、在主库创建复制用户

登陆到主数据库中：

mysql -uroot -p123 -S /data/3306/mysql.sock

创建授权用户，注意是slave用户。

grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

4、初始化从库数据

备份主库当前数据

mysqldump -uroot -p123 -A -B -F --master-data=2 -S /data/3306/mysql.sock >/tmp/full.sql

部分参数说明：

• -F 刷新二进制日志

• –master-data [=＃]这会导致二进制日志的位置和文件名被追加到输出中。如果等于1，则将其打印为CHANGE MASTER命令; 如果等于2，那么该命令将以注释符号为前缀。

到从库进行恢复

mysql -uroot -p123 -S /data/3307/mysql.sock

恢复备份的数据

set sql_log_bin=0;
source /tmp/full.sql

5、开启从库复制

查看备份的当前使用的文件及POS号

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000012 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

登入数据库，进行slave配置。

mysql -uroot -p123 -S /data/3307/mysql.sock
CHANGE MASTER TO
MASTER_HOST='10.0.0.52',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000012',
MASTER_LOG_POS=120;
start slave; # 启动从库复制

该配置想关说明可以通过 help 获得。

mysql> help CHANGE MASTER TO
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;

3.2 测试主从同步

查看slave库的状态，主要查看：

Slave_IO_Running与Slave_SQL_Running是否都为Yes

主库进行操作，在从库验证

[root@db02 ~]# mysql -uroot -p123 -S /data/3306/mysql.sock
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database clsn;
Query OK, 1 row affected (0.00 sec)

在从库上可以看到该数据库已创建

[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| clsn |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)

至此mysql主从复制就搭建完成

3.3 忘记数据库密码？

shell> /application/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables --skip-networking &
mysql> update user set password=password('123') where user='root' and host='localhost';
mysql> flush privileges;

3.4 主从复制状态失败的原因？

Last_IO_Error: error reconnecting to master 'repl@10.0.0.52:3306' - retry-time: 60 retries: 1

原因：

1. 主机没启动，或者宕机，检查主库的状态。

2. 网络通信问题，使用ping命令进行检查；或使用mysql命令进行shell端登陆测试

3. 防火墙，selinux（务必检查）

4. 复制用户和密码、端口号、地址有问题，使用mysql命令进行shell端登陆测试。

5. mysql自动解析，会将连接的IP解析成主机名（skip-name-resolve = 0）写入my.cnf文件即可。

6. 从库IO异常关闭，通过show slave status \G 进行查看。

四、MySQL主从复制常见问题

4.1 从库binlog落后主库binlog？

从库记录的已经主库已经给我传送的binlog事件的坐标，一般在繁忙的生产环境下会落后于主库

show master status\G --- 主
show slave status \G --- 从
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 729

落后太远的原因：

• 硬件条件有关的，机器磁盘IO性能不足。

• 主要还是网络问题，网络传输的性能。

• 主库存放二进制日志的存储性能太低，建议binlog日志存咋SSD中。

• 主库DUMP线程太繁忙，主要发生在一主多从的环境下。

• 从库IO线程太忙

• 人为控制（delay节点、延时节点 ）

4.2 主库update，从库迟迟的没有更新

特殊情况：日志已经传过来了，数据并没有同步

一般情况：

1. 没开启SQL线程

2. 传的东西有问题（你要做的事情，我提前已经做了，不想重复做了，然后他就死了）

3. SQL线程忙

4. 人为控制了【delay(从库)节点、延时节点，一般生产设置为3-6小时之间，可以保证过去3-6小时之间的误操作，可以避免】

4.3 主从复制延时配置(从库配置)

停止从库复制

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

修改延时参数，MASTER_DELAY，单位位S （秒）。

mysql>CHANGE MASTER TO MASTER_DELAY = 30;
Query OK, 0 rows affected (0.07 sec)

启动从库复制

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

查看配置是否生效

mysql> show slave status \G
……
              SQL_Delay: 30

4.4 从库安全配置（其他用户只读）

修改my.cnf配置文件，添加只读参数

read_only = 1 ====> 控制普通用户
innodb_read_only = 1 ====> 控制root用户，正常情况不要加

添加完成后重启数据库

mysql> show variables like '%read_only%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| tx_read_only | OFF |
+------------------+-------+
3 rows in set (0.00 sec)

延时从库: delay节点、延时节点

4.5 主从复制故障及解决（跳过错误）

命令行设置

stop slave; #<==临时停止同步开关。
set global sql_slave_skip_counter = 1 ; #<==将同步指针向下移动一个，如果多次不同步，可以重复操作。
start slave;

在配置文件修改，设置要跳过的pos

/etc/my.cnf
slave-skip-errors = 1032,1062,1007

在mysql中可以跳过某些错误,但是最好的解决办法，重新搭建主从复制。

4.6 延时节点概念 –> SQL线程延时？

Last_SQL_Errno: 0
Last_SQL_Error:

原因:

1. 主库做操作的对象，在从库不存在

2. 主库做操作的对象属性不一致

3. 主库做操作的对象，从库已经存在

   ……

4.7 Slave_*_Running：？

1. Slave_IO_Running I/O 线程正在运行、未运行还是正在运行但尚未连接到主服务器。可能值分别为Yes、No 或 Connecting。

2. Slave_SQL_Running SQL线程当前正在运行、未运行,可能值分别为Yes、No

3. 主服务器日志坐标：Master_Log_File 和 Read_Master_Log_Pos 标识主服务器二进制日志中 I/O 线程已经传输的最近事件的坐标。

4. 如果Master_Log_File和Read_Master_Log_Pos 的值远远落后于主服务器上的那些值，这表示主服务器与从属服务器之间事件的网络传输可能存在延迟。

4.8 中继日志坐标

Relay_Log_File 和 Relay_Log_Pos 列标识从属服务器中继日志中 SQL 线程已经执行的最近事件的坐标。

这些坐标对应于 Relay_Master_Log_File 和 Exec_Master_Log_Pos 列标识的主服务器二进制日志中的坐标。

如果 Relay_Master_Log_File 和 Exec_Master_Log_Pos 列的输出远远落后于 Master_Log_File 和Read_Master_Log_Pos 列（表示 I/O 线程的坐标）

这表示 SQL 线程（而不是 I/O 线程）中存在延迟。即，它表示复制日志事件快于执行这些事件。

4.9 单一主从需要改变的地方

从库的作用

1、相当于实时备份

2、使用从库备份

3、一主多从应对读多的业务需求

如果，从库只做备份服务器用，那么主库的压力会不减反增。因为，所有的业务都在主库实现，读和写，dump线程读取并投递binlog

解决方案：

1. 可不可以挪走一部分读业务到从库，读写分离

2. 一主多从应对读多的业务需求，一旦发展成这个架构，dump线程投递binlog的压力更大

3. 多级主从，采用中间库缓解主库dump的压力，会出现中间库瓶颈的问题，选择blackhole引擎，看性能与安全的权衡

4. 双主模型：缓解，数据一致性难保证

5. 环状复制

-END-

作者：惨绿少年

来源 :cnblogs.com/clsn/p/8150036.html#auto_id_39