How to retrieve all remote mysql binary log files when you don't know the first filename (due to rotation on remote server)

Question

any guidance much appreciated

We want to use mysqlbinlog to download the binary logs from remote server (has to be mysqlbinlog and not scp as that is the only protocol we have open)

The remote server is set to rotate logs after 2 days since full backup is taken every 24 hrs.

When binary logging first started filename was mysql-bin.000001 and crontab command ran fine:

mysqlbinlog mysql-bin.000001 --ssl=0 --read-from-remote-server --host=xxxxxxxxxxxx --user=xxxxxxx --password=xxxxxxxx --raw --to-last-log --result-file=/opt/tb_mysql_backup_binary_logs/production/

Now, due to log rotation, log filenames now start from ...bin.00008 and the command of course fails. I know that we can manually mysql in and run SHOW BINARY LOGS

But is there anyway we can 'ask for all the log files from the first up to latest WITHOUT knowing the first name'

Cheers Kit

Answer 1

There isn't, although you can combine commands and accomplish approximately the same result.

mysqlbinlog $(mysql -e 'show binary logs;' --skip-column-names | head -n 1) --ssl=0 ...

The result obtained from the command inside $() is dropped into the outer command. Inside $() you'll also need to repeat the --host, --user, etc. arguments to authenticate against the server.