3

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.

Rich Hewett
  • 31
  • 1
  • 4
  • where in the my.ini file did you add the path for logging? – BK435 Feb 27 '15 at 18:15
  • Several sites I found mentioned you could put it right in the log-bin statement for Linux. I haven't been able to find any sites that answer this for Windows, so I didn't have anywhere else to attempt this. – Rich Hewett Feb 27 '15 at 18:28

4 Answers4

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.

BK435
  • 3,076
  • 3
  • 19
  • 27
  • I looked in the .err file. It's telling me: `code` C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe: File 'z:\iis-sql-log.index' not found (Errcode: 2 - No such file or directory) 2015-02-27 14:06:12 3772 [ERROR] Aborting 2015-02-27 14:06:12 3772 [Note] Binlog end 2015-02-27 14:06:12 3772 [Note] C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe: Shutdown complete `code` – Rich Hewett Feb 27 '15 at 19:08
  • I copied my older bin-logs from their local location, into my mapped drive and that still didn't help. I tried changing the path to: "Z:/filename" and that didn't work. – Rich Hewett Feb 27 '15 at 19:15
  • EVERYONE has read and write permission for Z:. I set it for everyone here because it's behind a firewall on my test server and I didn't want that being the thing that gets in my way. Once I get this figured out and ready to go on my production server, I'll be more careful about my permissions. – Rich Hewett Feb 27 '15 at 19:22
  • Hmm... have you tried just log-bin=Z:\iis-sql-log.index?? – BK435 Feb 27 '15 at 19:26
  • Is this like PHP (open_basedir) where I need to give it permission within the my.ini file? – Rich Hewett Feb 27 '15 at 19:29
  • I tried adding the .index on the end (just now) and that didn't help. – Rich Hewett Feb 27 '15 at 19:32
  • No, I know on linux I run chown mysql:mysql * to change permission for my log directory and it always seems to work. The .index was what your error code was showing. I would not have added that. For some reason mysql is looking for a "file" not a "file path". – BK435 Feb 27 '15 at 19:33
  • `# Binary Logging. log-bin=Z:\iis-sql-log expire_logs_days = 14` That's after several other iterations. (flipped slashes, etc) – Rich Hewett Feb 27 '15 at 19:37
  • I am thinking that maybe you cannot write the logs to a mapped network drive. Try a quick test and change the file path to something on your local machine, in your C drive, and see if it works. Also, I would put each statement on its own line and spaces between the = sign in my.ini file. – BK435 Feb 27 '15 at 19:41
  • I was able to get the logs to go work on the same drive with: `log-bin=C:/inetpub/logs/binlogs/iis-sql-log`. The statements in my.ini are on seperate lines, but I'm not sure how to get them to show as such in comments here. – Rich Hewett Feb 27 '15 at 19:48
  • I see. Well I think that is your issue then. If you were able to get them to work on your local machine and not on a mapped network drive then it would appear that you cannot write the logs to that drive. – BK435 Feb 27 '15 at 19:50
  • It seems crazy to not allow transaction logging into remote filespace. – Rich Hewett Feb 27 '15 at 19:52
  • Hmm...actually, try using the entire UNC network path to that drive not just the Z:.. what I am thinking now is that it is mapped on your local machine as Z drive but in reality it exist on the network as //servername/share/filepath...I think that is why it cant find it – BK435 Feb 27 '15 at 19:59
1

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.

Harry Pehkonen
  • 3,038
  • 2
  • 17
  • 19
1

make sure you are ending with filename. see below.

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

Pavan Gilda
  • 41
  • 1
0

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)

Jockser
  • 63
  • 7