MySQL master-slave replication and read-write separation Technology Example (a) master-slave replication

System version: CentOS 6.5 mysql version: mysql-5.5.38

Production environment, if the same database server to do write service and read services, whether from security, high availability or high concurrency and other angles can not meet the actual demand, therefore, the general use of multiple database servers through master-slave replication to synchronize data to improve security, and read-write separation to improve the database's concurrent load capacity, as well as high availability.

Case topology diagram:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/7C/9E/wKiom1bTyVLz3IPaAACa-oMMbbs353.jpg "title=" Experimental topology diagram. jpg "alt=" wkiom1btyvlz3ipaaaca-ommbbs353.jpg "/>

Case Environment:

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/7C/9E/wKiom1bTyWfDC0B1AAExfoS_TEE580.jpg "title=" Case environment. jpg "alt=" wkiom1btywfdc0b1aaexfos_tee580.jpg "/>

Build MySQL master-slave replication

MySQL master-slave replication and read-write separation is inseparable, only in the premise of the implementation of master-slave replication to complete the read and write separation

First, set up a time synchronization environment

1. Install the Configure NTP service on the Master(MySQL master ) Server

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/7C/9E/wKiom1bTyyHgCMTDAAAQUmK-31M367.jpg "title=" Install ntp.jpg "alt=" Wkiom1btyyhgcmtdaaaqumk-31m367.jpg "/>

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/7C/9D/wKioL1bTy9mRBigwAAAQaubs4OI767.jpg "title=" Vim.ntp.jpg "alt=" Wkiol1bty9mrbigwaaaqaubs4oi767.jpg "/>

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/7C/9D/wKioL1bTzKmyNK0qAABSSF4sD1M075.jpg "title=" Configure Ntp.jpg "alt=" Wkiol1btzkmynk0qaabssf4sd1m075.jpg "/>

Start time synchronization Server

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/7C/9D/wKioL1bTzS2wVAyGAAAY1VGCH_U155.jpg "title=" Start Ntp.jpg "alt=" Wkiol1btzs2wvaygaaay1vgch_u155.jpg "/>

Note the firewall, the port number of the NTPD service is UDP 123

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/7C/9D/wKioL1bT1yqD8ldCAAA9U3aa5oY458.jpg "title=" NTP firewall. jpg "alt=" wkiol1bt1yqd8ldcaaa9u3aa5oy458.jpg "/>

2. Install ntpdate on each of the two Slave(MySQL slave ) servers and synchronize time with Master

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/7C/9E/wKiom1bT1cGj1YwiAAARzmkN0AI088.jpg "title=" Ntpdate installing the. jpg "alt=" wkiom1bt1cgj1ywiaaarzmkn0ai088.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/7C/9D/wKioL1bT12_BNG6_AAAyOEJL-WU305.jpg "title=" Sync time. jpg "alt=" wkiol1bt12_bng6_aaayoejl-wu305.jpg "/>

Install MySQL database, install on Master, Slave1 and Slave2 respectively

To install the database section please refer to: "MySQL database Source Installation"

Note firewall: After installing MySQL, the firewall opens the corresponding port number

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/7C/9E/wKioL1bT5-_xNsLZAAA9u5_Qujo700.jpg "title=" mysql firewall. jpg "alt=" wkiol1bt5-_xnslzaaa9u5_qujo700.jpg "/>

Third, configure master(MySQL master ) server

1. Modify and add the following in the/ETC/MY.CNF configuration file:

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/7C/9E/wKiom1bT2HOjGVdzAAAPrm8iZoI279.jpg "title=" Vim.master.jpg "alt=" Wkiom1bt2hojgvdzaaaprm8izoi279.jpg "/>

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/7C/A1/wKiom1bT8kDi-GVfAABBTj-YGG8772.jpg "title=" Master.cnf.jpg "alt=" Wkiom1bt8kdi-gvfaabbtj-ygg8772.jpg "/>

Restart MySQL Service

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/7C/9D/wKioL1bT3CjT_woMAAAkkizp8NE465.jpg "title=" Restart Mysql.jpg "alt=" Wkiol1bt3cjt_womaaakkizp8ne465.jpg "/>

2. Log in to the MySQL program, give authorization from the server (create a user for the slave server as "slaves", the password is "123456", Grant "replication slave" copy permission)

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/7C/9E/wKioL1bT4mizIwsTAAAPD6NsvNA201.jpg "title=" Login Mysql.jpg "alt=" Wkiol1bt4miziwstaaapd6nsvna201.jpg "/>

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/7C/9D/wKioL1bT3wujdivkAAArC_B5NR4329.jpg "title=" To authorize from the server. jpg "alt=" wkiol1bt3wujdivkaaarc_b5nr4329.jpg "/>

3. View the status information for the binary, where the file column displays the log name and the position column shows the offset, which is followed by the

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/7C/9E/wKioL1bT37WA9uP7AABUrlwHdgM031.jpg "title=" View binary log file status. jpg "alt=" wkiol1bt37wa9up7aaburlwhdgm031.jpg "/>

Master (MySQL master ) server configuration complete

Iv. Configuring the Slave (MySQL slave ) server

1. Modify and add the following in the/ETC/MY.CNF configuration file of the SLAVE1 server:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/7C/9F/wKiom1bT38aTy89bAAAPrm8iZoI127.jpg "title=" Vim.master.jpg "alt=" Wkiom1bt38aty89baaaprm8izoi127.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/7C/9E/wKioL1bT4cbTaUzPAABAFLUlmiQ257.jpg "title=" Slave1.cnf.jpg "alt=" Wkiol1bt4cbtauzpaabaflulmiq257.jpg "/>

Restart MySQL Service

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/7C/9F/wKiom1bT4W3zmKmzAAAkkizp8NE971.jpg "title=" Restart Mysql.jpg "alt=" Wkiom1bt4w3zmkmzaaakkizp8ne971.jpg "/>

2. Log in to MySQL program, configure synchronization

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/7C/9F/wKiom1bT4kbTX2qGAAAPD6NsvNA689.jpg "title=" Login Mysql.jpg "alt=" Wkiom1bt4kbtx2qgaaapd6nsvna689.jpg "/>

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/7C/9E/wKioL1bT47_i6ca7AAA35vuu4L8402.jpg "title=" Mysql-slave1.jpg "alt=" Wkiol1bt47_i6ca7aaa35vuu4l8402.jpg "/>

Note In the configuration item "Master_log_file" and "Master_log_pos" Specify the binary file name and offset of the primary MySQL server that you just viewed, respectively

3. Start the sync function of the SLAVE1 server

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/7C/9E/wKioL1bT5zzS_8shAAAJ7CgiB3U087.jpg "title=" Start Slave.jpg "alt=" Wkiol1bt5zzs_8shaaaj7cgib3u087.jpg "/>

Check the slave status to ensure that the two values in the figure are Yes

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/7C/9E/wKioL1bT6ryR03dIAAC3akx9nM8987.jpg "title=" View slave status values. jpg "alt=" wkiol1bt6ryr03diaac3akx9nm8987.jpg "/>

Do the same on the SLAVE2 server, you can change the value to 3 only if the Server-id in the configuration file my.cnf cannot be the same

Five, verify the master-slave copy effect

1. Create a new database on the primary server test_db

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/7C/9F/wKioL1bT7izjPv3AAAAZnIHIJCw879.jpg "title=" Verify that the primary. jpg "alt=" wkiol1bt7izjpv3aaaaznihijcw879.jpg "/>

2. On the server, see if there is a database that will appear this test_db

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/7C/9F/wKioL1bT7rfzadfJAAA_F-C8PhA177.jpg "title=" Validate from. jpg "alt=" wkiol1bt7rfzadfjaaa_f-c8pha177.jpg "/>

You can see that the test_db Library has been added from the server, and the master-slave replication succeeds

MySQL master-slave replication and read-write separation Technology Example (a) master-slave replication