1, Overview

MySQL performs real-time data synchronization and does not play back the executed import database script. Therefore, before synchronization, the data at both ends should be consistent.

• Data synchronization:

  • Master slave replication (if the master database hangs up, the slave database will not automatically become the master. Application scenario: read-write separation)
  • Primary replication (high concurrency, prone to primary key conflict)

• Implementation principle:

  • be based on Binlog (binary log file location) this article is based on binlog
  • be based on GTID (global transaction identifier)

Software version

mysql5.7.20

2, Configure master-slave

1. Modify the configuration file

Linux : /etc/my.cnf

windows : mysql.ini

• Master library configuration

  [mysqld]
  # Database ID number. When it is 1, it means master, where Master_ The ID must be a positive integer value between 1 and 232 – 1. The master-slave server ID cannot be the same; [required] the unique ID of the server. The default is 1. Generally, the last segment of the IP is used (the master and slave are set to the last segment of their respective IP)
  server-id=1
  # Binary logs are enabled for the master database, but not for the slave database; 
  log-bin=mysql-bin 
  # Indicates that MySQL will brush binlog to disk every time a transaction is committed, which is the safest setting with the greatest performance loss.
  sync-binlog=1
  auto_increment_increment=1   #Step auto_imcrement.  Generally, if there are n main MySQL, fill in n
  auto_increment_offset=1   #Starting value. Generally, the nth main MySQL is filled. This is the first master mysql
  
  # You need to synchronize the database name and synchronize multiple databases. You can copy them (master database setting, slave database not setting). Specify binlog log to record the binary logs of those libraries.
  binlog-do-db=wr
  
  # binlog_ Format = mixed / / binlog log log format. mysql adopts statement by default, and mixed is recommended
  # Log bin = / data / MySQL / MySQL bin.log / / binlog log log file
  # expire_ logs_ Days = 7 / / binlog expiration cleanup time
  # max_ binlog_ Size = 100M / / binlog size of each log file
  # binlog_ cache_ Size = 4m / / binlog cache size
  # max_ binlog_ cache_ Size = 512M / / maximum binlog cache size
  

  Restart MySQL

  mysql restart
  

• Configure from library

  [mysqld]
  # Database ID number. When it is 1, it means master, where Master_ The ID must be a positive integer value between 1 and 232 – 1. The master-slave server ID cannot be the same; [required] the unique ID of the server. The default is 1. Generally, the last segment of the IP is used (the master and slave are set to the last segment of their respective IP)
  server-id=2
  # Binary logs are enabled for the master database, but not for the slave database; 
  log-bin=mysql-bin 
  # Indicates that MySQL will brush binlog to disk every time a transaction is committed, which is the safest setting with the greatest performance loss.
  sync-binlog=1
  auto_increment_increment=1   #Step value auto_imcrement.  Generally, if there are n main MySQL, fill in n
  auto_increment_offset=1   #Starting value. Generally, the nth main MySQL is filled. This is the first master mysql
  # Specify the binlog logs of those libraries to be synchronized in the slave library. Default all?
  replicate-do-db=wr
  
  # Official description: https://dev.mysql.com/doc/refman/5.7/en/replication-options-replica.html
  # binlog_ Format = mixed / / binlog log log format. mysql adopts statement by default, and mixed is recommended
  # Log bin = / data / MySQL / MySQL bin.log / / binlog log log file
  # expire_ logs_ Days = 7 / / binlog expiration cleanup time
  # max_ binlog_ Size = 100M / / binlog size of each log file
  # binlog_ cache_ Size = 4m / / binlog cache size
  # max_ binlog_ cache_ Size = 512M / / maximum binlog cache size
  
  To be tested:
  73:binlog-do-db=cbei_core
  19:replicate-do-db=cbei_core
  
  73:binlog-do-db=cbei_core
  19:# replicate-do-db=cbei_core
  
  
  73:# binlog-do-db=cbei_core
  19:# replicate-do-db=cbei_core
  

  Restart MySQL:

  mysql restart
  

  View log mode:

  Default:
  mysql> show variables like '%binlog_format';
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | binlog_format | ROW   |
  +---------------+-------+
  1 row in set (0.00 sec)
  
  mysql Parameters: binlog-do-db and replicate-do-db
  http://blog.sina.com.cn/s/blog_747f4c1d0102w9pp.html
  

2. Build master-slave replication

• Create a special user synchronization account in the master database that can log in from the database

  mysql>CREATE USER 'sync_dba'@'%' IDENTIFIED BY '<password>';
  mysql>GRANT REPLICATION SLAVE ON *.* TO 'sync_dba'@'%' IDENTIFIED BY '<password>';
  mysql>FLUSH PRIVILEGES;
  

• Specify the master library information in the slave library

  • View main library information in main library

    mysql>show master status;
    

  • Specify the master library information in the slave library

    mysql>CHANGE MASTER TO MASTER_HOST='172.18.163.73', MASTER_PORT=60001, MASTER_USER='sync_dba', MASTER_PASSWORD='<password>', MASTER_LOG_FILE='loganalysis.000002', MASTER_LOG_POS=194;
    # MASTER_LOG_POS: synchronization point (synchronization point and port are numeric types)
    

  • Start replication from server

    # The database cannot be copied
    mysql>start slave;
    

  • Check whether master-slave replication is configured successfully

    mysql>show slave status\G; # \G is the result displayed vertically by row
    
    # Master_Log_File，Read_Master_Log_Pos records the current master binlog file and location read by IO thread, corresponding to the master binlog file and location.
    Master_Log_File: loganalysis.000006
    Read_Master_Log_Pos: 194
    # Relay_Log_File，Relay_Log_Pos records the execution of SQL thread to the relay log file and location, which corresponds to the relay log file and location on the slave.
    Relay_Log_File: weileyi-officeapp.000016
    Relay_Log_Pos: 322
    # Relay_Master_Log_File，Exec_Master_Log_Pos records the files and locations from the SQL thread execution to the master binlog, and the corresponding binlog files and locations on the master.
    Relay_Master_Log_File: loganalysis.000004
    # Slave_ IO_ Running & Slave_ SQL_ Running = = yes = = > master-slave replication is normal
    Slave_IO_Running: Yes # IO communication between slave library and master library: normal operation
    Slave_SQL_Running: Yes # Slave library process: normal operation
    

3, Abnormal

• Exception: specify the master library information in the slave library

  • Phenomenon:

  ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.
  

  • reason:

    Bound.

  • solve:

    # Stop enabled binding
    stop slave;
    # From library: resetting bindings
    reset master;
    reset slave;
    # Reassign master library information
    CHANGE MASTER TO MASTER_HOST='172.18.163.73', MASTER_PORT=60001, MASTER_USER='sync_dba', MASTER_PASSWORD='blockchain@2019', MASTER_LOG_FILE='loganalysis.000001', MASTER_LOG_POS=154;
    # Restart replication
    start slave;
    

• Exception: Slave_IO_Running: No

  • Phenomenon:

    Slave_IO_Running: No
    

  • reason:

    From library Master_Log_File does not correspond to the file of the main library.

    • 1. View main library

      mysql> show master status\G;
      *************************** 1. row ***************************
                   File: loganalysis.000006
               Position: 194
           Binlog_Do_DB: cbei_core
       Binlog_Ignore_DB: 
      Executed_Gtid_Set: 705fa506-ddfa-11ea-99c4-fa163e6d1441:1-183
      1 row in set (0.00 sec)
      

    • View from library

      mysql> show slave status\G;
      *************************** 1. row ***************************
                     Slave_IO_State: Waiting for master to send event
                        Master_Host: 172.17.163.33
                        Master_User: sync_dba
                        Master_Port: 60001
                      Connect_Retry: 60
                    Master_Log_File: loganalysis.000006 # Corresponding main library File
                Read_Master_Log_Pos: 194 # Corresponding main library Position
                     Relay_Log_File: adam-officeapp.000016
                      Relay_Log_Pos: 322
              Relay_Master_Log_File: loganalysis.000004
                   Slave_IO_Running: No
                  Slave_SQL_Running: No
      

  • solve:

    mysql> slave stop;                 
    mysql> change master to MASTER_LOG_FILE='loganalysis.000006', MASTER_LOG_POS=0;  
    mysql> slave start;                               
    mysql> show slave status\G;
    

• Exception: Slave_SQL_Running: No

  • Phenomenon:

    Slave_SQL_Running: No
    

  • reason:

    When a write operation or restart is performed from the database, the transaction rollback causes the values corresponding to the record File and Position to be different from those of the main database.

    Master	Slave
    File	master_log_file
    Position	master_log_pos

  • solve:

    # Reassign
    mysql> stop slave;
    # Reassign master library information
    mysql> CHANGE MASTER TO MASTER_HOST='172.18.163.73', MASTER_PORT=60001, MASTER_USER='sync_dba', MASTER_PASSWORD='blockchain@2019', MASTER_LOG_FILE='loganalysis.000006', MASTER_LOG_POS=194;
    # Restart replication
    mysql> start slave;
    mysql> show slave status\G;
    

• Exception: the master-slave status is normal but cannot be synchronized.

  • Phenomenon:

    Slave_IO_Running: Yes # IO communication between slave library and master library: normal operation
    Slave_SQL_Running: Yes # Slave library process: normal operation
    

    The status is normal, but the slave library is out of sync.

  • reason:

    1. The playback location of the slave database log is the latest location of the master database.

    2. There is a problem with the main database space, and the log is truncated.

  • solve:

    • Slave

      # Stop synchronizing from library
      mysql> stop slave;
      

    • Master

      • Export data

      • Clean up log

        # View main library information
        mysql> show master status\G;
        # The main library recreates a binlog file and Position is restored.
        # mysql> flush logs;
        # Clear all binlog files and create binlog.00000 1
        mysql> reset master;
        

    • Slave

      Specify MASTER_LOG_FILE and MASTER_LOG_POS;

      # Clear all binlog files and create binlog.00000 1
      mysql> reset master;
      # Reassign master library information
      mysql> CHANGE MASTER TO MASTER_HOST='172.18.163.73', MASTER_PORT=60001, MASTER_USER='sync_dba', MASTER_PASSWORD='blockchain@2019', MASTER_LOG_FILE='loganalysis.000001', MASTER_LOG_POS=154;
      # Restart replication
      mysql> start slave;
      mysql> show slave status\G;
      

    • Master

      • Import data

• Exception: the log file cannot be found on the slave.

  • Phenomenon:

    MySQL [(none)]> show slave status\G;
    Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
    

  • reason:

    There is no. 00000 1 index file in the. Index log index file in BINLOG directory.

  • solve:

    Clear all files in BINLOG and restart MySQL.

    reset slave; Unable to clear BINLOG, delete it directly on disk.