Backup MySQL Databases on your server - Linux

Difficulty: 1
Time: 30 minutes

No system or application is completely fail-proof and bad things can happen. This is why it is so important to have backups in place to protect your data in case you need to recover from an application problem, such as a security compromise. This is especially true for databases - like MySQL - where your important data is often changing on an hourly basis.

In this tutorial, you will learn several ways to back up your MySQL databases on your server. This article will work for any Linux distribution running MySQL.

Use mysqldump to backup MySQL databases

A simple and easy method for creating MySQL backups is to use the mysqldump command. This command will create a simple .sql file of an existing database, which an then be restored to any other empty MySQL database. This command can be run from any Linux SSH terminal instead of being logged directly into MySQL.

Back up the database

The mysqldump command uses the following syntax:

mysqldump -u your username -pthe database name > backup file.sql
  • The -u flag lets you choose which MySQL user you will use to access the database and create the backup.
  • The -p flag tells MySQL to prompt you for a password for this user. This is safer than directly entering the password into the command.
  • The < or > markers indicate which direction the data is moving. This is also important to keep in mind when restoring the database.

(Optional) Make database read-only before backing up

If your application's database is constantly in flux, you may want to set the database to read-only temporarily when creating the backup. This is to ensure that you are getting a consistent dataset, and preventing possible data loss.

Warning: Be careful when making a database read-only as it will stop any data requests in or out of the database. It is not recommended that you bring a MySQL database read-only while the application is live in production.
  1. Log into MySQL with your root user, or a user with root privileges:
    mysql -u root -p

    Note: If you do not know your root MySQL password and need to reset it, see how to change your root MySQL password.

  2. Write any cached changes to the database:
    FLUSH TABLES WITH READ LOCK;
    SET GLOBAL read_only = ON;
  3. Exit the MySQL prompt by typing exit or typing CTRL-C on the keyboard.
  4. Perform your backup using mysqldump
    mysqldump -u your username -pthe database name > backup file.sql
  5. Now that you have created your backup, log back in to MySQL:
    mysql -u root -p
  6. Set your MySQL system back to normal:
    SET GLOBAL read_only = OFF;
    UNLOCK TABLES;
  7. Exit MySQL by typing exit or typing CTRL-C on the keyboard.

Using backups

Now that your database is backed up, you can restore that backup to a different database. This part of the guide will show you how to restore this backup to a new database, as well as how to restore this to a database on a different remote server.

(Optional) Create a new database

If you already have a database on this server you want to restore to, and you know the credentials, you can just skip to the steps for the rest of the restore. However, you can use these steps to quickly create a new MySQL database, where you can restore your backup. Keep track of these credentials, as you will need them later when you restore the database.

  1. Log into MySQL as root:
    mysql -u root -p
  2. Create a new database and user if you don't already have one:
    CREATE DATABASE new database;
    CREATE USER new user@localhost;
  3. Give this new user a password and grant privileges for that database:
    SET PASSWORD FOR new user@localhost= PASSWORD("new user's password");
    GRANT ALL PRIVILEGES ON new database.* TO new user@localhost IDENTIFIED BY 'new user's password';
    FLUSH PRIVILEGES;
  4. Close the MySQL prompt by typing exit into the MySQL terminal, or typing CTRL-C on your keyboard.

Restore the backup to a local database server

The mysql command will let you take the contents of a .sql file backup, and restore it directly to a database. This is the syntax for the command:

mysql -uyour username -pthe database name < backup file name.sql

Note: Notice the direction of the <, pointing towards the database. Also notice the lack of space between the -u flag and the username. These are different than the previous mysqldump command example, and you should be mindful of both.

To demonstrate the previous example, to restore the original backup file.sql to the new database, you would use the following command:

mysql -unew user -pnew database < backup file.sql

Restore the backup to a remote database server

You can also use the mysql command to restore a .sql file backup to a remote MySQL server. If you have another server running MySQL, and you have the database credentials, you can define a hostname in the command by adding the -h flag to specify a hostname. This changes the syntax of the command to:

mysql -hhostname -uyour username -pthe database name < backup file name.sql

To demonstrate this example, we will restore the same .sql file to a remote MySQL server that we have set up on the hostname mysql.coolexample.com with different credentials. Use your own remote hostname for your MySQL server.

mysql -hmysql.coolexample.com -uremoteuser -premote_database < backup file.sql

As long as you have the correct credentials and the remote server is running, you will be able to restore the database remotely.

Automate MySQL database backups with Cron

So now you have a way to back up your databases. But you probably don't want to have to manually run a command each time you want to run the backup. This section will show you how to create a Bash script to simplify the backup process, and use cron to make that script run every night at 2:00 A.M.

Create backup script

  1. Navigate to your home directory:
    cd ~
  2. Make a new directory to store the .sql backups:
    mkdir db_backups
  3. Create a new file in a text editor
    vim backitup.sh
  4. Inside this file, paste the following contents, replacing the example database information or credentials with your own:
    #!/bin/bash
    mysqldump -uyour username -ppassword the database name > ~/db_backups/`date +%Y%m%d`.the database name.sql
  5. Save and close the file:
    :wq!
  6. Make the file executable:
    chmod u+x backitup.sh

You have taken the mysqldump command you previously used and modified it into a script. You added the password to this script, and the backup file will now have the date it ran at the beginning of the file (e.g. YYYYMMDD.database_name.sql). Now you can run this backup simply by running the command through bash:

sh ./backitup.sh

Once you run this, you should see your new backup file in ~/db_backups.

Note: This is your backup script - modify as you see fit! You can change this to restore to a remote database instead of a local one, restore it to a different directory, temporarily set the database to read only before restoring, or even backup multiple databases simultaneously. This is only a very simple example of using a bash script to automate your server administration.

Use cron to set up automatic rotating backups

Cron is the system automation tool used by all Linux distributions. Now that we have a functional script, we can use cron to automate this. You will configure cron to automatically run the script every day at midnight, and delete any copies older than 10 days. This way you should always have daily snapshots going back 10 days, but you don't accidentally fill up your disk space with redundant backups.

  1. Use the crontab command to open the cron file for editing:
    crontab -e
    You may need to select your preferred text editor. This example will use 3 for vim.basic.
  2. Add this line to the end of the file:
    1 0 * * * /bin/bash $HOME/backitup.sh
    This is the backup script automation that runs every day at 12:01 AM.
  3. Add this line to the end of the file as well:
    1 0 * * * /usr/bin/find $HOME/db_backups/* -mtime +10 -exec rm {} \\;
    This will search ~/db_backups for any files older than 10 days and remove them. This also runs every day at 12:01 AM. Your paths for bash and find may be different depending on your Linux distribution. You can find this path by using the which command, which will display the executable for that command:
    user@host:~$ which find
    /usr/bin/find

Your server will now automatically create a backup of the MySQL databases you specified in your backup script, and keep those backups on a 10-day rotating cycle.

Conclusion

If you mastered this, you're ready to move onto even more advanced features of mysqldump command, which you can find in the official MySQL documentation.


Was This Article Helpful?
Thanks for your feedback. To speak with a customer service representative, please use the support phone number or chat option above.
Glad we helped! Anything more we can do for you?
Sorry about that. Tell us what was confusing or why the solution didn’t solve your problem.