Ubuntu Linux Backup MySQL Server Shell Script

Q. I’m new to Linux and I’ve dedicated VPS server running Ubuntu Linux. I’m using CMS software and MySQL act as database server. Can you explain how can I backup all mysql server databases to ftp server IP address called

A. You can use mysqldump command to backup database. 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 or populate it, or both.

Once database is dumped, you need to upload the same to ftp server. Use lftp client to upload all files.

Install lftp

lftp is a file transfer program that allows sophisticated ftp, http and other connections to other hosts. If site is specified then lftp will connect to that site otherwise a connection has to be established with the open command. To install lftp, enter:

sudo apt-get install lftp

Shell script to backup MySQL database server

Following is the shell script. It will dump all database to /backup/mysql and later it will upload to FTP server. You need to setup correct username and password before using the script:

### MySQL Server Login Info ###
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
### FTP SERVER Login info ###
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
 FILE=$BAK/$db.$NOW-$(date +"%T").gz
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:
$ chmod +x /home/your-name/mysql.backup.sh
To backup MySQL, enter:
sudo /home/your-name/mysql.backup.sh

Run MySQL backup script as cron job

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/you/mysql.backup.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.

🐧 Get the latest tutorials on Linux, Open Source & DevOps via RSS feed or Weekly email newsletter.

🐧 23 comments so far... add one

CategoryList of Unix and Linux commands
Disk space analyzersdf duf ncdu pydf
File Managementcat cp mkdir tree
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Modern utilitiesbat exa
Network UtilitiesNetHogs dig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg glances gtop jobs killall kill pidof pstree pwdx time vtop
Searchingag grep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
23 comments… add one
  • davidevans Feb 7, 2008 @ 18:42

    I’m trying to invoke a lftp command within a shell script from a sqr. The idea is to lftp a file from unix to the mainframe. Unfortunately, I’ve receive the error message, “Unexpected Error”. The sqr code is as follows:
    let $script = ‘/server/peoplesoft/tmp/lftp.ssh’
    call system $script #status
    *******Unix Script*****
    put testfile.txt testtmp.txt
    #ption: This shell script will lftp a file from unix to the mainframe

  • The Traveller May 6, 2008 @ 18:02

    Thanks for this code! Its really help me!!!


  • ravi Aug 27, 2008 @ 6:53

    hi friends i m new to shell scripting. i want some help if u can. i want to take binary backup (not the simple dump)of mysql database and store it in a folder named of current date and this script should run at early hours of each day.

  • TimeWaster Jul 19, 2009 @ 18:55

    THANX! works like a charm!

  • Naveen Jul 31, 2009 @ 9:41

    I want to take backup for MYSQL server using java code. I use a backup file name – backup20060731.bak. Now I want to execute this file so that I coule place it into a .zip file and sore it to to a repository using FTP.
    I want this type of command for executing the backup(“.bak”) file

    Runtime rt = Runtime.getname();
    Runtime rt = Runtime.getname();
    rt.exec(“exp User/Password file = ” + backupFileName + “log = ” + backupFileName + “.log FULL = y”);

    The above command is for ORACLE database. Can any body give me the command for MYSQL database or SQL server 2000 database or POSTGRES database.
    Please help me, I need it urgently.

  • ZYV Aug 28, 2009 @ 20:04

    [ ! -d $BAK ] && mkdir -p $BAK || /bin/rm -f $BAK/*

    Most stupid idea ever. Just accidentally wiped my ~/bin directory. Ah, well…

  • skyper Sep 21, 2009 @ 20:33

    But how to create with proftpd?

  • azteam Jul 15, 2010 @ 8:47

    When I run shell scripts, it return error:
    mput: Access failed: 550 can’t access file.

    How i fix this problem?

  • StudioCart Mar 25, 2011 @ 19:02

    OK… this may help someone…here is my situation… I used this script to transfer backups from a virtualbox ubuntu Mysql instance to the host win 2003 server.

    The host is running FileZilla, Ubuntu was using lftp as above.

    When I ran the script I would get a 550 can’t access file error. So I proceeded to troubleshoot for about 10 hours… the thing that got me was that I could create a test.txt file and have the script pick it and send it, but the .gz files created by the script would not transfer even by using lftp at the command line. The error led me to search for a permissions issue. Then I tried other ubuntu clients and other ftp servers on the windows side.

    Low and behold I stumbled upon the solution by renaming the .gz file with a shorter name than the file creates.

    So it turns out that the name length is the issue…

    So I hope this saves someone some head banging..

    • simbonk Jan 18, 2012 @ 15:29

      thank you thankyou! You just saved me 8 hours ;)

    • Thanks Nov 5, 2014 @ 20:37

      Thanks, file name was too long giving 550 error

    • SmartUser Jan 25, 2016 @ 18:54

      Stop using Linux guides for Windows servers and you won’t have this problem.

  • marek Jul 26, 2011 @ 12:12

    [ ! -d $BAK ] && mkdir -p $BAK || /bin/rm -f $BAK/*

    Most stupid idea ever. Just accidentally wiped my ~/bin directory. Ah, well…


    same for me

    VIVEK GITE can modify this part of script …

  • 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


    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?


  • 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)”

  • 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


Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre> for code samples. Still have questions? Post it on our forum