LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - General (http://www.linuxquestions.org/questions/linux-general-1/)
-   -   mysql database restore help (http://www.linuxquestions.org/questions/linux-general-1/mysql-database-restore-help-679332/)

compused 10-27-2008 09:15 AM

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

trickykid 10-27-2008 10:57 PM

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.

compused 10-27-2008 11:29 PM

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

trickykid 10-28-2008 04:48 PM

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.


All times are GMT -5. The time now is 08:55 PM.