How to do continuous sql db backup to cloud ?
Everything is going fine apart from the fear of "What if we get hacked"
While all the magento files are backedup and stored off server in my google drive. But its the sql db which matters.
How do professionals backup sql databases ? How regular ? What would be the ideal solution to back up sql with complete peace of mind that if something goes wrong, we would have the latest database back up readily available ?
I am pretty new with server administration and still learning, so please be polite
The speed of recovery needs to be tested to ensure its within your business requirements. Also see Percona XtraBackup.
A combination of the regular point in time mysqldump/xtrabackup and the mysql bin logs will be needed to restore the database to the point before the hack. Restoring after that may be a little fiddly. Row replication format will help.
Of course I've assumed mysql and if using postgresql or sqlite your path may be different.
Still, we probably ought to do it more frequently. Reconstructing registration records from paypal receipts is not fun.
So you don't even need a FLUSH TABLES WITH READ LOCK.
We also use the Linode backup service for added redundancy.
I use the automysqlbackup program to backup to a folder in my home directory and then I simply upload that onto Google Drive myself and I also rsync it to a backup VPS.
I also use a script like you mentioned and that is mysqldumper which also make automatic database backup at home folder of your website.