Keepalived+mysql Mutual primary from highly available installation configuration

Keepalived+mysql Mutual primary from highly available installation configuration
Environment Introduction:
keepalived_vip=192.168.1.210 (write virtual IP)
Mysql_master01 eth0:192.168.1.211 eth1:172.20.27.211 (1-core 1G)
Mysql_master02 eth0:192.168.1.212 eth1:172.20.27.212 (1-core 1G)

1. Install MySQL database (all nodes installed)
Install MySQL service omitted here
2. Edit the MY.CNF configuration file
2.1 Modifying the Mysql_master01 configuration file

Vim/usr/local/lnmp/mysql/etc/my.cnf

server_id = 211

2.2 Modifying the Mysql_master02 configuration file

Vim/usr/local/lnmp/mysql/etc/my.cnf

server_id = 212

3. Build each other mainly from
3.1 Operating on Mysql_master01
3.1.1 Establish master-slave sync permissions for Mysql_master02

Mysql-u root-p123456//Enter database grant replication slave on * * to ' backup ' @ ' 192.168.1.212 ' identified by ' backup '; Set permissions flush privileges; Refresh Permissions Table

3.1.2 Copy data to Mysql_master02 node

Mysql-u root-p123456//Enter database flush tables with read lock; Lock table, ready to back up data mysql> show Master STATUS\G//View master node information *************************** 1. Row *************************** file:mysql-bin.000005 position:1231 Binlog_do_db:binlog_ignore _db:executed_gtid_set:1 row in Set (0.00 sec) mysqldump-u root-p123456 Liu >liu.sql//backup Liu This database mysql-u root-p1234 56//Enter database unlock tables; Unlock SCP liu.sql [email protected]:/root///Copy data

3.2 Operating on Mysql_master02
3.2.1 Recovering liu.sql to a database

Mysql-u root-p123456//Enter the database to create databases Liu; Create a restored database name use Liu; Enter database Source/root/liu.sql//Recover Data

3.2.2 Establishing master-Slave synchronization relationship

Mysql-u root-p123456//Enter database change master to master_host= ' 192.168.1.211 ', master_user= ' backup ', master_password= ' Backup ', master_log_file= ' mysql-bin.000005 ', master_log_pos=1231; Establish master-Slave synchronization relationship start slave; Start synchronization from the Library show slave status\g//Check master-Slave synchronization, if you see slave_io_running and slave_sql_running are yes, then the master-slave replication connection is normal.

3.2.3 Establish master-slave sync permissions for Mysql_master01

Mysql-u root-p123456//Enter database grant replication slave on * * to ' backup ' @ ' 192.168.1.211 ' identified by ' backup '; Set permissions flush privileges; Refresh Permissions Table mysql> Show Master status\g//View master node information *************************** 1. Row *************************** file:mysql-bin.000004 position:1242 Binlog_do_db:binlog_ignore _db:executed_gtid_set:1 row in Set (0.00 sec)

3.3 Operating on Mysql_master01
3.3.1 Establishing master-Slave synchronization relationship

Mysql-u root-p123456//Enter database change master to master_host= ' 192.168.1.212 ', master_user= ' backup ', master_password= ' Backup ', master_log_file= ' mysql-bin.000004 ', master_log_pos=1242; Establish master-Slave synchronization relationship start slave; Start synchronization from the Library show slave status\g//Check master-Slave synchronization, if you see slave_io_running and slave_sql_running are yes, then the master-slave replication connection is normal.

4. Install keepalived (all nodes are installed)
4.1 Installation Configuration keepalived

wget Http://www.keepalived.org/software/keepalived-1.2.15.tar.gztar ZXVF KEEPALIVED-1.2.15.TAR.GZCD Keepalived-1.2.15./configure--prefix=/usr/local/keepalivedmake && make install

4.2 Configuring keepalived as a system service

Cp/usr/local/keepalived/etc/rc.d/init.d/keepalived/etc/init.d/cp/usr/local/keepalived/etc/sysconfig/keepalived /ETC/SYSCONFIG/MKDIR/ETC/KEEPALIVED/CP/USR/LOCAL/KEEPALIVED/ETC/KEEPALIVED/KEEPALIVED.CONF/ETC/KEEPALIVED/CP/ usr/local/keepalived/sbin/keepalived/usr/sbin/

4.3 Modifying the keepalived configuration file
4.3.1 Modifying the keepalived configuration file for Mysql_master01

Echo ' >/etc/keepalived/keepalived.conf//empty config file, write it yourself vim/etc/keepalived/keepalived.conf

! configuration file for keepalived   global_defs {         notification_email {        [email  protected]        }       notification_email_from [email protected]       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         #优先级, the other one to 90   advert_int 1    nopreempt           #不抢占, only set on high priority machines, low priority machines do not set    authentication {        auth_type pass       auth_ pass 1111   }   virtual_ipaddress {        192.168.1.210   }}virtual_server 192.168.1.210 3306 {    delay_loop 6   lb_algo wrr   lb_kind DR    persistence_timeout 50         #会话保持时间    protocol  TCPreal_server 192.168.1.211 3306 {       weight  3       notify_down /usr/local/lnmp/mysql/bin/killkeepalived.sh      #检测到mysql服务挂了就执行这个脚本        TCP_CHECK {        connect_timeout 10         #连接超时时间         nb_get_retry 3            # Number of re-connection        delay_before_retry 3      # Re-connect interval time        connect_port 3306           #健康检查端口         }        }}

4.3.2 Modifying the keepalived configuration file for Mysql_master02

Echo ' >/etc/keepalived/keepalived.conf//empty config file, write it yourself vim/etc/keepalived/keepalived.conf

! configuration file for keepalived   global_defs {         notification_email {        [email  protected]        }       notification_email_from [email protected]       smtp_server  127.0.0.1       smtp_connect_timeout 30        router_id MySQL-ha       }  vrrp_instance  vi_1 {   state backup     interface eth0    virtual_router_id 51   priority 90    advert_int 1    nopreempt   authentication {        auth_type pass       auth_pass 1111   }   virtual_ipaddress {        192.168.1.210   }}virtual_server 192.168.1.210  3306 {   delay_loop 6   lb_algo wrr   lb_ kind dr   persistence_timeout 50   protocol tcpreal_server  192.168.1.212 3306 {       weight 3        notify_down /usr/local/lnmp/mysql/bin/killkeepalived.sh        TCP_CHECK {       connect_timeout 10        nb_get_retry 3       delay_before_retry  3       connect_port 3306         }       }} 

4.4 Writing killkeepalived.sh (all node installations)

vim/usr/local/lnmp/mysql/bin/killkeepalived.sh

#!/bin/sh Pkill keepalived

chmod +x/usr/local/lnmp/mysql/bin/killkeepalived.sh

4.5 Start keepalived (all node installations)

Service keepalived restart Echo ' service keepalived start ' >>/etc/rc.local

5. Test Database High Availability
5.1 Establishing permissions for test users

Grant all privileges on * * to ' liuzhuang ' @ ' percent ' identified by ' 123 '; flush privileges; Refresh Permissions Table

5.2 Analog fault, see whether to shut down a MySQL service, VIP will automatically switch, if it will, stating that the environment is normal.

This article is from the "Chengdu @ Ah-like" blog, please be sure to keep this source http://azhuang.blog.51cto.com/9176790/1636594

Keepalived+mysql Mutual primary from highly available installation configuration