Category: Technical

Implement multi-master to single-slave replication by periodically saving state and changing masters, and do it all in SQL.*

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 //

-- fetch next host
SELECT
id,
master_host,
master_port,
master_log_file,
master_log_pos
INTO _id, _master_host, _master_port, _master_file, _master_pos
FROM rotate_master
ORDER BY id <= LAST_INSERT_ID(), id LIMIT 1; -- advance to next host SET @sql := CONCAT( 'CHANGE MASTER TO master_host=', QUOTE(_master_host), ', master_port=', _master_port, ', master_log_file=', QUOTE(_master_file), ', master_log_pos=', _master_pos); PREPARE myStmt FROM @sql; EXECUTE myStmt; -- mark host as changed UPDATE mysql.rotate_master SET in_use = 0 WHERE in_use = 1; UPDATE mysql.rotate_master SET in_use = 1 WHERE id = _id; START SLAVE; END// 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();

csplit backup.sql /"^-- Current Database"/ {*}; for f in xx*; do cat xx00 "$f" | mysql& done;

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.

So you have two versions of a database sitting around – maybe you’ve been trying things out on your development box, and now you want to move it all over to production. If it’s just a new table here and an extra column there, you could do it by hand, but if you’ve made lots of changes, it would be nice to let the computer do it for you.

Enter MySQL Workbenchmysqldiff.

CREATE TABLE t1 (id int);
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW 
  INSERT INTO t1 VALUES (NEW.id);

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.

On occasion I need to generate a number of rows. I don’t care about the contents, just how many rows there are. Maybe I want to create a table filled with a sequence of numbers. One common way to do this is to join a bunch of subqueries built out of unions:


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)

A non-rigorous, non-scientific, totally off-the-cuff test of which function to pick when you need to group by year and month.

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):

Dunno why you’d rather do this in SQL than in your application layer, but if you do, here’s one way to turn a delimited string of values back into multiple rows – just the opposite of GROUP_CONCAT:

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') |
+----------------------------------------------------+