Incremental backups in Mysql

MySQL supports incremental backups: You need to start the server with the --log-bin option to enable binary logging; see Section 5.2.3, The Binary Log. At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained further below. The next time you do a full backup, you should also rotate the binary log using FLUSH LOGS, mysqldump --flush-logs, or mysqlhotcopy --flushlog.

I've used mysqlhotcopy successfully for months.  http://dev.mysql.com/doc/refman/5.0/en/mysqlhotcopy.html

FLUSH LOGS doesn't delete any binary log files unless you have the 'expire-log-days' variable set.  http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_expire_logs_days . Also see http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_max_binlog_size .
If expire-log-days isn't set, it just closes the current log and starts a new one.

> mysqladmin flush-logs
> mv /var/log/mysql/binary/* /var/fileserver/backups/mydatabase001.log
This is not so good.  The brand new binary log file that is started by the flush-logs command is also moved out of the directory.  Also, mysql maintains an index text file in the binary directory that contains the names of all binary log files, and it isn't a good idea to delete that file when mysql may be expecting it to be present.  

A more solid approach involves maintaining an open mysql client session while concurrently manipulating the files.
1.  In a mysql session, issue "LOCK TABLES READ".  This waits for all writing queries to finish, then locks the database to prevent any writes from occuring until after either this client session ends or this client issues a "UNLOCK TABLES" command.  You don't want to be in this state for very long.
2.  Look at the binary/ directory and create a list of all the binary log files that are present.
3.  In mysql, issue "FLUSH LOGS".  This closes the most recent binary log and creates a new one.  We're not interested in backing up the new one.
4.  "UNLOCK TABLES" or exit the client session.
5.  Use the list of binary logs you created in step 2.  These files are all dormant now, and you can copy or move them or whatever.

Of course, this doesn't have to be done manually.  A program or script that can use the mysql client api can do this as well.

Here's a similar way to do the full backup.
1.  mysql: "LOCK TABLES READ"
2.  shell: run the mysqldump command
3.  mysql: "RESET MASTER".  This deletes all binary log files present in the binary/ directory and resets the binary log serial number to 0.
4.  mysql: "UNLOCK TABLES"

So can i set expire-log-days in the my.snf file or how do i configure it?

I'm not familiar with "my.snf", but I'm guessing that it's the same as the "my.cnf" or "my.ini" file.  

It's supposed to be "expire-logs-days", not "expire-log-days" like I said before.

MySQL configuration options can be set on the command line or in the configuration file (like "my.cnf").  Command line options use hyphens '-' between words, and configuration file variables use underscores '_' beween words.  

In the [mysqld] section of the config file, add a line that looks like:
expire_logs_days=14

Alternatively, if you manually start mysqld, add "--expire-logs-days=14" to the command line.

Yeah I meant my.cnf not snf. sorry typo.

Thanks

I assumed it was just a typo, then you did it again. :)

Ok so i got the binary logging enabled and the expire-logs-days set and the database seems to be back up and running fine.

So flush-logs will delete the binary logs after they are expired. is that correct?

Yep.  Docs say "Possible removals happen at startup and at binary log rotation", and FLUSH LOGS causes a rotation.  That's only if expire_logs_days is set to a nonzero value, though.

Rotation also happens when the current binary log file exceeds "max_binlog_size", if you have that set.

So everything looks to be set up correctly but when i got here this morning i went to check to make sure a log file was created and there isnt a log file for my database. here is my my.cnf file what am i missing?

Thanks
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
log-bin=/var/log/mysql/binary/bugs_log
binlog-do-db=bugs
expire_logs_days=2

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Have you restarted the mysqld server since you changed the config file?

Yeah i restarted mysqld right after making the changes

Cool. looks like we are good. I needed to give mysql ownership of the /var/log/mysql/binary directory and rebooted mysqld and that solved it.

Thanks for all the Help

THanks for the help