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

Environment description:

OS:CentOS6.5_X64

MySQL-VIP:192.168.1.200

MySQL-master1:192.168.1.201

MySQL-master2:192.168.1.202

1. Configure the synchronization of two Mysql masters

[root@master ~] # yum install mysql-server mysql- y [root@master ~] # service mysqld start [root@master ~] # mysqladmin-u root password 123.com [root@master ~] # vi / etc/my.cnf # Open binary log Set id [mysqld] server-id = 1 # backup this set 2log-bin = mysql-binbinlog-ignore-db = mysql Information_schema # ignore the library written to the binlog log auto-increment-increment = 2 # Field change incremental value auto-increment-offset = 1 # initial field ID is 1slave-skip-errors = all # ignore all replication errors [root@master ~] # service mysqld restart

If it is Centos7, uninstall mariadb and install mysql. Refer to article: https://blog.51cto.com/sf1314/2073389

To install mysql, refer to the article:

Mysql5.6.37 https://blog.51cto.com/sf1314/2058754

# mysql-uroot-p 'password'

Check the log bin log and the posvalue location first.

The master configuration is as follows:

[root@ master ~] # mysql- u root-p123.commysql > GRANT REPLICATION SLAVE ON *. * TO 'replication'@'192.168.0.%' IDENTIFIED BY' replication';mysql > flush privileges;mysql > change master to-> master_host='192.168.0.203',-> master_user='replication',-> master_password='replication',-> master_log_file='mysql-bin.000002',-> master_log_pos=106; # the value shown in the peer status mysql > start slave # start synchronization

The backup configuration is as follows:

[root@backup] # mysql- u root-p123.commysql > GRANT REPLICATION SLAVE ON *. * TO 'replication'@'192.168.0.%' IDENTIFIED BY' replication';mysql > flush privileges;mysql > change master to-> master_host='192.168.0.202',-> master_user='replication',-> master_password='replication',-> master_log_file='mysql-bin.000002',-> master_log_pos=106;mysql > start slave

# after the master synchronization is configured, check the synchronization status of Slave_IO and Slave_SQL: YES indicates that the master synchronization is successful.

Under the master insert data test:

Check whether the synchronization is successful in backup:

You can see that the past has been successfully synchronized, and the same data has been inserted into the user table in backup, and the same synchronization has been done.

2. Configure keepalived to realize hot backup

[root@backup ~] # yum install-y pcre-devel openssl-devel popt-devel # install dependency package

[root@master ~] # wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz [root@master ~] # tar-xf keepalived-1.2.7.tar.gz-C / usr/local/src/ [root@master ~] # cd / usr/local/ [root@master ~] # ln-sv / usr/local/src/keepalived-1.2.7/ keepalived # modify [root@master ~] # cd keepalived according to the actual situation [root@master] # / configure-- prefix=/usr/local/keepalivedmake & & make install

# configure keepalived as a system service

[root@master ~] # cp / usr/local/etc/rc.d/init.d/keepalived / etc/init.d/chmod + x / etc/init.d/keepalived [root@master ~] # cp / usr/local/etc/sysconfig/keepalived / etc/sysconfig/ [root@master ~] # chkconfig-- add keepalived [root@master ~] # chkconfig keepalived on [root@master ~] # mkdir / etc/keepalived [root@master ~] # cp / usr/local/etc/keepalived/ Keepalived.conf / etc/keepalived/ [root@master ~] # cp / usr/local/sbin/keepalived / usr/sbin/ # or ln-s / usr/local/sbin/keepalived / usr/sbin/

Configure keepalived

We are creating a new configuration file. By default, we will look for the configuration file in the / etc/keepalived directory when keepalived starts.

[root@master ~] # vi / etc/keepalived/keepalived.conf global_defs {notification_email {862572301@qq.com} notification_email_from 862572301@qq.com smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id MYSQL_HA # logo Dual master same} vrrp_instance VI_1 {state BACKUP # two configurations here both BACKUP interface eth0 virtual_router_id 51 # master and standby have the same priority 100# priority, the other backup is changed to 90 advert_int 1 nopreempt # without preemption, and can only be set on machines with high priority master Machines with low priority backup do not set authentication {auth_type PASS auth_pass 1111} virtual_ipaddress {192.168.1.200}} virtual_server 192.168.1.200 3306 {delay_loop 2 # check real_server status # lb_algo wrr # LVS algorithm every 2 seconds. We turned off the # lb_kind DR # LVS mode. If not, the standby server cannot connect to the primary MySQL persistence_timeout 60 # session duration through VIP, and the connection to the same IP is assigned to the same real server protocol TCP real_server 192.168.1.201 3306 {# to detect the local mysql within 60 seconds. Backup also needs to write to detect local mysql weight 3 notify_down / usr/local/keepalived/mysql.sh # execute this script when mysq takes down Kill keepalived to switch TCP_CHECK {connect_timeout 10 # connection timeout nb_get_retry 3 # number of reconnections delay_before_retry 3 # reconnection interval connect_port 3306 # Health check Port}} write detection The script to be executed after the service down [root@master ~] # vi / usr _ usr/local/keepalived/mysql.sh localql.shql.shql.shroupkill keepalived [root@master ~] # chmod + x / usr/local/keepalived/mysql.sh [root@master ~] # / etc/init.d/keepalived start

Note: this script is used by the notify_down option in the configuration file above. Keepalived uses the notify_down option to check the service status of real_server and triggers this script when a real_server service failure is found. We can see that the script forcefully kills the keepalived process through pkill keepalived with a single command, thus realizing automatic MySQL failover. In addition, we do not have to worry that both MySQL will provide data update operation at the same time, because the configuration of the keepalived on each MySQL only contains the IP+VIP of the native MySQL, not the IP+VIP of the two MySQL.

Start keepalived

[root@master ~] # / usr/local/keepalived/sbin/keepalived-D or / etc/init.d/keepalived start [root@master ~] # ps-aux | grep keepalived

test

Find a local area network PC, and then go to ping MySQL's VIP. At this time, MySQL's VIP can be connected with ping.

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

# backup server only modifies priority to 90, nopreempt is not set, and real_server sets local IP.

# authorize two Mysql servers to allow root to log in remotely for login testing on other servers!

Mysql > grant all on *. * to'root'@'192.168.0.%' identified by '123.com'

Mysql > flush privileges

3. Test high availability

1. Connect through VIP through the Mysql client to see if the connection is successful.

2. To stop the mysql service of master, whether you can switch over normally, you can use the ip addr command to see which server VIP is on.

3. You can see the master / slave switching process by viewing / var/log/messges log.

4. Whether to actively preempt resources and become an active server after the failure recovery of master server.

Attached: keepalived-1.2.7 keepalived implements highly available http://down.51cto.com/data/2440924 for service

-

Do bond for Nic to do VIP: https://blog.51cto.com/sf1314/2073519

[root@master ~] # vi / etc/keepalived/keepalived.conf global_defs {notification_email {862572301@qq.com} notification_email_from 862572301@qq.com smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id MYSQL_HA # logo Two masters are the same} vrrp_instance VI_1 {state BACKUP # two configurations here are both BACKUP interface bond0.101 #-> the aggregation network card bond0.101 virtual_router_id 51 # of the specified configuration here has the same priority priority of master and slave. The other backup is changed to 90 advert_int 1 nopreempt # without preemption, and can only be set on machines with high priority master. Machines with low priority backup do not set authentication {auth_type PASS auth_pass 1111} virtual_ipaddress {10.104.101.13 VIP 24 #-> set the virtual VIP address}} virtual_server 10.104.101.13 backup 24 3306 { #-> specify configuration information for virtual VIP address delay_loop 2 # check real_server status every 2 seconds # lb_algo wrr # LVS algorithm If not, we turned off the # lb_kind DR # LVS mode. If not, the standby server cannot connect to the primary MySQL persistence_timeout 60 # session duration through VIP, and the connection to the same IP is assigned to the same real server protocol TCP real_server 10.104.101.12 3306 {# 10.104.101.12 refers to the locally configured IP address within 60 seconds to detect the local mysql. Backup also needs to write to detect local mysql weight 3 notify_down / usr/local/keepalived/mysql.sh # execute this script when mysq takes down Kill keepalived to switch TCP_CHECK {connect_timeout 10 # connection timeout nb_get_retry 3 # number of reconnections delay_before_retry 3 # reconnection interval connect_port 3306 # Health check Port}}

Attachment: to modify the password of replication under the mysql account, please refer to this article: https://blog.51cto.com/sf1314/2094562

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.