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
|
1 |
# mysqldump -h <Host> -u <username> -p <database name> > <filename>.sql |
– 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
#!/usr/bin/bash backup_dir=/backup_db/course current_time=$(date '+%d%m%Y_%H%M') for i in `mysql -h 127.0.0.1 -u root -Ns -e "show databases"` do if ! [[ $i =~ "information_schema" ]] && \ ! [[ $i =~ "mysql" ]] && \ ! [[ $i =~ "performance_schema" ]] && \ ! [[ $i =~ "sys" ]];then filename=$backup_dir"/"$i"_db_"$current_time".sql" echo $filename mysqldump -h 127.0.0.1 -u root $i > $filename fi done |
– 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
