Database Corruption

larryayers
(@larryayers)

19 years, 2 months ago

Anyone ever have their Mysql wordpress database get corrupted? Mine did last night and I could find no way to repair the damage. I ended up creating a new database and re-installing WP. Then spent three hours extracting 65 posts, about 30,000 words worth, from the corrupted post database file with a text editor and, one by one, recreated the blog postings. What a pain!

It made me wonder if one post database file per month might not be a good idea rather than just letting the single file grow and grow.

Viewing 8 replies - 1 through 8 (of 8 total)

philpeeps
(@philpeeps)

19 years, 2 months ago

I have a couple cron jobs set to do nightly backups of my databases…you might look into something similar….

I just go through about 1x week and delete the old ones….

DianeV
(@dianev)

19 years, 2 months ago

philpeeps, that’s a great idea. Would you care to post the syntax of your shell script?

philpeeps
(@philpeeps)

19 years, 2 months ago

date=’date -I’; mysqldump -u db_username -pdb_password db_name > /home/cpanel_login_name/public_html/backups/wp/xbackup_$date.sql; gzip /home/cpanel_login_name/public_html/backups/wp/xbackup_$date.sql

around date-I it should be backticks, but this forum won’t show them

There is no space between -p and your db_password. Ther eIS a space between -u and db_username….

Thread Starter larryayers
(@larryayers)

19 years, 2 months ago

Hey, philpeeps, I just tried out your script (after my disastrous corruption incident yesterday evening) and it works like a charm! I’m still a Mysql beginner and didn’t know about mysqldump, but thanks to you now I do. Now to set up a cron routine…

e-head
(@e-head)

19 years, 2 months ago

If your blog gets tons of traffic, or you have many bloggers, use mysqlhotcopy.

It is the safer way of backing up “hot” databases. That is, databases that might be currently “in use” by a commenter or a blogger.

philpeeps
(@philpeeps)

19 years, 2 months ago

so use the same syntax, just replace mysqldump with mysqlhotcopy?

DianeV
(@dianev)

19 years, 1 month ago

Here’s a *nix cron shell script

click the Cron and Database Backups link
http://developedtraffic.com/resources/mysql-database.php

I’d give the direct link to Cron and Database Backups, but I’m probably going to move that to a different page, as I’m seeing good tech stuff buried wherever in the blog.

Moderator James Huff
(@macmanx)

Volunteer Moderator

19 years, 1 month ago

Just a thought, but you might want to read through “repair your tables” at: http://www.tamba2.org.uk/wordpress/