> 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
> /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.
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
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:
And the user account as shown by vipw will look like this:
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
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
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