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 

 

#!/bin/sh  
pkill keepalived

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是谁都不属于的）