Backup MySQL : Execute database query using PHP file.

Below is an example of using SELECT INTO OUTFILE query for creating table backup 

<?php

$dbhost = '';

$dbuser = ' ';

$dbpass = ' ';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'db_name';

mysql_select_db($dbname);

$tableName  = 'table_name';

$backupFile = '/backup/file_name.sql';

$query      = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";

$result = mysql_query($query) or die();

mysql_close($conn);

}

?>

To restore the backup you just need to run LOAD DATA INFILE query like this :

<?

 $tableName  = 'table_name';

$backupFile = 'file.sql';

$query      = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";

$result = mysql_query($query);

?>

Facing Problem :

when you try to create output file on server may be faced 

Error : Can't create/write to file '/var/www/test/backup/mypets.sql' (Errcode: 13)

Solution :

Recent Ubuntu Server Editions (such as 10.04) ship with AppArmor and MySQL's profile might be in enforcing mode by default. You can check this by executing sudo aa-status like so:

# sudo aa-status

5 profiles are loaded.

5 profiles are in enforce mode.

   /usr/lib/connman/scripts/dhclient-script

   /sbin/dhclient3

   /usr/sbin/tcpdump

   /usr/lib/NetworkManager/nm-dhcp-client.action

   /usr/sbin/mysqld

0 profiles are in complain mode.

1 processes have profiles defined.

1 processes are in enforce mode :

   /usr/sbin/mysqld (1089)

0 processes are in complain mode.

If mysqld is included in enforce mode, then it is the one probably denying the write. Entries would also be written in /var/log/messages when AppArmor blocks the writes/accesses. What you can do is edit /etc/apparmor.d/usr.sbin.mysqld and add /backup/ or /data/* near the bottom like so:

...

/usr/sbin/mysqld {

    ...

    /var/log/mysql/ r,

    /var/log/mysql/* rw,

    /var/run/mysqld/mysqld.pid w,

    /var/run/mysqld/mysqld.sock w,

    /data/ r,

    /data/* rw,

   /backup/* rw,

}

And then make AppArmor reload the profiles.

# sudo /etc/init.d/apparmor reload

WARNING: the change above will allow MySQL to read and write to the /data directory. We hope you've already considered the security implications of this.