Usually, web developer use local computer like development web server and after testing upload code/database to hosting provider server. Also, sometimes is needed to move database from one hosting provider to another.

Below you can find instructions to move database from one hosting provider to another:

• Make database backup using native MySQL database backup program — mysqldump. The following shell command will dump whole database into file "dump.sql":

  shell> mysqldump --host=[hostname] --user=[username]
         --password=[passowrd] [database_name] > dump.sql

• 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

  Example you can see at following screenshot:
Screenshot: Dumping MySQL DB data into file
• Transfer archive with dump file from one hosting provider to another using FTP or anything else, what do you prefer.
• Unpack that backup archive at provider web server. The following shell command will unpack archive "dump.tar.gz":

  shell> tar -zxvf ./dump.tar.gz
  OR (for TAR that doesn't support 'z' option):
  shell> gunzip < ./dump.tar.gz | tar -xvf -

• Restore database from dump file using native MySQL command-line tool — mysql. The following shell command will import data from file "dump.sql" into MySQL database using native mysql command-line tool:

  shell> mysql --host=[hostname] --user=[username]
         --password=[password] [database_name] < dump.sql

  Example you can see at following screenshot:
Screenshot: Importing data into MySQL DB from file
• In case, when your hosting provider don't give you access to native mysql command-line tool, you can try to use mysqlimport data import program to restore database from dump file.
  WARNING: For each text file named on the command line, mysqlimport data import program strips any extension from the filename and uses the result to determine the name of the table into which to import the file's contents.

To find more information about mysql, mysqldump, mysqlimport, tar, gzip, gunzip command-line parameters just execute following shell commands:

shell> mysql --help
shell> mysqldump --help
shell> mysqlimport --help
shell> tar --help
shell> gzip --help
shell> gunzip --help

Or if you want to read manuals — execute following shell commands:

shell> man mysql
shell> man mysqldump
shell> man mysqlimport
shell> man tar
shell> man gzip
shell> man gunzip

Any size MySQL databases can be moved easily, using that technology.

<— Bookmark this page