MySQL binary log location Windows 2008 Server

Question

I'm running a Windows 2008 Server with IIS, PHP and MySQL. MySQL is installed as a service. There's software on the server that uses a database that I need good backups of, including transaction logging. I've turned on Binary Logging via setting log-bin= and expire_logs_days= in the my.ini file. This saves those logs in the MySQL data folder on that drive. If I lose the drive, I lose my logs and they've done me no good as a backup.

I've found all kinds of advice for relocating these logs on a Linux box, but trying to use the same idea of including a path in the log-bin statement is not working. I've tried "flipping" the slashes, adding quotes and the common other attempts when "translating" Linux to Windows.

I created a mapped drive to the external location to make this easier, and I've tried using \server\folder path statements as well as z:\ path statements.

Any help would be greatly appreciated.

Answer 1

If you made your changes in the [mysqld] section in the my.ini file then you should be good. The correct path naming convention, should look something like this log-bin="C:/yourfilepathname/logs". Since you are still experiencing issues, Please check to make sure that mysql has permission to write to the directory that you are specify the logs files to be written to.

Another way you can check if this is the issue, go to your .err log file when you try to stop and start mysql server it will tell you something along the lines of not having permission to write to that directory.

Also, I would note that log bin files in it of themselves are not a backup. You need to be taking mysqldumps or snapshots of the entire directory from your server, preferably from a slave of your master production server.

Answer 2

You are missing a trailing slash.

Here is what's in my my.ini (I'm using MariaDB 10.1, but I believe this will work for you also):

[mysqld]
datadir=D:/mysql-data
log-bin=E:/mysql-bin/

When I left out the trailing slash in the log-bin setting, I also got the same error in logs, and the service failed to start. After adding the slash, no errors, and the service started successfully.

I now see files getting created in E:\mysql-bin, such as .index, .000001.

Answer 3

Actually it's due to using the normal windows backslashes. Instead of z:\ use z:/ (forward slash). Also if you don't end with a forward slash then it will use the last part as the file name. Exp: z:/bin-logs/ (this will have files named .index and .000001) z:/bin-logs/log (this will have files named log.index and log.000001)

Answer 4

make sure you are ending with filename. see below.

log-bin=C:/Program Files/MySQL/MySQL Server 5.6/data/bin.log