Mysql – Does MySQL Workbench lock database during migration process

innodbmigrationMySQLmysql-workbench

I am doing a migration of my database from one server (RDS) to another (localhost) via the MySQL workbench tool. I want to know if during this process my database will be locked for write and read operations? The type of my tables is InnoDB.

Best Answer

EDIT:

I guess I deserved the -1 for my brevity and incomplete (i.e. wrong) answer. I was on the phone and had to keep it quick.

Let me try again.

I did a dry run for you on two test servers using the MySQL Workbench 6.3 CE migration wizard.

With the default options the migration is -non- locking. This vanilla method has a downside however. If you migrate while other DML transactions are going on, you might get an inconsistent data set on the destination, as it will not migrate a consistent snapshot as seen in the statement below. This risk is designed to achieve the non locking nature.

Here is an excerpt of the general log for the vanilla run:

151105  7:51:09 10398 Connect   root@my_host_name.com as anonymous on 
    10398 Query SET NAMES 'utf8'
    10398 Query USE `test_db_01`
    10398 Query SELECT count(*) FROM `t2`
    10398 Query USE `test_db_01`
    10398 Query SELECT count(*) FROM `t1`
    10398 Quit  
    10399 Connect   root@my_host_name.com as anonymous on 
    10399 Query SET NAMES 'utf8'
    10399 Query USE `test_db_01`
    10399 Query SELECT count(*) FROM `t2`
    10399 Query USE `test_db_01`
    10400 Connect   root@my_host_name.com as anonymous on 
    10400 Query SET NAMES 'utf8'
    10400 Query USE `test_db_01`
    10400 Query SELECT count(*) FROM `t1`
    10400 Query USE `test_db_01`
    10400 Prepare   SELECT `id`, `intcol1`, `intcol2`, `intcol3`, `intcol4`, `charcol1`, `charcol2`, `charcol3`, `charcol4`, `charcol5`, `charcol6`, `charcol7`, `charcol8`, `charcol9`, `charcol10` FROM `t1` ORDER BY `id`
    10399 Prepare   SELECT `id`, `intcol1`, `intcol2`, `intcol3`, `intcol4`, `charcol1`, `charcol2`, `charcol3`, `charcol4`, `charcol5`, `charcol6`, `charcol7`, `charcol8`, `charcol9`, `charcol10` FROM `t2` ORDER BY `id`
    10399 Execute   SELECT `id`, `intcol1`, `intcol2`, `intcol3`, `intcol4`, `charcol1`, `charcol2`, `charcol3`, `charcol4`, `charcol5`, `charcol6`, `charcol7`, `charcol8`, `charcol9`, `charcol10` FROM `t2` ORDER BY `id`
    10400 Execute   SELECT `id`, `intcol1`, `intcol2`, `intcol3`, `intcol4`, `charcol1`, `charcol2`, `charcol3`, `charcol4`, `charcol5`, `charcol6`, `charcol7`, `charcol8`, `charcol9`, `charcol10` FROM `t1` ORDER BY `id`
    10400 Close stmt    
    10399 Close stmt    
    10399 Quit  
    10400 Quit

Another option would be to use mysqldump. This will be -non- locking but, also create a consistent snapshop of the database.

Mysqldump --databases [db name] --user [user] --password --opt --triggers --routines --events --single-transaction | mysql -u[user] -p -h[host_address]
Related Question