What would be the best way to backup and restore mysql dumps with just php?

Question

I know how you do it from the console, and I know you can execute console commands with php, but would there be a way to recursively dump a database into a file, and then restore it from that file later, just using php? I want it to be able to work on windows and nix servers.

I am guessing it would need to loop through the tables and rows but how would I get a list of those? Or can it even be done? Seems like it would take a massive amount of memory if db was a good size. Thoughts?

Answer 1

mysql_query("SELECT * INTO OUTFILE 'sql/backup.sql' FROM my_table");
mysql_query("LOAD DATA INFILE 'sql/backup.sql' INTO TABLE my_table");

Answer 2

Yeah, I'm aware of phpmyadmin, but I want to do this in a custom page and execute it when I want to. Basically what I am trying to do is have a simple "dump_db.php" script that dumps the application database before I commit my changes in git. That way when I commit, a history of the database is saved as well. And then when I do a git pull, I just hit "update_db.php" and it restores the database to the fresh db dump if it has changed.

Answer 3

deizel, that looks very close to what I want, but can I do it for the whole db instead of just a single table? Also, if there are any schema differences would it pick those up as well?

Answer 4

Run the SHOW TABLES; query. Walk through the results and run the queries listed by deizel for each table.

Good luck!

Answer 5

cool, I will look into that, but I'm worried that I will miss things like primary keys and other nuances. If I can get a working prototype I will be sure to post here.

Answer 6

You can use the exec() function to call mysqldump. This would pipe the output of mysqldump (the database export) back to your PHP script and you can handle it there however is necessary. This can be used to export a single table or an entire database. The tool could be installed on a client machine, or the PHP script can be run on the MySQL server.

/path/to/mysqldump --host=[svr] --user=[user] --password=[passwd] [db]

Make sure that the user passed to MySQL has SELECT and LOCK TABLE permissions on each database to be backed up.

Answer 7

Look into the mysql database.

Answer 8

This might help with primary keys

SHOW CREATE TABLE my_table