MySQL dual-machine hot standby +heartbeat cluster + automatic failover

Environment Description: This environment consists of two MySQL databases and heartbeat, one IP

For 192.168.10.197, one for 192.168.10.198, external service VIP

To 192.168.10.200

Note: Heartbeat itself is not able to do services can not be automatically switched, so with the combination

Additional scripts are available, the Moniter script mentioned in this article is to implement a

When the MySQL service is not available, automatic switching can also automatically alarm

The installation and configuration process is divided into the following steps:

First part: MySQL installation configuration

1 installation

1.1. Add the users and groups required for MySQL to run

Groupadd MySQL

useradd-g MySQL MySQL

1.2. Unzip the installation

Tar mysql-5.1.26-rc-linux-x86_64-glibc23.tar.gz

MV mysql-5.1.26-rc-linux-x86_64-glibc23/usr/local/mysql/

Chown-r Mysql:mysql/usr/local/mysql

1.3. Copy the Master profile 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. Initializing the database

Cd/usr/local/mysql

scripts/mysql_install_db--user=mysql

1.5. Change the owner and group of the data Catalog

Chown-r Mysql:mysql./data

1.6. Registering MySQL for system service

Chkconfig--add mysqld

Chkconfig--levels 2345 mysqld on

1.7. Output Environment variables

#Vi/etc/profile (content below)

Path= $PATH:/usr/local/mysql/bin

# Source/etc/profile

1.8. Set the database password

Service mysqld Start

mysqladmin password 123456 (password yourself here is just an example)

1.9. Turn on the root remote access permission

mysql–p123456

Mysql> Grant All on * * to [e-mail protected] '% ' identified by ' 123456 ';

Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges;

Mysql> quit

2 parameter settings

2.1. Create the relevant directory 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 replication accounts (same settings for main and secondary libraries)

[Email protected] ~]# mysql-p

Enter Password: (enter root password)

Mysql> Grant All on * * to Qiangao identified by ' 123456 '; (where the account number and password are to be

Same as specified in the configuration file)

mysql> flush Privileges;

2.3. Modify the master configuration file

Note: Where IP to point to each other's IP set according to the actual situation, the user name and password to be consistent with the above user name password to maintain the main library 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 = 10M

Myisam_repair_threads = 1

################ Select cache Options ##################

Read_buffer_size = 2M

Read_rnd_buffer_size = 16M

Bulk_insert_buffer_size = 5M

Max_allowed_packet = 1M

Table_cache = 2048

Query_cache_size = 32M

Query_cache_limit = 2M

Sort_buffer_size = 8M

Join_buffer_size = 8M

Thread_concurrency = 8

################ Index Cache Options ##################

Key_buffer_size = 32M

Key_buffer_size = 32M

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

master-password=123456

master-connect-retry=10

############## Log ##################

Log-error=/var/log/mysql.log

######### INNODB #########

Innodb_file_per_table

#+++++++ Log ++++++++#

Innodb_log_buffer_size = 10M

Innodb_mirrored_log_groups = 1

Innodb_log_files_in_group = 3

Innodb_log_file_size = 50M

Innodb_flush_log_at_trx_commit = 0

#innodb_log_archive = 0

#+++++++ System Buffer +++++++#

Innodb_buffer_pool_size = 1024M

Innodb_additional_mem_pool_size = 40M

#+++++++ 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 = 16M

[MySQL]

No-auto-rehash

# Remove The next comment character if you is not a familiar with SQL

#safe-updates

[Isamchk]

Key_buffer = 128M

Sort_buffer_size = 128M

Read_buffer = 2M

Write_buffer = 2M

[Myisamchk]

Key_buffer = 128M

Sort_buffer_size = 128M

Read_buffer = 2M

Write_buffer = 2M

[Mysqlhotcopy]

Interactive-timeout

Secondary Library 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 = 10M

Myisam_repair_threads = 1

################ Select cache Options ##################

Read_buffer_size = 2M

Read_rnd_buffer_size = 16M

Bulk_insert_buffer_size = 5M

Max_allowed_packet = 1M

Table_cache = 2048

Query_cache_size = 32M

Query_cache_limit = 2M

Sort_buffer_size = 8M

Join_buffer_size = 8M

Thread_concurrency = 8

################ Index Cache Options ##################

Key_buffer_size = 32M

Key_buffer_size = 32M

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

master-password=123456

master-connect-retry=10

############## Log ##################

Log-error=/var/log/mysql/error.log

######### INNODB #########

Innodb_file_per_table

#+++++++ Log ++++++++#

Innodb_log_buffer_size = 10M

Innodb_mirrored_log_groups = 1

Innodb_log_files_in_group = 3

Innodb_log_file_size = 50M

Innodb_flush_log_at_trx_commit = 0

#innodb_log_archive = 0

#+++++++ System Buffer +++++++#

Innodb_buffer_pool_size = 1024M

Innodb_additional_mem_pool_size = 40M

#+++++++ 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 = 16M

[MySQL]

No-auto-rehash

# Remove The next comment character if you is not a familiar with SQL

#safe-updates

[Isamchk]

Key_buffer = 128M

Sort_buffer_size = 128M

Read_buffer = 2M

Write_buffer = 2M

[Myisamchk]

Key_buffer = 128M

Sort_buffer_size = 128M

Read_buffer = 2M

Write_buffer = 2M

[Mysqlhotcopy]

Interactive-timeout

2.4. Restart the service

Service mysqld Restart

3 Firewall is set

Note that the firewall settings for the primary and secondary libraries are the same

Iptables-i input-p TCP--dport 3306-j ACCEPT

Service Iptables Save

Part II: Installation and configuration of the heartbeat

Two host hardware environments (not exactly the same):

eth0: External IP

eth1: Internal IP (HA dedicated)

The eht1 of the two hosts is connected directly to the line using two machines.

"Two" pre-installation network environment settings:

================================================

========

Node1: Host Name: master.qiangao.com

eth0:192.168.10.197//External IP address

eth1:172.16.1.3//ha Heartbeat Use Address

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

Node2: Host Name: slave.qiangao.com

eth0:192.168.10.198//External IP address

eth1:172.16.1.4//ha Heartbeat Use Address

Special note To 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 of Node1 are as follows:

127.0.0.1 master.qiangao.com Master localhost.localdomain localhost

192.168.10.197 master.qiangao.com

192.168.10.198 slave.qiangao.com

:: 1 localhost6.localdomain6 Localhost6

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

The hosts of Node2 are 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"//gateway using network card

Onboot=yes//load at startup

forward_ipv4= "yes"//Only allow IPV4

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

#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 above configuration, in addition to

/etc/hosts

/etc/sysconfig/network

/etc/sysconfig/network-scripts/ifcfg-eth0

/etc/sysconfig/network-scripts/ifcfg-eth1

To be modified separately, the other is consistent.

After the configuration is complete, try pinging the host name on the respective host, and you should be able to ping through:

[[email protected] ~]# Ping slave.qiangao.com

PING slave.qiangao.com (192.168.10.198) bytes of data.

Bytes from slave.qiangao.com (192.168.10.198): Icmp_seq=1 ttl=64

time=0.136 ms

"Three" installs Ha and ha dependency packages

================================================

===========

Yum Install Heartbeat

Yum Install Ipvsadm

Yum Install Libnet

"Quad" configuration profiles for HA

First copy the configuration file to/etc directory

cd/usr/share/doc/heartbeat-2.1.3

CP HA.CF haresources/etc/ha.d/

CP authkeys/etc/ha.d/

================================================

===========

Configure how your heartbeat is encrypted: Authkeys

If you use dual-machine couplet lines (twisted pair), you can configure the following:

#vi/etc/hc.d/authkeys

Auth 1

1 CRC

Save the exit, and then

#chmod Authkeys

================================================

===========

Configuring Heartbeat Monitoring: Haresources

Create a detection script (if there is no monitoring script hearbeat will not start)

Vi/etc/init.d/test (content below)

#!/bin/bash

echo "" $>/dev/null

chmod 777/etc/init.d/test

================================================

===========

#vi/etc/ha.d/haresources (Configuration resource file)

This part of each host should be identical.

master.qiangao.com 192.168.10.200 Test

Specifies that master.qiangao.com invokes a pre-written test script, and the system attaches a virtual IP

192.168.10.200 to eth0:0

If master.qiangao.com is down, slave.qiangao.com can be assigned a new IP

192.168.10.200

================================================

===========

Configure the Heartbeat profile: HA.CF

#vi/ETC/HA.D/HA.CF

Log file record location for Logfile/var/log/ha_log/ha-log.log # # ha. If not

This directory, you need to manually add

Bcast eth1 # #使用eht1 do heartbeat monitoring

KeepAlive 2 # #设定心跳 (monitoring) time is 2 seconds

Warntime 4### #警告时间

Deadtime 6######## #确定服务以死的时间

Initdead 30

Hopfudge 1

Udpport 694 # #使用udp Port 694 for heartbeat monitoring

Auto_failback on

Node Master.qiangao.com # #节点1 must be consistent with the results of the UNAME-N directive.

Node Slave.qiangao.com # #节点2

Ping 172.16.1.04 # #通过ping The other side to monitor 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

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

Startup, shutdown of the "five" HA service

[Email protected] ha.d]# chkconfig--add Heartbeat

[Email protected] ha.d]# chkconfig--levels 2345 Heartbeat on

Start Ha:service Heartbeat start

Shut down ha; Service Heartbeat Stop

"Six" firewall settings

================================================

====

The heartbeat uses UDP 694 ports for heartbeat monitoring by default. If the system has to use iptables to do

Firewall, you should remember to turn this port on.

#vi/etc/sysconfig/iptables

Add the following (point to each other's IP)

-A rh-firewall-1-input-p udp-m UDP--dport 694-d 172.16.1.4-j ACCEPT

This means that the UDP 694 port is open to the other's heartbeat network address 172.16.1.4.

#service iptables Restart

Reload Iptables.

Part III: Monitoring scripts

cat/usr/local/mysql/bin/moniter.sh

#!/bin/bash

mysql_path=/usr/local/mysql/bin/

User= "Root"

Password= "123456"

email= "[Email protected]"

Logfile=/var/log/moniter.log

Date= ' (date +%y-%m-%d--%h:%m:%s) '

Sleeptime=30

ip=$ (/sbin/ifconfig | grep "inet addr" | grep-v "127.0.0.1" | awk ' {print $;} ' |

Awk-f ': ' {print $;} ' | HEAD-1)

slave_io_running=$ (MYSQL-U$USER-P$PASSWORD-E ' show Slave status\g ' |

grep "Slave_io_running" | awk ' {print $} ')

slave_sql_running=$ (MYSQL-U$USER-P$PASSWORD-E ' show Slave status\g ' |

grep "Slave_sql_running" | awk ' {print $} ')

echo "plese fix the server of $IP error now!" > $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 <=====================" >> $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!" >> $logfile

Else

Mail-s "{$ip} heartbeat can ' t to up" 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

Else

Echo

"==============> $date <=====================" >> $logfile

echo "Slave is not running!" >> $logfile

/bin/mail-s "{$ip}_replicate error please fix it"

$email < $letter

Fi

Fi

Then set the automation task to detect every 2 minutes, you can now realize the dual-machine hot prepared

MySQL dual-machine hot standby +heartbeat cluster + automatic failover