Backup MySQL Database Using PHP

It is always a good idea to backup your MySQL database often as possible, because you don't want to risk your entire work over some technical failure, you want to make latest copies of your database and keep it somewhere safe. Some web hosts do offer such backup features, you just have to push "back-up button" and you are good to go. But if not, you can also backup using PHP exec() function explained in this post but this function may be disabled by most host for security reasons, so in that case you can use this nifty PHP functions to back up your entire database tables.
PHP
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
function __backup_mysql_database($params) { $mtables = array(); $contents = "-- Database: `".$params['db_to_backup']."` --\n"; $mysqli = new mysqli($params['db_host'], $params['db_uname'], $params['db_password'], $params['db_to_backup']); if ($mysqli->connect_error) { die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error); } $results = $mysqli->query("SHOW TABLES"); while($row = $results->fetch_array()){ if (!in_array($row[0], $params['db_exclude_tables'])){ $mtables[] = $row[0]; } } foreach($mtables as $table){ $contents .= "-- Table `".$table."` --\n"; $results = $mysqli->query("SHOW CREATE TABLE ".$table); while($row = $results->fetch_array()){ $contents .= $row[1].";\n\n"; } $results = $mysqli->query("SELECT * FROM ".$table); $row_count = $results->num_rows; $fields = $results->fetch_fields(); $fields_count = count($fields); $insert_head = "INSERT INTO `".$table."` ("; for($i=0; $i < $fields_count; $i++){ $insert_head .= "`".$fields[$i]->name."`"; if($i < $fields_count-1){ $insert_head .= ', '; } } $insert_head .= ")"; $insert_head .= " VALUES\n"; if($row_count>0){ $r = 0; while($row = $results->fetch_array()){ if(($r % 400) == 0){ $contents .= $insert_head; } $contents .= "("; for($i=0; $i < $fields_count; $i++){ $row_content = str_replace("\n","\\n",$mysqli->real_escape_string($row[$i])); switch($fields[$i]->type){ case 8: case 3: $contents .= $row_content; break; default: $contents .= "'". $row_content ."'"; } if($i < $fields_count-1){ $contents .= ', '; } } if(($r+1) == $row_count || ($r % 400) == 399){ $contents .= ");\n\n"; }else{ $contents .= "),\n"; } $r++; } } } if (!is_dir ( $params['db_backup_path'] )) { mkdir ( $params['db_backup_path'], 0777, true ); } $backup_file_name = "sql-backup-".date( "d-m-Y--h-i-s").".sql"; $fp = fopen($backup_file_name ,'w+'); if (($result = fwrite($fp, $contents))) { echo "Backup file created '--$backup_file_name' ($result)"; } fclose($fp); }

Usage

Just call above function within your script and pass these parameters to it.
PHP
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
$para = array( 'db_host'=> 'localhost', //mysql host 'db_uname' => 'username', //user 'db_password' => 'password123', //pass 'db_to_backup' => 'wordpress', //database name 'db_backup_path' => '/home/my_wordpress/', //where to backup 'db_exclude_tables' => array('wp_comments','wp_w3tc_cdn_queue') //tables to exclude ); __backup_mysql_database($para);
I've written this PHP code using MySQLi, and it has been used to backup WordPress database and few other website database tables successfully, but if you've found glitches please share.
  • Thank you - what a great little function. One issue is that it doesn't handle tables with hyphens in it.To allow this (it may already work on some other systems), can we change two lines...$results = $mysqli->query("SHOW CREATE TABLE ".$table ); to $results = $mysqli->query("SHOW CREATE TABLE `".$table .'`');and$results = $mysqli->query("SELECT * FROM ".$table); to $results = $mysqli->query("SELECT * FROM `".$table .'`');
  • I get the date warning and below that I get "Backup file created '--sql-backup-28-06-2017--07-35-30.sql' (26)"Then when I go to the file it's only 1kb. Can you explain why this happens or help please?
New question is currently disabled!