MySQL Backup, Restore, Import, and Export Cheatsheet

mysqldump -u root -p --databases app_db > app_db.sql

# Export schema and data from one database.

mysqldump -u root -p app_db users > users.sql

# Export a single table only.

mysqldump -u root -p --no-data app_db > app_db-schema.sql

# Export DDL without row data.

mysqldump -u root -p --no-create-info app_db > app_db-data.sql

# Export rows without CREATE statements.

mysql -u root -p app_db < app_db.sql

# Load a dump file into MySQL.

SELECT id, email
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '
'
FROM users;

# Write rows to a server-side file.

LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users_staging
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 LINES
(email, full_name);

# Import delimited files efficiently.

mysqlimport -u root -p --local --fields-terminated-by=, app_db users.csv

# Use the mysqlimport command-line utility.

mysqlsh root@localhost -- util dump-instance /backups/mysql-instance

# Use MySQL Shell utilities for scalable dumps.

mysqlsh root@localhost -- util load-dump /backups/mysql-instance

# Restore a dump created by MySQL Shell utilities.