Imagine a geographical database of places and the languages spoken there, with keys to a dictionary of common words, with keys to the same word in all other languages.   Imagine again that this database is like a wiki, so it can be used by anyone.  You could type in your location and get "English is spoken here".  And maybe this database would have keys to the words spoken in each language.

If there was an API to this database, you could make simple translations of the language of the programs.  The programs would not have to code tables for translations.  The "PUSH" or "OK" button would simply hold the key to the word.  A web application could do an IP search on GEO and speak your language.  A mobile application could alert you that "French is spoken here".

Oh and this would need to be FREE and in beer.

Does this exist?  Why not?

�

�

.

I was delighted to hear just a day before the event that there was a MySQL user group in Oklahoma. OKCMySQL.org

July 23, 2014. The first meeting of the OKC MySQL Users Group will be held in OKC. Special guest Peter Zaitsev (CEO of Percona and co-author of High Performance MySQL) will give a presentation on "Indexing Best Practices" followed by a Q&A session.

There is very little time left.  During the RPVP meeting.

Hope to see you at The Paramount - 701 West Sheridan, Oklahoma City, OK (map).

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?

�

�

�

If you have upgraded from MySQL 5.1 to a newer version (5.5? 5.6? 5.7?), Mark Leith shows why you should enable performance_schema. Roland Bouman has provided us with Common_Schema to help improve the SQL user experience.   Now Mark lets us look at IO latency to improve the system.

Mark has developed a great set of views and procedures for working with performance_schema that can give you things like:

a list of all statements with the slowest 5% average execution time in microseconds.

statements that have performed a full table scan.

find indexes that were not preceded by any events (and therefore not used).

Show the largest disk IO users in terms of byte usage per file.

You will need to run mysql with the -performance_schema option, or put performance_schema=on in my.cnf and restart the server.  Then download the lattest 5.5 or 5.6 code from Mark's page and import it.

Now you’re ready to learn things like:

�

�

�

�

�

�

�

Migrating CentOS to Redhat