SitePoint Sponsor

User Tag List

Page 1 of 3 123 LastLast
Results 1 to 25 of 56
  1. #1
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Dumping Files - is it possible with PHP?

    Hi, I use phpMyAdmin to back it up, but I was wondering if you could do it with PHP.

    Please tlel me how, if it's possible!

    Thanks,
    ~someonewhois

  2. #2
    SitePoint Guru Majglow's Avatar
    Join Date
    Aug 1999
    Location
    B-Town
    Posts
    645
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What do you mean? Dumping the database?
    You can do that with PHP.

    -cARL
    Ohai!

  3. #3
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    phpMyAdmin is PHP so of course it can be done with with PHP. but it would suck just like phpMyAdmin does for dumping databases. it won't work for databases over, say, 50MB. it's slow and inefficient. it can timeout PHP.

    if you want to dump a database, use mysqldump. that's what it's for. you can even execute it from a PHP script.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  4. #4
    SitePoint Addict IsaacV's Avatar
    Join Date
    Jan 2002
    Location
    Saint Petersburg
    Posts
    246
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What do you mean by MySQLdump?
    Pants.
    Join the NYFlava Fashions Contest

  5. #5
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    when i say MySQL (note the capitalization), i am referring to the whole MySQL suite (server and client programs). and then mysql (lowercase) means the MySQL command line client. mysqldump is the program for dumping databases/tables.

    here's the manual page for mysqldump: www.mysql.com/doc/m/y/mysqldump.html

  6. #6
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, kinda forgot about this thread of mine!

    Thank you for your replies, I do mean mysql database dump, should've said that...


    I don't have access to a command line, I wanted to put it into my script.

    It would be a very small table, that I'd be dumping, only about 3kb normally!
    May grow up to 50kb, but that's about it.

    So can you post an example of it done with PHP?

    Thanks,
    ~someonewhois

  7. #7
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I couldn't find it in anything...

    Thanks,
    ~someonewhois

  8. #8
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    exec("mysqldump --opt -uyour_username -pyour_password database_name | gzip > /path/to/dump/file.gz"); 
    [edit]Wrong function before!

    Sean
    Last edited by seanf; May 26, 2002 at 09:34.
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  9. #9
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So would this work:

    PHP Code:
    exec("mysqldump --$server -$user -$password $database | zip > backups/file.zip"); 
    What's opt? The server? I'm guessing..

    And 2 -- before it?

    Thanks,
    ~someonewhois

  10. #10
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope

    PHP Code:
    exec("mysqldump --opt -[SIZE=3][b]u[/b][/SIZE]your_username -[SIZE=3][b]p[/b][/SIZE]your_password database_name | [SIZE=3][b]g[/b][/SIZE]zip > /path/to/dump/file.gz"); 
    As for --opt:

    Should give you the fastest possible dump for reading into a MySQL server
    http://www.mysql.com/doc/m/y/mysqldump.html

    BTW - gzipping like that wont work on Windows

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  11. #11
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I get it now!

    How would I do only 1 table?

    Thanks,
    ~someonewhois

  12. #12
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    exec("mysqldump --opt -u$username -p$password $database tablename | gzip > /path/to/dump/file.gz"); 
    Like that?

    Thanks,
    ~someonewhois

  13. #13
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yep, that works! i didn't think it would. i thought you had to use the --tables option:

    PHP Code:
    exec("mysqldump --opt -u$username -p$password --tables $database tablename | gzip > /path/to/dump/file.gz"); 

  14. #14
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's something I use with cron to backup db's regularily;

    PHP Code:
    // Mysql Database Information:
        
    $database 'db_name';   // name of the database.
        
    $username 'db_user';  // username with access to database.
        
    $password 'db_password';  // password for username.

    // Database Backup Filename & Location
        
    $backupto '/home/username/sql_backups';  // absolute path to folder containing database - no trailing slash.
        
    $backupas $database '.sql.' date "Ymd" ) . '.gz';

    // Perform backup
    $backupcommand "mysqldump -u$username -p$password $database | gzip >$backupto/$backupas";

    passthru ("$backupcommand"$error);
    if(
    $error) {
       echo (
    "Problem: $error\n"); exit;

    That dumps the database, adding data timestamp and zips the file with gzip (unzip with "gunzip [filename]").

    The full code I can't publish unfortunately, as it's partly someone elses and isn't open source but it's pretty easy to do stuff like email it or ftp it to another location, once you've got the file stored.

    Using the passthru (as opposed to the backtick operator `) means you don't broadcast your database username / password via the "ps" command on shared servers.

  15. #15
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by DR_LaRRY_PEpPeR
    yep, that works! i didn't think it would. i thought you had to use the --tables option:

    PHP Code:
    exec("mysqldump --opt -u$username -p$password --tables $database tablename | gzip > /path/to/dump/file.gz"); 

    Ok, I'll try that!

    Thanks HarryF, I might try that, but probably not! Thanks anyways.

    1 more question; Is there a way to put it back into the database, as in a restore? And in the zip (or gzip, wahtever), is it the sql file?

    Thanks,
    ~someonewhois

  16. #16
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:

    <?

    /* AMRSSBOSM
     * Abu Mami's Ridiculously Simple SQL Backup and Offsite Storage Mechanism
     *        (don't you wish that you had thunk of such a great acronym)
     * Version 1.00
     * Copyright (C) 5762 (2001 CE), by Abu Mami and Yisrael Hersch.
     *   All Rights Reserved.
     *   All copyright notices in this script must be left intact.
     * Disclaimer:
     *   AMRSSBOSM is distributed in the hope that it will be useful to you.
     *   This script has been carefully tested and appears to work fine, but
     *   not warranty is expressed or implied.
     * Terms of use:
     *   Permission will be granted to use this script on personal and
     *   commercial web pages for free as long as you leave this notice
     *   intact.
     * website: [url]http://web.abumami.com[/url]
     * email: [email]admin@abumami.com[/email]
     */


    /*
     *    OVERVIEW
     *    --------
     *
     *    AMRSSBOSM uses resources found on your server to create a backup of your
     *    mySQL data and automatically send it to safe offsite storage. If you have
     *    a small database, this is a cheap and easy way to ensure proper backup of
     *    your valuable data.
     *
     *    The mySQL database backup is created using mysqldump. I imagine that this
     *    script could be adapted for use by other database systems.
     *
     *    The backup file is [optionally] compressed using gzip as default, but this
     *    can be changed according to your needs. You may have to change program
     *    command line parameters accordingly for other compression programs.
     *
     *    The resulting file is then sent by email as an attachment. Make sure to
     *    send this file to an account not dependent on your host/server. A good
     *    choice would be a free web email account (hotmail.com, mail.com, etc).
     *    This makes a nice [free] secure offsite storage facility.
     *
     *  REQUIREMENTS
     *  ------------
     *  - Tested under PHP4, but should work under PHP3
     *  - mySQL database
     *  - optional: gzip installed on server
     *
     *    INSTALLATION
     *    ------------
     *    
     *    Copy this script into a directory on your server such as /etc/backup.
     *    This will be your backup directory. Set up the variables at the
     *    beginning of the script as needed for your configuration.
     *  (If you want, you can rename the script backup.php :-)
     *    
     *    CD to your new backup directory to try out the script. You can run the
     *    script at the prompt by typing:
     *         php amrssbosm.php <dbname>
     *    A directory with the name of the database <dbname> will be automatically
     *    created. A backup file will also be created. If you're using gzip compression,
     *    the name will be <dbname>.sql.gz. If no compression is used the name will
     *    be <dbname>.sql. The file will also be sent to the email address you supplied
     *    int the $to variable in the script. Sending a 500K attachment by email is
     *    certainly not unreasonable.
     *
     *    If you want, you can set up a CRON for this script, and you can then rest
     *    easy knowing your data will automatically be backed up.
     */


    //if($argc != 2)
    if($dbname =="")
        die(
    "no dbname");        // die if no dbname

    // ---- following variable to be set by user depending on configuration

    // mySQL - variables
    $dbhost "localhost";
    //$dbuser = "";
    //$dbpass = "";



    // compression support, both variables null if no compression support
    $comp_pgm 'gzip';        // compression program name
    $comp_ext '.gz';        // file name extension for compressed files

    // full path to our backup script
    //$path = "c:/phpdev/www/backup";
    //$path = "c:/phpdev/www/backup";

    // email settings (for sending of our backup file)
    $to         'u@email.com';
    $from     'backup@myserver.com';

    // ---- end of user settable parameters, do not change code below this line

    // routine to send our backup file as an email attachment
    function sendbackup($to$subject$text$from$file$type) {
        
    $content fread(fopen($file,"r"),filesize($file));
        
    $content chunk_split(base64_encode($content));
        
    $uid strtoupper(md5(uniqid(time())));
        
    $name basename($file);

        
    $header "From: $from\nReply-To: $from\n";
        
    $header .= "MIME-Version: 1.0\n";
        
    $header .= "Content-Type: multipart/mixed; boundary=$uid\n";

        
    $header .= "--$uid\n";
        
    $header .= "Content-Type: text/plain\n";
        
    $header .= "Content-Transfer-Encoding: 8bit\n\n";
        
    $header .= "$text\n";

        
    $header .= "--$uid\n";
        
    $header .= "Content-Type: $type; name=\"$name\"\n";

        
    $header .= "Content-Transfer-Encoding: base64\n";
        
    $header .= "Content-Disposition: attachment; filename=\"$name\"\n\n";
        
    $header .= "$content\n";

        
    $header .= "--$uid--";

        
    mail($to$subject""$header);
          return 
    true;
           echo 
    "sending ...";
    }


    $dbname $argv[1];
    flush();
    $conn mysql_connect($dbhost,$dbuser,$dbpass) or die(mysql_error());
    $path $path $dbname "/";

    if(!
    is_dir($path))
        
    mkdir($path0777);

    $fullname $path $dbname '.' 'sql';
    $dump_cmd "mysqldump -h " $dbhost " -u" $dbuser " -p" $dbpass " " $dbname " > " $fullname;
    exec($dump_cmd);        // execute mysql dump
    /*if ($comp_pgm) {
        $comp_cmd = $comp_pgm . " -f " . $fullname;
        exec($comp_cmd);        // execute gzip
    }*/

    $subject $dbname ' backup';
    $text     $dbname ' backup attached';
    $file     $fullname $comp_ext;
    $type     'application/x-gzip';
    sendbackup($to$subject$text$from$file$type);

    echo 
    "Created backup file for $dbname.\n";

    ?>

  17. #17
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, thank you, but the other one works.

    I really need to unzzip it now!

    Thanks,
    ~someonehwois

  18. #18
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    to restore (if it's NOT gzipped)

    PHP Code:
    exec("mysql -u$username -p$password $database < /path/to/dump/file.sql"); 
    or, if gzipped (i think )

    PHP Code:
    exec("gunzip /path/to/dump/file.gz | mysql -u$username -p$password $database"); 

  19. #19
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, and to dump it as sql?

    Thanks,
    ~someonewhois

  20. #20
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    mysqldump --opt -uyour_username -pyour_password database_name > /path/to/dump/file.sql
    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  21. #21
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    /me feels like an idiot who sould've been able to guess that...


    And does that work with doing "database_name tablename" too?

    And it goes in exec() too?

    Thanks,
    ~someonewhois

  22. #22
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, the only difference is you are not passing it through gzip

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  23. #23
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you!

  24. #24
    SitePoint Member
    Join Date
    Feb 2002
    Location
    Prince George, BC
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sheepishly another frustrated nerd enters...asking for the slap of wisdom ...

    Pleeaasse..will someone try to help figure out why all the wise advise (above)..does not work for me?

    Here's what I'm uploading to my host (I named it test.php), but when I go to it with my browser the only thing I get back is the "have dblink" echo but no "backup-file.sql"...

    ...where is the missing link (It's sure not me) ...Help!!!

    //-------------------------------------------
    <HTML>
    <HEAD>
    <TITLE>MySQL Dump</TITLE>
    </HEAD>
    <BODY>

    <?

    $host = "localhost";
    $user = "dotcom";
    $password = "pass"; //this of course is fake in this example
    $db = "dotcom";

    $dblink = mysql_connect($host, $user, $password) or die("Could not connect");

    if ($dblink) {
    echo("have dblink<BR>");
    }

    $save = "mysqldump --opt dotcom > /home/mistress/web/dotcom_backup/backup-file.sql";

    $result = exec($save);

    if ($result) {
    echo("have save<BR>");
    }

    ?>
    </body>
    </HTML>
    //------------------------------------

  25. #25
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You have got it slightly wrong. You are not connecting to MySQL through PHP you are running a command on the server which will connect

    Try this:

    1) FTP to your website and create a folder (you may have to give the folder write permissions)

    2) Save the script below as dump.php (or whatever)

    3) Change the varaibles, e.g. change it to your username and password

    4) Upload dump.php

    5) Run dump.php and look in the folder you created in step 1

    PHP Code:
    <?php

    $username 
    "yourusername";
    $password "yourpassword";
    $database "yourdatabasename";
    $path "/path/to/dump/folder";

    $command "mysqldump --opt -u$username -p$password $database > $path/backup.sql";

    exec $command );

    ?>
    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •