Table of Contents

mysqldump

text-based client for dumping or backing up mysql databases, tables, and or data.

usage

mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --add-databases [OPTIONS]

examples

My example…adds create database statements too so the only thing you need to do is source the file to restore 

mysqldump -u user -p --opt --databases "database" > "database".sql

The most normal use of mysqldump is probably for making a backup of whole databases. See Mysql Manual section 21.2 Database Backups. 

shell> mysqldump --opt database > backup-file.sql

Note: --opt is the same as: --add-drop-table --add-locks --all --extended-insert --quick --lock-tables

Most likely, you will need to provide user credentials: 

shell> mysqldump --opt -u user -p database > backup-file.sql

You can read this back into MySQL with: 

shell> mysql database < backup-file.sql

or

shell> mysql -e 'source /path-to-backup/backup-file.sql' database

or

mysql> source /path-to-backup/backup-file.sql

or, most likely...

mysql> create database databasename;
mysql> use databasename;
mysql> source /path-to-backup/backup-file.sql

* the source command can be followed with a path containing spaces, without escapes or quotes

More options and examples are available in the man page and in the MySQL manual

automated shell script

#!/bin/bash

# user must have select and lock tables privileges

destination='/path-to-dest-dir/MySQL Backups/'

databases="database1
database2
database3"

user=username
pass=password

# make the destination folder, if it doesn't exist
if [ ! -d "$destination" ]; then
	mkdir -p "$destination"
fi

# continue if the directory was successfully created
if [ -d "$destination" ]; then
	for db in $databases
	do
		echo "$db..."
		mysqldump --opt -u$user -p$pass "$db" > "$destination$db".sql
	done
fi
exit 0