Backing up the database using PHP

By | July 7, 2020
Backing up the database using PHP

Backing up data will help you to keep your data safe in case of any failure and you can retrieve data from the backup file any time you lost your data. PHP allows you to create a backup of the database in three different ways.

  • Using SQL command
  • Using binary mysqldump
  • Using the phpMyAdmin user interface

Using SQL command

SQL command can be used to create a table database, if you are looking for complete database backup then for each table separate query will be used and will be stored in a different file.

Vamware

Example:

<?php
   $db_host = 'localhost:3036';
   $db_user = 'root';
   $db_pass = 'rootpassword';
   $db_conn = mysql_connect($db_host, $db_user, $db_pass);  
   if(! $db_conn ) {
      die('Connection failed: ' . mysql_error());
   }
$table_name = "student";
   $back_file  = "/tmp/student.sql";
   $db_sql = "SELECT * INTO OUTFILE '$back_file' FROM $table_name";
mysql_select_db('demo_db');
   $db_retval = mysql_query( $db_sql, $db_conn );
if(! $db_retval ) {
      die('backup cannot be done: ' . mysql_error());
   }
echo "backup created successfully\n";
   mysql_close($db_conn);
?>

If you want to restore the data from the created backup then you can use the below SQL statement.

$db_sql = "LOAD DATA INFILE '$back_file' INTO TABLE $table_name";

Using binary mysqldump

Mysqldump is a MySQL utility that enables you to create the backup of the database. Using this the backup is done using a single line command.

Example-

<?php
   $db_host = 'localhost:3036';
   $db_user = 'root';
   $db_pass = 'rootpassword';
   $back_file = $dbname . date("Y-m-d-H-i-s") . '.gz';
   $db_command = "mysqldump --opt -h $db_host -u $db_user -p $db_pass ". "demo_db | gzip > $back_file";
   system($db_command);
?>

Using a phpMyAdmin user interface

phpMyAdmin provides an easy and direct way to create a backup through its user interface. You can open the phpMyAdmin page and click the export link to back up the MySQL database. You can then select appropriate options and the right right to create the backup.

Leave a Reply

Your email address will not be published. Required fields are marked *