Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
The issue is with AppArmor. I am not sure why MySql is unable access files even after uninstalling AppArmor. No luck, Even after moving the MySql profile under disabled profiles of AppArmor I copied the database files under /var/lib/mysql/ and MySql stopped throwing errors. –  Praveen Sep 10 '13 at 12:07
    
This question appears to be off-topic because it is about a local phenomenon with 3 different solutions: 1) installation, 3) file ownership, 3) file permissions. In principle, all submitted answers along with @Praveen's comment should all be a single answer. Moderators, I would close this one. –  RolandoMySQLDBA Apr 24 at 16:24

4 Answers 4

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.

share|improve this answer

The issue is with AppArmor. I am not sure why MySql is unable to access files even after uninstalling AppArmor.

Reinstalled AppArmor and moved the MySql profile under disabled profiles of AppArmor, even then it did not work.

I copied the database files under /var/lib/mysql/ and MySql stopped throwing errors

share|improve this answer

I faced the same issue... In Ubuntu systems you need to change the permissions for the new datadir.

Goto

/etc/apparmor.d/usr.sbin.mysqld

change the datadir path /var/lib/mysql to new-datadir OR add new lines

chown -R mysql:mysql “new datadir path”

Then it runs smoothly.

share|improve this answer

I've tried all of solutions I can found over the Internet. But this one is what worked for me.

So existing datadir is at /var/lib/mysql. I had it copied to /mnt/var/lib/mysql via cp -a <src> <dest> ( /mnt is Amazon EBS mountpoint )

Naturally I would have thought the -a switch should have taken care of everything. But upon checking the execute bit for o(thers), for /var and /mnt, they are different, the latter's is disabled. So I enabled it and yes, it is now functioning.

The thing is, mysql is not the first occupant of /mnt/var/lib. I earlier moved PostgreSQL datadir there, and it did not complain about having no execute permissions for o.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.