Setting up a backup routine with Select Into OUTFILE or alternative?

Posted on 2008-11-11
Last Modified: 2013-12-13

I am trying to create a php backup routine for my MySQL databases that I can run as CRON jobs a few times a day.
Here is what I have so far:

The error I get is:

Access denied for user 'database'@'%' (using password: YES)

I have set the directorypath folder permissions to 777 and created the file name in advance.

Can you provide assistance with this method or suggest an alternate script? Thank you very much.

connect stuff...

$tableName  = 'SampleTable';

$backupFile = $_SERVER['DOCUMENT_ROOT'].'/directorypath/Sampletable.sql';

$query      = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";

$result = mysql_query($query) or die(mysql_error());


Open in new window

Question by:aberns
    LVL 6

    Accepted Solution

    Hi there,

    INTO OUTFILE is a MySQL file query and therefore it is not limited by the OS permissions (CHMOD). I would say that your user hasn't got the proper GRANT privileges to enable you to perform this operation.

    If you're on shared hosting, I doubt you're going to be able to get the permissions from the provider.

    If it's your server then you may want to look at the following:

    GRANT FILE is the command you're looking for to give file permissions.

    Hope that helps,

    Author Comment

    Okay, thanks. It is a shared server so you're right...I will not be able to get this permission. I am looking at another solution....
    LVL 6

    Expert Comment

    You may want to check this out:

    It's a MySQL tool to run backups and it can be scheduled as a cron job.

    Author Comment

    For the link you sent, it looks like I need shell access? The only access I have is via PHPMyAdmin or by writing a PHP script.

    I was actually looking into this solution:

    LVL 6

    Expert Comment

    That is basically a PHP shell using exec() to use the mysqldump command.

    If you have permission to exec() it looks like a reasonable solution. Give it a go :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Suggested Solutions

    Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
    Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
    The viewer will learn how to count occurrences of each item in an array.
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now