Easiest Way To Create A MySQL Database Copy

Want to help support this blog? Try out Oh Dear, the best all-in-one monitoring tool for your entire website, co-founded by me (the guy that wrote this blogpost). Start with a 10-day trial, no strings attached.

We offer uptime monitoring, SSL checks, broken links checking, performance & cronjob monitoring, branded status pages & so much more. Try us out today!

Profile image of Mattias Geniar

Mattias Geniar, September 03, 2008

Follow me on Twitter as @mattiasgeniar

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.



Want to subscribe to the cron.weekly newsletter?

I write a weekly-ish newsletter on Linux, open source & webdevelopment called cron.weekly.

It features the latest news, guides & tutorials and new open source projects. You can sign up via email below.

No spam. Just some good, practical Linux & open source content.