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>