Davi, Sergei (and everyone else), Thanks for the great feedback. It got me looking at the capabilities of MySQL 5.5. It's POSSIBLE that the new syntax: FLUSH TABLES t1, t2, t3, ... , tn WITH READ LOCK is PRECISELY the functionality we need! Our (perl) migration script MIGHT (for example) do the following: Issue the following command (implemented by me ...): 1. START BINLOG FOR DATABASE <db>; (for each database a given user owns. [Hmmm... Maybe my implementation allows multiple <db> arguments. I need to check.] Each database would start binlogging to its own file. [We will configure--in my.cnf--the 1-database-per-file binlogs to be stored on a very fast solid-state drive.]) Then the script creates a list of all tables in all the user's databases being migrated to another server and issues the command (new to MySQL 5.5): 2. FLUSH TABLES t1, t2, t3, ..., tn WITH READ LOCK; (where the tables t1, t2, ..., tn are the tables being migrated.) Then issue the following command (implemented by me ...): 3. SHOW DATABASE BINLOG STATUS; which shows--for each database being binlogged to its own file: a. the database name b. whether binlogging is active c. the name of the current binlog file d. the binlog position in that file. The script captures the coordinates, then 4. Creates a copy of the database(s) using mysqldump or scp/tar/gzip, etc. (MyISAM databases will probably just be copied "raw," because it is much faster. Innodb will probably have to use mysqldump or something similar.) 5. UNLOCK TABLES: On the new server, the copied/mysqldump'ed files will be used to bring up the mysql on the new server. At some future time--hopefully SOON--the tables could be locked again. For any where the coordinates have changed: The binlog file(s) would be copied to the new server, "played" by mysqlbinlog, and the new server would be brought online on the new box and taken offline on the old. When the migration is determined to be complete, issue this command (implemented by me) on the old box: 6. STOP BINLOG FOR DATABASE <db>; for all the user's databases ... and the migration is done. QUESTIONS/ASSUMPTIONS: 1. In playing with the new FLUSH TABLES t1, t2, ..., tn command, I've noticed that if the connection drops, the locks are all unlocked. ... So our scripts will have to make sure the connection stays up. (ANY SUGGESTIONS?) 2. The UNLOCK TABLES command unlocks all the tables locked earlier in the same session. (So it doesn't need the variable argument list of tables to unlock ...) A post on dev.mysql.com shows the following possible sequence of commands: FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; SYSTEM xfs_freeze -f /var/lib/mysql; SYSTEM YOUR_SCRIPT_TO_CREATE_SNAPSHOT.sh; SYSTEM xfs_freeze -u /var/lib/mysql; UNLOCK TABLES; EXIT; (The FLUSH TABLES command would correspond to #2 above. SHOW MASTER STATUS would correspond to #3 above. UNLOCK TABLES would correspond to #5 above. The SYSTEM commands would have to be replaced by our own commands/scripts.) Would running (sub-)scripts from SYSTEM commands inside MySQL be sufficient to hold the locks? BUG IDENTIFIED. I noticed the following mysqld-crashing bug--in 5.5.6 rc as well as the one I modified: 1. FLUSH TABLES t1, t2, t3, ..., tn WITH READ LOCK; 2. FLUSH TABLES; The second FLUSH TABLES (with no added tokens) crashes at mdl.cc:1563 : DBUG_ASSERT(mdl_request->type != MDL_EXCLUSIVE || is_lock_owner(MDL_key::GLOBAL, "", "", MDL_INTENTION_EXCLUSIVE)); because is_lock_owner() returns NULL when FLUSH TABLES is called immediately after FLUSH TABLES [with additional args]; QUESTION: I read that Larry Ellison stated (at a conference) that 5.5 would be out by year end. How likely is that? Our company generally waits for some point releases before changing our customer boxes to the next release. Should I try to back port those locks to 5.1 in the interim? ... or leave well enough alone? Thanks in advance for your feedback/help/advice!! We.don Whipple On Mon, Oct 18, 2010 at 12:48 PM, Davi Arnaut <davi.arnaut@oracle.com> wrote:

On 10/18/10 3:16 PM, Weldon Whipple wrote:

...

Well, I finished a working prototype that does everything except the locking and unlocking of databases. Someone mentioned that it's now possible to lock individual tables. Would you--anyone--be willing to point me toward places in the 5.5 code that use that locking/unlocking?

Do you want to lock the database, or individual tables, or both? Anyway, take a look at mysql_rm_db as the locking is all very similar. Roughly, the algorithm of a DROP DATABASE is:

       // Protect against other schema operations        lock_schema_name("db1");        // Build list of tables in the db1 directory        table_list = ..        // Initialize metadata lock request        table_list->mdl_request.init(MDL_key::TABLE,                table_list->db,                table_list->table_name, MDL_EXCLUSIVE);        // Lock tables        lock_table_names(thd, tables, NULL ...)        // Remove from cache and drop the table...

The best thing at this point is to study (and debug) a similar statement and understand how metadata locks work (mdl prefix) or how tables are open/locked.

Regards,

Davi

-- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe:    http://lists.mysql.com/internals?unsub=weldon@whipple.org