SitePoint Sponsor

User Tag List

Page 1 of 4 1234 LastLast
Results 1 to 25 of 81

Hybrid View

  1. #1
    SitePoint Evangelist Chromate222's Avatar
    Join Date
    May 2002
    Posts
    422
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL periodic backup with PHP

    Hi all,

    I'm dealing with quite a large database as a backend for a website. It's quite an active site and I think that it's essential that the database gets regularly backed up. How can this be done on a periodic bases. Say, at the end of each day for example? Can this be coded to happen automatically as a server processes or does someone actually have to click a button for it to happen?

    Any advice would help me no end!

    Many thanks,

    Richard.

  2. #2
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the script I use to backup my databases everyday, well actually It backups every hour.

    PHP Code:
    <?php
    // Include your user and password $user $pass
    // outside the public folder
    include("db_login.php");
    // Mysql Database Information:
        
    $database 'name_of_db';   // name of the database.
        
    $username $user;  // username with access to database.
        
    $password $pass;  // password for username.
    // Database Backup Filename & Location
        
    $backupto '/path/to/save/databases';  // absolute path to folder containing database - no trailing slash.
        
    $backupas $database '.sql' '.gz';
    // Perform backup
    $backupcommand "mysqldump -u$username -p$password $database | gzip >$backupto/$backupas";
    passthru ("$backupcommand"$error);
    if(
    $error) {
       echo (
    "Problem: $error\n"); exit;
    }
    ?>
    Then you could upload the script anywhere on your server and set up a cron job to run this script everyday or every hour or whatever.

  3. #3
    SitePoint Zealot poLka's Avatar
    Join Date
    Apr 2003
    Location
    GF
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's a sweet script Toly, you don't mind if i use it do you?

  4. #4
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's a sweet script Toly, you don't mind if i use it do you?
    I didn't write that script, just did some slight modifications. Actually, I think someone posted it in this same forum.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  5. #5
    SitePoint Evangelist Chromate222's Avatar
    Join Date
    May 2002
    Posts
    422
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent. The thing I was really looking for was how I go about running a "cron job" to run the script? Not really sure how to go about setting it up? What do I need to do?

  6. #6
    SitePoint Evangelist Chromate222's Avatar
    Join Date
    May 2002
    Posts
    422
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the script advice. I already have something like that in place However, I guess my real problem is I don't know how to get the server to run it periodically? How should I go about getting it to do this?

  7. #7
    SitePoint Evangelist Chromate222's Avatar
    Join Date
    May 2002
    Posts
    422
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please excuse that last post. My otherone didn't show up for some reason. But it's there now. Oh well.

  8. #8
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent. The thing I was really looking for was how I go about running a "cron job" to run the script? Not really sure how to go about setting it up? What do I need to do?
    Well, the first thing you need to know if your host supports cron jobs. I guess you could go to your control panel search for that option.

    If you can, then you would just enter the full path to your script and set up the time to run it.

    Here's a link for setting up cron jobs:
    http://www.redhat.com/support/resour...cron/cron.html

    Hope that helps.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  9. #9
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright but did you check your host to see if you have cron jobs installed?
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  10. #10
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Toly

    I am playing around with the backup script but I have an error.

    When running the script I get a "Problem 1" error - Any idea what this could be?

    Cheers

  11. #11
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Toly, Can I know let say i want to back up 2 tables in my database only and i do not want gzip, just the .sql, how do I go about it?


    Thanks alot

  12. #12
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And 1 more thing, I notice that when using mysqldump for the INSERT INTO statement then to be on 1 line only, so lets say I got 1000 posts, the values for 1000 posts will all be in a line

    Any ideas?

  13. #13
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by elgumbo
    I am playing around with the backup script but I have an error.

    When running the script I get a "Problem 1" error - Any idea what this could be?
    I'm not sure why you are having problems with that script. You might want to check the path to the directory you are saving the databases. In any case if you could post the code here with your modifications, that would help.

    Quote Originally Posted by GamerZ
    Can I know let say i want to back up 2 tables in my database only and i do not want gzip, just the .sql, how do I go about it?
    I know you can do that with phpmyadim but I have no clue of how you would do it with this script.

    For saving as .sql only, you can change it to this:

    PHP Code:
    <?php
    // Include your user and password $user $pass
    // outside the public folder
    include("db_login.php" );
    // Mysql Database Information:
        
    $database 'db_name';   // name of the database.
        
    $username $user;  // username with access to database.
        
    $password $pass;  // password for username.
    // Database Backup Filename & Location
        
    $backupto '/path/to/save/databases';  // absolute path to folder containing database - no trailing slash.
        
    $backupas $database '.sql';
    // Perform backup
    $backupcommand "mysqldump -u$username -p$password $database > $backupto/$backupas";
    passthru ("$backupcommand"$error);
    if(
    $error) {
       echo (
    "Problem: $error\n" ); exit;
    }
    ?>
    Quote Originally Posted by GamerZ
    And 1 more thing, I notice that when using mysqldump for the INSERT INTO statement then to be on 1 line only, so lets say I got 1000 posts, the values for 1000 posts will all be in a line
    I'm not sure what you mean. Can you explain the problem with more details?
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  14. #14
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Toly for the reply.

    Regarding my 2nd question, when i backup using mysqldump, all the INSERT values then to be crowded into 1 statement. Here is an example
    PHP Code:
    INSERT INTO gamerz_b2useronline VALUES (1056372803,'Guest','195.145.249.80','/orientek/kore/gamerz/b2archives.php'),(1056373085,'GamerZ','202.156.40.184','/orientek/kore/gamerz/'); 
    while if you are using phpmyadmin it will give you
    PHP Code:
    INSERT INTO gamerz_b2useronline VALUES (1056372803,'Guest','195.145.249.80','/orientek/kore/gamerz/b2archives.php')
    INSERT INTO gamerz_b2useronline VALUES (1056373085,'GamerZ','202.156.40.184','/orientek/kore/gamerz/'); 
    Is much more neater on the phpmyadmin side.

  15. #15
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see what you mean. I haven't had that problem not even when I used to back up my databases manually through my shell. Did you try perhaps restoring everything to a test dbase to see if it works the same way?
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  16. #16
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Toly
    I see what you mean. I haven't had that problem not even when I used to back up my databases manually through my shell. Did you try perhaps restoring everything to a test dbase to see if it works the same way?
    oh I think i found my error, i use --opt. hehe now it works. Thanks alot

  17. #17
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Gamerz
    oh I think i found my error, i use --opt. hehe now it works. Thanks alot
    Well, the --opt method is used to optimize the dump process.

    Here's a quote from a mysqldump article on sitepoint:
    Specifying the --opt argument when we back up our database should theoretically give us the fastest possible dump for reading back into MySQL server (the "opt" stands for optimize). When we specify the --opt argument, the mysqldump utility creates a more sophisticated set of dump commands, which includes the "Drop TABLE IF EXISTS" statment. This deletes the table from the database if it already exists when the dump file is being used to restore the database. The dump also includes several table locking statements.
    Just to give you an idea.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  18. #18
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Toly
    Well, the --opt method is used to optimize the dump process.

    Here's a quote from a mysqldump article on sitepoint:

    Just to give you an idea.
    thanks alot

  19. #19
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Toly

    Here is the script I am using :

    PHP Code:
    <?php
    // Include your user and password $user $pass
    // outside the public folder
    include("/home/sitename/connect/user.php"); 
    // Mysql Database Information:
        
    $database 'db_name';   // name of the database.
        
    $username $user;  // username with access to database.
        
    $password $pass;  // password for username.
    // Database Backup Filename & Location
        
    $backupto '/home/sitename/database';  // absolute path to folder containing database - no trailing slash.
        
    $backupas $database '.sql' '.gz';
    // Perform backup
    $backupcommand "mysqldump -u$username -p$password $database | gzip >$backupto/$backupas";
    passthru ("$backupcommand"$error);
    if(
    $error) {
       echo (
    "Problem: $error\n" ); exit;
    }
    ?>
    The user.php is :

    PHP Code:
    <?php
    $user
    ='db_username';
    $pass='db_password';
    ?>
    I am using the correct values for sitename, db_username and db_password

    Again, all I get is "Problem: 1 " displayed on the screen.

  20. #20
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I think the problem is with the directory you are storing your backups. You need to change the permissions for that directory to 777. That will allow the script to write to that directory.

    Let me know if that works out for you.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  21. #21
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Toly ! You're a star !

    The chmod was the problem - Now I'm off to find out about cron jobs

  22. #22
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool. I'm glad it worked out.

    For cron jobs, just check if you have that option in your control panel of your hosting account. If you do, go to this link:

    http://www.redhat.com/support/resou.../cron/cron.html

    It has good examples of how to set up cron jobs.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  23. #23
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers Toly

    I got it working and did my first backup last night

    I also added the following code to yours

    PHP Code:
     $date date("dmy"); 
    and amended

    PHP Code:
    $backupas $database '.sql' '.gz'
    to

    PHP Code:
    $backupas $database $date '.sql' '.gz'
    So I get a unique filename for incremental backups. I know this will take up more space (as the old file is not overwritten) but the db on this site is only small and will probably only require backing up every couple of weeks. I just liked the idea of being able to go back to an older version of the DB if required.

    Thanks again for all your help.

  24. #24
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, that's how the script was set up in the first place. I took off the date option because since I got five dbases, they were taking a lot of space and it was annoying to delete the old versions all the time.

    But it's always good to have the old versions of your dbase anyhow. I'll have to consider putting it back again.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  25. #25
    SitePoint Enthusiast RedRose's Avatar
    Join Date
    Jul 2003
    Location
    A Nice place on earth..
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi.

    I was really ashamed of asking my newbie questions here, but thanks to the support and help of sincere people I know, I now have the courage to ask.. I hope you would help me without laughing at me.

    My first question is:

    Should you call this script from your browser?

    Is it safe to have this script in the public_html folder?


    Code:
    // Include your user and password $user $pass
    // outside the public folder
    Is it for security reasons that you are not including the username and password of the database in the same file?

    My script is working .


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
  •