Shulou(Shulou.com)06/01 Report--

The data from the master-slave database server will eventually be consistent (because mysql replication is asynchronous and there will always be delays), which seems to solve the single point problem, but the solution is not perfect. Because once the master server goes down, the read-write connection must be manually switched to the original slave server, and this process takes time, ranging from minutes to hours, during which the service will be paralyzed.

How to automatically switch database connections after mastery downtime?

The simplest thing is to use a virtual ip:

Here is another way to keepalived:

(note that only one master provides services at a time, and the other is read-only.)

Configuration:

(specify an id growth step of 2, and the id growth starts from 1, so the id growth on the main database is 1, 3, 5, 7, 9. Note that this parameter modification is global for the entire mysql server, not for a particular table or library, that is, once modified, the values of all library tables under the server are increased in this way)

(specify an id growth step of 2, and the id growth starts from 2, so the id growth on the main database is 2, 4, 6, 8, 10. Note that this parameter modification is global for the entire mysql server, not for a particular table or library, that is, once modified, the values of all library tables under the server are increased in this way)

(after the above settings, there will be no conflict in the growth of the primary key id between the primary key and the standby key.)

(as mentioned earlier, the primary key of innodb had better use the column of self-increasing id, but in the primary primary replication, in order to avoid the primary key conflict caused by writing two groups at the same time, we need to modify the configuration of the self-increasing primary key so that the two self-increasing primary keys grow according to different step size values, of course, just in case, when we use primary primary replication. The safest way is to ensure that only one master provides the service at any time, while the other master provides only read-only)

Next, install the keepalived component (which can virtualize two devices into one device and provide a virtual ip, that is, vip):

(note: install keepalived on both devices that are master and slave to each other)

The best case is to change the master-slave replication to master-master replication, and complete the master-master replication configuration first:

First modify the configuration vi / etc/my.cnf on the primary server:

(since these two are dynamic parameters, there is no need to restart mysql)

And set it on the mysql client:

Exit the current mysql client, and then go to view:

Modify the configuration vi / etc/my.cnf on the slave server:

(since these two are dynamic parameters, there is no need to restart mysql)

And set it on the mysql client:

Check the account of the slave server:

(the dba_repl account also exists on the slave server)

Since the slave server is in the read_only state and does not write, there is no need to reinitialize the data, because its data is exactly the same as the master, because it does not have any write operations:

View the points on the slave server:

Change master on the master (master-slave, slave-master):

(set the master server of master to the original slave server)

Start the replication link (the original master server and now the slave server):

View:

Install keepalived:

Install on both servers: yum install keepalived-y

View two config files:

(be sure to ensure that check_mysql.sh scripts have execution permissions, both.)

Start the keepalived of the two servers:

View the virtual ip:

(it may take a few seconds to produce)

Turn off the mysql of the primary server:

At the same time, the main keepalived will automatically kill, the virtual ip (vip) has been released, and its virtual ip will disappear:

From the server:

(at this point, the seamless handover under downtime is complete)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.