Restoring database via php script with .sql file

I created a file that backs up the user database and saves it to a standard .sql file with comments and such.

I am looking for a way to create a script that will restore the database using that .sql file

I know I can do it via phpMYAdmin but I dont want a user to have to do that. I want them to be able to restore it directly from the administrator back end of my script.

Anyone have any idea how to do this.


Have a look at:

*I have never used this before I just ran across it the other day.

I thought I’d mention that I’ve used this script before and it’s really wonderful, I recommend it to anyone who needs to import a large database dump with only basic (no SSH/Telnet) access to the server.

I imported a 500+mb dump using that and had no problems, it’s wonderful.

That’s not quite what I am looking for. I am looking to have this run completely from a php script on my administrator control panel. I’m assuming that the user will not have access to phpmyadmin or telnet.

Hmm, this might get tricky. Do you have a record of what database is associated with what user?

The thing I worry about is that if the user has no specific database assigned to them, the script would need to access MySQL with elevated privileges, potentially allowing for the user to throw in some sneaky drop statements or other data destroying niceties.

That said, before I can offer any sort of sound advice on how to approach this, I’d need to know what kind of restrictions you have in place.

The user would only be restoring the database that they have all privlidges to.

Basically my CMS script is powered by php/mysql. There is an option in the backend to backup the database tables.

There’s a form which lists all the tables that are assigned to the CMS database and the user selects which table to back up.

The backup script creates a .sql file and stores it on the server. The user can download the backup or keep it on the server.

Now I would like the user to be able to restore that database file from the admin panel where they backed it up. It makes it easier for the user to just go to and click on restore backed up database instead of having to go to their website cpanel and then phpmyadmin.

well, you could have an upload form, store the backup sql file in a /tmp directory, then run a system command on mysql to restore the backup.

However, it cannot be trusted that what the user is uploading is a backup SQL of a table. They could be running a DROP DATABASE mysql; (or drop table) for all we know. Hence why I asked about permissions, because the mysql command line would need a username and a password to deal with access for the related sql.

I definatly know what you are saying about security. My admin backend is secure and only authorized administrators are allowed to the backend.

would I use the system() command in php to execute a mysql restore?

Funny you ask, I posted a similar thread here just a few days ago.
Here is the script I am using now:

system(“mysql -u {$this->mysqlUsername} -p{$this->mysqlPassword} -D {$this->database} < {$this->filepath}/data.sql”);