Mysql database sharing _mysql from configuration method

MySQL master configuration small Note:
There are four machines: A (10.1.10.28), B (10.1.10.29), C (10.1.10.30), D (10.1.10.31).
After the configuration result: A-c is mainly from, B is a slave,d for C slave.

0) Preparation work
After installing MySQL on four machines, create an account for synchronization.
Add Account:

Copy Code code 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 ', ' y ', ' y ', ' y ', ' y ');

To refresh the database:

Copy Code code as follows:

FLUSH privileges;

1) Configure A-C to be based on each other
Modify a configuration file to:

Copy Code code 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 to:

Copy Code code 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

Restart MySQL is configured to take effect
Set A to Primary:
Stop sync:

Copy Code code as follows:

Slave stop;

To empty the server master log:

Copy Code code as follows:

Reset Master;

Authorized Sync Account:

Copy Code code as follows:

GRANT REPLICATION SLAVE on *.* to ' test ' @ '% ' identified by ' test ';

Refresh Authorization:

Copy Code code as follows:

Flush privileges;

To lock a database:

Copy Code code as follows:

Flush tables with read lock;

Set C to from:
Stop sync:

Copy Code code as follows:

Slave stop;

To configure synchronization information:

Copy Code code 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 to primary:
Stop sync:

Copy Code code as follows:

Slave stop;

To empty the server master log:

Copy Code code as follows:

Reset Master;

Authorized Sync Account:

Copy Code code as follows:

GRANT REPLICATION SLAVE on *.* to ' test ' @ '% ' identified by ' test ';

Refresh Authorization:

Copy Code code as follows:

Flush privileges;

To lock a database:

Copy Code code as follows:

Flush tables with read lock;

Set A to from:
Stop sync:

Copy Code code as follows:

Slave stop;

To configure synchronization information:

Copy Code code 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 from

Copy Code code 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 the MySQL service
Stop sync:

Copy Code code as follows:

Slave stop;

To configure synchronization information:

Copy Code code 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;

To start synchronization:

Copy Code code as follows:

Slave start;

3) Set D to C from

Copy Code code 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 the MySQL service
Stop sync:

Copy Code code as follows:

Slave stop;

To configure synchronization information:

Copy Code code 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;

To start synchronization:

Copy Code code as follows:

Slave start;

When you are done, you can create a test to add data.
the following are common error handling:

1)
Change Master results in:
Last_io_error:error connecting to master ' repl1@ip:3306 '-retry-time:60 retries
2)
To 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 a active transaction
3)
Change master syntax error, drop 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 a 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 is performed with a running slave; Run STOP SLAVE

5)
The Server-id of a B is the same:
Last_io_error:fatal error:the slave I/O thread stops because master and slave have MySQL server IDs;
These IDs must is different for replication to work (or the--replicate-same-server-id option must the is used on
Slave but this does is 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; #此处的数值和my the same as in CNF.
mysql> slave start;
6 After the change master, check the status of the slave and find that slave_io_running is no