MySql Database HA：Keepalived+MySQL Replication-Oracle&Mysql DBA-51CTO博客
# mysql -uroot -pabcd.1234mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.202
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 120
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
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: 120
Relay_Log_Space: 450
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: 2
Master_UUID: 69d85c84-25be-11e8-93a5-0800277b1412
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 the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
# mysql -uroot -pabcd.1234
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.201
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 120
Relay_Log_File: relay-bin.000008
Relay_Log_Pos: 283
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: 120
Relay_Log_Space: 613
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: 1
Master_UUID: 6dc7ad57-25b9-11e8-9384-08002746e9d6
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 the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
# yum install -y libnl libnl-devel
# cd /usr/local/src
# tar zxvf keepalived-1.2.19.tar.gz
# cd keepalived-1.2.19
# ./configure --sysconf=/etc --with-kernel-dir=/usr/src/kernels/2.6.32-431.el6.x86_64
# make && make install
# ln -s /usr/local/sbin/keepalived /sbin/
# chkconfig --add keepalived
# chkconfig --level 35 keepalived on
# chkconfig --list |grep keepalived
keepalived 0:off 1:off 2:off 3:on 4:off 5:on 6:off
# vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.15
smtp_connect_timeout 30
router_id king01
}
vrrp_script check_mysql {
script "/usr/local/mysql/scripts/check_mysql.sh"
interval 2
}
vrrp_instance v01 {
state BACKUP
interface eth0
virtual_router_id 200
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.200
}
track_script {
check_mysql
}
}
# vi /usr/local/mysql/scripts/check_mysql.sh
#!/bin/bash
MYSQL=/usr/local/mysql/bin/mysql
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=abcd.1234
$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "show status;" >/dev/null 2>&1
if [ $? == 0 ]
then
echo " $host mysql login successfully "
exit 0
else
echo " $host mysql login faild"
exit 1
fi
# chmod a+x /usr/local/mysql/scripts/check_mysql.sh
# service keepalived start
Starting keepalived:
# service keepalived status
keepalived (pid3452) is running...
# tail -n 100 /var/log/messages
Feb 23 05:09:04 king01 Keepalived: Starting Keepalived v1.2.19 (02/23,2018)
Feb 23 05:09:04 king01 Keepalived: Starting Healthcheck child process, pid=11670
Feb 23 05:09:04 king01 Keepalived: Starting VRRP child process, pid=11671
Feb 23 05:09:04 king01 Keepalived_vrrp: Netlink reflector reports IP 192.168.1.201 added
Feb 23 05:09:04 king01 Keepalived_vrrp: Netlink reflector reports IP fe80::a00:27ff:fe2b:d617 added
Feb 23 05:09:04 king01 Keepalived_vrrp: Registering Kernel netlink reflector
Feb 23 05:09:04 king01 Keepalived_vrrp: Registering Kernel netlink command channel
Feb 23 05:09:04 king01 Keepalived_vrrp: Registering gratuitous ARP shared channel
Feb 23 05:09:04 king01 Keepalived_vrrp: Opening file '/etc/keepalived/keepalived.conf'.
Feb 23 05:09:04 king01 Keepalived_vrrp: Configuration is using : 69135 Bytes
Feb 23 05:09:04 king01 Keepalived_healthcheckers: Netlink reflector reports IP 192.168.1.201 added
Feb 23 05:09:04 king01 Keepalived_vrrp: Using LinkWatch kernel netlink reflector...
Feb 23 05:09:04 king01 Keepalived_vrrp: VRRP_Instance(v01) Entering BACKUP STATE
Feb 23 05:09:04 king01 Keepalived_healthcheckers: Netlink reflector reports IP fe80::a00:27ff:fe2b:d617 added
Feb 23 05:09:04 king01 Keepalived_healthcheckers: Registering Kernel netlink reflector
Feb 23 05:09:04 king01 Keepalived_healthcheckers: Registering Kernel netlink command channel
Feb 23 05:09:04 king01 Keepalived_healthcheckers: Opening file '/etc/keepalived/keepalived.conf'.
Feb 23 05:09:04 king01 Keepalived_healthcheckers: Configuration is using : 11739 Bytes
Feb 23 05:09:04 king01 Keepalived_healthcheckers: Using LinkWatch kernel netlink reflector...
Feb 23 05:09:04 king01 Keepalived_vrrp: VRRP sockpool:
Feb 23 05:09:04 king01 Keepalived_vrrp: VRRP_Script(check_mysql) succeeded
Feb 23 05:09:08 king01 Keepalived_vrrp: VRRP_Instance(v01) Transition to MASTER STATE
Feb 23 05:09:09 king01 Keepalived_vrrp: VRRP_Instance(v01) Entering MASTER STATE
Feb 23 05:09:09 king01 Keepalived_vrrp: VRRP_Instance(v01) setting protocol VIPs.
Feb 23 05:09:09 king01 Keepalived_vrrp: VRRP_Instance(v01) Sending gratuitous ARPs on eth0 for 192.168.1.200
Feb 23 05:09:09 king01 Keepalived_healthcheckers: Netlink reflector reports IP 192.168.1.200 added
Feb 23 05:09:14 king01 Keepalived_vrrp: VRRP_Instance(v01) Sending gratuitous ARPs on eth0 for 192.168.1.200
# ip addr
2: eth0:mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:2b:d6:17 brd ff:ff:ff:ff:ff:ff
inet 192.168.1.201/24 brd 192.168.1.255 scope global eth0
inet 192.168.1.200/32 scope global eth0
inet6 fe80::a00:27ff:fe2b:d617/64 scope link
valid_lft forever preferred_lft forever
页:
[1]