Keepalived+mysql Dual Master High-availability configuration

The concrete structure diagram is as follows

Two nodes a master one from (the library can be hung from the top of another from the library), or a dual-master, and then use the keepalived in the event of disaster tolerant high-availability switch.

Keepalived Principle Description:

In fact, this principle can be very simple with a story description.

There used to be a gang, like all the other gangs, with the boss. This eldest brother live also very suppressed, not only do all of their own work, but also to tell all the younger brother every time I am the eldest, you are honest point. All the younger brother is also very good, as long as the eldest brother does not die, they certainly will not rob the boss's status, but once they are not receiving the eldest brother's communication, they are convinced that the eldest brother died, they began to rob the status of the boss. But they rob the boss of the status is also a rule, they have issued a notice, high-level talent can become the boss. and shoulder all the tasks, and regular notice under the younger brother, I am the eldest, all give me honest point. The boss is also tired, but we still like to be the eldest.

Here is a very detailed document, the address is http://blog.chinaunix.net/uid-27003384-id-3870083.html.

Specific implementation:

Here MySQL replication is no longer elaborated (mainly to do double-master), the main keepalived configuration explained.

My environment is:

Centos6.7

Mysql5.6.27

Installing keepalived

Yum–y install keepalived.

Configure keepalived

Keepalived configuration file is the default in the/etc/keepalived/directory

Configuration file details are as follows

keepalived Simple configuration instructions (very simple configuration)

Vrrp_script Vs_mysql_82 {

Script "/etc/keepalived/checkmysql.py-h 192.168.11.82-p 3306"--detects if a switch is required

Interval 30 detection time, 30s

}

Vrrp_instance Vi_82 {

State BACKUP status (mainly using Master,backup two types)

Nopreempt said that Master died after the life, will not take the initiative to seize the current master status

Interface eth0

VIRTUAL_ROUTER_ID 82 Group Name

Priority 100 Level

Advert_int 5

Authentication {

Auth_type PASS

Auth_pass 1111

}

Track_script {

Vs_mysql_82

}

virtual_ipaddress {

192.168.11.100

} Virtual IP Settings

checkmysql.py Script parsing

#!/usr/bin/python#Coding:utf-8#ImportSYSImportOSImportgetoptImportMySQLdbImportLoggingdbhost='localhost' #主机地址Dbport=3306 #端口号Dbuser='Monitor' #账号Dbpassword='M0n1tor' #密码defcheckmysql ():GlobalDbhostGlobalDbportGlobalDbuserGlobalDbpassword Shortargs='h:p:'opts, args=getopt.getopt (sys.argv[1:],shortargs) forOPT, valueinchopts:ifopt=='- H': Dbhost=valueelifopt=='- P': Dbport=value#print "Host:%s, Port:%d, User:%s, password:%s"% (dbhost, int (dbport), Dbuser, Dbpassword)db =Instancemysql (Dbhost, Dbport, Dbuser, Dbpassword) St=db.ishavemysql () #主要调用这个函数    returnStclassInstancemysql:conn=Nonedef __init__(Self, Host=none,port=none, User=none, passwd=None): Self.dbhost=host Self.dbport=Int (port) Self.dbuser=User Self.dbpassword=passwddefIshavemysql (self): cmd="ps-ef | egrep-i \ "mysqld\" | grep%s | EGREP-IV \ "mysqld_safe\" | grep-v grep | wc-l"%Self.dbport #查看mysql进程是不是活着, it is important to note that if you start a database with mysqld, you need to modify the above command. PutEGREP-IV \ "Mysqld_safe\" this remove

Mysqldnum=os.popen (cmd). Read () cmd="NETSTAT-TUNLP | grep \ ":%s\" | wc-l"%self.dbport #检查mysql端口号是不是正常接收 mysqlportnum=os.popen (cmd). Read ()#print Mysqldnum, Mysqlportnum if(int (mysqldnum) <=0):Print "Error" return1if(int (mysqldnum) > 0 andMysqlportnum <=0):return1return0if __name__=="__main__": St=Checkmysql () sys.exit (ST)

Issues to be aware of:

To summarize, there are three aspects to note:

1: Replication delay

For latency issues, you can optimize from two aspects:

1: Hardware aspects

Two host configuration should not be a small difference, on the one hand to prevent switching over directly to a dry die, on the other hand can reduce latency.

2: Software aspects

Semi-synchronous can be used directly (with great loss in performance) or directly on 5.7 (5.7 with multi-thread replication), and with the MARIADB branch, which has a feature of multithreaded replication. Much better than 5.6 of official MySQL, mysql5.6 official multithreaded replication is the schema level.

2: Single-sided write in the switch when the need to pay attention to the problem

Issues to be aware of

1: Prevent brain fissure (what is the brain fissure?) )

Two nodes are set to keepalived configuration files are configured as backup state, configuration nopreempt.

2: If the switch is delayed again, it may result in information not being detected or write duplication (such as self-growth ID may cause duplication.) Can be prevented by setting auto_increment_increment and auto_increment_offset )

3:keepalived Determination of the detection criteria

The simplest rule is to detect if a database instance is present, with PS, or the database port.

Appropriate additions need to be made in a specific business environment. You also need to add a delayed probe, which is either a direct switch or a different process.

Keepalived+mysql Dual Master High-availability configuration