I run several home servers, one of which is dedicated to my Android app development. This particular server hosts the entire backend infrastructure for my budget tracking application, with MySQL serving as the database system that stores all operational data.
In this setup, I occasionally need to back up the database—especially after making structural changes to a table. Below is a guide on how to back up your MySQL database and its tables.
1. Dump Entire Database (Schema + Data)
mysqldump -u [username] -p [database_name] > backup.sql- Replace
[username]with your MySQL user. - Replace
[database_name]with the name of the database you want to copy. - It will prompt for the password.
This file (backup.sql) will contain:
- All
CREATE DATABASE,CREATE TABLE, andINSERTstatements for the DB. - Basically, everything needed to recreate the DB elsewhere.
2. Dump Specific Tables
mysqldump -u [username] -p [database_name] table1 table2 > backup_tables.sqlYou can list as many table names as you want after the database name.
3. Dump Schema Only (No Data)
mysqldump -u [username] -p --no-data [database_name] > schema_only.sql4. Dump Data Only (No Schema)
mysqldump -u [username] -p --no-create-info [database_name] > data_only.sql5. Restore to Another Database
If you want to “copy” the DB into a new one:
mysql -u [username] -p -e "CREATE DATABASE new_db_name;"
mysql -u [username] -p new_db_name < backup.sqlFYI…
By default, the backup.sql file will be saved to whatever the current working directory is when you run the command — usually shown by pwd.
You can specify a full path like this:
mysqldump -u [username] -p [database_name] > /path/to/your/backup/backup.sqlJust make sure the directory exists and that you have write permissions to it. You can create it if needed:
mkdir -p /home/youruser/db_backups