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

The following content mainly brings you mysql dual-master configuration handouts, the knowledge here is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

End 2

1. Database architecture diagram:

two。 Installation environment information:

Master1 172.16.90.13 CentOS 7.2 Keepalived read VIP:172.16.90.15

Master2 172.16.90.14 CentOS 7.2 Keepalived read VIP:172.16.90.16

3.MySQL dual master configuration

Master1 modifies my.cnf and adds the following configuration:

Server-id=13log-bin=mysql-binsync-binlog=1binlog-checksum=nonebinlog-format=mixedauto-increment-increment=2auto-increment-offset=1log-slave-updatesslave-skip-errors=all

Master2 modifies my.cnf and adds the following configuration:

Server-id=14log-bin=mysql-binsync-binlog=1binlog-checksum=nonebinlog-format=mixedauto-increment-increment=2auto-increment-offset=1log-slave-updatesslave-skip-errors=all

Authorize mysql from the library account in master1:

Grant replication slave on. To 'sync'@'%' identified by' syncpwd'

Flush privileges

Show master status; # current status of the main library, i.e. master1

Show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000004 | 599 | |

+-+

Authorize mysql from the library account in master2:

Grant replication slave on. To 'sync'@'%' identified by' syncpwd'

Flush privileges

Show master status; # current status of the main library, i.e. master2

Show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000002 | 468 |

+-+

Specify master2 as the primary library in maste1:

Stop slave

Change master to master_host='172.16.90.14',master_user='sync',master_password='syncpwd',master_log_file='mysql-bin.000002',master_log_pos=468

Flush privileges

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE

Start slave

Specify master1 as the primary library in maste2:

Stop slave

Change master to master_host='172.16.90.13',master_user='sync',master_password='syncpwd',master_log_file='mysql-bin.000004',master_log_pos=599

Flush privileges

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE

Start slave

The dual master configuration of MySQL is completed. Verify that the configuration is successful:

Information displayed in show slave status\ G # master1

1. Row

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.90.13

Master_User: sync

Master_Port: 3306

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Information displayed in show slave status\ G # master2

1. Row

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.90.14

Master_User: sync

Master_Port: 3306

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

4.Keepalived highly available configuration

Installation: yum install-y keepalived

Launch: systemctl stop keepalived

Description:

When both CVMs are normal

The user writes data to server A by default, and accesses server B if there is any exception in A.

The user reads the data and accesses server B by default, and server An if there is any exception in server B.

The initial weight of the write data of server An is 100 and the weight of B is 90.

The initial weight of the read data of server An is 90, and the weight of B is 100.

When the detection process detects an exception, it will reduce the weight of the machine by 20%.

Server A

Vrrp_script chk_master1 {script "/ opt/context/keepalive_check/chk_mysql.sh" interval 2 weight-20} vrrp_instance VI_MASTER1 {state MASTER interface eno16780032 virtual_router_id 51 priority 100 mcast_src_ip 172.16.90.13 advert_int 1 authentication {auth_type PASS auth_pass 5678} virtual_ipaddress {172.16.90. 15} track_script {chk_master1}} vrrp_instance VI_MASTER2 {state BACKUP interface eno16780032 virtual_router_id 52 priority 90 mcast_src_ip 172.16.90.13 advert_int 1 authentication {auth_type PASS auth_pass 15678} virtual_ipaddress {172.16.90.16}}

Server B

Vrrp_script chk_master2 {script "/ opt/context/keepalive_check/chk_mysql.sh" interval 2 weight-20} vrrp_instance VI_MASTER1 {state BACKUP interface eno16780032 virtual_router_id 51 priority 90 mcast_src_ip 172.16.90.14 advert_int 1 authentication {auth_type PASS auth_pass 5678} virtual_ipaddress {172.16.90.15} vrrp _ instance VI_MASTER2 {state MASTER interface eno16780032 virtual_router_id 52 priority 100 mcast_src_ip 172.16.90.14 advert_int 1 authentication {auth_type PASS auth_pass 15678} virtual_ipaddress {172.16.90.16} track_script {chk_master2}}

Test script

#! / bin/bashcounter=$ (netstat-na | grep "LISTEN" | grep "3306" | wc-l) if ["${counter}"-eq 0]; then systemctl stop keepalived

Fi

For the above handouts on the dual-master configuration of mysql, if you have more information, you can continue to pay attention to the innovation of our industry. If you need professional solutions, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

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.