MySQL Error – ‘ibdata1’ Must Be Writable Despite Full Permissions

According to MySQL Documentation on SELECT ... INTO OUTFILE

Any file created by INTO OUTFILE or INTO DUMPFILE is writable by all users on the server host. The reason for this is that the MySQL server cannot create a file that is owned by anyone other than the user under whose account it is running. (You should never run mysqld as root for this and other reasons.) The file thus must be world-writable so that you can manipulate its contents.

You should output the SELECT INTO OUTFILE to /var/lib/mysql as follows

SELECT * FROM data INTO OUTFILE 'data.csv';

Of course, you need to make sure you have FILE permission on gs@localhost.

There are two ways to have this permission given

METHOD #1

GRANT FILE ON *.* TO 'gs'@'localhost';

METHOD #2

UPDATE mysql.user SET File_priv = 'Y' WHERE user='gs' AND host='localhost';
FLUSH PRIVILEGES;

UPDATE 2012-05-01 07:09 EDT

To give yourself FILE privilege, do the following:

service mysql restart --skip-networking --skip-grant-tables
mysql <hit enter>
UPDATE mysql.user SET File_priv = 'Y' WHERE user='gs' AND host='localhost';
exit
service mysql restart

• Changing the datadir requires the following steps: 1) Stopping the service completely mysqladmin shutdown / service mysql stop. 2) Changing the options on the config file for everything that points to the old location to the new one 3) moving the actual files and directories at the old datadir manually to the new location with cp/rsync (make sure that it has the right permissions in the end, normally owned by the mysql user) 4) Starting the server again
• You can check that new values on my.cnf have effect by executing: mysqld --print-defaults which will confirm you that the options changed are actually applied and that there are no syntax errors on the file.