Mysql database migration using shell command

Spread the love
  •  
  •  
  •  
  •  
  •  
  •  

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/

Spread the love
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

2 comments:

  1. hi,

    I thank to this tuto. It’s helpful, but my problem is to backup mysql to local and I don’t know how to do that.

    Beg you help me please

    Regards

Leave a Reply

Your email address will not be published. Required fields are marked *