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, 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 & 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

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

1 2 3 4 Next Page