Posted: 2014-06-10 20:29:49
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).
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”.
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.
* 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
For wordpress, and other webapps, you can find the name, host, and password in the webapp’s configuration file.
* WinSCP is a good FTP client if you need to transfer the files.