Mysql database migration using shell command

July 11, 2008 · Filed Under Linux / Unix Commands, Mysql · 2 Comments 

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 backup

The 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 - mysql

shell> mysql –host=[hostname] –user=[username] –password=[password] [database_name] < dump.sql
To view how to restore mysql from compressed mysql file Pleasit visit http://blog.technointellects.com/how-to-restore-compressed-mysql-backup-file/

Tags: , ,