MySQL Database Import/Export from Shell

Managing MySQL databases from the command line is an essential skill for server administrators. This guide covers importing and exporting MySQL databases using shell commands.

Exporting a Database

# Export a single database
mysqldump -u username -p database_name > backup.sql
# Export all databases
mysqldump -u root -p --all-databases > all_databases.sql
# Compress the export
mysqldump -u username -p database_name | gzip > backup.sql.gz

Importing a Database

# Import a database
mysql -u username -p database_name < backup.sql
# Import compressed backup
gunzip < backup.sql.gz | mysql -u username -p database_name

Creating a New Database

mysql -u root -p -e "CREATE DATABASE newdb;"
mysql -u root -p -e "GRANT ALL ON newdb.* TO 'user'@'localhost' IDENTIFIED BY 'password';"
mysql -u root -p -e "FLUSH PRIVILEGES;"