Moving Binary Logs

Can't get permissions right

[boxfan]

Hi,

I am trying to change the location of where my MySQL binary logs are kept as I want them stored to my back up drive.

Currently they are being written to /var/lib/mysql/bin.log which is the Redhat default location but I would like to write them to /backup/mysql/binary/bin.log.

I added the following to my.cnf

log-bin = /backup/mysql/binary/bin.log

but I can't get MySQL to write to that location. I tried changing the permissions of the mysql folder to mysql:mysql and 660 but it's still not working.

Any ideas?

[webdoctor]

If /backup/mysql/binary/bin.log is actually a mount point, you need to check the permissions on the /backup/mysql/binary/bin.log directory before you mount anything on it, and the permissions on the directory you are going to mount there.

[boxfan]

Yes, the /backup location is a valid mount and I have other backup data written to this location each night.

I know it's a permission problem but I was hoping someone would know the proper permissions for MySQL to write to the directory.

[webdoctor]

If you are mounting /backup/mysql on /var/lib/mysql/backup then you need to set the owner of /backup/mysql to mysql:mysql before you mount it on /var/lib/mysql/backup in order for mysqld to be able to write to it.

[boxfan]

Yeah...read my initial post

"I tried changing the permissions of the mysql folder to mysql:mysql and 660 but it's still not working."

[webdoctor]

You need the permissions to be right in TWO places - the /var/lib/mysql folder, and the BACKUP folder /backup/foo (which you will mount in the empty directory /var/lib/mysql/bin.log)

It's not clear to me that you've done this...

[boxfan]

Ok, I'm obviously way off course. Let's start from scratch.

I have a second drive for backup files. I mounted the second drive to /backup

I run scripts each night that backup all of the cpanel accounts to /backup/$todaysdate

I then created a directory /backup/mysql and I run a script each night that backs up all my databases and stores it in that directory.

I created a new directory /backup/mysql/binary where I want to store the binary files

I added the following to my.cnf

log-bin = /backup/mysql/binary/bin.log

I set the permissions on /backup/mysql/binary to mysql:mysql and chmod 660 which is what the permissions for /var/lib/mysql is set to.

This is all I've done. It sounds like what you're talking about is mapping /backup/mysql/binary to /var/lib/mysql?

[webdoctor]

Ok, I'm obviously way off course. Let's start from scratch.

My fault - I was making too many assumptions about how you would mount partitions from your backup drive - but you're not doing it the way I imagined.

I have a second drive for backup files. I mounted the second drive to /backup

Who owns the /backup directory and what are its permissions? The way you're doing it, you need to have at least read access for everyone, otherwise the mysql user won't be able to list the subdirectories to get down to /backup/mysql/binary/...

I run scripts each night that backup all of the cpanel accounts to /backup/$todaysdate
I then created a directory /backup/mysql and I run a script each night that backs up all my databases and stores it in that directory.
I created a new directory /backup/mysql/binary where I want to store the binary files
I added the following to my.cnf
log-bin = /backup/mysql/binary/bin.log
I set the permissions on /backup/mysql/binary to mysql:mysql and chmod 660 which is what the permissions for /var/lib/mysql is set to.
This is all I've done.

Check the permissions in /backup, /backup/mysql, and /backup/mysql/binary and see what the permissions are.

It sounds like what you're talking about is mapping /backup/mysql/binary to /var/lib/mysql?

I thought you'd be mounting a partition from your backup drive somewhere UNDER the /var/lib/mysql folder.

[boxfan]

Ok

/backup is
rw------- 17 root root 4096 Mar 14 03:00 ./

/backup/mysql is
rwx--x--x 4 root root 4096 Mar 13 07:43 mysql/

/backup/mysql/binary is
rwx--x--x 2 root root 4096 Mar 10 13:25 binary/

Which is odd because I set the permissions on binary to mysql:mysql and 660.

So, backup, mysql and binary should probably be
rw-rw----

and binary should be chown mysql:mysql

Right?

[webdoctor]

If mysqld is supposed to save the binary log in /backup/mysql/binary then as far as I understand it, the mysql user (and/or mysql group) needs to have at least read access on each of the parent directories - otherwise it can't navigate down the tree to find the directory it's supposed to save the data in.

If you give everyone read access on /backup, /backup/mysql, and /backup/mysql/binary, and make sure that mysql:mysql is the owner of /backup/mysql/binary then I reckon this will fix the problem.