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 -pBamboocloud@1234 -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

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 root@10.88.1.184:/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 #Specify the MySQL id 
   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 It 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=1032,1062  #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 'Bamboocloud@1234';
   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 = 'Bamboocloud@1234',        
 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;

Main inspection items:

slave_io_running and slave_ sql_ If both running items are YES, it 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=1032,1062  #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 'Bamboocloud@1234';

   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};