LLI Wiki — My Digital Knowledge Base

MySQL: dump, restore and drop all tables

mysqldump and mysql commands to back up, restore, copy and empty MySQL databases, including to a managed Azure server.

This article gathers practical commands to dump, restore, move and wipe MySQL / MariaDB databases from the command line. Passwords are shown as <PASSWORD>; prefer MYSQL_PWD or an option file over a password on the command line.

Connect locally

mysql -h 127.0.0.1 -u root -p
mysql -h 127.0.0.1 -u dba -p

Back up a database

mysqldump -h 127.0.0.1 -u root -p AppDb_Dev > 20190122_AppDb_Dev.sql
mysqldump -h 127.0.0.1 -u root -p AppDb_Prod | gzip -6 > 20190122_AppDb_Prod.sql.gz

A more complete, consistent dump of one database with an environment-variable password:

ts=$(date +%Y%m%d_%H%M%S); set -o pipefail
export MYSQL_PWD='<PASSWORD>'
mysqldump --no-defaults -h mysql01.example.com -u app_admin --ssl-mode=REQUIRED \
  --single-transaction --quick --no-tablespaces --routines --triggers --events \
  --set-gtid-purged=OFF --databases AppDb_Prod | gzip -6 > "${ts}_AppDb_Prod.sql.gz"

All databases, compressed:

mysqldump -u USERNAME -p --all-databases --events --ignore-table=mysql.event \
  --extended-insert --add-drop-database --disable-keys --flush-privileges \
  --quick --routines --triggers | gzip > all_databases.gz

Restore a database

mysql -h 127.0.0.1 -u root -p AppDb_Dev < 20190122_AppDb_Prod.sql

# compressed dump
gunzip < AppDb_Prod-2022-06-02.sql.gz | mysql -h 127.0.0.1 -u root -p AppDb_Dev
zcat AppDb_Prod-2022-06-02.sql.gz | mysql -h 127.0.0.1 -u root -p AppDb_Dev

# a dump produced with --databases or --all-databases already contains CREATE DATABASE / USE
zcat AppDb_Prod-2022-10-28.sql.gz | mysql -u dba -p

If the target database does not exist yet:

mysql -h 127.0.0.1 -u root -p -e "CREATE DATABASE AppDb_Prod"

To restore an existing database from scratch, drop it first (DROP DATABASE IF EXISTS AppDb_Prod;) and create it again.

Restore all databases or only one from a full dump

# all databases
gunzip < alldb.sql.gz | mysql -u root -p -h localhost

# one database, from a per-database dump
gunzip < alldb.sql.gz | mysql -u user -p dbname

# one database out of a full dump, using --one-database
mysqldump -p --all-databases > all_databases.sql
mysql -p --one-database db1 < all_databases.sql

# extract one database section from a full dump file
sed -n '/^-- Current Database: `db_name`/,/^-- Current Database: `/p' all_databases.sql > db.sql

Copy a database to another server

Pipe the dump straight into the remote server:

mysqldump -u root -p AppDb_Prod | mysql -h remote_host -u root -p AppDb_Prod

Through SSH (set up key authentication so no password is requested):

mysqldump dbname | ssh user@example.com "mysql -D dbname"

Empty a database before restoring

A dump made with the default options already contains DROP TABLE IF EXISTS before each CREATE TABLE. If you need to empty the target first, the following methods work. Disable foreign-key checks so tables can be dropped in any order.

Prepared statement dropping all tables

select database();
use AppDb_Dev;

SET FOREIGN_KEY_CHECKS = 0;
SET @tables = NULL;
SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables
  FROM information_schema.tables
  WHERE table_schema = 'AppDb_Dev'; -- specify the database name here
SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;

Generate the DROP statements, then run them

SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'AppDb_Prod';

Run the generated list between the two foreign-key statements:

SET FOREIGN_KEY_CHECKS = 0;
-- semicolon separated list of DROP statements here
SET FOREIGN_KEY_CHECKS = 1;

One-liner using mysqldump

--add-drop-table --no-data produces only the structure with its DROP statements; keep the DROP lines and replay them:

mysqldump -u${user} -p -h ${host} --add-drop-table --no-data ${database} | grep ^DROP | mysql -u${user} -p -h ${host} ${database}

Shell script dropping all tables

#!/bin/bash
MUSER="$1"
MPASS="$2"
MDB="$3"

# Detect paths
MYSQL=$(which mysql)
AWK=$(which awk)
GREP=$(which grep)

if [ $# -ne 3 ]
then
	echo "Usage: $0 {MySQL-User-Name} {MySQL-User-Password} {MySQL-Database-Name}"
	echo "Drops all tables from a MySQL"
	exit 1
fi

TABLES=$($MYSQL -u $MUSER -p$MPASS $MDB -e 'show tables' | $AWK '{ print $1}' | $GREP -v '^Tables' )

for t in $TABLES
do
	echo "Deleting $t table from $MDB database..."
	$MYSQL -u $MUSER -p$MPASS $MDB -e "drop table $t"
done

Usage: ./drop.table.sh app_user <PASSWORD> AppDb

Drop all user databases

This keeps the mysql and information_schema schemas and drops everything else. Use with great care.

mysql -uroot -p -e "show databases" | grep -v Database | grep -v mysql | grep -v information_schema \
  | gawk '{print "drop database `" $1 "`;select sleep(0.1);"}' | mysql -uroot -p

Back up and restore users and privileges

A database restore does not bring back the accounts. Inspect the existing grants first:

SELECT DATABASE();
SELECT user();
SELECT User, Host, Password FROM mysql.user;
SHOW GRANTS FOR 'app_user'@'localhost';

Typical grants to recreate an application account:

GRANT USAGE ON *.* TO 'app_user'@'%' IDENTIFIED BY '<PASSWORD>';
GRANT ALL PRIVILEGES ON `AppDb\_Prod`.* TO 'app_user'@'%';

Dump the mysql.user table to keep accounts and privileges:

mysqldump -h 127.0.0.1 -u root -p mysql user > user_table_dump.sql

For a migration, see the Percona-style approach of exporting users with SHOW GRANTS (How to restore MySQL users and passwords during migration).

Connect to a managed MySQL server (Azure Database for MySQL)

Managed servers require TLS. Test connectivity and latency before a big dump or restore:

time mysql -h mysql01.mysql.database.azure.com -u app_admin -p --ssl-mode=REQUIRED -e "SELECT SLEEP(0.1);"
time mysql -h mysql01.mysql.database.azure.com -u app_admin -p --ssl-mode=REQUIRED -N -s -e "SELECT 1;"

If the connection fails, check DNS resolution and network reachability of the port:

getent hosts mysql01.mysql.database.azure.com
time nc -vz mysql01.mysql.database.azure.com 3306

Dump a database from the managed server, compressed:

mysqldump -h mysql01.mysql.database.azure.com -u app_admin -p AppDb --compress | gzip > AppDb.sql.gz

Restore it to another managed server, using an environment variable for the password and a larger packet size:

export MYSQL_PWD='<PASSWORD>'
mysql --no-defaults -h mysql02.mysql.database.azure.com -u app_admin --ssl-mode=REQUIRED -e "SELECT CURRENT_USER();"
gunzip -c 20260820_102316_AppDb_Prod.sql.gz | mysql --no-defaults -h mysql02.mysql.database.azure.com -u app_admin --ssl-mode=REQUIRED --max-allowed-packet=512M AppDb_Prod

Then check that the application account can log in:

export MYSQL_PWD='<PASSWORD>'
mysql --no-defaults -h mysql02.mysql.database.azure.com -u app_user --ssl-mode=REQUIRED -e "SELECT CURRENT_USER();"

Notes

Mastodon LLIMastodon SocialFosstodon