This tutorial guides you through backing up MySQL and PostgreSQL databases from the command line. While their native tools—mysqldump and pg_dump—share a similar purpose, their command syntax differs. We will cover the essential commands for both systems, highlighting their key similarities and differences.
Backup MySQL / MariaDB Database
Export every table’s schema and existing records to an SQL executable script.
mysqldump -u username -p database_name > database.sql
This will export the specified table’s schema and records to an executable script.
mysqldump -u username -p database_name table_name > db_table.sql
This will export the specified table’s data only(no schema).
mysqldump -u username -p --no-create-info database_name table_name > table_data.sql
This will export the specified table’s schema only(no data).
mysqldump -u username -p --no-data database_name table_name > table_schema.sql
Restore MySQL / MariaDB Database
mysql -u username -p database_name < database.sql
Backup PostgreSQL Database
Export every table’s schema and existing records to an SQL executable script.
pg_dump -U username database_name > database.sql
This will export both the specified table’s schema and data to an executable script.
pg_dump -U username -t table_name database_name > table.sql
Only data (no schema):
pg_dump -U username -a -t table_name database_name > table_data.sql
Only schema (no data):
pg_dump -U username -s -t table_name database_name > table_schema.sql
Restore PostgreSQL Database
psql -U username -d database_name -f database.sql