MySQL Backup And Restore Database (Command Line)

December 16, 2017

Backup MySQL Database.

mysqldump -u [user] -p [database] > [sql_file]

Restore MySQL Database.

mysql -u [user] -p [database] < [sql_file]

Restore MySQL Database in MySQL prompt.

mysql -u [user] -p
use [database];
source [sql_file];
# For faster performance, turn of `autocommit`.
SET autocommit=0; source [sql_file]; COMMIT; SET autocommit=1;

Script to backup multiple databases daily

#!/bin/bash
timestamp=`date +"%Y%m%d"`

# backup mysql
db_user="root"
db_pass="secret"
databases=("db_1" "db_2")
for database in ${databases[@]}
do
 echo "backup mysql ${database}"
 backup_output="/backup/${timestamp}-${database}.sql"
 # repair database
 mysqlcheck -ap --auto-repair -u $db_user -p$db_pass $database 
 # backup
 mysqldump -u $db_user -p$db_pass $database > $backup_output 
 # gzip
 gzip -f $backup_output 
done

The following script can remove databases backup older then N days

echo "remove files older than 3 days"
find /backup/* -mtime +3 -exec rm -f {} \;
This work is licensed under a
Creative Commons Attribution-NonCommercial 4.0 International License.