mysql的master和slave同步方案

时间：2022-10-20 21:23:32
  1. 同步原理
    1. master将改变记录到二进制日志(binary log)中
    2. slave将master的binary log events拷贝到它的中继日志(relay log)
    3. slave重做中继日志中的事件，改变自己的数据，相当于执行了master的所有操作
      mysql的master和slave同步方案
    4. 同步配置
      • master配置

        • 开启binlog vim /etc/my.cnf添加配置

          1
          2
          log_bin   = mysql-bin
          server_id = 1

        • 创建复制账号

          1
          GRANTREPLICATION SLAVE,RELOAD,SUPER ON*.*  TOroot@’slave ip’  IDENTIFIED BY'password';
      • 拷贝数据，如果master有数据而slave没有数据 则需要先把master的数据拷dump到slave中，如果是全新的master和slave(master和slave均没有数据则不需要此步骤)
      • slave配置

        • 对master运行

          1
          2
          3
          4
          5
          6
          SHOW MASTER STATUS;
          +------------------+-----------+--------------+------------------+-------------------+
          | File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
          +------------------+-----------+--------------+------------------+-------------------+
          | mysql-bin.000001 | 686034183 |              |                  |                   |
          +------------------+-----------+--------------+------------------+-------------------+
        • 配置slave

          • 开启binlog vim /etc/my.cnf添加配置

            1
            2
            log_bin   = mysql-bin
            server_id = 1

          • 启动slave执行sql

            1
            CHANGE MASTER TOMASTER_HOST='master host',  MASTER_USER='root', MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=686034183 ;

            其中的MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=686034183;为上一步在master上执行SHOW MASTER STATUS;的结果

          • 运行

            1
            2
            3
            4
            5
            6
            7
            8
            9
            10
            11
            12
            13
            14
            15
            16
            17
            18
            19
            20
            21
            22
            23
            24
            25
            26
            27
            28
            29
            30
            31
            32
            33
            34
            35
            36
            37
            38
            39
            40
            41
            42
            43
            44
            45
            46
            47
            48
            49
            50
            51
            52
            53
            54
            55
            56
            57
            SHOW SLAVE STATUS\G;
            *************************** 1. row ***************************
                           Slave_IO_State: Waiting formaster tosend event
                              Master_Host: 123.57.207.198
                              Master_User: root
                              Master_Port: 3306
                            Connect_Retry: 60
                          Master_Log_File: mysql-bin.000001
                      Read_Master_Log_Pos: 686034183
                           Relay_Log_File: mysqld-relay-bin.000002
                            Relay_Log_Pos: 686034113
                    Relay_Master_Log_File: mysql-bin.000001
                         Slave_IO_Running: Yes
                        Slave_SQL_Running: Yes
                          Replicate_Do_DB:
                      Replicate_Ignore_DB:
                       Replicate_Do_Table:
                   Replicate_Ignore_Table:
                  Replicate_Wild_Do_Table:
              Replicate_Wild_Ignore_Table:
                               Last_Errno: 0
                               Last_Error:
                             Skip_Counter: 0
                      Exec_Master_Log_Pos: 686034183
                          Relay_Log_Space: 686034287
                          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
            Master_SSL_Verify_Server_Cert: No
                            Last_IO_Errno: 0
                            Last_IO_Error:
                           Last_SQL_Errno: 0
                           Last_SQL_Error:
              Replicate_Ignore_Server_Ids:
                         Master_Server_Id: 2
                              Master_UUID: b4afeca0-1c33-11e5-856d-00163e002b08
                         Master_Info_File: /var/lib/mysql/master.info
                                SQL_Delay: 0
                      SQL_Remaining_Delay: NULL
                  Slave_SQL_Running_State: Slave has readallrelay log; waiting forthe slave I/O thread toupdateit
                       Master_Retry_Count: 86400
                              Master_Bind:
                  Last_IO_Error_Timestamp:
                 Last_SQL_Error_Timestamp:
                           Master_SSL_Crl:
                       Master_SSL_Crlpath:
                       Retrieved_Gtid_Set:
                        Executed_Gtid_Set:
                            Auto_Position: 0
            1 row inset(0.00 sec)

            其中Slave_IO_Running: Yes和Slave_SQL_Running: Yes则为正常，此时开始同步了

相关文章