MySQL Backup under Linux

MySQL databases have become an integral part of today's servers. Information of all kinds can be stored, retrieved and filtered. MySQL offers a syntax that can be easily integrated into PHP, for example.

To ensure that databases are still available even after a system crash, it is necessary to back them up regularly.

MySQL comes with a tool called mysqldump. With this tool it is possible to make a backup of all databases. Modern systems work with a password-protected query. For this reason, I will only present this variant here.

If we call mysqldump without parameters, we get the following instructions for use:

$ mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

With

$ mysqldump --help

all available options are displayed. However, not all of these are relevant for a backup. The most important options for us are: -u -p

The -u option specifies the user name that has access to the database. The -p option displays a password prompt before the command is executed.

To back up a specific database, we simply use the following syntax:

$ mysqldump -u USER -p database USERDATABASE > usr1_db.sql

USER corresponds to the user. The -p option is passed without an associated parameter. USERDATABASE corresponds to the actual database. If everything has been entered correctly, we will see a password prompt. Here we enter the user's password and should now open the file usr1_db.sql which contains all database entries.

To back up all databases, we must log in as MySQL root. The following syntax is used, which is similar to the one described above:

$ mysqldump -u USER -p --all-databases > full_db.sql

Importing a dump file is just as easy. We use the following syntax for this:

$ mysql -p USERDATABASE -u USER -p < usr1_db.sql

or to restore all databases:

$ mysql -p USERDATABASE -u USER -p < full_db.sql

To back up the database automatically, you can use the following script, which Jerome Griessmeier provides on his Webseite website:

#!/bin/sh 
# Backup Script 
# Author: Jerome Griessmeier 
# Version: 0.2
#
# This Shell Script backup your database
# For automating use a cronjob
 
# 
# Pfade setzen/ Setting path variables
# 
MYSQL_DUMP=/usr/bin/mysqldump 
BACKUP_DIR=/pfad/zu/backup_verzeichnis
TAR=/bin/tar 
RM=/bin/rm 
DB_NAME=DB_NAME 
DB_USER=DB_USER 
DB_PASS=DB_PASS 
AKT_DATUM=`date +%Y%m%d%H%M` 
 
# 
# mysql dump erzeugen / create mysql dump
# 
$MYSQL_DUMP $DB_NAME -u $DB_USER --password=$DB_PASS > 
$BACKUP_DIR/$AKT_DATUM.backup.sql 
 
# 
# mysql dump komprimieren / Compress data
# 
cd $BACKUP_DIR 
$TAR -cvzf $AKT_DATUM.backup.sql.tgz $AKT_DATUM.backup.sql 
 
# 
# aufraeumen / clean up
# 
$RM $AKT_DATUM.backup.sql

crontab -e

create cronjob:

55 2 * * * root /backup/backup.sh >> /dev/null 2>&1

Start of the script at 02.55 every night

http://www.griessmeier.de/linux/mysql_backup.htm