Binary Logging stopping mysql start

[lone_stranger]

I'm new to mysql and after getting installed and working successfully on Linux I wanted to add in a backup solution to protect my data.

I know how to backup a database using the command mysqldump, but I wanted to add in binary logging, so I can get my database back to exactly where it was if something goes pear shaped.

If I add this to my.cnf file

[mysqld]
log-bin=/usr/local/mysql/logs/update

and restart mysql I get the following errors in the localhost.err file in the data directory:

060116 13:35:03 mysqld started
/usr/local/mysql/bin/mysqld: Can't read dir of '/root/tmp/' (Errcode: 13)
/usr/local/mysql/bin/mysqld: File '/usr/local/mysql/logs/update.index' not found (Errcode: 13)
060116 13:35:03 [ERROR] Aborting

060116 13:35:03 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

060116 13:35:03 mysqld ended

I scoured the Internet looking for a solution and so far I'm coming up blank, can some one please please help?

[longneck]

does the directory /usr/local/mysql/logs/ exist? it must be writeable by the user that mysql runs under because mysql will create file in that directory.

it looks like you're trying to run mysql under the root account. this is a bad idea. see http://dev.mysql.com/doc/refman/5.0/...ysql-user.html

[lone_stranger]

I've narrowed the problem down to running mysql as user mysql, but both the /root/usr/ and /usr/local/mysql/logs/ directory both belonged to root. I changed the /usr/local/mysql/logs directory to be owned by mysql.

I don't want to change /root/tmp/ as it will intefere with other applications.

Does anyone know how to tell mysql to set the socket needed for binary-logging to be in another directory other than /root/temp/ in the my.cnf file?

[longneck]

does that error only show up when you try to turn on binary logging?

[lone_stranger]

Yes it all works fine without binary logging. Soon as I turn it on I get errors.

[lone_stranger]

It turned out to be a bug in MySql when being run on Mandrake 10 (which I am running).

For a full explanation of the bug and how to fix it follow this link:

http://bugs.mysql.com/bug.php?id=5822

[longneck]

very nice. do how did you fix it? using a different shell, or by specifying the temp directory as a command line option?

[lone_stranger]

I made sure /root/tmp had the right rw access and then used the following two commands:

export TMPDIR=/tmp
echo $TMPDIR

the echo showed that TMPDIR was set to /root/tmp where mysql was trying to right the socket for binary logging. After that it worked even after restarting the machine. It booted with Mysql running with binary logging turned on.