2

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.

2
  • 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, 2013 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. Apr 24, 2014 at 16:24

5 Answers 5

3

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.

0

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

0

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.

0

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.

0

If you move your datadir, you not only need to give the new datadir permissions, but you need to insure all parent directories have permission.

I moved my datadir to a hard drive, mounted in Ubuntu as:

/media/*user*/Data/

and my datadir was Databases.

I had to set permissions to 771 to each of the media, user and Data directories:

sudo chmod 771 *DIR*

If this does not work, another way you can get mysql to work is to change user in /etc/mysql/my.cnf to root; though there are no doubt some issues with doing that from a security perspective.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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