Download Wordpress Database

October 15, 2016 75 views

Hi all - I'm a newbie to all of this. I know enough to be dangerous. Right now, I'm downloading a copy of my wordpress site so I can mess around with it locally without affecting anything live. I'm in the process of trying to download MAMP and mess with that, but one of the steps is to download the "database". I read the instructions here:

On the first step, "where" exactly does it put it on my "droplet"? I'm running it on Ubuntu. I'm not sure if I can ftp it out of my droplet. Any clarity would be appreciated.


3 Answers

Hi @sengerson,

What you need to do is backup of wordpress MySQL database, and put it anywhere you want on Droplet. After that, you need to download it to your local machine using scp or rsync.

First of all - SSH to your Droplet.
Command we will use for backup is called mysqldump. It is taking "dump" of database in .sql format, from which you can create database with same data on local machine.
Execute following command:

  • mysqldump -u sammy -p wordpress > ~/wordpress.sql

Change sammy with MySQL username which you want to use. If everything is successful it will create wordpress.sql in your home folder.

Now exit SSH session.

First download file you created. You can use scp or rsync.
Note: Follow only for scp or only for rsync, basically it is same, but I'll write both for reference.
In case you want to use scp:

  • scp -r sammy@droplet-ip:~/wordpress.sql wordpress.sql

This command will download wordpress.sql from Droplets home directory to your current directory.
Same could be done with rsync:

  • rsync -r sammy@droplet-ip:~/wordpress.sql wordpress.sql

I'm assuming you're using MAMP on OS X, so steps are for it.
This article on MAMP blog says how to login to MySQL on it, so I'm basing following steps on it.

First step is on local machine is to login on MySQL using steps from following link.
If it is successful we will start with creating database for WordPress.

  • CREATE DATABASE wordpress DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

It will create database. Now we need to create user for it.

  • GRANT ALL ON wordpress.* TO 'wordpressuser'@'localhost' IDENTIFIED BY 'password';

For wordpressuser and password make sure it is matching username/password as is it on your Droplet. Or if you want to change it, make sure you change it also in wp-config.php.
If you want to change it, or verify current settings, go to your WordPress directory and open that file with any text editor.
There you will find following lines:

/** MySQL database username */
define('DB_USER', 'wordpressuser');

/** MySQL database password */
define('DB_PASSWORD', 'password');

So it says what is your username and password for database, make sure it matches with above query.

Now let's update MySQL privileges with this simple command:


And exit MySQL shell with

  • EXIT;

Now we need to extract backup we made earlier. Open wordpress.sql you downloaded from server with any text editor. On beginning of file (first line) add following line:

USE wordpress;

Save it and exit editor.
Now let's extract it. If you used following command to login to bash:

  • /Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot

Instead it you want to execute now:

  • /Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot < wordpress.sql

Make sure you have wordpress.sql in current directory. :)

After that, WordPress should be working on your local machine. I hope it is not late answer, anyways it can stay here for future. :)

Hi @xMudrii,

Thanks so much for the detail! When you say "SSH into the droplet", is that just using terminal to access the droplet?

I'm getting a -Bash: mysqldump: command not found

Do i need to change my folder?

  • This is strange. I used this command on many servers and never had similar problem.
    This is what happens when I execute mysqldump on mine box:

    ➜  ~ mysqldump
    Usage: mysqldump [OPTIONS] database [tables]
    OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
    For more options, use mysqldump --help

    Make sure you SSH-ed to Droplet.

    On Droplet you can try running it as root:

    • sudo mysqldump

    You can try "searching" for that command with:

    • which mysqldump

    This probably can return not found error, but maybe it will return location of mysqldump.

    Try looking for it on default location: /usr/bin/mysqldump, e.g. instead of mysqldump -u sammy -p wordpress > ~/wordpress.sql execute:

    • /usr/bin/mysqldump -u sammy -p wordpress > ~/wordpress.sql

    Did you remember how you got this MySQL installation? Did you used some of DigitalOcean LAMP tutorial or maybe One-Click App?

Have another answer? Share your knowledge.