Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to understand MySQL master replication based on keepalived. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

System environment: Linux red hat 6.3

Mysql version: mysql 5.6.34

Mater1 node ip:192.168.140.222 hostname: red1

Mater2 node ip:192.168.140.224 hostname: red2

First, install the mysql database on two nodes.

II. 1 Node modifies / etc/my.cnf parameters

Server_id = 1 / / flag, which cannot be repeated between nodes

Log-bin=/var/lib/mysql/xbinlog/binlog / / Open binlog and save it in location / var/lib/mysql/xbinlog/

The mode of binlog-format=ROW / / binlog is row mode

Log_slave_updates=1 / / when the log-slave-updates parameter is enabled, the data copied from the library from the master library will be written to the log-bin log file

Sync_binlog=1 / / after every transaction commit, MySQL will issue a disk synchronization instruction such as fsync to force the data in binlog_cache to disk.

Innodb_flush_log_at_trx_commit=1 / / means that every transaction commit or instruction outside a transaction needs to be written to (flush) the hard disk.

Slave-parallel-workers=2 / / set 2 SQL threads from the server; 0 means to turn off multithreaded replication

Replicate-do-db=replicate / / synchronized database is replicate

And give mysql:mysql permission to the / var/lib/mysql/xbinlog/ directory, otherwise chown mysql:mysql / var/lib/mysql/xbinlog/ will not work.

Restart the mysql service

2 Node modify / etc/my.cnf parameter

Server_id = 2 / / this parameter should ensure that the two nodes are inconsistent.

Log_bin=/opt/mysql/data/ybinlog/binlog

Binlog-format=ROW

Log_slave_updates=1

Sync_binlog=1

Innodb_flush_log_at_trx_commit=1

Slave-parallel-workers=2

Replicate-do-db=replicate

And give mysql:mysql permission to the / var/lib/mysql/ybinlog/ directory, otherwise chown mysql:mysql / var/lib/mysql/ybinlog/ will not work.

Restart the mysql service

3. At Node 1:

Mysql > grant replication slave on *. * to 'replicate'@'192.168.140.224' identified by' replicate'

Query OK, 0 rows affected (0.00 sec)

Mysql > flush tables with read lock

Query OK, 0 rows affected (0.00 sec)

Mysql > show master status

+-+

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

+-+

| | binlog.000002 | 337 |

+-+

1 row in set (0.00 sec)

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

At 2 nodes

Mysql > change master to master_host='192.168.140.222',master_user='replicate',master_password='replicate',master_log_file='binlog.000002',master_log_pos=337

Query OK, 0 rows affected, 2 warnings (0.00 sec)

Mysql > start slave

Query OK, 0 rows affected, 1 warning (0.01 sec)

Mysql > show slave status\ G / / check whether Slave_IO_Running and Slave_SQL_Running are yes status

Mysql > show master status

+-+

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

+-+

| | binlog.000001 | 120 | |

+-+

At Node 1

Mysql > change master to master_host='192.168.140.224',master_user='replicate', master_password='replicate',master_log_file='binlog.000001',master_log_pos=120

Query OK, 0 rows affected, 2 warnings (0.01 sec)

Mysql > start slave

Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql > show slave status\ G / / check whether Slave_IO_Running and Slave_SQL_Running are yes status

Fourth, test, insert data in both nodes, and then verify it by select

Download keepalived

Http://www.keepalived.org/software/keepalived-1.2.13.tar.gz

Decompress: tar xvf keepalived-1.2.13.tar.gz

Cd keepalived-1.2.13

Initialize:. / configure-- disable-fwmark

Compile and install: make & & make install

Configuration prerequisites for keepalived

1. The hostname of this machine is the same as the host defined in hosts.

2. Each node should be able to resolve the host name to each other, that is to say, the local ip and the corresponding host name should be included in the hosts file.

3. The time of each node must be synchronized

Turn off selinux and clear the firewall rules

[root@red1 ~] # setenforce 0

[root@red1] # iptables-F

Configure keepalived

Cp / usr/local/etc/sysconfig/keepalived / etc/sysconfig/

Cp / usr/local/sbin/keepalived / usr/bin/

Cp / software/keepalived/keepalived-1.2.13/keepalived/etc/init.d/keepalived.init / etc/init.d

Mkdir-p / etc/keepalived

Cp / software/keepalived/keepalived-1.2.13/keepalived/etc/keepalived/keepalived.conf / etc/keepalived/

Configuration on Node 1

Vim / etc/keepalived/keepalived.conf

! Configuration File for keepalived

Global_defs {/ / Global configuration segment

Notification_email {/ / define fault notification mailbox

Root@localhost / / email address, which can be multiple

}

Notification_email_from root@localhost / / Sender address

Smtp_server localhost / / Mail server address

Smtp_connect_timeout 30 / / timeout for contacting the mail server

Router_id red1 / / the unique ID identity of the current physical device in the network

}

Vrrp_instance VI_1 {/ / instance name cannot be duplicated in the configuration file

State MASTER / / MASTER or BACKUP, which defines whether the current node is a master or slave node by default

Interface eth0

Virtual_router_id 51 / / Identifier for the current virtual route, which must be globally unique

Priority 100 / / defines the priority of the current instance. The standby is smaller than the host.

Advert_int 1 / / status notification period (seconds)-used by the master node to advertise heartbeat information to the BACKUP node

Authentication {/ message authentication configuration

Auth_type PASS / / authentication method (MD5 or PASS simple character authentication)

Auth_pass 1111 / / Authentication password, all nodes of the same virtual instance must be consistent

}

Virtual_ipaddress {/ / A (vip) virtual IP address that is automatically configured on interface, which can be multiple

192.168.140.66 / / Virtual ip

}

}

Virtual_server 192.168.140.66 {/ / Virtual ip

Interval between health status probes for delay_loop 6 / / RealServer

Lb_algo rr / / scheduling algorithm for load balancing (Scheduler)

Lb_kind DR / / LVS type. Support for NAT/DR/TUN

Persistence_timeout 50 / / persistent connection timeout

Protocol TCP / / can only be applied to TCP. If UDP is applicable, ops parameter is used.

Real_server 192.168.140.222 {/ / 1 node ip

Weight 1 weight

TCP_CHECK {

Connect_timeout 3 Health status Monitoring timeout

Nb_get_retry 3 number of get retry

Delay_before_retry 3 delays 3 seconds before each retry

# connect_port 3306

}

}

}

Configuration on Node 2

! Configuration File for keepalived

Global_defs {

Notification_email {

Root@localhost

}

Notification_email_from root@localhost

Smtp_server localhost

Smtp_connect_timeout 30

Router_id red2

}

Vrrp_instance VI_1 {

State BACKUP

Interface eth0

Virtual_router_id 51

Priority 90

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

192.168.140.66

}

}

Virtual_server 192.168.140.66 {

Delay_loop 6

Lb_algo rr

Lb_kind DR

Persistence_timeout 50

Protocol TCP

Real_server 192.168.140.224 {

Weight 1

TCP_CHECK {

Connect_timeout 3

Nb_get_retry 3

Delay_before_retry 3

# connect_port 3306

}

}

}

Enable the service service keepalived start

View status service keepalived status

Check to see if the VIP is on this node ip add / / VIP:192.168.140.66 will be on node 1, because node 1 has a high priority.

6. Test keepalived

When you execute ip add on 1 and 2 nodes, you will find that VIP 192.168.140.66 floats on the 1 node.

Then execute service keepalived stop on the 1 node, and then ip add on the 2 node, and you will find that vip floats to the 2 node.

Then at the 1-node service keepalived start, you will find that the vip is back to the 1-node.

At this point, the test was successful

On how to understand the keepalived-based MySQL master replication is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.