MySQL Tree-shaped mode

Cascading replication Mode

Since the number of connections to the primary database is limited in the master-slave replication mode, it is not necessary to build more than 10 slave libraries for the primary server when it is close to full load in accordance with the common Criteria. A good way to do this is to set up multiple distribution main libraries between the main library and the slave library, removing the load pressure from the main library. The purpose of distributing the main library is to provide a binary log of the main library from the library.

When I configured the master-slave server, I used two servers: 10.19.34.126, 10.19.34.127, 10.19.34.91, 10.19.34.92. Where 10.19.34.126 is master, 10.19.34.127 is Repl, 10.19.34.91 is Slavea, 10.19.34.92 is SLAVEB.

1. Install the MySQL server on master.

2. Set the master my.conf file:

log-bin= Mysql-bin                #所有的数据操作写入二进制日志 to facilitate the synchronization of SQL statements to the trunk log lserver-id=1

3. Create an account on master that synchronizes master data from the library:

Mysql>grant all on * * to ' root ' @ '% ' identified by ' 123456 ';

4. Set the my.conf file on the REPL database:

log-bin= Mysql-bin                #所有的数据操作写入二进制日志 to facilitate the synchronization of SQL statements to the trunk log Log_slave_updates=1             #该参数用来设置将中继日志中的操作传入二进制日志, So that the main library from the database is used as the other instance. Replay_log=mysql-replay-bin   #用来存储中继日志server-id=2

5. View the update point of the binary file on the master database:

Mysql> Show master status;

　　　　

6. Create an account on REPL to synchronize REPL data from the library:

Mysql>grant all on * * to ' root ' @ '% ' identified by ' 123456 ';

7. Set the synchronization point of the synchronization master database on the REPL database:

mysql> stop Slave;

mysql> change MASTER to Master_host= "10.19.34.126", master_user= "root", master_password= "123456", master_port=3306 , master_log_file= "mysql-bin.000003", master_log_pos=393044781,master_connect_retry=10;

mysql> start slave;

8. Set the my.conf file on Slavea and SLAVEB:

Slavea's my.conf:

log-bin= Mysql-bin                #所有的数据操作写入二进制日志 to facilitate the synchronization of SQL statements to the trunk log replay_log=mysql-replay-bin   #用来存储中继日志server-id=3

SLAVEB's my.conf:

log-bin= Mysql-bin                #所有的数据操作写入二进制日志 to facilitate the synchronization of SQL statements to the trunk log replay_log=mysql-replay-bin   #用来存储中继日志server-id=4

9. View the update point for the REPL binary file:

Mysql> Show master status;

　　

10. Set the sync point from Repl on Slavea and SLAVEB:

mysql> stop Slave;

mysql> change MASTER to Master_host= "10.19.34.127", master_user= "root", master_password= "123456", master_port=3306 , master_log_file= "mysql-bin.000004", master_log_pos=393044425, master_connect_retry=10;

mysql> start slave;

Filtering replication Mode

Filtering replication can filter the entire data center through the data, synchronize to different from the database, so that the client's request load of data is dispersed, and when synchronizing the data, each from the library to synchronize only part of the data, to some extent, improve the ability of the main library. For example, in the main database to save the entire data center data, in order to share the pressure of the primary database, each from the library to synchronize only part of the data, you can spread the read request to the database.

1, filtering mode of the server: 10.19.34.126 for the master (including user, Department, message three database), 10.19.34.127 to Slavea (only synchronize user database), The 10.19.34.91 is SLAVEB (only the department database is synchronized), 10.19.34.92 is Slavec (only the message database is synchronized).

2, filtering mode configuration file:

Slavea's my.conf

log-bin= Mysql-bin                #所有的数据操作写入二进制日志 to facilitate the synchronization of SQL statements to the trunk log replay_log=mysql-replay-bin   #用来存储中继日志replicat-do-db= User     //This parameter sets the changes in the database to be synchronized to the trunk log, and the changes to the database are performed on the standby.

SLAVEB's my.conf

log-bin= Mysql-bin                #所有的数据操作写入二进制日志 to facilitate the synchronization of SQL statements to the trunk log replay_log=mysql-replay-bin   #用来存储中继日志replicat-do-db= Department     //This parameter sets the change of the database to be synchronized to the trunk log, and the changes to the database are performed on the standby.

Slavec's my.conf

log-bin= Mysql-bin                #所有的数据操作写入二进制日志 to facilitate the synchronization of SQL statements to the trunk log replay_log=mysql-replay-bin   #用来存储中继日志replicat-do-db= Message     //This parameter sets the change of the database to be synchronized to the trunk log, and the changes to the database are performed on the standby.

4, other configuration information in accordance with the configuration of the master-slave replication to set up.

MySQL Tree-shaped mode