How to Transfer a Local MySQL Database to Another Server

These instructions will show you how to export a single database from MySQL into an SQL file, that you can move/copy to the remote Host, and import it into that Host’s MySQL server. The local and remote Hosts can be either Windows or Linux (it will work identically).

Execute these commands into the command-line (Windows) or shell (Linux).

On Local Host

mysqldump --user=root --password db.name > db.name.sql

This will create and populate file \<working-directory-path>\db.name.sql with database named “db.name”.

On Remote Host

mysqladmin --user=root --password drop db.name
mysqladmin --user=root --password create db.name
mysql -u root -p db.name < \path\to\db.name.sql

This will delete and create database named “db.name”, and then will populate that database with data from SQL file \path\to\db.name.sql.

Notes

* Leave the “--password” and “-p” switch out if MySQL’s root password is not set. Otherwise, the password will be prompted for when you press Enter.

* Be careful copy/pasting character: ‘-’. It can be inadvertently changed to a special Unicode dash character (instead of the ASCII hyphen-minus) that’s indistinguishable, which will not work.

* Aside from the command-line, all this can also be done from phpMyAdmin.

* Also, this can be done automatically by saving the export and import commands into a BAT or SH file on each system, so you never have to redo/retype them, just click or execute one file.

* To create a user with permissions set to a database, execute:

mysql -u root -p
GRANT ALL PRIVILEGES ON `db.name`.* TO 'db.username'@'db.hostname' IDENTIFIED BY 'db.password';
exit

* WinSCP is a good FTP client if you need to transfer the files.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>