Backing up your site’s files is relatively straightforward. In essence, you put them all in an archive, which you can later extract and restore everything. When it comes to generating a backups of your MySQL databases, however, things are a bit more complicated.
Today, we’ll look into the different types of database backups, and we’ll see some of the most popular techniques for creating and restoring a safe copy of your website’s data.
Types of MySQL Backups
MySQL is a relational database management system. The information is stored in tables, with relations between the different datasets. In effect, these relations build the structure of the database.
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. Otherwise, your website wouldn’t work.
There are two main types of database backups in MySQL:
Logical backups
A logical backup exports (or dumps) the data and the data structure to an SQL file. The SQL file itself contains the SQL statements (e.g., CREATE DATABASE, CREATE TABLE, etc.) required to rebuild the database. Because SQL statements tend to be universal, logical backups are often used to move databases from one host to another.
Physical backups
A physical backup is a copy of the database’s datadir directory. All the tables and the data inside them are copied in their original file formats and can be restored in a fully functioning database as long as the structure is maintained.
Physical backups are quicker but can only work if they are restored from the same database engine on the same MySQL version. Hence, they aren’t always suitable for moving a database from one environment to another.
Your backup strategy should be determined by a number of different factors including the hosting setup, the size of the database, and your needs.
Logical backups tend to be more common because, as we’ll find out in a minute, tools that can create them are usually readily available. Furthermore, logical backups can copy portions of the database, giving you more flexibility and a quicker resolution when you’re dealing with data corruption problems in specific parts of the database.
Overall, while physical backups do make sense in a few specific scenarios, for the regular website owner, logical ones are easier to set up and manage. That’s why today’s article will focus on them.
Creating a Database Backup via the Command Line
If you use the command line, you’ll export MySQL databases with the mysqldump utility. 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.
Your first job is to access your hosting account via SSH. Modern operating systems support the protocol out of the box. Windows computers connect to remote servers via PowerShell or the Command prompt tool, and Unix-based systems do it with the Terminal.
Some people prefer dedicated SSH clients like PuTTY, and if you have an SPanel server, you can open a shell directly via the SSH Terminal available on the homepage of the User Interface.
Before you continue, make sure you have access to a MySQL user account with privileges over the database you’re about to back up. If you have access to root or any other account with SYSTEM_USER permissions, you can export data from any database you want.
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 command-line utility to check for errors.
The command looks like this:
$ mysqlcheck [the database’s name] -u [your MySQL user’s username] -p
After you provide your user account’s password, mysqlcheck will scan all tables for data corruption. Ideally, the output will look like this:
To check all databases for errors, add the –all-databases option. Having scanned your databases for errors, you can proceed with the backup.
The mysqldump utility covers quite a few scenarios. Here are the most common ones:
Backing up a single database via the command line.
To back up one of the databases on your account, you need a command that looks like this:
$ mysqldump -u [your MySQL account’s username] -p [the name of the database] > [the name of the file you want to export the data to]
With the above command, the user test_user exports the database test_database to the file backup.sql. All you need to do is enter the user account’s password. If backup.sql doesn’t exist, MySQL will automatically create it.
Backing up specific tables from a selected database
The mysqldump utility can also back up specific tables only. Just add the name of the table after the database name. Here’s an example:
In the screenshot above, test_user is backing up the table wp_posts from test_database2 to the posts.sql file. To dump multiple tables to the same file, simply add their names separated by spaces. For example:
$ mysqldump -u test_user -p test_database2 wp_posts wp_options wp_users wp_links > wordpress.sql
Backing up multiple databases
You can export multiple databases to the same SQL file with mysqldump. To do that, use the command above, adding the –databases option and the names of the databases separated by spaces. For example:
$ mysqldump -u test_user -p –databases test_database test_database2 test_database3 > backup.sql
Backing up all accessible databases
If you want to back up all the databases you have access to, replace the database name with the –all-databases option. For example:
This will dump all databases except information_schema, performance_schema and any other default MySQL schemas. To include them in the backup, add the — skip-lock-tables option.
Backing up a database structure only
Sometimes, you may need to generate a backup of a database’s structure without the data stored in it. The mysqldump utility can do it if you include the –no-data option. Here’s what the command looks like:
Backing up the data without the structure
The reverse is also possible – you can backup the data without the structure. The option is –no-create-info, and the command looks like this:
The mysqldump utility won’t print a message telling you that it has successfully backed up your database, so you can use the ls command to confirm that the SQL file has been created.
Dumping a large database with mysqldump
Using mysqldump on larger databases could be trickier. If you have lots of GBs to back up, you may experience increased server load, and if the database is really big, the entire operation could fail.
The first problem is the amount of storage space the SQL file takes up. Large databases produce large backups, but the good news is that you can pipe mysqldump’s output through gzip and compress the SQL file before writing it on the disk.
The command will looks like this:
$ mysqldump -u [your mysql account’s username] -p [your database’s name] | gzip > [filename].sql.gz
On the one hand, this reduces the size of the generated backup, and on the other, it cuts the IO load.
The mysqldump utility has a couple of other options that may also help. The –opt option encompasses several different command parameters that optimize the dump operation. Bear in mind that they make the backup more difficult to understand by other database systems, so this may not be the best approach if you want to migrate the database to a new host.
The –quick option may also work. By default, mysqldump stores each table row into memory before dumping it into the SQL file. with the –quick option, the data is transferred directly to the backup.
If a large backup operation fails, you can try to increase the max_allowed_packet parameter in MySQL’s configuration file (the my.cnf file usually stored in /etc). This is usually necessary only when you’re trying to shift dozens of gigabytes of information, and it’s not guaranteed to succeed.
Backing up that much data with mysqldump is never going to be easy, and many experts suggest that in such cases, you’re better off using a physical backup solution like MySQL Enterprise Backup or Percona XtraBackup.
Creating a Database Backup With phpMyAdmin
Many shared and managed hosting accounts come with phpMyAdmin preinstalled. Its default login URL is https://[your server’s IP]/phpmyadmin (some hosts change it for security reasons), though it’s often accessible from the control panel, as well.
It has an easy-to-use graphic interface, meaning you don’t need to learn any new commands to manage your database. You can also export data to an SQL file with a few mouse clicks. Here’s how to do it.
Backing up a single database
After you open phpMyAdmin, you’ll see a list of all the databases on your hosting account.
Click on the one you want to back up and go to the Export tab.
You can choose the export method and the format. It’s preferable to keep the SQL format, as this will give you more options when you need to restore the database.
As for the method, Quick is selected by default, and if you leave it like that, phpMyAdmin will dump the entire database (the data and the structure) into the SQL file.
If you select the Custom 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.
Further down, you can have phpMyAdmin automatically rename the database, its tables, and/or columns while exporting them. This is where you specify the filename template as well. By default, it’s set to “@[email protected]” which means that phpMyAdmin will name the SQL file after your database.
You can lock the database’s tables or export them as separate files. Options for the exported file’s encoding and compression 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.
In the Format-specific options section, you decide whether to have comments metadata, and other formatting elements in your SQL file. You can have phpMyAdmin export the database as a transaction, disable foreign key checks, and dump views as tables.
There’s also a drop-down menu letting you make the backup backwards compatible with a number of older systems.
Next, you have the Object creation options section. It mainly deals with the statements that will be added to the SQL file, and they concern the way the SQL file will rebuild the database.
For example, the CREATE DATABASE and USE 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.
If the IF NOT EXISTS option is enabled, your backup will check for matching tables before trying to create them, and with the AUTO_INCREMENT checkbox, you can append the backed up data to existing tables.
In the Data creation options section, you’ll find more settings related to the way your data will be restored. 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.
After you’re done tweaking the settings, click Go to generate the SQL file.
Backing up multiple databases
The steps for backing up multiple databases is 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 tab from the homepage.
If you’d like to back up all your databases at once, set the export method to Quick and the format to SQL, and click Go to have phpMyAdmin generate the backup. If you select the Custom method, you can pick which databases to backup. Click Unselect all and us the mark those you want to back up using the Ctrl/Cmd key.
Further down, in the Format-specific options section, you can decide whether to dump the data from the selected databases, their structure only, or both.
The rest of the settings are the same as the ones you see when you’re exporting a single database.
Creating a Database Backup With MySQL Workbench
MySQL Workbench is another free database management tool. You can set it up on your home computer and control your databases remotely.
You’ll first need to connect to your hosting account. There’s a + button in the MySQL Connections section on the homepage.
The dialog asks you for the type and details of the connection. The best option is to connect to your server using the standard TCP/IP protocol over SSH. This way, the communication will be encrypted, and your data will be safe.
You’ll have to provide the SSH login credentials as well as the ones for your MySQL user account. After you fill in all the required fields, click Test Connection to ensure everything works. If it does, hit OK to save the connection.
After you connect to the server, open the Administration tab in the Navigator section and click Data Export.
In the next window, you’ll see the databases your user has access to and the different export options.
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.
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.
Creating a Database Backup With SPanel
SPanel strives to cover every aspect of modern website building and development. This includes database management, which is why every SPanel server comes with phpMyAdmin preinstalled.
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 section inside SPanel’s User Interface.
To access the User Interface, you can either use the account’s login credentials at https://[the account’s domain name]/spanel/ or log in to your server’s Admin Area and select Manage from the Actions drop-down menu next to your account.
Once you access the MySQL Databases section, you’ll see a list of all the databases on your account. Locate the one you want to back up, open the Actions drop-down menu, and select Export & Download Database.
SPanel will put the data into an SQL file and save it inside the account’s home directory. Then, a popup lets you download the file for local storage and remove it from the server.
As you can see, backing up the database before making any changes to your site is a matter of a couple of clicks. Even if you forget to do it, however, SPanel still has you covered.
Every SPanel customer at ScalaHosting gets daily backups of all the files and databases on their server. The backups are stored in a remote location. They’re not in the same data center as your production site, so even in case of an accident, you have a much better chance of restoring the data.
Speaking of restoring data, let’s see what you need to do to rebuild a broken database from a backup.
Restoring a MySQL Database
Once again, you have several methods to choose from, and which one you’ll pick depends on many things, including your hosting platform, your technical skills, and your needs.
Restoring a database via the command line
To restore a database from the command line, you first need to upload the backup file to your hosting account. The easiest way is via an FTP client or from your control panel’s File Manager.
You can place the SQL file in any folder you want, but to keep the commands simple, it’s probably best to save it in the home directory. After you restore the database, you can delete the backup file to free up space.
Next, create a new empty database and a MySQL user account with access to it. You can also use an account with SYSTEM_USER privileges.
Connect to your hosting account via SSH and enter the following command:
$ mysql -u [your user account’s username] -p [the name of the database] < [the name of the backup file]
In the above example, user account test_user is restoring data into database test_database2 from the file backup.sql.
Restoring a database with phpMyAdmin
You can use phpMyAdmin’s import feature to restore databases from SQL files. You need to remember that there’s often a limit on how big the SQL file can be, so if you have a larger database, you’ll probably need to use one of the other methods.
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.
Go to the Import tab, click Choose File, and select the SQL backup from your computer.
The most important option on this screen is the Format drop-down. You have to ensure it’s set to SQL. The default configuration will likely work fine for most website owners. Click Go to import the data into the database.
Restoring a database with MySQL Workbench
Like the other utilities we’re discussing today, MySQL Workbench has both export and import capabilities. To restore a database, launch MySQL Workbench and open a connection to your server. In the Navigator section, select the Administration tab and click Data Import/Restore.
Click the Import from Self-Contained File radio button, and select the SQL backup file from your computer.
Finally, click Start Import to restore the database.
Restoring a database with SPanel
If you have an SPanel server, you can restore your database from an SQL backup file 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.
When setting up your new SPanel server at ScalaHosting, 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.
The backups are accessible via the Restore backup section on the homepage of SPanel’s User Interface.
First, choose the date of the backup you want to restore and click Browse Databases to see the databases backed up on the selected day.
Find the database you need and open the Restore drop-down menu.
You have three options:
- Download an SQL file with a backup of the database.
- Restore the original database.
- Restore the data in another database. SPanel will open a dialog requesting the name of the database you’d like to use. You can restore the data in an existing database or enter the name of a new one. SPanel will automatically set it up for you.
Potential Problems While Trying to Backup a MySQL Database
The export process can fail and result in an error for a couple of different reasons. Luckily, troubleshooting the problem is usually pretty straightforward. Let’s have a look at some of them.
The MySQL Service isn’t working
First, you need to make sure the MySQL server is running on your hosting account. You can do it over SSH with the following command:
$ service mysql status
If you use SPanel, you can also check whether MySQL is running from the Admin Interface. The list of essential service is available in the Server Status menu, and if the MariaDB Database Server is down for some reason, you can bring it back online from the Restart Service section.
There are corrupted tables in your database
As we mentioned already, you can use the mysqlcheck command-line utility to see whether there are any corrupted tables in your database. It can also fix errors in case it fails because of a broken row or table.
All you need is the -r option:
$ mysqlcheck [the database’s name] -u [the user account’s username] -p -r
Errors can also be addressed via phpMyAdmin and SPanel.
In phpMyAdmin, pick the database from the menu on the left, and you will be redirected straight to the Structure tab, where you’ll see a list of all the database’s tables. Use the checkboxes next to the tables you want to repair (if you’re not sure where the errors are, you can use the Check all option at the bottom) and pick Repair table from the With selected menu.
With SPanel, the process is even simpler. Log in to the account’s User Interface and go to MySQL Databases. Scroll down to the list of databases, find the one you want and open the Actions drop-down menu next to it. After you select Repair database, SPanel will locate and fix any errors that might be present in the database’s tables.
Not enough disk space
The backup is generated on the server, so it inevitably takes up some storage space. If you exceed your account’s capacity while you’re backing up the database, the process will fail.
Your first option is to pipe the backup through gzip. This will reduce its size and may be enough to let you retrieve the SQL file in an archive. However, you should also think about deleting any unnecessary information from your account, optimizing your database, and possibly upgrading your account, so you can have more storage space.
