MySQL dual-machine Hot Standby + Heartbeat cluster + automatic failover (2 seconds)

Environment Description: This environment consists of two mysql Databases and heartbeat. One ip address is 192.168.10.197 and the other ip address is 192.168.10.198. The vip address that provides external services is

Environment Description: This environment consists of two mysql Databases and heartbeat. One ip address is 192.168.10.197 and the other ip address is 192.168.10.198. The vip address that provides external services is

Environment Description: This environment consists of two mysql Databases and heartbeat. One ip address is 192.168.10.197, the other is 192.168.10.198, and the other is 192.168.10.200.

Note: heartbeat itself cannot automatically switch between services, so it can be achieved only by combining additional scripts, the moniter script mentioned in this Article can automatically trigger alarms when a 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.

2
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> grant all on *. * to root @ '%' identified by '123 ';
Query OK, 0 rows affected (0.00 sec)
Mysql> flush privileges;
Mysql> 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> grant all on *. * to qiangao identified by '000000'; (the account and password must be
As specified in the configuration file)
Mysql> flush privileges;
2.3. Modify the master configuration file

3

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

4
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

5
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