SQL Query to Backup a Database in MySQL

If often need to migrate our database from one system to anther. Or may need to backup a database so that later, in case of some failure, we can restore the backup copy. So we must know a quick command to backup a database.

SQL query is not used to backup the databases. But there comes a utility name mysqldump, it is located the bin/ folder of your MySQL installation directory. If you have added the bin/ path in system's path variable, you can directly use the mysqldump command to make a backup of database.
Here is the command, assume we want to take backup of university_db database. Open your command prompt and type following:

mysqldump -uroot -p university_db > university_db_backup_file.sql

The detail of the above command is as follows:

mysqldump -> a utility ships with MySQL that is used to back databases

-uroot  -> -u means "user", root means, the "root" user, so want to connect with database using root user. Only authorized users can take backup of databases/

-p -> Indicate, we want to connect using password. It would prompt for the password after you hit enter key.

university_db -> is the name of database whose backup we want to take

university_db_backup_file.sql -> is the name of database backup file where backup with be stored.
after you hit the 'enter' key, it would ask the root password, just provide it. and backup would be taken

Please note, it would backup not only schema but also the data or records that exist in the database. mysqldump command has different options tune the backup procedure, for example to take only schema or only data backup etc.


Popular posts from this blog

Task 12 - Create Java Program with MySQL Database Connectivity

Java Interfaces and Exception Handling - Practice Problem with Solution

Task 16 - Javafx Program to Create, Retrieve, Update and Delete records with Database