Use mysql-proxy to implement dual-host mutual backup and Failover for MySQL servers

Source: Internet
Author: User
Tags failover lua

The previous gitlab dual-host mutual backup environment (refer to the previous blog post) still has a problem: the backend database is a single server and there is a risk of spof. In the past few days, we have investigated relevant materials, mysql-proxy can be used to solve this problem.

Environment required:

Build a master-slave environment with two MySQL servers: Master-Master

Build mysql-proxy on one server

Summary

1. Set up the MySQL Master/Slave environment: Master-Master

2. Implement failover through mysql-proxy

3. Modify the database configuration of gitlab and directly connect to MySQL-proxy

4. Issues to be resolved

1. Build a master-slave MySQL environment (master-master)

Prepare two Linux servers: A and B (MySQL version, 5.0). perform the following operations on A and B respectively:

1. Install MySQL

1 $A/$B: yum install mysql-server.x86_64 

2. Create a synchronization account and grant the synchronization permission

1 $A/$B: GRANT REPLICATION SLAVE ON *.* TO 'slave'@'$B' IDENTIFIED BY 'slave';2 $A/$B: flush privileges;

3. Modify the MySQL configuration and add the following content to the/etc/My. CNF file:

1 log-bin = mysql-bin # log file of the synchronization event 2 log-Salve-Updates 3 server-id = 47 # Database ID ID4 5 master-host = $/ $ B # synchronize database address 6 master-user = slave # synchronize database user 7 master-Password = slave # synchronize Database Password 8 master-connect-retry = 60 # If the server finds that the master server is disconnected, time Difference of reconnection

Restart the MySQL server

4. Set server Parameters

Stop synchronization on the current host

1 $A/$B: mysql>stop slave;

View the mysqlbinlog log location of the current database on the slave machine ($ B)

1 mysql> show master status;2 +——————+———-+——————+——————+3 | File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB |4 +——————+———-+——————+——————+5 | mysql-bin.00008 |      210 | ikey_db            |6 +——————+———-+——————+——————+7 1 row in set (0.00 sec)

Record the current synchronization Log File mysql-bin.00008, synchronization location: 210;

Change the Data Location of the standby host ($)

1 mysql> change master to2-> master_host = '$ B', # IP address of the original slave Node 3-> master_user = 'slave ', 4-> master_password = 'slave ', 5-> master_log_file = 'mysql-bin.00008 ', # We just recorded the log file that the slave node database wants to copy. 6-> master_log_pos = 210; # the location of the log file to be copied in the backup node database of the VM just recorded. 7 query OK, 0 rows affected (0.02 Sec)

Similarly, view the mysqlbinlog log location of the current database of the host ($ ).

1 mysql> show master status;2 +——————+———-+——————+——————+3 | File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB |4 +——————+———-+——————+——————+5 | mysql-bin.00009 |      213 | ikey_db            |6 +——————+———-+——————+——————+7 1 row in set (0.00 sec)

Change the Data Location of the slave machine ($ B) to sync Host Data

1 mysql> change master to2-> master_host = '$ A', # master node IP address 3-> master_user = 'slave', 4-> master_password = 'slave ', 5-> master_log_file = 'mysql-bin.00009 ', # the log file we just logged to the master database to execute replication. 6-> master_log_pos = 213; # the location of the log file to be copied in the master database of the VM just recorded. 7 query OK, 0 rows affected (0.02 Sec)

5. Start the synchronization status and check whether the synchronization is successful.

1 $A/$B: mysql>start slave;

Check Data Synchronization status

1 $ A/$ B: mysql> show slave status \ G; 2 **************************** 1. row *************************** 3 slave_io_state: Waiting for Master to send event 4 master_host: virtual Machine slave node ip 5 master_user: sync_ikey 6 master_port: 3306 7 connect_retry: 60 8 master_log_file: mysql-bin.000008 9 read_master_log_pos: 21010 relay_log_file: node1-relay-bin.00000311 relay_log_pos: 25112 slave: mysql-bin . 00000213 slave_io_running: yes14 slave_ SQL _running: Yes...

As long as you can view it in the database
Slave_io_running: Yes
Slave_ SQL _running: Yes
This indicates that the data synchronization status is normal.

2. Build a MySQL-Proxy Server

Prepare the Linux Server C and download the mysql-proxy installation package http://dev.mysql.com/downloads/mysql-proxy/. decompress the package to your local computer.

Write failover scripts

VI $ mysql-proxy_path/share/doc/MySQL-proxy/mysql_failover.lua

 1 function connect_server() 2     for i = 1, #proxy.backends do 3         local s = proxy.backends[i] 4         print ("s.state:" + s.state) 5         if s.state ~= proxy.BACKEND_STATE_DOWN then 6             proxy.connection.backend_ndx = i 7             print ("connecting to " .. i) 8             return 9         end10     end11 end12 13 function read_query(packet)14     for i = 1, #proxy.backends do15         local s = proxy.backends[i]16         print ("s.state:" + s.state)17         if s.state ~= proxy.BACKEND_STATE_DOWN then18             proxy.connection.backend_ndx = i19             print ("connecting to " .. i)20             return21         end22     end23 end

Start mysql-proxy

$mysql-proxy_path/bin/mysql-proxy --proxy-address=:4040 --proxy-lua-script=$mysql-proxy_path/share/doc/mysql-proxy/mysql_failover.lua --proxy-backend-addresses=$A:3306 --proxy-backend-addresses=$B:3306 --log-level=error  --log-file=$mysql-proxy_path/mysql-proxy.log --keepalive --proxy-fix-bug-25371

Mysql-proxy is connected to server a by default. If server a fails, MySQL-proxy will automatically switch to server B. If server a recovers, MySQL-proxy will switch to server a again, in this way, MySQL dual-host mutual backup and Failover can be implemented.

3. Modify the database configuration of gitlab and directly connect to MySQL-proxy

VI $ gitlab_path/config/database. yml

1 production: 2 adapter: mysql2 3 encoding: utf8 4 reconnect: False 5 Database: gitlab 6 pool: 25 7 Username: root 8 password: 9 Host: $ C # mysql-proxy host address 10 port: 4040 # mysql-proxy host port 11 # socket:/tmp/MySQL. sock
4. Issues to be resolved

The biggest problem with MySQL-proxy is that it may become a single point of failure and there is a risk of single point of failure.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.