MySQL Backup And Restore Database (Command Line)

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

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

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;

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

echo "remove files older than 3 days"
find /backup/* -mtime +3 -exec rm -f {} \;