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


Vui lòng nhập bình luận của bạn
Vui lòng nhập tên của bạn ở đây