Linux shell script for database backup

by

After hours and hours work, I created a solution like the below. I copy paste for other people that can benefit.

First create a script file and give this file executable permission.

# cd /etc/cron.daily/
# touch /etc/cron.daily/dbbackup-daily.sh
# chmod 755 /etc/cron.daily/dbbackup-daily.sh
# vi /etc/cron.daily/dbbackup-daily.sh

Then copy following lines into file with Shift+Ins

#!/bin/sh
now="$(date +'%d_%m_%Y_%H_%M_%S')"
filename="db_backup_$now".gz
backupfolder="/var/www/vhosts/example.com/httpdocs/backups"
fullpathbackupfile="$backupfolder/$filename"
logfile="$backupfolder/"backup_log_"$(date +'%Y_%m')".txt
echo "mysqldump started at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
mysqldump --user=mydbuser --password=mypass --default-character-set=utf8 mydatabase | gzip > "$fullpathbackupfile"
echo "mysqldump finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
chown myuser "$fullpathbackupfile"
chown myuser "$logfile"
echo "file permission changed" >> "$logfile"
find "$backupfolder" -name db_backup_* -mtime +8 -exec rm {} \;
echo "old files deleted" >> "$logfile"
echo "operation finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
echo "*****************" >> "$logfile"
exit 0

Edit:
If you use InnoDB and backup takes too much time, you can add “single-transaction” argument to prevent locking. So mysqldump line will be like this:

mysqldump --user=mydbuser --password=mypass --default-character-set=utf8
          --single-transaction mydatabase | gzip > "$fullpathbackupfile"

More Related Contents:

  1. error: ‘Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)’ — Missing /var/run/mysqld/mysqld.sock
  2. Can’t connect to MySQL server error 111 [closed]
  3. How do I find the MySQL my.cnf location
  4. ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
  5. Stored procedure that Automatically delete rows older than 7 days in MYSQL
  6. MySQL – ERROR 1045 – Access denied
  7. Suppress warning messages using mysql from within Terminal, but password written in bash script
  8. How to execute a MySQL command from a shell script?
  9. Cronjob or MySQL event?
  10. Run a mySQL query as a cron job?
  11. How do I know when my docker mysql container is up and mysql is ready for taking queries?
  12. Run a shell command when a file is added
  13. How do I create a crontab through a script
  14. Bash script to insert values in MySQL
  15. How to change MySQL table names in Linux server to be case insensitive?
  16. Commit data in a mysql container
  17. Permission denied with bash.sh to run cron
  18. libmysqlclient15-dev on macs?
  19. MySQL fails on: mysql “ERROR 1524 (HY000): Plugin ‘auth_socket’ is not loaded”
  20. Access mysql remote database from command line
  21. How to change the mysql root password
  22. Accessing a mysql database from external host/ip? (ie: mysql workbench)
  23. Storing image in database directly or as base64 data?
  24. Is MySQL breaking the standard by allowing selecting columns that are not part of the group by clause?
  25. Mysql: Setup the format of DATETIME to ‘DD-MM-YYYY HH:MM:SS’ when creating a table
  26. UNION query with codeigniter’s active record pattern
  27. mysql stored procedure that calls itself recursively
  28. When to use NULL in MySQL tables
  29. Where can I download mysql jdbc jar from? [closed]
  30. Default sort-ordering in MySQL (ALTER TABLE … ORDER BY …;)

Leave a Comment