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

Environment

OS: centos7

Mysql version: mysql 5.7

Keepalived: keepalived-1.2.20

Mysql-vip:192.168.41.100

Mysql-master1:192.168.41.10

Mysql-master2:192.168.41.11

The lab environment turns off the firewall rule firewall-cmd

1. Configure two mysql to each other as master and slave

The first part of the process is that master records binary logs. Before each transaction updates the data, master sets the

The two logs record these changes. MySQL writes transactions to the binary log. After the event is written to the binary log, master

Notifies the storage engine to commit the transaction.

The next step is for slave to copy master's binary log to its own relay log. First, slave starts a job.

As a thread-- Ithumb O thread. The iUnip O thread opens a normal connection on master and then starts binlog dump process.

Binlog dump process reads events from master's binary log and sleeps if master has been synchronized

Sleep and wait for master to generate new events. The Icano thread writes these events to the relay log.

SQL slave thread (SQL from the thread) handles the last step of the process. The SQL thread reads events from the relay log, and

Replay the events in it and update the data in slave to make it consistent with the data in master. As long as the thread is associated with the Ithumb O thread

To be consistent, the relay log is usually located in the OS cache, so the overhead of the relay log is small. Master-master synchronization is a relationship in which two machines are dominant to each other, and writes on any machine will be synchronized.

1. Modify mysql configuration file

The binlog log feature must be enabled for both mysql, and the server-id of the two cannot be the same:

The master1 configuration is as follows:

[mysqld]

Log-bin = mysql-bin

Binlog_format = mixed

Server-id = 1

Relay-log = relay-bin

Relay-log-index = slave-relay-bin.index

Auto-increment-increment = 2

Auto-increment-offset = 1

Restart the mysqld service

Systemctl restart mysqld

The master2 configuration is as follows:

Log-bin = mysql-bin

Binlog_format = mixed

Server-id = 2

Relay-log = relay-bin

Relay-log-index = slave-relay-bin.index

Auto-increment-increment = 2

Auto-increment-offset = 2

Restart the mysqld service

Systemctl restart mysqld

Note: the only difference between master1 and master2 is server-id and auto-increment-offset.

There are self-growing fields in mysql. You need to set two configurations related to self-growing when you synchronize the master and master of the database:

Auto_increment_offset and auto_increment_increment.

Auto-increment-increment represents the amount of each increment from the growth field, and its default value is 1. Its value should be set to the entire

The total number of servers in the structure, two servers are used in this case, so the value is set to 2.

Auto-increment-offset is used to set the starting point (that is, the initial value) for automatic growth in the database, because both servers

An automatic growth value of 2 is set, so their starting points must be different in order to avoid data synchronization between the two servers.

A primary key conflict occurs when the

2. Set master1 as the master server of master2

Create an authorized account on the master1 host to allow connections on the master2 (192.168.41.11) host

Set master1 as your own master server on master2 and turn on slave.

To check the slave status, mysql > show slave status\ G; the following two values must be yes, indicating that the slave server can connect to the master normally.

Server

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

3. Set master2 as the master server of master1

Create an authorized account on the master2 host to allow connections on the master1 (192.168.41.10) host

Set master2 as your own master server on master1 and turn on slave.

Show slave status\ G to view the status of the slave. The following two values must be yes, which means that the slave server can connect to the master server normally.

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

4. Test master master synchronization

Create a database to be synchronized on master1 such as test_db, and create a test table such as tab1 in test_db

Check to see if the master2 host synchronizes the data changes on the master1

Insert data into the tab1 table on the master2 host

Check to see if the master1 host synchronizes the data changes on the master2

Now the updated data on any MySQL will be synchronized to another MySQL, and the MySQL synchronization will be completed.

Note: if the master MYSQL server already exists, the slave MYSQL server will only be built later. Before configuring data synchronization, the slave server should be set up.

First copy the database to be synchronized from the master MYSQL server to the slave MYSQL server (such as backup on the master MYSQL first

Copy of the database, and then restore it from the MYSQL server with backup)

Let's complete the high availability of keepalived.

Keepalived is a software solution to ensure the high availability of clusters in cluster management. Its function is similar to that of heartbeat.

To prevent a single point of failure

Keepalived is implemented on the basis of VRRP protocol. VRRP is called Virtual Router Redundancy Protocol.

Virtual routing redundancy protocol.

Virtual routing redundancy protocol can be considered as a protocol to achieve high availability of routers, that is, N routes that provide the same function.

The device forms a router group, in which there is a master and multiple backup, and the master has an external feed.

Vip and master for service will send multicast (the multicast address is 224.0.0.18). When the backup cannot receive the vrrp packet, it will recognize it.

If it is delayed for master, it is necessary to elect a backup as master according to the priority of VRRP. In that case,

You can ensure the high availability of the router.

Keepalived has three main modules, namely, core, check and vrrp. Core module is the core of keepalived

Responsible for the startup and maintenance of the main process and the loading and parsing of global configuration files. Check is responsible for health check-ups, including common

All kinds of examination methods. The vrrp module is used to implement the VRRP protocol.

II. Installation and configuration of keepalived

1. Install the package keepalived on master1 and master2

Install keepalived package and service control

Before compiling and installing Keepalived, you must install the kernel development package kernel-devel as well as openssl-devel,

Support libraries such as popt-devel.

Compile and install Keepalived

Note: if you don't know which dependent packages keepalived needs, you can check the INSTALL text in the downloaded source code directory.

Content, +

Note: after centos6 performs the make install operation, the / etc/init.d/keepalived script file is automatically generated, but it needs to be added manually

Serve the system, so you can use service and chkconfig tools to manage keepalived service programs.

Centos7: don't add-with-kernel-dircentos6

. / configure-prefix=/-with-kernel-dir=/usr/src/kernels/3.10.0-327.el7.x86_64/ & & make & & make install

The Master2 host also completes the keepalived installation. Like master1, the installation process is slight.

2. Modify the configuration file of Keepalived

Keepalived has only one configuration file, keepalived.conf, which mainly includes the following configuration areas, which are

Global_defs, vrrp_instance and virtual_server.

Global_defs: mainly configure the notification object and machine identification when a failure occurs.

Vrrp_instance: used to define the VIP area where services are provided and its related attributes.

Virtual_server: virtual server definition

Modification of the keepalived.conf file on the master1 host:

Vi / etc/keepalived/keepalived.conf:

! Configuration File for keepalived / /! Indicates global_defs {router_id MYSQL-1 / / indicates an identity of running keepalived server} vrrp_instance VI_1 {state BACKUP / / specifies the role of keepalived, where both configurations are BACKUP, and setting it to BACKUP will determine the interface virtual_router_id 51 / / virtual routing identity of the monitoring network based on priority, master or slave HA / / This ID is a number (the value is between 0255and used to distinguish the VRRP multicast of multiple instance). The same vrrp instance uses a unique identity to ensure that it is the same as master2. This must be different for different clusters within the same network, otherwise conflicts will occur. Priority 100 / / for electing master, to be master, the value range is 1-255. outside this range, it will be recognized as the default value of 100. here, the master2 is set to the interval between 50advert_int 1 / / sending VRRP packets, that is, how often a master election (which can be considered as a health check interval) nopreempt / / is not preempted, that is, a node with a lower priority is allowed to act as a master. Even if the node with higher priority starts authentication {/ / authentication zone, the authentication types are PASS and HA (IPSEC). It is recommended to use PASS (password only recognizes the first 8 digits) auth_type PASSauth_pass 1111} virtual_ipaddress {/ / VIP zone, specify vip address 192.168.1.100}} virtual_server 192.168.1.100 {/ / set virtual server, need to specify virtual IP address and service port The running time of delay_loop 2 / / is separated by a space between IP and port. The unit is lb_algo rr / / to set the back-end scheduling algorithm. Here, it is set to rr, that is, the polling algorithm lb_kind DR / / sets the mechanism of LVS to achieve load balancing. There are three modes: NAT, TUN and DR. Persistence_timeout 60 / session duration is available in seconds. This option is very useful for dynamic web pages and provides a good solution for session sharing in cluster systems. With this session persistence feature, the user's request is distributed to a service node until the duration of the session is exceeded. Protocol TCP / / specifies the forwarding protocol type. There are two types of real_server: TCP and UDP: 192.168.1.101 {/ / configure service node 1. You need to specify the real IP address and port of real server. Note: here on master2 is changed to 192.168.1.102 (i.e. master2 native ip) weight 3 / / configure the weight of the service node. The larger the number, the higher the weight. Set the weight size to distinguish between servers with different performance notify_down / etc/keepalived/bin/mysql.sh / / script TCP_CHECK {connect_timeout 3 / / connection timeout nb_get_retry 3 / / reconnection interval connect_port 3306 / / Health check port}} executed after detection of realserver's mysql service down.

The specific configuration for the keepalived.conf file on the master1 host is as follows:

Start keepalived

Systemctl start keepalived.service

Ps-aux | grep keep

Modification of the keepalived.conf file on the Master2 host:

Start the keepalived service

Systemctl start keepalived.service

3 、 # mkdir / etc/keepalived/bin

Vi / etc/keepalived / bin/mysql.sh, the contents are as follows:

The Master2 host completes the same operation

4. Test

Execute the ip addr show dev eth0 command in master1 and master2 respectively to view master1 and master2 versus VIP

Control of (cluster virtual IP).

View results of the Master1 master:

View results of the Master2 master:

You can see from the figure above that master1 is the primary server and master2 is the standby server.

Stop the MySQL service to see if the keepaliv ed health checker triggers the script we wrote

Stop the mysql service of the master1 host

View results of the Master2 master:

This means that stopping the MySQL service on the main service triggers the script we wrote to automatically fail over.

MySQL Telnet Test

Let's find one with MySQL client installed, and then log in to VIP to see if we can log in. Before logging in, there are two MySQL.

All servers are authorized to log in remotely. For example:

Grant all on. To root@'%' identified by '123456'

Test login on the client (turn it off before master1 starts):

The figure above shows that the VIP address is accessed on the client side, and the response is provided by the master1 host, because master1 is currently the master

Server, stop the mysql service of master1, and execute show variables like 'server_id' on the client

The figure above shows that the query request on the client side is answered by the master2 host. The failover was successful.

Summary:

Keepalived+mysql dual host generally speaking, when small and medium-sized scale, using this architecture is the most convenient.

After the failure of the master node, the high availability mechanism of keepalived is used to quickly switch to the standby node.

There are several points to pay attention to in this plan:

1. When using keepalived as the high availability scheme, it is best to set both nodes to BACKUP mode to avoid accidents

In the case of preemption (such as a brain fissure), the same data is written to two nodes, resulting in a conflict.

two。 The auto_increment_increment (self-increment step size) and auto_increment_offset (self-increment) of the two nodes

The initial value is set to a different value. The purpose is to avoid some binlog failures when the master node goes down unexpectedly.

Copy to the slave in time and be applied, which will cause the self-increment of the newly written data in slave to conflict with the original master.

So stagger it in the first place; of course, if there is a suitable fault-tolerant mechanism to solve the conflict of master-slave self-increasing ID,

You don't have to do that.

The 3.slave node server configuration should not be too poor, otherwise it is more likely to cause replication latency. Slave server as a hot backup node

Hardware configuration cannot be lower than master node

4. If you are sensitive to latency, you can consider using the MariaDB branch version, or launching MySQL 5.7 directly.

In the new version, the replication delay can be greatly reduced by using multithreaded replication.

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.