{"id":251,"date":"2023-05-07T13:20:49","date_gmt":"2023-05-07T13:20:49","guid":{"rendered":"https:\/\/linux.atheesh.com\/?p=251"},"modified":"2023-05-07T13:21:28","modified_gmt":"2023-05-07T13:21:28","slug":"effortlessly-take-daily-mysql-backups-and-manage-old-files-with-a-shell-script","status":"publish","type":"post","link":"https:\/\/linux.atheesh.com\/index.php\/2023\/05\/07\/effortlessly-take-daily-mysql-backups-and-manage-old-files-with-a-shell-script\/","title":{"rendered":"Effortlessly Take Daily MySQL Backups and Manage Old Files with a Shell Script"},"content":{"rendered":"\n

Taking daily backups of your MySQL database is an essential task for ensuring data security and preventing data loss. In this blog, we’ll walk you through how to set up a shell script to take daily MySQL backups and delete older backup files more than one week old.<\/p>\n\n\n\n

Step 1: Creating the Backup Script<\/strong><\/p>\n\n\n\n

To create the backup script, you need to open a text editor and enter the following code<\/p>\n\n\n\n

<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
#!\/bin\/bash<\/span><\/span>\n<\/span>\n# <\/span>Set<\/span> <\/span>the<\/span> <\/span>date<\/span> <\/span>format<\/span> <\/span>for<\/span> <\/span>the<\/span> <\/span>backup<\/span> <\/span>filename<\/span><\/span>\nDATE<\/span>=<\/span>$<\/span>(<\/span>date<\/span> <\/span>+%<\/span>Y<\/span>-%<\/span>m<\/span>-%<\/span>d<\/span>)<\/span><\/span>\n<\/span>\n# <\/span>Set<\/span> <\/span>the<\/span> <\/span>MySQL<\/span> <\/span>credentials<\/span><\/span>\nMYSQL_USER<\/span>=<\/span>"<\/span>your_mysql_username<\/span>"<\/span><\/span>\nMYSQL_PASSWORD<\/span>=<\/span>"<\/span>your_mysql_password<\/span>"<\/span><\/span>\n<\/span>\n# <\/span>Set<\/span> <\/span>the<\/span> <\/span>directory<\/span> <\/span>for<\/span> <\/span>storing<\/span> <\/span>the<\/span> <\/span>backups<\/span><\/span>\nBACKUP_DIR<\/span>=<\/span>"<\/span>\/path\/to\/backup\/folder<\/span>"<\/span><\/span>\n<\/span>\n# <\/span>Create<\/span> <\/span>the<\/span> <\/span>backup<\/span> <\/span>file<\/span><\/span>\nBACKUP_FILE<\/span>=<\/span>"<\/span>$BACKUP_DIR\/$DATE.sql<\/span>"<\/span><\/span>\nmysqldump<\/span> <\/span>--<\/span>user<\/span>=<\/span>$MYSQL_USER<\/span> <\/span>--<\/span>password<\/span>=<\/span>$MYSQL_PASSWORD<\/span> <\/span>--<\/span>all<\/span>-<\/span>databases<\/span> <\/span>><\/span> <\/span>$BACKUP_FILE<\/span><\/span>\n<\/span>\n# <\/span>Delete<\/span> <\/span>backup<\/span> <\/span>files<\/span> <\/span>older<\/span> <\/span>than<\/span> <\/span>7<\/span> <\/span>days<\/span><\/span>\nfind<\/span> <\/span>$BACKUP_DIR<\/span>\/* -mtime +7 -exec rm {} \\;<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
In the code above, we first set the date format for the backup filename using the date<\/code> command. Next, we set the MySQL credentials to be used by the script. You should replace “your_mysql_username” and “your_mysql_password” with the actual credentials for your MySQL server.<\/p>\n\n\n\n
We then set the directory for storing the backups using the BACKUP_DIR<\/code> variable. You should replace “\/path\/to\/backup\/folder” with the actual path to the folder where you want to store the backups.<\/p>\n\n\n\n
We create the backup file using the mysqldump<\/code> command. This command dumps all the databases to a file, which is named after the current date.<\/p>\n\n\n\n
Finally, we delete backup files older than 7 days using the find<\/code> command. This command searches for files in the backup directory that are older than 7 days and deletes them.<\/p>\n\n\n\n
Step 2: Making the Script Executable<\/p>\n\n\n\n
Once you have created the backup script, you need to make it executable using the chmod<\/code> command. Open the terminal and navigate to the directory where you saved the backup script. Then, enter the following command:<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
chmod<\/span> <\/span>+<\/span>x<\/span> <\/span>backup<\/span>.<\/span>sh<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
This command makes the script executable.<\/p>\n\n\n\n
Step 3: Running the Backup Script<\/strong><\/p>\n\n\n\n
To run the backup script, open the terminal and navigate to the directory where you saved the backup script. Then, enter the following command:<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
.<\/span>\/<\/span>backup<\/span>.<\/span>sh<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
This command executes the backup script, and a new backup file is created in the backup directory.<\/p>\n\n\n\n
Step 4: Automating the Backup Process<\/strong><\/p>\n\n\n\n
To automate the backup process, you can use a cron job to run the backup script at a specific time each day. To set up a cron job, open the terminal and enter the following command:<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
crontab<\/span> <\/span>-<\/span>e<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
This command opens the cron table in the editor. Add the following line to the end of the file:<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
0<\/span> <\/span>0<\/span> <\/span>*<\/span> <\/span>*<\/span> <\/span>*<\/span> <\/span>\/<\/span>path<\/span>\/<\/span>to<\/span>\/<\/span>backup<\/span>.<\/span>sh<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
This line sets the backup script to run at midnight each day. Replace “\/path\/to\/backup.sh” with the actual path to the backup script.<\/p>\n\n\n\n
Step 5: Verifying the Backup<\/strong><\/p>\n\n\n\n
To verify that the backup is working correctly, you can check the backup directory for the presence of the backup file. You should see a new backup file for each day the script runs.<\/p>\n\n\n
Backup script for Cloud backup storage setups<\/h3>\n\n\n
To update the backup script for remote backup storage setups, you can modify the backup directory to point to a remote storage location instead of a local folder. Here’s how you can modify the script:<\/p>\n\n\n
\n
\"\"
AWS Backup<\/figcaption><\/figure><\/div>\n\n\n
Step 1: Set up Remote Storage<\/strong><\/p>\n\n\n\n
To set up remote storage, you can use services like Amazon S3, Google Cloud Storage, or any other cloud storage service that provides an API to upload files. You will need to create an account and set up the necessary credentials to access the remote storage.<\/p>\n\n\n\n
Step 2: Install and Configure the AWS CLI<\/strong><\/p>\n\n\n\n
If you are using Amazon S3 for remote storage, you will need to install and configure the AWS CLI on the server where the backup script runs. You can follow the official AWS documentation to install and configure the CLI.<\/p>\n\n\n\n
Step 3: Modify the Backup Script<\/strong><\/p>\n\n\n\n
To modify the backup script, you need to change the backup directory to the remote storage location. Here’s an example of how you can modify the script to upload the backup file to an S3 bucket:<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
#!\/bin\/bash<\/span><\/span>\n<\/span>\n# <\/span>Set<\/span> <\/span>the<\/span> <\/span>date<\/span> <\/span>format<\/span> <\/span>for<\/span> <\/span>the<\/span> <\/span>backup<\/span> <\/span>filename<\/span><\/span>\nDATE<\/span>=<\/span>$<\/span>(<\/span>date<\/span> <\/span>+%<\/span>Y<\/span>-%<\/span>m<\/span>-%<\/span>d<\/span>)<\/span><\/span>\n<\/span>\n# <\/span>Set<\/span> <\/span>the<\/span> <\/span>MySQL<\/span> <\/span>credentials<\/span><\/span>\nMYSQL_USER<\/span>=<\/span>"<\/span>your_mysql_username<\/span>"<\/span><\/span>\nMYSQL_PASSWORD<\/span>=<\/span>"<\/span>your_mysql_password<\/span>"<\/span><\/span>\n<\/span>\n# <\/span>Set<\/span> <\/span>the<\/span> <\/span>directory<\/span> <\/span>for<\/span> <\/span>storing<\/span> <\/span>the<\/span> <\/span>backups<\/span><\/span>\nBACKUP_DIR<\/span>=<\/span>"<\/span>\/path\/to\/local\/folder<\/span>"<\/span><\/span>\n<\/span>\n# <\/span>Create<\/span> <\/span>the<\/span> <\/span>backup<\/span> <\/span>file<\/span><\/span>\nBACKUP_FILE<\/span>=<\/span>"<\/span>$BACKUP_DIR\/$DATE.sql<\/span>"<\/span><\/span>\nmysqldump<\/span> <\/span>--<\/span>user<\/span>=<\/span>$MYSQL_USER<\/span> <\/span>--<\/span>password<\/span>=<\/span>$MYSQL_PASSWORD<\/span> <\/span>--<\/span>all<\/span>-<\/span>databases<\/span> <\/span>><\/span> <\/span>$BACKUP_FILE<\/span><\/span>\n<\/span>\n# <\/span>Upload<\/span> <\/span>backup<\/span> <\/span>file<\/span> <\/span>to<\/span> <\/span>S3<\/span><\/span>\naws<\/span> <\/span>s3<\/span> <\/span>cp<\/span> <\/span>$BACKUP_FILE<\/span> s3<\/span>:<\/span>\/\/your-bucket-name\/$DATE.sql<\/span><\/span>\n<\/span>\n# <\/span>Delete<\/span> <\/span>backup<\/span> <\/span>files<\/span> <\/span>older<\/span> <\/span>than<\/span> <\/span>7<\/span> <\/span>days<\/span><\/span>\nfind<\/span> <\/span>$BACKUP_DIR<\/span>\/* -mtime +7 -exec rm {} \\;<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
In the code above, we added the AWS CLI command aws s3 cp<\/code> to upload the backup file to the S3 bucket. Replace “your-bucket-name” with the actual name of the S3 bucket where you want to store the backup file.<\/p>\n\n\n\n
Step 4: Test the Backup Script<\/strong><\/p>\n\n\n\n
To test the backup script, you can run the script manually and verify that the backup file is uploaded to the remote storage location.<\/p>\n\n\n\n
Step 5: Automate the Backup Process<\/strong><\/p>\n\n\n\n
To automate the backup process, you can set up a cron job to run the backup script at a specific time each day, as described in the previous section.<\/p>\n\n\n
Remote server instead of a local folder or remote storage<\/h3>\n\n\n
To update the backup script for a remote backup server, you need to modify the script to copy the backup file to the remote server instead of a local folder or remote storage. Here’s how you can modify the script:<\/p>\n\n\n
\n
\"\"
Remote Backup<\/figcaption><\/figure><\/div>\n\n\n
Step 1: Set up Remote Backup Server<\/strong><\/p>\n\n\n\n
To set up a remote backup server, you need to have access to a remote server with SSH enabled. You will also need to create a backup directory on the remote server and set up the necessary credentials to access the server.<\/p>\n\n\n\n
Step 2: Modify the Backup Script<\/strong><\/p>\n\n\n\n
To modify the backup script, you need to add an SCP command to copy the backup file to the remote server. Here’s an example of how you can modify the script to copy the backup file to a remote server:<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
#!\/bin\/bash<\/span><\/span>\n<\/span>\n# <\/span>Set<\/span> <\/span>the<\/span> <\/span>date<\/span> <\/span>format<\/span> <\/span>for<\/span> <\/span>the<\/span> <\/span>backup<\/span> <\/span>filename<\/span><\/span>\nDATE<\/span>=<\/span>$<\/span>(<\/span>date<\/span> <\/span>+%<\/span>Y<\/span>-%<\/span>m<\/span>-%<\/span>d<\/span>)<\/span><\/span>\n<\/span>\n# <\/span>Set<\/span> <\/span>the<\/span> <\/span>MySQL<\/span> <\/span>credentials<\/span><\/span>\nMYSQL_USER<\/span>=<\/span>"<\/span>your_mysql_username<\/span>"<\/span><\/span>\nMYSQL_PASSWORD<\/span>=<\/span>"<\/span>your_mysql_password<\/span>"<\/span><\/span>\n<\/span>\n# <\/span>Set<\/span> <\/span>the<\/span> <\/span>directory<\/span> <\/span>for<\/span> <\/span>storing<\/span> <\/span>the<\/span> <\/span>backups<\/span><\/span>\nBACKUP_DIR<\/span>=<\/span>"<\/span>\/path\/to\/local\/folder<\/span>"<\/span><\/span>\n<\/span>\n# <\/span>Create<\/span> <\/span>the<\/span> <\/span>backup<\/span> <\/span>file<\/span><\/span>\nBACKUP_FILE<\/span>=<\/span>"<\/span>$BACKUP_DIR\/$DATE.sql<\/span>"<\/span><\/span>\nmysqldump<\/span> <\/span>--<\/span>user<\/span>=<\/span>$MYSQL_USER<\/span> <\/span>--<\/span>password<\/span>=<\/span>$MYSQL_PASSWORD<\/span> <\/span>--<\/span>all<\/span>-<\/span>databases<\/span> <\/span>><\/span> <\/span>$BACKUP_FILE<\/span><\/span>\n<\/span>\n# <\/span>Copy<\/span> <\/span>backup<\/span> <\/span>file<\/span> <\/span>to<\/span> <\/span>remote<\/span> <\/span>server<\/span><\/span>\nREMOTE_SERVER<\/span>=<\/span>"<\/span>your_remote_server_address<\/span>"<\/span><\/span>\nREMOTE_DIR<\/span>=<\/span>"<\/span>\/path\/to\/remote\/folder<\/span>"<\/span><\/span>\nscp<\/span> <\/span>$BACKUP_FILE<\/span> $REMOTE_SERVER<\/span>:<\/span>$REMOTE_DIR<\/span><\/span>\n<\/span>\n# <\/span>Delete<\/span> <\/span>backup<\/span> <\/span>files<\/span> <\/span>older<\/span> <\/span>than<\/span> <\/span>7<\/span> <\/span>days<\/span><\/span>\nfind<\/span> <\/span>$BACKUP_DIR<\/span>\/* -mtime +7 -exec rm {} \\;<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
In the code above, we added the SCP command scp<\/code> to copy the backup file to the remote server. Replace “your_remote_server_address” with the actual IP address or domain name of the remote server where you want to store the backup file. Replace “\/path\/to\/remote\/folder” with the actual directory on the remote server where you want to store the backup file.<\/p>\n\n\n\n
Step 3: Test the Backup Script<\/strong><\/p>\n\n\n\n
To test the backup script, you can run the script manually and verify that the backup file is copied to the remote server.<\/p>\n\n\n\n
Step 4: Automate the Backup Process<\/strong><\/p>\n\n\n\n
To automate the backup process, you can set up a cron job to run the backup script at a specific time each day, as described in the previous section.<\/p>\n\n\n\n
Conclusion<\/strong><\/p>\n\n\n\n
By modifying the backup script to copy the backup file to a remote server, you can ensure that your data is protected in case of local hardware failure or disaster. With the SCP command and remote server access, it’s easy to set up a secure and reliable backup process for your MySQL database.<\/p>\n","protected":false},"excerpt":{"rendered":"
Taking daily backups of your MySQL database is an essential task for ensuring data security and preventing data loss. In this blog, we’ll walk you through how to set up a shell script to take daily MySQL backups and delete older backup files more than one week old. Step 1: Creating the Backup Script To […]<\/p>\n","protected":false},"author":1,"featured_media":257,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[9],"tags":[],"_links":{"self":[{"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/posts\/251"}],"collection":[{"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/comments?post=251"}],"version-history":[{"count":1,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/posts\/251\/revisions"}],"predecessor-version":[{"id":256,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/posts\/251\/revisions\/256"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/media\/257"}],"wp:attachment":[{"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/media?parent=251"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/categories?post=251"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/linux.atheesh.com\/index.php\/wp-json\/wp\/v2\/tags?post=251"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}