mysql 快速设置主从同步

怼烎@ 2022-03-16 09:58 221阅读 0赞

MySQL主从同步用在读写分离或数据备份，快速设置步骤如下：

# 设置步骤 #

1.先开启master slave的bin-log 并设置 server-id，然后重启mysql检查是否设置OK。  
2.得到master的`MASTER_LOG_FILE` 和`MASTER_LOG_POS`，在master上运行以下语句：

mysql> show master status\G
    *************************** 1. row ***************************
                File: mysql-bin.000085
            Position: 223251
        Binlog_Do_DB: 
    Binlog_Ignore_DB: 
    1 row in set (0.00 sec)

3.在master上加入slave用户`slave1`，并给予slave权限

create user slave1@'%' identified by 'slave1';
    grant replication slave on *.* to slave1@'%';
    flush privileges;

4.登陆slave，进行配置并且开启：

CHANGE MASTER TO 
    MASTER_HOST='192.168.1.223',
    MASTER_USER='slave1',
    MASTER_PASSWORD='slave1',
    MASTER_LOG_FILE='mysql-bin.000085',
    MASTER_LOG_POS=223251;
    
    start slave;

5.在master上看是否有主从同步：

此刻 slave的processlist

主从同步创建成功，这个时候可以在`master` 的这个数据库里面创建表，然后观察 slave是否有对应变化。

# 注意事项 #

mysql主从同步是slave 主动去链接master，所以需要在master上设置一个给slave的访问权限。  
而slave上必须设有对master的访问设置。

# 错误处理 #

错误1：无访问权限

Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Access denied for user 'slave1'@'%' (using password: YES) (Errno: 1045)

原因：slave只需要 replication 权限。  
解决办法：master上执行下面一句，然后slave上再`start slave`重启slave即可。

grant replication slave on *.* to slave1@'%';
    flush privileges;

错误2：

Duplicate entry '3' for key 'PRIMARY'' on query. Default database: 'master1'. Query: 'insert into sphinx(type) values (22)'

测试在slave对应表里面新增一条master不存在的记录，在 slave上查看`show slave status` 会有报错

mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.223
                      Master_User: slave1
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000085
              Read_Master_Log_Pos: 224849
                   Relay_Log_File: USER-20180409EL-relay-bin.000002
                    Relay_Log_Pos: 1445
            Relay_Master_Log_File: mysql-bin.000085
                 Slave_IO_Running: Yes
                Slave_SQL_Running: No
                  Replicate_Do_DB:
              Replicate_Ignore_DB:
               Replicate_Do_Table:
           Replicate_Ignore_Table:
          Replicate_Wild_Do_Table:
      Replicate_Wild_Ignore_Table:
                       Last_Errno: 1062
                       Last_Error: Error 'Duplicate entry '3' for key 'PRIMARY'' on query. Default database: 'master1'. Query: 'insert into sphinx(type) values (22)'
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 224392
                  Relay_Log_Space: 2119
                  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: 0
                    Last_IO_Error:
                   Last_SQL_Errno: 1062
                   Last_SQL_Error: Error 'Duplicate entry '3' for key 'PRIMARY'' onquery. Default database: 'master1'. Query: 'insert into sphinx(type) values (22)'
      Replicate_Ignore_Server_Ids:
                 Master_Server_Id: 1
                      Master_UUID:
                 Master_Info_File: D:\mysql\Data\master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State:
               Master_Retry_Count: 86400
                      Master_Bind:
          Last_IO_Error_Timestamp:
         Last_SQL_Error_Timestamp: 190227 12:48:41
                   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.00 sec)

解决办法，删除slave中该条记录。

# 最后 #

slave上查看slave状态

mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.223
                      Master_User: slave1
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000085
              Read_Master_Log_Pos: 225050
                   Relay_Log_File: USER-20180409EL-relay-bin.000002
                    Relay_Log_Pos: 2103
            Relay_Master_Log_File: mysql-bin.000085
                 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: 225050
                  Relay_Log_Space: 2320
                  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: 1
                      Master_UUID:
                 Master_Info_File: D:\mysql\Data\master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more up
    dates
               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
             Replicate_Rewrite_DB:
                     Channel_Name:
               Master_TLS_Version:
    1 row in set (0.00 sec)
    
    mysql>

master上查看 master状态

mysql> show master status\G
    *************************** 1. row ***************************
                File: mysql-bin.000085
            Position: 225050
        Binlog_Do_DB: 
    Binlog_Ignore_DB: 
    1 row in set (0.00 sec)
    
    mysql>

# 相关资料 #

官方教程设置主从同步  
[https://dev.mysql.com/doc/refman/5.7/en/replication-setup-slaves.html][https_dev.mysql.com_doc_refman_5.7_en_replication-setup-slaves.html]

[https_dev.mysql.com_doc_refman_5.7_en_replication-setup-slaves.html]: https://dev.mysql.com/doc/refman/5.7/en/replication-setup-slaves.html