1. Home
  2. MySQL Tutorials
  3. MySQL Backup and Restore

MySQL Backup and Restore

In this article we are going to Perform how to do MySQL Backup and restore operations using mysqldump and mysql tool.

Prerequisites

  • SSH access with sudo privileges
  • MySQL UserName and Password

Backup Single MySQL Database

Use below command to take backup of Single Database.

Syntax:

mysqldump -u username -p database_name > backup_file_name.sql

Example:

Lets take backup of Database name “Employee”.

$ mysqldump -u fossadmin -p Employee > employee_dbbackup.sql

Once you enter above commands it asks MySQL database login password.

Backup MySQL Database with Stored procedures and Triggers

If we want to take MySQL Backup with stored procedures and routines use below command,

Example:

Lets take backup of Database name “Employee” with stored procedure and routines.

$ mysqldump -u fossadmin -p --routines employee > employee_dbbackup.sql

 

Backup Multiple MySQL Databases

To backup multiple MySQL databases use –database option followed by the list of databases to backup and each database name should be separated by space.

Syntax:

mysqldump -u username -p --databases database_name_1 database_name_2 > databases_1_2.sql

Example:

Lets take backup of Database name “Employee and Accounts “ with stored procedure and routines.

$ mysqldump -u fossadmin -p --databases Employee Accounts > emp_accnt_bkp.sql

Backup MySQL All Databases

To take all databases backup one time, use the –all-databases option.

Syntax:

mysqldump -u username -p --all-databases > all_databases.sql

Example:

$ mysqldump -u fossadmin -p --all-databases > all_databases.sql

Backup MySQL Stored procedures and Triggers only.

To take take backup of MySQL Stored procedures and Triggers only, Follow below steps.

Syntax:

mysqldump -u username -p --routines --no-create-info --no-data --no-create-db --skip-opt database_name > backup_filename.sql

Example:

$ mysqldump -u fossadmin -p --routines --no-create-info --no-data --no-create-db --skip-opt Employee > employee_bkp.sql

Restore Single MySQL Database

To restore Single MySQL Database using mysql tool, use below command,

Syntax:

mysql -u username -p database_name < backup_file.sql

Example:

Before restoring database first to create database if not exist in destination MySQL Database Server.

To create database if not exist.

$ mysql -u fossadmin -p -e "create database Employee";

Lets restore “employee_dbbackup.sql” backup into “Employee” Database.

$ mysql -u username -p Employee < employee_dbbackup.sql

To Know more about MySQL Backup follow MySQL Official Guide.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.