LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - General
User Name
Password
Linux - General This Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.

Notices

Reply
 
Search this Thread
Old 10-27-2008, 09:15 AM   #1
compused
Member
 
Registered: Oct 2006
Location: Melbourne Australia
Distribution: centos and redhat 8
Posts: 76

Rep: Reputation: 15
mysql database restore help


I have restored a mysql database, named pbcs7, from a backup that was created via a tar command, but the data is not there.
The database program - PBCS which is an appointment system - resides in /home/www/public_html and the mysql database is in /var/lib/mysql

It all started when I deleted one of the users, 'michael', that had admin rights, from one group and lo and behold 'michael' disappeared from all groups as did the data. My other admin user was still there, but the data belonged to 'michael'

Steps for restoration of the backup (permissions were found to be preserved):
1. the backed up 'pbcs7' database files were temporarily restored using the tar -zxvf command to:
/var/lib/restore/var/lib/mysql/pbcs7/

2. made copy of files in use (ie the files from which data was missing) to...restore2 directory
cp -a /var/lib/mysql/pbcs7/* /var/lib/restore2/

3. /sbin/service mysqld stop

4. restored pbcs7 database files to the working mysql directory
cp -a /var/lib/restore/var/lib/mysql/pbcs7/* /var/lib/mysql/pbcs7/

5. /sbin/service mysqld start

Any help would be great
Michael
 
Old 10-27-2008, 10:57 PM   #2
trickykid
Guru
 
Registered: Jan 2001
Posts: 24,133

Rep: Reputation: 197Reputation: 197
Okay, was your backup an actual mysqldump backup or did you just simply copy the directory for the database from /var/lib/mysql?

From the looks, seems like you probably just backed up the files from /var/lib/mysql from a live database, which is going to give you unreliable backups.

In order to get good backups of unchanged data while a mysql database is running is to either use mysqldump and lock the tables during the dump or you need to shutdown the mysql database, then backup the files in /var/lib/mysql, then restart the database.

What do you restore files look like? You may need to drop the database from within mysql first, then copy over the files from a backup if they actually have data within them. If that doesn't work, you probably got a corrupt backup cause you didn't back them up properly.
 
Old 10-27-2008, 11:29 PM   #3
compused
Member
 
Registered: Oct 2006
Location: Melbourne Australia
Distribution: centos and redhat 8
Posts: 76

Original Poster
Rep: Reputation: 15
Thanks
I have a backup script that stops mysql, does the backup then restarts mysql. The 'restore' files look ok...

'Dropping' the database from within mysql ...can you advise how to do that safely? I have phpmyadmin or feel confortable with command line

Regards
Michael
 
Old 10-28-2008, 04:48 PM   #4
trickykid
Guru
 
Registered: Jan 2001
Posts: 24,133

Rep: Reputation: 197Reputation: 197
mysql> drop database <database name>

There's no safe way to delete a database since you're dropping it from existence. Before you drop it, I'd suggest making sure your backed up copy is good and works, maybe testing on another system first.

And you might want to look into using mysqldump instead of doing cold backups of the files themselves.
 
  


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 On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can i copt the MYSQL Database to a other server (Database) halvorls Linux - Server 3 07-27-2008 09:19 PM
Restore Database RAH104 Linux - Server 3 07-05-2008 09:49 PM
mysql database restore problem sajith Programming 1 07-01-2007 06:36 AM
to restore the database Ameii83 General 3 06-09-2006 04:11 AM
How do I restore a MySQL database simply dobriain Linux - Software 2 04-19-2006 12:05 PM


All times are GMT -5. The time now is 12:08 AM.

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