I had a problem recently when I had to copy a pretty big InnoDB database from one server to another.
Doing import-export through MySQL dump was taking forever, so I found this easy way to do that.
- First of all, stop MySQL on both servers (service mysql stop)
- You need to copy several files in your MySQL DB folder. Mine was /var/lib/mysql/
- Locate the folder with the name of your database and copy that. I highly recommend rsync for copying files between two servers.
- Copy ibdata1, ib_logfile0, ib_logfile1, mysql_upgrade_info
- Start MySQL (service mysql start)
That’s it! Now you have your entire DB copied and it probably took 100x less time.
Please note that you must have the same architecture on both machines. I.e. if you have 32-bit on one server and 64-bit on another – you will have to use MySQL dump.