<p>How do I do backups in MySQL?  </p>

<p>I'm hoping there'll be something better than just running mysqldump every "x" hours.</p>

<p>Is there anything like SQL Server has, where you can take a full backup each day, and then incrementals every hour, so if your DB dies you can restore up to the latest backup?</p>

<p>Something like the DB log, where as long as the log doesn't die, you can restore up to the  exact point where the DB died?</p>

<p>Also, how do these things affect locking?
I'd expect the online transactions to be locked for a while if I do a mysqldump.</p>

[{'answer_id': 46549, 'author': 'Kyle Cronin', 'author_id': 658, 'author_profile': 'https://Stackoverflow.com/users/658', 'pm_score': 5, 'selected': True, 'text': '<p>You might want to look at <a href="http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html" rel="noreferrer">incremental backups</a>.</p>\n'}, {'answer_id': 46670, 'author': 'jake', 'author_id': 389, 'author_profile': 'https://Stackoverflow.com/users/389', 'pm_score': 3, 'selected': False, 'text': '<p>now i am beginning to sound like a marketeer for this product. i answered a question with it <a href="https://stackoverflow.com/questions/36551/mysql-shell-on-windows">here</a>, then i answered another with it again <a href="https://stackoverflow.com/questions/40902/looking-for-mysql-ide#41381">here</a>.</p>\n\n<p>in a nutshell, try sqlyog (enterprise in your case) from <a href="http://www.webyog.com/en" rel="nofollow noreferrer">webyog</a> for all your mysql requirements. it not only <a href="http://www.webyog.com/faq/category/24/backup_restore.html" rel="nofollow noreferrer">schedules backups</a>, but also <a href="http://www.webyog.com/faq/category/25/sqlyog-job-agent-sja.html" rel="nofollow noreferrer">schedules synchronization</a> so you can actually replicate your database to a remote server.</p>\n\n<p>it has a free community edition as well as an enterprise edition. i recommend the later to  you though i also reccomend you start with the comm edition and first <a href="http://www.webyog.com/en/sqlyog_feature_matrix.php" rel="nofollow noreferrer">see how you like it</a>.</p>\n'}, {'answer_id': 46686, 'author': 'Daniel Magliola', 'author_id': 3314, 'author_profile': 'https://Stackoverflow.com/users/3314', 'pm_score': 1, 'selected': False, 'text': '<p>@Jake,</p>\n\n<p>Thanks for the info.\nNow, it looks like only the commercial version has backup features.</p>\n\n<p>Isn\'t there ANYTHING built into MySQL to do decent backups?  </p>\n\n<p>The official MySQL page even recommends things like "well, you can copy the files, AS LONG AS THEY\'RE NOT BEING UPDATED"...</p>\n'}, {'answer_id': 46713, 'author': 'Leon Timmermans', 'author_id': 4727, 'author_profile': 'https://Stackoverflow.com/users/4727', 'pm_score': 2, 'selected': False, 'text': "<p>I use mysqlhotcopy, a <em>fast on-line hot-backup utility for local MySQL databases and tables</em>. I'm pretty happy with it.</p>\n"}, {'answer_id': 47240, 'author': 'Harry', 'author_id': 4704, 'author_profile': 'https://Stackoverflow.com/users/4704', 'pm_score': 2, 'selected': False, 'text': '<p>You might want to supplement your current offline backup scheme with <a href="http://dev.mysql.com/doc/refman/5.0/en/replication.html" rel="nofollow noreferrer">MySQL replication</a>.  </p>\n\n<p>Then if you have a hardware failure you can just swap machines.  If you catch the failure quickly you\'re users won\'t even notice any downtime or data loss.</p>\n'}, {'answer_id': 47296, 'author': 'Brent ', 'author_id': 3764, 'author_profile': 'https://Stackoverflow.com/users/3764', 'pm_score': 1, 'selected': False, 'text': '<p>The problem with a straight backup of the mysql database folder is that the backup will not necessarily be consistent, unless you do a write-lock during the backup.</p>\n\n<p>I run a script that iterates through all of the databases, doing a mysqldump and gzip on each to a backup folder, and then backup that folder to tape.</p>\n\n<p>This, however, means that there is no such thing as <em>incremental</em> backups, since the nightly dump is a complete dump.  But I would argue that this could be a good thing, since a restore from a <em>full backup</em> will be a significantly quicker process than restoring from incrementals - and if you are backing up to tape, it will likely mean gathering a number of tapes before you can do a full restore.</p>\n\n<p>In any case, whichever backup plan you go with, make sure to do a trial restore to ensure that it works, and get an idea of how long it might take, and exactly what the steps are that you need to go through.</p>\n'}, {'answer_id': 47353, 'author': 'dbr', 'author_id': 745, 'author_profile': 'https://Stackoverflow.com/users/745', 'pm_score': 2, 'selected': False, 'text': '<p>I use a simple script that dumps the mysql database into a tar.gz file, encrypts it using gpg and sends it to a mail account (Google Mail, but that\'s irrelevant really)</p>\n\n<p>The script is a Python script, which basically runs the following command, and emails the output file.</p>\n\n<p><code>mysqldump -u theuser -p mypassword thedatabase | gzip -9 - | gpg -e -r 12345 -r 23456 &gt; 2008_01_02.tar.gz.gpg</code></p>\n\n<p>This is the entire backup. It also has the web-backup part, which just tar/gzips/encrypts the files. It\'s a fairly small site, so the web backups are much less than 20MB, so can be sent to the GMail account without problem (the MySQL dumps are tiny, about 300KB compressed). It\'s extremely basic, and won\'t scale very well. I run it once a week using cron.</p>\n\n<p>I\'m not quite sure how we\'re supposed to put longish scripts in answers, so I\'ll just shove it as a code-block..</p>\n\n<pre><code>#!/usr/bin/env python\n#encoding:utf-8\n#\n# Creates a GPG encrypted web and database backups, and emails it\n\nimport os, sys, time, commands\n\n################################################\n### Config\n\nDATE = time.strftime("%Y-%m-%d_%H-%M")\n\n# MySQL login\nSQL_USER = "mysqluser"\nSQL_PASS = "mysqlpassword"\nSQL_DB = "databasename"\n\n# Email addresses\nBACKUP_EMAIL=["email1@example.com", "email2@example.com"] # Array of email(s)\nFROM_EMAIL = "root@myserver.com" # Only one email\n\n# Temp backup locations\nDB_BACKUP="/home/backupuser/db_backup/mysite_db-%(date)s.sql.gz.gpg" % {\'date\':DATE}\nWEB_BACKUP="/home/backupuser/web_backup/mysite_web-%(date)s.tar.gz.gpg" % {\'date\':DATE}\n\n# Email subjects\nDB_EMAIL_SUBJECT="%(date)s/db/mysite" % {\'date\':DATE}\nWEB_EMAIL_SUBJECT="%(date)s/web/mysite" % {\'date\':DATE}\n\nGPG_RECP = ["MrAdmin","MrOtherAdmin"]\n### end Config\n################################################\n\n################################################\n### Process config\nGPG_RECP = " ".join(["-r %s" % (x) for x in GPG_RECP]) # Format GPG_RECP as arg\n\nsql_backup_command = "mysqldump -u %(SQL_USER)s -p%(SQL_PASS)s %(SQL_DB)s | gzip -9 - | gpg -e %(GPG_RECP)s &gt; %(DB_BACKUP)s" % {\n    \'GPG_RECP\':GPG_RECP,\n    \'DB_BACKUP\':DB_BACKUP,\n    \'SQL_USER\':SQL_USER,\n    \'SQL_PASS\':SQL_PASS,\n    \'SQL_DB\':SQL_DB\n}\n\nweb_backup_command = "cd /var/www/; tar -c mysite.org/ | gzip -9 | gpg -e %(GPG_RECP)s &gt; %(WEB_BACKUP)s" % {\n    \'GPG_RECP\':GPG_RECP,\n    \'WEB_BACKUP\':WEB_BACKUP,\n}\n# end Process config\n################################################\n\n################################################\n### Main application\ndef main():\n        """Main backup function"""\n        print "Backing commencing at %s" % (DATE)\n\n        # Run commands\n        print "Creating db backup..."\n        sql_status,sql_cmd_out = commands.getstatusoutput(sql_backup_command)\n        if sql_status == 0:\n                db_file_size = round(float( os.stat(DB_BACKUP)[6]  ) /1024/1024, 2) # Get file-size in MB\n                print "..successful (%.2fMB)" % (db_file_size)\n                try:\n                    send_mail(\n                        send_from = FROM_EMAIL,\n                        send_to   = BACKUP_EMAIL,\n                        subject   = DB_EMAIL_SUBJECT,\n                        text      = "Database backup",\n                        files     = [DB_BACKUP],\n                        server    = "localhost"\n                    )\n                    print "Sending db backup successful"\n                except Exception,errormsg:\n                    print "Sending db backup FAILED. Error was:",errormsg\n                #end try\n\n                # Remove backup file\n                print "Removing db backup..."\n                try:\n                        os.remove(DB_BACKUP)\n                        print "...successful"\n                except Exception, errormsg:\n                        print "...FAILED. Error was: %s" % (errormsg)\n                #end try\n        else:\n                print "Creating db backup FAILED. Output was:", sql_cmd_out\n        #end if sql_status\n\n        print "Creating web backup..."\n        web_status,web_cmd_out = commands.getstatusoutput(web_backup_command)\n        if web_status == 0:\n                web_file_size = round(float( os.stat(WEB_BACKUP)[6]  ) /1024/1024, 2) # File size in MB\n                print "..successful (%.2fMB)" % (web_file_size)\n                try:\n                    send_mail(\n                        send_from = FROM_EMAIL,\n                        send_to   = BACKUP_EMAIL,\n                        subject   = WEB_EMAIL_SUBJECT,\n                        text      = "Website backup",\n                        files     = [WEB_BACKUP],\n                        server    = "localhost"\n                    )\n                    print "Sending web backup successful"\n                except Exception,errormsg:\n                    print "Sending web backup FAIELD. Error was: %s" % (errormsg)\n                #end try\n\n                # Remove backup file\n                print "Removing web backup..."\n                try:\n                        os.remove(WEB_BACKUP)\n                        print "...successful"\n                except Exception, errormsg:\n                        print "...FAILED. Error was: %s" % (errormsg)\n                #end try\n        else:\n                print "Creating web backup FAILED. Output was:", web_cmd_out\n        #end if web_status\n#end main\n################################################\n\n################################################\n# Send email function\n\n# needed email libs..\nimport smtplib\nfrom email.MIMEMultipart import MIMEMultipart\nfrom email.MIMEBase import MIMEBase\nfrom email.MIMEText import MIMEText\nfrom email.Utils import COMMASPACE, formatdate\nfrom email import Encoders\n\ndef send_mail(send_from, send_to, subject, text, files=[], server="localhost"):\n        assert type(send_to)==list\n        assert type(files)==list\n\n        msg = MIMEMultipart()\n        msg[\'From\'] = send_from\n        msg[\'To\'] = COMMASPACE.join(send_to)\n        msg[\'Date\'] = formatdate(localtime=True)\n        msg[\'Subject\'] = subject\n\n        msg.attach( MIMEText(text) )\n\n        for f in files:\n                part = MIMEBase(\'application\', "octet-stream")\n                try:\n                    part.set_payload( open(f,"rb").read() )\n                except Exception, errormsg:\n                    raise IOError("File not found: %s"%(errormsg))\n                Encoders.encode_base64(part)\n                part.add_header(\'Content-Disposition\', \'attachment; filename="%s"\' % os.path.basename(f))\n                msg.attach(part)\n    #end for f\n\n        smtp = smtplib.SMTP(server)\n        smtp.sendmail(send_from, send_to, msg.as_string())\n        smtp.close()\n#end send_mail\n################################################\n\nif __name__ == \'__main__\':\n        main()\n</code></pre>\n'}, {'answer_id': 62678, 'author': 'Jon Topper', 'author_id': 6945, 'author_profile': 'https://Stackoverflow.com/users/6945', 'pm_score': 4, 'selected': False, 'text': '<p>mysqldump is a reasonable approach, but bear in mind that for some engines, this will lock your tables for the duration of the dump - and this has availability concerns for large production datasets.</p>\n\n<p>An obvious alternative to this is mk-parallel-dump from Maatkit (<a href="http://www.maatkit.org/" rel="nofollow noreferrer">http://www.maatkit.org/</a>) which you should really check out if you\'re a mysql administrator.  This dumps multiple tables or databases in parallel using mysqldump, thereby decreasing the amount of total time your dump takes.</p>\n\n<p>If you\'re running in a replicated setup (and if you\'re using MySQL for important data in production, you have no excuses not to be doing so), taking dumps from a replication slave dedicated to the purpose will prevent any lock issues from causing trouble.</p>\n\n<p>The next obvious alternative - on Linux, at least - is to use LVM snapshots.  You can lock your tables, snapshot the filesystem, and unlock the tables again; then start an additional MySQL using a mount of that snapshot, dumping from there.  This approach is described here:  <a href="http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/" rel="nofollow noreferrer">http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/</a></p>\n'}, {'answer_id': 63979, 'author': 'longneck', 'author_id': 8250, 'author_profile': 'https://Stackoverflow.com/users/8250', 'pm_score': 1, 'selected': False, 'text': "<p>the correct way to run incremental or continuous backups of a mysql server is with binary logs.</p>\n\n<p>to start with, lock all of the tables or bring the server down. use mysql dump to make a backup, or just copy the data directory. you only have to do this once, or any time you want a FULL backup.</p>\n\n<p>before you bring the server back up, make sure binary logging is enabled.</p>\n\n<p>to take an incremental backup, log in to the server and issue a FLUSH LOGS command. then backup the most recently closed binary log file.</p>\n\n<p>if you have all innodb tables, it's simpler to just use inno hot backup (not free) or mysqldump with the --single-transaction option (you'd better have a lot of memory to handle the transactions).</p>\n"}, {'answer_id': 64047, 'author': 'DirkReiners', 'author_id': 7996, 'author_profile': 'https://Stackoverflow.com/users/7996', 'pm_score': 1, 'selected': False, 'text': '<p>Binary logs are probably the correct way to do incremental backups, but if you don\'t trust binary file formats for permanent storage here is an ASCII way to do incremental backups.</p>\n\n<p>mysqldump is not a bad format, the main problem is that it outputs stuff a table as one big line. The following trivial sed will split its output along record borders:</p>\n\n<p>mysqldump --opt -p  | sed -e "s/,(/,\\n(/g" > database.dump</p>\n\n<p>The resulting file is pretty diff-friendly, and I\'ve been keeping them in a standard SVN repository fairly successfully. That also allows you to keep a history of backups, if you find that the last version got borked and you need last week\'s version.</p>\n'}, {'answer_id': 86079, 'author': 'jake', 'author_id': 389, 'author_profile': 'https://Stackoverflow.com/users/389', 'pm_score': 0, 'selected': False, 'text': '<p>@Daniel,</p>\n\n<p>in case you are still interested, there is a newish (new to me) solution shared by <a href="http://capttofu.livejournal.com/" rel="nofollow noreferrer">Paul Galbraith</a>, a tool that allows for online backup of innodb tables called <a href="http://www.innodb.com/hot-backup/" rel="nofollow noreferrer">ibbackup</a> from oracle which to quote Paul,</p>\n\n<blockquote>\n  <p>when used in conjunction with\n  <a href="http://www.innodb.com/support/documentation/innodb-hot-backup-manual/#innobackup" rel="nofollow noreferrer">innobackup</a>, has worked great in\n  creating a nightly backup, with no\n  downtime during the backup</p>\n</blockquote>\n\n<p>more detail can be found on <a href="http://capttofu.livejournal.com/12455.html" rel="nofollow noreferrer">Paul\'s blog</a></p>\n'}, {'answer_id': 228895, 'author': 'Laurynas Pečiūra', 'author_id': 30693, 'author_profile': 'https://Stackoverflow.com/users/30693', 'pm_score': 2, 'selected': False, 'text': '<p>You can make full dumps of InnoDB databases/tables without locking (downtime) via mysqldump with "--single-transaction --skip-lock-tables" options. Works well for making weekly snapshots + daily/hourly <a href="http://dev.mysql.com/doc/refman/5.0/en/backup.html" rel="nofollow noreferrer">binary log increments</a> (#Using the Binary Log to Enable Incremental Backups).</p>\n'}, {'answer_id': 397218, 'author': 'datasn.io', 'author_id': 49318, 'author_profile': 'https://Stackoverflow.com/users/49318', 'pm_score': 0, 'selected': False, 'text': "<p>Sound like you are talking about transaction roll back.</p>\n\n<p>So in terms of what you need, if you have the logs containing all historical queries, isn't that the backup already? Why do you need an incremental backup which is basically a redundant copy of all the information in DB logs?</p>\n\n<p>If so, why don't you just use mysqldump and do the backup every once a while?</p>\n"}, {'answer_id': 1097131, 'author': 'jacktrade', 'author_id': 81202, 'author_profile': 'https://Stackoverflow.com/users/81202', 'pm_score': 2, 'selected': False, 'text': '<p>the Percona guys made a open source altenative to innobackup ... </p>\n\n<p><strong>Xtrabackup</strong></p>\n\n<p><a href="https://launchpad.net/percona-xtrabackup/" rel="nofollow noreferrer">https://launchpad.net/percona-xtrabackup/</a></p>\n\n<p>Read this article about XtraDB\n<a href="http://www.linux-mag.com/cache/7356/1.html" rel="nofollow noreferrer">http://www.linux-mag.com/cache/7356/1.html</a></p>\n'}, {'answer_id': 1411549, 'author': 'phirschybar', 'author_id': 146602, 'author_profile': 'https://Stackoverflow.com/users/146602', 'pm_score': 1, 'selected': False, 'text': '<p>This is a pretty solid solution for Linux shell. I have been using it for years:</p>\n\n<p><a href="http://sourceforge.net/projects/automysqlbackup/" rel="nofollow noreferrer">http://sourceforge.net/projects/automysqlbackup/</a></p>\n\n<ul>\n<li>Does rolling backups: daily, monthly, yearly</li>\n<li>Lots of options</li>\n</ul>\n'}]
['https://Stackoverflow.com/questions/46545', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3314/']