MySQL database master-slave Configuration

MySQL Master/Slave configuration note:
There are four machines in total: A (10.1.10.28), B (10.1.10.29), C (10.1.10.30), and D (10.1.10.31 ).
After the configuration result: the A-C is the master and slave, B is the slave of A, D is the slave of C.

0) Preparations
After MySQL is installed on four machines, create an account for synchronization.
Add account:CopyCodeThe Code is as follows: insert into user (host, user, password, select_priv, insert_priv, update_priv, delete_priv, create_priv, drop_priv) values ('%', 'test ', password ('test'), 'y ');

Refresh the database:Copy codeThe Code is as follows: flush privileges;

1) configure the A-C to master and slave each other
Modify the configuration file a:Copy codeThe Code is as follows: Server-id = 1
Replicate-do-DB = test
Replicate-do-DB = test_admin
Log-bin = mysql-bin
Log-slave-Updates
Replicate-wild-do-table = test. %
Replicate-wild-do-table = test_admin. %
BINLOG-ignore-DB = MySQL
Slave-Skip-errors = all

Modify the C configuration file:Copy codeThe Code is as follows: Server-id = 3
BINLOG-do-DB = test
BINLOG-do-DB = test_admin
Log-bin = mysql-bin
Log-slave-Updates
Replicate-wild-do-table = test. %
Replicate-wild-do-table = test_admin. %
BINLOG-ignore-DB = MySQL
Slave-Skip-errors = all

Restarting MySQL takes effect
Set a as the primary node:
Stop synchronization:Copy codeThe Code is as follows: slave stop;

Clear server master logs:Copy codeThe Code is as follows: reset master;

Authorize the synchronization account:Copy codeThe Code is as follows: grant replication slave on *. * To 'test' @ '% 'identified by 'test ';

Refresh authorization:Copy codeThe Code is as follows: flush privileges;

Lock the database:Copy codeThe Code is as follows: flush tables with read lock;

Set C as follows:
Stop synchronization:Copy codeThe Code is as follows: slave stop;

Configure synchronization information:Copy codeThe Code is as follows: Change master to master_host = '10. 1.10.28 ', master_user = 'test', master_password = 'test', master_log_file = 'mysql-bin.000001', master_log_pos = 107;

Set C as the primary node:
Stop synchronization:Copy codeThe Code is as follows: slave stop;

Clear server master logs:Copy codeThe Code is as follows: reset master;

Authorize the synchronization account:Copy codeThe Code is as follows: grant replication slave on *. * To 'test' @ '% 'identified by 'test ';

Refresh authorization:Copy codeThe Code is as follows: flush privileges;

Lock the database:Copy codeThe Code is as follows: flush tables with read lock;

Set A as follows:
Stop synchronization:Copy codeThe Code is as follows: slave stop;

Configure synchronization information:Copy codeThe Code is as follows: Change master to master_host = '10. 1.10.30 ', master_user = 'test', master_password = 'test', master_log_file = 'mysql-bin.000001', master_log_pos = 107;

2) Set B to a's slave
Copy codeThe Code is as follows: Server-id = 2
Replicate-do-DB = test
Replicate-do-DB = test_admin
Log-bin = mysql-bin
Log-slave-Updates
Replicate-wild-do-table = test. %
Replicate-wild-do-table = test_admin. %
BINLOG-ignore-DB = MySQL
Slave-Skip-errors = all

Restart MySQL Service
Stop synchronization:Copy codeThe Code is as follows: slave stop;

Configure synchronization information:Copy codeThe Code is as follows: Change master to master_host = '10. 1.10.28 ', master_user = 'test', master_password = 'test', master_log_file = 'mysql-bin.000001', master_log_pos = 107;

Start synchronization:Copy codeThe Code is as follows: slave start;

3) set D to C's slave Copy codeThe Code is as follows: Server-id = 4
Replicate-do-DB = test
Replicate-do-DB = test_admin
Log-bin = mysql-bin
Log-slave-Updates
Replicate-wild-do-table = test. %
Replicate-wild-do-table = test_admin. %
BINLOG-ignore-DB = MySQL
Slave-Skip-errors = all

Restart MySQL Service
Stop synchronization:Copy codeThe Code is as follows: slave stop;

Configure synchronization information:Copy codeThe Code is as follows: Change master to master_host = '10. 1.10.30 ', master_user = 'test', master_password = 'test', master_log_file = 'mysql-bin.000001', master_log_pos = 107;

Start synchronization:Copy codeThe Code is as follows: slave start;

After that, you can create and add data to test whether the data can be added.
Common error handling is as follows:

1)
change master:
last_io_error: Error connecting to master 'repl1 @ IP: 100'-retry-time: 60 retries
2)
stop the slave process without unlocking:
mysql> stop slave;
error 1192 (hy000 ): can't execute the given command because you have active locked tables or an active transaction
3)
change master syntax error, drop the comma
mysql> change master to
-> master_host = 'IP'
-> master_user = 'user ',
-> master_password = 'passwd',
-> master_log_file = 'mysql-bin.000002 ',
-> master_log_pos = 106;
error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL Server version for the right syntax to use near 'master _ User = 'user',
master_password = 'passwd ',
master_log_file = 'mysql-bin.000002 'At Line 3

4)
Change master without stopping the slave Process
Mysql> change master to master_host = 'IP', master_user = 'user', master_password = 'passwd', master_log_file = 'mysql-bin.000001 ', master_log_pos = 106;
Error 1198 (hy000): this operation cannot be completed MED with a running slave; run Stop slave first

5)
Server-ID of a B is the same:
Last_io_error: Fatal error: the slave I/O thread stops because master and slave have equal MySQL Server IDs;
These IDS must be different for replication to work (or the -- replicate-same-server-ID option must be used on
Slave but this does not always make sense; please check the manual before using it ).
View server-ID
Mysql> show variables like 'server _ id ';
Manually modify server-ID
Mysql> set global server_id = 2; # The value here is the same as that set in my. CNF.
Mysql> slave start;
6) after changing the master, check the slave status and find that slave_io_running is no.