✎ MySQL Backup under Linux [NoSociety.de]

You've loaded an old revision of the document! If you save it, you will create a new version with this data.

====== MySQL backup under Linux ======

===== Introduction =====

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.

===== 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 information on how to use it:

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

===== 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 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.

==== Back up all databases ====

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:

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

===== Restore the MySQL database =====

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

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

Please solve the following equation to prove you're human. 30 +11 = Please keep this field empty:

Edit summary

Note: By editing this page you agree to license your content under the following license: CC Attribution-Noncommercial-Share Alike 4.0 International