moving an entire mysql database…
There comes a time when it is necessary to move servers... and it's related services and databases... it was time for me to move onto bigger and better ... server that is. Over the years, we have tried out and customized few things within the web server which ended up getting big ... in the process mysql database also gotten bit big... so to migrate from one to the other... I had to use the dumpmysql but standard usage of mysql dump resulted in whole bunch of errors including errorno: 24 which was result of large database.
But this can be avoided by using the flag –lock-tables=false into the command line.
$mysqldump --opt -u [user.ID] -p -A --lock-tables=false > [dump.filename]
After that, it was easy as just moving file from one server to another using any secure method you like and using following command to restore it on to the new server...
mysql -u root -p[root_password] [database_name] < dumpfilename.sql
Note: if no database exist in your new server [database_name] can be omitted.
And that's it, your new database is all new and shiny...
Reference: http://jamielesouef.com/linux/tip-backing-up-a-large-mysql-database-errno-24/