MySQL backup under Linux

Nowadays it is impossible to imagine a server without MySQL databases. Information of any kind can be stored, accessed and filtered. For this purpose, MySQL offers a syntax that can be integrated into e.g. PHP without any problems.

To ensure that databases are still available 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 information on how to use it:

$ 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 option -u specifies the username that has access to the database. The option -p shows a password prompt before executing the command.

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 here. The option -p is passed without associated parameters. USERDATABASE corresponds to the actual database. If everything has been entered correctly, we will see a password prompt. Here we enter the password of the user and should now open the file usr1_db.sql which contains all database entries.

To backup all databases, we need to log in as MySQL root. The following syntax is used, which works similar to the one already described above:

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

Importing a dump file is just as easy. For this we use the following syntax:

$ 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 at 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