Shulou(Shulou.com)06/01 Report--

Next, let's learn how to configure MySQL database with Keepalived dual-master. I believe you will benefit a lot after reading it. The text is not much in essence. I hope that MySQL database how to configure Keepalived dual-master is what you want.

1. Environment setting

10.0.0.132 master110.0.0.134 master2setenforce 0systemctl stop firewalld

2. Configure my.cnf configuration file

On master1

Vim / etc/my.cnf [client] socket=/usr/local/mysql/mysql.sock [mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/data user=mysql pid-file=/usr/local/mysql/data/mysqld.pid log-error=/usr/local/mysql/data/mysql.err socket=/usr/local/mysql/mysql.sock port=3306 Server_id=1 log-bin=mysql-bin gtid-mode=on enforce-gtid-consistency=1 log_slave_updates = 1 binlog-format=mixed auto-increment-increment=2 auto-increment-offset=1systemctl restart mysqld

On master2

Vim / etc/my.cnf [client] socket=/usr/local/mysql/mysql.sock [mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/data user=mysql pid-file=/usr/local/mysql/data/mysqld.pid log-error=/usr/local/mysql/data/mysql.err socket=/usr/local/mysql/mysql.sock port=3306 server_id=2 Log-bin=mysql-bin gtid-mode=on enforce-gtid-consistency=1 log_slave_updates = 1 binlog-format=mixed auto-increment-increment=2 auto-increment-offset=2systemctl restart mysqld

3. Authorization allows synchronization

On master1

Mysql-uroot-pmysql > grant replication slave on *. * to rep@'10.0.0.%' identified by '123'

On master2

Mysql-uroot-pmysql > grant replication slave on *. * to rep@'10.0.0.%' identified by '123'

4. Enable slave synchronization

On master1

Mysql > change master to master_host='10.0.0.134',master_user='rep',master_password='123',master_port=3306,master_autoter_auto_position=1;mysql > start slave

On master2

Mysql > change master to master_host='10.0.0.132',master_user='rep',master_password='123',master_port=3306,master_autoter_auto_position=1;mysql > start slave

5. Verify that each other is master and subordinate.

On master1

Mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.134 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: master1-relay-bin.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000001 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: 154 Relay_Log_Space: 576 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: 0Master_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: e59d0925 -be6a-11e8-9cab-000c29b63bad Master_Info_File: / usr/local/mysql/data/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: Executed_Gtid_Set: Auto_Position: 1 Replicate_Rewrite _ DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

On master2

Mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.132 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: master2-relay-bin.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000001 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: 154 Relay_Log_Space: 576 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: 0Master_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: ceb0ca3d -8366-11e8-ad2b-000c298b7c9a Master_Info_File: / usr/local/mysql/data/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: Executed_Gtid_Set: Auto_Position: 1 Replicate_Rewrite _ DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

6. Install keepalived

On master1

Yum-y install keepalived

On master2

Yum-y install keepalived

7. Configure keepalived

On master1

Vim / etc/keepalived/keepalived.conf

Global_defs {router_id master1} vrrp_instance VI_1 {state BACKUP interface ens33 virtual_router_id 51 priority 100 advert_int 1 nopreempt authentication {auth_type PASS auth_pass 1111} virtual_ipaddress {10.0.0.100}} virtual_server 192.168.1.100 3306 {delay_loop 6 lb_algo rr lb_kind DR persistence_ Timeout 50 protocol TCP real_server 10.0.0.132 3306 {weight 1 notify_down / etc/keepalived/bin/mysql.sh TCP_CHECK {connect_timeout 3 retry 3 delay_before_retry 3 connect_port 3306} systemctl enable keepalivedsystemctl start keepalivedmkdir / etc/keepalived/bin

Vim / etc/keepalived/bin/mysql.sh

#! / bin/bashpkill keepalived/sbin/ifdown ens33 & & / sbin/ifup ens33chmod + x / etc/keepalived/bin/mysql.sh

On master2

Vim / etc/keepalived/keepalived.conf

Global_defs {router_id master2} vrrp_instance VI_1 {state BACKUP interface ens33 virtual_router_id 51 priority 50 advert_int 1 nopreempt authentication {auth_type PASS auth_pass 1111} virtual_ipaddress {10.0.0.100}} virtual_server 192.168.1.100 3306 {delay_loop 6 lb_algo rr lb_kind DR persistence_ Timeout 50 protocol TCP real_server 10.0.0.134 3306 {weight 1 notify_down / etc/keepalived/bin/mysql.sh TCP_CHECK {connect_timeout 3 retry 3 delay_before_retry 3 connect_port 3306} systemctl enable keepalivedsystemctl start keepalivedmkdir / etc/keepalived/bin

Vim / etc/keepalived/bin/mysql.sh

#! / bin/bashpkill keepalived/sbin/ifdown ens33 & & / sbin/ifup ens33chmod + x / etc/keepalived/bin/mysql.sh

8. Final verification (keepalived double master)

Verification one

On master1

Ip addr show ens33 can see that we set up vip

On master2

Ip addr show ens33 has no vip.

Verification two

Authorization on two databases allows remote users to log in remotely

On master1

Mysql-uroot-pmysql > grant all on *. * to remote@'%' identified by '123'

On master2

Mysql-uroot-pmysql > grant all on *. * to remote@'%' identified by '123'

Find another MySQL database to remotely access the database cluster using vip

Check server_id, which shows 1, so we are currently logged in to master1. It also proves that master1 is now in active state, while master2 is in backup status.

Let's try to create a database.

Go back to master1.

Check it on master2 again.

This shows that there is no problem with our master-slave replication.

Verification 3: verify the availability of keepalived dual-host cluster

First stop the mysqld service on master1

Systemctl stop mysqld

Check master1's ens33 network card again. Vip has disappeared.

Check the ens33 network card of master2 and vip appears.

If you log in remotely through vip again, you can still log in, and you can see that server_id becomes 2.

After reading this article on how to configure Keepalived dual hosts in MySQL database, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.