Basic information

Main storehouse:

• Database 2 10.126.4.2
• Database 3 10.126.4.3

1. Stop database 3 external service

Prevent services from writing data through database 3 during synchronization

$ firewall-cmd --remove-port=3306/tcp

$ firewall-cmd --add-rich-rule="rule f amily="ipv4" source address="10.126.4.2" port protocol="tcp" port="3306" accept"

$ firewall-cmd --reload

2. Backup the main database

$ mysqldump -uroot -p --single-transaction --master-data=2 --no-autocommit -A >alldatas-190708.sql

Remember master? Log? File and master? Log? POS

$ head -n 30 alldatas-190708.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=801595396;

3. Cut off the synchronization of databases 2 and 3

Two hosts execute respectively

mysql>change master to master_host=''
mysql>stop slave;
mysql>reset slave;

4. Clear database 3

Empty data file

$ rm /var/lib/mysql
$ mv /db/mysql /db/mysql-190708
$ mkdir /db/mysql
$ ln -s /db/mysql /var/lib/mysql
chown mysql:mysql /var/lib/mysql
chown -h mysql:mysql /var/lib/mysql

Initialization

mysqld --initialize --user=mysql
grep password /var/log/mysqld.log

Reset master and slave

mysql> reset master;
mysql> show master status\G
mysql> stop slave;
mysql> reset slave;
mysql> show slave status\G

             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

5. Import the main database information and point to the main database again

$ mysql -uroot -p < alldatas.sql

mysql > change master to 
        -> master_host='10.126.4.2', 
        -> master_user='repl', 
        -> master_password='********', 
        -> master_log_file='binlog.???',
        -> master_log_pos=???;
mysql > start slave;
mysql > show slave status\G

6. Database 2 configuration main database information

Query the master log file and master log POS of database 3

10.126.4.3

show master status;

10.126.4.2

mysql > change master to 
        -> master_host='10.126.4.3', 
        -> master_user='repl', 
        -> master_password='********', 
        -> master_log_file='binlog.???',
        -> master_log_pos=???;
mysql > show slave status\G

7. Open database 3 external service

$ firewall-cmd --reload

www.tonglei.win