Web | This Site

Learning PHP
Advanced PHP
PHP Function Reference
PHP Useful Resources
Selected Reading
© 2011 TutorialsPoint.COM

| Home |  | References |  | Discussion Forums |  | About TP

# Perform MySQL backup using PHP

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

- Using SQL Command through PHP.

- Using MySQL binary mysqldump through PHP.

- Using phpMyAdmin user interface.

## Using SQL Command through PHP

You can execute SQL SELECT command to take a backup of any table. 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 out following example of using 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 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);
    ?>

## Using MySQL binary mysqldump through PHP:

MySQL provides one utility mysqldump to perform database backup. Using this binary you can take complete database dump in a single command.

### Example:

Try out 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 phpMyAdmin user interface available then its very easy for your to take backup of your database.

To backup your MySQL database using phpMyAdmin click on the "export" link on phpMyAdmin main page. Choose the database you wish to backup, check the appropriate SQL options and enter the name for the backup file.

Advertisement

Online Image Processing

Indian Baby Names