This is for Linux (specifically Debian/Ubuntu)

There is more than one way to do this. What I’m concerned with here is not so much getting the database on your home computer’s hard drive, but verifying the database (like, seeing inside it to make sure your stuff is really there) and using it.

The database is a mysql database. So use cPanel to back it up. cPanel will have a backup utiliyt, or you can use the phpMyAdmin utility if it is there.

in phpMyAdmin, use “Export” on the database you want to back up. Select sql as the output format, select all the tables unless there are some tables you don’t want to back up (if you don’t know what this means, select all the tables).

Under the SQL selection, tick on the folowing options: “structure”, “Add DROP TABLE”, “Add AUTO_INCREMENT”, and “Enclose table and field names wiht backquotes”

Leave everything inside the DATA section unchecked, but keep the DATA heading itself checked.

Select “Save as file” down near the bottom of the form. Pick compression if you want. Don’t mess with the template name.

No pick “go”. Your computer may prompt you with a choice to “open as” vs. “download” or “Save”. Save/download the file.

Now, everything BUT YOUR IMAGES are backed up.

For large files, use the cPanel backup utility or some other method rather than php’s admin utility.

Now, the following is the important part. This is how you get to see inside the database.

You have to have mysql installed. If you don’t, do that using synaptic package manager. Search for “mysql admin” and install the apropriate metafile that will install all other files (something like “mysql-5.0-bla-bla-bla”)

When this installs, you will be prompted for a password that will be used as your mysql root password (not to be confused with your linux system root password). Let’s call it, for the present purposes, “myPassword”.

Now, here comes the magic commands that will make this work. If the following commands don’t work, then something is wrong (probably nothing too terrible) and you must go back to the Google search that brought you to this page and try somewhere else for the answer.

In a terminal, type in:

mysqladmin -u root -p create wpbackup

where “wpbackup” is the database name you want to use to access your wordpress data.

the -u means “user” and you are doing this as user ‘root’. the ‘-p’ is telling mysql to get a passord input for root.

After you type this in you will be prompted for the password you entered before. myPassword. Enter that.

Now, you have created a blank database called wpbackup. You need to magically suck the data from the backup file that was downloaded into this file. Why? Why not just use the original backup file? Who the hell knows! Just do it. Like this:

mysql -uroot -p wpbackup < DownloadedBackupDatabase

where DownlaodedBackupDatabase is the database you downloaded as your backup. You will be prompted for the password.

The less than sign causes the downloaded database backup to be sucked into the wpbackup database using the program “mysql”. It really is magic.

If nothing seemed to happen but a little time went by, it probably worked. Now type in:

mysql -u root -p

This will put you in mysql’s interactive command prompt mode as root, after you’ve entered the password.

Now, type in

USE wpbackup

if the database is there, and it should be, mysql will tell you some stuff that you don’t care about.

Now try something like:

SHOW TABLES;

and you’ll see a list of wp tables (starting with wp_). That is your word press database! To verify that your carefully crafted posts are really there, try this:

SELECT * FROM wp_posts;

… and that will print out the text of every one of your posts. This could take a while. If you get bored, just minimize the terminal or turn it off. At the end it will tell you how many rows were in the database, and that should be the same as the number of posts in your blog. Presumably.

For more information, try this page.