MySQL gives me:“Can’t open and lock privilege tables: Table ‘host’ is read only”

MySQLmysql-5.5Ubuntu

I am facing problem restoring a MySQL database. My primary database was MySQL 5.1 and now I am trying to copy it to MySQL 5.5. The database was backed up by using Xtrabackup.

I am using Ubuntu 12.04.3 LTS on this server, MySQL version is: 5.5.32-0ubuntu0.12.04.1-log.

I have followed all the steps to restore using Xtrabackup, this created database files, which I have copied to a tmp directory.

I have modified my.cnf to point to this tmp directory. I have changed the tmp directory permissions and changed the ownership of the files to mysql user.

drwxr-xr-x 12 mysql mysql 4096 Sep 10 10:04 base

Now when I start the MySQL server I get this error:

[ERROR] Fatal error: Can't open and lock privilege tables: Table 'host' is read only

I have given a try as follows:

  1. Even tried installing MySQL 5.1 to see if that was the issue.

  2. tried chcon mysql_db_t to change the context but it gives me:

    can't apply partial context to unlabelled file

  3. I have used --skip-grant to get into database, but using this I can only access InnoDB tables only, MyISAM tables throw read-only error.

  4. After --skip-grant, I have used upgrade_mysql. This throws me errors stating that many tables are read-only.

  5. I have removed AppArmor too. And restarted too.

  6. I have restored a different database (5.1 to 5.5) previously on Ubuntu 12.04.2 LTS without any issues.

Can some please point me in right direction? I am not sure whats wrong with permissions.

Best Answer

custom mysql datadir? removed apparmor and still having mysql process failing to start issues? try this. may help some of you depending on your config. worked for me:

when using a custom mysql (5.5) data dir as described above- ie., if the abs. path to datadir (my.cnf) is /opt/some/other/mysql

even with apparmor removed/purged, you must make certain that permissions on ALL of the folders in the mysql datadir path are perm'd correctly. this is a mysql convention- not related to apparmor.

look at /var/lib/mysql- the default datadir (my.cnf) on most installations: perms are 755 for /var and /var/lib.

in my custom datadir example above, when I set any/all of the dirs called 'opt' or 'some' or 'other' without an execute permission for allusers (ie., 750), mysql will not start. once i add an execute perm to allusers (ie. 755, 771, etc.), BINGO. mysql starts.

Related Question