Implementation method of MySQL master-slave replication

Time:2021-12-8

Implementation method of MySQL master-slave replication

  1. Prepare two machines with MySQL installed (installed on different machines). If it is a machine for virtual machine replication, you need to modify the UUID in the / var / lib / MySQL / auto.cnf file to ensure that the UUIDs of the two machines are different
  2. Backup the data from the primary database to the standby database. The two databases are consistent
    Execute on the main database (existing data)
    Execute command

    mysqldump  -uroot [email protected] -A  --master-data=2 --single-transaction -R -E --triggers --max-allowed-packet=64M >/tmp/full.sql

    Import binaries into / TMP / full.sql

Open VIM / TMP / full.sql to find the line of comments
Implementation method of MySQL master-slave replication

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=3050;
MASTER_ LOG_ File and master_ LOG_ The POS value is recorded and kept for backup. It is used for master-slave replication later

Then transfer full.sql to the machine where the standby database is located

scp /tmp/full.sql [email protected]:/tmp

MySQL connected to the standby database

Execute the following command:
Close binary
Synchronous data
Open binary

set sql_log_bin =0;
source /tmp/full.sql
set sql_log_bin =1;

The data synchronization is complete

  1. Configure the MySQL configuration files of the primary and standby databases respectively

    server_ Id = 1 # specifies the ID of MySQL 
    Log bin = MySQL bin # open binary log file
    auto_increment_increment=2
    auto_increment_offset=1
    port=13306
    sql_ mode=NO_ ENGINE_ SUBSTITUTION,STRICT_ TRANS_ Tables -- sqlmode needs to be set to non strict mode, otherwise the program may report an error https://www.cnblogs.com/zhoujinyi/p/8035413.html
    lower_case_table_names=1
    default-storage-engine = INNODB
    #MySQL master-slave idempotent mode
    slave_exec_mode=IDEMPOTENT
    #Skip specified error
    Slave skip errors = 10321062 # ignore errors
    
    #Update fast table out of sync first
    replicate-ignore-table=bim.qrtz_scheduler_state
    replicate-ignore-table=bim.tb_jgroupsping
  2. Configure master-slave
    a) Create a special account that can be copied on the main database. The creation statement is as follows
    GRANT REPLICATION SLAVE ON *.* to 'replication'@'%' identified by '[email protected]';
    Then execute the following SQL on the slave database:;
CHANGE MASTER TO MASTER_HOST = '10.88.1.73', 
 MASTER_USER = 'replication',                    
MASTER_PORT =13306,                            
 MASTER_PASSWORD = '[email protected]',        
 MASTER_LOG_FILE = 'mysql-bin.000008',
 MASTER_LOG_POS = 3050;    

master_ Host is the IP address of the primary server
master_ Port = 13306 (not configured here, 3306 by default)
master_ User: the authorized user of the master server, that is, the user created in front of the master
master_ Password: the password corresponding to the authorized user of the master server
master_ log_ File: Master binlog file name
master_ log_ Pos: the position value in the master binlog file. This value represents the node (starting point) where the slave database synchronizes data from the master database

After checking, you can start the thread from the library

start slave;

Then check the status

show slave status;

Implementation method of MySQL master-slave replication

Main inspection items:

slave_ io_ Running and slave_ sql_ Both running items areYESIt means success. At this time, the master database will synchronize data to the slave database

Attention

  1. If show slave status is displayed during the running of subsequent programs; If one of the two indicators is found to be no, it means that the program is disconnected. You need to check the MySQL log under / var / log to see the cause of the error. You can specify the master-slave replication as idempotent mode in the configuration file my.cnf and skip the specified error

    #MySQL master-slave idempotent mode
    slave_exec_mode=IDEMPOTENT
    #Skip specified error
    Slave skip errors = 10321062 # ignore errors
  2. Configuring master-slave replication with master-slave configuration is just a reverse configuration on the host (slave)
    On the opposite machine, configure another machine with a special account for master-slave replication

    GRANT REPLICATION SLAVE ON *.* to 'replication'@'%' identified by '[email protected]';

    Use show master status; View Master_ log_ File and master_ log_ pos
    And replace it in the following command

CHANGE MASTER TO MASTER_HOST = ${masterip},
MASTER_USER = ${masteruser},
MASTER_PORT = ${masterport},
MASTER_PASSWORD = ${masterpassword},
MASTER_LOG_FILE = ${master_log_file},
MASTER_ LOG_ POS = ${and master_log_pos};