Backup mysql data

Is there a way using PHP to make a backup of开发者_如何学Python data only from MySQL database tables?

---

Have a look at http://davidwalsh.name/backup-mysql-database-php and remove the parts where he generates sql commands for re-creating the structure.

---

Use SHOW TABLES (http://dev.mysql.com/doc/refman/5.0/en/show-tables.html) to get each table, loop them with PHP using SELECT * FROM -tablename-. Then loop this record set, use implode(',' $results) to get the data (CSV). You might want to create a seperate for or something for each table of course.

---

You agree that you want "want the raw data (e.g.: as CSV) rather than any form of INSERT statements" - but the answer you've accepted does precisely the latter, using e.g. this line of code:

$return.= 'INSERT INTO '.$table.' VALUES(';

Which is fine if that's really what you want, but if I were you I would then just make a system call to mysqldump instead. You can get mysqldump to just generate a set of INSERT INTO statements in a safe, robust way without worrying about "did I close that double quote? Should there be an extra comma here?" etc.

I don't know why you don't want to export the schema as well - if you specify --add-drop-table then your database dump becomes a nice, neat restore of your whole db if the worst happens - but if you just want to export your data, and you can make a system call to mysqldump in your PHP script, you could run:

mysqldump --skip-triggers --compact --no-create-info db_name | gzip -c > /tmp/db_name_backup.sql.gz

--no-create-info excludes all the CREATE TABLE statements; but as I say, I would rather include both CREATE and DROP IF EXISTS, so you have a complete backup of data and schema.

I really recommend backing up using mysqldump's generated SQL - which MySQL innately understands, and which handles encoding, whitespace etc. properly - rather than something like CSV - which AFAIK isn't actually any kind of internationally recognized standard, and can't be piped back into mysql to restore your database at a later date:

gunzip -c < /tmp/db_name_backup.sql.gz | mysql db_name

If your backup was instead a separate CSV file for every single table, restoring that backup becomes a far more difficult task.