Ubuntu Linux Backup MySQL Server Shell Script

See all Ubuntu Linux related FAQ
I am a new Linux user with a dedicated VPS server running Ubuntu Linux. Iโ€™m using CMS software, and MySQL act as a database server. How can I backup all MySQL server databases to ftp server IP address 10.1.5?

You can use mysqldump command command to the backup database on Ubuntu Linux. The mysqldump client is a backup program. It can be used to dump a database or a collection of databases for backup or for transferring the data to another SQL server. The dump contains SQL statements to create the table, populate it, or both. Once the database is dumped, you need to upload the same to ftp server. Use the lftp client to upload all files. Let us see how to backup your MySQL server with shell script on Ubuntu Linux.
Tutorial details
Difficulty level Easy
Root privileges Yes
Requirements Linux terminal
Category Backup Management
OS compatibility Debian โ€ข Linux โ€ข Ubuntu
Est. reading time 3 minutes
Advertisement

Installing lftp on Ubuntu Linux to backup MySQL server databases

The lftp is a file transfer program that allows sophisticated ftp, http and other connections to other hosts. If a site is specified, then lftp will connect to that site. Otherwise, a connection has to be established with the open command. First, install pending security updates on your Ubuntu Linux server:
$ sudo apt-get update
$ sudo apt-get upgrade

Reboot the Linux machine if a new Linux kernel was installed using the shutdown/reboot command:
$ sudo reboot
Next, install lftp, enter the following apt command or apt-get command:
$ sudo apt-get install lftp

Ubuntu Linux Backup MySQL Server Shell Script - Installing lftp using Apt

Click to enlarge

Shell script to backup MySQL database server under Ubuntu Linux

The following is the shell script. It will dump all database to /backup/mysql directory and later it will upload to given FTP server. You need to setup correct username and password before using the script. Let us call this script mysql-backup-to-ftp.sh:

#!/bin/bash
# Name: mysql-backup-to-ftp.sh
# Usage: Ubuntu Linux Backup MySQL Server Shell Script
# Author: Vivek Gite {https:/www.cyberciti.biz} under GPL v2.x+
# -------------------------------------------------------------------
 
### MySQL Server Login Info ###
MUSER="root"
MPASS="MYSQL-ROOT-PASSWORD"
MHOST="localhost"
 
## Automatically detect path to mysql and mysqldump/lftp/gzip ##
MYSQL="$(command -v mysql)"
MYSQLDUMP="$(command -v mysqldump)"
GZIP="$(command -v gzip)"
LFTP="$(command -v lftp)"
 
## Tmp backup directory ## 
BAK="/backup/mysql"
 
### FTP SERVER Login info ###
FTPU="FTP-SERVER-USER-NAME"
FTPP="FTP-SERVER-PASSWORD"
FTPS="FTP-SERVER-IP-ADDRESS"
NOW=$(date +"%d-%m-%Y")
 
### See comments below ###
### [ ! -d $BAK ] && mkdir -p $BAK || /bin/rm -f $BAK/* ###
[ ! -d "$BAK" ] && mkdir -p "$BAK"
 
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
 FILE=$BAK/$db.$NOW-$(date +"%T").gz
 $MYSQLDUMP -u "$MUSER" -h "$MHOST" -p"$MPASS" "$db" | $GZIP -9 > "$FILE"
done
 
$LFTP -u "$FTPU,$FTPP" -e "mkdir /mysql/$NOW;cd /mysql/$NOW; mput /backup/mysql/*; quit" $FTPS

Save script as /home/your-name/mysql.backup.sh file. Setup executable permission using the chmod command:
$ chmod +x /home/your-name/mysql-backup-to-ftp.sh
To backup MySQL, enter:
$ /home/your-name/mysql-backup-to-ftp.sh
OR
$ sudo /home/your-name/mysql-backup-to-ftp.sh

Running MySQL backup script as cron job on Ubuntu Linux

To automate procedure setup a cron job. For example run backup everyday at midnight (i.e once a day), enter:
$ sudo crontab -e
Append following cron job:
@midnight /home/your-name/mysql-backup-to-ftp.sh >/dev/null 2>&1
Save and close the file. Please note that above script should work with other Linux distros or UNIX like oses too provided that you have bash shell and mysqldump installed.

Summing up

And there you have it. It is a simple but easy-to-use MySQL backup ftp script for Ubuntu Linux. I hope this helps new Linux sysadmins and developers. For more info see the following resources using man command or help command:
$ man mysqldump
$ man lftp
$ man gzip

See also

Please see our guides about commands used in this tutorial for more info too:

๐Ÿฅบ Was this helpful? Please add a comment to show your appreciation or feedback.

nixCrat Tux Pixel Penguin
Hi! ๐Ÿค 
I'm Vivek Gite, and I write about Linux, macOS, Unix, IT, programming, infosec, and open source. Subscribe to my RSS feed or email newsletter for updates.

24 comments… add one
  • ch Nov 18, 2011 @ 8:19

    Gr3at script! Thank you very much!

    I only have one question, when i execute the script i got this error message:

    mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES
    

    How can i avoid this? The other db’s were dumped correctly. Is there a additional command i have to add?

    thank you in advance!

  • Youri Nov 22, 2011 @ 10:45

    Even though this post is from 2007, it was very useful!
    I’ve created my own simple version of this script which you can pass arguments to and skips the “information_schema” database. For anyone that wants to use it: https://gist.github.com/1385371

    usage:

    backup_mysql_databases.sh -u backup -p password -o /home/user/mysql_backups

  • JJ Dec 1, 2011 @ 21:14

    Great artilce – Thanks
    BUT how do I restore my db’s? I ran the script on my old machine and now I need to restore the backed-up DB’s on my new machine.
    How do I do this?

    Thanks!

  • Avin Tokade Dec 9, 2011 @ 7:55

    Nice and Very well explained..
    I have one issue
    I have admin user in Ubuntu box. But any script for this user skip by cron.

    How to run script as unprivileged user ?

  • Darek Feb 3, 2012 @ 19:41

    “Most stupid idea ever. Just accidentally wiped my ~/bin directory. Ah, wellโ€ฆ”

    SHIT! Same thing on my side. I had to reformat and reinstall my server. NOT COOL!

  • Eitan Feb 9, 2012 @ 11:35

    using lftp the files are cut at the “:” in the file name (comes from the %T). How can I avoid this?

  • dennis Jun 13, 2012 @ 13:28

    In your example, what do you mean by:
    MYSQL=”$(which mysql)”
    MYSQLDUMP=”$(which mysqldump)”

    • Ronny D'Hoore Mar 8, 2023 @ 11:48

      For anyone wondering about the same (I did too), the answer is that this can be left as it is, no need to replace this with anything else.

  • Newbie Apr 8, 2013 @ 12:55

    How do I exclude certain Databases with this script?

  • shanavas Jul 23, 2013 @ 13:01

    Is there a way to take incremental back up using this method.

  • Alecz Oct 30, 2013 @ 15:19

    sweeeeeet!!!!

Leave a Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre> for code samples. Your comment will appear only after approval by the site admin.