Skip site navigation (1)Skip section navigation (2)
Date:      Mon, 11 Jun 2012 15:26:09 -0400
From:      Michael Powell <nightrecon@hotmail.com>
To:        freebsd-questions@freebsd.org
Subject:   Re: mysqld startup issue
Message-ID:  <jr5go5$ept$1@dough.gmane.org>
References:  <4FD62BA8.7030702@dreamchaser.org>

next in thread | previous in thread | raw e-mail | index | archive | help
Gary Aitken wrote:

> I've done the following after having a running system with a running mysql
> on it:
> 
> moved user accounts, although no logical move:
>   /usr/home/foo was => /hd1/foo
> now
>   /usr/home => /hd1/home  and /hd1/foo is now /hd1/home/foo
> repartitioned the SSD and restored the system from a dump taken prior to
> repartitioning. removed all ports and reinstalled them

Although I have not really experienced much in the way of toruble with this, 
as my systems are not very 'busy', it can be better to boot from a LiveCD to 
do the dump because no files are open for writing and all your MySQL files 
will be static. No possibility of change during the dump. That being said, I 
have done dumps from live filesystems and have been able to restore them many 
times. Forewarned is forearmed.
 
> Unfortunately, mysqld won't start:
> 
> 120611 10:55:52 [Warning] Can't create test file
> /var/db/mysql/breakaway.lower-test 120611 10:55:52 [Warning] Can't create
> test file /var/db/mysql/breakaway.lower-test mysqld: Table 'mysql.plugin'
> doesn't exist 120611 10:55:52 [ERROR] Can't open the mysql.plugin table.
> Please run mysql_upgrade to create it. 120611 10:55:52 InnoDB: The InnoDB
> memory heap is disabled 120611 10:55:52 InnoDB: Mutexes and rw_locks use
> GCC atomic builtins 120611 10:55:52 InnoDB: Compressed tables use zlib
> 1.2.5 120611 10:55:52 InnoDB: Initializing buffer pool, size = 128.0M
> 120611 10:55:52 InnoDB: Completed initialization of buffer pool
> 120611 10:55:52  InnoDB: Operating system error number 13 in a file
> operation. InnoDB: The error means mysqld does not have the access rights
> to InnoDB: the directory.
> InnoDB: File name ./ibdata1
> InnoDB: File operation call: 'create'.
> InnoDB: Cannot continue operation.

I have had trouble before when playing with these files outside of MySQL. 
What happens is if they get out of sync with the index they will become 
totally unusable. There are recovery procedures in the docs, but if memory 
serves it was just easier to delete the ib_logfile(x) and allow MySQL to 
recreate from scratch. Probably not central to your problem, per se, just 
thought I'd make mention in passing. I would recommend consulting the docs 
on this subject of index/logfile recovery prior to any blindly mucking about.
 
> Running mysqld --verbose shows:
> 
>   basedir             /usr/local
>   general-log-file    /var/db/mysql/breakaway.log

Usually this file is of the form <FQDN hostname>.err
 
> ls -aol /usr/local/libexec/mysqld
> -rwxr-xr-x  1 root  wheel  - 9558944 Jun 11 10:40

mine shows:

testbed# ls -aol /usr/local/libexec/mysqld
-r-xr-xr-x  1 root  wheel  - 6694672 May 10 11:16 /usr/local/libexec/mysqld

> /usr/local/libexec/mysqld ls -dl /var/db/mysql
> drwxr-xr-x  2 mysql  mysql  512 Jun 11 10:31 /var/db/mysql

and my /var/db shows the below for the mysql directory:

drwxr-xr-x   21 mysql     mysql        3072 Jun  4 12:09 mysql
 
> cd /
> find . -ls | grep my.cnf
> 
> shows nothing.
> 
> This looks like some kind of access / setuid problem, but I'm not sure
> what. Suggestions?

Beginning with the mysql_enable="YES" I have found when using rc startup 
scripts you need the entire path: /usr/local/etc/rc.d/mysql-server <blah>

Moving on to permissions. Don't recall specifically, but if memory serves 
during the port install/reinstall there is a choice displayed to keep/use a 
previous mysql:mysql user and group combo. Perhaps an incorrect choice 
allows for deleting this - never known as I have always chosen to not delete 
but to reuse the old accounts. 

Nevertheless, in /etc/group there should be an entry like this:

mysql:*:88:

And the user account as shown by vipw will look like this:

mysql:*:88:88::0:0:MySQL Daemon:/nonexistent:/sbin/nologin

These should both be handled 'automagically' by the ports build system. You 
can cd to /var/db and do a chown -R mysql:mysql mysql if you want to ensure 
file ownership is correct, provided the above referenced entries are in 
place.

The ports these days will place the my.cnf file in /usr/local/etc instead of 
the old location of /var/db/mysql. I believe it is still supposed to fall 
back to /var/db/mysql if not found. One thing I found out when I was having 
a problem was that MySQL will not source this file if permissions on it are 
world read/write. Not exactly sure what it's supposed to be (I'm sure this 
can be found in the docs) but I've just chmod'd it 444 when I'm done with 
edits.

I do not grasp why any movement of your home directories mentioned at the 
top would matter to MySQL. I suspect something more to do with the "removed 
all ports and reinstalled them" part. The mysql.plugin tells you to run an 
update script to update schema. There is more info on this in the docs. 
Don't believe that is the main problem. I suspect either you have InnoDB 
configs not being read in my.cnf or that InnoDB logfiles or index got 
corrupted by the backup process. If you have MyIsam tables and you comment 
out in my.cnf the activation (once you get my.cnf being read and used) of 
the InnoDB engine and now MySQL starts then I would focus on the InnoDB 
corruption issue.

-Mike







Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?jr5go5$ept$1>