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