Fixing MySQL error 1036: table is read only

Recently I undertook formatting my hard drive, and therefore had to do some backups and restoring of my data including my local mysql databases.

For this I used rsync to back up my /var directory. The Problem is that the drive storeing the backed up files was running a FAT32 partion. This means all file owner ship was lost.

When I restored my files I was not able to access mysql databses getting the 1036 error.

After a few searches I found this article. It had a reason but no fix for people. So here is my extention of that article.

To see the problem

From the command line on a standard *nix install one can view who owns the files: by typing:

ls -l /var/lib/mysql

your results might look like:

total 20544-rw-r--r-- 1 mysql mysql        0 2010-05-02 15:57 debian-5.1.flag
drwxr-xr-x 2 root root	    12288 2010-05-04 15:03 drupal
-rw-rw---- 1 mysql mysql        6 2010-05-04 15:04 dublin.pid
-rw-rw---- 1 mysql mysql      125 2010-05-03 12:52 dublin-relay-bin.000001
-rw-rw---- 1 mysql mysql      125 2010-05-04 15:04 dublin-relay-bin.000002
-rw-rw---- 1 mysql mysql      106 2010-05-04 15:04 dublin-relay-bin.000003
-rw-rw---- 1 mysql mysql       78 2010-05-04 15:04 dublin-relay-bin.index
-rw-rw---- 1 mysql mysql 10485760 2010-05-04 15:04 ibdata1
-rw-rw---- 1 mysql mysql  5242880 2010-05-04 15:04 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 2010-05-02 15:57 ib_logfile1
-rw-rw---- 1 mysql mysql       30 2010-05-04 15:04 master.info
drwxr-xr-x 2 root root	     4096 2010-05-04 15:03 mysql
-rw-rw---- 1 mysql mysql        6 2010-05-02 15:57 mysql_upgrade_info
-rw-rw---- 1 mysql mysql       31 2010-05-04 15:04 relay-log.info
drwxr-xr-x 2 root root	    16384 2010-05-04 15:03 zc139

If in some of the output of the, ls -l has the pattern "root root", this means that the unix user root owns these files and that mysql can not modify them. This is what is causeing the Read Only issue.

The Fix:

One needs super user privs to do this, most commonly sudo is uesed to acheve this.

To Change the owner of the files.

sudo chown -R mysql:mysql /var/lib/mysql 

Reboot Mysql so that the changes are detected.

sudo service mysql restart

From there it should work.