April 12

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


Copyright 2021. All rights reserved.

Posted April 12, 2012 by Timothy Conrad in category "Databases

About the Author

If I were to describe myself with one word it would be, creative. I am interested in almost everything which keeps me rather busy. Here you will find some of my technical musings. Securely email me using - PGP: 4CB8 91EB 0C0A A530 3BE9 6D76 B076 96F1 6135 0A1B