高可用mysql集群搭建（mysql5.6+keepalived）

2023-10-31

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;

4 Keepalived架构图

图1-2keepalived架构图

5 keepalived安装及配置

5.1. 安装keepalived

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

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

大数据