[SHELL] mysql Master/Slave + keepalived online Recovery Solution

A recent project requires a high-availability master-slave synchronization of mysql + keepalived, which is of course easy to configure. However, it is easy to think about how to restore data as quickly as possible after the master database is suspended and taken over from the slave database, and let the master database take over again.

A recent project requires a high-availability master-slave synchronization of mysql + keepalived, which is of course easy to configure. However, it is easy to think about how to restore data as quickly as possible after the master database is suspended and taken over from the slave database, and let the master database take over again.

A recent project requires a high-availability master-slave synchronization of mysql + keepalived, which is of course easy to configure. However, it is easy to think about how to restore data as quickly as possible after the master database is suspended and taken over from the slave database, and let the master database take over again, so I sorted out the following ideas and compiled the following script for your reference.

Master/Slave mysql and keepalived high-availability configuration of this blog Portal:

1. Back up the current database of the slave Database

Note: All the following configurations must be performed when the database is not running. We recommend that you set an iptables rule to prohibit non-Administrator IP addresses from accessing the master/slave server IP addresses and VIP addresses, this not only ensures that the administrator can remotely log on to the configuration through SSH, but also restore the Master/Slave environment. After the configuration is complete, you can remove the prohibition.

# Vi/etc/rc. d/mysql_bak.sh

-------------------------

#! /Bin/bash

# Environment Variables

PATH =/bin:/usr/bin:/sbin:/usr/sbin; export PATH

Export LANG = C

# Daily Backup Directory

Basedir =/backup/daily/

# Database account

User = root

Passswd = 123456

# Mysql Backup Directory

Mysql_db_bak = $ basedir/$ (date + % F _ % H % M)

[! -D "$ mysql_db_bak"] & mkdir-p $ mysql_db_bak

# Names of all databases in mysql

Db_name = ("information_schema" "mysql" "test_db1" "test_db2" "test_db3" "test_db4 ")

#1. Back up the MySQL database

For (I = 0; I! =$ {# Db_name [@]}; ++ I ))

{

Mysqldump-u $ user-p $ passwd -- opt -- skip-lock-tables -- flush-logs -- database $ {db_name [I]}> $ mysql_db_bak/$ {db_name [I] }. SQL

}

-------------------------

2. Copy data from the (192.168.7.9) database to the master database (192.168.7.12)

# Vi/etc/rc. d/scp_mysql.sh

----------------------

#/Bin/bash

# Environment Variables

PATH =/bin:/usr/bin:/sbin:/usr/sbin; export PATH

Scp/backup/daily/2013-09-10_1258/* 192.168.7.9:/tmp

----------------------

3. Restore the master database (192.168.7.9)

# Vi/etc/rc. d/recover_mysql.sh

------------------------------------

#/Bin/bash

# Environment Variables

PATH =/bin:/usr/bin:/sbin:/usr/sbin; export PATH

Export LANG = C

# Database account

User = root

Passswd = 123456

# Names of all databases in mysql

Db_name = ("information_schema" "mysql" "test_db1" "test_db2" "test_db3" "test_db4 ")

# Restoring a MySQL database

Cd/tmp

For (I = 0; I! =$ {# Db_name [@]}; ++ I ))

{

Mysql-u $ user-p $ passwd-e "drop database $ {db_name [I]};"

Mysql-u $ user-p $ passwd-e "create database $ {db_name [I]};"

Mysql-u $ user-p $ passwd $ {db_name [I]} <$ {db_name [I]}. SQL

}

# Enable slave Database

Mysql-u $ user-p $ passwd-e "stop slave ;"

Mysql-u $ user-p $ passwd-e "reset slave ;"

# Set parameters for connecting the slave database to the master database. You can view the parameters using related commands.

Mysql-u $ user-p $ passwd-e "change master to master_host = '2017. 168.7.9 ', master_user = 'slave', master_password = '2017 # ', master_log_file = 'bin. 000029 ', master_log_pos = 106 ";

Mysql-u $ user-p $ passwd-e "start slave ;"

# Check whether the Master/Slave connection is successful

Mysql-u $ user-p $ passwd-e "show slave status \ G ;"

------------------------------------

4. view master-slave synchronization Information

1. View binlog logs and offsets of the master database

# Mysql-u root-p123456-e "show master status"

2. Check whether the slave database is connected to the master database

# Mysql-u root-p123456-e "show slave status \ G ;"

Note: in practice, it is found that once the VIP is switched over, the master/slave server may obtain the virtual IP address at the same time, resulting in a Master/Slave database conflict. No matter whether keepalived is restarted or not, the VIP cannot be killed, currently, my temporary solution is to restart both servers. If you have a better solution, please leave a message and let me know. Thank you very much.

Follow-up:

Read a large amount of information on the Internet to preliminarily determine the conflict caused by VIP switching because the pkill keepalived process was used to kill the keepaliaved process when the switchover was triggered. As a result, the system only killed the keepalived process, no time to switch the VIP,

The current solution is to use the regular keepalived STARTUP script/etc/init. d/keepalived stop to close

No exceptions were found in the preliminary test...

If the VIP address of the master node is not automatically closed when the master node switches to the VIP address, and the slave node is enabled normally, the VIP address is obtained, causing mutual conflict, you can manually enter the following command to delete the VIP address of one party:

# Ip addr del "virtual ip" dev eth0

However, this method is only a temporary solution. After you manually delete the VIP, restarting the keepalived service will not enable the vip normally. You need to restart the server to recover the service.

This article is from the "all the way to the North" blog. Please keep this source