How to Backup & Restore MySQL Database with MySQL Backup

With over 5 million users and 35,000 daily downloads, MySQL is an established and popular database solution.

Like every other app, service or technology, backups are an essential aspect of MySQL database administration too.

A MySQL database backup will save all the data in a particular database. This can be handy for backing up valuable script data; however, it is just a partial backup.

DNS, home directory files, and other system files and settings will not be included.

According to statistics, 7% of MySQL customers are from India, 7% are from the United Kingdom, and 42% are from the United States.

It is critical to create frequent MySQL database backups in order to restore data and rerun applications if difficulties arise.

Among these issues are:

• The system has crashed.
• Hardware malfunctions
• The mysql database has been corrupted.
• User mistake results in data erasure.

The MySQL database provides several techniques for creating backups and restoring them with MySQL databases. Any approach that best meets the backup and disaster recovery needs can be used.

What exactly is MySQL?

It’s sometimes pronounced “my sequel,” and it’s named after a Swedish firm called MySQL AB, which created the open-source MySQL back in 1994. “MY-ES-KYOO-EL” is the right pronunciation.

MySQL database is open source, despite the fact that it appears to be closed at times.

Understanding MySQL Database Backup and Restore

1# Backup a MySQL Database from the Command Line Using mysqldump

The mysqldump utility is a useful tool for backing up MySQL databases and transferring them to another MySQL server.

It is a collection of SQL statements that are used to reconstruct the original MySQL database.

To rebuild the MySQL database, the mysqldump program may make a backup of the database as a single.sql file that includes SQL statements like DROP TABLE, CREATE TABLE, and INSERT.

The application also facilitates the conversion of numerous MySQL databases or their objects into MySQL database tables in a single file.

The mysqldump software cannot backup MySQL databases or data to separate.sql files. Furthermore, it does not dump the performance schema, information schema, or NDB Cluster information database (ndbinfo).

Consider the syntax of the mysqldump command given below.

mysqldump -u [database_username] –p [database_password] [database_name] [tablename] > [database_backup_file.sql]

 

2# Creating a Backup of a Single MySQL Database

One can use the following command syntax to backup a single MySQL database:

When one enters this command, one will be prompted for a password.

mysqldump -u database_username -p database_name > database_backup_file.sql

The command’s output is shown below.

$ mysqldump  -u a056c59a_f56757  -p a056c59a_f56757 > a056c59a_f56757.sql

Enter password: 

$ ll

-rw-rw-r–   1 a056c59a a056c59a 53886 Mar 25 17:16 a056c59a_f56757.sql

One may bypass the password prompt by using the —password option. This command’s syntax is as follows. Replace the MySQL database password with the password for your database.

mysqldump -u database_username –password=”database_password” database_name > database_backup_file.sql

Here is the result.

$ mysqldump -u a056c59a_f56757 –password=”DB_password” a056c59a_f56757 > a056c59a_f56757.sql 

$ ll

-rw-rw-r–  1 a056c59a a056c59a 53886 Mar 25 17:33 a056c59a_f56757.sql

3# Multiple MySQL databases should be backed up.

One may use the following command syntax to backup several MySQL databases.

mysqldump -u database_username -p –databases database_name1 database_name2 > database_backup_file.sql

This is the result of performing this command.

$ mysqldump -u a056c59a_f56757 -p –databases a056c59a_f56757 a056c59a_g67868 > a056c59a_newdb.sql

Enter password: 

$ ll

-rw-rw-r–   1 a056c59a a056c59a 54159 Mar 27 03:11 a056c59a_newdb.sql

To bypass the password prompt, use the —password option once more. Replace the MySQL database password with the password for your database.

mysqldump -u database_username –password=”database_password” –databases database_name1 database_name2  > database_backup_file.sql

The command’s output is shown below.

$ mysqldump -u a056c59a_f56757 –password=”DB_password” –databases a056c59a_f56757 a056c59a_g67868 > a056c59a_newdb2.sql 

$ ll

-rw-rw-r–  1 a056c59a a056c59a 54159 Mar 27 03:19 a056c59a_newdb2.sql

4# Back up all MySQL databases.

Use the following command syntax to backup all MySQL databases or MariaDB databases at once.

mysqldump -u database_username -p –all-databases > all_databases_backup_file.sql

Here is the result.

$ mysqldump -u a056c59a_f56757 -p –all-databases > a056c59a_newdb3.sql

Enter password: 

$ ll

-rw-rw-r–  1 a056c59a a056c59a 54144 Mar 27 03:38 a056c59a_newdb3.sql

To skip the password prompt, the —password option is used here. As always, replace the database_password part with the password for the MySQL database.

mysqldump –all-databases -u database_username –password=”database_password” > all_databases_backup_file.sql

This command’s output is shown below.

$ mysqldump –all-databases -u a056c59a_f56757 –password=”DB_password” > a056c59a_newdb4.sql

$ ll

-rw-rw-r–  1 a056c59a a056c59a 54144 Mar 27 03:41 a056c59a_newdb4.sql

Using mysqldump, one may restore a MySQL database.

1# MySQL Database Restore

The MySQL utility may be used to restore a MySQL database from a MySQL download and backup. The following is the command syntax.

mysql -u  database_username  database_name -p < database_backup_file.sql

The end result will be as follows.

$ mysql -u a056c59a_f56757 a056c59a_f56757 -p <a056c59a_f56757-20220329.sql

Enter password: 

$

2# Using a Full MySQL Dump File, Restore a Single MySQL Database.

If users wish to restore a single MySQL database from a mysql download backup file that contains several MySQL databases, use the —one-database option in the command.

mysql -u database_username –one-database database_name1 -p < all_databases_backup_file.sql

Here is the result.

$ mysql -u a056c59a_f56757 –one-database a056c59a_f56757 -p < a056c59a_full_db.sql

Enter password: 

$

3# One Command to Export and Import a MySQL Database

The following one-line command may be used to produce a dump file from one MySQL database and import it into another MySQL database.

This example sends the output to a remote MySQL database client, which imports it into the database.

Replace the remote database name with the name of the preferred remote host database.

mysqldump -u database_username database_name -p | mysql -h remote_host -u remote_database_username remote_database_name -p

The methods above should be followed if one needs assistance backing up or restoring the MySQL database.

If one wants help setting up a dedicated server for its business, Exabytes offers dedicated server hosting to help businesses maximize performance.

Talk to our consultant to clear all the doubts related to dedicated server hosting. To book an appointment, contact us now.

Related articles:

Tips to Avoid 6 Frequent Mistakes Database Development

The Differences Between MS SQL Web vs. SQL Standard Edition