mysql 双主高可用配置

Mysql+ Keepalive配置

server1：10.0.0.4
server2: 10.0.0.3

Mysql安装配置

使用YUM安装：
#yum install -y mysql-community-client.x86_64 mysql-community-common.x86_64 mysql-community-devel.x86_64 mysql-community-libs.x86_64 mysql-community-libs-compat.x86_64 mysql-community-server.x86_64

############ server1: My.cnf配置文件：##################

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server-id=202
gtid-mode=ON
binlog_format=row
log-slave-updates=true
enforce-gtid-consistency
log-bin=mysql-bin
log-slave-updates
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=4
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
character_set_server=utf8
max_connections = 500
expire_logs_days = 5
slow_query_log = 1
slow_query_log_file=/var/lib/mysql/slow.log
long_query_time = 1
sync_binlog=1
join_buffer_size = 16M
read_buffer_size = 16M
sort_buffer_size = 16M
table_open_cache = 700
max_allowed_packet = 32M
skip_name_resolve = 1
explicit_defaults_for_timestamp = 1

innodb_file_per_table = 1
innodb_fast_shutdown = 0
innodb_purge_threads = 32
innodb_buffer_pool_size = 2G
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_instances = 4
innodb_use_native_aio = on
innodb_support_xa = 1
innodb_flush_neighbors = 1
autocommit=1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 30
sql_mode=NO_ENGINE_SUBSTITUTION

replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=performance_schema.%

############## Server2: My.cnf配置文件 ##################

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server-id=201
gtid-mode=ON
binlog_format=row
log-slave-updates=true
enforce-gtid-consistency
log-bin=mysql-bin
log-slave-updates
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=4
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1

replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=performance_schema.%

################ END ###############

修改Mysql初时密码：

使用初时密码登陆Mysql
#mysql -u root

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');
mysql> FLUSH PRIVILEGES;

在两台服务器上创建主从复制用户：
mysql> grant replication slave,replication client on *.* to slave@'%' identified by '123456';
mysql> FLUSH PRIVILEGES;

#### Server1操作：

mysql> change master to master_host='10.0.0.3',master_port=3306,master_user='slave',master_password='123456',master_auto_position=1;

mysql> start slave;

#### Server2操作：

mysql> change master to master_host='10.0.0.4',master_port=3306,master_user='slave',master_password='123456',master_auto_position=1;

mysql> start slave;

查看状态：
server2 上：
mysql> show slave statusG;

mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.4 #######
Master_User: slave #######
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 1406
Relay_Log_File: appserver-relay-bin.000007
Relay_Log_Pos: 454
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes ##########
Slave_SQL_Running: Yes ##########
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1406
Relay_Log_Space: 918
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 202 ##########
Master_UUID: 6a9d0dae-86fa-11e7-a9a6-0894ef365e0a
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 6a9d0dae-86fa-11e7-a9a6-0894ef365e0a:4-6
Executed_Gtid_Set: 6a9d0dae-86fa-11e7-a9a6-0894ef365e0a:1-6,
fcfa135e-873b-11e7-8930-0894ef364852:1-15
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

Keepalive 安装：

# yum install keepalived ipvsadm

######### 配置：10.0.0.3 ####
[root@appserver ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
notification_email {
wdrain@163.com
}
notification_email_from support@163.com
smtp_server localhost
smtp_connect_timeout 30
router_id LVS_DEVEL
}

vrrp_instance VI_1 {
state BACKUP ##服务器均是backup模式
interface eth0
virtual_router_id 51 ##数字相同
priority 100 ###优先级数字越大优先级越高
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
10.0.0.5/24 dev eth0
}
}

virtual_server 10.0.0.5 3306 {
delay_loop 5
lb_algo wrr ###算法
lb_kind DR
nat_mask 255.255.255.0
persistence_timeout 50
protocol TCP

real_server 10.0.0.3 3306 {
weight 3
notify_down /etc/keepalived/change_mysql.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}

######### 配置：10.0.0.4 ##########
[root@dataserver ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
notification_email {
wdrain@163.com
}
notification_email_from support@163.com
smtp_server localhost
smtp_connect_timeout 30
router_id LVS_DEVEL
}

vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
nopreempt ##不主动抢占资源，设置非抢占模式预防脑裂发生就是在主服务器回复后vip不漂移。
priority 200 ###优先级数字越大优先级越高
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
10.0.0.5/24 dev eth0
}
}

virtual_server 10.0.0.5 3306 {
delay_loop 5 ##检测延迟
lb_algo wrr
lb_kind DR
nat_mask 255.255.255.0
persistence_timeout 50
protocol TCP

real_server 10.0.0.4 3306 {
weight 3
notify_down /etc/keepalived/change_mysql.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}

[root@dataserver ~]# cat /etc/keepalived/change_mysql.sh
#!/bin/bash
pkill keepalived

#service keepalived start/stop
#service mysqld start/stop