Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Server \ [SHELL] MySQL primary recovery solution from + Keepalived online     - Linux system security configuration (Linux)

- Boot-Repair Tool - repair of frequent start-up problems (Linux)

- Oracle PL / SQL selective basis (IF CASE), (LOOP WHILE FOR) (Database)

- Let OpenCV face detection score output codes (Programming)

- Debian GNU / Linux service list acquisition, shutting down services or run (Linux)

- Terminal Linux command prints - echo (Linux)

- Linux Getting Started tutorial: hard disk partition and to deal with traps (Linux)

- RHEL7.0 log system (Linux)

- Linux5 install MySQL 5.6 (RPM mode) (Database)

- Using LLVM Clang and Blocks under Linux (Programming)

- How to install Virtualbox 4.3.20 in Ubuntu 14.04 (Linux)

- jobs command example (Linux)

- Java memory area and memory overflow exception (Programming)

- MySQL 5.7.10 source code for the latest version of the installation process in detail (Database)

- Java memory mechanism Description (Programming)

- Analysis of Java in the deep copy and shallow copy (Programming)

- How to make GRub instead of the default Ubuntu software center (Linux)

- Oracle to create an external table (Database)

- MySQL binary packages install for RedHat Linux Enterprise 6.4 (Database)

- Linux System Getting Started Learning: Join cron job in Linux (Linux)

 
         
  [SHELL] MySQL primary recovery solution from + Keepalived online
     
  Add Date : 2018-11-21      
         
         
         
  Recently a project needs to do a highly available MySQL + Keepalived master-slave synchronization with it hundreds of times before, of course, but think of how to hang in the main library, with the fastest speed to restore the data from the database to take over and get back to take over the main library, so compiled under the idea to write the script as follows, for your reference
A. Backup the current database from the library
NOTE: All of the following configuration requires a database is not running in the state, it is recommended to do a iptables rule prohibiting non-administrator IP access from the main server IP, as well as VIP, so can guarantee the administrator can configure the SSH remote login, but also to restore the primary from the environment, to be configured to lift the ban.
# 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
passwd = 123456

# Mysql backup directory
mysql_db_bak = $ basedir / $ (date +% F_% H% M)
[! -d "$ Mysql_db_bak"] && mkdir -p $ mysql_db_bak

All library under the name # mysql
db_name = ( "information_schema" "mysql" "test_db1" "test_db2" "test_db3" "test_db4")

# 1 MySQL database backup
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
}
-------------------------

B. Copied from (192.168.7.9) database backup to the main library (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
----------------------


III. Recovery main library (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
passwd = 123456


All library under the name # # mysql
db_name = ( "information_schema" "mysql" "test_db1" "test_db2" "test_db3" "test_db4")


# Restore 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
}


# Open from Library
mysql -u $ user -p $ passwd -e "stop slave;"
mysql -u $ user -p $ passwd -e "reset slave;"
# Set the connection parameters of the main library from the library, you can use the relevant command
mysql -u $ user -p $ passwd -e "change master to master_host = '192.168.7.9', master_user = 'slave', master_password = '123456 #', master_log_file = 'bin.000029', master_log_pos = 106";
mysql -u $ user -p $ passwd -e "start slave;"
# Check whether the connection is successful master-slave
mysql -u $ user -p $ passwd -e "show slave status G;"
------------------------------------

C. Check master-slave synchronization information
1. Check the main library binlog log and offsets
# Mysql -u root -p123456 -e "show master status"
2. View from the library is connected to the main library
# Mysql -u root -p123456 -e "show slave status G;"

Note: The actual operation found that once the VIP have a chance to cause the main switch at the same time will be obtained from the server to the virtual IP, leading to the master database from the conflict, whether or not to restart keepalived can not get rid of the VIP, currently my temporary solution the two servers are restarted, if we have a better way, please leave a message tell me, be grateful.

Follow-up:
Read a lot of information on the Internet, the initial judge in post-conflict VIP switching caused because pkill keepalived switching operation is triggered when used to kill keepaliaved process, causing the system just kill keepalived own process, not enough time to switch the VIP,
The current solution is to use a regular keepalived startup script /etc/init.d/keepalived stop to close
Initial tests found no abnormalities. . .

If the Lord did not automatically turn off when you switch from the VIP, VIP Lord appeared, from the normal open, causing both to get VIP, resulting in conflicting situations, you can manually enter the following command to delete one of the VIP address
# Ip addr del "virtual ip" dev eth0
However, this method is only a temporary solution, when manually delete the VIP, restart keepalived services will not normally open vip, need to restart the server in order to recover.
     
         
         
         
  More:      
 
- Fedora 20 Installation and Configuration (Linux)
- Java static internal class (Programming)
- Hands to teach you to solve Ubuntu error message (Linux)
- Oracle large table to clean truncate .. reuse storage (Database)
- Node.js installed on Ubuntu Upstream version (Linux)
- Linux three ways to set environment variables (Linux)
- Ubuntu 14.04 installed Nvidia CUDA 7.5 and build Python Theano deep learning development environment (Linux)
- HttpClient4 usage upgrade from HttpClient3 (Programming)
- Linux redirection and piping (Linux)
- Android first line of code study notes (Programming)
- How to use the Linux terminal Git commands (Linux)
- On the PC goes heavy security watch your startup items (Linux)
- Docker + Nginx + Tomcat7 simple load balancing configuration (Server)
- Linux firewall settings -DNS server articles (Server)
- JDK installation notes under CentOS (Linux)
- Ceph cluster disk is no workaround for the remaining space (Server)
- Git 2.5 increase in the working tree, triangle improved workflow performance (Linux)
- Oracle database import and export (Database)
- When RHEL7 use fdisk partition, all partitions can not be used (Linux)
- Linux system font color change (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.