{"id":5302,"date":"2022-11-01T15:46:47","date_gmt":"2022-11-01T13:46:47","guid":{"rendered":"https:\/\/www.scalahosting.com\/kb\/?p=5302"},"modified":"2024-03-07T11:45:52","modified_gmt":"2024-03-07T09:45:52","slug":"how-to-back-up-a-mysql-database","status":"publish","type":"post","link":"https:\/\/www.scalahosting.com\/kb\/how-to-back-up-a-mysql-database\/","title":{"rendered":"How to Back Up a MySQL Database"},"content":{"rendered":"\n

Backing up your site’s files is relatively straightforward<\/strong>. In essence, you put them all in an archive, which you can later extract and restore everything. When it comes to generating backups of your MySQL databases<\/strong><\/a>, however, things are a bit more complicated.<\/p>\n\n\n\n

Today, we’ll look into the different types of database backups<\/strong>, and we’ll see some of the most popular ways to back up and restore your website’s data.<\/p>\n\n\n\n

Types of MySQL Backups<\/strong><\/h2>\n\n\n\n

MySQL is a<\/strong> relational database management system <\/strong>used by most modern websites and applications. The information is stored in tables, with relations between the different datasets. In effect, these relations build the structure of the database<\/strong>.<\/p>\n\n\n\n

A working backup of a database must be able to recreate the tables and the relations between them in the way they are originally organized. Ideally, it must do it quickly and efficiently in order to minimize service interruption.<\/p>\n\n\n\n

There are two main types of MySQL database backups<\/em>:<\/p>\n\n\n\n

Logical backups<\/strong><\/h3>\n\n\n\n

A logical backup exports (or dumps) the data and the data structure to a file with an SQL extension. SQL files are created by running scripts either through a third-party GUI-based tool or from a shell. Once generated, the backup can be stored locally, sent to another device on the network, or, ideally, transferred to an offsite location (it could be a server located in a different center, but it could also be an account with a popular cloud storage provider like Dropbox, for example).<\/p>\n\n\n\n

In addition to the data itself, a logical backup contains the SQL statements<\/strong> (e.g., CREATE DATABASE, CREATE TABLE<\/em>, etc.) \u2013 instructions that enable MySQL to rebuild the database with the correct table structure and relations. The main advantage of logical backups is that they can be used to transfer a database from one host to another.<\/p>\n\n\n\n

Physical backups<\/strong><\/h3>\n\n\n\n

A physical backup<\/strong> is a snapshot of the database’s datadir directory<\/strong>. All the tables and the data inside them are copied in their original file formats and can be restored in a fully functioning database<\/strong> as long as the structure is maintained.<\/p>\n\n\n\n

Physical backups are quicker but can only work if they are restored from the same database engine on the same MySQL version<\/strong><\/a>. Hence, they aren’t always suitable for moving a database from one environment to another.<\/p>\n\n\n\n

Your backup strategy<\/strong> should be determined by a number of different factors including the hosting setup, the database size, and your requirements.<\/p>\n\n\n\n

Logical backups tend to be more common because, as we’ll find out in a minute, there are several ways of creating them, with tools readily available and fairly easy to use. Furthermore, logical backups<\/strong> can copy individual database objects, giving you more flexibility<\/strong> and a quicker resolution when you’re dealing with data corruption problems in specific tables<\/strong>. They are also more suitable if you’re looking for an incremental backup solution.<\/p>\n\n\n\n

Overall, while physical backups do make sense in a few specific scenarios<\/strong>, for the regular website owner, logical ones are easier to set up and manage. That’s why today’s article will focus on them.<\/p>\n\n\n\n

Creating a Database Backup via the Command Line<\/strong><\/h2>\n\n\n\n

If you use the command line, you’ll export MySQL databases with<\/strong> mysqldump command-line utility\u00a0 \u2013 a tool designed for this specific purpose<\/strong>. The lack of a graphical user interface means many will likely prefer one of the other methods. However, if you decide to give it a go, you’ll see that there’s nothing too complicated about the process.<\/p>\n\n\n\n

Your first job is to open an SSH shell<\/strong><\/a>. Modern operating systems support the protocol out of the box. Windows computers connect to remote servers via PowerShell or the Command prompt tool<\/strong>, and Unix-based systems do it with the Terminal<\/strong>.<\/p>\n\n\n\n

Some people prefer dedicated SSH clients like PuTTY<\/strong>, and if you have an SPanel server, you can open a shell directly via the SSH Terminal<\/strong><\/a> available on the homepage of the User Interface.<\/p>\n\n\n\n

Before you continue, make sure you use a MySQL user account<\/strong><\/a> with privileges over the database you’re about to back up. If you log in as root<\/strong> or any other account with SYSTEM_USER permissions<\/strong>, you can export data from any database you want.<\/p>\n\n\n\n

It is worth mentioning that mysqldump won’t be able to back up a database if one of the tables is corrupted. To make sure everything will go smoothly, you can use the mysqlcheck <\/strong>command-line utility to connect to the MySQL server and check for corrupted data.<\/p>\n\n\n\n

The command looks like this:<\/strong><\/p>\n\n\n\n

$ mysqlcheck [the database’s name] -u [your MySQL user’s username] -p<\/em><\/em><\/p>\n\n\n\n

After you provide your user account’s password, mysqlcheck will scan all tables for data corruption. Ideally, the output will look like this:<\/strong><\/strong><\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

To check all databases, add the –all-databases <\/em><\/strong>option. With that done, you can proceed with the backup.<\/p>\n\n\n\n

The mysqldump utility covers quite a few scenarios. Here are the most common ones:<\/p>\n\n\n\n

Backing up a single database via the command line.<\/strong><\/strong><\/h3>\n\n\n\n

To back up one of the databases on your account, you need a command that looks like this:<\/strong><\/strong><\/p>\n\n\n\n

$ mysqldump -u [your MySQL account’s username] -p [the name of the database] > [the name of the backup file]<\/em><\/em><\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

With the above command, the user test_user <\/em>exports the database test_database<\/em><\/strong> <\/em>to the file backup.sql<\/em>. All you need to do is enter the user account’s password. If backup.sql<\/em><\/strong> <\/em>doesn’t exist, MySQL will automatically create it.<\/strong><\/p>\n\n\n\n

Backing up specific tables from a selected database<\/strong><\/strong><\/h3>\n\n\n\n

The mysqldump utility can also back up specific tables only. Just add the table’s name after the database. Here’s an example:<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

In the screenshot above, test_user<\/em> is trying to back up the table wp_posts<\/em> from test_database2<\/em> to the posts.sql <\/em>file. To dump multiple tables to the same file, simply add their names separated by spaces. For example:<\/p>\n\n\n\n

$ mysqldump -u test_user -p test_database2 wp_posts wp_options wp_users wp_links > wordpress.sql<\/em><\/strong><\/em><\/strong><\/p>\n\n\n\n

Multiple database backups<\/strong><\/strong><\/h3>\n\n\n\n

You can export more than one database to the same file with mysqldump<\/strong>. To do that, use the command above, adding the –databases <\/em><\/strong>option and the names of the databases separated by spaces. For example:<\/p>\n\n\n\n

$ mysqldump -u test_user -p –databases test_database test_database2 test_database3 > backup.sql<\/em><\/strong><\/em><\/strong><\/p>\n\n\n\n

Backing up all accessible databases<\/strong><\/strong><\/h3>\n\n\n\n

If you want to back up all the databases you have control over, replace the database name with the –all-databases<\/em><\/strong> option. For example:<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

This will dump all databases except information_schema<\/strong>, performance_schema<\/strong> and any other default MySQL schemas<\/strong>. To include them in the backup, add the — skip-lock-tables<\/em><\/strong> option.<\/p>\n\n\n\n

Backing up a database structure only<\/strong><\/strong><\/h3>\n\n\n\n

Sometimes, you may need to generate a backup of a database’s structure without the data stored<\/strong> in it. The mysqldump utility can do it if you include the –no-data<\/em><\/strong> option. Here’s what the command looks like:<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

Backing up the data without the structure<\/strong><\/strong><\/h3>\n\n\n\n

The reverse is also possible \u2013 you can backup the data without the structure<\/strong>. The option is \u2013no-create-info<\/em><\/strong>, and the command looks like this:<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

The mysqldump utility<\/strong> won’t print a message telling you that it has successfully backed up your database, so you can use the ls<\/em><\/strong> command to confirm that the backup is completed.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

Dumping a large database with mysqldump<\/strong><\/strong><\/h3>\n\n\n\n

Using mysqldump on larger databases<\/em><\/strong> could be trickier. If you have lots of GBs to back up, you may experience increased server load<\/strong>, and if the database is really big, the entire operation could fail.<\/p>\n\n\n\n

The first problem is the amount of storage the backup<\/strong> takes up. Large databases produce large files, and although mysqldump has some options like –extend-insert<\/em> that help to some extent, they aren’t always as effective as you may hope.<\/p>\n\n\n\n

What is almost guaranteed to shrink the size of your backup is piping mysqldump’s backup through gzip <\/strong>and compressing the output file before writing it on the disk.<\/p>\n\n\n\n

The command will looks like this:<\/p>\n\n\n\n

$ mysqldump -u [your mysql account’s username] -p [your database’s name] | gzip > [filename].sql.gz<\/em><\/strong><\/em><\/strong><\/p>\n\n\n\n

On the one hand, this reduces the size of the generated backup, and on the other, it cuts the IO load<\/strong>.<\/p>\n\n\n\n

The mysqldump utility has a couple of other options that may also help. The –opt <\/em><\/strong>option encompasses several different command parameters<\/strong> that optimize the dump operation. Bear in mind that they make the backup more difficult to understand by other database systems<\/strong>, so this may not be the best approach if you want to migrate the database to a new host.<\/p>\n\n\n\n

The –quick<\/em><\/strong> option may also work. By default, mysqldump stores each table row into memory before dumping it into the backup file. with the –quick option<\/em>, the data is transferred directly to the backup.<\/p>\n\n\n\n

If a large backup operation fails, you can try to increase the max_allowed_packet<\/strong> parameter in MySQL’s configuration file<\/strong> (the my.cnf file usually stored in \/etc<\/em>). This is usually necessary only when you’re trying to shift dozens of gigabytes of information, and it’s not guaranteed to succeed.<\/p>\n\n\n\n

Putting that much data into a single file will never be easy, and many experts suggest that in such cases, you’re better off using a physical backup solution<\/strong> like MySQL Enterprise Backup<\/strong> or Percona XtraBackup<\/strong>.<\/p>\n\n\n\n

How to Back Up a MySQL Database Using phpMyAdmin?<\/strong><\/strong><\/h2>\n\n\n\n

Many shared and managed hosting accounts come with phpMyAdmin preinstalled. Its default login URL is https:\/\/[your server’s IP]\/phpmyadmin<\/em><\/strong> (it’s deliberately changed by some hosts for security reasons), though it’s often accessible from the control panel, as well.<\/p>\n\n\n\n

It has an easy-to-use graphic interface, meaning you don’t need to learn any SQL commands to manage your database. You can also export data with a few mouse clicks<\/strong>. Here’s how to do it.<\/p>\n\n\n\n

Backing up a single database<\/strong><\/strong><\/h3>\n\n\n\n

After you open phpMyAdmin, you’ll see a list of all the databases on your hosting account<\/strong><\/a>.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

Click on the one you want to back up and go to the Export<\/strong> tab.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

You can choose the export method and the format. It’s preferable to keep the SQL format<\/strong>, as this will give you more options when you need to restore the backup.<\/p>\n\n\n\n

As for the method, Quick<\/strong> is selected by default, and if you leave it like that, phpMyAdmin<\/strong><\/a> will dump the entire database (the data and the structure<\/em>) into the SQL file<\/strong>.<\/p>\n\n\n\n

If you select the Custom <\/strong>radio button, you’ll see quite a few more options. First, you can select which tables you’d like to dump. You can back up the structure, the data, or both.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

Further down, you can have phpMyAdmin automatically rename the database<\/strong>, its tables, and\/or columns while exporting them. This is where you specify the file name template as well. By default, it’s set to “@DATABASE@<\/strong>” which means that phpMyAdmin will name the backup file after your database.<\/p>\n\n\n\n

You can lock the database’s tables or export them as separate files. Options for the exported file’s encoding and compression<\/strong> are also available in this section. You can skip tables over a certain size and see the output as text instead of dumping it into an SQL file<\/em>.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

In the Format-specific options <\/strong>section, you decide whether to have comments, metadata, and other formatting elements in your backup. You can have phpMyAdmin export the database as a transaction<\/strong>, disable foreign key checks, and dump views as tables.<\/p>\n\n\n\n

There’s also a drop-down menu letting you make the backup backward compatible with a number of older systems.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

Next, you have the Object creation options <\/strong>section. It mainly deals with the statements that will be added to the backup, which concern the way the SQL file will recreate the database’s contents.<\/p>\n\n\n\n

For example, the CREATE DATABASE and USE <\/strong>statements can save you some time when you need to restore the data to a new empty database. If, on the other hand, you plan on restoring the data into an existing database, you can have the DROP statements overwrite the old data.<\/p>\n\n\n\n

If the IF NOT EXISTS <\/strong>option is enabled, your backup will check for matching tables before trying to create them, and with the AUTO_INCREMENT checkbox<\/strong>, you can append the backed up data to existing tables.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

In the Data creation options<\/strong> section, you’ll find more settings related to data restoration. There are a few advanced options that may require a tweak in certain cases. However, for most website owners, the default configuration should work fine.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

After you’re done tweaking the settings, click Go<\/strong> to generate the backup<\/strong>.<\/p>\n\n\n\n

Multiple database backups<\/strong><\/strong><\/h3>\n\n\n\n

The steps to back up multiple databases<\/strong> are pretty much the same as those for dumping a single one. The difference is, instead of selecting a database from the menu on the left, you need to go straight to the Export <\/strong>tab from the homepage.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

If you’d like to back up all your databases at once, set the export method to Quick<\/strong> and the format to SQL<\/strong>, and click Go<\/strong> to have phpMyAdmin generate the backup. If you select the Custom <\/strong>method, you will be choosing which databases to back up. Click Unselect all<\/strong> and mark the ones you want to back up using the Ctrl\/Cmd<\/strong> key.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

Further down, in the Format-specific options<\/strong> section, you can decide whether to dump data from the selected databases, back up the structure only, or both.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

The rest of the settings are the same as the ones you see when you’re exporting a single database.<\/p>\n\n\n\n

Database Backups With MySQL Workbench<\/strong><\/strong><\/h2>\n\n\n\n

MySQL Workbench is another free database management tool. You can set it up on your home computer (it’s available for Linux, Windows, and MacOS) and control your databases remotely.<\/p>\n\n\n\n

You’ll first need to connect to your hosting account<\/strong>. There’s a +<\/strong> button in the MySQL Connections<\/strong> section on the homepage.<\/p>\n\n\n\n

Next, you have to determine how you’re going to connect to the server. The best option is to use the standard TCP\/IP protocol over SSH<\/strong>. This way, the communication will be encrypted, and your data will be safe.<\/p>\n\n\n\n

You’ll have to provide the SSH login credentials<\/strong> as well as the ones for your MySQL user account<\/strong>. After you fill in all the required fields, click Test Connection <\/strong>to ensure everything works. If it does, hit OK<\/strong> to save the the settings.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

After you connect to the server, open the Administration tab<\/strong> in the Navigator<\/strong> section and click Data Export<\/strong>.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

In the next window, you’ll see the databases your user can control and the different export options<\/strong>.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

You can select the databases you want to with the using the checkboxes next to them. And when you click on a database, you’ll see all its tables, so you can exclude specific tables from the backup<\/strong>.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

Below the lists of databases and tables, you have a drop-down menu letting you decide whether you want to back up the database, the structure, or both. When you’re ready with the configuration, click Start Export<\/strong>.<\/p>\n\n\n\n

Database Backups in SPanel<\/strong><\/strong><\/h2>\n\n\n\n

SPanel strives to cover every aspect of modern website building and development. This includes database management, which is why every SPanel server comes with<\/strong> phpMyAdmin<\/strong> preinstalled.<\/p>\n\n\n\n

However, we wanted to ensure you have even more control over your site’s data, which is why, you have quite a few tools in the MySQL Databases <\/strong>section inside SPanel’s User Interface<\/strong>.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

To get to the User Interface, you can either use the account’s login credentials at https:\/\/[the account’s domain name]\/spanel\/<\/em><\/strong> or log in to your server’s Admin Area and select Manage <\/strong>from the Actions<\/strong> drop-down menu next to your account.<\/p>\n\n\n\n

In the MySQL Databases<\/strong> section you’ll see a list of all the databases on your account. Locate the one you want to back up, open the Actions<\/strong> drop-down menu, and select Export & Download Database<\/strong>.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

SPanel will put the data into an SQL file and save it inside the account’s home directory<\/em>. Then, a popup lets you download the file for local storage and remove it from the server.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

This is the quickest way of backing up a database in SPanel, and it’s probably the one you should go for when you want to play it safe before making any changes to your website. It’s not the only method, though.<\/p>\n\n\n\n

If you want more options, go back to the SPanel user interface homepage and click Backups <\/strong>> New Manual Backup<\/strong>. Under Which databases do you want to include?<\/strong> section, you have a list of the existing databases on your account. you can select the ones you need via the checkboxes or click Add All <\/strong>to back up all of them.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

Further down, you have a range of options, letting you configure anything from the backup’s contents to the encryption and compression mechanisms. You can even send it to a remote location by entering the target server’s FTP credentials.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

Click Submit<\/strong>, and a few seconds later, the archive will be available under the Backups <\/strong>> Manual Backups <\/strong>section. Your last backup is at the top of the list.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

In addition to the manual backups they create themselves, every SPanel customer at ScalaHosting has daily secure copies of their files and databases, which are stored in a remote backup location<\/strong>.<\/p>\n\n\n\n

They’re not in the same data center<\/strong> as your production site, so even if the system crashes and the entire server fails, you can recover the information in seconds. If your backed up databases are stored in a cloud environment, you have multiple servers <\/strong>working simultaneously to guarantee that the data can be restored should the need arises.<\/p>\n\n\n\n

Automatically Back Up a MySQL Database in Linux<\/strong><\/h2>\n\n\n\n

If you use a self-managed web hosting service, you are unlikely to have a tool for creating automatic database backups. You’ll have to create one yourself, which will probably sound a bit intimidating. If you’re not used to working with Linux, the prospect may sound even more daunting.<\/p>\n\n\n\n

However, once you understand the logic, you’ll see that there’s nothing scary about the task.<\/p>\n\n\n\n

In essence, our backup solution revolves around a simple script that uses the mysqldump utility to dump selected databases, compresses them with gzip, and, after naming them according to a predetermined convention, saves them to a backup folder on your server.<\/p>\n\n\n\n

There are several options for creating the script. You can use a text editor on your local computer and then upload the script to the server. If your server comes with a control panel, you can also use the File Manager and the built-in editor to set it up. And for those who prefer to use the command line for these sorts of things, there are text editors like GNU nano and Vim.<\/p>\n\n\n\n

We’re creating a shell script, so the file must have an .sh extension (e.g., dbbackup.sh). You can situate it in any directory on your server.<\/p>\n\n\n\n

In addition to creating new backups, the script is also responsible for deleting old ones. All this in no more than a few dozen lines of code. Here it is:<\/p>\n\n\n\n

#!\/usr\/bin\/bash<\/em><\/strong><\/p>\n\n\n\n

# Date format<\/em><\/strong><\/p>\n\n\n\n

DATE_FORMAT=$(date +”%Y-%m-%d”)<\/em><\/strong><\/p>\n\n\n\n

# MySQL login credentials<\/em><\/strong><\/p>\n\n\n\n

MYSQL_HOST=”localhost”<\/em><\/strong><\/p>\n\n\n\n

MYSQL_USER=”[Your MySQL account’s username.]”<\/em><\/strong><\/p>\n\n\n\n

MYSQL_PASSWORD=”[Your MySQL account’s password.]”<\/em><\/strong><\/p>\n\n\n\n

# Backup directory<\/em><\/strong><\/p>\n\n\n\n

LOCAL_BACKUP_DIR=”[The directory reserved for storing backups.]”<\/em><\/strong><\/p>\n\n\n\n

# Retention period for the backup files<\/em><\/strong><\/p>\n\n\n\n

BACKUP_RETAIN_DAYS=07<\/em><\/strong><\/p>\n\n\n\n

# Database name(s)<\/em><\/strong><\/p>\n\n\n\n

DATABASES=”[The name of the database. If you want to back up multiple databases, simply separate them with spaces]”<\/em><\/strong><\/p>\n\n\n\n

# Creating the backup directory<\/em><\/strong><\/p>\n\n\n\n

mkdir -p ${LOCAL_BACKUP_DIR}<\/em><\/strong><\/p>\n\n\n\n

# Using mysqldump to back up the databases and gzip to compress them<\/em><\/strong><\/p>\n\n\n\n

for db in $DATABASES; do<\/em><\/strong><\/p>\n\n\n\n

   mysqldump \\<\/em><\/strong><\/p>\n\n\n\n

    <\/em><\/strong> <\/em><\/strong>-h ${MYSQL_HOST} \\<\/em><\/strong><\/p>\n\n\n\n

    <\/em><\/strong> <\/em><\/strong>-u ${MYSQL_USER} \\<\/em><\/strong><\/p>\n\n\n\n

    <\/em><\/strong> <\/em><\/strong>-p${MYSQL_PASSWORD} \\<\/em><\/strong><\/p>\n\n\n\n

    <\/em><\/strong> <\/em><\/strong>–single-transaction ${db} | gzip -9 > ${LOCAL_BACKUP_DIR}\/${db}-${DATE_FORMAT}.sql.gz<\/em><\/strong><\/p>\n\n\n\n

done<\/em><\/strong><\/p>\n\n\n\n

# Removing backups once their retention period runs out<\/em><\/strong><\/p>\n\n\n\n

DBDELDATE=`date +”${DATE_FORMAT}” –date=”${BACKUP_RETAIN_DAYS} days ago”`<\/em><\/strong><\/p>\n\n\n\n

if [ ! -z ${LOCAL_BACKUP_DIR} ]; then<\/em><\/strong><\/p>\n\n\n\n

 cd ${LOCAL_BACKUP_DIR}<\/em><\/strong><\/p>\n\n\n\n

 if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then<\/em><\/strong><\/p>\n\n\n\n

 rm -rf ${DBDELDATE}<\/em><\/strong><\/p>\n\n\n\n

 fi<\/em><\/strong><\/p>\n\n\n\n

fi<\/em><\/strong><\/p>\n\n\n\n

## End<\/em><\/strong><\/p>\n\n\n\n

Before you save the script, make sure you enter the correct information in the placeholders above. The next step is to ensure your shell script is executable. If you use a File Manager or an FTP client, you’ll likely have a button or a context menu option. Through SSH, you can use the following command:<\/p>\n\n\n\n

$ chmod +x [the name of the script file]<\/em><\/strong><\/p>\n\n\n\n

At this point, you may want to test the script and ensure it works. Because we have a shell script, we can only start it from the command line. You can use the sh <\/em><\/strong>command followed by the name of the file.<\/p>\n\n\n\n

With that done, it’s time to add the script to a cron job and make sure it’s executed at regular intervals. If your server is equipped with a control panel, you most likely have a GUI-based tool for managing your cron jobs. It most likely looks something like this:<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

The first fields and drop-downs determine how frequently the script will be executed, and in Cron command<\/strong>, you need to enter sh [the backup script’s filepath]<\/em><\/strong>.<\/strong><\/p>\n\n\n\n

Alternatively, you can do it all from the command-line interface. To do that, you have to edit the crontab<\/strong> file. Your Linux server likely has multiple text editors installed and ready to use. However, GNU nano is recognized as the easiest to use, so the command we’ll enter looks like this:<\/p>\n\n\n\n

$ EDITOR=nano crontab -e<\/em><\/strong><\/p>\n\n\n\n

You’ll see a configuration file with all your currently active cron jobs. If there aren’t any, the file will be blank.<\/p>\n\n\n\n

We want our dbbackup.sh file to be executed daily, but we don’t want it to run during the busy part of the day, when the server’s under heavy load. That’s why, we’ll schedule its execution for 2 AM. The line you need to add to your file is:<\/p>\n\n\n\n

0 2 * * * sh [the filepath for your backup script]<\/em><\/strong><\/p>\n\n\n\n

On the next day, you can confirm whether the script has been executed correctly.<\/p>\n\n\n\n

Automatically Back Up a MySQL Database to Amazon S3<\/strong><\/h2>\n\n\n\n

The previous section showed you how to automate database backups on a self-managed Linux server. It’s a great way of getting some extra peace of mind, but the setup isn’t quite perfect.<\/p>\n\n\n\n

The backups you create with it are stored on your production server. If, for whatever reason, it goes down, your backups will become inaccessible, and you won’t be able to restore them as quickly as possible and minimize downtime. Worst case scenario, you could irreversibly lose some data.<\/p>\n\n\n\n

That’s why experts recommend storing your backups in a remote location<\/strong>.<\/p>\n\n\n\n

Now, you could get yourself another server just for your backups, but this will hardly be the most cost-effective solution. If it’s situated in the same data center as the production machine, it won’t be the most secure<\/a>, either.<\/p>\n\n\n\n

Using one of the popular cloud storage services like an AWS S3 bucket is a much better choice. As an added bonus, configuring your server to automatically send your database backups to Amazon’s cloud is relatively straightforward.<\/p>\n\n\n\n

Your first job is to make sure you have AWS CLI installed on your server.<\/p>\n\n\n\n

AWS CLI is available in most package managers, so if you use Ubuntu or another Debian-based distribution, you can install it with:<\/p>\n\n\n\n

$ sudo apt install awscli<\/em><\/strong><\/p>\n\n\n\n

For CentOS 7 and similar distros, the command is:<\/p>\n\n\n\n

$ sudo dnf install awscli<\/em><\/strong><\/p>\n\n\n\n

And for CentOS 8 and Fedora, it’s:<\/p>\n\n\n\n

$ sudo yum install awscli<\/em><\/strong><\/p>\n\n\n\n

Alternatively, you can download the zip archive from https:\/\/awscli.amazonaws.com\/awscli-exe-linux-x86_64.zip<\/a>, extract it, and install the utility manually.<\/p>\n\n\n\n

With AWS CLI installed, you need to head over to the AWS Management Console. Click on your root account email in the top-right corner, and select Security Credentials<\/strong> from the drop-down menu. Find the Access keys<\/strong> section and click Create access key<\/strong>. <\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

This access key allows you to connect to your AWS account via AWS CLI. All you need to do is configure AWS CLI to use it. The command is:<\/p>\n\n\n\n

$ aws configure<\/em><\/strong><\/p>\n\n\n\n

AWS CLI will as you for the Access Key ID, the Secret Access Key, the default region, and the output format. After you provide the information, you will be able to use AWS CLI to create a new S3 bucket and send your database backups to it.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

Next, it’s time to create the bucket that will hold your backups. As you have an open shell, the easiest way to do it is with AWS CLI. The command is:<\/p>\n\n\n\n

$ aws s3api create-bucket –bucket [the bucket’s name] –region [the nearest AWS location to your server]<\/em><\/strong><\/p>\n\n\n\n

If you prefer to use a graphical user interface, you can also set up the bucket via the AWS management console.<\/p>\n\n\n\n

The code from the previous section can already take care of creating, compressing, and cleaning backups, so what we need to do now is add the functionality that sends the secure copies to your AWS S3 bucket. With it, your shell script should look like this:<\/p>\n\n\n\n

#!\/usr\/bin\/bash <\/em><\/strong><\/p>\n\n\n\n

# Date format<\/em><\/strong><\/p>\n\n\n\n

DATE_FORMAT=$(date +”%d-%m-%Y”)<\/em><\/strong><\/p>\n\n\n\n

# MySQL login credentials<\/em><\/strong><\/p>\n\n\n\n

MYSQL_HOST=”localhost”<\/em><\/strong><\/p>\n\n\n\n

MYSQL_USER=”[Your user’s username]”<\/em><\/strong><\/p>\n\n\n\n

MYSQL_PASSWORD=”[The user’s password]”<\/em><\/strong><\/p>\n\n\n\n

# Backup directory on the server<\/em><\/strong><\/p>\n\n\n\n

LOCAL_BACKUP_DIR=”[a directory on your server where you’ll store backups, e.g., home\/user\/backups\/]”<\/em><\/strong><\/p>\n\n\n\n

# S3 bucket details<\/em><\/strong><\/p>\n\n\n\n

S3_BUCKET_NAME=”[your S3 bucket’s name]”<\/em><\/strong><\/p>\n\n\n\n

S3_BUCKET_PATH=”[a directory in the bucket reserved for your backups]”<\/em><\/strong><\/p>\n\n\n\n

# Retention period for local backup files<\/em><\/strong><\/p>\n\n\n\n

BACKUP_RETAIN_DAYS=7<\/em><\/strong><\/p>\n\n\n\n

# Database name(s)<\/em><\/strong><\/p>\n\n\n\n

DATABASES=”[if you want to back up multiple databases, add their names separated by spaces]”<\/em><\/strong><\/p>\n\n\n\n

# Creating a new directory for today’s backup<\/em><\/strong><\/p>\n\n\n\n

mkdir -p ${LOCAL_BACKUP_DIR}<\/em><\/strong><\/p>\n\n\n\n

 # Setting the local and remote backup directories<\/em><\/strong><\/p>\n\n\n\n

LOCAL_DIR=${LOCAL_BACKUP_DIR}<\/em><\/strong><\/p>\n\n\n\n

REMOTE_DIR=s3:\/\/${S3_BUCKET_NAME}\/${S3_BUCKET_PATH}<\/em><\/strong><\/p>\n\n\n\n

# Using mysqldump to back up the selected databases and gzip to archive them<\/em><\/strong><\/p>\n\n\n\n

for db in $DATABASES; do<\/em><\/strong><\/p>\n\n\n\n

   mysqldump \\<\/em><\/strong><\/p>\n\n\n\n

        -h ${MYSQL_HOST} \\<\/em><\/strong><\/p>\n\n\n\n

        -u ${MYSQL_USER} \\<\/em><\/strong><\/p>\n\n\n\n

        -p${MYSQL_PASSWORD} \\<\/em><\/strong><\/p>\n\n\n\n

        –single-transaction ${db} | gzip -9 > ${LOCAL_BACKUP_DIR}\/${db}-${DATE_FORMAT}.sql.gz<\/em><\/strong><\/p>\n\n\n\n

# Using AWS CLI to send the backup to the S3 bucket<\/em><\/strong><\/p>\n\n\n\n

        aws s3 cp ${LOCAL_DIR}\/${db}-${DATE_FORMAT}.sql.gz ${REMOTE_DIR}\/${DATE_FORMAT}\/<\/em><\/strong><\/p>\n\n\n\n

done<\/em><\/strong><\/p>\n\n\n\n

# Removing backups after their retention period runs out<\/em><\/strong><\/p>\n\n\n\n

DBDELDATE=`date +”${DATE_FORMAT}” –date=”${BACKUP_RETAIN_DAYS} days ago”`<\/em><\/strong><\/p>\n\n\n\n

if [ ! -z ${LOCAL_BACKUP_DIR} ]; then<\/em><\/strong><\/p>\n\n\n\n

cd ${LOCAL_BACKUP_DIR}<\/em><\/strong><\/p>\n\n\n\n

if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then<\/em><\/strong><\/p>\n\n\n\n

rm -rf ${DBDELDATE}<\/em><\/strong><\/p>\n\n\n\n

fi<\/em><\/strong><\/p>\n\n\n\n

fi<\/em><\/strong><\/p>\n\n\n\n

## end<\/em><\/strong><\/p>\n\n\n\n

From here on, the steps are the same as the ones described in the previous section: you add execution permissions to the SH file, test it to make sure it works, and add it as a cron job.<\/p>\n\n\n\n

Restoring a MySQL Database<\/strong><\/strong><\/h2>\n\n\n\n

Once again, you have several methods to choose from, and which one you’ll pick depends on many things, including your hosting platform<\/strong>, your technical skills, and your needs.<\/p>\n\n\n\n

How to restore\u00a0 MySQL databases via the command line?<\/strong><\/strong><\/h3>\n\n\n\n

To restore a database from the command line, you first need to upload the backup file<\/strong> to your hosting account. The easiest way is via an FTP client or from your control panel’s File Manager.<\/p>\n\n\n\n

You can place it in any folder you want, but to keep the commands simple, it’s probably best to save it in the home directory<\/strong>. After you restore the database, you can delete the backup file to free up some storage.<\/p>\n\n\n\n

Next, create a new empty database<\/a> and a MySQL user account with access to it. You can also use an account with SYSTEM_USER privileges.<\/p>\n\n\n\n

Connect to your hosting account via SSH and use the following command:<\/strong><\/strong><\/p>\n\n\n\n

$ mysql -u [your user account’s username] -p [the name of the database] < [the name of the backup file]<\/em><\/em><\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

In the above example, user account test_user <\/strong>is restoring data into database test_database2<\/strong> from the file backup.sql<\/strong>. There are a few optional parameters with various functions, which are explained in detail in MySQL’s documentation.<\/p>\n\n\n\n

Restoring a database with phpMyAdmin<\/strong><\/strong><\/h3>\n\n\n\n

You can use phpMyAdmin’s import feature to recover data from SQL files<\/strong>. You need to remember that there’s often a limit on how big the backup file can be, so if you have a larger database, you’ll probably need to use one of the other methods.<\/p>\n\n\n\n

To import data into an existing database, open phpMyAdmin and select the database you want to restore from the menu on the left. If the database doesn’t exist, you’ll need to create it first.<\/p>\n\n\n\n

Go to the Import <\/strong>tab, click Choose File<\/strong>, and select the SQL backup from your computer.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

The most important option on this screen is the Format<\/strong> drop-down. You have to ensure it’s set to SQL<\/strong>. The default configuration will likely work fine for most website owners. After you click Go<\/strong>, phpMyAdmin will update the selected database with the information from the backup file.<\/p>\n\n\n\n

Restoring a database with MySQL Workbench<\/strong><\/strong><\/h3>\n\n\n\n

Like the other utilities we’re discussing in today’s tutorial, MySQL Workbench<\/strong> has both export and import capabilities. To restore a database, launch MySQL Workbench and open a connection to your server. In the Navigator <\/strong>section, select the Administration <\/strong>tab and click Data Import\/Restore<\/strong>.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

Click the Import from Self-Contained File<\/strong> radio button, and select the SQL backup file from your computer.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

Finally, click Start Import<\/strong> to restore the database.<\/p>\n\n\n\n

Restoring a database with SPanel<\/strong><\/strong><\/h3>\n\n\n\n

If you have an SPanel server, you can restore your database from an SQL backup file <\/strong>using any of the methods we’ve described so far. However, you can also retrieve the data from one of the automatic daily backups SPanel generates.<\/strong><\/p>\n\n\n\n

When setting up your new SPanel server at ScalaHosting<\/strong>, you can choose how many backups you want to have stored at any given time. By default, you have a daily backup that is kept for 24 hours. However, we also have three- and seven-day options.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

The backups are accessible via the Restore backup <\/strong>section on the homepage of SPanel’s User Interface.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

First, choose the date of the backup you want to restore and click Browse Databases<\/strong> to see the dumped databases from the selected day.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

Find the database you need and open the Restore <\/strong>drop-down menu.<\/p>\n\n\n\n

\"How<\/figure>\n\n\n\n

You have three options:<\/strong><\/p>\n\n\n\n