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.
