Thursday, October 8, 2020

How to Backup MySQL Database using PHP



Hello dear readers! welcome back to another section of my tutorial on PHP. In this tutorial guide, we will be discussing about how to backup a MySQL database using PHP.

It's always a 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.

  • By using the SQL Command through PHP.
  • By using the MySQL binary mysqldump through PHP.
  • By using phpMyAdmin user interface.



Using SQL Command through PHP

You can implement SQL SELECT command to take backup of any table. In order to take a complete database dump, you will need to write separate query for separate 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 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);
?>


There may be instances when you would need to restore data which you have backed up already. 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);
?>

RECOMMENDED: PHP Date and Time


Using MySQL binary mysqldump through PHP

MySQL provides 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 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 your MySQL database using phpMyAdmin click the "export" link on phpMyAdmin main page. Choose the database that you wish to backup, check the proper SQL options and enter the name for the backup file.


Alright guys! This is where we are rounding up for this tutorial post. In my next tutorial guide, we will be discussing about how to use PHP and AJAX.

Feel free to ask your questions where necessary and i 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.
Share:

0 comments:

Post a Comment

Hello dear readers! Please kindly try your best to make sure your comments comply with our comment policy guidelines. You can visit our comment policy page to view these guidelines which are clearly stated. Thank you.