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";
?>