How can I extract data from Wordpress MySQL files?

May 9, 2008 3:47 PM

Is there any way to restore Wordpress MySQL tables from the Unix directories? I don't have a database backup. If a restore of the tables is impossible, can I at least recover the text of my blog entries?

An unfortunate Ubuntu meltdown required a re-install of my server. I saved /etc, /home and /var, thinking I'd got everything. Alas! I forgot two important details: I didn't perform a MySQL database backup; and I didn't realise that Wordpress on Ubuntu saves stuff into /usr/wordpress. *%#$&!!!

After I spent a little time cursing Ubuntu's Wordpress for putting user-modified files into /usr, I remebered that the text of the entries and comments is in the database. I have a full backup of /var/lib/mysql so I should at least be able to extract the text of the blog entries -- right? Or am I completely screwed?

I'm proficient in Unix and C but know very little about databases.

posted by phliar to computers & internet (6 comments total)

If the db is still in /var/lib/mysql (which is where Ubuntu should put it) mysqldump should help you out. Though you may not need it to restore WP: just rebuild the wp-config.php file with the appropriate db name, username and password.
posted by holgate at 4:21 PM on May 9

Adding to holgate's comment, if you get MySQL working again on that or even on another machine then the files in /var/lib/mysql should be enough to get the database going again if they were indeed backed up and didn't get missed because of a "file in use" type problem.

I'm no expert and I know there are different types of tables in MySQL but the default seems to be that there is a directory for each database (same name as the db) and a file for each each table and index.

It's possible that all databases might come back if you simply restore /var/lib/mysql on a new install of MySQL but ... perhaps a cleaner way is to get a new MySQL working with no user databases, then create an empty database of the correct name using the CREATE DATABASE statement. That will create the directory. Then put the old data files in that new directory. That will ensure that the metadata is created properly in the master database which is called mysql. That worked for me a while back in similar circumstances. All the tables came back.
posted by tetranz at 5:23 PM on May 9

If you have all the MySQL files, from /var, then you still have the database. All your info is contained in those files in /var/lib/mysql/{dbname} therefore, a reinstall of WP on the new system, then just fart the old db files into the directory, overwrite the new db of the same name, and you will be looking good again. Your themes are gone though :/ any additional modules, I dunno how WP runs.

I had to do this with a WebGUI install once (Don't EVER use webGUI). Worked well.
posted by br4k3r at 7:27 AM on May 11

Apparently when I made my backup of /var/lib/mysql the database was still running, so now MySQL refuses to open the database. (This also means editing wp-config.php is fruitless.)

Any way to repair the database? How do people recover the DB if their MySQL machine crashed? (The DB equivalent of fsck is what I need, I think.)

Any way to dump all (ASCII) text in the database?
posted by phliar at 3:40 PM on May 12

I don't know about repairing the db but I doubt the fact that MySQL was running would stop them being usable. I don't really know ... but ... I had an rsync backup of a machine and MySQL was definitely running at the time and the rsynced files were totally usable. Perhaps it depends if any queries were running at the time so I might be talking nonsense but ...

Have you tried my suggestion of creating an empty db of the same name and then copying the files into that directory?

I know you said you didn't know much about databases so ... (say the Wordpress db is called wordpress, substitute as required)

Make sure the mysql server is running and then type mysql (enter). That starts the client. If it's a new install then there is probably no root password. If there is then you need to do something like:

mysql -u root -p
It will prompt you for a password.

Once you have a mysql> prompt, type SHOW DATABASES; (enter)
If it's a new install wordpress will not be there.
Now try CREATE DATABASE wordpress; (enter)
SHOW DATABASES; should now list wordpress.
quit (enter)
A directory of the same name will now exist in /var/lib/mysql
Try copying the files from the backup of that directory into that directory.
Now go back into the mysql client and type use wordpress; (enter).
SHOW TABLES;
Hopefully it will show some tables;
I don't know what tables WP has but say there's a table called articles, try something like
select * from articles; (enter) and see what happens.

I'm going by memory but I'm pretty sure I just copied the data files into the directory, set file permissions, restarted the client and did the use command and the tables suddenly appeared. Perhaps I restarted the server too but I don't think it needed anything else. Looking at a working MySQL db now, it seems like permissions should be 660. On my RedHat server, user and group are mysql.

If it seems to work then you should setup a user by typing something like this:

GRANT ALL ON wordpress.* TO myuser@localhost IDENTIFIED BY 'mypassword'

I think that's it or just use root to get things going.

Good luck.
posted by tetranz at 7:05 PM on May 12

Hooray! Thank you tetranz, that worked. After entering "SELECT * FROM wp_posts;" it printed out a whole bunch of stuff to stdout, text from the blog posts. It looks like the database is saved.

Unfortunately wordpress still can't use the database. From the error message on the web page ("Unknown column link_count in where clause") suggests there's a version problem, this version of wordpress incompatibly newer than the one that created the database. At least I know where to start looking next...
posted by phliar at 11:33 AM on May 13