mysql的双机热备自动切换_MySQL双机热备+Heartbeat集群+自动故障转移（2秒）

MySQL双机热备+Heartbeat集群+自动故障转移(2秒)

[日期：2011-12-17]

来源：Linux社区

作者：qubaoquan

[字体：大 中 小]

6

write_buffer = 2M

[MySQLhotcopy]

interactive-timeout

辅库设置

vi /etc/my.cnf(用如下内容覆盖原有内容，)

################################################

##########

[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

7

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

8

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 are not 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

9

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

2.4. 重新启动服务

service mysqld restart

3 防火墙是设置

注意主库和辅库的防火墙设置是一样的

iptables -I INPUT -p tcp --dport 3306 -j ACCEPT

service iptables save

第二部分：heartbeat 的安装和配置

两台主机硬件环境(不必完全一致)：

eth0: 对外IP

eth1: 对内IP(HA 专用)

两台主机的eht1 使用双机对联线直接连接。