Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

i have this situation

  • 2 large databases on mysql server - srv1

  • I need to transfer both to new server - srv2

so

  • I use sql command "FLUSH TABLES WITH READ LOCK;" to stop all modifications
  • Then I use shell command "mysqldump --user=root --password=blaablaaa --databases shop1 shop2 > "e:\backup\dump_both.sql"

  • I am completely stuck here because only SHOP1 database is dumped right and then file size stops growing and for long time nothing happens. Why - its exactly in a moment when second database should be dumped.


i have tried to unlock databases and then BOTH databases can be dumped perfectly, but while databases are locked only one can be dumped. Of coourse i need to lock them because of consistency. But why second database cannot be dumped in this situation ?

any ideas ?

note1: shop1 database uses only myisam tables, but shop2 mostly uses innodb.

share|improve this question
add comment

migrated from stackoverflow.com Nov 9 '11 at 2:42

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

1 Answer

The FLUSH TABLES WITH READ LOCK should be executed within the backup session. Try to backup MyISAM tables with a --flush-logs and a --lock-all-tables options; and try to backup InnoDb tables with a --single-transaction option. More information in the documenation - mysqldump — A Database Backup Program.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.