Vardchives

Backup and Restore ​

Description ​

Dump a MySQL database to a file, move it to another server, and load it in. The standard three-step move for copying production down or pushing a database up.

Dump ​

bash
mysqldump -u root -p mydatabase > mydatabase.sql

Transfer ​

bash
scp mydatabase.sql user@host:

Restore ​

bash
mysql -u root -p targetdatabase < mydatabase.sql

Notes ​

-p with no value prompts for the password interactively. Passing it inline as -pSecret works but puts the password in your shell history and in the process list, where any other user on the box can read it via ps. Prefer the prompt, or a ~/.my.cnf with 0600 permissions.

The dump and restore database names don't have to match — dumping myapp_prod into myapp_local is normal, since mysqldump writes table statements, not a CREATE DATABASE. The target database must already exist.

Useful flags:

bash
# Schema only, no rows
mysqldump -u root -p --no-data mydatabase > schema.sql

# Compress in flight — large databases shrink dramatically
mysqldump -u root -p mydatabase | gzip > mydatabase.sql.gz

# Restore a gzipped dump
gunzip < mydatabase.sql.gz | mysql -u root -p targetdatabase

On a Forge server the MySQL user is typically forge rather than root, and scp lands in that user's home directory.