Problem using 'OUTFILE' to produce a backup of table

[chazzy69]

Im currently using a php script to access a database and use the following lines to produce a backup of a table-

$query = "SELECT * INTO OUTFILE 'test.sql' FROM `default_en_listingsdbelements`";
 
$result = mysql_query($query);
 
 

Which seems to execute fine and produces no errors except it doesn't actually produce any file. I figured it would produce the file in the same directory as the script or if not in the root directory but neither contains the file.

I then tested the same line in phpmyadmin/mysql which produced user does not have permission to create file.

So im a little unsure as what to do from here, do i need to change permission for the user and if so what access do i need to allow or am i using the 'OUTFILE' function incorrectly?

Any help would be greatly appreciated, note i think the username im using currently has all access permissions-

user_name (Privileges: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE ROUTINE)

[Atli]

Hey.

I think this is not a problem with internal MySQL user privileges, but rather the access the user running the MySQL service has to the actual file-system.

If you want to use the SELECT INTO OUTFILE command, the user running MySQL (typically; mysql) needs to have access to the directory you want the file saved into.

On a Linux system, try running the following command as root:

$ chown -R mysql:mysql /path/to/where/you/save/the/file/

If that doesn't work, you can try altering the permission of the file, giving everybody access to it. Note! this is a very unsafe thing to do, as it gives everybody access to everything under the directory.

$ chmod -R 777 /path/to/where/you/save/the/file/

[chazzy69]

First up thx for the reply, secondly the database, files and everything are on a remote server i.e. hosted under website so i don't think i could run the linux command.

The idea of giving everyone access to the folder might work, ill check it out.

Thanks again for the reply,

Ok tried changing the permission for the whole folder to chmod 777 and interestingly produces a internal server 500 error whenever i try to run the script. So where should i go from here? talk to the people hosting the account?

Thanks again,

[Atli]

As it turns out, your MySQL user does need the FILE privilege to use this.

The file is created on the server host, so you must have the FILE privilege to use this syntax.

So to use this at all you would need to contact your host and have him grant you this privilege.


Why do you want to use the OUTFILE clause at all? Is there any specific to want the file saved on the server?

You could replicate the file created by the command using PHP, and then just download it to your computer via the PHP script.

<?php
// Query the database
$dbLink = new mysqli('host', 'usr', 'pwd', 'dbname');
if(mysqli_connect_errno()) {
    trigger_error(mysqli_connect_error(), U_USER_ERROR);
}
$sql = "SELECT * FROM `tblName`";
$result = $dbLink->query($sql);
 
 
if($result)
{
    // Set it up so the data will be downloaded as a CSV file.
    header('content-type: text/csv; charset=utf-8');
    header('content-disposition: attachment; filename="test.csv"');
 
    // Create the file.
    while($row = $result->fetch_row())
    {
        // Counts down the number of rows left.
        // Used to skip the \t after the last column.
        $col_countdown = count($row) - 1;
        foreach($row as $_column)
        {
            // In case the column is NULL, write the
            // NULL escape char: \N (Note the upper case N)
            if($_column === null)
            {
                echo "\N";
            }
            else
            {
                // By default, only \n, \t and  \ are escaped.
                // Escaping \ alone will esacpe the other two as well
                // so we don't have to explicitly escape them.
                echo str_replace("\\", "\\\\", $_column);
            }
 
            // Print the trailing \t, unless this is the last column.
            if($col_countdown > 0)
            {
                echo "\t";
            }
            --$col_countdown;
        }
 
        // Line breaks are a single \n by default. (Unix style)
        echo "\n";
    }
}
else
{
    trigger_error($dbLink->error, E_USER_ERROR);
}
?>

That should create the file in the default format used by the SELECT INTO OUTFILE command, and send it to you as a CSV file.

[chazzy69]

That would work great but i need it to be saved onto the server not my computer, the reason for this is basically -

attached to one website is the main database which contains a whole bunch of elements, now im setting up satellite sites that can link to the other database, previously it was directly linked to the main database but this was cause to many problems and wasn't working. Therefore decided that i could use php to backup certain tables within the database then use another php script on the satellite sites to retrieve and input the tables into their own databases.

More specifically i wanted to use this method "outfile" because it has a simple input method -

$query = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";

but in any case i should be able to change the privileges of the mysql user as i have access to it, hopefully this will fix the problem.

Thanks again for the help :)

[Atli]

Ok. I see.

In case you are unable to alter the privileges to get this to work, you could alter the script I posted so that, instead of sending the file to you, it saves it onto the server's file system.
This would bypass the MySQL privilege problem, and would still allow you to use the LOAD DATA INFILE command to update your other databases.

[chazzy69]

Oh really? sweet thanks heaps then that will definitely help in the case in can't get the appropriate privileges.

Didn't realize that you could use the LOAD DATA INFILE with a .csv file thought it had to be it had its own structure,

But thanks agains, cheers