LinuxQuestions.org
Go Job Hunting at the LQ Job Marketplace
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
 
LinkBack Search this Thread
Old 01-02-2005, 09:47 AM   #1
bifur
LQ Newbie
 
Registered: Nov 2003
Posts: 23

Rep: Reputation: 15
Backing up MySql


Recently I screwed my server up by changing a permission and then it got worse and worse with my tinkering. Anyway, I am going to reinstall Linux (needed to get newer mandrake anyway), but ran into a problem backing up mySql databases. I cannot connect properly to use mysqldump (in fact, I cannot connect to any user besides root via ssh). I found my database directory (/var/lib/mysql/the_dir_I_need). I tarred that directory and was wondering if that would be good enough for a backup? Can I just untar the directory after clean install? Any input would be great.

Bif
 
Old 01-02-2005, 09:56 AM   #2
trickykid
Guru
 
Registered: Jan 2001
Posts: 24,133

Rep: Reputation: 194Reputation: 194
It should work but never a garauntee doing it that way. I've had to do it myself that way before, didn't run into any problems.
 
Old 01-02-2005, 10:00 AM   #3
vinu_shu
LQ Newbie
 
Registered: Sep 2004
Location: pune India
Distribution: Redhat ES
Posts: 8

Rep: Reputation: 0
Hello,

Just untarring will not help you.

There must be some other way to do that. Check

http://dev.mysql.com/doc/mysql/en/Backup.html
 
Old 01-02-2005, 10:06 AM   #4
ttolst
Member
 
Registered: Jan 2005
Location: Århus, Denmark
Distribution: Debian GNU/Linux
Posts: 34

Rep: Reputation: 15
The best way of backing up any SQL database, is by exporting it. If you are doing a filesystem dump, you must make sure that the database isn't running, or you are getting a copy of a possible inconsistent database. (not that mysql really does much to make sure it is consistent in the first place, some will claim that mysql isn't really a RDBMS at all)

The backup command for mysql is named mysqldump

In its simplest form it works something like this:

mysqldump -a -u user -p database > export_file.sql

It generates a bunch of SQL queries that can be used to recreate the database as it was, so you can simply use the mysql command to restore it. You can also, just by editing it, restore only parts of it.
 
Old 01-02-2005, 10:09 AM   #5
trickykid
Guru
 
Registered: Jan 2001
Posts: 24,133

Rep: Reputation: 194Reputation: 194
Quote:
Originally posted by ttolst
The best way of backing up any SQL database, is by exporting it. If you are doing a filesystem dump, you must make sure that the database isn't running, or you are getting a copy of a possible inconsistent database. (not that mysql really does much to make sure it is consistent in the first place, some will claim that mysql isn't really a RDBMS at all)

The backup command for mysql is named mysqldump

In its simplest form it works something like this:

mysqldump -a -u user -p database > export_file.sql

It generates a bunch of SQL queries that can be used to recreate the database as it was, so you can simply use the mysql command to restore it. You can also, just by editing it, restore only parts of it.
And if you read his post, he states clearly that the given command to do it properly isn't working, he's trying to find alternatives.
 
Old 01-02-2005, 10:18 AM   #6
bifur
LQ Newbie
 
Registered: Nov 2003
Posts: 23

Original Poster
Rep: Reputation: 15
Thanks for the replies. I know the correct way to backup my databases, but seeing I can't connect it is useless. I'll definitely hope that just backing up the directory will work this time (and now I know to make daily backups hehe). Thanks, everyone!
 
Old 01-02-2005, 10:33 AM   #7
ttolst
Member
 
Registered: Jan 2005
Location: Århus, Denmark
Distribution: Debian GNU/Linux
Posts: 34

Rep: Reputation: 15
Whoops, i must have skipped the middle part :-)

As already stated above, it should work simply untarring it if you have everything, it probably wont even know you did it :-)
 
Old 01-02-2005, 10:47 AM   #8
trickykid
Guru
 
Registered: Jan 2001
Posts: 24,133

Rep: Reputation: 194Reputation: 194
Quote:
Originally posted by bifur
Thanks for the replies. I know the correct way to backup my databases, but seeing I can't connect it is useless. I'll definitely hope that just backing up the directory will work this time (and now I know to make daily backups hehe). Thanks, everyone!
Like I said, just grabbing the db directories out of /var/lib/mysql should work, though I've never grabbed the default mysql db, that I just recreated with a new install/new host, etc.

The worst that can happen is you lose some data or some is corrupt, easily just removing and rebuilding, etc.
 
Old 01-05-2005, 07:30 PM   #9
bifur
LQ Newbie
 
Registered: Nov 2003
Posts: 23

Original Poster
Rep: Reputation: 15
It worked fine, thanks again.

Bif
 
  


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
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Backing up MySQL without the dumping jme Linux - Software 8 05-27-2006 12:01 AM
mysql error Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. Dannux Linux - Software 3 03-24-2006 08:44 AM
Backing up mysql without it actually being on c00l Linux - General 1 03-12-2005 01:03 PM
backing up MySQL dbase DropHit Linux - Software 5 05-04-2004 03:19 PM
Backing up large MySQL DB's WeNdeL Linux - Software 5 11-29-2003 09:34 AM


All times are GMT -5. The time now is 05:23 PM.

Main Menu
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