2

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.

migrated from stackoverflow.com Nov 3 '15 at 21:33

This question came from our site for professional and enthusiast programmers.

1

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]
0

No, MySQL Workbench doesn't lock your database for read or write.

  • 3
    While a simple "No" might be enough (provided it's true), your answer would be much more educative if you elaborated a little on what happens during the migration process so that it is clear why one doesn't need to worry whether the database could be locked. – Andriy M Nov 4 '15 at 10:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.