Here’s just about the easiest way of copying a MySQL database from one server to another, or from one database to another – through the command line. This is the favored way when your database exceeds a certain size, because scripts such as PHPMyAdmin fail to import sql-dumps that are too large.
Log in on your server, and create a new folder to store our MySQL dump.
[root@vps ~]# mkdir mysql-dump
[root@vps ~]# cd mysql-dump/
[root@vps mysql-dump]#
Start the MySQL Dump.
[root@vps mysql-dump]# mysqldump -u site_db_user -B site_db -p > database_dump.sql
Enter password:
Quick explanation:
- **–**u
: define the user to log into MySQL and perform the back-up. This user needs the correct priviliges. - -B
: the “-B” adds the “CREATE DATABASE” statement to our dump, so we don’t have to create it first on the other server - -p: this allows us to enter our password. You can also append it straight after the “-p” (such as: “-pMYPASS”), but this makes your password visible in the history, and to whoever is standing behind you. Just “-p” allows you to enter the password discretely.
- > database_dump.sql: store the output of the MySQL Dump to a file called “database_dump.sql”.
Copy the file to your other server (move it to a FTP-directory and download it, place it in a public folder and browse to it, …) and navigate to the folder.
[root@vps mysql-dump]# mysql -h localhost -u new_site_db_user -p < database_dump.sql
Enter password:
Again, a quickie:
- -h: define the host to which you want to connect, by default this is “localhost”, but you could use it to connect directly to a MySQL server on another server – just enter the correct hostname/ip.
- -u
: the username used to log in to MySQL and create the database. User needs correct priviliges. - -p: same as above, enter a password for the database-user
- < database_dump.sql: import the contents of database_dump.sql (which we just created) to MySQL – any statement in that file will be executed (CREATE DB’s, INSERT’s, …)
Could also be combined to the following:
mysqldump -u site_db_user -B site_db | mysql -h localhost -u new_site_db_user -p
This will send the output of the MySQL Dump directly to a new MySQL connection – without storing it in a local file first.