How To Backup MySQL Databases on an Ubuntu VPS

Published on August 28, 2013

How To Backup MySQL Databases on an Ubuntu VPS

Status: Deprecated

This article covers a version of Ubuntu that is no longer supported. If you are currently operate a server running Ubuntu 12.04, we highly recommend upgrading or migrating to a supported version of Ubuntu:

Reason: Ubuntu 12.04 reached end of life (EOL) on April 28, 2017 and no longer receives security patches or updates. This guide is no longer maintained.

See Instead:
This guide might still be useful as a reference, but may not work on other Ubuntu releases. If available, we strongly recommend using a guide written for the version of Ubuntu you are using. You can use the search functionality at the top of the page to find a more recent version.

What is MySQL?

MySQL is a popular database management solution that uses the SQL querying language to access and manipulate data. It can easily be used to manage the data from websites or applications.

Backups are important with any kind of data, and this is especially relevant when talking about databases. MySQL can be backed up in a few different ways that we will discuss in this article.

For this tutorial, we will be using an Ubuntu 12.04 VPS with MySQL 5.5 installed. Most modern distributions and recent versions of MySQL should operate in a similar manner.

How to Backup a MySQL Database with mysqldump

One of the most common ways of backing up with MySQL is to use a command called "mysqldump".

Backing Up

There is an article on how to export databases using mysqldump here. The basic syntax of the command is:

mysqldump -u username -p database_to_backup > backup_name.sql

Restoring

To restore a database dump created with mysqldump, you simply have to redirect the file into MySQL again.

We need to create a blank database to house the imported data. First, log into MySQL by typing:

mysql -u username -p

Create a new database which will hold all of the data from the data dump and then exit out of the MySQL prompt:

CREATE DATABASE database_name;
exit

Next, we can redirect the dump file into our newly created database by issuing the following command:

mysql -u username -p database_name < backup_name.sql

Your information should now be restored to the database you've created.

How to Backup a MySQL Table to a Text File

You can save the data from a table directly into a text file by using the select statement within MySQL.

The general syntax for this operation is:

SELECT * INTO OUTFILE 'table_backup_file' FROM name_of_table;

This operation will save the table data to a file on the MySQL server. It will fail if there is already a file with the name chosen.

Note: This option only saves table data. If your table structure is complex and must be preserved, it is best to use another method!

How to Backup MySQL Information using automysqlbackup

There is a utility program called "automysqlbackup" that is available in the Ubuntu repositories.

This utility can be scheduled to automatically perform backups at regular intervals.

To install this program, type the following into the terminal:

sudo apt-get install automysqlbackup

Run the command by typing:

sudo automysqlbackup

The main configuration file for automysqlbackup is located at "/etc/default/automysqlbackup". Open it with administrative privileges:

sudo nano /etc/default/automysqlbackup

You can see that this file, by default, assigns many variables by the MySQL file located at "/etc/mysql/debian.cnf". This contains maintenance login information

From this file, it reads the user, password, and databases that must be backed up.

The default location for backups is "/var/lib/automysqlbackup". Search this directory to see the structure of the backups:

ls /var/lib/automysqlbackup

daily  monthly weekly

If we look into the daily directory, we can see a subdirectory for each database, inside of which is a gzipped sql dump from when the command was run:

ls -R /var/lib/automysqlbackup/daily

.:
database_name  information_schema  performance_schema

./database_name:
database_name_2013-08-27_23h30m.Tuesday.sql.gz

./information_schema:
information_schema_2013-08-27_23h30m.Tuesday.sql.gz

./performance_schema:
performance_schema_2013-08-27_23h30m.Tuesday.sql.gz

Ubuntu installs a cron script with this program that will run it every day. It will organize the files to the appropriate directory.

How to Backup When Using Replication

It is possible to use MySQL replication to backup data with the above techniques.

Replication is a process of mirroring the data from one server to another server (master-slave) or mirroring changes made to either server to the other (master-master).

While replication allows for data mirroring, it suffers when you are trying to save a specific point in time. This is because it is constantly replicating the changes of a dynamic system.

To avoid this problem, we can either:

Disable replication temporarily
Make the backup machine read-only temporarily

Disabling Replication Temporarily

You can disable replication for the slave temporarily by issuing:

mysqladmin -u user_name -p stop-slave

Another option, which doesn't completely stop replication, but puts it on pause, so to speak, can be accomplished by typing:

mysql -u user_name -p -e 'STOP SLAVE SQL_THREAD;'

After replication is halted, you can backup using one of the methods above. This allows you to keep the master MySQL database online while the slave is backed up.

When this is complete, restart replication by typing:

mysqladmin -u user_name -p start-slave

Making the Backup Machine Read-Only Temporarily

You can also ensure a consistent set of data within the server by making the data read-only temporarily.

You can perform these steps on either the master or the slave systems.

First, log into MySQL with enough privileges to manipulate the data:

mysql -u root -p

Next, we can write all of the cached changes to the disk and set the system read-only by typing:

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

Now, perform your backup using mysqldump.

Once the backup is complete, return the system to its original working order by typing:

SET GLOBAL read_only = OFF;
UNLOCK TABLES;

A Note About Techniques That Are No Longer Recommended

mysqlhotcopy

MySQL includes a perl script for backing up databases quickly called "mysqlhotcopy". This tool can be used to quickly backup a database on a local machine, but it has limitations that make us avoid recommending it.

The most important reason we won't cover mysqlhotcopy's usage here is because it only works for data stored using the "MyISAM" and "Archive" storage engines.

Most users do not change the storage engine for their databases and, starting with MySQL 5.5, the default storage engine is "InnoDB". This type of database cannot be backed up using mysqlhotcopy.

Another limitation of this script is that it can only be run on the same machine that the database storage is kept. This prevents running backups from a remote machine, which can be a major limitation in some circumstances.

Copying Table Files

Another method sometimes suggested is simply copying the table files that MySQL stores its data in.

This approach suffers for one of the same reasons as "mysqlhotcopy".

While it is reasonable to use this technique with storage engines that store their data in files, InnoDB, the new default storage engine, cannot be backed up in this way.

Conclusion

There are many different methods of performing backups in MySQL. All have their benefits and weaknesses, but some are much easier to implement and more broadly useful than others.

The backup scheme you choose to deploy will depend heavily on your individual needs and resources, as well as your production environment. Whatever method you decide on, be sure to validate your backups and practice restoring the data, so that you can be sure that the process is functioning correctly.

By Justin Ellingwood

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about our products

About the author

Justin Ellingwood

Author

See author profile

Former Senior Technical Writer at DigitalOcean, specializing in DevOps topics across multiple Linux distributions, including Ubuntu 18.04, 20.04, 22.04, as well as Debian 10 and 11.

Category:

Tags:

Still looking for an answer?

Ask a question Search for more help

Was this helpful?

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

dorgan

August 29, 2013

I would highly suggest using the --single-transaction parameter on large databases, otherwise INSERTS and UPDATE will wait until the current table is done being dump, and if you run a high transaction database those connections will just pile up and then you’ll get the infamous “too many connections” error

cloudnine

• September 12, 2016

Replying to dorgan

But, doesn’t this apply only to InnoDB tables? As per the MySQL docs, this parameter will not help dump either MyISAM or the MySQL memory tables in a consistent state. So, this may not be helpful for web applications that use both InnoDB and MyISAM tables - Magento is a good example.

alexdo

• August 20, 2024

Replying to dorgan

You’re absolutely right. When dealing with large databases and high-transaction environments, using the --single-transaction parameter with mysqldump is crucial. This option helps ensure that your dump is consistent without locking tables, which is especially important for busy databases where long-running locks can lead to performance issues and “too many connections” errors.

The --single-transaction option instructs mysqldump to perform a backup in a consistent state using a single transaction. This means that all tables are backed up in a consistent snapshot of the database at the start of the transaction, avoiding the need to lock tables for the duration of the dump.

Regards

alexdo

• August 22, 2024

Replying to dorgan

For large, high-transaction databases, the following command is recommended:

mysqldump -u [username] -p[password] --single-transaction --routines --triggers --events [database_name] > /path/to/backup/[backup_file].sql

E.g

mysqldump -u root -pmypassword --single-transaction --routines --triggers --events

Regards

manishbhatias

October 7, 2013

May I recommend Xtrabackup from Percona. http://www.percona.com/doc/percona-xtrabackup/2.1/

carlosspohr

November 2, 2013

Its important uses ‘–routines’ arg for mysqldump exports all triggers, views, procedures and functions of your database.

mysqldump -u root -p --routines … > out.sql

milo

November 27, 2013

My extracted back up files are empty. Did I miss something?

magim7002

November 29, 2013

@ Milo Felipe,

Can i have the mysqldump command ? Did you check your current DB size ? so that i can give a suggestion.

asendia

January 23, 2014

“For larger databases, where mysqldump would be impractical or inefficient, you can back up the raw data files instead”

http://dev.mysql.com/doc/mysql-backup-excerpt/5.6/en/replication-solutions-backups.html

kevinthulin

February 5, 2014

I am going to add a 1GB slave server, where I will make my backups.

Can I by cron job disable the replication on the slave server, then do a mysqlDump?

Kamal Nasser

February 8, 2014

@KiwoT: Why do you want to disable the replication?

info723957

July 22, 2014

Hi,

is there any way to bakcup all server config (ubuntu / plesk / apache / all mods / varnish config / fail2ban / … ) ?

thanks Best regards Edouard

Andrew SB

July 22, 2014

The simplest way to backup the entire server is to take a snapshot.

info723957

July 23, 2014

@Andrew SB ; great, i’ll install this so :) i found this: https://www.digitalocean.com/community/tutorials/how-to-install-rsnapshot-on-ubuntu-12-04 ;) thank you

eBernimont

June 12, 2015

I have a web solution to make auto mysql backup easly,

Here the link : http://www.abmysql.com/

In August 2014 our technical service alert: we lost all our mysql database after a restart of your servers. After studying the case, it can happen in larger society, the best! it’s scary. Are you sure that your data bases backup function properly? Do you have a backup system for your mysql databases? We fight every day for it to not happen and it does not happen to you

If you are interested to speak about my solution, I can put your link to my website

Here the link : http://www.abmysql.com/

Thanks, BERNIMONT Thomas

SAACONSULT

July 11, 2015

Did you know ? now www.abmysql.com,we use this web service, before we used command line but this is too hard sometimes, abmysql can help you

ashitvora

October 22, 2015

I just setup automysqlbackup. One small question I have is, do I need to manually add that command in cron job or it is added automatically?

faridmovsumov

March 5, 2016

Thanks for tutorial.

There is a little typo: “ls -R /var/lib/automysqlbackup/dailey” must be “ls -R /var/lib/automysqlbackup/daily”

e4eabfc03246622c13fc3416ff212c

May 3, 2016

Hello, on ubuntu repositories automysqlbackup is on version 2.6, how can i update to 3? thanks a lot

da21133f66e39390bf65a514b5158c

August 13, 2016

We are using this tool on all our servers at DO: backupbird.com

It gather all your servers in a simple dashboard and alerts you if a server fails to backup. You can see statistics on CPU usage and memory usage during backup.

It can backup MySql as well as regular server files.

It also shows the backup size over time.

hawk007

September 6, 2016

Thanks for the automysqlbackup setup. I never knew about that extention, but then I am new to ubuntu. It works fine.

Now all I have to do is learn how to create an automated WinSCP script to download the database and Im good to go.

I dont want to have an email server on the vps so cant use that option.

WinSCP should take a leaf from your book and create instructions for us simple folk.

Thanks again for the great informative instruction.

cloudnine

September 12, 2016

This is a very helpful overview of the db backup options. I wasn’t aware that mysqlhotcopy is not recommended.

However, doesn’t any kind of automated mysql backup (automysql backup or otherwise) involve using mysqldump, which in turn, is not transaction safe?

If yes, wouldn’t the best options for fail-safe db backups, especially for web sites and applications with significant traffic/transactions, without shutting the site or mysql server down, probably be?

Setup master-slave replication (where the slave would act as the backup), or,
Install and configure Percona XtraBackup

For sites or applications with infrequent transactions or where frequent maintenance windows are fine, automated backups using mysqldump are probably okay.

It would be interesting to hear some thoughts from other users about this.

Olek

September 19, 2016

This comment has been deleted

info723957

July 23, 2014

@Andrew SB ; great, i’ll install this so :) i found this: https://www.digitalocean.com/community/tutorials/how-to-install-rsnapshot-on-ubuntu-12-04 ;) thank you

eBernimont

June 12, 2015

I have a web solution to make auto mysql backup easly,

Here the link : http://www.abmysql.com/

If you are interested to speak about my solution, I can put your link to my website

Here the link : http://www.abmysql.com/

Thanks, BERNIMONT Thomas

SAACONSULT

July 11, 2015

Did you know ? now www.abmysql.com,we use this web service, before we used command line but this is too hard sometimes, abmysql can help you

ashitvora

October 22, 2015

I just setup automysqlbackup. One small question I have is, do I need to manually add that command in cron job or it is added automatically?

faridmovsumov

March 5, 2016

Thanks for tutorial.

There is a little typo: “ls -R /var/lib/automysqlbackup/dailey” must be “ls -R /var/lib/automysqlbackup/daily”

e4eabfc03246622c13fc3416ff212c

May 3, 2016

Hello, on ubuntu repositories automysqlbackup is on version 2.6, how can i update to 3? thanks a lot

da21133f66e39390bf65a514b5158c

August 13, 2016

We are using this tool on all our servers at DO: backupbird.com

It gather all your servers in a simple dashboard and alerts you if a server fails to backup. You can see statistics on CPU usage and memory usage during backup.

It can backup MySql as well as regular server files.

It also shows the backup size over time.

hawk007

September 6, 2016

Thanks for the automysqlbackup setup. I never knew about that extention, but then I am new to ubuntu. It works fine.

Now all I have to do is learn how to create an automated WinSCP script to download the database and Im good to go.

I dont want to have an email server on the vps so cant use that option.

WinSCP should take a leaf from your book and create instructions for us simple folk.

Thanks again for the great informative instruction.

cloudnine

September 12, 2016

This is a very helpful overview of the db backup options. I wasn’t aware that mysqlhotcopy is not recommended.

However, doesn’t any kind of automated mysql backup (automysql backup or otherwise) involve using mysqldump, which in turn, is not transaction safe?

Setup master-slave replication (where the slave would act as the backup), or,
Install and configure Percona XtraBackup

For sites or applications with infrequent transactions or where frequent maintenance windows are fine, automated backups using mysqldump are probably okay.

It would be interesting to hear some thoughts from other users about this.

Olek

September 19, 2016

This comment has been deleted

info723957

July 23, 2014

@Andrew SB ; great, i’ll install this so :) i found this: https://www.digitalocean.com/community/tutorials/how-to-install-rsnapshot-on-ubuntu-12-04 ;) thank you

eBernimont

June 12, 2015

I have a web solution to make auto mysql backup easly,

Here the link : http://www.abmysql.com/

If you are interested to speak about my solution, I can put your link to my website

Here the link : http://www.abmysql.com/

Thanks, BERNIMONT Thomas

SAACONSULT

July 11, 2015

Did you know ? now www.abmysql.com,we use this web service, before we used command line but this is too hard sometimes, abmysql can help you

ashitvora

October 22, 2015

I just setup automysqlbackup. One small question I have is, do I need to manually add that command in cron job or it is added automatically?

faridmovsumov

March 5, 2016

Thanks for tutorial.

There is a little typo: “ls -R /var/lib/automysqlbackup/dailey” must be “ls -R /var/lib/automysqlbackup/daily”

e4eabfc03246622c13fc3416ff212c

May 3, 2016

Hello, on ubuntu repositories automysqlbackup is on version 2.6, how can i update to 3? thanks a lot

da21133f66e39390bf65a514b5158c

August 13, 2016

We are using this tool on all our servers at DO: backupbird.com

It gather all your servers in a simple dashboard and alerts you if a server fails to backup. You can see statistics on CPU usage and memory usage during backup.

It can backup MySql as well as regular server files.

It also shows the backup size over time.

hawk007

September 6, 2016

Thanks for the automysqlbackup setup. I never knew about that extention, but then I am new to ubuntu. It works fine.

Now all I have to do is learn how to create an automated WinSCP script to download the database and Im good to go.

I dont want to have an email server on the vps so cant use that option.

WinSCP should take a leaf from your book and create instructions for us simple folk.

Thanks again for the great informative instruction.

cloudnine

September 12, 2016

This is a very helpful overview of the db backup options. I wasn’t aware that mysqlhotcopy is not recommended.

However, doesn’t any kind of automated mysql backup (automysql backup or otherwise) involve using mysqldump, which in turn, is not transaction safe?

Setup master-slave replication (where the slave would act as the backup), or,
Install and configure Percona XtraBackup

For sites or applications with infrequent transactions or where frequent maintenance windows are fine, automated backups using mysqldump are probably okay.

It would be interesting to hear some thoughts from other users about this.

Olek

September 19, 2016

This comment has been deleted

Nick

September 19, 2016

This comment has been deleted

Nick

September 19, 2016

As for me, the best way to backup MySQL databases on Linux is to use mysqldump. But if you are looking for tool to backup MySQL on Windows then try http://mysqlbackupftp.com/mysql-blog/how-to-backup-mysql-database/

luismuzquiz

September 21, 2016

Does automysqlbackup work with MaríaDB? @Jellingwood

dhavidcg

December 5, 2016

Thanks it really help me a lot.

jcamomile

January 11, 2017

" mysqldump -u username -p database_to_backup > backup_name.sql "

Where does this save to?

paysofts

October 23, 2018

Followed the below linked script from this page and it works well for me. It has much featured options listed there. Automatically can backup by your cron job. MySql AutoBackup GZIP [https://vvcares.com/w/mysql-auto-backup-all-databases-solved/]