MySql教程

Mysql配置主从同步

本文主要是介绍Mysql配置主从同步,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

一、什么是Mysql主从同步

 将主数据库中的DDL和DML操作通过二进制日志传输到从数据库上,然后将这些日志重新执行(重做);从而使得从数据库的数据与主数据库保持一致。

基本原理:

  • MySQL支持单向、异步复制,复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。
  • MySQL复制是基于主服务器在二进制日志中跟踪所有对数据库的更改。因此,要进行复制,必须在主服务器上启用二进制日志。每个从服务器从主服务器接收主服务器已经记录到日志的数据。
  • 当一个从服务器连接主服务器时,它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新,并在本机上执行相同的更新。然后封锁并等待主服务器通知新的更新。从服务器执行备份不会干扰主服务器,在备份过程中主服务器可以继续处理更新。

二、主从复制的作用

  • 主数据库出现问题,可以切换到从数据库。
  • 可以进行数据库层面的读写分离。
  • 可以在从数据库上进行日常备份。

三、释义图

Binary log:主数据库的二进制日志。

Relay log:从服务器的中继日志。

第一步:master在每个事务更新数据完成之前,将该操作记录串行地写入到binlog文件中()。

第二步:salve开启一个I/O Thread,该线程在master打开一个普通连接,主要工作是binlog dump process。如果读取的进度已经跟上了master,就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中()。

第三步:SQL Thread会读取中继日志,并顺序执行该日志中的SQL事件,从而与主数据库中的数据保持一致()。

四、主从配置实操

1、准备两台Mysql服务器(主:192.168.2.64,从:192.168.2.34),并满足以下要求

  • 服务器网络互通,端口(3306等)互通。
  • Mysql版本一致,不一致的话问题千奇百怪。

2、修改服务器Mysql配置,并重启Mysql服务,Linux下为 /etc/my.cnf ,Windows下为 my.ini 具体位置根据安装时设置为准。

master

[mysqld]
#设置主服务器id,在主从关系中保持唯一
server-id=1
#设置同步哪个数据库,此数据库变更将操作写入二进制日志Binary log
binlog-do-db=bds-oneaccept
#要生成的二进制日志文件名称前缀,例如:mysql-bin.000001
log-bin=mysql-bin

slave

[mysqld]
#设置从服务器id,在主从关系中保持唯一
server-id=2
#要复制的数据库名称,与主Mysql配置保持一致
replicate-do-db=bds-oneaccept
#要生成的二进制日志文件名称前缀,例如:mysql-bin.000001
log-bin=mysql-bin

  

3、创建用于复制的专门用户,并刷新用户权限(

grant replication slave on *.* to 'jianguan'@'192.168.2.34' indentified by 'Spgtest_1';

flush privileges;

4、获取主数据库此刻数据坐标,用于主从启动后,复制数据的起始位置,获取到这个值后,主数据库就不能再有数据的修改操作()。

show master status;

 5、在从数据库中设置复制数据的用户、坐标等信息(),在执行此操作之前需保证主从数据库现时刻数据一致,因为是从设置的坐标处开始复制。

mysql> mysql> change master to master192.168.2.64',
    -> master_port=3306,
    -> master_user='jiangkun',
    -> master_password='Spgtest_1',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=23431;

如果执行出现如下报错,则表示当前主从正在进行,需要先stop slave;再设置上述命令。

 6、启动复制

start slave;

 7、开始启动后,需要查看从Mysql复制状态

注意:其中Slave_IO_Running、Slave_SQL_Running两项为YES时,表示同步正在进行。

 

8、做简单测试,在主Mysql创建表,并插入数据,在从表查看,表已建,数据也已插入,主从同步成功!

 9、查看主库数据坐标,已到mysql-bin.000001文件的24587坐标。

10、从库的执行位置表示同步已经完成,至此主从同步设置完成。

 

 11、理解从库中的同步状态

 

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.64  //主库的ip地址
                  Master_User: jianguan  //master上的一个用户,用来主从复制,创建主从复制的时候建立,具有reolication
                  Master_Port: 3306
                Connect_Retry: 60   //连接中断后,重新尝试连接的时间间隔,默认值60s
              Master_Log_File: mysql-bin.000001     //当前I/O线程正在读取的主服务器二进制日志文件的名称
          Read_Master_Log_Pos: 24587     //当前I/O线程正在读取的二进制日志的位置             
               Relay_Log_File: data-anal-relay-bin.000002    //SQL线程当前正在读取和执行的中继日志文件的名称
                Relay_Log_Pos: 1476      //当前已读取和执行的中继日志的位置                      
        Relay_Master_Log_File: mysql-bin.000001   
             Slave_IO_Running: Yes   //YES表示I/O线程和主库连接正常并能实施复制工作,No则说明与主库通讯异常,一般只要网络端口通,复制所用用户名、秘密正常,很少出问题
            Slave_SQL_Running: Yes   //YES表示正常,非常容易出问题,比如手动在从表插入数据,主复制到从主键冲突就会导致同步终止
              Replicate_Do_DB: bds-oneaccept  //复制的数据库
          Replicate_Ignore_DB:   //复制中忽略的数据库
           Replicate_Do_Table:   //复制的表
       Replicate_Ignore_Table:   //复制中要忽略的表
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0   //slave的SQL线程读取日志参数的错误数量
                   Last_Error:     //错误消息
                 Skip_Counter:     //用于设置跳过sql执行步数。
          Exec_Master_Log_Pos: 24587   //slave SQL线程当前执行的事件,对应在master相应的二进制日志中的position。(结合Relay_Master_Log_File理解,而且在Relay_Master_Log_File这个值等于Master_Log_File值的时候,Exec_Master_Log_Pos是不可能超过Read_Master_Log_Pos的。)
              Relay_Log_Space: 1687   //所有原有的中继日志结合起来的总大小。
              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: 0   //这个值是时间戳的差值。是slave当前的时间戳和master记录该事件时的时间戳的差值。
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0   //最后一次IO错误号
                Last_IO_Error:     //最后一次IO错误消息
               Last_SQL_Errno: 0   //最后一次SQL错误号
               Last_SQL_Error:     //最后一次SQL错误消息
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1    //主库服务器id号
                  Master_UUID: cc63533c-b9b5-11ea-83e9-000c29ae6191   //主库服务器的UUID
             Master_Info_File: /var/lib/mysql/master.info  //从库中保存主库服务器相关的目录位置
                    SQL_Delay: 0     //一个非负整数,表示秒数,slave滞后于master多少秒
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400   //连接主库失败最多的重试次数
                  Master_Bind:     //slave从库在多网络接口的情况下使用,以确定用哪一个slave网络接口连接到master
      Last_IO_Error_Timestamp:    //最后一次I/O线程错误时的时间戳。
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.01 sec)

 

这篇关于Mysql配置主从同步的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!