Backup and Restore MySQL database using mysqldump


Backup Single MySQL Database:

# mysqldump -u root -p database_name > database_name.sql

Backup Multiple MySQL Databases

# mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql

Backup All MySQL Databases

# mysqldump -u root -p --all-databases > all_databases.sql

Backup all MySQL databases to Separate files using scripts

mysqlbackup_all.sh

#! /bin/bash

TIMESTAMP=$(date +"%F")

BACKUP_DIR="/backup/$TIMESTAMP"

MYSQL_USER="backup"

MYSQL=/usr/bin/mysql

MYSQL_PASSWORD="password"

MYSQLDUMP=/usr/bin/mysqldump

mkdir -p "$BACKUP_DIR/mysql"

databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`

for db in $databases; do

$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/mysql/$db.gz"

Done

Create a Compressed MySQL Database Backup

mysqldump database_name | gzip > database_name.sql.gz

Create a Backup with Timestamp

mysqldump database_name > database_name-$(date +%Y%m%d).sql

Restoring a MySQL dump

You can restore a MySQL dump using the mysql tool. The command general syntax is as follows:

mysql database_name < file.sql

In most cases you’ll need to create a database to import into. If the database already exists, first you need to delete it.

In the following example the first command will create a database named database_name and then it will import the dump database_name.sql into it:

mysql -u root -p -e "create database database_name";

mysql -u root -p database_name < database_name.sql

Restore a Single MySQL Database from a Full MySQL Dump

mysql --one-database database_name < all_databases.sql