MySQL 主从，5 分钟带你掌握

MySQL 主从一直是面试常客，里面的知识点虽然基础，但是能回答全的同学不多。

目前成都创新互联已为上千余家的企业提供了网站建设、域名、网络空间、网站托管、服务器托管、企业网站设计、孟连网站维护等服务，公司将坚持客户导向、应用为本的策略，正道将秉承"和谐、参与、激情"的文化，与客户和合作伙伴齐心协力一起成长，共同发展。

比如楼哥之前面试小米，就被问到过主从复制的原理，以及主从延迟的解决方案，因为回答的非常不错，给面试官留下非常好的印象。你之前面试，有遇到过哪些 MySQL 主从的问题呢？

所谓 MySQL 主从，就是建立两个完全一样的数据库，一个是主库，一个是从库， 主库对外提供读写的操作，从库对外提供读的操作 ，下面是一主一从模式：

对于数据库单机部署，在 4 核 8G 的机器上运行 MySQL 5.7 时，大概可以支撑 500 的 TPS 和 10000 的 QPS， 当遇到一些活动时，查询流量骤然，就需要进行主从分离。

大部分系统的访问模型是读多写少，读写请求量的差距可能达到几个数量级，所以我们可以通过一主多从的方式， 主库只负责写入和部分核心逻辑的查询，多个从库只负责查询，提升查询性能，降低主库压力。

MySQL 主从还能做到服务高可用，当主库宕机时，从库可以切成主库，保证服务的高可用，然后主库也可以做数据的容灾备份。

整体场景总结如下：

MySQL 的主从复制是依赖于 binlog 的，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上二进制日志文件。

主从复制就是将 binlog 中的数据从主库传输到从库上，一般这个过程是异步的，即主库上的操作不会等待 binlog 同步的完成。

详细流程如下：

当主库和从库数据同步时，突然中断怎么办？因为主库与从库之间维持了一个长链接，主库内部有一个线程，专门服务于从库的这个长链接的。

对于下面的情况，假如主库执行如下 SQL，其中 a 和 create_time 都是索引：

我们知道，数据选择了 a 索引和选择 create_time 索引，最后 limit 1 出来的数据一般是不一样的。

所以就会存在这种情况：在 binlog = statement 格式时，主库在执行这条 SQL 时，使用的是索引 a，而从库在执行这条 SQL 时，使用了索引 create_time，最后主从数据不一致了。

那么我们改如何解决呢？

可以把 binlog 格式修改为 row，row 格式的 binlog 日志记录的不是 SQL 原文，而是两个 event:Table_map 和 Delete_rows。

Table_map event 说明要操作的表，Delete_rows event用于定义要删除的行为，记录删除的具体行数。 row 格式的 binlog 记录的就是要删除的主键 ID 信息，因此不会出现主从不一致的问题。

但是如果 SQL 删除 10 万行数据，使用 row 格式就会很占空间的，10 万条数据都在 binlog 里面，写 binlog 的时候也很耗 IO。但是 statement 格式的 binlog 可能会导致数据不一致。

设计 MySQL 的大叔想了一个折中的方案，mixed 格式的 binlog，其实就是 row 和 statement 格式混合使用， 当 MySQL 判断可能数据不一致时，就用 row 格式，否则使用就用 statement 格式。

有时候我们遇到从数据库中获取不到信息的诡异问题时，会纠结于代码中是否有一些逻辑会把之前写入的内容删除，但是你又会发现，过了一段时间再去查询时又可以读到数据了，这基本上就是主从延迟在作怪。

主从延迟，其实就是“从库回放” 完成的时间，与 “主库写 binlog” 完成时间的差值， 会导致从库查询的数据，和主库的不一致 。

谈到 MySQL 数据库主从同步延迟原理，得从 MySQL 的主从复制原理说起：

总结一下主从延迟的主要原因 ：主从延迟主要是出现在 “relay log 回放” 这一步，当主库的 TPS 并发较高，产生的 DDL 数量超过从库一个 SQL 线程所能承受的范围，那么延时就产生了，当然还有就是可能与从库的大型 query 语句产生了锁等待。

我们一般会把从库落后的时间作为一个重点的数据库指标做监控和报警，正常的时间是在毫秒级别，一旦落后的时间达到了秒级别就需要告警了。

解决该问题的方法，除了缩短主从延迟的时间，还有一些其它的方法，基本原理都是尽量不查询从库。

具体解决方案如下：

在实际应用场景中，对于一些非常核心的场景，比如库存，支付订单等，需要直接查询从库，其它非核心场景，就不要去查主库了。

两台机器 A 和 B，A 为主库，负责读写，B 为从库，负责读数据。

如果 A 库发生故障，B 库成为主库负责读写，修复故障后，A 成为从库，主库 B 同步数据到从库 A。

一台主库多台从库，A 为主库，负责读写，B、C、D为从库，负责读数据。

如果 A 库发生故障，B 库成为主库负责读写，C、D负责读，修复故障后，A 也成为从库，主库 B 同步数据到从库 A。

如何在一台windows主机上搭建mysql主

1、首先要本建立两mysql服务（参考）指定同端口我主（3306）（3307）

2、修改主配置文件:

[mysqld]

server-id = 1

binlog-do-db=test #要同步数据库

#binlog-ignore-db=mysql #同步数据库,指定binlog-do-db应该用指定

log-bin=mysql-bin #要二进制记文件名称

修改配置文件:

[mysqld]

server-id = 2

log-bin = mysql-bin

replicate-do-db=test

3、主库添加用户 repl 并指定replication权限

create user 'repl'@'127.0.0.1' identified by 'asdf';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; -- --我指定数据库（test.*）报错指定全库（*.*）功

4、保持主mysqltest数据库初始状态致

般先所表加读锁copy磁盘数据库文件夹我直接停止服务数据文件拷贝

5、主数据库面运行show master status;记fileposition字段应参数

mysql show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 107 | test | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

6、库设置master：

mysql change master to master_host='127.0.0.1',master_port=3306,master_user='repl',master_password='asdf',master_log_file='mysql-bin.000001',master_log_pos=107;

Query OK, 0 rows affected (0.19 sec)

master_log_filemaster_log_pos应刚才show master status记参数

7、库启数据库复制功能

slave start;

mysql slave start;

Query OK, 0 rows affected (0.00 sec)

库通show slave status查看些参数

8. 主库创建表或插入数据库快能看

-- 主库

mysql create table tianyc_02(b int);

Query OK, 0 rows affected (0.16 sec)

mysql insert into tianyc_02 values(2013);

Query OK, 1 row affected (0.13 sec)

-- 库

mysql show tables;

+----------------+

| Tables_in_test |

+----------------+

| tianyc_01 |

| tianyc_02 |

+----------------+

2 rows in set (0.00 sec)

mysql select * from tianyc_02;

+------+

| b |

+------+

| 2013 |

+------+

1 row in set (0.00 sec)

同理搭建第二、第三节点

若满意请点击右侧【采纳答案】若问题请点击【追问】

希望我答您所帮助望采纳

~ O(∩_∩)O~

1、首先要在本地建立两个mysql服务（参考这里），指定不同的端口。我这里一个主（3306），一个从（3307）。 2、然后修改主配置文件: [mysqld] server-id = 1 binlog-do-db=test #要同步的数据库 #binlog-ignore-db=mysql #不同步的数据库,如果指定了binlog-do-db这里应该可以不用指定的 log-bin=mysql-bin #要生成的二进制日记文件名称 修改从配置文件: [mysqld] server-id = 2 log-bin = mysql-bin replicate-do-db=test 3、在主库添加一个用户 repl 并指定replication权限 create user 'repl'@'127.0.0.1' identified by 'asdf'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; -- --这里我指定数据库（test.*）时报错，而指定全库（*.*）时会成功。 4、保持主从mysql的te...

如何用mysql workbench做mysql主备

双击或右键打开MySQL Workbench，进入软件主界面。

点击new connection,会有个弹出框，让我们填写user(用户名)，password(密码)。

填写完用户名和密码，点击确定就会出现我们创建的数据库工作空间，例如：local instance MySQL56。

双击local instance MySQL56，进入数据库工作空间。

在左上方找到一个圆柱形带加号的图标，单击该图标，可以显示出数据库名让我们填例如：baidu。

创建好数据库，我们在左菜单栏找到baidu这个数据库，双击该数据库，可以看到下面有个tables,右键table，选择create table，就能创建表了。

如何在一台windows主机上搭建mysql主从配置

先在主数据库中创建新数据库rep_test。

然后编辑主数据库的my.ini文件

在[mysqld]节点中增加如下内容：

server-id=1 #指定唯一的ID，1至32，必须的

log-bin=mysql-log-bin #指定二进制日志存放路径，必须的

binlog-do-db=rep_test #指定要同步的数据库，必须的

#binlog-ignore-db=mysql #指定不要同步的数据库，如果指定了binlog-do-db就不用再指定该项

重启主数据库，然后在主数据库中建立一个备份账户

mysqlgrant replication slave on *.* to slave@192.168.1.128 identified by 'slave' ;

mysqlflush privileges;

PS:identified by 指定的slave是账号slave@192.168.1.128 的密码

显示主服务器的状态信息，并且找到File 和 Position 的值记录下来；

mysqlshow master status;

在从数据库中创建新的数据库rep_test。

然后编辑从数据库的my.ini文件

在[mysqld]节点中增加如下内容：

server-id=2 #指定唯一的ID，2至32，必须的，并且不能跟主数据库一样

replicate-do-db=rep_test #指定要同步的数据库，必须的

#replicate-ignore-db=mysql #指定不要同步的数据库，

重启从数据库，设置登录主数据库的账号和密码等信息，然后启动slave

mysqlchange master to master_host='192.168.1.2',master_user='slave',master_password='slave', master_log_file='mysql-bin.000002',master_log_pos=120;

mysqlstart slave;

查看从数据库的信息

mysqlshow slave status \G;

如果出现： Slave_IO_Running: YesSlave_SQL_Running: Yes以上两项都为Yes，那说明没问题了

本文名称：mysql主备怎么搭建,mysql 主备和主从
网页路径：http://myzitong.com/article/dsepioc.html