2

I have a MySQL database with activated binary logging (i.e. rollforward logs). The logs are located (by default) in /var/log/mysql which is accessible by the users mysql and root only.

I make (full) backups every Sunday with a cronjob like this:

mysqldump -u root --flush-logs $database > $database.sql
mysql -u root -e 'purge binary logs before now();'
            # or "reset master", which is the same

I know that mysqldump has no option for incremental backups and the binary logs actually are the incremental backup -- or at least can bee seen as such. So every Wednesday I make an incremental backup by flushing and copying the binlog files:

mysql -u root -e 'flush binary logs;'
cp /var/log/mysql/mysql-bin.* $some_backup_path

(Code to sort out the current binlog omitted here.)

There are various commands to inspect the binlog files, like

show binary logs;                    -- display
flush binary logs;                   -- start a new one
reset master;                        -- delete all and start over
purge binary logs before $some_date; -- delete older logs

However, there seems to be no MySQL command to fetch them. The only way seems to copy them with "regular" Unix commands, like cp or rsync as user root. Is that true?

I know there are other ways to make backups, such as the MySQL Enterprise stuff or simply backing up the /var/lib/mysql directory. I'm just curious.

Java Guru
  • 466
  • 4
  • 12
PerlDuck
  • 5,610
  • 3
  • 20
  • 39
  • 1
    What exactly do you mean by fetching logs? – Shadow Feb 21 '16 at 13:52
  • 1
    Note that for some table types copying `/var/lib/mysql` doesn't work v well. You'll miss anything in memory that hasn't been committed. – ethrbunny Feb 21 '16 at 13:55
  • @Shadow Copying them to the local filesystem into a directory of my choice. Like `mysqldump` fetches the DDL and the full data to a file of my choice. – PerlDuck Feb 21 '16 at 13:55
  • Yes, it's true. There are myriad OS commands to copy files over a network but none specifically written for MySQL. MySQLDump merely provides the Host/User/PW like the std mysql client. – ethrbunny Feb 21 '16 at 14:04
  • @ethrbunny Good point. But it would be OK for me (and my users) if I'd shutdown the whole DB for a couple of minutes within a maintenance window. Actually my full backup is so fast (< 3 secs.) that I don't really _need_ incremental backups at all. I'm doing them for fun and learning so I know when times come. – PerlDuck Feb 21 '16 at 14:04
  • I stand corrected. @shadow FTW! – ethrbunny Feb 21 '16 at 14:07

1 Answers1

2

You can use mysqlbinlog utility to administer binary log files, including copying them to another location. Within mysql you can use show binlog events command to access most of the contents within a binary log.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Ahh, I wasn't aware of the `show binlog events`. Thank you. I know `mysqlbinlog` as it is crucial for applying the binlog. It works fine but only with local files I thought. But when you pointed me to the docs I noticed the [--read-from-remote-server](https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_read-from-remote-server) option. I think that's exactly what I was looking for. The binary logs in their binary form are of no use anyway. – PerlDuck Feb 21 '16 at 14:33