MySQL,OUTFILE: Is it possible to store query result into a variable instead of a file

Question

I am trying to backup a table records with php. I use the following code to get data:

 $table_name = "my_table";
 $backup_file  = dirname(__FILE__)."/backup/my_table.sql";
 $sql = "SELECT * INTO OUTFILE '$backup_file' FROM $table_name";
 $retval = mysql_query( $sql, $conn );

But, the last line gives the following error:

Access denied for user 'user_name'@'10.1.1.9' (using password: YES)

I changed the file permission for backup directory and its files to 777. But still the same error exists.

Now, I want to know, in the SELECT * INTO OUTFILE , is it possible to store the backup data into a variable (instead of the file). So then I can store that variable into a file?

Jite · Accepted Answer · 2015-04-18 11:35:25Z

1

Access denied for user 'user_name'@'10.1.1.9' (using password: YES)

This error is not for denied access to the file system, but for the database.
You are trying to connect to the database with a user named user_name and a password. And it does not have access (wrong name or password maybe?).

If you rather wan't to store the result of the query into a variable, you can do this by removing the INTO OUTFILE part of the query:

"SELECT * FROM $table_name"

While we are at it, I would recommend you to stop use the mysql_* API provided by older versions of PHP. Its a bad and deprecated API and will be removed in future releases.
Take a look at mysqli or PDO instead.

answered Apr 18, 2015 at 11:35

Jite

5,8772 gold badges28 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2015-04-18 11:35:18Z

1

This is a bit long for a comment.

You are not really "backing up" the table. You are simply copying its contents. If that is sufficient for what you want to accomplish, then just read the records back, one at a time, and write them to a file. This is a php loop.

The problem that you are facing is that the server does not have permissions on the directory where the file is located. Perhaps there is a way to give the server permissions or to use a common file system, mounted on both the server and client computers.

"Backup" and "recovery" are concepts that are applied to entire databases and all the contents in them, including tables, table structures, constraints, views, stored procedures, user defined functions, triggers, and so on. You can read more about the built-in capabilities here.

answered Apr 18, 2015 at 11:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Ormoz Over a year ago

I chmod'ed the backup directory to 777, then why does not server have the necessary permissions to write the file?

Gordon Linoff Over a year ago

@Ormoz . . . The server probably doesn't even see the file system, or perhaps not as you do. You need to name it from the perspective of the server.

Collectives™ on Stack Overflow

MySQL,OUTFILE: Is it possible to store query result into a variable instead of a file

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related