Let's see how to backup and restore MySQL databases, with a few extra tricks, using the mysqldump tool.### Setup

sudo apt-get update
sudo apt-get install -y vim tmux curl wget unzip mysql-server-5.6

MySQL:

create database some_db_1;
use some_db_1;
create table users (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    username VARCHAR(255) NOT NULL, 
    password VARCHAR(255) NOT NULL) 
engine=innodb charset=utf8mb4 collate=utf8mb4_general_ci;
insert into users (username, password) values 
    ('fideloper', 'some_password1'), 
    ('bob', 'some_password2'), 
    ('jane', 'some_password3');

create database some_db_2;
use some_db_2;
create table articles (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    content TEXT NOT NULL, 
    author_id INT NOT NULL) 
engine=innodb charset=utf8mb4 collate=utf8mb4_general_ci;
insert into articles (content, author_id) values 
    ('some content here', 1), 
    ('some other content here', 2);

create database some_db_3;
use some_db_3;
create table purchases (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    sku INT NOT NULL, 
    user_id INT NOT NULL) 
engine=innodb charset=utf8mb4 collate=utf8mb4_general_ci;
insert into purchases (sku, user_id) values 
    (112233, 1), 
    (445566, 2);

Backup

Basic usage:

mysqldump -u root -p some_database > some_database.sql

Compress:

# gzip
mysqldump -u root -p some_database | gzip > some_database.sql.gz

# bzip
mysqldump -u root -p some_database | bzip2 > some_database.sql.bz2

Add a date to the filename:

mysqldump -u root -p some_database > some_database_`date +%Y%m%d%H%M`_.sql

If you run these backsups in an automated way, we can add the following command to to delete any backups older than 60 days (based on file creation time):

find /path/to/backup -name "*.sql" -mtime +60 -exec /bin/rm {} \;

You can export one database and import it into another in one command:

mysqldump -u root -p some_database | mysql -h remotehost -u root -p new_db

Here are some other flags we can use with mysqldump:

# Use this to decide not to add/drop tables in exported sql
--add-drop-table

# Dump of all databases
--all-databases, -A
# e.g. --database db1 --database db2
--database

# One query per insert instead of multiple inserts per qery
--extended-insert=FALSE

# Schema only
# Maybe make two dumps - schema vs data
--no-data

# Data only, no schema
--no-create-info

Restore

Restoring from a backup is generally pretty easy. Usually you'll need to create a database to import into, but that's not always true (if your export has a CREATE DATABASE statement in it, then it will also create a new database for you).

# If no "add/drop database" statements
mysql -u root -p -e "create database blah";
mysql -u root -p blah < some_database.sql

Side note, If you're interested in a service to help you manage MySQL-optimized, backup and (eventually) replication-enabled database servers, sign up here to let me know! The idea is to allow you to better manage your MySQL servers, taking advantage of many of MySQL's more advanced options, especially around backup and recovery.