keepalived+mysql主主配置手册
实验架构图：http://s3.运维网.com/wyfs02/M01/5A/04/wKiom1TzzK3htHrLAADWa3vn87s945.jpg
一．mysql 5.5双机热备份 master-master
1.系统环境
操作系统:centos6.6
masterA IP:192.168.166.161
masterB ip:192.168.166.162
应用软件
mysql-5.5.42.tar.gz
keepalived-1.2.12.tar.gz
2.安装mysql
wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.42.tar.gz
#useradd-r mysql -s /sbin/nologin
#mkdir-p /data/mysql/{data,binlog,relaylog}
#chownmysql:mysql -R /data/mysql
#tar xfmysql-5.5.42.tar.gz
#cdmysql-5.5.42
# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/data/mysql/data \
-DMYSQL_DATADIR=/etc \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_ZLIB=system \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DWITH_LIBWRAP=0 \
-DWITH_READLINE=1 \
-DSYSCONFDIR=/etc \
-DMYSQL_TCP_PORT=3306
# #会编译不过去，提示需要安装ncurses-devel
#yum -y install ncurses-develgcc gcc-c++ bison
#rm -f CMakeCache.txt
重新执行cmake
#make&& make install
#cd /usr/local/mysql
#chown -R mysql:mysql *
# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
#chmod +x /etc/init.d/mysqld
#chkconfig -add mysqld
#chkconfig mysqld on
# vim /etc/profile 添加PATH=$PATH:/usr/local/mysql/bin
# . /etc/profile(或者export PATH=$PATH:/usr/local/mysql/bin)
# ln -sv /usr/local/mysql/include/ /usr/include/mysql
# echo '/usr/local/mysql/lib' >/etc/ld.so.conf.d/mysql.conf 加载库文件
# ldconfig -v |grep mysql
# vim /etc/man.config 添加一行MANPATH /usr/local/mysql/man
# cd /usr/local/mysql
# scripts/mysql_install_db --user=mysql--datadir=/data/mysql/data
3.配置master A
将如下配置拷贝到/etc/my.cnf
default-character-set = utf8
port=3306
socket = /tmp/mysql.sock
user = mysql
port=3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir=/data/mysql/data
pid-file = /data/mysql/mysql.pid
log-error = /data/mysql/mysql-error.log
#max_connections=1000
#log_slave_update =1
log-bin = /data/mysql/binlog/mysql-bin
log-bin-index = /data/mysql/binlog/mysql-bin.index
binlog_format = mixed
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 1G
expire_logs_days = 30
#不需要同步的数据，且不记录到binlog中。
binlog-do-db=small
binlog-ignore-db=mysql
replicate-do-db=small
replicate-ignore-db=mysql
#同步参数:
#保证slave挂在任何一台master上都会接收到另一个master的写入信息
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_offset=1
auto_increment_increment=2
key_buffer_size = 384M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size =16M
join_buffer_size =2M
thread_cache_size = 300
query_cache_limit = 2M
query_cache_min_res_unit =2K
thread_concurrency = 8
table_cache =614
table_open_cache = 512
open_files_limit = 10240
back_log = 600
max_connections = 5000
max_connect_errors = 6000
external-locking = FALSE
max_allowed_packet =16M
default_storage_engine = MyISAM
#default_storage_engine = InnoDB
thread_stack =192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 256M
max_heap_table_size =512M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
long_query_time = 2
slow_query_log = on
slow_query_log_file = /data/mysql/slow.log
log-queries-not-using-indexes =on
log-slow-admin-statements
skip-name-resolve
skip-external_locking
log_bin_trust_function_creators=1
#skip-networking
#skip-innodb
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 512M
innodb_data_file_path = ibdata1:256M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit =2
innodb_log_file_size =128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout =240
innodb_file_per_table = 0
innodb_status_file = 1
interactive_timeout=120
wait_timeout=120
server-id=1
#innodb_flush_logs_at_trx_commit=1
quick
max_allowed_packet = 64M
no-auto-rehash
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
4.配置 master B
将如下配置拷贝到/etc/my.cnf
default-character-set = utf8
port=3306
socket = /tmp/mysql.sock
user = mysql
port=3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir=/data/mysql/data
pid-file = /data/mysql/mysql.pid
log-error = /data/mysql/mysql-error.log
#max_connections=1000
#log_slave_update =1
log-bin = /data/mysql/binlog/mysql-bin
log-bin-index = /data/mysql/binlog/mysql-bin.index
binlog_format = mixed
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 1G
expire_logs_days = 30
#需要同步的数据库
binlog-do-db=small
binlog-ignore-db=mysql
replicate-do-db=small
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
key_buffer_size = 384M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size =16M
join_buffer_size =2M
thread_cache_size = 300
query_cache_limit = 2M
query_cache_min_res_unit =2K
thread_concurrency = 8
table_cache =614
table_open_cache = 512
open_files_limit = 10240
back_log = 600
max_connections = 5000
max_connect_errors = 6000
external-locking = FALSE
max_allowed_packet =16M
default_storage_engine = MyISAM
#default_storage_engine = InnoDB
thread_stack =192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 256M
max_heap_table_size =512M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
long_query_time = 2
slow_query_log = on
slow_query_log_file = /data/mysql/slow.log
log-queries-not-using-indexes =on
log-slow-admin-statements
skip-name-resolve
skip-external_locking
log_bin_trust_function_creators=1
#skip-networking
#skip-innodb
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 512M
innodb_data_file_path = ibdata1:256M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit =2
innodb_log_file_size =128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout =240
innodb_file_per_table = 0
innodb_status_file = 1
interactive_timeout=120
wait_timeout=120
server-id=2
quick
max_allowed_packet = 64M
no-auto-rehash
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
5.创建授权用户
masterA:
Mysql>grant replication slave on *.* torpuser1@192.168.166.162 identified by '123456';
Mysql> flush privileges;
masterB:
Mysql>grant replication slave on *.* torpuser2@192.168.166.161 identified by '123456';
Mysql> flush privileges;
6.准备复制
Master A：
mysql> flush tables with read lock\G
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G
*************************** 1. row***************************
File: mysql-bin.000001
Position: 347
Binlog_Do_DB: small
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
master B：
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G
*************************** 1. row***************************
File: mysql-bin.000001
Position: 347
Binlog_Do_DB: small
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
7.配置同步
Master A：
mysql> change master to
-> master_host='192.168.166.162',
-> master_user='rpuser2',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
->master_log_pos=347;
Master B：
mysql> change master to
-> master_host='192.168.166.161',
-> master_user='rpuser1',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=347;
8.查看与验证
Master A:
Mysql>show slave status \G
Slave_IO_Running: Yes 这两个为yes表示正常
Slave_SQL_Running: Yes
Master B:
Mysql>show slave status \G
Slave_IO_Running: Yes 这两个为yes表示正常
Slave_SQL_Running: Yes
Master A:
创建数据库
create databases small;
Master B:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| small |
| test |
+--------------------+
5 rows in set (0.05 sec)
可以发现数据库small
Master B:
在small数据库里创建一张表，T1：
mysql> create table T1 select * from mysql.user;
Query OK, 7 rows affected (0.08 sec)
Records: 7 Duplicates: 0Warnings: 0
Master A：
查看small下面有T1这个表格：
mysql> use small;
No connection. Trying to reconnect...
Connection id: 10
Current database: *** NONE ***
Database changed
mysql> show tables;
+-----------------+
| Tables_in_small |
+-----------------+
| T1 |
+-----------------+
1 row in set (0.00 sec)
二．配置Keepalived实现MySQL双主高可用
1.下载软件
wget http://www.keepalived.org/software/keepalived-1.2.12.tar.gz
需要在两台服务器上安装keepalived，这里拿Master A安装过程举例
#tar xf keepalived-1.2.12.tar.gz
#cd keepalived-1.2.12
#yum –y install openssl-devel kernel-devel
#./configure --sysconf=/etc--with-kernel-dir=/usr/src/kernels/2.6.32-504.el6.x86_64/
#make
#make install
2.配置keepalived
Master A：
global_defs {
notification_email{
root@localhost
}
notification_email_from keepalived.example.com
router_id MySQL_HA
}
vrrp_script check_mysqld {
script "/etc/keepalived/check_slave.pl 127.0.0.1"
interval 2
weight 21
}
vrrp_instance HA_1 {
state BACKUP //master A和master B上均配置为BACKUP
interface eth0
virtual_router_id80
priority 100
advert_int 2
nopreempt //不抢占模式，只在优先级高的机器上设置即可，优先级低的机器可以不设置。
authentication {
auth_type PASS
auth_pass 23b14455cd
}
track_script {
check_mysqld
}
virtual_ipaddress {
192.168.166.254
}
}
其中，/etc/keepalived/check_slave.sh 脚本内容为：
#!/bin/bash
#######################################
# this script function is :
#check_mysql_slave_replication_status
#
# User YYYY-MM-DD - ACTION
# mlx2013-12-29 - Created
# mail 552326439@qq.com
#######################################
HOST_IP=localhost
HOST_PORT=3306
MYUSER=root
MYPASS="123456"
MYSOCK=/tmp/mysql.sock
MYSQL_PATH=/usr/local/mysql/bin
MYSQL_CMD="$MYSQL_PATH/mysql-u$MYUSER -p$MYPASS -S $MYSOCK"
CHECKNUM=123
MYSQL1=$($MYSQL_CMD -N -s -e"select ${CHECKNUM}")
if [ $? -ne 0 ] || ["${MYSQL1}" -ne "${CHECKNUM}" ];then
/etc/init.d/keepalived stop
exit 1
else
SlaveStatusArr=($($MYSQL_CMD -e"show slave status \G"|egrep "_Behind|_Running"|awk '{print$NF}'))
if [ "${SlaveStatusArr}"= "No" ]|| ["${SlaveStatusArr}" = "No" ];then
/etc/init.d/keepalived stop
fi
fi
说明：此监控脚本可以检测mysql服务是否启动和mysql主从同步是否正常。
Master B：
global_defs {
notification_email{
root@localhost
}
notification_email_from keepalived.example.com
router_id MySQL_HA
}
vrrp_script check_mysqld {
script "/etc/keepalived/check_slave.pl 127.0.0.1"
interval 2
weight 21
}
vrrp_instance HA_1 {
state BACKUP //master A和master B上均配置为BACKUP
interface eth0
virtual_router_id80
priority 90
advert_int 2
authentication {
auth_type PASS
auth_pass 23b14455cd
}
track_script {
check_mysqld
}
virtual_ipaddress {
192.168.166.254
}
}
其中，/etc/keepalived/check_slave.pl 脚本内容为：
#!/bin/bash
#######################################
# this script function is :
# check_mysql_slave_replication_status
#
# User YYYY-MM-DD - ACTION
# mlx2013-12-29 -Created
# mail 552326439@qq.com
#######################################
HOST_IP=localhost
HOST_PORT=3306
MYUSER=root
MYPASS="123456"
MYSOCK=/tmp/mysql.sock
MYSQL_PATH=/usr/local/mysql/bin
MYSQL_CMD="$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS -S$MYSOCK"
CHECKNUM=123
MYSQL1=$($MYSQL_CMD -N -s -e "select${CHECKNUM}")
if [ $? -ne 0 ] || [ "${MYSQL1}" -ne"${CHECKNUM}" ];then
/etc/init.d/keepalived stop
exit 1
else
SlaveStatusArr=($($MYSQL_CMD -e "show slave status \G"|egrep"_Behind|_Running"|awk '{print $NF}'))
if ["${SlaveStatusArr}" = "No" ]|| [ "${SlaveStatusArr}" ="No" ];then
/etc/init.d/keepalived stop
fi
fi
3.启动keepalived
Master A:
#cp /usr/local/sbin/keepalived/usr/sbin/
#service keepalived start
#chkconfig –level 2345 keepalived on
Master B:
#cp /usr/local/sbin/keepalived/usr/sbin/
#service keepalived start
#chkconfig –level 2345 keepalived on
3.测试服务的高可用功能
我们在webserver这台主机上用mysql客户端连接vip：192.168.166.254
#mysql-uroot -p123456 -h 192.168.166.254
http://s3.运维网.com/wyfs02/M00/5A/01/wKioL1TzziiR3mGDAAIwlTKjX3M992.jpg
http://s3.运维网.com/wyfs02/M02/5A/01/wKioL1TzzjPSE46FAAHzW_p3crc891.jpg
说明：此时vip地址在Master B 这台服务器上。
4.测试故障转移
故障模拟，我们这时手动停掉192.168.166.254的MySQL复制线程
mysql>stopslave;
http://s3.运维网.com/wyfs02/M01/5A/01/wKioL1Tzzmuj3JJtAAEafZJt0sc932.jpg
http://s3.运维网.com/wyfs02/M01/5A/04/wKiom1TzzVvg1zxoAACkYgC1GYg683.jpg
http://s3.运维网.com/wyfs02/M02/5A/01/wKioL1TzzmvThgg8AACjS2eufyI245.jpg
这里可以看到，当停掉复制线程后，执行查询时连接中断了一次，马上再次连接上完成查询，显示的server_id已经变成1了，表示服务器已经切换了。
查看master A服务器的IP地址验证VIP是否转移过来了
http://s3.运维网.com/wyfs02/M02/5A/04/wKiom1TzzX6iy_EpAAF1vZBPOcY116.jpg
5.模拟网线故障
手动停掉master A服务武器eth0网卡
#ifdown eth0
http://s3.运维网.com/wyfs02/M01/5A/04/wKiom1TzzcDBjrUCAAF1vZBPOcY854.jpg
http://s3.运维网.com/wyfs02/M02/5A/01/wKioL1TzztHw-FW-AAJMeqjsKx4655.jpg
http://s3.运维网.com/wyfs02/M02/5A/04/wKiom1TzzcDBYL4YAAFc6x5Iq-A183.jpg
说明：vip地址已经转到Master B服务器上，并且mysql服务已经做了故障转移
5.测试完成
页:
[1]