基于bin-log&position搭建主从架构MySQL

MySQL 前端之家
前端之家收集整理的这篇文章主要介绍了基于bin-log&position搭建主从架构MySQL，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

目录

一、MysqL主从搭建

搭建主从架构的MysqL常用的有两种实现方式：

  1. 基于binlog的fileName + postion模式完成主从同步。
  2. 基于gtid完成主从同步搭建。

本篇就介绍如何使用第一种方式完成MysqL主从环境的搭建。

基于fileName和position去实现主从复制，所谓的fileName就是bin-log的name，position指的是slave需要从master的binlog的哪个位置开始同步数据。

这种模式同步数据方式麻烦的地方就是需要我们自己通过如下的命令去查找应该从哪个bin-log的哪个position去开始同步。

二、主库

2.1、确定主库的binlog是否开启

命令：show variables like 'bin-log'

原因：了解MysqL中常见的三个日志：

  1. 单机MysqL的undolog日志中记录着如何将现有的数据恢复成被修改前的旧数据。
  2. 单机MysqL的redolog. 中记录事物日志。
  3. 主从模式的MysqL通过bin-log日志同步数据。

2.2、骚气的命令

grant replication slave on *.* to MysqLsync@"127.0.0.1" identified by "MysqLsync123";

这条命令是在干什么呢？

捋一下思路：我们做主从同步，在主库这边我们其实会单独创建一个账号用于实现主从同步。下面的命令其实就会帮我们创建出 username=MysqLsync password=MysqLsync123的账户专门用户主从同步使用。

执行完上面的命令后，执行如下的命令查看上面的grant执行结果：

select user,host from MysqL.user like '%MysqLsync%'

2.3、记录主库的master状态

注意主库的查看主库当前是第几个binlog，已经数据的position。

因为一会从库就是根据这两个信息知道自己该从主库的第几个binlog的什么positon开始同步。

image-20200526192749846

三、从库

3.1、从库和主库保持同步

从库执行change语句，和主库保持同步

CHANGE MASTER TO
    MASTER_HOST='10.157.23.158',MASTER_USER='MysqLsync',MASTER_PASSWORD='MysqLsync123',MASTER_PORT=8882,MASTER_LOG_FILE='MysqL-bin.000008',MASTER_LOG_POS=1013;

  
 CHANGE MASTER TO
  MASTER_HOST = '${new_master_ip}',MASTER_USER = '${user}',MASTER_PASSWORD = '${password}',MASTER_PORT = ${new_master_port},master_auto_position = 1; 
  
CHANGE MASTER TO
  MASTER_HOST = '10.157.23.123',MASTER_USER = 'MysqLsync',MASTER_PASSWORD = 'MysqLsync123',MASTER_PORT = 8882,master_auto_position = 1;

3.2、开启主从同步

start slave
show slave status \G

image-20200409150520640

当我们可以看到 io线程和sql线程的状态都是yes时，说明此刻主从同步已经搭建完成了。

3.3、从库：如何断开主从

stop slave io_thread
stop slave sql_thread

3.4、主库：如何断开主从

把用于进行主从同步的账号删除就好了

drop user ${user}@${slave_ip}

​

四、中断处理

中断处理部分说的是，一开始我们搭建主从很可能并不是一番风顺的，就比如上面的Slave_IO_Running和Slave_sql_Running很可能处于NO的状态。下面介绍一下常见的解决方式。

4.1、Slave_IO_Running异常

Slave_IO_Running：no/connecting

这说明从库连接不上主库，或者是一直处于正在连接的状态。

可能是主库没有对从库进行授权，如果已经授权了那么重启一下salve。

另一种原因就是master和slave的MysqLd相关配置文件中，配置了相同server_id。

还有可能你在执行change master命令时，输入的主库相关的信息本来就是错误的。

4.2、Slave_sql_Running异常

Slave_sql_Running:no

一般这种情况是bin-log中的sql出问题了。

第一种情况：可能我们配置了slave只能读，但是却有写请求打过来了，导致slave不能继续往下执行。

第二种解决思路：让slave跳过有问题的这个事件，但是还是得把事件的原因查明白，不然不推荐直接跳过这个事件。

stop slave;
set global sql_slave_skip_counter=1;
start slave;

第三种思路：我们提前配置好错误号机制，当slave在同步的过程中，碰到我们配置的错误号采取自动跳过的机会而不再去默认的终止同步数据。

# 一般我们可以像下面这样，在my.cnf中的[MysqLd]的启动参数中添加如下内容
--slave-skip-errors=1062,1053  
--slave-skip-errors=all  
--slave-skip-errors=ddl_exist_errors

# 通过如下语句查看当前MysqL配置的变量
MysqL> show variables like 'slave_skip%';  

# 通过如下命令可以查看到出现的errorno
show slave status; # 观察Last_Errno

# 常见的errorno
1007：数据库已存在，创建数据库失败
1008：数据库不存在，删除数据库失败
1050：数据表已存在，创建数据表失败
1051：数据表不存在，删除数据表失败
1054：字段不存在，或程序文件跟数据库有冲突
1060：字段重复，导致无法插入
1061：重复键名
1068：定义了多个主键
1094：位置线程ID
1146：数据表缺失，请恢复数据库
1053：复制过程中主服务器宕机
1062：主键冲突 Duplicate entry '%s' for key %d

第四种思路：手动给slave调整fileName和position的位置（如何允许放弃之前的一部分数据，而从当前最新的数据开始同步）

# 停掉slave
slave stop
# 进入master
# 停止master的写操作
# 查看master中当前bin-log和position
show master status；
# 切换回slave从新根据最新的position和bin-log进行同步
# 进入master，开启master的写操作

五、流程

通过fileName和position完成定位，从库会向主库发送命令，BINLOG_DUMP ，命令中包含有positon和fileName， 主库获取到这些信息之后，指定name到指定position往从库发送bin-log

六、可能会遇到的问题

6.1、问题一：

change master时报错了

image-20200526202556494

报错说：ERROR 1776 (HY000): Parameters MASTER_LOG_FILE,MASTER_LOG_POS,RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.

原因是我之前使用过gtid进行同步数据，当时将master_auto_position设置成了1，再想使用手动指定position的主从同步方式需要得像下面这样，change回去。

CHANGE MASTER TO
		MASTER_AUTO_POSITION=0;

6.2、问题二：

如果我随便写了个position再搭建主从时，会发生什么？

下面的 MASTER_LOG_POS = 1003 就是我随便写的一个position，然后你可以看到两个现象

  1. Slave_IO_Running : No
  2. Last_IO_Error 位置报了个严重的错误
MysqL>  CHANGE MASTER TO
    ->        MASTER_HOST='10.157.23.xxx',->         MASTER_USER='MysqLsync',->        MASTER_PASSWORD='MysqLsync123',->        MASTER_PORT=8882,->        MASTER_LOG_FILE='MysqL-bin.000008',->       MASTER_LOG_POS=1003;
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:
                  Master_Host: 10.157.23.123
                  Master_User: MysqLsync
                  Master_Port: 8882
                Connect_Retry: 60
              Master_Log_File: MysqL-bin.000008
          Read_Master_Log_Pos: 1003
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: MysqL-bin.000008
             Slave_IO_Running: No
            Slave_sql_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table: MysqL.%,test.%
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1003
              Relay_Log_Space: 521
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event 'MysqL-bin.000008' at 1003,the last event read from './MysqL-bin.000008' at 123,the last byte read from './MysqL-bin.000008' at 1022.'
               Last_sql_Errno: 0
               Last_sql_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2787871625
                  Master_UUID: a5f1d6b2-8f9a-11ea-8138-b8599f2ef058
             Master_Info_File: MysqL.slave_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: 200529 10:22:46
     Last_sql_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 00c755a6-7a07-11ea-8701-b8599f2ef058:33-222,40efcb1b-7a1f-11ea-84ac-b8599f229b38:1-20,7e2dcb21-7d3b-11ea-aa0c-b8599f2ef058:1-18,9e6027f2-7ae9-11ea-ac13-b8599f2ef058:1409-7176,a5f1d6b2-8f9a-11ea-8138-b8599f2ef058:6-9:12-13:15,e90fdd54-7e04-11ea-8b23-b8599f2ef058:1-11
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

6.3、问题三：

假设我们有这样的场景：

场景：现在主库有7条数据，从库有5条数据，搭建主从时如何让从库从第六条开始同步？

这种情况仅仅是我们在做这种小实验，为啥这样说呢？如果是为线上的业务搭建搭建主从MysqL的话，大概率我们会清空主库然后再做同步。如果数据很重要，我们会对主库中的数据进行一次全量拷贝到从库（拷贝var包）。再做主从同步。

在线上的环境中，主从的数据是会强一致的，从库只会接受业务方的读流量，也许网络环境很恶劣从库同步的速度明显比主库写入到速度低，但是只要从库没有说跳过了某个binlog而少同步了某条记录，我们都可以认为它们是正常的主从同步。不会出现主从中断的情况。

线上的环境中什么情况下会出现主从中断呢？比如说，从库同步数据时，从库同步binlog时丢了一条数据，这时业务上突然来了条update语句，要更新数据，然后从库美滋滋的回放在主库dump过来的binlog时发现，竟然自己没有需要更新的这条记录，就会报错，这时为了业务止损，我们要在第一时间下线从库，然后去分析哪里出现问题了。

针对这个实验我们这样去binlog中查看第5，6条数据的position，然后在从库中使用相应的position完成主从数据的同步。

进入主库，通过下面的命令查看binlog

MysqLbinlog --no-defaults -vv --base64-output=decode-rows ../var/MysqL-bin.000008 | less

image-20200529095601076

找到了指定的binlog和指定的end_log_pos

比如从库中没有第10，11条数据，我们就能通过end_log_pos = postion = 1013完成定位。

CHANGE MASTER TO
    MASTER_HOST='10.157.23.158',MASTER_LOG_POS=1013;

开启同步，并查看状态

start slave;
show slave status\G;

再去查看从库就能发现，从你指定的position开始往后和主库的数据保持同步的。

6.4、问题四：

问：主从接流量的情况是怎样的？业务的CRUD请求是如何被主从平分消费的？

答：默认这种架构下是读写分离，也就是说，仅读流量会打到从库中

问：那如果我们在从库所在的机器上本地登陆，然后手动执行删除的操作能成功吗？

答：是的，可以执行成功。

问：我可以简单粗暴的限制从库仅读吗？

答：可以的，像下面这样

MysqL> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)


set global read_only=0;  #关闭只读,可以读写
set global read_only=1;  #开始只读模式

6.5、问题五：

假设主库中有1～7 共7条数据，从库中有1～5五条数据。也就是说，主库从库中前五条数据一样，但是主库比从库多了两条新数据。

这时我们搭建主从同步时搞一搞事情，重复这个动作：在从库断开同步，然后查到主库第一个binlog中的数据的记录，确定我们要查找的position，再重新构建主从环境。观察一下从库这边数据的同步情况，以及会出现什么问题？从库这边的数据会成为double吗？

答：数据不会double的

6.6、问题六：

假设从库执行changemaster时，主库MASTER_HOST填错了:

在查看slave 状态时，我们可以看到Last_IO_Error列有报错提示： error connecting to master 

MysqL> CHANGE MASTER TO
    ->     MASTER_HOST='10.157.23.158',->     MASTER_USER='MysqLsync',->     MASTER_PASSWORD='MysqLsync123',->     MASTER_PORT=8882,->     MASTER_LOG_FILE='MysqL-bin.000008',->     MASTER_LOG_POS=1013;
Query OK,0 rows affected (0.00 sec)

MysqL> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 10.157.23.123
                  Master_User: MysqLsync
                  Master_Port: 8882
                Connect_Retry: 60
              Master_Log_File: MysqL-bin.000008
          Read_Master_Log_Pos: 1003
               Relay_Log_File: relay-log.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: MysqL-bin.000008
             Slave_IO_Running: Connecting
            Slave_sql_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table: MysqL.%,test.%
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1003
              Relay_Log_Space: 154
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'MysqLsync@10.157.23.123:8882' - retry-time: 60  retries: 1
               Last_sql_Errno: 0
               Last_sql_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             Master_Info_File: MysqL.slave_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: 200529 10:13:34
     Last_sql_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 00c755a6-7a07-11ea-8701-b8599f2ef058:33-222,e90fdd54-7e04-11ea-8b23-b8599f2ef058:1-11
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:

6.7、问题七：

假设这种场景：假设主从现在的数据是一致的，然后你在从库所在的机器上本地登陆，然后手动删除一条，再从主库写入数据，那从库还能同步成功吗？

答：从库依然会同步成功，但是其实这时候已经算是事故了，主从数据不一致，万一业务打来一条sql刚好使用你删的数据，那就会报错。

如果觉得对你有帮助欢迎关注我，后面还会分享通过gtid搭建主从MysqL以及其他相关的知识点

猜你在找的MySQL相关文章

Mysql通过frm和ibd恢复数据库
昨天的考试过程中，有个考点的服务器蓝屏重启后发现Mysql启动不了(5.6.45 x32版本，使用in...
MySQL 数据库中的数据类型
整数类型 标准 SQL 中支持 INTEGER 和 SMALLINT 这两种类型，MySQL 数据库除了支持这两种类...
MySQL Select 语句执行顺序
一条 SQL 查询语句结构如下： SELECT DISTINCT <select_list> FROM &lt...
MySQL 数据备份与恢复
数据备份 1. 备份数据库 使用 mysqldump 命令可以将数据库中的数据备份成一个文本文件，表...
MySQL 大表拆分
概述 在实际工作中，在关系数据库（MySQL、PostgreSQL）的单表数据量上亿后，往往会出现查...
MySQL 索引详解
索引的含义和特点 索引是创建在表上的，是对数据库表中一列或多列的值进行排序的一种数据结...
MySQL 索引失效场景总结
查询条件有 or 假设在 customer_name 字段设置了普通索引，执行以下 sql： # type: ref, p...
MySQL 主从复制
概述 MySQL 的高可用主要通过主从复制来实现，同时在主从复制的基础上可以构建一个 MySQL ...
MySQL 日志管理
概述 日志文件记录 MySQL 数据库运行期间发生的变化，当数据库遭到意外的损害时，可以通过...
MySQL 事务和锁
事务概述 当多个用户访问同一份数据时，一个用户在更改数据的过程中，可能有其他用户同时发...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问