MySQL master-slave replication under Linux (ii)

A master multi-slave mode for MySQL

Principle Structure diagram

650) this.width=650; "Src=" http://img.blog.csdn.net/20170723123931731?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "style=" border:none; "/>

Configuration steps

1. Primary server:

1. Change the server ID 2. Enable binary log file 3. Create a user with replication 123123

For specific operation, see: http://www.crazyrui.pw:8088/index.php/2017/07/23/linux_mysql_savle_1/

2. First configuration from server

1. Change the server ID 2. Enable Relay_log 3. Connect the master server Mysql>change master to master_host= "IP", master_user= "USER", master_password= "PASSWORD", master_log_bin= "", Master_log_pas= ""; 4. Start synchronizing mysql>start slave;

How do you know the value of Master_log_bin and MASTER_LOG_PASD?
Use show slave status\g in the master server;
650) this.width=650; "Src=" http://img.blog.csdn.net/20170723121759161?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "style=" border:none; "/>

3. Configure the server for him

The configuration of the other server is the same as the configuration of the first server 11

MySQL's semi-synchronous replication mode

1. Principle

The client submits the data to the primary server, and the master server synchronizes the data from the server, as long as one of the synchronizations from the server completes, and the primary server replies with a message to the client. That is, the client submits data to the primary server, the master server synchronizes the data with multiple slave servers, multiple slave servers are executed, and a message is returned to the primary server when the primary server returns a message to the client.

2. Pre-conditions

To use semi-synchronous replication, the following conditions must be met: 1. MySQL version 5.5 and above 2. The variable have_dynamic_loading is yes 3. Asynchronous replication already exists 12341234

3. Primary server Configuration

1. Change the server ID 2. Enable binary log file 3. Create a user with replication 4. Install the plugin mysql> install plugin rpl_semi_sync_master soname ' semisync_master.so ' 5. Turn on semi-synchronous replication: mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;123456789123456789

650) this.width=650; "Src=" http://img.blog.csdn.net/20170723134708725?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "style=" border:none; "/>

4. From the server configuration

1. Change the server ID 2. Enable Relay_log 3. Install plugin mysql> Install plugin rpl_semi_sync_slave soname ' semisync_slave.so '; 4. Connect the master server Mysql>change master to master_host= "IP", master_user= "USER", master_password= "PASSWORD", master_log_bin= " ", master_log_pas=" "; 5. Turn on the semi-synchronous mode: mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1 6. Start synchronizing mysql>start slave;

650) this.width=650; "Src=" http://img.blog.csdn.net/20170723151621444?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "style=" border:none; "/>

650) this.width=650; "Src=" http://img.blog.csdn.net/20170723154644401?watermark/2/text/ ahr0cdovl2jsb2cuy3nkbi5uzxqva2fpcnvpmtiz/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70/gravity/ Southeast "alt=" here to write a picture describing "title=" "style=" border:none; "/>

Copy Filter

How can I use master-slave replication to back up only individual tables in the database?

The first method: Filtering principle on the primary server: Filtering the information in the binary log file on the primary server, implementing the filtering of the database in the configuration file, add the following lines binlog_do_db= database//Whitelist Binlo    g_ignore_db=//blacklist The second approach: filtering from the server is based on the SQL thread thread, which means that only the logs of that library are used. In the configuration file, add the following lines replicate_do_db=//Database filtering whitelist replicate_ignore_db=//Database filter blacklist Replicate_do_table=db.tbna Me//Make Table filter in database Replicate_ignore_table=db.tbname wildcard filter replicate_wild_do_table= REPLICATE_WILD_IGNORE_TABL E=

• Top

• 0

• Step

MySQL master-slave replication under Linux (ii)