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:
Copy codeThe 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', '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)
Caused by changing the 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)
The change master syntax is incorrect. A comma is dropped.
Mysql> change master
-> 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.