Geekly Articles each Day
📜 ⬆️ ⬇️

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


This article contains one of the oldest methods proposed by the NIX Craft in 2006. The article, in my opinion, is valuable in that it contains, as it were, the basic direction of thought, in which a beginner or a “casual” (system administration system, forced to deal with databases in addition to other tasks), can follow.
In my opinion, an understanding of the basic principles outlined in this article is akin to an understanding of the principles of pilaf production. You can experiment with the pillow in a fairly wide range; the main thing is not to boil rice into sticky porridge and not to put in pilaf instead of fat soft meat it is not clear what. Similarly , the MySQL database backup strategy can vary within very wide limits, but the basics — a combination of full and incremental backups, establishing the periodicity of individual tasks and monitoring their proper execution — remain the same 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.

* * *

Here is a simple backup solution for administrators who maintain their own MySQL web servers and database management systems (DBMS) on dedicated or VPS servers. Most specialized hosting providers provide the user with backup services in a dedicated network storage (NAS) or FTP server. These providers bind the user to their redundantly functional storage arrays via a private virtual network. Since I’ve managed to manage multiple server projects, I’m ready to offer my own automated solution instead. If you really want a script, go ahead, you can grab my php script generator code.

Incremental backup using tar utility

You can create backups on a tape or tape drive (or work with any file as with a tape drive or tape). But nowadays this solution loses its functionality. The GNU tar utility allows you to create incremental backups with 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 / extracts new (incremental) information from the backup and places it in the /var/log/tar-incremental.log file.
')
Backup MySQL databases

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

Creating a simple backup scheme for your project

The fundamental advantage of using remote network storage (FTP or NAS) for storing backups is additional protection against data loss. For transferring backups you can use several protocols:
  1. FTP
  2. Ssh
  3. RSYNC
  4. A variety of commercial solutions

I want to describe here only the solution intended for backup by FTP. The idea behind the backup strategy is:


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:

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

Automatic backup using the tar utility

Now you know how to back up MySQL files and databases using the tar and mysqldump commands. It's time to automate once and for all the procedure, linking these commands into a single script.
  1. To begin with, our script collects all 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 data storage server via FTP and creates the directory structure described above.
  3. The script resets files from the / backup directory to the FTP server.
  4. Temporary files are removed from the / backup directory.
  5. If the backup to FTP is interrupted for any reason or is unsuccessful, the script notifies you by email.

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

A listing of our example, named ftpbackup.sh, is shown below:
#! / bin / sh
# System + MySQL backup script
# Full backup day - Sun (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 ||:
###
if ["$ DAY" == "$ FULLBACKUP"]; then
FTPD = "/ home / vivek / full"
FILE = "fs-full- $ NOW.tar.gz"
tar -zcvf $ BACKUP / $ FILE $ DIRS
else
i = $ (date + "% Hh% Mm% Ss")
FILE = "fs-i- $ NOW- $ i.tar.gz"
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 << EOF
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 install automatic periodic execution of the backup script using the cron utility?

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

As a small afterword from the translator.
Since data backup tasks are among the most frequently troubling issues for the system administrator, the applicability of a successful script in this area, even without alterations and changes, can be very high for 5, 10 or 20 years. In particular, a script similar to the one 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 sophisticated GUI program.

It is clear that serious administrators are able to create such scripts on their own, without much effort. I pursued a goal - to show the logic on which we, scientists, forced by virtue of well-known economic reasons to work also with computer databases, learned to build interaction tasks with OS and DBMS at the command language level. For such users, who only master the scripts and administer the database, such examples can be extremely useful.

These are considerations, so to speak, of an ideological order. Someone else's script can be quite accurately compared with a chess etude, indicatively solving one or another task. Classic solutions, like the one presented 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 GUI products and want to try to bring all the power of batch commands to life. Therefore, for me, it is not so much the prescription that matters, or, conversely, the effectiveness of the proposed solution, but its internal consistency and the explanability of each of the points. Taking it as a base, you can easily create your own “sketch” for another DBMS or another range of tasks; A common logic of thinking, given once, will become in the future a valuable addition to the arsenal of an administrator or developer.

The simplicity of understanding, the fulfillment of the stated basic conditions (which were mentioned 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 MySQL data backup. Well, and if this “etude” is completely outdated - well, it is possible that. 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 plug the above product.

Source: https://habr.com/ru/post/239699/

More articles:


All Articles