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.