maria-discuss team mailing list archive

[Weldon Whipple <weldon@xxxxxxxxxxx>]

It's been awhile since I first posted about db-binlogging (actually,
file-per-database binlogging). Our requirement is to enable the
migration of a user account--including the user's MySQL
database(s)--from one physical server to another, minimizing database
downtime.

After being briefly sidetracked to work on two other projects, it
LOOKS like I have an implementation that fulfills the needs of my
employer. In a typical migration, our tools (likely implemented in
perl) would issue this command sequence (where db1 and db2 are the
user's databases):

START BINLOG FOR DATABASE db1;
START BINLOG FOR DATABASE db2;
FLUSH TABLES db1.t1, db1.t2, db1.t3, db2.t1, db2.t2 WITH READ LOCK;
SHOW DATABASE BINLOG STATUS;  # Remember binlog coordinates for db1 and db2
[Issue mysqldump or other appropriate commands to export the databases
and copy them to new server]
UNLOCK TABLES;  # Unlocks tables locked earlier in this session

[Use mysqlbinlog command or other commands to bring up user databases
on new physical server--already running mysqld to support existing
users.]
[On original server, issue SHOW DATABASE BINLOG STATUS to see if
changes have occurred since first SHOW DATABASE BINLOG STATUS.]

If databases have changed, then (on originating server):
- flush the same tables as above with READ LOCK
- STOP BINLOG FOR DATABASE db1;
- STOP BINLOG FOR DATABASE db2;
- Copy binlogs to new server and issue mysqlbinlog command, specifying
coordinates from initial SHOW DATABASE BINLOG STATUS

/* If no databases changed during the migration, skip the above "IF" */

(Note: on most account migrations, databases change infrequently, so
the initial data copied from the old server to the new server will
still be up-to-date.)

Change routers to direct user's traffic to new server that has user's
MySQL databases.

The tool writers here say that the FLUSH TABLES ... WITH READ LOCK and
UNLOCK TABLES are sufficiently granular for our installations. Those
two commands are part of MySQL 5.5. (Well, UNLOCK TABLES has been
around since before 5.5 ...)

I have implemented the following in the version of MySQL 5.5 I checked
out of launchpad in September.
START BINLOG FOR DATABASE db;
SHOW DATABASE BINLOG STATUS;
STOP BINLOG FOR DATABASE db;

I also created a test suite in mysql-test/suite/db_binlog that passes all tests.

The changes are conditionally compiled by #defining HAVE_DB_BINLOG

I would appreciate putting the code out for feedback/suggestions.

If there IS interest, please suggest how you want it. (E.g., a
launchpad patch file, etc.)

Thanks in advance!

Weldon Whipple
weldon@xxxxxxxxxxx

(P.S. Because of our dependence on FLUSH TABLES t1, t2, [... tn] WITH
READ LOCK, our company's rollout will wait until after 5.5 has been
released and we have verified it.)