Wiped Database Recovery??

Discussion in 'General' started by corona, Aug 19, 2008.

  1. corona

    corona New Member

    I'm managing an ISPconfig system. A couple of hours ago I made a chown mistake on the /var/www folder. I found this post to fix it:

    I ran the mysql statements, added a user to a website, then deleted the user, and checked the file permissions, didn't seem completely fixed. Checked the database and it's now completely empty. no tables. no nothing.

    All services seem to be running still, and I can navigate around ispconfig and everything is still there, all the websites, all the users. Has anyone got any ideas what's going on, and how to recover from this?

  2. till

    till Super Moderator Staff Member ISPConfig Developer

    Which database is empty? If you can see the users and sites in ISPConfig, the ispconfig database is defiantely not empty.
  3. corona

    corona New Member

    well, ispconfigdb databse is empty, and I tried a backup from the management menu of ispconfig, and the downloaded file is basically empty:

    -- MySQL dump 10.11
    -- Host: localhost Database: ispconfigdb
    -- ------------------------------------------------------
    -- Server version 5.0.38-Ubuntu_0ubuntu1-log

    /*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
    /*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
    /*!40101 SET @[email protected]@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    /*!40103 SET @[email protected]@TIME_ZONE */;
    /*!40103 SET TIME_ZONE='+00:00' */;
    /*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;
    /*!40103 SET [email protected]_TIME_ZONE */;

    /*!40101 SET [email protected]_SQL_MODE */;
    /*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;
    /*!40014 SET [email protected]_UNIQUE_CHECKS */;
    /*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
    /*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
    /*!40101 SET [email protected]_COLLATION_CONNECTION */;
    /*!40111 SET [email protected]_SQL_NOTES */;

    -- Dump completed on 2008-08-19 7:46:55

    It's got me freaked out. Is there any way to verify where ispconfig is looking for it's data?

  4. corona

    corona New Member

    also, the "check database" and "repair database" functions in management aren't working, seemingly because it's not finding tables to run them on:

  5. till

    till Super Moderator Staff Member ISPConfig Developer

    The database login details are in /home/admispconfig/ispconfig/lib/config.inc.php

    If the websites are visible in ISPConfig, then they are dfinately there. maybe you changed the permissions in /var/lib/mysql too and not just in /var/www
  6. corona

    corona New Member

    actually I think the problem will be the permissions on the mysql database files will have been walked on too. Found them in /var/lib/mysql, do you know whether the owner of the files should be the same as the group (some mysql, some root)? Only the owner got accidentally modified, so group is the same as it should be.

    Thanks for your help,
  7. till

    till Super Moderator Staff Member ISPConfig Developer

    Owner should be user: mysql and group: mysql
  8. corona

    corona New Member

    oh, thank you so very much for your assistance, once I fixed the owner all the tables jumped back into existence in phpmyadmin. Now to do a full backup!!!

    Do you know of any add-ons or something for ispconfig that do nightly backups? I'm really wishing we had something in place to do that now.

    Thanks again,
  9. till

    till Super Moderator Staff Member ISPConfig Developer

    There are no addons for ISPConfig, yiu may use any linux backup program for your ispconfig server. You will find several howtos here om howtoforge if you search for backup.

    Personally I prefer the "old" way, just a shell script that extracts the databases with mysqldump or mysqlhotcopy and then tar to store them as archive. Also make a tar backup of the document root (/var/www) and the /etc/ directory.
  10. JeGr

    JeGr Member

    @corona: What distro is your installation running on?
    On our etch double-db-server at work, we are running zmanda OpenSource MySQL backup. (http://www.zmanda.com/backup-mysql.html)
    But that's only if you're talking about mysql database backup. Or do you want to fully backup your server at night?
  11. corona

    corona New Member

    That zmanda does look good, but I guess it'd be safest to backup the web directory and etc as well (those two and the mysql directory are the exact things I made a hurried tar of yesterday when I though it was all going down the drain).

    Cron scripts are fine, but I don't have a lot of backup space around, it'd be best if I could get it auto-rotating the tars (like logrotate) so only keep a few days worth. I'll have to have a look around, I'm sure there's some simple solutions available. I also don't have enough time to write up bash scripts to manage it all myself, I'm supposed to be focussing on other work than the server at the moment. Oh well, crisis averted, I guess I can put it off for a while now ;-)

    Thanks again for the assistance,
  12. JeGr

    JeGr Member

    You can backup the rest (file based) with rsync for example. Theres a solution swimming around with creating backups via hard-linking the old backup-set to a new date and rsyncing above that new set. So in your backup location you have a full backup at every scheduled run but it only takes as much space as an incremental backup. We use that in our datacenter to make backups to removable hds.
    Remember if you're using InnoDB type MySQL Databases, only copying data files won't do any good. Those DBs need a backup with some kind of software like hotinnobackup or like zmanda a mysqldump with locks and flushes.

    Greets Grey

Share This Page