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
# 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
#!/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
