MySQL has many high-availability scenarios, a highly available solution that is simple to configure and useful.
Experimental architecture diagram:
650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/57/79/wKioL1SbeqHyGF7WAAEGk3sODA4346.jpg "title=" Qq20141225104057.png "alt=" Wkiol1sbeqhygf7waaegk3soda4346.jpg "/>
Modify the DB1 configuration file to add the following items:
[Mysqld]
Log-bin=mysql-bin
Relay-log=mysql-relay-bin
Server-id=1
Replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
Modify the DB2 configuration file to add the following items:
[Mysqld]
Log-bin=mysql-bin
Relay-log=mysql-relay-bin
server-id=2
Replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
It is recommended to use the replicate-wild-ignore-table and replicate_wild_do_table two options on master and slave libraries to solve the problem of replication filtering.
Ensure data synchronization
If you already have MySQL data on the DB1, you need to synchronize the MySQL on the DB1 and the DB2 before performing the primary master Interop.
First, the lock table is executed on DB1 and then the data is exported and applied on the DB2.
Lock Table command: FLUSH TABLES with READ lock;
Create a replication user and authorize
First create a replication user in DB1
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/57/79/wKioL1SbgXOzyPF5AAB3PKq6Os0325.jpg "title=" 1.png " alt= "Wkiol1sbgxozypf5aab3pkq6os0325.jpg"/>
Then set DB1 as your home server in DB2 's MySQL library
mysql> change Master to \
Master_host= ' 192.168.0.254 ',
Master_user= ' Repl_user ',
-master_password= ' 123456 ',
Master_log_file= ' mysql-bin.000002 ',
master_log_pos=106;
Note: The values of the two options for Master_log_file and Master_log_pos correspond to the values that are queried by the SQL statement "Show Master Status" on DB1.
Start the slave service on DB2
mysql> start slave;
Viewing the running status of slave on DB2
650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/57/79/wKioL1SbhH3QdWdOAAJBhRl9pYM736.jpg "title=" Qq20141225112557.png "alt=" Wkiol1sbhh3qdwdoaajbhrl9pym736.jpg "/>
This way the master-slave copy of DB1 to DB2 has been completed, followed by a master-slave copy from DB2 to DB1, which is exactly the same as before. First create a replication user in the MySQL library of DB2
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/57/79/wKioL1SbhRaxVtc5AAB3PKq6Os0925.jpg "title=" 1.png " alt= "Wkiol1sbhraxvtc5aab3pkq6os0925.jpg"/>
Then set DB2 as your home server in DB1 's MySQL library
mysql> change Master to \
Master_host= ' 192.168.0.251 ',
Master_user= ' Repl_user ',
-master_password= ' 123456 ',
Master_log_file= ' mysql-bin.000002 ',
master_log_pos=106;
Start the replication thread
mysql> start slave;
Viewing the running status of slave on DB1
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/57/7C/wKiom1SbhjPxUXBjAAKZv1_WjLU955.jpg "title=" 2.png " alt= "Wkiom1sbhjpxuxbjaakzv1_wjlu955.jpg"/>
As seen from the state, the Replication service is functioning properly, and the master-slave replication configuration for MySQL dual master mode is complete.
Configuring keepalived for MySQL dual master high Availability
Need to install keepalived on both servers, here take DB1 installation process example, DB2 installation process is the same here does not repeat.
Tar XF keepalived-1.2.12.tar.gz
CD keepalived-1.2.12
./configure--sysconf=/etc--with-kernel-dir=/usr/src/kernels/2.6.32-504.el6.x86_64/
Make
Make install
DB1 Server configuration file/etc/keepalived/keepalived.conf
Global_defs {
Notification_email {
[Email protected]
}
Notification_email_from keepalived.example.com
router_id Mysql_ha
}
Vrrp_script Check_mysqld {
Script "/etc/keepalived/check_slave.pl 127.0.0.1"
Interval 2
Weight 21
}
Vrrp_instance ha_1 {
Configured as backup on state backup//DB1 and DB2
Interface eth0
VIRTUAL_ROUTER_ID 80
Priority 100
Advert_int 2
Nopreempt//No preemption mode, only set on the priority machine, the low priority machine can not be set.
Authentication {
Auth_type PASS
Auth_pass 23B14455CD
}
Track_script {
Check_mysqld
}
virtual_ipaddress {
192.168.0.150
}
}
Where the/etc/keepalived/check_slave.pl script content is:
#!/usr/bin/perl-w
Use DBI;
Use Dbd::mysql;
# CONFIG VARIABLES
$SBM = 120;
$db = "wpdb";
$host = $ARGV [0];
$port = 3306;
$user = "root";
$PW = "123456";
# SQL Query
$query = "show slave status";
$DBH = Dbi->connect ("Dbi:mysql: $db: $host: $port", $user, $PW, {raiseerror = 0,printerror = 0});
if (!defined ($DBH)) {
Exit 1;
}
$sqlQuery = $dbh->prepare ($query);
$sqlQuery->execute;
$Slave _io_running = "";
$Slave _sql_running = "";
$Seconds _behind_master = "";
while (my $ref = $sqlQuery->fetchrow_hashref ()) {
$Slave _io_running = $ref->{' slave_io_running '};
$Slave _sql_running = $ref->{' slave_sql_running '};
$Seconds _behind_master = $ref->{' Seconds_behind_master '};
}
$sqlQuery->finish;
$DBH->disconnect ();
if ($Slave _io_running eq "no" | | $Slave _sql_running eq "no") {
Exit 1;
} else {
if ($Seconds _behind_master > $SBM) {
Exit 1;
} else {
Exit 0;
}
}
This is written in Perl to detect the MySQL replication status of the script, modify the file in the MySQL database port, user name, password can be used directly.
If the following error is performed on this foot of this newspaper:
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/57/7D/wKiom1Sbn_GiqrKRAAEysQybBKs065.jpg "title=" 5.png " alt= "Wkiom1sbn_giqrkraaeysqybbks065.jpg"/>
Use the following command to resolve:
# yum Install perl-dbi perl-dbd-mysql-y
DB2 keepalived.conf files and DB1 Basically, just change the priority value to 90, because the configuration is not preemption mode, you also need to remove the nopreempt option.
Then start the Keepalived service on both hosts separately
High-availability capabilities for testing services
We are connecting with the MySQL client on the 192.168.0.3 host computer vip:192.168.0.150
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/57/7D/wKiom1SbknvxkZ91AAIARvzmG7A712.jpg "title=" 3.png " alt= "Wkiom1sbknvxkz91aaiarvzmg7a712.jpg"/>
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/57/7D/wKiom1SbktvS-2H9AAFtIG96YV8466.jpg "title=" 4.png " alt= "Wkiom1sbktvs-2h9aaftig96yv8466.jpg"/>
from the above two graphs can be seen, is currently connected to the 192.168.0.254 server, also represents the cursor IP On this server.
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/57/7A/wKioL1SbnSmxn-3_AAHGuMxOX0Q529.jpg "title=" 33.png "alt=" Wkiol1sbnsmxn-3_aahgumxox0q529.jpg "/>
Test failover
Failure Simulation , we then manually stop 192.168.0.254 of the MySQL replication thread
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/57/7D/wKiom1Sbngrx37kTAAFswoBFAF8765.jpg "title=" rr.png "alt=" Wkiom1sbngrx37ktaafswobfaf8765.jpg "/>
As you can see here, when the replication thread is stopped, the connection is interrupted once when the query is executed, the query is immediately connected again, and the displayed server_id has become 2, indicating that the server has switched.
Check the IP address of the DB2 server to verify that the cursor IP is over.
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/57/7D/wKiom1SbnyHDj4vzAAItp-PDPKE779.jpg "title=" 11.png "alt=" Wkiom1sbnyhdj4vzaaitp-pdpke779.jpg "/>
Experiment Done!
This article from "Breakthrough Comfort zone" blog, reproduced please contact the author!
Keepalived+mysql Master Master Interop mode for high availability