I had a problem migrating the large mysql databases from one server to another. When ever i tried to do that using some utility I got “Out of Memory” error.
After a bit of research work, I realized that big databases can be migrated using shell command easily
Below you can find instructions to move database from one hosting provider to another:
1. Make database backupThe following shell command will dump whole database into file “dump.sql”: shell> mysqldump -u [uname] -p [dbname] > [dump.sql] It will prompt you for database password. Type in the password OR
shell> mysqldump –host=[hostname] –user=[username] –password=[passowrd] [database_name] > dump.sql
2. Back up the database
Pack that backup into archive (for example – tar.gz). The following shell command will pack file “dump.sql” into archive “dump.tar.gz”:shell> tar -czvf dump.tar.gz ./dump.sql OR (for TAR that doesn’t support ‘z’ option):
shell> tar -cvf – ./dump.sql | gzip -c > dump.tar.gz
3. Transfer the archived from one hosting provider to another using FTP or anything else.
4. Unpack that backup archive at new server.shell> tar -zxvf ./dump.tar.gz
OR (for TAR that doesn’t support ‘z’ option): shell> gunzip < ./dump.tar.gz | tar -xvf –
5. Restore database from dump file using native MySQL command-line tool – mysqlhttp://blog.technointellects.com/how-to-restore-compressed-mysql-backup-file/