How to backup and restore MySQL database

Using mysqldump, you can backup a local database and restore it on a remote database at the same time, using a single command. In this article, let us review several practical examples on how to use mysqldump to backup and restore.

Single Database

Backup:

mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

Restore:

mysql -u root -p[root_password] [database_name] < dumpfilename.sql

Multiple Databases

Backup:

mysqldump -u root -ptmppassword --databases db1 db2 > db1_db2.sql

Backup all databases:

mysqldump -u root -ptmppassword --all-databases > all-database.sql

Backup Multiple Databases with table prefix

#!/bin/bash
# Script to dump specific tables from the database

DB_HOST="localhost"
DB_USER="dbUser"
DB_PASS="dbSecretPassword"
DB_NAME="dbName"

DATED=`date '+%Y-%m-%d-%H:%M:%S'`

PREFIX="table_prefix"

# Dump SQLs
mysqldump --no-tablespaces --host ${DB_HOST} --user=${DB_USER} --password=${DB_PASS} ${DB_NAME} $(mysql --host ${DB_HOST} --user=${DB_USER} --password=${DB_PASS} -D ${DB_NAME} -Bse "SHOW TABLES LIKE '${PREFIX}%'") > "${DATED}.SQL"