Announcement

Collapse
No announcement yet.

MySql database backup question

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySql database backup question

    1. Is there a php script I can use to automaticvally back up the forum database to a zip file in a directory bellow the root called db_backup ?

    2. what would be the correct cron command string for the same job on a server which has cron enabled?

    Max

  • #2
    In order to get support on these forums you need to follow the instructions I posted in this thread:

    http://www.vbulletin.com/forum/showt...187#post483187
    Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
    Change CKEditor Colors to Match Style (for 4.1.4 and above)

    Steve Machol Photography


    Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


    Comment


    • #3
      Done

      BTW, my owner's email address is no longer valid as I no longer have the @mymatchmaker.com website and server. can you please change it to [email protected] ?

      Comment


      • #4
        Originally posted by AtheistWorld
        1. Is there a php script I can use to automaticvally back up the forum database to a zip file in a directory bellow the root called db_backup ?

        2. what would be the correct cron command string for the same job on a server which has cron enabled?

        Max
        This works if you have php installed as a binary. It will not work on all servers!:
        PHP Code:
        #!/usr/local/bin/php -q
        //change the above to the path to the php binary. -q disables headers.
        <?php
        $dt 
        date("n-j-y");
        $chr "";
        while(
        file_exists("/the/path/to/the/dump/$dt$chr")){
        //change the above to the path to the dumpfile, and leave /$dt$chr on the end.
        if($chr=="") {
        $chr "a";
        } else {
        $chr chr((ord($chr)+1));
        }
        }
        $path $dt $chr;
        shell_exec("mysqldump -B thedatabasename --password=yourpassword > /home/mikeb/dumps/$path");
        //change thedatabasename and yourpassword

        //uncomment the following to delete anything over two days old.
        /* $dt = date("n-j-y",strtotime("-2 day"));
        $chr = "";
        $filelist = array();
        while(file_exists("/home/mikeb/dumps/$dt$chr")){
        $filelist[] = "/home/mikeb/dumps/$dt$chr";
        if($chr=="") {
        $chr = "a";
        } else {
        $chr = chr((ord($chr)+1));
        }
        }
        foreach($filelist as $val) {
        // echo $val."\n";
        unlink($val);
        }*/
        ?>

        Comment


        • #5
          Originally posted by Beorn
          This works if you have php installed as a binary. It will not work on all servers!:
          PHP Code:
          #!/usr/local/bin/php -q
          //change the above to the path to the php binary. -q disables headers.
          <?php
          $dt 
          date("n-j-y");
          $chr "";
          while(
          file_exists("/the/path/to/the/dump/$dt$chr")){
          //change the above to the path to the dumpfile, and leave /$dt$chr on the end.
          if($chr=="") {
          $chr "a";
          } else {
          $chr chr((ord($chr)+1));
          }
          }
          $path $dt $chr;
          shell_exec("mysqldump -B thedatabasename --password=yourpassword > /home/mikeb/dumps/$path");
          //change thedatabasename and yourpassword

          //uncomment the following to delete anything over two days old.
          /* $dt = date("n-j-y",strtotime("-2 day"));
          $chr = "";
          $filelist = array();
          while(file_exists("/home/mikeb/dumps/$dt$chr")){
          $filelist[] = "/home/mikeb/dumps/$dt$chr";
          if($chr=="") {
          $chr = "a";
          } else {
          $chr = chr((ord($chr)+1));
          }
          }
          foreach($filelist as $val) {
          // echo $val."\n";
          unlink($val);
          }*/
          ?>
          How do you tell if php is installed as a binary?
          vBulletin - Sometimes, I'm just like, Wow, and then I'm like, Whoa, and then I'm like, Damn.

          vBulletin.org's ol' Moderator

          I have a lifetime terrorist hunting permit - #091101

          chmod a+x /bin/laden -- Allows anyone the permission to execute /bin/laden

          Comment


          • #6
            Originally posted by AtheistWorld
            Done

            BTW, my owner's email address is no longer valid as I no longer have the @mymatchmaker.com website and server. can you please change it to [email protected] ?
            You will need to send this request via the support system:

            http://www.vbulletin.com/members/support_form.php
            Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
            Change CKEditor Colors to Match Style (for 4.1.4 and above)

            Steve Machol Photography


            Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


            Comment


            • #7
              I use cron for this and not PHP.
              The cron command isn't told a directory, and thus puts it in your usr home directory, which on a shared server is usually the directory above the docroot (i.e. the one you log into via SSH and FTP).

              The command is:
              mysqldump --opt -l -v -h SERVER -u USER -pPASSWORD DATABASE_NAME > backup_file.sql

              Note that you replace all things in capitals with your vars. Note that there is no space between '-p' and your password. Note that SERVER is usually 'localhost'.

              This file is just a plain text dump, so it's best to compress it:
              /bin/gzip -f backup_file.sql

              That will create a file named 'backup_file.sql.gz'. You can open this locally in WinZip. It will also delete the original file once it's safely compressed.


              When you need to restore, this will uncompress your file:
              /bin/gzip -d backup_file.sql

              And this will import it back into the database:
              mysql -h SERVER -u USERNAME -pPASSWORD DATABASE_NAME < backup_file.sql

              Of course, the latter two you won't want to have cron jobs for! But you'll need them if you ever want to do a full restore from the backup... i.e. when moving servers.

              Cheers

              David K
              London Fixed-gear and Single-speed

              Comment


              • #8
                David, thank you sooooooooooooooooo much!

                Max

                Comment


                • #9
                  When you need to restore, this will uncompress your file:
                  /bin/gzip -d backup_file.sql
                  I used the above and it unziped the file but I cannot find where it put it.

                  Max

                  Comment


                  • #10
                    I get
                    mysql: unknown option '-c'

                    when I try to restore.

                    Comment

                    widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                    Working...
                    X