I have a production database that I usually run fullbackups on once every week. I have bin logs turned on for incremental backups.
The production server is on the west coast while business hours are on the east coast.
When I perform the fullback up I query the master for is log and position. I put these values in the filename of the zip that I compress the databases to.
I'm having difficulties using mysqlbinlog to create incremental sql file from a certain position that I know the backup has ended at.
Show Master Status then then running
mysqldump --flush-logs --single-transaction... suffice to get accurate information in order to restore.
I know the ideal setup is to backup a slave database since locking won't hurt the production environment but at the moment I haven't made the switch.
Am I not getting the desirable results because I'm not locking all the tables?
Thanks in advance
I realized with the help of RolandoMySQLDBA that I wasn't pointing to the appropriate binlog file. I was instead pointing to the whole directory
binlog/bin-log.[0-9]* > sql.txt. So with just the position I was hitting logs that started much earlier than desired. So I changed my script to pull the appropriate binlog file as well as the position and that worked like a charm.