How to Backup and Restore MySQL Database with/without Mysqldump?

A database is a systematic collection of structured information stored on a computer, particularly one that can be accessed in several ways. Using a system with a database brings organizations a lot of benefits. It separates metadata from data applications so the programs remain unchangeable while the company transfers the data to another system; it leaves minimal data redundancy in one logical structure while improving data sharing among members, and the data is secured with DBMS (database management system) controlling security through privileges.

MySQL is one of the best databases on the market. Since the database has its weaknesses in backup and recovery, to solve the problems, MySQL develops Mysqldump, a database backup program that produces logical backups composed of SQL statements that can be backed up or transferred to another server. The command can produce XML, delimited text, or CSV format.

In this article, I will introduce 2 ways for MySQL database backup and recovery with/without the Mysqldump command-line utility, so you can figure out what works best for your business.

Backup and Restore MySQL with Mysqldump

Mysqldump comes with a wide range of capabilities and choices. The full list of choices is available in MySQL documentation. This tool can also be used to backup and restore MariaDB. I'll discuss some of the fundamental components. The syntax and basic parameters for the Mysqldump utility are listed below.

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

-u [user_name]: The username to access the MySQL server.

-p [password]: The user's authorized password for MySQL.

-h [hostname]: The name of the server that you wish to access in order to dump data from.

option: The configuration setting to customize the backup.

database name: Database name for which you wish to create a backup.

table name: You can include names in the command if you wish to back up certain tables.

<or >: You can create a backup using“>”and and restore it with“<”.

dumpfilename.sql: A dump file path that contains the database backup.

Note: Recommend adding the specific Mysqldump path to the command for operation, such as C:\Program Files\MySQL\MySQL Server 7.0\bin.

Single MySQL database backup: 

This command will create a single dump file called dawn_20220913.sql that contains data and the structure of the database.

Mysqldump -u root -p dawn > C:\MySQLBackup\dawn_20220913.sql

Multiple or all MySQL database backups:

Add “-databases” options for multiple databases:

Mysqldump -u root -p --databases dawn1 dawn2 > C:\MySQLBackup\dawn1_dawn2_20220913.sql

Use the “--all-databases” option for all databases:

Mysqldump -u root -p --all-databases > C:\MySQLBackup\all_databases_20220913.sql

MySQL database structure backup:

Use the “–no-data” option in the command. 

Mysqldump -u root -p --no-data dawn > C:\MySQLBackup\dawn_structure_only_20220913.sql

MySQL database data backup only:

Use the “–no-create-info” option in the command.

Mysqldump -u root -p dawn --no-create-info > C:\MySQLBackup\dawn_data_only_20220913.sql

MySQL specific or all table backup:

Add the specific or all table names with spaces after the database.

Mysqldump -u root -p dawn town payment > C:\MySQLBackup\town_payment_table_20220913.sql

Restore MySQL database:

Create an empty database and restore a backup via the following command (make sure it is mysql instead of Mysqldump):

mysql -u root -p dawn < C:\MySQLBackup\dawn_20220913.sql

Restore a table in the MySQL database:

1.Create a dummy database and restore the backup of the database on it.

mysql> create database dawn_dummy;

mysql> use dawn_dummy;

mysql> source C:\MySQLBackup\dawn_20220913.sql

2.Back up the “town” table to the file.

mysql> source C:\MySQLBackup\dawn_dummy_town_20220913.sq

3.Restore the “town” table from the file.

mysql> source C:\MySQLBackup\dawn_dummy_town_20220913.sq

4.Verify whether it succeeds.

mysql> use dawn;

Database changed

mysql> show tables;

Backup and Restore MySQL without Mysqldump

We can try another way out without Mysqldump with Vinchin Backup & Recovery, the more efficient and convenient backup and restore tool for MySQL, Oracle DB, SQL Server, MariaDB, Postgres Pro, and PostgreSQL in physical and virtual machines as well.

With the solution, you can automate the MySQL backup process with backup schedules and email alerts under preset circumstances; while saving more storage after the data is duplicated and compressed.

Care for a free test of Vinchin Backup & Recovery without complicated command lines and explore more about its possibilities? Click the below button to download the 60-day full-featured free trial now.

Follow the simple 3-step guide, you are good for easy MySQL backup and recovery.

1. Specify the backup source.

This backup source list will display all hosts that have valid Vinchin database agents.

2. Designate the backup destination.

3. Configure backup strategies.

Advanced options such as backup schedules, data reduction, retention policy, etc. can be set up here.

Lastly, review and submit the job, and the backup will run at the set time. 

The MySQL database recovery is just as simple as that of backup, and you can choose to override the original database or create a new one to restore. Additionally, all of the tasks could be centrally managed via a web-based console.

Except for that, Vinchin Backup & Recovery is also accomplished in virtual machine fast and effortless backup with CBT/SpeedKit, SAN/HotAdd, and other transmission modes, backup schedules, etc; cross-platform V2V migration for 10+ virtualization; and multiple recovery choices such as 15s instant recovery and file-level recovery.

Conclusion

Mysqldump is a backup program developed by MySQL and can be used to backup and restore MySQL and MariaDB, a replacement from the same company. But a command-line tool isn't that convenient to use especially when there are so many confusing lines here.

Consider a professional thrid-party backup solution like Vinchin Backup & Recovery with various backup strategies and options to simplify everything for you.