MySQL two-way master plus keepalived high availability

Recently in the MySQL database of two-way master and slave, learned that keepalived can automatically determine and switch to the available database, try it yourself, to sort out the document.

Just declare the environment.

Iptables turn on port 3306 or turn off SELinux.

mysql-01:192.168.204.138

mysql-02:192.168.204.139

vip:192.168.204.200 #web服务器连接的ip, you can use the tool to connect to try.

MySQL installation process is skipped, according to personal circumstances to install themselves.

1. Modify the database configuration file/etc/my.cnf:

1.1 Modify the MYSQL-01 database file and add the following under [MySQL]

server_id = 1log_bin = Mysql-bin

1.2 Modify the MYSQL-02 database file and add the following under [MySQL]

server_id = 2log_bin = Mysql-bin

2. Build one-way master-Slave

2.1 On the MySQL-01

2.1.1 Operation authorization

Mysql-u root-p #输入密码mysql > Grant replication Slave on * * to ' slave ' @ ' 192.168.204.139 ' identified by ' 1234 ";mysql> flush privileges;

2.1.2 Data passed to MySQL-02

mysql -u root -p            # Enter Password flush tables with read lock;         #锁表操作mysql > show master status;+------------------+----------+--------------+------------------+------ -------------+| file             |  position | binlog_do_db | binlog_ignore_db | executed_gtid_set |+---------- --------+----------+--------------+------------------+-------------------+| mysql-bin.000012 |       120 |               |                   |                    |+------------------+----------+--------------+------------------+-------------------+1 row in  set  (0.00 sec) mysqldump -u root -p --all-databases >  mysqldump.sqlmysql -u root -pmysql> unlock tables;mysql> quit     scp myqsldump.sql 192.168.204.139:/root/

2.2 Operating on the MySQL-02

2.2.1 Recovering database Data

Mysql-u Root-p < Mysqldump.sql

2.2.2 Establish master-Slave synchronization

Mysql-u root-pmysql> Change Master to master_host= ' 192.168.204.138 ', master_user= ' slave ', master_password= ' 123456 ' , master_log_file= ' mysql-bin.000012 ', Master_log_pos=120,master_port=3306;start slave;

Check if successful

show slave status\g;

Slave_IO_Running:YesSlave_SQL_Running:Yes

Yes indicates that the synchronization was successful.

Note:master_port=3306 is not required by default, but it is required if the port has been modified

3. Build each other mainly from

3.1 Operating on the MySQl-01

3.1.1 User Authorization

Mysql-u root-p123456mysql> Grant Replication Slave on * * to ' slave ' @ ' 192.168.204.138 ' identified by ' 123456 '; Establish permissions mysql> flush privileges;

Note: because the master and slave of all the libraries are being done, the operation on the MySQL-01 will be synchronized to the MySQL-02

         

mysql -u root -pmysql> show master status\g;+------------------+----------+-- ------------+------------------+-------------------+| file              | position | binlog_do_db | binlog_ignore_db  | executed_gtid_set |+------------------+----------+--------------+------------------+-------- -----------+| mysql-bin.000009 |      120 |               |                   |                    |+------------------+----------+------ --------+------------------+-------------------+1 row in set  (0.00 sec)

3.1.2 Operating authorization on MySQL-01

mysql> Change Master to master_host= ' 192.168.204.138 ', master_user= ' slave ', master_password= ' 123456 ', Master_log_ File= ' mysql-bin.000009 ',master_log_pos=120;mysql> start slave;

Check if successful

show slave status\g;

Slave_IO_Running:YesSlave_SQL_Running:Yes

indicates success.

This, MySQL's bidirectional master-slave synchronization has been completed

4.keepalived Build-up

4.1 Perform the following operations on both servers in turn

A. Installing the 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

If there is an error in the compilation process, please install Gcc,openssl,openssl-devel

B. Copying related files

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.2 Modifying a configuration file

Please clear the/etc/keepalived/keepalived.conf file first

4.2.1 operation on MySQL-01

Copy the content in.

! 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 master           interface eth0    virtual_router_id 51     priority 100           advert_int 1     nopreempt                      authentication {        auth_type pass         auth_pass 1111    }     virtual_ipaddress {        192.168.204.222     }}virtual_server 192.168.204.222 3306 {    delay_loop 6     lb_algo wrr    lb_kind dr    persistence_ timeout 50            protocol tcp     real_server 192.168.204.138 3306 {         weight 3        notify_down /var/lib/mysql/ killkeepalived.sh          tcp_check {             connect_timeout 10                     nb_get_retry 3                        delay_ before_retry 3                   connect_port 3306                 }    }}

4.2.2 operation on MySQL-02

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 100           advert_int 1     nopreempt                      authentication {        auth_type pass         auth_pass 1111    }     virtual_ipaddress {        192.168.204.222     }}virtual_server 192.168.204.222 3306 {    delay_loop 6     lb_algo wrr    lb_kind dr    persistence_ timeout 50            protocol tcp     real_server 192.168.204.139 3306 {         weight 3        notify_down /var/lib/mysql/ killkeepalived.sh          tcp_check {             connect_timeout 10                     nb_get_retry 3                        delay_ before_retry 3                   connect_port 3306                 }    }}

4.3 Perform the following operations on both servers

vim/var/lib/mysql/killkeepalived.sh #!/bin/sh pkill keepalived chmod +x/var/lib/mysql/killkeepalived.sh

4.4 Starting the keepalived service

Service keepalived Restart

5. Testing is available

Create a new user      

Mysql> Grant all privileges on * * to ' test ' @ ' percent ' identified by ' 123456 ';mysql> flush privileges;

Use the tool to test, select Navacat, the IP address to fill in VIP addresses, connection available

At this point, the configuration is complete

MySQL two-way master plus keepalived high availability