… consistent backup of your MySQL databases – but did you know that you can now restore directly from a snapshot (and binary logs for… every MySQL restart, the logs are flushed and a new binary log is created, looking at the current binary logs after restoring the snapshot, I… and skipping the DROP statement at position 336797160: mysql> show binary logs; +——————+———–+ | Log_name | File_size | +——————+———–+ … | mysql-bin.000022 | 336797725 | | mysql-bin.000023…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By Leave a Comment
Post: Backing up binary log files with mysqlbinlog
January 18, 2012 By 2 Comments
… taking a backup. The problem with this approach was that you had to do periodic filesystem level backups of the binary log files which…/refman/5.6/en/mysqlbinlog-backup.html ). This can be used as a foundation of our live binary log backups. The wrapper script below… last binary log. This last file will be still overwritten hence we make a backup first. #!/bin/sh source $1 cd $BACKUPDIR echo “Backup…
Post: Just how useful are binary logs for incremental backups?
July 21, 2009 By 6 Comments
… effects of the binary log being serialized, is that it also limits the effectiveness of using it for incremental backup. Let me… setup a new slave with a 24-hour old backup, and apply the binary logs continuously until it catches up, you will be… using all InnoDB tables, an XtraBackup incremental backup should be much faster than using binary logs. You can understand Vadim’s excitement…
Post: MySQL Limitations Part 2: The Binary Log
October 23, 2010 By 11 Comments
…. The binary log is necessary not only for replication, but for point-in-time recovery, too. Given a backup and the corresponding binary log position, you can replay the binary log and roll forward the state of…
Post: Storing MySQL Binary logs on NFS Volume
July 30, 2010 By 19 Comments
… such setup. There are good reasons to have binary logs on NFS volume – binary logs is exactly the thing you want to survive the… – using them you can do point in time recovery from backup. I was testing high volume replication today using Sysbench: sysbench… reachable even with binary log stored on NFS volume. This number is for sync_binlog=0 and innodb_flush_log_at_trx_commit…
Post: Using LVM for MySQL Backup and Replication Setup
August 21, 2006 By 53 Comments
… only Innodb tables and do not need to synchronize binary log position with backup you can skip this step. 2) While holding connection…/backup 6) Copy data to backup. Normally you can skip slow query logs and error log while taking backup. You also can skip most of binary logs… the backup you will need to restore slaves as well and skip binary logs in your backup process. 7) Unmount filesystem umount /mnt/backup 8…
Post: Thoughs on Innodb Incremental Backups
November 10, 2008 By 8 Comments
… couple of percents per day. The solution MySQL offers – using binary log works in theory but it is not overly useful in… will take over 24 hours to cover month worth of binary logs… and quite typically you would have much higher update traffic… update the backup and store the rollback changes or if dealing with read-only compressed backup create the roll-forward recovery log, which…
Post: 10 things you need to know about backup solutions for MySQL
March 3, 2009 By 23 Comments
…-time recovery? How does the system guarantee consistency with the binary log, InnoDB logs, and replication? Can you use the system to set… good thing. In reality, a proprietary, secret backup black-box is unacceptable. MySQL backups are complex and difficult to get right. It… list as a way to evaluate your backup provider’s suitability for enterprise-ready backup uses. If you can’t answer these…
Post: How would you compress your MySQL Backup
June 5, 2008 By 29 Comments
… backup impact on server performance may well be). We also assume backup is done on physical level here (cold backup, slave backup, innodb hot backup or snapshot backup) as this is only… compressed file size. Though in this example we used MySQL binary log file which often contains plenty of similar events, which could…
Post: Troubleshooting Relay Log Corruption in MySQL
August 2, 2008 By 5 Comments
… log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted… can’t use classical method of recovery from backup – because you would need relay logs to roll forward, and they are corrupted…
