How to automatically back up MySQL database and Web server in FTP repository

This article contains one of the oldest methods proposed by NIX Craft in 2006. The article, in my opinion, is valuable in that it contains, as it were, a basic line of thought, in which a novice or “random” (forced to administer databases in addition to other tasks) system administrator can follow.
In my opinion, an understanding of the basic principles set forth in this article is akin to understanding the principles of making pilaf. You can experiment with pilaf in a fairly wide range; the main thing is not to boil rice into sticky porridge and not stick it into pilaf instead of fatty soft meat, it is not clear what. Likewise , the MySQL database backup strategycan vary within a very wide range, but the basics - a combination of a full backup with incremental backups, establishing the frequency of individual tasks and controlling their correct execution - remain unchanged regardless of the tools used.
The original article is taken here: http://www.cyberciti.biz/tips/how-to-backup-mysql-databases-web-server-files-to-a-ftp-server-automatically.html . Next comes the translation text itself.

* * *

This is a simple backup solution for administrators who run their own MySQL web server and database management systems on dedicated or VPS servers. Most specialized hosting providers provide the user with backup services to a dedicated network storage (NAS) or FTP server. These providers bind the user to their overly functional storage arrays through a private virtual network. Since I managed to manage many server projects, I am ready to offer my own automated solution instead. If you just want a shell script, go here (you just need to provided appropriate input and it will generate FTP backup script for you on fly, you can also grab my php script generator code).

Incremental backup using tar utility

You can create backups on magnetic tape or tape drive (or work with any file as with a tape drive or tape). But nowadays this solution is losing functionality. The GNU tar utility allows you to create incremental backups using the -g option. In the following example, the tar command will create incremental copies of the / var / www / html, / home, and / etc directories. Run it in the console:
# tar -g /var/log/tar-incremental.log -zcvf /backup/today.tar.gz / var / www / html / home / etc
Here, the -g switch creates / displays / retrieves a new one ( incremental) information from the backup and places it in the /var/log/tar-incremental.log file.

Creating MySQL Database Backups

The mysqldump client application is for dumping or backing up databases, tables, and individual MySQL data. For example, the following command will show the list of databases in a specific DBMS:
$ mysql -u root -h localhost -p -Bse 'show databases'
Output (shown as an example):
brutelog
cake
faqs
mysql
phpads
snews
test
tmp
van
wp
Now you can create a backup a copy of each of the databases using the mysqldump command; for example, for a faqs database, the command might look like this:
$ mysqldump -u root -h localhost -pmypassword faqs | gzip -9> faqs-db.sql.gz

Create a simple backup scheme for your project

The principal advantage of using remote network storage (FTP or NAS) for storing backups is additional protection against data loss. You can use several protocols to transfer backups:

1. FTP
2. Ssh
3. RSYNC
4. A variety of commercial solutions

I want to describe here only a solution designed for FTP backup. The idea behind the backup strategy underlying this solution is this:

• Creating a full backup of our database around midnight every Sunday (that is, on Sundays, a backup copy of the entire contents of the DBMS is recreated);
• During the week, only the changed data is backed up (incremental backup).
• The backup cycle is repeated every 7 days.

Parameters of our test configuration

Our server ===> ftp / nas server
IP: 202.54.1.10 ===> 208.111.2.5
Suppose for the test the following detailed information about our FTP account:

• FTP server IP address: 208.111.2.5
• FTP Account Name: nixcraft
• FTP account password: somepassword
• FTP directory: / home / nixcraft (or /)

Places where we will place the backups:
=> / home / nixcraft / full / dd-mm-yy / files - full;
=> / home / nixcraft / incremental / dd-mm-yy / files - incremental.
Here dd-mm-yy are the backup dates.

Automatic backup using tar utility

Now you know how to back up MySQL files and databases using the tar and mysqldump commands. It's time to automate the entire procedure once and for all by linking these commands into a single script.

1. To get started, our script collects all the data from both the MySQL server and the file system into a temporary directory called / backup. To do this, use the tar command.
2. Next, the script connects to your storage server via FTP and creates the directory structure, which was described above.
3. The script flushes files from the / backup directory to the FTP server.
4. The temporary files are removed from the / backup directory.
5. If the backup to FTP is interrupted for some reason or is unsuccessful, the script will notify you by e-mail.

To use the script correctly, you must have the following packages installed (the ncftp utility is used as the FTP client):

• ncftp
• mysqldump
• GNU tar

A listing of our example, called ftpbackup.sh, is shown below:
#! / Bin / sh
# System + MySQL backup script
# Full backup day - Sun (rest of the day do incremental backup)
# Copyright © 2005-2006 nixCraft < www.cyberciti .biz / fb >
# This script is licensed under GNU GPL version 2.0 or above
# Automatically generated by bash.cyberciti.biz/backup/wizard-ftp-script.php

### System Setup ###
DIRS = "/ home / etc / var / www "
BACKUP = / tmp / backup. $$
NOW = $ (date +"% d-% m-% Y ")
INCFILE =" / root / tar-inc-backup.dat "
DAY = $ ( date + "% a")
FULLBACKUP = "Sun"
### MySQL Setup ###
MUSER = "admin"
MPASS = "mysqladminpassword"
MHOST = "localhost"
MYSQL = "$ (which mysql)"
MYSQLDUMP = "$ (which mysqldump)"
GZIP = "$ (which gzip)"
### FTP server Setup ###
FTPD = "/ home / vivek / incremental "
FTPU =" vivek "
FTPP =" ftppassword "
FTPS =" 208.111.11.2 "
NCFTP =" $ (which ncftpput) "
### Other stuff ###
EMAILID =" admin@theos.in "
### Start Backup for file system ###
[! -d $ BACKUP] && mkdir -p $ BACKUP ||:
### See if we want to make a full backup ###
if ["$ DAY" == "$ FULLBACKUP"]; then
FTPD = "/ home / vivek / full"
FILE = "fs-full- $ NOW.tar.




tar -g $ INCFILE -zcvf $ BACKUP / $ FILE $ DIRS
fi
### Start MySQL Backup ###
# Get all databases name
DBS = "$ ($ MYSQL -u $ MUSER -h $ MHOST -p $ MPASS -Bse 'show databases') "
for db in $ DBS
do
FILE = $ BACKUP / mysql- $ db. $ NOW - $ (date +"% T "). gz
$ MYSQLDUMP -u $ MUSER -h $ MHOST -p $ MPASS $ db | $ GZIP -9> $ FILE
done
### Dump backup using FTP ###
#Start FTP backup using ncftp
ncftp -u "$ FTPU" -p "$ FTPP" $ FTPS < mkdir $ FTPD
mkdir $ FTPD / $ NOW
cd $ FTPD / $ NOW
lcd $ BACKUP
mput *
quit
EOF
### Find out if ftp backup failed or not ###
if ["$?" == "0"]; then
rm -f $ BACKUP / *
else
T = / tmp / backup.fail
echo "Date: $ (date)"> $ T
echo "Hostname: $ (hostname)" >> $ T
echo "Backup failed" >> $ T
mail -s “BACKUP FAILED” "$ EMAILID" <$ T
rm -f $ T
fi

How to set automatic periodic backup script execution using cron utility?

Just add the cron task with the required time and frequency parameters:
13 0 * * * /home/admin/bin/ftpbackup.sh> / dev / null 2> & 1

As a short afterword from the translator.
Since data backup tasks are among the most frequently worrying problems for the system administrator, the applicability of a successful script in this area, even without alterations and changes, can turn out to be very high for 5, 10, or 20 years. In particular, a script similar to that described in the article, only made initially under Windows, worked for several years on one of the servers of our institute, until we replaced it with a more modern and advanced GUI program.

It is clear that serious administrators are able to create such scripts on their own, without much effort. My goal was to show the logic on which we, scientists, who, due to well-known economic reasons, were forced to work with computer databases, learned how to build tasks for interacting with the OS and DBMS at the command language level. Such users who are only mastering scripts and database administration, such examples can be extremely useful.

These are ideological considerations, so to speak. A foreign script can be quite accurately compared with a chess sketch, which exponentially solves a particular problem. Classical solutions, like the one given above, can become a time-tested field for independent experiments, show the logic and direction of thought for those who are not satisfied with the functionality of products with a GUI and want to try to bring to life the full power of batch commands. Therefore, for me, it’s important not so much ago, or, conversely, the effectiveness of the proposed solution, how much its internal logic and explainability of each of the points. Taking it as a base, you can easily create your own "sketch" for another DBMS or a different range of tasks; the general logic of thinking, set once, will in the future become a valuable addition to the arsenal of the administrator or developer.

The ease of understanding, the fulfillment of the stated basic conditions (which were discussed at the beginning of the article) and the small size of this script allow me to recommend it as a base for various experiments with backing up MySQL data. Well, and if this "study" is completely out of date - well, maybe that is. It will be interesting to compare it with the solutions that modern programmers and system administrators are now offering for the same task. Welcome to the comments with your own scripts that can put the above product on your belt.