MySQL - Detailed explanation of dual-machine HA based on Keepalived (picture and text)

1. Environment description:

OS: CentOS6.5_X64
MASTER: 192.168.0.202
BACKUP: 192.168.0.203
VIP: 192.168.0.204

2. Configure two MysqlMaster-Master Synchronization

Regarding the installation of MySQL, you can also refer to "MySQL - CentOS6.5 Compile and Install MySQL5.6.16", the main Master synchronization configures the slave server as the master of the previous master server based on master-slave synchronization. It is equivalent to setting the original slave as the master of the original master based on the original master-slave synchronization. You can also refer to "MySQL - —MS master-slave replication (read-write separation) implementation》 , after setting A as B's Master and B as A's Slave, then set B as A's Master and A as B's Slave.

[root@masterr ~]# yum install mysql-server mysql -y
[root@masterr ~]# service mysqld start
[root@masterr ~]# mysqladmin -u root proot
[root@masterr ~]# vi /etc/my.cnf  #开启二进制日志，设置id
[mysqld]
server-id = 1                    #backup这台设置2
log-bin = mysql-bin
binlog-ignore-db = mysql,information_schema       #忽略写入binlog日志的库
auto-increment-increment = 2             #字段变化增量值
auto-increment-offset = 1              #初始字段ID为1
slave-skip-errors = all                       #忽略所有复制产生的错误     
[root@masterr ~]# service mysqld restart

#First check the log bin log and pos value location

##The master configuration is as follows:

[root@ master ~]# mysql -u root -proot
mysql> GRANT  REPLICATION SLAVE ON *.* TO 'replication'@'192.168.0.%' IDENTIFIED  BY 'replication';
mysql> flush  privileges;
mysql> change  master to
    ->  master_host='192.168.0.203',
    ->  master_user='replication',
    ->  master_password='replication',
    ->  master_log_file='mysql-bin.000002',
    ->  master_log_pos=106;  #对端状态显示的值
mysql> start  slave;         #启动同步

The backup configuration is as follows ：

[root@backup ~]#  mysql -u root -proot
mysql> GRANT  REPLICATION SLAVE ON *.* TO 'replication'@'192.168.0.%' IDENTIFIED  BY 'replication';
mysql> flush  privileges;
mysql> change  master to
    ->  master_host='192.168.0.202',
    ->  master_user='replication',
    ->  master_password='replication',
    ->  master_log_file='mysql-bin.000002',
    ->  master_log_pos=106;
mysql> start  slave;

#The master-master synchronization configuration is completed. Check the synchronization status Slave_IO and Slave_SQL. If it is YES, it means the master-master synchronization is successful.

Insert data in master

Under test:

In Backup to check whether the synchronization is successful:

You can see that the synchronization has been successful, and the same data is inserted into the user table in the backup, and the same synchronization is done, dual master It was done successfully.

3. Configure keepalived to achieve hot backup

[root@backup ~]# yum install -y pcre-devel openssl-devel popt-devel #安装依赖包

[root@masterr ~]# wget http://www.php.cn/
[root@masterr ~]# tar zxvf keepalived-1.2.7.tar.gz
[root@masterr ~]# cd keepalived-1.2.7
[root@masterr ~]#./configure --prefix=/usr/local/keepalived
make

#Configure keepalived as a system service

[root@masterr ~]# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
[root@masterr ~]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@masterr ~]# mkdir /etc/keepalived/
[root@masterr ~]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
[root@masterr ~]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

[root@masterr ~]# vi /etc/keepalived/keepalived.conf
! Configuration File forkeepalived
global_defs {
notification_email {
test@sina.com
 }
notification_email_from  admin@test.com
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           #优先级，backup设置90
 advert_int 1
 nopreempt             #不主动抢占资源，只在master这台优先级高的设置，backup不设置
 authentication {
 auth_type PASS
 auth_pass 1111
 }
 virtual_ipaddress {
 192.168.0.204
 }
}
virtual_server 192.168.0.204 3306 {
 delay_loop 2
 #lb_algo rr              #LVS算法，用不到，我们就关闭了
 #lb_kind DR              #LVS模式，如果不关闭，备用服务器不能通过VIP连接主MySQL
 persistence_timeout 50  #同一IP的连接60秒内被分配到同一台真实服务器
 protocol TCP
 real_server 192.168.0.202 3306 {   #检测本地mysql，backup也要写检测本地mysql
 weight 3
 notify_down /usr/local/keepalived/mysql.sh    #当mysq服down时，执行此脚本，杀死keepalived实现切换
 TCP_CHECK {
 connect_timeout 3    #连接超时
 nb_get_retry 3       #重试次数
 delay_before_retry 3 #重试间隔时间
  }
}

[root@masterr ~]# vi /usr/local/keepalived/mysql.sh
#!/bin/bash
pkill keepalived
[root@masterr ~]# chmod +x /usr/local/keepalived/mysql.sh
[root@masterr ~]# /etc/init.d/keepalived start

#The backup server only changes the priority to 90. Nopreempt is not set, real_server sets the local IP.
#Authorize two Mysql servers to allow root remote login for testing on other servers!

mysql> grant all on *.* to'root'@'192.168.0.%' identified by 'root';
mysql> flush privileges;

4. Test high availability

1) Connect through VIP through Mysql client to see if the connection is successful.

2) Stop the master mysql service and check whether it can switch to it normally. You can use the ip addr command to check which server the VIP is on.
3) You can see the master/backup switching process by checking the /var/log/messges log
4) After the master server recovers from a fault, whether it actively seizes resources and becomes an active server.

Note: The order of service startup: start MySQL first, then Keepalived.

The above is the detailed content of MySQL - Detailed explanation of dual-machine HA based on Keepalived (picture and text). For more information, please follow other related articles on the PHP Chinese website!