Backing up your database via SSH can be a more efficient and reliable method than using web-based tools. This guide will walk you through creating a database backup manually through SSH and automating the process with a cron job. Ensure you have SSH access set up on your Windows or Mac computer and have a basic understanding of directories and files.
Creating a One-Off Backup via SSH:
Use an SSH client like PuTTY to log into your SSH account.
Run the following command, replacing mysqluser and mysqldatabase with your MySQL database username and database name configured in cPanel:
mysqldump --opt -u mysqluser -p mysqldatabase > backup.sql- With the -p flag, you will be prompted to enter the password manually each time you run the command.
- The output file (backup.sql in this example) will be created in your present working directory.
- For large databases, the process might take some time; just wait for it to finish.
Automating a MySQL Backup from a Script or Cron Job:
If you want to schedule regular MySQL backups as part of a shell script or Cron job, follow these considerations:
Create a script (e.g., backup_script.sh) with the following command, replacing placeholders accordingly:
/usr/bin/mysqldump --opt -u mysqluser -ppassword mysqldatabase > /home/cpanelusername/backup.sql- /usr/bin/mysqldump: Explicitly specifies the path to the mysqldump command on the server.
- -u mysqluser -ppassword: Replace with your MySQL username and password.
- mysqldatabase: Replace with your MySQL database name.
- /home/cpanelusername/backup.sql: Specifies the output file’s location, typically in your cPanel account’s home directory.
Ensure you give execute permissions to the script using the chmod +x backup_script.sh command.
You can then add this script as a cron job to automate the backup process. To edit your crontab, use the command crontab -e, and add a line like this:
0 2 * * * /path/to/backup_script.shThis example schedules the script to run daily at 2:00 AM. Adjust the timing to your preference.
Save and exit the text editor to activate the cron job. The script will now run automatically at the specified interval.
Remember to handle passwords properly in your script, and use secure practices when storing them, as they will be required for automated backups. This method ensures that your database backups are executed on a schedule without manual intervention.
