Backup MySQL Database Using PHP

Import and export of data to and from any database is a common task of any website development. By exporting databases we secured our application from any kind of data loss. Sometimes, we need to develop our own custom PHP code to import or export databases. So friends, in this tutorial, I will show you how to backup or export MySQL database using PHP.

Backup or Export MySQL Database Using PHP

By using this PHP code you can export the whole database at once or you can export specific tables. Mention the specific table names that you want to download – $tables = array(“table1”, “table2”, “table3”, …);  Please follow the below code —

2. define("DB_HOST", "localhost");
3. define("DB_USERNAME", "username");
4. define("DB_PASSWORD", "password");
5. define("DB_NAME", "database_name");
6.  
7. $con = mysqli_connect(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME) or die ("Error : ");
8. if(mysqli_connect_errno($con)) {
9. echo "Failed to connect MySQL: " .mysqli_connect_error();
10. } else {
11. //If you want to export or backup the whole database then leave the $table variable as it is
12. //If you want to export or backup few table then mention the names of the tables within the $table array like below
13. //eg, $tables = array("wp_commentmeta", "wp_comments", "wp_options");
14.  
15. $tables = array();
16. $backup_file_name = DB_NAME.".sql";
17. backup_database($con, $tables, $backup_file_name);
18. }
19. ?>
20.  
22. function backup_database($con, $tables = "", $backup_file_name) {
23.  
24. if(empty($tables)) {
25. $tables_in_database = mysqli_query($con, "SHOW TABLES");
26. if(mysqli_num_rows($tables_in_database) > 0) {
27. while($row = mysqli_fetch_row($tables_in_database)) {
28. array_push($tables, $row[0]);
29. }
30. }
31. } else {
32. // Checking for any table that doesn't exists in the database
33. $existed_tables = array();
34. foreach($tables as $table) {
35. if(mysqli_num_rows(mysqli_query($con, "SHOW TABLES LIKE '".$table."'")) == 1) {
36. array_push($existed_tables, $table);
37. }
38. }
39. $tables = $existed_tables;
40. }
41.  
42.  
43. $contents = "--\n-- Database: `".DB_NAME."`\n--\n-- --------------------------------------------------------\n\n\n\n";
44.  
45. foreach($tables as $table) {
46. $result = mysqli_query($con, "SELECT * FROM ".$table);
47. $no_of_columns = mysqli_num_fields($result);
48. $no_of_rows = mysqli_num_rows($result);
49.  
50. //Get the query for table creation
51. $table_query = mysqli_query($con, "SHOW CREATE TABLE ".$table);
52. $table_query_res = mysqli_fetch_row($table_query);
53.  
54. $contents .= "--\n-- Table structure for table `".$table."`\n--\n\n";
55. $contents .= $table_query_res[1].";\n\n\n\n";
56.  
57. /**
58. * $insert_limit -> Limits the number of row insertion in a single INSERT query.
59. * Maximum 100 rowswe will insert in a single INSERT query.
60. * $insert_count -> Counts the number of rows are added to the INSERT query.
61. * When it will reach the insert limit it will set to 0 again.
62. * $total_count -> Counts the overall number of rows are added to the INSERT query of a single table.
63. */
64. $insert_limit = 100;
65. $insert_count = 0;
66. $total_count = 0;
67.  
68.  
69. while($result_row = mysqli_fetch_row($result)) {
70.  
71. /**
72. * For the first time when $insert_count is 0 and when $insert_count reached the $insert_limit
73. * and again set to 0 this if condition will execute and append the INSERT query in the sql file.
74. */
75. if($insert_count == 0) {
76. $contents .= "--\n-- Dumping data for table `".$table."`\n--\n\n";
77. $contents .= "INSERT INTO ".$table." VALUES ";
78. }
79.  
80. //Values part of an INSERT query will start from here eg. ("1","mitrajit","India"),
81. $insert_query = "";
82. $contents .= "\n(";
83. for($j=0; $j<$no_of_columns; $j++) {
84. //Replace any "\n" with "\\n" escape character.
85. //addslashes() function adds escape character to any double quote or single quote eg, \" or \'
86. $insert_query .= "'".str_replace("\n","\\n", addslashes($result_row[$j]))."',";
87. }
88. //Remove the last unwanted comma (,) from the query.
89. $insert_query = substr($insert_query, 0, -1)."),";
90.  
91. /*
92. * If $insert_count reached to the insert limit of a single INSERT query
93. * or $insert count reached to the number of total rows of a table
94. * or overall total count reached to the number of total rows of a table
95. * this if condition will exceute.
96. */
97. if($insert_count == ($insert_limit-1) || $insert_count == ($no_of_rows-1) || $total_count == ($no_of_rows-1)) {
98. //Remove the last unwanted comma (,) from the query and append a semicolon (;) to it
99. $contents .= substr($insert_query, 0, -1);
100. $contents .= ";\n\n\n\n";
101. $insert_count = 0;
102. } else {
103. $contents .= $insert_query;
104. $insert_count++;
105. }
106.  
107. $total_count++;
108. }
109. }
110.  
111.  
112. //Set the HTTP header of the page.
113. header('Content-Type: application/octet-stream');
114. header("Content-Transfer-Encoding: Binary");
115. header("Content-disposition: attachment; filename=\"".$backup_file_name."\"");
116. echo $contents; exit;
117. }
118. ?>

The PHP array_push() function adds elements into an array and the addslashes() function quote string with slashes. The substr() function returns a part of the string.

Change the Database host, name, username, and password according to your database credentials. Except for these changes, you do not need to do anything. Just run the code and backup or export the MySQL database to your system.

That’s all friends, Hope you enjoyed this tutorial. If you face any problem feel free to inform me in the comment section below. Please like and share this tutorial on how to backup or export MySQL database using PHP with others. Kindly subscribe F5CRAFT to keep yourself updated with the upcoming tutorials.

Need a Website Or Web Application Or Any Help In Code , Contact Us: +91 8778409644 (Whatsapp) or Email: uma@f5craft.com | Visit: www.f5craft.in /.com, Note: Paid Service Only