How to Backup MySQL Database using PHP

Hello folks! welcome back to a new section of our tutorial on PHP. In this tutorial guide, we will be discussing about how to backup a MySQL database using PHP.

It is always a very good practice to take a regular backup of your database. There are three ways that you can use to take backup of your MySQL database.

• Using the SQL Command through PHP.

• Using the MySQL binary mysqldump through PHP.

• Using phpMyAdmin user interface.

READ: How to Delete Data from MySQL Database using PHP

Using SQL Command through PHP

You can implement SQL SELECT command to take backup of any table. In order for you to take a complete database dump, you will need to write separate query for each table. Each table will be stored into separate text file.

Example

Try the following example below by using a SELECT INTO OUTFILE query for creating a table backup -

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
	
   $table_name = "employee";
   $backup_file  = "/tmp/employee.sql";
   $sql = "SELECT * INTO OUTFILE '$backup_file' FROM $table_name";
   
   mysql_select_db('test_db');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not take data backup: ' . mysql_error());
   }
   
   echo "Backedup  data successfully\n";
   
   mysql_close($conn);
?>

READ: How to Connect MySQL Database using PHP

There may be cases when you would need to restore data which you have backed up some time ago. To restore the backup, you just need to run LOAD DATA INFILE query like this -

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
	
   $table_name = "employee";
   $backup_file  = "/tmp/employee.sql";
   $sql = "LOAD DATA INFILE '$backup_file' INTO TABLE $table_name";
   
   mysql_select_db('test_db');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not load data : ' . mysql_error());
   }
   echo "Loaded  data successfully\n";
   
   mysql_close($conn);
?>

READ: PHP Date and Time

Using MySQL binary mysqldump through PHP

MySQL makes available to its users a utility mysqldump to perform a database backup. Using this binary you can take a complete database dump in just a single command.

Example

Try out the following example to take your complete database dump -

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   
   $backup_file = $dbname . date("Y-m-d-H-i-s") . '.gz';
   $command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass ". "test_db | gzip > $backup_file";
   
   system($command);
?>

Using phpMyAdmin user interface

If you have the phpMyAdmin user interface available then it will be easy for you to take a backup of your database.

In order to backup a MySQL database using phpMyAdmin click on the export link on the phpMyAdmin main page. Pick the database you wish to backup, check the proper SQL options and enter the name for the backup file.

READ: PHP Error and Exception Handling

Alright guys! This is where we are rolunding up for this tutorial post. In our next tutorial, we are going to be discussing about how to use PHP and AJAX.

Feel free to ask your questions where necessary and we will attend to them as soon as possible. If this tutorial was helpful to you, you can use the share button to share this tutorial.

Follow us on our various social media platforms to stay updated with our latest tutorials. You can also subscribe to our newsletter in order to get our tutorials delivered directly to your emails.

Thanks for reading and bye for now.