{{tag>linux debian mysql backup}}
====== MySQL Backup under Linux ======

===== Introduction =====

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.

===== mysqldump =====

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:

<code bash>
$ 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
</code>

With

<code bash>$ mysqldump --help</code>

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.

===== Backing up the MySQL database =====

==== Backing up a specific database ====

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

<code bash>$ mysqldump -u USER -p database USERDATABASE > usr1_db.sql</code>

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.

==== Backing up all databases ====

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:

<code bash>$ mysqldump -u USER -p --all-databases > full_db.sql</code>

===== Restoring the MySQL database =====

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

<code bash>$ mysql -p USERDATABASE -u USER -p < usr1_db.sql</code>

or to restore all databases:

<code bash>$ mysql -p USERDATABASE -u USER -p < full_db.sql</code>


===== Automated backup =====

To back up the database automatically, you can use the following script, which Jerome Griessmeier provides on his [[http://www.griessmeier.de/linux/mysql_backup.htm#3-1|Webseite]] website:

<code bash>
#!/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
</code>

==== set up cronjob ====

<code bash>crontab -e</code>

create cronjob:

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

//Start of the script at 02.55 every night//

===== Sources =====

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