Table of contents
1.
Introduction to mysqldump tool
2.
Backing Up databases using mysqldump tool
3.
Restoring from a Dump file
3.1.
Setting up a Database
3.2.
Restoring an SQL dump file
4.
FAQs
5.
Key takeaways
Last Updated: Mar 27, 2024

MySQL Backup And Restore

Author Ankit Kumar
1 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction to mysqldump tool

The mysqldump program allows you to back up one or more databases by creating a text file with SQL statements that We may use to recreate the databases from scratch.
The mysqldump program is found in the MySQL installation directory's root/bin directory.
To use the mysqldump tool, go to the root/bin folder and run the mysqldump command with the arguments listed below.

The following are the most popular mysqldump options:

  1. Add-drop-table 
    Each table in the database has a DROP TABLE statement.
     
  2. Add-locks
    Before and after each INSERT statement, use the LOCK TABLES and UNLOCK   TABLES  statements. It increases the speed of recovering data from a dump file.
     
  3. All-databases
    Creates a backup of the MySQL server's databases.
     
  4. Create-options
    Includes ENGINE and CHARSET options in the CREATE TABLE statement for each table.
     
  5. Databases
    The command creates a dump of one or more databases.
     
  6. Extended-insert
    This option helps speed up data restoration by combining single-row INSERT statements into a single account that inserts numerous table rows.
     
  7. Flush-logs
    Before dumping the data, it flushes the server logs. This is very beneficial when using incremental backups.
     
  8. Lock-tables
    Locking all the tables in a database during the dump ensures that the dump is a consistent snapshot.
     
  9. No-data create 
    A dump file containing only the commands needed to recreate the database structure (only CREATE DATABASE, CREATE TABLE...), not the data (no INSERT  statements).
     
  10. OptBy default, 
    The opt is used by the mysqldump utility. The opt option enables add-drop-table, add-locks, create-options, disable-keys,   extended-insert, lock-tables, quick, and set-charset. Skip-opt is used to disable this option. You can use skip-option name> to skip each specific option. To bypass the disable-keys option, for example, use the skip-disable-keys option.
     
  11. Quick
    This command tells mysqldump not to buffer tables in memory before writing them to the file. This option reduces the time it takes to dump data from large tables.
     
  12. Result-file
    The path to the output dump file is specified.
     
  13. Set-charset
    The command specifies the database's character set, such as latin1 or utf8.
     
  14. Tables
    The table command creates a dump of one or more tables.
     
  15. Where
    Rows that satisfy the condition mentioned in the WHERE clause are dumped. In the next section, let us now see how we can back up databases using the mysqldump tool.

    In the next section, let us now see how we can back up databases using the mysqldump tool.

Backing Up databases using mysqldump tool

Let's look at some instances of how to back up databases with the mysqldump tool:

1. Making BackUp of a single database

Syntax:

mysqldump --user=<username> --password=<password> --result-file=<path_to_backup_file> --databases <database_name>

Where:

  • The user account that will log into the MySQL server is named <username>.
  • The password for the username is password>.
  • The path_to_the_backup_file is specified by a path to the backup file.
  • The —databases option tells the mysqldump utility to dump the databases provided.
  • The database name> specifies the name of the database to be backed up.

The following command, for example, creates a backup of the database dbs1 in the file c:\backup\dbs1.sql:

mysqldump --user=root --password=Supe!rPass1 --result-file=c:\backup\dbs1.sql --databases dbs1


2. Making Back up of multiple databases:

After the —database option, you must enter a list of database names to make a backup of multiple databases:

mysqldump --user=<username> --password=<password> --result-file=<path_to_backup_file> --databases <dbname1>[,<dbname2>, ...]

The following command, for example, creates a backup of the dbs1 and dbs2 databases:

mysqldump --user=root --password=Supe!rPass1 --result-file=c:\backup\dbs1_dbs2.sql --databases dbs1dbs2


3. Backing up all the databases using mysqldump

The –all-database option is used to backup all databases in a MySQL Server:

mysqldump --user=<username> --password=<password> --result-file=<path_to_backup_file> --all-databases

All databases on the current MySQL server will be backed up with the following command:

mysqldump --user=root --password=Supe!rPass1 --result-file=c:\backup\all_databases.sql --all-databases


4. Making a backup of specified tables in a database

The following command is used to make a backup of given tables from a database:

mysqldump --user=<username> --password=<password> --result-file=<path_to_backup_file> <database_name> <table_name>

Multiple tables can also be specified after the database:

mysqldump --user=<username> --password=<password> --result-file=<path_to_backup_file> <database_name> <table1> <table2> <table3>

To make a backup of the table1 from the dbs1 database, the following command is used:

mysqldump --user=root --password=Supe!rPass1 --result-file=c:\backup\table1.sql dbs1table1


5. Back up the database structure using the mysqldump tool:

–n-data option is used to make the backup of the database structure:

mysqldump --user=<username> --password=<password> --result-file=<path_to_backup_file> --no-data --databases <database_name>

The statement will create a dump file with the SQL statements required to recreate the database structure. Also, there are no INSERT statements in the dump file.

mysqldump --user=root --password=Supe!rPass1 

--result-file=c:\backup\classicmodels-ddl.sql --no-data --databases dbs1


6. Backing up of data only using mysqldump

The —no-create-info option is used to make a backup of the database data only:

mysqldump --user=<username> --password=<password> --result-file=<path_to_backup_file> –-no-create-info --databases <database_name>

The statement creates a dump file with the SQL queries needed to lock tables and insert data into them. There are no CREATE TABLE statements in this database.

The command below creates a backup of the data in the dbs1 database:

mysqldump –-user=root --password=Supe!rPass1 --result-file=c:\backup\dbs1-data.sql –-no-create-info --databases dbs1

In this article, you learned how to back up databases in MySQL Server using the mysqldump tool.

Restoring from a Dump file

Further, we will be learning how to restore an SQL dump file using the MySQL tool.

Setting up a Database

Let us create a new database called DBS:

CREATE DATABASE dbs;

Next, using the USE command, use the database:

USE dbs;

After that, create a table in the database named example:

CREATE TABLE example(

           id INT PRIMARY KEY

);

Next, we will be inserting some rows into the table.

INSERT INTO example(id)

VALUES(1),(2),(3);

And finally, using the mysqldump command, we will dump the dbsdatabase:

mysqldump --user=root --password=Supe!rPass1 --result-file=c:\backup\dbs.sql --databases dbs

Restoring an SQL dump file

Follow the following steps to restore the dbs.sql SQL dump file:

First of all, it connects to the MySQL server:

C:\>mysql -u root -p

Enter password: ********

Next, drop the database:

mysql>DROP DATABASE dbs;

After that, load the dump file using the SOURCE command:

mysql>source c:\backup\dbs.sql

The command built the database DBS, selected it, then ran SQL operations. Furthermore, it displayed all conceivable warnings and faults.

When restoring a dump file, it is suggested that you use the SOURCE command because it returns a lot of information, including warnings and problems.

FAQs

1. What are the different forms of MySQL backups?
Physical and logical backups are the two types of backups available. Physical backups (Percona XtraBackup, RDS/LVM Snapshots, MySQL Enterprise Backup) are available. Still, you may also copy the data-dir using the cp or rsync command lines when MySQL is down/stopped.


2. How do you restore a database with the overwrite option from a full backup?
The following command restores the database from the backup file given. It will overwrite the database files if the database already exists. If the database does not exist, it will construct it and restore the files to the backup command's provided location.

RESTORE DATABASE <database_name> FROM DISK <path>

WITH OVERWRITE


3. What is binary backup?
Binary backup is a filesystem-level backup that creates a copy of the database files. A binary backup produces binary files containing all entries, indexes, change logs, and transaction logs. Configuration data is not included in a binary backup.

Key takeaways

Pheww, that was a lengthy article. But wait, you came through. You should be proud.

Let's take a minute out and see what we learned in the article. 

After learning about the mysqldump tool, we looked at some frequent arguments. Then we looked at how to back up a database, numerous databases, and all of the databases on the MySQL server. Then we learned how to back up a database's specified multiple tables. We also learned how to use mysqldump to backup a database schema.

After backing up all of the data, we learned how to use the MySQL tool to restore a SQL file.

You have the required step in improving your knowledge about SQL and MySQL. Do not stop here and check out Coding Ninjas Studio and cover some advanced questions on Top-100-SQL-problems.

Live masterclass