LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices



Reply
 
Search this Thread
Old 02-12-2005, 06:14 AM   #1
jme
Member
 
Registered: Sep 2003
Location: Cardiff, UK
Distribution: Debian
Posts: 49

Rep: Reputation: 15
Backing up MySQL without the dumping


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.
 
Old 02-12-2005, 06:22 AM   #2
kevinatkins
Member
 
Registered: Jan 2004
Location: cheshire, uk
Distribution: Ubuntu Hoary
Posts: 605

Rep: Reputation: 32
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...
 
Old 02-12-2005, 06:27 AM   #3
jme
Member
 
Registered: Sep 2003
Location: Cardiff, UK
Distribution: Debian
Posts: 49

Original Poster
Rep: Reputation: 15
Exellent.

Just wanted to check before I relied on it!

Many thanks.

Jamie
 
Old 04-15-2006, 11:35 PM   #4
JerryMcFarts
Member
 
Registered: Mar 2004
Location: Ohio, USA
Distribution: Ubuntu 6.04
Posts: 117

Rep: Reputation: 15
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.

Last edited by JerryMcFarts; 04-15-2006 at 11:36 PM.
 
Old 04-16-2006, 03:37 AM   #5
demoman
LQ Newbie
 
Registered: Nov 2005
Posts: 18

Rep: Reputation: 0
Post

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.

Last edited by demoman; 04-16-2006 at 03:44 AM.
 
Old 04-16-2006, 04:49 AM   #6
JerryMcFarts
Member
 
Registered: Mar 2004
Location: Ohio, USA
Distribution: Ubuntu 6.04
Posts: 117

Rep: Reputation: 15
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
 
Old 04-16-2006, 03:00 PM   #7
JerryMcFarts
Member
 
Registered: Mar 2004
Location: Ohio, USA
Distribution: Ubuntu 6.04
Posts: 117

Rep: Reputation: 15
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
 
Old 04-17-2006, 05:07 PM   #8
JerryMcFarts
Member
 
Registered: Mar 2004
Location: Ohio, USA
Distribution: Ubuntu 6.04
Posts: 117

Rep: Reputation: 15
Question

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?

Last edited by JerryMcFarts; 04-17-2006 at 05:08 PM.
 
Old 05-27-2006, 01:01 AM   #9
JerryMcFarts
Member
 
Registered: Mar 2004
Location: Ohio, USA
Distribution: Ubuntu 6.04
Posts: 117

Rep: Reputation: 15
alright couldn't fix it. Reinstalled.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
dumping mysql db script with dates markehb Linux - Software 2 04-23-2006 11:11 PM
Novell is dumping KDE, so I'll be dumping SuSE KimVette Suse/Novell 10 11-12-2005 09:09 PM
Backing up mysql without it actually being on c00l Linux - General 1 03-12-2005 02:03 PM
Backing up MySql bifur Linux - Software 8 01-05-2005 08:30 PM
backing up MySQL dbase DropHit Linux - Software 5 05-04-2004 04:19 PM


All times are GMT -5. The time now is 11:30 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration