MySQL master-slave replication and high availability

MySQL itself provides a highly available solution, and the data synchronization method uses MySQL replication technology. MySQL replication is to pull the binary log file from the server to the master server, and then parse the log file into the corresponding SQL to re-perform the operation of the master server from the server, in this way to ensure the consistency of the data.

In order to achieve higher availability, in the actual application environment, it is generally used MySQL Replication technology with highly available cluster software keepalived to achieve automatic Failover

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/7F/88/wKiom1chsMWSxeeoAABbmbCWni4847.png "title=" 1.png " alt= "Wkiom1chsmwsxeeoaabbmbcwni4847.png"/>

The experimental environment is as follows

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/7F/85/wKioL1chsfzTIyTlAAAh1DDZ7fk104.png "title=" 2.png " alt= "Wkiol1chsfztiytlaaah1ddz7fk104.png"/>

The database compiles slightly, and the following begins to enter the configuration process.

Host01 the "[Mysqld]" segment in the/etc/my.cnf file,Add the following:

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/7F/85/wKioL1chskDzYO1_AAAeHjZ47C8474.png "title=" 3.png " alt= "Wkiol1chskdzyo1_aaaehjz47c8474.png"/>

Host02 the "[Mysqld]" segment in the/etc/my.cnf file,Add the following:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/7F/86/wKioL1chsojyY0tQAAAdY05dG5M839.png "title=" 4.png " alt= "Wkiol1chsojyy0tqaaady05dg5m839.png"/>

first , create the replication user in the MySQL library of host01 , the operation process.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/7F/88/wKiom1chsd3SGMAvAABPKVwhGp4383.png "title=" 5.png " alt= "Wkiom1chsd3sgmavaabpkvwhgp4383.png"/>

and then in host02 of the MySQL Library will host01 set as your own master server and operate the process.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/7F/86/wKioL1chswmwAUNpAACaSGvBgTc848.png "title=" 6.png " alt= "Wkiol1chswmwaunpaacasgvbgtc848.png"/>

then host01 the Primary database to create the database KKK, in the host02 standby database to observe whether synchronization over

Host01 above Operation

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/7F/88/wKiom1chsnCC1Q4KAAAqAW4Bhec309.png "title=" 7.png " alt= "Wkiom1chsncc1q4kaaaqaw4bhec309.png"/>

Host02 above observation

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/7F/86/wKioL1chs4SR2Po4AAAnjIvg0_8173.png "title=" 8.png " alt= "Wkiol1chs4sr2po4aaanjivg0_8173.png"/>

you can see the database KKK synchronized, indicating that the master-slave synchronization is successful

Two databases install highly available software keepalived, the installation process is skipped here.

The Main Library host01 keepalived is configured as follows

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/7F/88/wKiom1chsxbzR3ZgAABlzqOWZkA421.png "title=" 1.png " alt= "Wkiom1chsxbzr3zgaablzqowzka421.png"/>

from the library host02 Configure the following

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/7F/86/wKioL1chtBOzP6-yAABdykO83Ig175.png "title=" 2.png " alt= "Wkiol1chtbozp6-yaabdyko83ig175.png"/>

Check the VIP address situation, note that the VIP address can only appear at one end, both ends have a description of the brain crack

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/7F/86/wKioL1chtFTzkUjDAABY5X2cvuQ303.png "title=" 3.png " alt= "Wkiol1chtftzkujdaaby5x2cvuq303.png"/>

turn on the third machine to connect as a client VIP Address Test

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/7F/86/wKioL1chtJ2TEBxdAAB6Ewa8AUU744.png "title=" 4.png " alt= "Wkiol1chtj2tebxdaab6ewa8auu744.png"/>

Do the client's authorization operation on the master and slave server

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/7F/88/wKiom1chs_yyosr-AAAtIVoDOLM452.png "title=" 1.png " alt= "Wkiom1chs_yyosr-aaativodolm452.png"/>

Client Connection VIP Landing Test

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/7F/86/wKioL1chtQbDlwb0AAB7f186N-4253.png "title=" 2.png " alt= "Wkiol1chtqbdlwb0aab7f186n-4253.png"/>

you can see that the main library is connected at this time, which simulates the failure and stops the main library MySQL from observing the phenomenon

above the main library, you can see The VIP address was removed from the

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/7F/86/wKioL1chtTOCOD3hAABtIbaqIns350.png "title=" 3.png " alt= "Wkiol1chttocod3haabtibaqins350.png"/>

from the top of the library, VIP The address has drifted.

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/7F/88/wKiom1chtJSyJrNbAACT_W9_InI168.png "title=" 5.png " alt= "Wkiom1chtjsyjrnbaact_w9_ini168.png"/>

On the client side, you can see that you are actually connected to the library

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/7F/88/wKiom1chtNTSzWg2AABoDO7ITl0656.png "title=" 5.png " alt= "Wkiom1chtntszwg2aabodo7itl0656.png"/>

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/7F/86/wKioL1chthXR77QOAAIHgxxvmMU654.png "title=" 1.png " alt= "Wkiol1chthxr77qoaaihgxxvmmu654.png"/>

This article is from "Martin" blog, please make sure to keep this source http://huaxin.blog.51cto.com/903026/1768614

MySQL master-slave replication and high availability