Just a proof of concept. I suggest you add some error checking (like, don't make the switch if the slave isn't running).

Remember that all this stuff goes on the SLAVE. If you absent-mindedly log in to the master, like I did a few times, you might wonder why your relay_log.info file isn't read.

Set up a table to keep track of each master file. Mine use the same user name and password for all hosts; you can add those fields and adjust the procedure accordingly if you like.

USE mysql;
DROP TABLE IF EXISTS rotate_master;
CREATE TABLE rotate_master (
id int auto_increment primary key,
master_host varchar(255),
master_port int unsigned,
master_log_file varchar(255),
master_log_pos int unsigned,
in_use boolean DEFAULT 0
);

Insert the list of masters. The one that is currently master should have `in_use` set to 1; all the others should be 0. The order you insert them is the order they will rotate.

INSERT INTO rotate_master VALUES (NULL, 'localhost', 3306, 'bin.000001', 0, 1);
INSERT INTO rotate_master VALUES (NULL, 'localhost', 3308, 'bin.000001', 0, 0);

Then, a procedure to grab the current position (by reading from the relay_log.info file, since the slave position is not otherwise exposed in any accessible manner), record it in the above table, and change masters to the next one in the list.

DROP PROCEDURE IF EXISTS rotate_master;
DELIMITER //

DELIMITER ;

Finally, schedule it to rotate. I used 10 seconds just to play with; I imagine a few minutes would be more practical. *If you don't have 5.1 or later, you'll have to schedule this part externally, with cron or task scheduler.

DROP EVENT IF EXISTS rotate_master;
CREATE EVENT rotate_master
ON SCHEDULE EVERY 10 SECOND
DO CALL mysql.rotate_master();

Start with a text file, 'test.txt', with these three lines:

1
1
2

Set up the test in MySQL:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id int primary key);
LOAD DATA INFILE 'test.txt' INTO TABLE t1;

This gives "ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'", which is expected.

What's in the table?

Show Answer ▼

Now, pretend you're setting up a slave, or there was a crash and you're recovering from binary logs:

mysqlbinlog bin.000001 | mysql

How many rows are in t1 now?

Show Answer ▼

and then rm xx* when the background jobs have finished.

I haven't done any benchmarking, and I haven't worked out what to do if the number of databases exceeds the number of available connections.

Both mysqldump and the mysql client have --xml options. The output from the cli looks like this:

<resultset statement="SELECT here">
  <row>
    <field name="field1">value</field>
    <field name="field2">value</field>
  </row>
  <row>
    <field name="field1">value</field>
    <field name="field2">value</field>
  </row>
</resultset>

I wanted to change those <field name="fieldName"> into <fieldName> tags, and all it takes is a little XSLT, which I've uploaded here.

On Linux you can do something like this:

mysql -e "SELECT * FROM dbname.tablename" | xsltproc mysql.xsl -

Enter MySQL Workbench.

Workbench lets you import the structure from a dump file, or reverse engineer from a running server. Then you can synchronize it with a same-named database on another server, and it will sort out the required CREATE TABLE or ALTER TABLE ADD COLUMN for you. You don't even have to complete the step and actually let it do the synchronization, if all you want is to save the script for reference purposes.

Here's a little screencast showing the action.

I first import the old structure into the MySQL server using the command line client:

mysql < oldTest.sql

I then show connecting to the server to demonstrate that the server holds just two tables, `t1` and `t2`, and that t1 has only a single column, `id`.

Next, I switch to Workbench and import the modified file, newTest.sql. Note that both of the files include the USE dbname; statement so that Workbench knows which schema to use. The new schema includes a new table, `t3`, as well as adding a new column to `t1`, column `d`.

I synchronize the model with the running server, ignoring the `mydb` schema and updating the source for the `test` schema. The resulting script shows the CREATE TABLE statement for `t3`, and the ALTER TABLE statement to add `d` to `t1`. That script can be saved as a file, copied to the clipboard, or executed directly on the server.

That's not allowed; it won't let you create the trigger.

CREATE TABLE t1 (id int);
CREATE TABLE f1 (id int) ENGINE=FEDERATED CONNECTION='localhost/test/t1';
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
  INSERT INTO f1 VALUES (NEW.id);

The server lets that through. Don't do it.


SET @count := 0;
INSERT INTO table SELECT @count := @count + 1
FROM (SELECT 1 UNION SELECT 2) a
JOIN (SELECT 1 UNION SELECT 2) b
JOIN (SELECT 1 UNION SELECT 2) c
...

Of course, I could union more than two selects (e.g. SELECT 1 UNION SELECT 2 UNION SELECT 3). That makes each derived table longer, but reduces how many must be joined.

Question: What's the optimal size of each derived table?

Defining "optimal": since this is a one-off query, I don't much care about performance (within reason), but I do care about how much typing I have to do. So I will favor "fewer characters" over "fewer joins".

Each derived table in the query takes up 15 * numSelects + 2 characters.

To generate a given number of rows requires CEILING(LOG(numRows) / LOG(numSelects)) derived tables.

If I want 1000 rows in the result, I'd need 10 derived tables of two rows each (320 characters), or 7 of three rows (329 characters), or 3 of ten rows (456 characters).

For any given numRows, what's the minimum of CEILING(LOG(numRows) / LOG(numSelects)) * (15 * numSelects + 2) ?

The answer varies with numRows - 2, 3, or 4 are alternately best when generating less than 130 rows, but any higher than that and the best choice usually turns out to be 3.

(SELECT 1 UNION SELECT 2 UNION SELECT 3)

I populated a table with 262K rows of random dates, and then ran

SELECT %s, COUNT(*) FROM table GROUP BY %s ORDER BY NULL

with various functions, which should all result in the same grouping. I repeated each query five times and show the average time, using three different column types DATE, DATETIME, and TIMESTAMP.

And just in case you want to extract a full date (which really only makes sense for datetime and timestamp):

The most popular answer online was "use a bi-metal hole saw, low speed, lots of pressure, lots of lubricant." I tried that, and the pilot bit wouldn't even scratch it.

I happened to have a hammer drill and some 3/8" bits, and those went through ... eventually. So I tried to enlarge it with a 1" hammer drill bit. I got a jagged hole and a burned out hammer drill motor, which I have to ship off for repair now, having used it only three times.

The next most popular answer seemed to be a conduit knockout punch, but that requires a pilot hole of 3/4" (if I could make that, I wouldn't need this punch, now would I?) and Lowe's only carries them in a set of 4 for $150.

Fortunately, the manager appeared and talked me down to a $55 Greenlee step bit. Went home, popped it in my little "backup" drill, and had two nice clean holes in well under ten minutes.

Some tips:

- I bought a Greenlee step drill bit, $55 at Lowe's. They also had an Irwin for $53, but the manager said the Greenlee is twice the quality of the Irwin.
- Measure the hole location every which way you can (remember, you only get one shot at this!), and then make a little starter divot with a hammer and nail or punch so the drill doesn't wander over the surface.
- Don't run the drill at full speed; you will work-harden the sink and dull the bit. If it's smoking, you were going too fast.
- Don't run the drill too slow. Apply firm downward pressure (we're talking "both hands on the drill" firm, not "the combined body weight of you and your cousin Earl" firm.) If you run it too slow or don't press firmly, when you finish one step and drop to the next step, it will gouge two chunks out of the side of the hole and lock the bit in place.
- Use plenty of lubricant. WD-40 is fine; any oil ought to suffice. Spray it on liberally before and during drilling.
- Don't forget to mark on the drill bit where to stop - wrap a piece of masking tape around it at the right height, or you may get to repeat this procedure on a new sink.

SET @sourceString = 'a,b,c,d,e';
SET @sql = CONCAT('INSERT INTO t VALUES (\\'', REPLACE(@sourceString, ',', '\\'),(\\''), '\\')');
PREPARE myStmt FROM @sql;
EXECUTE myStmt;

Just to show what's going on:

mysql> SELECT @sql;
+----------------------------------------------------+
| @sql                                               |
+----------------------------------------------------+
| INSERT INTO t VALUES ('a'),('b'),('c'),('d'),('e') |
+----------------------------------------------------+