How to backup MySQL database in PHP?

by

While you can execute backup commands from PHP, they don’t really have anything to do with PHP. It’s all about MySQL.

I’d suggest using the mysqldump utility to back up your database. The documentation can be found here : http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html.

The basic usage of mysqldump is

mysqldump -u user_name -p name-of-database >file_to_write_to.sql

You can then restore the backup with a command like

mysql -u user_name -p <file_to_read_from.sql

Do you have access to cron? I’d suggest making a PHP script that runs mysqldump as a cron job. That would be something like

<?php

$filename="database_backup_".date('G_a_m_d_y').'.sql';

$result=exec('mysqldump database_name --password=your_pass --user=root --single-transaction >/var/backups/'.$filename,$output);

if(empty($output)){/* no output is good */}
else {/* we have something to log the output here*/}

If mysqldump is not available, the article describes another method, using the SELECT INTO OUTFILE and LOAD DATA INFILE commands. The only connection to PHP is that you’re using PHP to connect to the database and execute the SQL commands. You could also do this from the command line MySQL program, the MySQL monitor.

It’s pretty simple, you’re writing an SQL file with one command, and loading/executing it when it’s time to restore.

You can find the docs for select into outfile here (just search the page for outfile). LOAD DATA INFILE is essentially the reverse of this. See here for the docs.

More Related Contents:

  1. Php date() Date and time is stuck [closed]
  2. mysqli or die, does it have to die?
  3. MySQL vs MySQLi when using PHP [closed]
  4. PHP/MySQL insert row then get ‘id’
  5. How do I set ORDER BY params using prepared PDO statement?
  6. Show values from a MySQL database table inside a HTML table on a webpage [duplicate]
  7. How can I store and retrieve images from a MySQL database using PHP?
  8. mysqli_stmt::bind_param(): Number of elements in type definition string doesn’t match number of bind variables
  9. PHP file cannot enter some part of code
  10. Laravel migration: unique key is too long, even if specified
  11. Laravel: PDOException: could not find driver
  12. Laravel Eloquent select all rows with max created_at
  13. MySQL – count total number of rows in php
  14. Advantages Of MySQLi over MySQL [closed]
  15. Grouping WHERE clauses in Codeigniter
  16. Using if(isset($_POST[‘submit’])) to not display echo when script is open is not working
  17. check if the query results empty row mysqli [duplicate]
  18. SELECT COUNT() vs mysql_num_rows();
  19. Store procedures in phpMyAdmin
  20. Unique IPs in a voting system
  21. double results in my array ( mysql_fetch_array )
  22. How best to get someone’s ‘rank’ from a scores table with php and mysql without looping
  23. How to get rid of MySQL error ‘Prepared statement needs to be re-prepared’
  24. Get MySQL database output via PHP to XML
  25. Storing time information: Timezone required?
  26. How to create PHP two column table with values from the database?
  27. How do you make good use of multicore CPUs in your PHP/MySQL applications? [closed]
  28. how to execute mysql command DELIMITER
  29. How to get a list of databases?
  30. PDO and MySQL ‘between’

Leave a Comment