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
- Do not leave passwords in shell history;
-pwithout a value prompts for the password, andMYSQL_PWDis better than-pPASSWORD. --single-transactiongives a consistent dump of InnoDB tables without locking them.- Dropping tables or databases is irreversible: take a fresh backup of the target first.
- Reference: Restoring a database backup from the command line, MySQL drop all tables ignoring foreign keys.