{"id":1005,"date":"2010-07-23T11:01:49","date_gmt":"2010-07-23T15:01:49","guid":{"rendered":"http:\/\/linuxhostingsupport.net\/blog\/?p=1005"},"modified":"2013-03-20T08:43:17","modified_gmt":"2013-03-20T12:43:17","slug":"shell-script-to-backup-a-mysql-database-and-save-it-on-a-remote-server-using-ftp","status":"publish","type":"post","link":"https:\/\/linuxhostingsupport.net\/blog\/shell-script-to-backup-a-mysql-database-and-save-it-on-a-remote-server-using-ftp","title":{"rendered":"Shell script to backup a Mysql database and save it on a remote server using Ftp"},"content":{"rendered":"

The following shell script will dump the mysql database and will save the .sql file on a remote location using Ftp. This script will create a backup file including the current date so you can have multiple copies of the backups of the same database under one directory.<\/p>\n

Create a file called mysqlbkup.sh<\/p>\n

\n
# vi \/root\/mysqlbkup.sh<\/span><\/pre>\n<\/blockquote>\n
and paste the following code in the file as it is.<\/p>\n
\n
##############START OF THE SCRIPT##############\r\n\r\n#!\/bin\/bash\r\n\r\n# Specify the temporary backup directory.\r\nBKUPDIR=\"\/tmp\"\r\n\r\n# Specify Database Name\r\ndbname=\"dbname_here\"\r\n\r\n# Save the current date\r\ndate=`date '+%Y-%m-%d'`\r\n\r\n# Specify Ftp details of the remote server.\r\nftpserver=\"FtpServerIP\"\r\nftpuser=\"your ftp user\"\r\nftppass=\"your ftp password\"\r\n\r\n# Dump the mysql database with the current date and compress it.\r\n# Save the mysql password in a file and specify the password file\r\n# path in the below command.\r\n\r\n\/usr\/bin\/mysqldump -uroot -p`cat \/path\/to\/pass\/file` $dbname\\\r\n\u00a0| gzip > $BKUPDIR\/$date.$dbname.sql.gz\r\n\r\n# Change directory to the backup directory.\r\ncd $BKUPDIR\r\n\r\n# Upload the backup\r\nftp -n $ftpserver <<!EOF!\r\nuser $ftpuser $ftppass\r\nbinary\r\nprompt\r\nmput *.sql.gz\r\nquit\r\n!EOF!\r\n\r\n# Remove the local backup file.\r\n\/bin\/rm -f \/$BKUPDIR\/$date.$dbname.sql.gz\r\n\r\n##############END OF THE SCRIPT##############<\/pre>\n<\/blockquote>\n
Make sure to specify your db_name, ftpserver, ftpuser and ftppass values in the script.<\/p>\n
Save the file and schedule a cronjob to execute the file on daily basis, say during night hours at 1.00AM. Edit the cron file<\/p>\n
\n
# crontab -e<\/span><\/pre>\n<\/blockquote>\n
and set the following cronjob<\/p>\n
\n
0 1 * * * \/bin\/sh \/root\/mysqlbkup.sh<\/span><\/pre>\n<\/blockquote>\n
save the file and restart the crond service<\/p>\n
\n
# service crond service<\/span><\/pre>\n<\/blockquote>\n
The script will work on a Linux\/Plesk server as well. You just have to replace the mysqldump line in the script with the following<\/p>\n
\n
\/usr\/bin\/mysqldump -uadmin -p`cat \/etc\/psa\/.psa.shadow` $dbname\\<\/span>\r\n | gzip > $BKUPDIR\/$date.$dbname.sql.gz<\/span><\/pre>\n<\/blockquote>\n
Note:<\/strong><\/span> Leave a comment if you have any suggestions, questions OR have received any error message using this script.<\/p>\n","protected":false},"excerpt":{"rendered":"
The following shell script will dump the mysql database and will save the .sql file on a remote location using Ftp. This script will create a backup file including the current date so you can have multiple copies of the backups of the same database under one directory.
\nCreate a file called mysqlbkup.sh<\/p>\n
# vi \/root\/mysqlbkup.sh<\/p>\n
and paste the following code in the file as it is.<\/p>\n
##############START OF THE SCRIPT##############<\/p>\n
#!\/bin\/bash<\/p>\n
# Specify the temporary backup directory.
\nBKUPDIR=”\/tmp”<\/p>\n
# Specify Database Name
\ndbname=”dbname_here”<\/p>\n
# Save the current date
\ndate=`date ‘+%Y-%m-%d’`<\/p>\n
# Specify Ftp details of the remote server.
\nftpserver=”FtpServerIP”
\nftpuser=”your ftp […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[106],"tags":[985,984,987,983,982,981,980,986],"_links":{"self":[{"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/posts\/1005"}],"collection":[{"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/comments?post=1005"}],"version-history":[{"count":20,"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/posts\/1005\/revisions"}],"predecessor-version":[{"id":2232,"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/posts\/1005\/revisions\/2232"}],"wp:attachment":[{"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/media?parent=1005"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/categories?post=1005"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/linuxhostingsupport.net\/blog\/wp-json\/wp\/v2\/tags?post=1005"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}