双A复制mysql_mysql双主复制并利用keepalived做高可用_MySQL

bitsCN.com

1、架构

vip 192.168..45.244

mysql-1:192.168.45.238

mysql-2:192.168.45.239

2、mysql双主设置

192.168.45.238

#vim /etc/my.cnf

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

port = 3306

socket = /tmp/mysql.sock

datadir=/usr/local/mysql/var/

skip-locking

skip-name-resolve

key_buffer = 64M

max_allowed_packet = 64M

table_cache = 2048

sort_buffer_size = 4M

net_buffer_length = 256K

read_buffer_size = 10M

read_rnd_buffer_size = 10M

myisam_sort_buffer_size = 16M

interactive_timeout = 240

wait_timeout = 240

max_connections = 800

connect_timeout=30

open_files_limit=8192

query_cache_size = 1024M

thread_cache_size=16

thread_concurrency = 8

long_query_time = 1

log-slow-queries = slow.log

innodb_additional_mem_pool_size = 8M

innodb_buffer_pool_size = 32M

innodb_log_buffer_size=8M

innodb_log_file_size = 256M

innodb_log_files_in_group = 3

innodb_file_io_threads = 8

innodb_lock_wait_timeout= 50

innodb_thread_concurrency = 16

innodb_file_per_table

log_slave_updates

expire_logs_days=7

auto_increment_increment=2

auto_increment_offset=2

binlog_format=mixed

log-bin=mysql-bin

server-id = 8

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[isamchk]

key_buffer = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

[myisamchk]

key_buffer = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

设置mysql复制账号

mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.45.238' IDENTIFIED BY 'repl_123';

mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.45.239' IDENTIFIED BY 'repl_123';

导出数据库

#mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases > all.sql

192.168.45.239

mysql配置

#vim /etc/my.cnf

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

port = 3306

socket = /tmp/mysql.sock

datadir=/usr/local/mysql/var/

skip-locking

skip-name-resolve

key_buffer = 64M

max_allowed_packet = 64M

table_cache = 128

sort_buffer_size = 4M

net_buffer_length = 256K

read_buffer_size = 10M

read_rnd_buffer_size = 10M

myisam_sort_buffer_size = 64M

interactive_timeout = 240

wait_timeout = 240

max_connections = 800

connect_timeout=30

open_files_limit=8192

query_cache_size = 1024M

thread_cache_size=16

thread_concurrency = 8

long_query_time = 1

log-slow-queries = slow.log

innodb_additional_mem_pool_size = 8M

innodb_buffer_pool_size = 64M

innodb_log_buffer_size=8M

innodb_log_file_size = 256M

innodb_log_files_in_group = 3

innodb_file_io_threads = 8

innodb_lock_wait_timeout= 50

innodb_thread_concurrency = 16

innodb_file_per_table

log_slave_updates

expire_logs_days=7

auto_increment_increment=2

auto_increment_offset=1

binlog_format=mixed

log-bin=mysql-bin

server-id = 9

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[isamchk]

key_buffer = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

[myisamchk]

key_buffer = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

导入主库数据

#mysql -uroot -p < all.sql

设置同步

mysql> CHANGE MASTER TO MASTER_HOST='192.168.45.238',MASTER_PORT='3306',MASTER_USER='repl',MASTER_PASSWORD='repl_123',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=106;

mysql> start slave;

在192.168.45.238上设置同步

mysql>CHANGE MASTER TOMASTER_HOST='192.168.45.239',MASTER_PORT='3306',MASTER_USER='repl',MASTER_PASSWORD='repl_123',MASTER_LOG_FILE='mysql-bin.000008',MASTER_LOG_POS=105020214;

mysql>start slave;

3、配置keepalived

192.168.45.238

192.168.45.239

安装keepalived

wget http://www.keepalived.org/software/keepalived-1.2.2.tar.gz

tar zxvf keepalived-1.2.2.tar.gz

cd keepalived-1.2.2

./configure --prefix=/

make

make install

192.168.45.238 keepalived 配置

6># vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

router_id yuangnag.com

}

vrrp_script check_run {

script "/root/keepalived_check_mysql.sh"

interval 5

}

vrrp_sync_group VG1 {

group {

VI_1

}

}

vrrp_instance VI_1 {

state MASTER

interface eth0

virtual_router_id 88

priority 100

advert_int 1

nopreempt

authentication {

auth_type PASS

auth_pass yuangang.net

}

track_script {

check_run

}

virtual_ipaddress {

192.168.45.244

}

}

检测mysql脚本配置(两台mysql一样的配置)

#vim #!/bin/bash

MYSQL=/usr/local/mysql/bin/mysql

MYSQL_HOST=localhost

MYSQL_USER=root

MYSQL_PASSWORD=

CHECK_TIME=3

#mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0

MYSQL_OK=1

function check_mysql_helth (){

$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -e "show status;" >/dev/null 2>&1

if [ $? = 0 ] ;then

MYSQL_OK=1

else

MYSQL_OK=0

fi

return $MYSQL_OK

}

while [ $CHECK_TIME -ne 0 ]

do

let "CHECK_TIME -= 1"

check_mysql_helth

if [ $MYSQL_OK = 1 ] ; then

CHECK_TIME=0

exit 0

fi

if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ]

then

/etc/init.d/keepalived stop

exit 1

fi

sleep 1

done

#!/bin/bash

MYSQL=/usr/local/mysql/bin/mysql

MYSQL_HOST=localhost

MYSQL_USER=root

MYSQL_PASSWORD=

CHECK_TIME=3

#mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0

MYSQL_OK=1

function check_mysql_helth (){

$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -e "show status;" >/dev/null 2>&1

if [ $? = 0 ] ;then

MYSQL_OK=1

else

MYSQL_OK=0

fi

return $MYSQL_OK

}

while [ $CHECK_TIME -ne 0 ]

do

let "CHECK_TIME -= 1"

check_mysql_helth

if [ $MYSQL_OK = 1 ] ; then

CHECK_TIME=0

exit 0

fi

if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ]

then

/etc/init.d/keepalived stop

exit 1

fi

sleep 1

done

chmod 755 /root/keepalived_check_mysql.sh

192.168.45.239 keepalived配置

# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

router_id yuangang.com

}

vrrp_script check_run {

script "/root/keepalived_check_mysql.sh"

interval 5

}

vrrp_sync_group VG1 {

group {

VI_1

}

}

vrrp_instance VI_1 {

state BACKUP

interface eth0

virtual_router_id 88

priority 80

advert_int 1

authentication {

auth_type PASS

auth_pass yuangang.com

}

track_script {

check_run

}

virtual_ipaddress {

192.168.45.244

}

}

启动 238上的keepalived mysql

/etc/init.d/keepalived start

/etc/init.d/mysqld start

启动239上的keepalived mysql

/etc/init.d/keepalived start

/etc/init.d/mysqld start

测试，关闭238上的mysql在另外一台机器上用vip连接mysql

关闭239上的mysql在另外一台机器上用vip连接mysql

作者“linux运维”bitsCN.com

本文原创发布php中文网，转载请注明出处，感谢您的尊重！