I am sure this has been talked about to death but here we go again I need a mysql backup tool but I think most tools out there are junk. On larger tables, selects can often lock the table. The best mysql backup in my experience is a raw dump of the data. Does anyone know of a good mysql backup tool that will - Copy data files - Repair those files - zip up the files - then ssh those files to a remote location? If not, then a good mysql backup that doesn't rely on mysql to do the backups?
mmm how about to do it with the command line mysqldump –-user admin –-password=password mydatabase > sql.dump
What you are asking for is not really possible. You need to lock tables for the backup no matter how you do it otherwise if you copy the files only you might not get some records What you could is something like this A perl script to - loop trough all tables and create an associated record in a separate database - transfer data for each table - check tables and repair them (just in case) - backup the other database - transfer by FTP Prefferable to use InnoDB tables to avoid table locking
Actually is possible, use to do it all the time. The challenge with the traditional mysql tools is you get table lock and all sorts of problems that create issues in a live enviroment. The best option is to copy the tables, repair them, zip them up and sftp them over, not ftp of course. I decided to just write the script myself. I had done it a while back but lost it but it's only about 50 lines of code so simple enough. The problem with all the junk out there is most people don't know how to handle large 1GB+ tables and when you get into 10GB+ tables, God help them, this approach may seem dirty but raw table moves are used by some of the largest companies as they provide the quickest and best way of backing up live enviroments without any locking. Thanks all.
Just use mysqldumper. http://www.mysqldumper.de/english You can do everything on biggest databases and iy can zip and transfer to an ftp etc.
mysqldump will do the trick for normal * sized databases and then scp it away, but as you say for larger tables then you will encounter lock issues. I haven't tried hot-copy on very big tables, but that might be worth investigating. I feel, but can't prove, that SCP is a better option than FTP; thought that's probably just my bias. * what ever the heck that means!