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.

    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.

    People are also reading:

    Previous Tutorial Next Tutorial