Backup And Recovery MySQL Database Using Mysqldump/mysql Command And Workbench Tool

1/ Backup with mysqldump:

-Mysqldump is an utility used for logical backup.It will backup all SQL create, insert statements for re-create objects and data. Mysqldump also can generate csv, delimited text and XML format for import data to any database application such as SQL Server, Oracle, PostgreSQL…

-But for large backup and restore if using backup all physical data file with original format can be restore more quickly.

*Backup single database:

– Show all database in your server:

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

– If you want not type password when run mysqldump command (use when run 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 password (not use option -p) for backup

*Backup multiple database with mysqldump:

Can backup multiple database  with single mysqldumpt command running by using option –databases to specify which database you want to backup into single dump file

 # mysqldump -h <Host> -u <user> -p –databases <db name 1>  < dbname 2>.. > <filename>.sql

*Backup all database to separate dump files with timestamp:

– If you wan backup all database in a mysql server (exclude system database) into separate multiple dump files with filename include database name and timestamp you can write an 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:

mysqldump -host <Host IP> -u <user> -p –all_databases > <filename>.sql

*Backup single table in a database:

# mysqldump -h <Host> -u <user> -p –databases <db name> –tables <<table 1> <table 2> … > <filename>.sql

*Backup all Procedure, Triggers and Events in database:

# mysqldump -h <Host> -u <user> -p –databases <db name> –routines –no-create-info –no-data –no-create-db  > <filename>.sql

2/ Restore with mysql:

– Always test your backup by try restoring to dev/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 by drop database before try restore database

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

# mysql -h <Host> -u <user> -p <db name> < <filename>.sql

*Restore multiple database from single dump file:

 # mysql -h <Host> -u <user> -p < <filename>.sql

First backup multiple databases with option –databases:

– Drop 2 databases create again and restore

3/ Data Export/Import Use MySQL Workbench tool:

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

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

– Can backup individual table in one database by select table name after select database name in menu Data Export


Please enter your comment!
Please enter your name here