A quick tutorial on how to backup and recover MySQL Database using Mysqldump/MySQL command and workbench tool.
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.
– 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
– 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.
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
– 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.
– Backup full single database first
– Testing restore function by dropping database beforehand
– Create database again (can use different name for restoring) and then restore:
– First, backup multiple databases with option –databases:
– Drop 2 databases create again and restore
– 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