MySQL 3-node master-slave synchronous replication Scheme test

on theMySQL 3 node master-slave synchronous replication Scheme Six. Test master-Slave synchronous replication 

now let's test it out . MySQL master-slave synchronization.

1. Inserting test data in the main library

Insert and delete 2 data to the M_S_REP database first on the main library Mastera. As follows:

mysql> INSERT INTO Test (id,content) VALUES (3, ' data3 ');mysql> insert INTO Test (id,content) VALUES (2, ' data2 '); Mysql> select * from test;

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/80/5D/wKioL1c-vmaTAqzPAABxbz-1Q7I651.png "style=" float: none; "title=" M35.png "alt=" Wkiol1c-vmataqzpaabxbz-1q7i651.png "/>

2. Login masterb View data sync results

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/80/5F/wKiom1c-vXuzfJDiAABLFaSFj4I752.png "style=" float: none; "title=" M36.png "alt=" Wkiom1c-vxuzfjdiaablfasfj4i752.png "/>

3. Log in to the slave node to view data synchronization results

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/80/5D/wKioL1c-vreigjOFAABHRZ1yOmI199.png "title=" M37.png "alt=" Wkiol1c-vreigjofaabhrz1yomi199.png "/>

through, we can see from the library The data is consistent between the M_s_rep database in slave and the M_s_rep database in Master Library master. Indicates that master-slave replication has been successful.

4. Let us now check the information for the Mastera mysql-bin file

# Mysqlbinlog Mysql-bin.000003|tail

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/80/5D/wKioL1c-vuzQL6nbAAA7xLtGs5Y481.png "title=" M38.png "alt=" Wkiol1c-vuzql6nbaaa7xltgs5y481.png "/>

5. Now let's check the information of the Masterb master.info file

# Cat Master.info |more

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/80/5D/wKioL1c-vxiS9yXbAAArKlhCN8c684.png "title=" M39.png "alt=" Wkiol1c-vxis9yxbaaarklhcn8c684.png "/>

6. Now let's check the information of the slave master.info file

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/80/5F/wKiom1c-vlTj_r1UAAAqcq_QiJY088.png "title=" M40.png "alt=" Wkiom1c-vltj_r1uaaaqcq_qijy088.png "/>

7. Master-Slave synchronous copy test end

By testing, the POS location node of the main library's Binlog file is compared to Master.info. We can see that the Master.info file does record the Binlog file name and POS location node for MySQL at synchronous replication.

Seven. Test Interop Master from synchronous replication1. Create a second table in the Masterb m_s_rep library M_s_rep

Let's create a test table to test Mastera and Masterb to master each other from the synchronous test.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/80/5D/wKioL1c-v3GjfzVTAABzbPqtJWs270.png "title=" M41.png "alt=" Wkiol1c-v3gjfzvtaabzbpqtjws270.png "/>

2. See if the M_s_table table is synchronized in the M_s_rep Library of Mastera

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/80/5D/wKioL1c-v5XwXI-RAABUfVL3tq4674.png "title=" M42.png "alt=" Wkiol1c-v5xwxi-raabufvl3tq4674.png "/>

3. See if the M_s_table table is synchronized in the M_s_rep library of slave

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/80/5F/wKiom1c-vsygxZyhAABVTHFy-II029.png "title=" M43.png "alt=" Wkiom1c-vsygxzyhaabvthfy-ii029.png "/>

4. Mutual master from synchronous replication test End

At this point, the program has been fully deployed testing completed, I think: "Mastera and Masterb regardless of the node down, can still guarantee the production environment of the master-slave relationship." Regardless of node state, to ensure data consistency, Mastera and Masterb are only writable if only one node can be written. Other nodes can use the lock table to prevent the data from being written to the inside to avoid brain fissures.

thinking:

If you are in a production environment, the database for different business units is increasing as your business grows. So how do you replicate a new database? The steps are as follows:

Mastera and Masterb Main Library lock tableàModify all nodes in themy.cnf configuration file to increase the name of the database that needs to be replicated synchronouslyà RestartMySQL serviceàExportMastera Database in the main libraryàjust create the database name from the libraryàImportMastera The database file from the main library to the libraryàMastera and Masterb Main Library unlockedàSince then, data from the main library has been changed automatically and copied to the library.

Reference Blog: http://www.ilanni.com/?p=7992

This article is from the JESSEN6 blog, so be sure to keep this source http://zkhylt.blog.51cto.com/3638719/1775420

MySQL 3-node master-slave synchronous replication Scheme test