How to Backup and Restore MySQL Database

A quick tutorial on how to backup and recover MySQL Database using Mysqldump/MySQL command and workbench tool.

1. Backup with mysqldump:

Mysqldump is a utility used for logical backup. It backups all SQL create, insert statements for restoring objects and data. Mysqldump can also generate CSV files, delimited texts, and XML files for importing data to any database management tool such as SQL Server, Oracle, PostgreSQL, etc.

You can use the backup of all physical data functions so that files in the original format can be restored more quickly.

Backup single database:

– Show all databases on your server:

– Use this command to backup single database to dump file (.sql) with mysqldump

– If you do not want  to type in your password when running mysqldump command (when running crontab job for schedule backup), create configure file include username/password in <user home_folder>/.my.cnf (hidden file) and grant permission as 600 as below:

– Then run mysqldump without using a password for the backup

Backup multiple database with mysqldump:

– This can backup multiple databases with just one single mysqldump command. You can run it by using the –databases option to specify which database you want to backup into single dump files.

Backup all databases to separate dump files with timestamp:

If you want to backup all databases into separate dump files, each with a specific filename, you can write a simple script as below to use for crontab.

– Include username and password in file <home folder>/.my.cnf as below for run this script

Backup all database into single dump file use mysqldump:

Backup single table in a database:

Backup all Procedure, Triggers and Events in database:

2. Restore with MySQL:

– Always test your backup by restoring it to the development/test environment.

– Backup with scripts using mysqldump command and restore with scripts using MySQL command.

Restore single database with MySQL command:

– Backup full single database first

– Testing restore function by dropping database beforehand

– Create database again (can use different name for restoring) and then restore:

Restore multiple database from single dump file:

– First, backup multiple databases with option –databases:

– Drop 2 databases create again and restore

3. Data Export/Import Use MySQL Workbench tool:

– Use MySQL Workbench connect to MySQL Server and choose menu Data Export

Select which database name you want to backup and location and filename be exported

– You can backup individual tables in one database by selecting table name after selecting database name in menu Data Export