mysqldump is a program to do a dump of a MySQL database. It creates a .sql file, which you can then use to restore the database.
Back up a MySQL database:
mysqldump -u mysql_user -h ip -pmysql_password database_name > database_name.sql
To restore a database from the database_name.sql file:
mysql -u mysql_user -h ip -pmysql_password database_name < database_name.sql
Backup all databases on the server:
Interestingly, you can backup all databases on the server:
mysqldump -u mysql_user -h ip -pmysql_password -A > all_databases.sql
To restore all databases:
mysql -u mysql_user -h ip -pmysql_password < all_databases.sql
Backup a table on a MySQL database:
You can also do mysqlduml at the table level:
mysqldump -u mysql_user -h ip -pmysql_password database_name table_name > table_name.sql
To restore the table to the database:
mysql -u mysql_user -h ip -pmysql_password database_name table_name < table_name.sql
I have a bunch of MySQL databases hosted on a bunch of servers. I have been pretty lazy to back them up regularly. So I wrote a quick bash script to back up the MySQL databases, creating a separate backup file for each database.
Here’s what the script does in short:
1. You provide it a list of ip address, username and password.
2. It will mysqldump all the databases on each host server that the user has access to.
3. It will store all the dumps in the backup_dir and compress each dump using gunzip.
Bash doesn’t really support multi dimensional arrays. So I had to store the ip, username and password as a comma separated string and split it up in each iteration. Ugly, but gets the job done for now.
#!/bin/bash
# Script to do back of all mysql databases on different hosts.
servers=(
"10.10.10.10,mysql_user0,mysql_password0"
"10.10.10.11,mysql_user1,mysql_password1"
"10.10.10.12,mysql_user2,mysql_password2"
)
function mysql_dump() {
local ip="$1"
local mysql_user="$2"
local mysql_password="$3"
mysql_databases=`mysql -u ${mysql_user} -p${mysql_password} -h ${ip} -e "show databases"| sed /^Database$/d`
for database in $mysql_databases
do
if [ "${database}" == "information_schema" ]; then
echo "Skipping $database"
else
echo "Backing up ${database}"
mysqldump -u ${mysql_user} -p${mysql_password} -h ${ip} ${database} | gzip > "${backup_dir}/${database}.gz"
fi
done
}
parent_dir="/home/sacharya/database_backup"
backup_date=`date +%Y_%m_%d_%H_%M`
backup_dir=${parent_dir}/${backup_date}
mkdir -p "${backup_dir}"
saveIFS=$IFS
for server in ${servers[@]}
do
IFS=","
cols=($server)
IFS=$saveIFS
mysql_dump ${cols[0]} ${cols[1]} ${cols[2]}
done