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

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

环境说明：本环境由两台mysql 数据库和heartbeat 组成，一台的ip 为192.168.10.197，一台为192.168.10.198，对外提供服务的vip 为192.168.10.200

备注：heartbeat 本身是不能做到服务不可用自动切换的，所以用结合额外的脚本才可以做到，本文中提到的moniter 脚本即为实现某个mysql 服务不可用的时候自动切换的还可以自动报警

安装和配置过程分为如下几步：

第一部分：mysql 的安装配置
1 安装
1.1. 添加mysql 运行所需的用户和组
groupadd mysql
useradd -g mysql mysql
1.2. 解压安装
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. 复制主配置文件和启动脚本
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. 初始化数据库
cd /usr/local/mysql
scripts/mysql_install_db --user=mysql
1.5. 更改数据目录的所有者和组

2
chown -R mysql:mysql ./data
1.6. 注册mysql 为系统服务
chkconfig --add mysqld
chkconfig --levels 2345 mysqld on
1.7. 输出环境变量
#Vi /etc/profile(内容如下)
PATH=$PATH:/usr/local/mysql/bin
# source /etc/profile
1.8. 设置数据库密码
service mysqld start
mysqladmin password 123456（密码自己定这里只是个例子）
1.9. 开启root 远程访问权限
mysql –p123456
mysql> grant all on *.* to root@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
mysql> quit
2 参数设置
2.1. 创建相关目录并设置权限
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. 添加同步复制的账号（主库和辅库的设置相同）
[root@master ~]# mysql -p
Enter password:（输入root 密码）
mysql> grant all on *.* to qiangao identified by '123456';（其中的账号和密码要
和配置文件中指定的相同）
mysql> flush privileges;
2.3. 修改主配置文件

3

注意：其中ip 要互相指向对方的ip 按实际情况来设定，用户名和密码要和上文的用户名密
码保持一致
主库设置
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
bulk_insert_buffer_size = 5M

4
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

5
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
read_buffer = 2M