Backing up MySQL without the dumping

jme · 02-12-2005, 06:14 AM

I now have over 20 databases on my server and backing them up by manually dumping them into a file is becoming a chore so I was wondering if I am right in thinking that if I take a backup of /var/lib/mysql I will be backing up every database on the server.

Any feedback would be great.

Thanks all.

kevinatkins · 02-12-2005, 06:22 AM

Hi,

I think you're right in what you suggest. I have, in the past, just taken copies of the database subdirectories in /var/lib/mysql... restoring was as easy as just copying the directories + contents back again...

jme · 02-12-2005, 06:27 AM

Exellent.

Just wanted to check before I relied on it!

Many thanks.

Jamie

JerryMcFarts · 04-15-2006, 11:35 PM

Hi, I am in a situation where I need to manually backup my databases.

I am going to

Code:

sudo tar -zcvpf ./MySQL`date '+%Y%m%d'`.tar.gz /var/lib/mysql/

I was wondering.. after i format my computer. What are the steps to put the database back into place?

I was thinking this. I have a WordPress database and a Gallery database.

1.install Wordpress/Gallery
2. In Mysql assign the database that Wordpress/Gallery IS going to use the same name as I have backed-up in the /var/lib/mysql/
3.extract the tar file in the /var/lib/mysql/

is there any other steps? I don't know if i remember the user's and passwords to use the databases through the programs. Is that a problem?

thank you.

demoman · 04-16-2006, 03:37 AM

Jerry -

Not an answer to your questions - just a comment:

Using mysql dumps for backups has drawbacks and for a sysadmin who wants to be able to recover from a hardware failure then backing up the whole directory might be the way to go.

But for backing up a personal blog I'd argue that a mysql dump is a safer backup strategy. It's light (a few megs at most - can be emailed from scripts, placed on USB sticks...) but moreover it's portable - perfect for the day you want to switch ISP, OS, upgrade mysql versions etc. Also, no need to worry about remembering passwords, aligning users on new system, etc. I do this on a regular basis for my Wordpress install (and moved my blog around in more than one occasion):

Code:

#!/bin/sh
# Determine current date
CURDATE=`date '+%Y-%m-%d'`

# Backup all WP databases to compressed, dated file in home directory
mysqldump -q -e -hmydatabasehost.com -umyusername -pmypassword mydatabasename | gzip - > ~/BACKUP/${CURDATE}_wordpress_db.sql.gz

# Backup WP files to compressed, dated file in home directory
cd ~/myblog/
tar cf - . | gzip - > ~/BACKUP/${CURDATE}_wordpress_files.tar.gz

Hope this will help.

JerryMcFarts · 04-16-2006, 04:49 AM

yea, see I was stupid before I did an upgrade, and i didn't back up. I did a force upgrade using apt-get, jumping from 5.04 to 6.04 and i got some socks error in my mysql and read up on it big time but none of the solutions worked.

plus I had no internet because it also broke my network card, lots of factors, and i need to complete a task at hand, before tuesday.

But Thank you for the response. Definitely in the future i will be more generous with my backups.

if anyone else knows about manual backups i would love to hear about it :-P Night

JerryMcFarts · 04-16-2006, 03:00 PM

I believe that Gallery2 the photo gallery that i am using keeps all its pictures in a user so i also am backing up that user.

I don't know if it works, i have all the pictures backup, but i don't know if the database will convert over correctly. So if all fails i will have to do everything manually

JerryMcFarts · 04-17-2006, 05:07 PM

OK I am having trouble now. I am leaving Gallery for now. I am working on putting my WordPress blog back into my new system.

this is what I have done.

Quote:

1. Backed up my WordPress Directory (tar)
2. Back up mysql database with tar /var/lib/mysql/WordPress
3. reformated, installed Ubuntu 6.04 as a server
4. installed apache2, php, mysql, etc.
5. untared my Wordpress directory in a virtual host
/var/www/mysite/blog/
6. Created a user and database (WordPress) that was exactly same as the old computer to run my WordPress
7. found the /var/lib/mysql/WordPress/
all it had was a db.opt file
6. stopped mysql
8. cp all the backed up mysql database files into /var/lib.mysql/WordPress
9. restarted mysql
10. went to my blog

all it says

Code:

It doesn't look like you've installed WP yet. Try running install.php.

any thoughts?

JerryMcFarts · 05-27-2006, 01:01 AM

alright couldn't fix it. Reinstalled.