Download Wordpress Database

My god. Why does every single DigitalOcean documentation page, whether their own or in the “Community Forums”, provide instructions that just plain don’t work?

What’s the Username if it’s not “sammy”? What’s the password? How do I get those? I picked ones I wanted to use and it said “mysqldump: Got error: 1045: Access denied for user ‘sammy’@‘localhost’ (using password: YES) when trying to connect”.

Geez, I wish I could just get working instructions on how to do this. Why does DigitalOcean make everything so hard?

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

Do i need to change my folder?

Hi @xMudrii,

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

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:

FLUSH PRIVILEGES;

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. :)