As an old firewall developer, I dream of a MySQL switch (MySwitch).
You can customize your application so that you can change the database destination. Often you have to restart the application, which causes downtime. Most servers have many applications connected to them. Switching to a slave server requires everything to be done at once. Some applications start MUCH slower than others, causing more downtime.
The purpose of this project is to manage application connections to MySQL servers. I suggest using network address translation (NAT) as a "MySQL switch" (MySwitch) to move applications between servers. Simply put, the application would not connect directly to the database server. Instead, it would connect to MySwitch. The switch would then change the destination IP address of the packet to redirect it to the correct server. Since the source address packet does not change, the response packet would return back to the source application. Of course, nothing is that simple.
Some exceptions to this are: you cannot change the server in the middle of a conversion. The slave server must be ready for read/write before switching. Slave replication must not be delayed. Idle connections must be closed gracefully on the Master. How are new connections maintained while the switchover is taking place or can it be done fast enough? There is no need to make DNS changes or move IP addresses between servers.
I'm thinking of a Linux project written in C, perhaps with a NodeJS web interface. I would document and manage the master/slave relationships. Maybe you could point the service to a Master or Slave, and I would build the configuration. At least the cluster configurations could be documented in a JSON file. Perhaps the Slave could be configured as a Master and its current state at the time of the change could be documented, making it easier to switch from the old Master to Slave.
Here is a simple schematic of what needs to happen.
The client in the client cluster (10.0.0.1) connects to MySwitch (10.0.1.1).
MySwitch changes the destination IP (10.0.1.1) to the correct MySQL server (10.0.2.1).
(MySQL requests are made and returned to the client).
The administrator makes a request to switch to the MySQL server (10.0.2.2).
MySwitch checks that the slave (10.0.2.2) is less than ? seconds late.
MySwitch starts queuing new connection requests and continues processing all other types of packets.
MySwitch checks for connections from the master client cluster (10.0.2.1).
MySwitch
MySwitch closes inactive connections opened by members of the client cluster.
MySwitch checks the slave delay twice.
MySwitch switches the slave to read/write mode and restarts the slave connection.
SHOW SLAVE STATUS\G
Check that:
Master_Host: (Master = correct master)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
SELECT ID FROM information_schema.processlist WHERE host like ‘67.67%’ ;
This is used to check connections from the “Application Cluster” (67.67) on the Master.
SELECT concat(‘KILL ‘,id,’;’) FROM information_schema.processlist WHERE host like ‘67.67%’ and COMMAND = ‘Sleep’ into outfile ‘/tmp/a.txt’;
SOURCE /tmp/a.txt;
This is used to kill all the open connection for the “Application Cluster” on the Master.
SET GLOBAL READ_ONLY=0;
RESET SLAVE;
These are used to turn off replication on the Slave;
AM I A DREAMER? CAN THIS BE DONE? WHAT AM I MISSING?
�
�
�