Mysql dual-machine Hot Standby + heartbeat cluster + automatic failover

Environment Description: This environment consists of two mysql Databases and heartbeat, one ip Address
For 192.168.10.197 and 192.168.10.198.
192.168.10.200
Note: heartbeat itself cannot automatically switch between services.
The moniter script mentioned in this article is used to implement
Automatic alarms can be triggered automatically when the mysql service is unavailable.
The installation and configuration process is divided into the following steps:
Part 1: install and configure mysql
1 Installation
1.1. Add users and groups required for mysql operation
Groupadd mysql
Useradd-g mysql
1.2. Unzip and install
Tar mysql-5.1.26-rc-linux-x86_64-glibc23.tar.gz
Music mysql-5.1.26-rc-linux-x86_64-glibc23/usr/local/mysql/
Chown-R mysql: mysql/usr/local/mysql
1.3. Copy the master configuration file and startup script
Cd/usr/local/mysql
Cp support-files/my-medium.cnf/etc/my. cf
Cp support-files/mysql. server/etc/init. d/mysqld
Chmod + x/etc/init. d/mysqld
1.4. initialize the database
Cd/usr/local/mysql
Scripts/mysql_install_db -- user = mysql
1.5. Change the owner and group of the data directory.
Author: Qu Baoquan, reposted to indicate the source!
2/14
Chown-R mysql: mysql./data
1.6. Register mysql as a system service
Chkconfig -- add mysqld
Chkconfig -- levels 2345 mysqld on
1.7. Output Environment Variables
# Vi/etc/profile (content as follows)
PATH = $ PATH:/usr/local/mysql/bin
# Source/etc/profile
1.8. Set the Database Password
Service mysqld start
Mysqladmin password 123456 (set your own password. Here is an example)
1.9. Enable root Remote Access
Mysql-p123456
Mysql & gt; grant all on *. * to root @ '%' identified by '123 ';
Query OK, 0 rows affected (0.00 sec)
Mysql & gt; flush privileges;
Mysql & gt; quit
2 parameter settings
2.1. Create related directories and Set permissions
Mkdir/usr/local/mysql/binlog/
Chown-R mysql: mysql/usr/local/mysql/binlog/
Touch/var/log/mysql. log
Chown-R mysql: mysql/var/log/mysql. log
2.2. Add the account for Synchronous replication (the master database and slave database have the same settings)
[Root @ master ~] # Mysql-p
Enter password: (Enter the root password)
Mysql & gt; grant all on *. * to qiangao identified by '000000'; (the account and password must be
As specified in the configuration file)
Mysql & gt; flush privileges;
2.3. Modify the master configuration file
Author: Qu Baoquan, reposted to indicate the source!
3/14
Note: The ip addresses must point to each other's ip addresses based on actual conditions. The user name and password must be password-secured with the preceding user name.
Consistent Codes
Master database settings
Vi/etc/my. cnf (overwrite the original content with the following content)
######################################## ########
##########
[Client]
Port = 3306
Socket =/tmp/mysql. sock
[Mysqld]
################### Auto_increment
###########################
Auto_increment_offset = 1
Auto_increment_increment = 2
############# Other options ##############
Default-character-set = utf8
Default-storage-engine = InnoDB
Default-table-type = INNODB
Max_connections = 800
Port = 3306
Socket =/tmp/mysql. sock
Skip-locking
######### MyISAM options #################
Myisam_max_sort_file_size = 10G
Myisam_max_extra_sort_file_size = 10G
Myisam_sort_buffer_size = 10 M
Myisam_repair_threads = 1
################ Select cache options ##################
Read_buffer_size = 2 M
Read_rnd_buffer_size = 16 M
Bulk_insert_buffer_size = 5 M
Author: Qu Baoquan, reposted to indicate the source!
4/14
Max_allowed_packet = 1 M
Table_cache = 2048
Query_cache_size = 32 M
Query_cache_limit = 2 M
Sort_buffer_size = 8 M
Join_buffer_size = 8 M
Thread_concurrency = 8
############### Index cache options ##################
Key_buffer_size = 32 M
Key_buffer_size = 32 M
################# Master ###################### ###
Server-id = 1
Log-bin =/usr/local/mysql/binlog/master-bin
Binlog_format = mixed
Relay-log =/usr/local/mysql/binlog/mysqld-relay-bin
##################### Slave
##########################
Relay-log =/usr/local/mysql/binlog/slave-relay-bin
Master-host = 192.168.10.198
Master-user = qiangao
Masters-password = 123456
Master-connect-retry = 10
############# Log ##################
Log-error =/var/log/mysql. log
######### INNODB #########
Innodb_file_per_table
# ++ Log ++ #
Innodb_log_buffer_size = 10 M
Innodb_mirrored_log_groups = 1
Innodb_log_files_in_group = 3
Author: Qu Baoquan, reposted to indicate the source!
5/14
Innodb_log_file_size = 50 M
Innodb_flush_log_at_trx_commit = 0
# Innodb_log_archive = 0
# ++ System buffer ++ #
Innodb_buffer_pool_size = 1024 M
Innodb_additional_mem_pool_size = 40 M
# ++ Other ++ #
Innodb_file_io_threads = 4
Innodb_lock_wait_timeout = 5
Innodb_force_recovery = 0
Innodb_fast_shutdown = 1
Innodb_thread_concurrency = 8
Innodb_lock_wait_timeout = 50
Transaction-isolation = READ-COMMITTED
[Mysqldump]
Quick
Max_allowed_packet = 16 M
[Mysql]
No-auto-rehash
# Remove the next comment character if you are not familiar with SQL
# Safe-updates
[Isamchk]
Key_buffer = 128 M
Sort_buffer_size = 128 M
Read_buffer = 2 M
Write_buffer = 2 M
[Myisamchk]
Key_buffer = 128 M
Sort_buffer_size = 128 M
Read_buffer = 2 M
Author: Qu Baoquan, reposted to indicate the source!
6/14
Write_buffer = 2 M
[Mysqlhotcopy]
Interactive-timeout
Auxiliary database settings
Vi/etc/my. cnf (overwrite the original content with the following content ,)
######################################## ########
##########
[Client]
Port = 3306
Socket =/tmp/mysql. sock
[Mysqld]
################### Auto_increment
###########################
Auto_increment_offset = 1
Auto_increment_increment = 2
############# Other options ##############
Default-character-set = utf8
Default-storage-engine = InnoDB
Default-table-type = INNODB
Max_connections = 800
Port = 3306
Socket =/tmp/mysql. sock
Skip-locking
######### MyISAM options #################
Myisam_max_sort_file_size = 10G
Myisam_max_extra_sort_file_size = 10G
Myisam_sort_buffer_size = 10 M
Myisam_repair_threads = 1
################ Select cache options ##################
Read_buffer_size = 2 M
Read_rnd_buffer_size = 16 M
Author: Qu Baoquan, reposted to indicate the source!
7/14
Bulk_insert_buffer_size = 5 M
Max_allowed_packet = 1 M
Table_cache = 2048
Query_cache_size = 32 M
Query_cache_limit = 2 M
Sort_buffer_size = 8 M
Join_buffer_size = 8 M
Thread_concurrency = 8
############### Index cache options ##################
Key_buffer_size = 32 M
Key_buffer_size = 32 M
################# Master ###################### ###
Server-id = 2
Log-bin =/usr/local/mysql/binlog/master-bin
Binlog_format = mixed
Relay-log =/usr/local/mysql/binlog/mysqld-relay-bin
##################### Slave
##########################
Relay-log =/usr/local/mysql/binlog/slave-relay-bin
Master-host = 192.168.10.197
Master-user = qiangao
Masters-password = 123456
Master-connect-retry = 10
############# Log ##################
Log-error =/var/log/mysql/error. log
######### INNODB #########
Innodb_file_per_table
# ++ Log ++ #
Innodb_log_buffer_size = 10 M
Innodb_mirrored_log_groups = 1
Author: Qu Baoquan, reposted to indicate the source!
8/14
Innodb_log_files_in_group = 3
Innodb_log_file_size = 50 M
Innodb_flush_log_at_trx_commit = 0
# Innodb_log_archive = 0
# ++ System buffer ++ #
Innodb_buffer_pool_size = 1024 M
Innodb_additional_mem_pool_size = 40 M
# ++ Other ++ #
Innodb_file_io_threads = 4
Innodb_lock_wait_timeout = 5
Innodb_force_recovery = 0
Innodb_fast_shutdown = 1
Innodb_thread_concurrency = 8
Innodb_lock_wait_timeout = 50
Transaction-isolation = READ-COMMITTED
[Mysqldump]
Quick
Max_allowed_packet = 16 M
[Mysql]
No-auto-rehash
# Remove the next comment character if you are not familiar with SQL
# Safe-updates
[Isamchk]
Key_buffer = 128 M
Sort_buffer_size = 128 M
Read_buffer = 2 M
Write_buffer = 2 M
[Myisamchk]
Key_buffer = 128 M
Sort_buffer_size = 128 M
Author: Qu Baoquan, reposted to indicate the source!
9/14
Read_buffer = 2 M
Write_buffer = 2 M
[Mysqlhotcopy]
Interactive-timeout
2.4. Restart the service
Service mysqld restart
3. Firewall settings
Note that the firewall settings of the master and slave databases are the same.
Iptables-I INPUT-p tcp -- dport 3306-j ACCEPT
Service iptables save
Part 2: heartbeat installation and configuration
Hardware environment of the two hosts (not completely consistent ):
Eth0: external IP address
Eth1: internal IP address (for HA)
The eht1 of the two hosts is directly connected by dual-host couplets.
[2] network environment settings before installation:
========================================================== ==========
==========
Node1: Host Name: master.qiangao.com
Eth0: 192.168.10.197 // external IP address
Eth1: 172.16.1.3 // HA heartbeat address
---------------------------
Node2: Host Name: slave.qiangao.com
Eth0: 192.168.10.198 // external IP address
Eth1: 172.16.1.4 // HA heartbeat address
Check the following files:
/Etc/hosts
/Etc/host. conf
/Etc/resolv. conf
/Etc/sysconfig/network
/Etc/sysconfig/network-scripts/ifcfg-eth0
/Etc/sysconfig/network-scripts/ifcfg-eth1
/Etc/nsswitch. conf
# Vi/etc/hosts
The hosts content of node1 is as follows:
127.0.0.1 master.qiangao.com master localhost. localdomain localhost
192.168.10.197 master.qiangao.com
Author: Qu Baoquan, reposted to indicate the source!
10/14
192.168.10.198 slave.qiangao.com
: 1 localhost6.localdomain6 localhost6
---------------------------
The hosts content of node2 is as follows:
127.0.0.1 slave.qiangao.com slave localhost. localdomain localhost
192.168.10.197 master.qiangao.com
192.168.10.198 slave.qiangao.com
: 1 localhost6.localdomain6 localhost6
# Cat/etc/host. conf
Order hosts, bind
# Cat/etc/resolv. conf
Nameserver 202.96.134.133 // DNS address
# Cat/etc/sysconfig/network
NETWORKING = yes
HOSTNAME = master.qiangao.com // host name
GATEWAY = "192.168.10.1" // GATEWAY
GATEWAY = "eth0" // The network card used by the GATEWAY
ONBOOT = YES // load at startup
FORWARD_IPV4 = "yes" // only IPV4 addresses are allowed
---------------------------
# Cat/etc/sysconfig/network-scripts/ifcfg-eth0
DEVICE = eth0
ONBOOT = yes
BOOTPROTO = static
IPADDR = 192.168.10.197
NETMASK = 255.255.255.0
GATEWAY = 192.168.10.1
TYPE = Ethernet
IPV6INIT = no
---------------------------
# Cat/etc/sysconfig/network-scripts/ifcfg-eth1
DEVICE = eth1
ONBOOT = yes
BOOTPROTO = none
IPADDR = 172.16.1.3
NETMASK = 255.255.0.0
TYPE = Ethernet
[Node1] and [node2] in the preceding configuration,
/Etc/hosts
/Etc/sysconfig/network
/Etc/sysconfig/network-scripts/ifcfg-eth0
/Etc/sysconfig/network-scripts/ifcfg-eth1
Except for modification.
After the configuration is complete, try to ping the Host Name of the other host. You can ping the Host Name of the other host:
Author: Qu Baoquan, reposted to indicate the source!
11/14
[Root @ master ~] # Ping slave.qiangao.com
PING slave.qiangao.com (192.168.10.198) 56 (84) bytes of data.
64 bytes from slave.qiangao.com (192.168.10.198): icmp_seq = 1 ttl = 64
Time = 0.136 MS
[3] install the HA and HA dependency packages
========================================================== ==========
==============
Yum install heartbeat
Yum install ipvsadm
Yum install libnet
[4] configuration files for configuring HA
Copy the configuration file to the/etc directory.
Cd/usr/share/doc/heartbeat-2.1.3
Cp ha. cf haresources/etc/ha. d/
Cp authkeys/etc/ha. d/
========================================================== ==========
==============
Configure the heartbeat encryption method: authkeys
If you use dual-host couplet wires (twisted pair wires), you can configure the following:
# Vi/etc/hc. d/authkeys
Auth 1
1 crc
Save the disk and exit, and then
# Chmod 600 authkeys
========================================================== ==========
==============
Configure heartbeat monitoring: haresources
Create a detection script (this module cannot be started if there is no monitoring script hearbeat)
Vi/etc/init. d/test (content is as follows)
#! /Bin/bash
Echo "" $ & gt;/dev/null
Chmod 777/etc/init. d/test
========================================================== ==========
==============
# Vi/etc/ha. d/haresources (configuration resource file)
All hosts should be identical.
Master.qiangao.com 192.168.10.200 test
Specify master.qiangao.com to call a pre-written test script, and the system attaches a virtual IP Address
192.168.10.200 to eth0: 0
If master.qiangao.com is down, slave.qiangao.com can allocate a new IP address.
192.168.10.200
========================================================== ==========
==============
Configure the heartbeat configuration file: ha. cf
Author: Qu Baoquan, reposted to indicate the source!
12/14
# Vi/etc/ha. d/ha. cf
Logfile/var/log/ha_log/ha-log.log # the location where the ha log file is recorded. If no
This directory, You need to manually add
Bcast eth1 # Use eht1 for heartbeat monitoring
Keepalive 2 # Set the heartbeat (Monitoring) Time to 2 seconds
Warntime 4 #### warning time
Deadtime 6 ######## determine the time when a service is killed
Initdead 30
Hopfudge 1
Udpport 694 # Use udp port 694 for heartbeat monitoring
Auto_failback on
Node master.qiangao.com # node 1 must be consistent with the result obtained by the uname-n command.
Node slave.qiangao.com # node 2
Ping 172.16.1.04 # ping each other to check whether the heartbeat is normal.
Respawn hacluster/usr/lib64/heartbeat/ipfail (varies by operating system)
Apiauth ipfail gid = haclient uid = hacluster
Debugfile/var/logs/ha-debug.log
---------------------------
[5] Starting and disabling the HA Service
[Root @ master ha. d] # chkconfig -- add heartbeat
[Root @ master ha. d] # chkconfig -- levels 2345 heartbeat on
Start HA: service heartbeat start
Disable HA; service heartbeat stop
[6] firewall settings
========================================================== ==========
====
Heartbeat uses udp port 694 by default for heartbeat monitoring. If the system uses iptables
Firewall, remember to open this port.
# Vi/etc/sysconfig/iptables
Add the following content (pointing to each other's ip addresses)
-A RH-Firewall-1-INPUT-p udp-m udp -- dport 694-d 172.16.1.4-j ACCEPT
This means that udp port 694 is open to the peer's heartbeat NIC address 172.16.1.4.
# Service iptables restart
Reload iptables.
Part 3: Monitoring script
Cat/usr/local/mysql/bin/moniter. sh
#! /Bin/bash
Mysql_path =/usr/local/mysql/bin/
User = "root"
Password = "123456"
Email = "qubq@qian-gao.com"
Logfile =/var/log/moniter. log
Author: Qu Baoquan, reposted to indicate the source!
13/14
Date = '(date + % y-% m-% d -- % H: % M: % S )'
Sleeptime = 30
Ip = $ (/sbin/ifconfig | grep "inet addr" | grep-v "127.0.0.1" | awk '{print $2;}' |
Awk-F': ''{print $2;} '| head-1)
Slave_IO_Running = $ (mysql-u $ user-p $ password-e 'show slave status \ G' |
Grep "Slave_IO_Running" | awk '{print $2 }')
Slave_ SQL _Running = $ (mysql-u $ user-p $ password-e 'show slave status \ G' |
Grep "Slave_ SQL _Running" | awk '{print $2 }')
Echo "plese fix the server of $ ip error now! "& Gt; $ mysql_path/letter
Letter = $ mysql_path/letter
Mysql-p $ password-e "use test ;"
If [[$? ! = 0]
Then
Mail-s "{$ ip} _ database connect lost the srcprits fix it now"
$ Email <$ letter
Killall-9 heartbeat
Killall-9 mysqld
/Etc/init. d/mysqld start
Sleep $ sleeptime
Mysql-p $ password-e "use test ;"
If [$? = 0]
Then
Echo
"==============>$ Date <======================================= "> & gt; $ logfile
Mail-s "{$ ip} _ database up now" $ email
Sleep $ sleeptime
Service heartbeat start
Sleep $ sleeptime
Netstat-an | grep udp | grep 694
If [$? = 0]
Then
Echo "complete! "& Gt; $ logfile
Else
Mail-s "{$ ip} heartbeat can't to up please fix it! "
$ Email <$ letter
Fi
Else
Mail-s "{$ ip} _ database cant't to up plese fix it" $ email
Fi
Else
If ["$ Slave_IO_Running" = "Yes"-a "$ Slave_ SQL _Running" =
"Yes"]
Then
Echo "Slave is running! ">/Dev/null
Author: Qu Baoquan, reposted to indicate the source!
14/14
Else
Echo
"===================& Gt; $ date <================================="> & gt; $ logfile
Echo "Slave is not running! "& Gt; $ logfile
/Bin/mail-s "{$ ip} _ replicate error please fix it"
$ Email & lt; $ letter
Fi
Fi
Then you can set up an automated task to detect the task every 2 minutes. Now you can implement dual-host hot backup!