16

I'm trying to start the server and then fix the root user by

$ mysql -u root mysql
$mysql> UPDATE user SET Password=PASSWORD('my_password') where USER='root';
$mysql> FLUSH PRIVILEGES;

But the server can't be started:

$ mysqld --skip-grant-tables
2014-10-21 07:18:24 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-10-21 07:18:24 1967 [Warning] Can't create test file /usr/local/mysql-5.6.21-osx10.8-x86_64/data/x4430.lower-test
2014-10-21 07:18:24 1967 [Warning] Can't create test file /usr/local/mysql-5.6.21-osx10.8-x86_64/data/x4430.lower-test
2014-10-21 07:18:24 1967 [Note] Plugin 'FEDERATED' is disabled.
2014-10-21 07:18:24 1967 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-10-21 07:18:24 1967 [Note] InnoDB: The InnoDB memory heap is disabled
2014-10-21 07:18:24 1967 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-10-21 07:18:24 1967 [Note] InnoDB: Memory barrier is not used
2014-10-21 07:18:24 1967 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-10-21 07:18:24 1967 [Note] InnoDB: Using CPU crc32 instructions
2014-10-21 07:18:24 1967 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-10-21 07:18:24 1967 [Note] InnoDB: Completed initialization of buffer pool
2014-10-21 07:18:24 1967 [ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode
2014-10-21 07:18:24 1967 [ERROR] InnoDB: The system tablespace must be writable!
2014-10-21 07:18:24 1967 [ERROR] Plugin 'InnoDB' init function returned error.
2014-10-21 07:18:24 1967 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2014-10-21 07:18:24 1967 [ERROR] Unknown/unsupported storage engine: InnoDB
2014-10-21 07:18:24 1967 [ERROR] Aborting

2014-10-21 07:18:24 1967 [Note] Binlog end
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'partition'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_METRICS'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_CMPMEM'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_CMP'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_LOCKS'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'INNODB_TRX'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'BLACKHOLE'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'ARCHIVE'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'MRG_MYISAM'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'MyISAM'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'MEMORY'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'CSV'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'sha256_password'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'mysql_old_password'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'mysql_native_password'
2014-10-21 07:18:24 1967 [Note] Shutting down plugin 'binlog'
2014-10-21 07:18:24 1967 [Note] mysqld: Shutdown complete

Mac OS X 10.9.5 and MySQL: mysql Ver 14.14 Distrib 5.6.21, for osx10.8 (x86_64) using EditLine wrapper

Permissions:

$ ls -la /usr/local/mysql-5.6.21-osx10.8-x86_64
total 232
drwxr-xr-x  17 qazwsx  wheel    578 Oct 20 23:27 .
drwxrwxr-x  23 qazwsx  admin    782 Oct 20 23:27 ..
-rw-r--r--   1 qazwsx  wheel  17987 Sep 11 06:53 COPYING
-rw-r--r--   1 qazwsx  wheel  87980 Sep 11 06:53 INSTALL-BINARY
-rw-r--r--   1 qazwsx  wheel   2496 Sep 11 06:53 README
drwxr-xr-x  47 qazwsx  wheel   1598 Oct 20 23:27 bin
drwxr-xr-x  12 _mysql  wheel    408 Oct 21 07:13 data
drwxr-xr-x   5 qazwsx  wheel    170 Oct 20 23:27 docs
drwxr-xr-x  45 qazwsx  wheel   1530 Oct 20 23:27 include
drwxr-xr-x  12 qazwsx  wheel    408 Oct 20 23:27 lib
drwxr-xr-x   4 qazwsx  wheel    136 Sep 11 07:13 man
-rw-r--r--   1 qazwsx  wheel    943 Oct 20 23:25 my.cnf
drwxr-xr-x  19 qazwsx  wheel    646 Oct 20 23:27 mysql-test
drwxr-xr-x   3 qazwsx  wheel    102 Oct 20 23:27 scripts
drwxr-xr-x  36 qazwsx  wheel   1224 Oct 20 23:27 share
drwxr-xr-x  28 qazwsx  wheel    952 Oct 20 23:27 sql-bench
drwxr-xr-x   8 qazwsx  wheel    272 Oct 20 23:27 support-files


$ ls -la /usr/local/mysql-5.6.21-osx10.8-x86_64/data/
total 221248
drwxr-xr-x  12 _mysql  wheel       408 Oct 21 07:13 .
drwxr-xr-x  17 qazwsx  wheel       578 Oct 20 23:27 ..
-rw-rw----   1 _mysql  wheel        56 Oct 20 23:28 auto.cnf
drwx------   3 _mysql  wheel       102 Oct 20 23:45 cloudplatform
-rw-rw----   1 _mysql  wheel  50331648 Oct 21 07:13 ib_logfile0
-rw-rw----   1 _mysql  wheel  50331648 Oct 20 23:25 ib_logfile1
-rw-rw----   1 _mysql  wheel  12582912 Oct 21 07:13 ibdata1
drwx------  81 _mysql  wheel      2754 Oct 20 23:25 mysql
drwx------  55 _mysql  wheel      1870 Oct 20 23:25 performance_schema
drwxr-xr-x   3 _mysql  wheel       102 Oct 20 23:27 test
-rw-r-----   1 _mysql  wheel     24444 Oct 21 07:13 x4430.err
-rw-rw----   1 _mysql  wheel         5 Oct 21 07:13 x4430.pid
16

The entire folder should be owned by mysql

cd /usr/local/mysql-5.6.21-osx10.8-x86_64/data/
chown -R mysql:mysql *

Then go restart mysql

Give it a Try !!!

  • Using homebrew on Mac OS Sierra and mysql Ver 14.14 Distrib 5.7.16, for osx10.12 (x86_64), I had to "cd /usr/local/var; chown -R mysql:mysql mysql" – Tad Nov 29 '16 at 19:19
3

I find another reason for the same error. I use homebrew to install mysql.

The server could not be stopped by simply use the command:

mysql.server stop

The mysql process will automatically restart. The only way to stop that is using this command:

brew services stop mysql

Then, you could fix issue with root user.

2

Don't forget that things like apparmor can block write access to directories for mysql, as well.

See places like /etc/apparmor.d/usr.sbin.mysqld, and give "** rwk" to your mysql datadir. (Needs apparmor restart) See place like /var/log/kern.log for apparmor reports.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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