Different Ways to Back Up MySQL Databases and Tables

The article provides an overview of the backup types available in MySQL and describes how-to examples of using the mysqldump command-line utility to take a backup of the database, tables, data, or schema and to restore the MySQL database.

In addition, you can view how to generate a database backup with MySQL Workbench and how simple and quick it is to perform the same task with the MySQL dump tool available in dbForge Studio for MySQL.

Contents

• Types of Backup in MySQL
• Back up a MySQL database using the mysqldump command
  • mysqldump to back up only data
  • mysqldump to back up only structure
  • mysqldump to back up all tables in the database
  • mysqldump to back up a single table
• MySQL Workbench to back up a database
• How to back up a database using dbForge Studio for MySQL
• Restore the database in MySQL from the file

Types of Backup in MySQL

In MySQL, there are different types of backup. Which backup strategy to choose depends on several factors, for example, data size, the hardware you use, performance you want to achieve, storage capacity of your database, etc. In addition, you should take into consideration how much time it will take to restore the backup.

MySQL supports the following backup types:

• Logical backup outputs a database structure in a .sql file by generating the CREATE or INSERT statements. Later, this file can be restored with the help of the mysqldump utility. This type backs up only data without indexes, thus, has a small size. However, its recovery option is slower compared with its alternatives, because it should execute all statements one by one.
• Physical backup copies database files in the same format, in which they are stored on the disk. It is faster than the logical type but can be restored only to the MySQL server from the same database engine.
• Consistent backup copies the files at the exact moment of time – only after the server stops running or is locked.

If there may happen some interruptions during a copying operation, backups can be divided into the following types:

• Cold backup blocks access to data during the backup and does not allow making any changes to data. It is simple, fast, and does not impact performance.
• Hot backup copies files while the database is running. Users can read and manipulate data.
• Warm backup makes a database copy while it is running. Users can read but cannot write data during the backup.

Whether data should be fully or partially copied, backups can be classified into:

• Full backup copies all the data from the database. It can be logical or physical. The full backup can be restored on another server.
• Differential backup copies all the changes that were made since the latest full backup. The differential backup can be restored only after the full backup was restored.
• Incremental backup copies all the data changes made since the previous backup (either full or differential).

Back up a MySQL database using the mysqldump command

To back up a MySQL database, you can use either third-party tools or execute the mysqldump command from the command line.

mysqldump is a command-line utility used to generate a MySQL logical database backup. It creates a single .sql file that contains a set of SQL statements. This file can be used to create tables, objects, and/or insert data that were copied from the database. With the utility, you can dump tables, one or several databases, or the entire server with all databases and tables along with their objects or migrate them to another server.

In addition, the mysqldump utility can output the dump to the CSV or XML file formats. It should be noted that mysqldump cannot dump a database or data to separate .sql files.

To restore the database, the utility executes all SQL statements to recreate the tables and populate them with data that, in turn, requires a lot of time.

The basic syntax of the mysqldump command includes the following parameters:

mysqldump -u [user name] –p [password] -h [host name] [options] [database_name] [tablename] > [dumpfilename.sql]

• -u (or --user=[username]) is a username to connect to a MySQL server.
• -p is a password for the username you use to connect to the server.
• -h ( --host=[hostname] ) is the name of the server you want to connect to dump data from.
• options are additional parameters to configure the backup.
• database_name is the name of the database you want to back up.
• tablename is the table name that you want to back up.
• < or > refers to parameters indicating the process of database backup (>) or restore (<).
• dumpfilename.sql is a path to a dump file containing the database backup.

Note: As a rule, mysqldump is located in the MySQL Server installation directory. For example, for MySQL 8.0, the path to the directory is C:\Program Files\MySQL\MySQL Server 8.0\bin. Thus, we recommend adding this directory to the mysqldump command.

Next, let’s take a closer look at the examples describing how to take a backup of the MySQL database using the mysqldump command-line utility.

For demo purposes, we are going to connect to a remote server dbfmylast. That’s why we will add some additional information to the mysqldump command, such as --host and --port parameters.

Note: See Backing up all MySQL server databases or only the required ones for more information.

mysqldump to back up only data

If you want to take a backup of data without the database structure, execute the command with the –no-create-info parameter. For example, dump data for the sakila database.

mysqldump --host=dbfmylast --user=root --port=3306 -p --no-create-info sakila > D:\backup_data_only.sql

The file will output the script containing only dumping table data:

mysqldump to back up only structure

There may be cases when you need to export the database or table structure without data. To get that done, run the command with the -no-data parameter.

mysqldump --host=dbfmylast --user=root --port=3306 -p --no-data sakila > D:\backup_database_structure_only.sql

The mysqldump command outputs only table structure to the file.

mysqldump to back up all tables in the database

The mysqldump command can generate a backup of all or specific tables in the database by adding the selected table names to the command. Keep in mind that the names of the tables should be separated by a space. For example, dump the category, city, and country tables by running the following command:

mysqldump --host=dbfmylast --user=root --port=3306 -p sakila city country category > D:\backup_selected_tables.sql

The output is as follows:

mysqldump to back up a single table

To take a backup of a single table in the database, indicate the name of the specific table in the mysqldump command. For example, dump the inventory table from the sakila database.

mysqldump --host=dbfmylast --user=root --port=3306 -p sakila inventory > D:\backup_single_table.sql

The backup script outputs structure and data only for the inventory table:

MySQL Workbench to back up a database

In this part, we are going to examine how to make a database backup using the MySQL Workbench tool.

To create a database backup, do the following:

1. On the Administration panel, click Data Export. The Administration – Data Export document opens.

2. On the Object Select > Tables to Export tab, select the sakila schema.

3. Under Export Options, select Export to Dump Project Folder if you want database tables to be stored to separate .sql files or Export to Self-Contained File to store the database dump in a single .sql file.

4. To create a backup file, click Start Export. On the progress bar, you can view that the data was exported to the specified file.

Note: With MySQL Workbench, it is not possible to automate or schedule a database backup.

How to back up a database using dbForge Studio for MySQL

Now, let’s see how easy and fast it is to create a database backup using dbForge Studio for MySQL. It is a versatile MySQL GUI tool for developing, managing, and administering MariaDB and MySQL databases. This IDE can increase productivity, optimize query performance, efficiently compare and synchronize databases, automate and schedule database backups.

With the MySQL backup tool, built into dbForge Studio for MySQL, you can back up and restore MySQL databases in the most efficient and quickest way.

To create a backup for the database, open the tool and connect to the MySQL server. In Database Explorer, right-click the database you want to back up and select Backup and Restore > Backup Database.

In the Database Backup Wizard that opens, specify the connection, database, location to store the output file, and name for this file.

On the Backup content tab, select whether you want to back up a database structure or data, or both, and select database objects.

On the Options tab, set additional options that will handle the backup process.

If you want to configure error processing behavior and logging options, switch to the Errors handling tab and set up the options. To launch the database backup, click Backup. After the process is complete, you will see the corresponding notification and can close the wizard.

Wondering how to automate and schedule backups for a MySQL database? Elevate your workflow with the MySQL backup tool in dbForge Studio for MySQL!

Restore the database in MySQL from the file

Now, let’s restore the sakila database from the .sql output file using the mysqldump utility.

First, you need to create an empty database on the target server and restore the database using the mysql command that will generate a schema with data.

mysql --host=dbfmylast --user=root --port=3306 -p sakila < D:\dumps\sakila.sql

where sakila is an empty database that will contain a database structure with data after the import from the sakila.sql backup file.

Note: If there is a database with the same name on the target server, then first, you need to drop it and then create an empty database.

To view the list of tables located in the sakila database, execute the following mysql command:

mysql> use sakila;
...
mysql> show tables;

Conclusion

In the article, we have covered the backup types in MySQL, provided examples of how to take a backup of all databases, data or database structure, and tables, as well as how to restore the database using the mysqldump utility. In addition, we examined how to generate a database backup using MySQL Workbench and dbForge Studio for MySQL. Additionally, if you’re interested in learning how to duplicate tables in MySQL, feel free to read How to Copy Table in MySQL: Duplicate Structure, Data, and Indexes Correctly.

Download a 30-day free trial version to evaluate the features of dbForge Studio for MySQL. After it expires, consider the purchase of the full license of the tool to double your productivity during the MySQL database backup/restore and other database administration operations. Also, you can watch this video tutorial: