New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
mysql backup
sorry if this is a simple problem. but how do you backup a mysql database to another machine like rsync does on normal files?
my sites are in wordpress only, and have one vps as the webserver and another as backup. the purpose of the backup vps is to switch to that as the webserver, when the original one got problems. so i am thinking rsync the webfiles, but i don't know how to deal with mysql. i only know dumping it to sql.tgz and automatically transfer via scp, then uncompress and restore via mysql client.
Comments
http://sourceforge.net/projects/automysqlbackup/
Run it from any host
Ditto to Tim's solution. Automysqlbackup is a very handy script, and can be run from any machine/VM with remote SQL access. Fairly easy to setup and run, and if you wish you can also write pre- and post- scripts to toss into the mix for extra functionality.
is it possible to just compress the /var/mysql (not sure if this is the folder where mysql put the live files) and uncompress to the other machine? or will that blow up? or does automysqlbackup already do that?
To do so safely, you'd really need to halt the SQLd. What automysqlbackup does is a literal mysqldump, compresses (tar.gz as default, I believe), keeps daily/weekly/monthly rotating backups, and emails you the results (and any errors encountered) every time it runs.
This is the code i used for backup the mysql :
Edited : sorry.. was in rush before went to work
@ErawanArifNugroho - wrap your code in <pre>pre tags</pre>, especially if you have a line starting with #. Greatly improves readability
@Aldryic i need to halt the mysql on the vps i am backing up also, or just the one i am restoring to?
I strongly recommend against this method because of the potential for disaster. If you're wanting regular backups, a script like automysqlbackup (or writing your own
mysqldump -hscript) is the best way to go.If you're needing immediate, live replication though, there's other ways to go about that.
got it. but another question. is there a way to backup and restore without a .sql file? for my other project (not wordpress) where restoring from a .sql backup takes 5minutes already.
because whenever there are production bugs, i take a dump of mysql and restore it at home. but while restoring, i can have coffee and snack before it can finish.
The file is just raw text, the
.sqlextension is only for convenience. (just random info :P)I really can't think of any method that would be any faster, to be honest.
thanks man!
btw, for the live replication. can i make master slave configuration, but from vps of different providers? same country (US) but different state, though. db updates are not frequent. maybe several post a week, and some few comments here and there.
Hrm. To be honest, I haven't tried on VMs that weren't behind the same router. @Francisco , any thoughts on this?
Should be fine. Sending the data across can lock the whole table depending on the schema though. enable the binary log and replay it on the backup server. This works with rsync and for something that size won't be muchoverhead at all
It should be fine since it's over basic TCP/IP, not multicast
Francisco
@exussum thanks, i'll research on this.
Basically, I run a daily backup script to backup my database to FTP provided by BuyVM. Here is my script for your reference:
Ops, having problem paste the script here. Moved to pastebin.
http://pastebin.com/5Wav2YQW
Absolutely, I run PowerDNS with mysql replication, the master in on my LA nodes, and there is a slave on my Charlotte nodes and then a RocketVPS
I think if I recall correctly zAmanda did live migration
@miTgiB what about @exussum comment that it may lock?
Its locked for the time ot needs to. MyiSam uses table level locking. And innodb uses row level. If the link is slow its going to be locked longer
Well, you will never know how it works for you til you try it.
http://forums.kayako.com/threads/cron-job-to-backup-kayako-owned-db.8202/#post-40471
im scared now to try. ill just zip then and scp.
btw, i also wish there is a way i can version control my backup. because if i only keep the latest, what if the source server got whacked and lost data. then script sends compromised data to the backup server. if i version control, i can go back at any point in time i wish and restore from there.
You might have misunderstood mysql locking. It simply means that you can't write on the table/row while performing the dump. It's safer than you think. What you're trying to do (zipping and scping mysql files directly) the sql files is definitely worse. If you're using InnoDB, you can even dump w/o locking the table at all using
--single-transactionoption on mysqldump. And never be scared to try and break things in the process, that's what LEB's are forYes you can, one simple way is to just append the date&time to the name of dump before sending to the backup server.
If you need good backup (including hotbackups) solution for MySQL and MySQL based dbs (like MariaDB, Percona etc.) use Percona XtraBackup (http://www.percona.com/software/percona-xtrabackup/). And yes it's free
Manual but Hot method
Backup:
Restore:
That worked in my Debian/Ubuntu, I believe in Centos as well.
You are very lucky you did not crash countless tables.
lol
I must note this was done with either Mysql server not running, or in a non-active website where the database isn't accessed often.
this is what i am thinking but seems risky
Well, doing nothing is even worse than locking the tables for a few seconds while you do a proper backup