37

I am having difficulty using MySQL on my MacBook, I am trying to start my server using sudo mysqld_safe and this results in the following output:

140131 00:03:02 mysqld_safe Logging to '/usr/local/var/mysql/david-mbp.err'.
140131 00:03:02 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
140131 00:03:03 mysqld_safe mysqld from pid file /usr/local/var/mysql/david-mbp.pid ended

I had a look at this question and it has all the same issues and methods I have tried to resolve my issues. His fix was the upgrade to Lion but I am already on the latest version of Mavericks so this is not a viable solution for me.

I have googled around and reinstalled numerous times but haven't managed to find why I get this issue.

How can I fix this issue?

Edit:

140131 00:03:02 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
2014-01-31 00:03:03 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-01-31 00:03:03 13223 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive
2014-01-31 00:03:03 13223 [Note] Plugin 'FEDERATED' is disabled.
/usr/local/Cellar/mysql/5.6.15/bin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13 - Permission denied)
2014-01-31 00:03:03 13223 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2014-01-31 00:03:03 13223 [Note] InnoDB: The InnoDB memory heap is disabled
2014-01-31 00:03:03 13223 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-01-31 00:03:03 13223 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-01-31 00:03:03 13223 [Note] InnoDB: Using CPU crc32 instructions
2014-01-31 00:03:03 13223 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-01-31 00:03:03 13223 [Note] InnoDB: Completed initialization of buffer pool
2014-01-31 00:03:03 13223 [ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode
2014-01-31 00:03:03 13223 [ERROR] InnoDB: The system tablespace must be writable!
2014-01-31 00:03:03 13223 [ERROR] Plugin 'InnoDB' init function returned error.
2014-01-31 00:03:03 13223 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2014-01-31 00:03:03 13223 [ERROR] Unknown/unsupported storage engine: InnoDB
2014-01-31 00:03:03 13223 [ERROR] Aborting

2014-01-31 00:03:03 13223 [Note] Binlog end
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'partition'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_METRICS'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_CMPMEM'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_CMP'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_LOCKS'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'INNODB_TRX'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'BLACKHOLE'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'ARCHIVE'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'MRG_MYISAM'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'MyISAM'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'MEMORY'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'CSV'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'sha256_password'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'mysql_old_password'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'mysql_native_password'
2014-01-31 00:03:03 13223 [Note] Shutting down plugin 'binlog'
2014-01-31 00:03:03 13223 [Note] /usr/local/Cellar/mysql/5.6.15/bin/mysqld: Shutdown complete

140131 00:03:03 mysqld_safe mysqld from pid file /usr/local/var/mysql/david-mbp.pid ended

ls -l /usr/local/var/mysql output:

-rw-rw----   1 dave      admin  50331648 30 Jan 22:35 ib_logfile0
-rw-rw----   1 dave      admin  50331648 30 Jan 22:35 ib_logfile1
-rw-rw----   1 dave      admin  12582912 30 Jan 22:35 ibdata1
drwx------  81 dave      admin      2754 30 Jan 22:35 mysql
drwx------  55 dave      admin      1870 30 Jan 22:35 performance_schema
-rw-r-----   1 _mysql    admin      4784 31 Jan 00:03 david-mbp.err
drwx------   2 dave      admin        68 30 Jan 22:35 test
Community
  • 1
  • 1
user3144542
  • 599
  • 3
  • 9
  • 19
  • Please post the contents (or the most recent, relevant portion) of the error log: `cat /usr/local/var/mysql/david-mbp.err` or `sudo cat /usr/local/var/mysql/david-mbp.err` – ATP_JD Jan 31 '14 at 03:36
  • @ATP_JD Sorry for the late reply, was asleep please look at the edit. – user3144542 Jan 31 '14 at 13:20
  • This is indicative of a permissions error. Either the data directory is not owned by MySQL, or mysqld is not being launched as the mysql user. Next piece of info to provide is `ls -l /usr/local/var/mysql/` to see who owns the data directory. – ATP_JD Jan 31 '14 at 14:49
  • @ATP_JD Have added the output. Dave being me here. – user3144542 Jan 31 '14 at 15:07

4 Answers4

96

File permissions are definitely the cause of the issue. There are far better experts than me, but I recommend the following:

Ensure your entire data directory is owned by the _mysql user with permissions drwxr-xr-x.

In your case, it appears that your MySQL installation directory is your data directory. My data directory is in a subdirectory of the installation directory.

You can probably get up and running by changing permissions per the command:

sudo chown -R _mysql:admin /usr/local/var/mysql
sudo chmod -R u+rwX,g+rwX,o-rwx /usr/local/var/mysql

This may limit your dave account from accessing the mysql installation directory without sudo, so you may wish to tweak these permissions to include g+rwx if your dave user is a member of the admin group, which it appears to be. That tweak would be accomplished with:

sudo chmod -R g+rwx /usr/local/var/mysql
kenorb
  • 155,785
  • 88
  • 678
  • 743
ATP_JD
  • 1,295
  • 9
  • 7
  • Ah thanks that worked, but can I ask will I be able to fully delete everything and then try this again? As my installation of MySQL is really buggy. – user3144542 Feb 04 '14 at 21:39
  • Yes, you can. Either with `sudo` or without (if you did the second set of commands to provide access to the group). I agree that your installation layout is not ideal. A re-install from the Mac OS X package would clean a lot of this up. – ATP_JD Feb 05 '14 at 01:57
  • This worked for me too. But I had an unused install at that /usr/local/var/mysql. Figured out the instance my system has been starting was different (usr/local/mysql). Otherwise though, this command solved my issue. – Bane Jun 12 '14 at 14:45
  • 2
    A note for others: My original error message was "MySQL server PID file could not be found!" – edan Sep 08 '14 at 20:41
  • Thanks. Saved my day. Can you tell me the difference between the o and g again? – Christian Matthew Jan 21 '16 at 22:06
2

i fixed that by simply delete the ".err" file. Somehow the error log owned by "_mysql" and prevent mysqld zu start.

user5349557
  • 21
  • 1
  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient [reputation](http://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](http://stackoverflow.com/help/privileges/comment). – Luke Peterson Sep 18 '15 at 08:27
  • 2
    @LukePeterson it looks to me that this could be a possible fix for the question, no? – Jasper Sep 18 '15 at 08:32
2

In my case I had a similar problem on Mac OS 10.10 Yosemite. For some reason when I killed the mysql process manually it changed the ownership of the error log file /usr/local/var/mysql/rally.local.err to _mysql instead of caleb. Unlike the accepted solution above, the whole rest of my directory is owned by caleb, not _mysql.

I fixed it by running sudo chown caleb /usr/local/var/mysql/rally.local.err

For the record I guess the proper way to stop and start the mysql server in Yosemite is with /usr/local/var/mysql/mysql.server start|stop|restart.

Caleb
  • 188
  • 4
1

If you don't have any important data, the easiest would be to reinstall MariaDB from scratch (otherwise skip this step), e.g.

mv /usr/local/var/mysql /usr/local/var/mysql.bak
brew reinstall mariadb

Then fix the permissions by:

sudo chmod -R u+rwX,g+rwX,o-rwx /usr/local/var/mysql
sudo chown -R $USER:_mysql /usr/local/var/mysql

Restart MariaDB services by:

brew services restart mariadb

Consider configuring your MariaDB credentials by running:

mysql_secure_installation

Troubleshooting

  • In case it still doesn't work, check the logs for any further issues by:

    tail /usr/local/var/mysql/*.err

  • For example check if in old config file consist any invalid settings, as they may be not supported anymore.

kenorb
  • 155,785
  • 88
  • 678
  • 743