mysql多机互备_三机互备MYSQL同步复制

主：

! Configuration File for keepalived

global_defs {

notification_email {

jkuser@163.com

}

notification_email_from root@localhost.local

smtp_server localhost

smtp_connect_timeout 30

router_id master_mysql

}

vrrp_instance VI_mysqlha {

state MASTER

interface eth1

virtual_router_id 51

priority 100

advert_int 1

authentication {

auth_type PASS

auth_pass aaa111

}

virtual_ipaddress {

172.16.20.100

}

}

virtual_server 172.16.20.100 3306 {

delay_loop 6

lb_algo wrr

lb_kind DR

persistence_timeout 60

protocol TCP

real_server 172.16.20.22 3306 {

weight 3

notify_down /usr/local/mysql/bin/mysqlha.sh

TCP_CHECK {

connect_timeout 13

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

}

vrrp_instance VI_mysqlharo {

state BACKUP

interface eth1

virtual_router_id 60

priority 60

advert_int 1

authentication {

auth_type PASS

auth_pass aaa222

}

virtual_ipaddress {

172.16.20.101

}

}

virtual_server 172.16.20.101 3306 {

delay_loop 8

lb_algo wrr

lb_kind DR

persistence_timeout 80

protocol TCP

real_server 172.16.20.24 3306 {

weight 3

#notify_down /usr/local/mysql/bin/mysqlha.sh

TCP_CHECK {

connect_timeout 13

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

real_server 172.16.20.25 3306 {

weight 3

TCP_CHECK {

connect_timeout 13

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

}

[mysqld]

datadir = /data/mysql

log-bin = /data/mysql/Mastermysql-bin

log-error = /usr/local/mysql/logs/mysqlerr.log

slow_query_log_file = /usr/local/mysql/logs/mysqlSlow.log

slow_query_log

long_query_time = 10

binlog-do-db = ddd

server-id = 11

innodb_flush_log_at_trx_commit = 0

innodb_buffer_pool_size = 512M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

从1：

! Configuration File for keepalived

global_defs {

notification_email {

jkuser@163.com

}

notification_email_from root@localhost.local

smtp_server localhost

smtp_connect_timeout 30

router_id bak1_mysql

}

vrrp_instance VI_mysqlha {

state BACKUP

interface eth1

virtual_router_id 51

priority 50

advert_int 1

authentication {

auth_type PASS

auth_pass aaa111

}

virtual_ipaddress {

172.16.20.100

}

}

virtual_server 172.16.20.100 3306 {

delay_loop 6

lb_algo wrr

lb_kind DR

persistence_timeout 60

protocol TCP

real_server 172.16.20.22 3306 {

weight 3

TCP_CHECK {

connect_timeout 13

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

}

vrrp_instance VI_mysqlharo {

state MASTER

interface eth1

virtual_router_id 60

priority 90

advert_int 1

authentication {

auth_type PASS

auth_pass aaa222

}

virtual_ipaddress {

172.16.20.101

}

}

virtual_server 172.16.20.101 3306 {

delay_loop 8

lb_algo wrr

lb_kind DR

persistence_timeout 80

protocol TCP

real_server 172.16.20.24 3306 {

weight 3

notify_down /usr/local/mysql/bin/mysqlha.sh

TCP_CHECK {

connect_timeout 13

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

real_server 172.16.20.25 3306 {

weight 3

notify_down /usr/local/mysql/bin/mysqlha.sh

TCP_CHECK {

connect_timeout 13

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

}

[mysqld]

datadir = /data/mysql

log-bin = /data/mysql/Slavemysql1-bin

relay-log = Slavemysql1-relay-bin

log-error = /usr/local/mysql/logs/mysqlerr.log

slow_query_log_file = /usr/local/mysql/logs/mysqlSlow.log

slow_query_log

long_query_time = 10

binlog-do-db = ddd

server-id = 22

innodb_flush_log_at_trx_commit = 0

innodb_buffer_pool_size = 512M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

从2：

! Configuration File for keepalived

global_defs {

notification_email {

jkuser@163.com

}

notification_email_from root@localhost.local

smtp_server localhost

smtp_connect_timeout 30

router_id bak2_mysql

}

vrrp_instance VI_mysqlha {

state BACKUP

interface eth1

virtual_router_id 51

priority 80

advert_int 1

authentication {

auth_type PASS

auth_pass aaa111

}

virtual_ipaddress {

172.16.20.100

}

}

virtual_server 172.16.20.100 3306 {

delay_loop 6

lb_algo wrr

lb_kind DR

persistence_timeout 60

protocol TCP

real_server 172.16.20.22 3306 {

weight 3

TCP_CHECK {

connect_timeout 13

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

}

vrrp_instance VI_mysqlharo {

state BACKUP

interface eth1

virtual_router_id 60

priority 80

advert_int 1

authentication {

auth_type PASS

auth_pass aaa222

}

virtual_ipaddress {

172.16.20.101

}

}

virtual_server 172.16.20.101 3306 {

delay_loop 8

lb_algo wrr

lb_kind DR

persistence_timeout 80

protocol TCP

real_server 172.16.20.24 3306 {

weight 3

#notify_down /usr/local/mysql/bin/mysqlha.sh

TCP_CHECK {

connect_timeout 13

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

real_server 172.16.20.25 3306 {

weight 3

TCP_CHECK {

connect_timeout 13

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

}

}

[mysqld]

datadir = /data/mysql

log-bin = /data/mysql/Slavemysql2-bin

relay-log = Slavemysql2-relay-bin

log-error = /usr/local/mysql/logs/mysqlerr.log

slow_query_log_file = /usr/local/mysql/logs/mysqlSlow.log

slow_query_log

long_query_time = 10

binlog-do-db = ddd

server-id = 23

innodb_flush_log_at_trx_commit = 0

innodb_buffer_pool_size = 512M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES