MYSQL: Mysql Backup Options
1. Backing Up With MySQLDump
Backing up a single DB:
mysqldump –user [user name] –password databasename > dumpfilename
Backing up an entire MySQL server:
mysqldump –user=[user name] –password -A > dumpfilename
Backing up individual tables:
mysqldump –user=[user name] –password –databases DB_NAME –tables TABLE_NAME > dumpfilename
2. How to Backup MySQL Database automatically (for Linux users)
Create a cron job:
15 2 * * * /usr/bin/mysqldump –opt –all-databases -u root -pPASSWORD > /foo/bar/db-`date +%Y-%m-%d`.sql
3. Backup Your Database into an XML File Using PHP
mysqldump —user [user name] —password=[password] [database name] > [dump file]
4. Dump a remote database locally
ssh user@server “/usr/bin/mysqldump -u user -p password database_name” | dd of=/where/you/want/the/dump.sql
5. How to – Using PHP To Backup MySQL Database
Execute a database backup query from PHP file. Below is an example of using SELECT INTO OUTFILE query for creating table backup:
view plaincopy to clipboardprint?
<?php
include ‘config.php’;
include ‘opendb.php’;
$tableName = ‘mypet’;
$backupFile = ‘backup/mypet.sql’;
$query = “SELECT * INTO OUTFILE ‘$backupFile’ FROM $tableName”;
$result = mysql_query($query);
include ‘closedb.php’;
?>
To restore the backup you just need to run LOAD DATA INFILE query like this :
view plaincopy to clipboardprint?
<?php
include ‘config.php’;
include ‘opendb.php’;
$tableName = ‘mypet’;
$backupFile = ‘mypet.sql’;
$query = “LOAD DATA INFILE ‘backupFile’ INTO TABLE $tableName”;
$result = mysql_query($query);
include ‘closedb.php’;
?>
6. How to e-mail yourself an automatic backup of your MySQL database table with PHP
This script will send an e-mail to you with an .sql file attached, thus enabling you to back up specific tables easily. You could even set up an e-mail account just to receive these backups… A file over 2MB may cause PHP issues
<?php
// Create the mysql backup file
// edit this section
$dbhost = “yourhost”; // usually localhost
$dbuser = “yourusername”;
$dbpass = “yourpassword”;
$dbname = “yourdb”;
$sendto = “Webmaster <[email protected]>”;
$sendfrom = “Automated Backup <[email protected]>”;
$sendsubject = “Daily Mysql Backup”;
$bodyofemail = “Here is the daily backup.”;
// don’t need to edit below this section
$backupfile = $dbname . date(“Y-m-d”) . ‘.sql’;
system(“mysqldump -h $dbhost -u $dbuser -p$dbpass $dbname > $backupfile”);
// Mail the file
include(‘Mail.php’);
include(‘Mail/mime.php’);
$message = new Mail_mime();
$text = “$bodyofemail”;
$message->setTXTBody($text);
$message->AddAttachment($backupfile);
$body = $message->get();
$extraheaders = array(“From”=>”$sendfrom”, “Subject”=>”$sendsubject”);
$headers = $message->headers($extraheaders);
$mail = Mail::factory(“mail”);
$mail->send(“$sendto”, $headers, $body);
// Delete the file from your server
unlink($backupfile);
?>
7. Ubuntu Linux Backup MySQL server Shell Script
If you have a dedicated VPS server running Ubuntu Linux. Here is how to backup all your mysql server databases to your ftp server
Install lftp
lftp is a file transfer program that allows sophisticated ftp, http and other connections to other hosts. If site is specified then lftp will connect to that site otherwise a connection has to be established with the open command. To install lftp, enter:
sudo apt-get install lftp
Shell script to backup MySQL database server
Following is the shell script. It will dump all database to /backup/mysql and later it will upload to FTP server. You need to setup correct username and password before using the script:
#!/bin/bash
### MySQL Server Login Info ###
MUSER=”root”
MPASS=”MYSQL-ROOT-PASSWORD”
MHOST=”localhost”
MYSQL=”$(which mysql)”
MYSQLDUMP=”$(which mysqldump)”
BAK=”/backup/mysql”
GZIP=”$(which gzip)”
### FTP SERVER Login info ###
FTPU=”FTP-SERVER-USER-NAME”
FTPP=”FTP-SERVER-PASSWORD”
FTPS=”FTP-SERVER-IP-ADDRESS”
NOW=$(date +”%d-%m-%Y”)
### See comments below ###
### [ ! -d $BAK ] && mkdir -p $BAK || /bin/rm -f $BAK/* ###
[ ! -d “$BAK” ] && mkdir -p “$BAK”
DBS=”$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse ‘show databases’)”
for db in $DBS
do
FILE=$BAK/$db.$NOW-$(date +”%T”).gz
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done
lftp -u $FTPU,$FTPP -e “mkdir /mysql/$NOW;cd /mysql/$NOW; mput /backup/mysql/*; quit” $FTPS
Save script as /home/your-name/mysql.backup.sh file. Setup executable permission:
$ chmod +x /home/your-name/mysql.backup.sh
To backup MySQL, enter:
/home/your-name/mysql.backup.sh
OR
sudo /home/your-name/mysql.backup.sh
Run MySQL backup script as cron job
To automate procedure setup a cron job. For example run backup everyday at midnight (i.e once a day), enter:
$ sudo crontab -e
Append following cron job:
@midnight /home/you/mysql.backup.sh >/dev/null 2>&1
