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

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.