1、方案在一定程度上保证主库的高可用,在一台主库down掉之后,可以在极短的时间内切换到另一台从库上(尽可能减少主库宕机对业务造成的影响)
(1) 一台主库(master)提供服务,只负责数据的写入
(2) 一台数据库服务器资源做master主库的从库,之间做主从同步
(3) 两台主库之间做高可用,采用keepalived,master同时也作为keepalived的主
(4) 程序调用使用高可用的keepalived VIP的地址
(5) 所有提供服务的从服务器与master进行主从同步
图1-1 mysql集群系统框架图
2、主从复制详细搭建过程
在集群中的所有服务器上搭建mysql服务,一般采用的是源码包安装。
MySQL-VIP:192.168.237.200
MySQL-version: mysql-5.5.36
MySQL-master:192.168.237.127
MySQL-slave01:192.168.237.128
MySQL-slave02:192.168.237.128
Keepalived版本:1.1.20
4、安装依赖包
yum-y install cmake make gcc gcc-c++ ncurses-devel bisonopenssl-devel
5、所需要的用户/组
groupadd mysql
useradd -gmysql -r mysql
6、源码包
wgethttp://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.36.tar.gz
7、安装所需的目录
mkdir /data/mydata/{data,tmp,logs,mysqllog}
cd /data/mydata/mysqllogs
mkdir logs
修改权限:
chgrp -R mysql /data/mydata/data&& chown -R mysql /data/mydata/data
chgrp -R mysql /data/mydata/logs&& chown -R mysql /data/mydata/logs
chgrp -R mysql /data/mydata/tmp&& chown -R mysql /data/mydata/tmp
chgrp -R mysql /data/mydata/mysqllog&& chown -R mysql /data/mydata/mysqllog
tar xf mysql-5.5.36.tar.gz
cd mysql-5.5.36
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/data/mydata/data \
-DSYSCINFDIR=/etc \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWARP=0 \
-DWITH_UNIX_ADDR=/tmp/mysql.sock \
-DDEFAULT_CHARASET=uft8 \
-DDEFAULT_COLLATTON=utf9_general_ci
make && make install
9、提供启动脚本
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
10、主库提供配置文件
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set=utf-8
[mysqld]
server-id = 1
port = 3306
user = mysql
basedir = /usr/local/mysql
datadir = /data/mydata/data
tmpdir = /data/mydata/tmp
socket = /var/lib/mysql/mysql.sock
skip-external-locking
skip-name-resolve
default-storage-engine = INNODB
character-set-server = utf8
wait-timeout = 100
connect_timeout = 20
interactive_timeout = 100
back_log = 300
myisam_recover
event_scheduler = on
log-bin=/data/mydata/logs/mysql-bin
binlog_format = row
max_binlog_size = 64M
binlog_cache_size = 1M
slave-net-timeout = 10
skip-slave-start
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mydata/mysqllog/logs/mysql.slow
log-error= /data/mydata/mysqllog/logs/error.log
max_connections = 1000
max_user_connections = 1000
max_connect_errors = 10000
key_buffer_size =32M #以MyISAM为主的服务器,要调大此值
max_allowed_packet = 64M
table_cache = 4096
table_open_cache = 4096
table_definition_cache = 4096
sort_buffer_size =512K
read_buffer_size =512K
read_rnd_buffer_size = 512K
join_buffer_size =512K
tmp_table_size = 64M
max_heap_table_size = 64M
query_cache_type = 0
query_cache_size = 0
bulk_insert_buffer_size = 16M
thread_cache_size = 64
thread_concurrency =16 #CPU核数*2
thread_stack = 256K
innodb_data_home_dir = /data/mydata/data
innodb_log_group_home_dir= /data/mydata/mysqllog/logs
innodb_data_file_path = ibdata1:1G:autoextend
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances =4
innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 512M
innodb_log_buffer_size = 32M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 10
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 90
innodb_support_xa = 1
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 500
innodb_file_io_threads = 4
innodb_concurrency_tickets = 1000
log_bin_trust_function_creators = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table #是否采用单表单空间
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_io_capacity = 1000
innodb_file_format =Barracuda #不开启单表单空间,此选项无效
innodb_purge_threads = 1
innodb_purge_batch_size = 32
innodb_old_blocks_pct = 75
innodb_change_buffering = all
transaction_isolation = READ-COMMITTED
[mysqldump]
quick
max_allowed_packet = 32M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 10240
3.8 为salve提供配置文件
slave的配置文件仅需在master上稍作修改
server-id = 20
log_slave_updates = 1 #添加(将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启)
replicate-same-server-id=0 #添加(防止MySQL循环更新)
relay_log_recovery = 1 #添加(MySQLrelay_log的自动修复功能)
3.9 为从库提供配置文件
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set = utf8
[mysqld]
server-id = 2
port = 3306
user = mysql
basedir = /usr/local/mysql
datadir = /data/mydata/data
tmpdir = /data/mydata/tmp
socket = /var/lib/mysql/mysql.sock
skip-external-locking
skip-name-resolve
default-storage-engine = INNODB
character-set-server = utf8
wait-timeout = 100
connect_timeout = 20
interactive_timeout = 100
back_log = 300
myisam_recover
event_scheduler = on
log-bin=/data/mydata/logs/mysql-bin
binlog_format = row
max_binlog_size = 64M
binlog_cache_size = 1M
slave-net-timeout = 10
relay_log_recovery = 1
slow_query_log = 1
long_query_time = 1
slow_query_log_file= /data/mydata/mysqllog/logs/mysql.slow
log-error= /data/mydata/mysqllog/logs/error.log
max_connections = 500
max_user_connections = 500
max_connect_errors = 10000
key_buffer_size =32M #以MyISAM为主的服务器,要调大此值
max_allowed_packet = 64M
table_cache = 2048
table_open_cache = 2048
table_definition_cache = 2048
sort_buffer_size =128K
read_buffer_size =128K
read_rnd_buffer_size = 128K
join_buffer_size =128K
tmp_table_size =16M
max_heap_table_size = 16M
query_cache_type = 0
query_cache_size = 0
bulk_insert_buffer_size = 16M
thread_cache_size = 64
thread_concurrency =4 #CPU核数*2
thread_stack = 128K
innodb_data_home_dir= /data/mydata/data
innodb_log_group_home_dir= /data/mydata/mysqllog/logs
innodb_data_file_path =ibdata1:1G:autoextend
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances =4
innodb_additional_mem_pool_size = 4M
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 10
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 90
innodb_support_xa = 1
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 500
innodb_file_io_threads = 4
innodb_concurrency_tickets = 1000
log_bin_trust_function_creators = 1
innodb_flush_method =O_DIRECT
innodb_file_per_table #是否采用单表单空间
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_io_capacity = 1000
innodb_file_format =Barracuda #不开启单表单空间,此选项无效
innodb_purge_threads = 1
innodb_purge_batch_size = 32
innodb_old_blocks_pct = 75
innodb_change_buffering = all
transaction_isolation = READ-COMMITTED
[mysqldump]
quick
max_allowed_packet = 32M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 10240
3.10 初始化MySQL
/usr/local/mysql/scripts/mysql_install_db --user=mysql--datadir=/data/mydata/data/ --basedir=/usr/local/mysql
3.11 为启动脚本赋予可执行权限并启动MySQL
chmod +x /etc/rc.d/init.d/mysqld
/etc/init.d/mysqld start
3.12 映射命令路径
ln -s /usr/local/mysql/bin/mysql/usr/bin/mysql
3.13 关闭防火墙
关闭命令: service iptables stop
永久关闭防火墙:chkconfig iptables off
两个命令同时运行,运行完成后查看防火墙关闭状态
service iptables status
3.14 配置master
3.12.1 添加同步账户
mysql> grant replication slaveon *.* to 'repl'@'192.168.216.%' idetified by 'repl';
mysql> flush privileges;
3.12.2查看主库状态
mysql> show master status;
3.15 配置slave
3.13.1配置slave为master的从
grant replication slave on *.* to'repl'@'%' identified by 'repl';
flush privileges;
#若是线上有数据需要先导入数据
mysql> CHANGE MASTER TO
->MASTER_HOST='192.168.237.128',
->MASTER_PORT=3306,
->MASTER_USER='repl',
->MASTER_PASSWORD='123456',
->MASTER_LOG_FILE='mysql-bin.000009',
->MASTER_LOG_POS=652;
Query OK, 0 rows affected (0.03sec)
mysql> start slave;
mysql> show slave status \G
Slave_IO_Running:Yes #确保为yes
Slave_SQL_Running:Yes #确保为yes
如果显示
Slave_IO_State: Waiting to reconnect aftera failed registration on master
3.13.2 配置slave的同步用户
mysql> grant replication slave on *.* to 'repl’@’192.168.237.%' identifiedby '123456';
mysql> flush privileges;
3.13.3 查看slave的状态
mysql> showmaster status;
3.16 从库根据配置slave的从即可。
4 Keepalived架构图
图1-2keepalived架构图
5 keepalived安装及配置
5.1.
tar zxvf keepalived-1.1.20.tar.gz
cd keepalived-1.1.20
yum install popt-devel,
./configure--prefix=/usr/local/keepalived--with-kernel-dir=/usr/src/kernels/2.6.32-431.el6.x86_64/
make && make install
5.2. 配置
我们自己在新建一个配置文件,默认情况下keepalived启动时会去/etc/keepalived目录下找配置文件
mkdir /etc/keepalived
vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
luwenju@live.cn
}
notification_email_from luwenju@live.cn
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL-ha
}
vrrp_instance VI_1 {
state BACKUP #两台配置此处均是BACKUP
interface eth0
virtual_router_id 51
priority 100 #优先级,另一台改为90
advert_int 1
nopreempt #不抢占,只在优先级高的机器上设置即可,优先级低的机器不设置
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.237.200
}
}
virtual_server 192.168.237.200 3306 {
delay_loop 2 #每个2秒检查一次real_server状态
lb_algo wrr #LVS算法
lb_kind DR #LVS模式
persistence_timeout 60 #会话保持时间
protocol TCP
real_server 192.168.237.127 3306 {
weight 3
notify_down /usr/local/MySQL/bin/MySQL.sh #检测到服务down后执行的脚本
TCP_CHECK {
connect_timeout 10 #连接超时时间
nb_get_retry 3 #重连次数
delay_before_retry 3 #重连间隔时间
connect_port 3306 #健康检查端口
}
}
编写检测服务down后所要执行的脚本
vi /usr/local/MySQL/bin/MySQL.sh
#!/bin/sh
pkill keepalived
#chmod +x /usr/local/mysql/bin/mysql.sh
注:此脚本是上面配置文件notify_down选项所用到的,keepalived使用notify_down选项来检查real_server的服务状态,当发现real_server服务故障时,便触发此脚本;我们可以看到,脚本就一个命令,通过pkill keepalived强制杀死keepalived进程,从而实现了MySQL故障自动转移。另外,我们不用担心两个MySQL会同时提供数据更新操作,因为每台MySQL上的keepalived的配置里面只有本机MySQL的IP+VIP,而不是两台MySQL的IP+VIP
5.3. 启动keepalived
/usr/local/keepalived/sbin/keepalived –D
ps -aux | grep keepalived
5.4. 主库keepalived测试
找一台局域网服务器,然后去ping MySQL的VIP,这时候MySQL的VIP是可以ping的通的停止MySQL服务,看keepalived健康检查程序是否会触发我们编写的脚本.
5.5. 从库keepalived安装及配置
tar zxvf keepalived-1.1.20.tar.gz
cd keepalived-1.1.20
./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18-164.el5-i686
make && make install
5.6. 配置keepalived
这台配置和上面基本一样,但有三个地方不同:优先级为90、无抢占设置、real_server为本机IP:
mkdir /etc/keepalived
vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
luwenju@live.cn
}
notification_email_from luwenju@live.cn
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL-ha
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.200
}
}
virtual_server 192.168.237.200 3306 {
delay_loop 2
lb_algo wrr
lb_kind DR
persistence_timeout 60
protocol TCP
real_server 192.168.237.128 3306 {
weight 3
notify_down /usr/local/MySQL/bin/MySQL.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
5.7. 编写检测服务down后所要执行的脚本
vi /usr/local/MySQL/bin/MySQL.sh
|
chmod +x /usr/local/MySQL/bin/MySQL.sh
5.8. 启动keepalived
/usr/local/keepalived/sbin/keepalived –D
ps -aux | grep keepalived
5.9. MySQL远程登录测试
我们找一台安装有MySQL客户端的windows,然后登录VIP,看是否能登录,在登录之两台MySQL服务器都要授权允许从远程登录
MySQL> grant all privileges on *.* to 'root'@'%' identified by '123456';
MySQL> flush privileges;
5.10. 使用客户端登录VIP测试
C:\MySQL\bin>MySQL.exe -uroot -p123456 -h192.168.1.200 -P3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 224
Server version: 5.0.89-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL>
5.11. keepalived故障转移测试
在windows客户端一直去ping VIP,然后关闭192.168.237.127上的keepalived,正常情况下VIP就会切换到192.168.237.128上面去
开启192.168.237.127上的keepalived,关闭192.168.237.128上的keepalived,看是否能自动切换,正常情况下VIP又会属于192.168.237.127
注:keepalived切换速度还是非常块的,整个切换过程只需1-3秒
5.12. MySQL故障转移测试
在192.168.237.127上关闭MySQL服务,看VIP是否会切换到192.168.237.128上,开启192.168.237.127上的MySQL和keepalived,然后关闭192.168.237.128上的MySQL,看VIP是否会切换到192.168.237.127上。
下面是用windows客户端连接的MySQL的VIP,在切换时我执行了一个MySQL查询命令,从执行show databases到显示出结果时间为3-5秒(大家可以看到上面有个错误提示,不过不用担心,因为我们的keepalived切换大概为3秒左右,这3秒左右VIP是谁都不属于的)