Backup and restore mysql database using mysqldump

To backup:

mysqldump -u root -p liveDBname > backup.sql
mysqldump -u root -p liveDBname > backup-$(date +%Y-%m-%d-%H).sql
mysqldump -u root -p liveDBname > backup-$(date +%Y-%m-%d-%H.%M.%S).sql

 

To restore:

mysql -u root -p liveDBname < from_backup.sql

To Import:

mysqlimport -u root -p liveDBname from_backup.sql

Schedule your backup using crontab

sudo mysqldump -u 'username' -p'password' DBNAME > /var/www/auto-backup-sql/liveDB-`date +\%Y-\%m-\%d-\%H\%M`.sql

 

 

Secure MySQL password

Create .my.cnf at your hom directory

sudo nano ~/.my.cnf

Content of .my.cnf

[client]
user="root"
password="root_password"

Change file permission to secure your root password
(Recommended permission 400 or 600)

sudo chmod 600 ~/.my.cnf

Secured mysqldump command

mysqldump --defaults-extra-file=/home/kim/.my.cnf dbname > /var/www/auto-backup-sql/dbname-`date +\%Y-\%m-\%d-\%H\%M`.sql

Automate backup (3am daily)

0 3 * * * mysqldump --defaults-extra-file=/home/kim/.my.cnf dbname > /var/www/auto-backup-sql/dbname-`date +\%Y-\%m-\%d-\%H\%M`.sql

Confirm your cron is working well

sudo grep CRON /var/log/syslog
sudo tail -f /var/log/syslog

Fix your server time for correct log (change timezone on Ubuntu)

sudo dpkg-reconfigure tzdata

 

You may wish to use php to backup

<?php
$toDay = date('Y-m-d_Hi');
$dbhost = "localhost";
$dbuser = "db_user";
$dbpass = "db_password";
$dbname = "database_name";

exec("mysqldump --user=$dbuser --password='$dbpass' --host=$dbhost $dbname > /var/www/backup-sql/vhost/WebBackup-".$toDay."_DB.sql");

echo "Reached the end of script! $toDay";
?>

 

Leave a Comment

Your email address will not be published. Required fields are marked *