How to Backup a MySQL Database on Your Server and Download It to Your Local Computer

Backing up your database is essential before making changes or moving data. This guide shows how to dump a MySQL database on a server and download it to your local machine.

Step 1: Dump Your Database on the Server

We will use mysqldump to create a backup of your MySQL database.

Command:

mysqldump --no-tablespaces -h 127.0.0.1 -P 3306 -u username -p'password123' database_name> /var/www/hotel_management_system/avalong_db_backup.sql

Explanation:

• mysqldump → tool to export MySQL database
• --no-tablespaces → skips tablespace info (avoids permission errors)
• -h 127.0.0.1 → MySQL host (localhost)
• -P 3306 → MySQL port
• -u user_name→ MySQL username
• -p'...' → MySQL password (wrap in single quotes to handle special characters)
• databse_name → database name
• > → writes the output to a file
• /var/www/hotel_management_system/avalong_db_backup.sql → file path where backup is saved

Note: This will create a file named avalong_db_backup.sql inside your project folder on the server.

Step 2: Verify the Backup

Check if the backup file was created successfully:

scp [email protected]:/var/www/hotel_management_system/avalong_db_backup.sql ~/Downloads/

• root → your server SSH username
• 111.111.11.111 → your server IP address
• /var/www/hotel_management_system/avalong_db_backup.sql → path to the SQL file on the server
• ~/Downloads/ → destination folder on your local machine

It will prompt you for the server password. Enter your root password (123456789) to start the download.