Mysql dual host hot standby and implementation of Mysql dual host and high availability with preserved

1, mysql creating database

This step should be performed by both hosts. Both hosts need to be installed. For mysql installation, please refer to "CentOS7 install mysql 8.0.12" or search by yourself.

After the installation, use the command to log in. The password is the password you configured:

mysql -u root -p

Create a test database:

mysql> CREATE DATABASE `test`;

Use the test library (use test;) to create an empty test table:

mysql> create table tb_mobile( mobile VARCHAR(20) comment'phone number', time timestamp DEFAULT now() comment'time' );

Note: it is mentioned in the online data that the two mysql data must be consistent before configuration. However, since both mysql are newly installed this time, they have not been tested.

2, Server configuration

1. Create synchronization user

Using mysql library,

mysql> use mysql

Then create a linked user for the other host. The account must be granted the REPLICATION SLAVE permission, because mysql8 cannot have the IDENTIFIED BY 'password' in the authorization statement. Therefore, the creation of the user and the granting of the permission need to be executed separately:

1) Execute create user and empower on 136:

mysql> CREATE USER 'replicate'@'172.17.3.134' IDENTIFIED WITH 'mysql_native_password' BY 'TestBicon@123';

Because the permission authentication mechanism of mysql8 has changed, there will be an exception prompt when using the following command to create users

create user 'replicate'@'172.17.3.134' identified by 'TestBicon@123';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'172.17.3.134';

2) On 134:

mysql> CREATE USER 'replicate'@'172.17.1.136' IDENTIFIED WITH 'mysql_native_password' BY 'TestBicon@123'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'172.17.1.136';

[note] after making permission changes to the user, remember to reload the permission and write the permission information into the database from memory.

mysql> flush privileges;

After creation, we can use the other host to access the local database to see whether it can be successfully linked [Note: when linking the other host, you need to exit the local mysql or open a new terminal first]:

mysql -h 172.17.3.134 -u replicate -p //Test each other in each host mysql -h 172.17.1.136 -u replicate -p

If the login is successful, the two servers can be hot standby.

2. Modify mysql configuration file

After mysql is installed, a my.cnf configuration file will be generated in the / etc / directory (if there is no copy from the / usr/share/mysql Directory: cp /usr/share/mysql/my-medium.cnf /etc/my.cnf). Find the configuration file and edit it (vim /etc/my.cnf). Add (modify) the following content in [mysqld]:

1) 172.17.1.136 host

server-id=1 # Unique id innodb_flush_log_at_trx_commit=2 # 1 by default log-bin=mysql-bin-1 #binlog log file name (can be named arbitrarily) #binlog-do-db=test # Database for logging (if not, all libraries are synchronized) binlog-ignore-db=mysql,information_schema,performance_schema,sys # Database without logging sync_binlog=1 #Enable binlog log synchronization function slave_skip_errors=1146 # Skip 1146 error

2) 172.17.3.134 host

server-id=2 # Unique id innodb_flush_log_at_trx_commit=2 # 1 by default log-bin=mysql-bin-2 #binlog log file name (can be named arbitrarily) #binlog-do-db=test # Logged database binlog-ignore-db=mysql,information_schema,performance_schema,sys # Database without logging sync_binlog=1 #Enable binlog log synchronization function slave_skip_errors=1146 # Skip 1146 error

3, Restart and configure synchronization

1. Restart mysql on two hosts

The two hosts execute respectively:

systemctl restart mysqld

perhaps

service mysqld restart

2. View the status of two servers as primary servers

mysql> show master status\G

3. Use the change mster statement to specify the synchronization location

Enter the MySQL operation interface (mysql-uroot-p), and execute the following instructions first:

mysql> unlock tables;

Otherwise, when stop slave; is executed, the following exception will be reported: ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction.

1) 172.17.1.136 host

It is very important to stop the slave service thread first. Otherwise, the following operations will not succeed.

mysql> stop slave;

then:

mysql> change master to master_host='172.17.3.134',master_user='replicate',master_password='TestBicon@123', master_log_file='mysql-bin-2.000001',master_log_pos=749;

Restart the slave service thread on the server:

mysql> start slave;

2) 172.17.3.134 host

It is very important to stop the slave service thread first. Otherwise, the following operations will not succeed.

mysql> stop slave;

then:

mysql> change master to master_host='172.17.1.136',master_user='replicate',master_password='TestBicon@123', master_log_file='mysql-bin-1.000001',master_log_pos=749;

Restart the slave service thread on the server:

mysql> start slave;

4. View the slave server status

mysql> show slave status\G

If the following two statuses are yes, the configuration is successful:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

4, Verification

Insert / delete data and other operations in mysql of the two hosts, and the other will automatically synchronize the operations.

5, Configure keepalived to realize mysql dual primary and high availability

The above configuration can realize the double-click hot standby of MySQL. The two hosts provide services to the outside respectively. If the service link fails to one host mysql, it cannot automatically switch to another host to realize high availability. If this can meet the needs or do not need to switch, the configuration can not continue. We will configure keepalived to make it highly available.

1. Download and install keepalived

Official website: http://www.keepalived.org (take version 2.0.6 as an example)

Installation location / usr/local / cd /usr/local/

Download and unzip using the following command:

wget http://www.keepalived.org/software/keepalived-2.0.6.tar.gz tar -xzvf keepalived-2.0.6.tar.gz -C keepalived

Before installation, you need to use the following command to install related dependencies:

yum -y install curl gcc openssl-devel libnl3-devel net-snmp-devel yum -y install libnfnetlink-devel yum -y install ipvsadm

Then install keepalived:

cd keepalived ./configure make && make install

PS: if there is a problem (exception) during compilation, make corresponding modifications according to the exception information. If there is a lack of XX devel and other information, just install XX devel. Otherwise, please determine the operation according to the exception information.

After compiling and installing, the startup script will be generated according to the system environment, as follows:

cat /usr/lib/systemd/system/keepalived.service

Add keepalived to system service and write configuration file in / etc/sysconfig /, and add keepalived command to / usr/bin and / usr/sbin (note in the following command is ignored in use):

cp /usr/local/keepalived/keepalived/etc/init.d/keepalived /etc/init.d/keepalived #It's better to check whether the maintained has execution permission mkdir /etc/keepalived/ cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/ cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/ cp /usr/local/sbin/keepalived /usr/bin/ #It is convenient to view the version of keepalived in the future. You can view it with keepalived - v cp /usr/local/sbin/keepalived /usr/sbin/

Modify the profile after copying:

vim /etc/keepalived/keepalived.conf

The following is the configuration content. It can not be copied directly according to the actual modification

1) 172.17.1.136 host

! Configuration File for keepalived global_defs { router_id LVS_DEVEL vrrp_skip_check_adv_addr #vrrp_strict vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_instance VI_1 { state BACKUP interface ens33 virtual_router_id 51 priority 100 advert_int 1 nopreempt #No preemption mode. Only the machines with high priority can be set, and the machines with low priority can not be set authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 172.17.3.210 } } virtual_server 172.17.3.210 3306 { delay_loop 2 lb_algo wrr lb_kind DR persistence_timeout 60 #Session hold time protocol TCP real_server 172.17.1.136 3306 { weight 3 notify_down /root/shutdown.sh #Script executed after service down detected TCP_CHECK { nb_get_retry 3 #Number of reconnections connect_timeout 10 #Connection timeout delay_before_retry 3 #Reconnection interval connect_port 3306 #Health check port } } }

2) 172.17.3.134 host

! Configuration File for keepalived global_defs { router_id LVS_DEVEL # String identifying this node, usually hostname vrrp_skip_check_adv_addr #vrrp_strict #It's better to comment out this, otherwise the browser of the physical machine may not be able to access the application vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_instance VI_1 { #state MASTER state BACKUP interface eno16777736 virtual_router_id 51 priority 100 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { # Block limited to 20 IP addresses @IP 172.17.3.210 } } #Virtual server definition block virtual_server 172.17.3.210 3306 { delay_loop 2 lb_algo wrr lb_kind DR persistence_timeout 60 protocol TCP real_server 172.17.3.134 { weight 3 notify_down /root/shutdown.sh TCP_CHECK { nb_get_retry 3 #Number of reconnections connect_timeout 10 #Connection timeout delay_before_retry 3 #Reconnection interval connect_port 3306 #Health check port } } }

Write the script shutdown.sh to execute after the detection service is down:

vim /root/shtdown.sh

The content is

#!/bin/bash killall keepalived

Note: this script is used by the notify down option in the above configuration file. Keepalived uses the notify down option to check the service status of the real server. When the real server service fails, this script will be triggered. As we can see, the script is a command to kill the keepalived process by killing keepalived, thus realizing the automatic failover of MySQL. In addition, we don't need to worry about the data update operation provided by two MySQL at the same time, because in the configuration of maintained on each mysql, only the IP+VIP of the local MySQL is available, not the IP+VIP of the two mysql

2. Start keepalived and view the log

chkconfig keepalived on service keepalived start #Start service service keepalived stop #Out of Service service keepalived restart #Restart service systemctl enable keepalived.service #Set startup

View log after startup:

tail -f /var/log/messages

3. Test

Pass vip login test on remote client

mysql -h 172.17.3.210 -uroot -p

4. Others

1) Modify log location

Modify the log file storage location / var/log/keepalived/keepalived.log

echo 'local0.* /var/log/keepalived/keepalived.log' >>/etc/rsyslog.conf

Restart the log service

systemctl restart rsyslog

2) keepalived configuration parameters

Global definition block

1. Email notification (notification, SMTP server, SMTP connect timeout): it is used to send email alarm in case of service failure, optional, not recommended. It is recommended to use the third independent monitoring service, such as nagios comprehensive monitoring instead.

2. lvs_id: lvs load balancer ID, in a network, its value should be unique.

3. Router ID: the name of the user ID node, usually hostname

4. Curly bracket {}: used to separate definition blocks, which must appear in pairs. If the write is missed, the keepalived runtime will not get the expected result. It is easy to miss curly braces at the end of the definition block due to nesting, which requires special attention.

VRRP instance definition block

vrrp? Sync? Group: synchronizes the vrrp level to determine the number of routing instances contained in a FailOver. In the case of two load balancers, once a load balancer fails, you need to automatically switch to another load balancer instance

group: at least one VRRP instance must be included. The name of the VRRP instance must be consistent with the definition of vrrp_instance

vrrp? Instance: vrrp instance name

1> State: instance state. There are only two states: MASTER and BACKUP, and they need to be all capitalized. In preemption mode, MASTER is in working state and BACKUP is in standby state. When the server of MASTER fails, the service of BACKUP will automatically switch its status from BACKUP to MASTER. When the service of the failed MASTER is restored, BACKUP is restored from the MASTER to the BACKUP state.

2> Interface: the network card interface that provides external services, that is, the network card interface bound by VIP. For example: eth0, eth1. At present, the mainstream servers have two or more interfaces (corresponding to the external network and the internal network respectively). When selecting the network card interface, it must be verified clearly.

3> Mcast SRC IP: local IP address

4> Virtual router ID: the ID number of the virtual route. Each node must be set the same. You can select the last IP segment to use. The same VRID is a group. It will determine the MAC address of the multicast.

5> Priority: node priority, value range 0-254, MASTER higher than BACKUP

6> Advert'int: the time interval between the synchronization check between MASTER and BACKUP node, in seconds

7> LVS ﹣ sync ﹣ daemon ﹣ interface: monitoring interface between load balancers, similar to HA HeartBeat's heart cord. But its mechanism is better than Heartbeat, because it does not have the problem of "split brain". It uses the priority mechanism to avoid this trouble. In DR mode, LVS sync daemon interface and service interface interface interface use the same network interface

8> Authentication: authentication type and authentication password. There are two types: PASS and AH. PASS is usually used. It is said that AH has problems in use. Verify that the password is clear text. Only when the same vrrp instance MASTER and BACKUP use the same password can they communicate normally.

9> Smtp_alert: whether to activate email notification in case of failure

10> Nopreempt: no preemption of services. By default, when the MASTER service is hung up, BACKUP will automatically upgrade to MASTER and take over its tasks. When the MASTER service is restored, the BACKUP service upgraded to MASTER will automatically reduce to BACKUP and give the work right to the original MASTER. When nopreempt is configured, MASTER will not preempt the service from hang up to recovery.

11> Virtual? IPAddress: virtual IP address pool, which can have multiple IPS, each IP occupies a row, and does not need to specify a subnet mask. Note: this IP must be consistent with our vip settings.

Virtual server virtual server definition block

Virtual server: define a virtual server. This ip is one of the virtual IPAddress definitions, followed by a space, and then add the port number of the virtual service.

1> Delay? Loop: interval of health examination in seconds

2> Lb_algo: load balancing and scheduling algorithm. wlc or rr is commonly used in Internet applications

3> Lb_kind: load balanced forwarding rule. It includes Dr, NAT and Tun, and generally uses routing (DR) forwarding rules.

4> Persistence Ou timeout: http service session hold time in seconds

5> Protocol: Forwarding Protocol, divided into TCP and UDP

real_server: real server IP and port, multiple can be defined

1> Weight: load weight, the higher the value, the higher the priority of forwarding

2> Notify down: script executed after the service is stopped

3> TCP "check: service validity detection

*Connect? Port: service connection port

*Connect Ou timeout: service connection timeout, in seconds

*NB get retry: service connection failure retries

*Delay? Before? Retry: retry connection interval in seconds

Add new comment

0 comments