My mysql Database Directory size is full. So I want to change it to another partition where there is large amount of space. The database version am using is MySQL 5.7.23 and OS is CentOS 7.

After searching internet i got 2 solutions. First one as follows:

Firstly,I created a Systemlink pointing to the new drive. Then I tried to start mysql, it fails everytime when I try to start. On removing systemlink it will start.

The error am getting is

[Note] InnoDB: Highest supported file format is Barracuda.
2018-08-21T10:08:04.839330Z 0 [ERROR] InnoDB: Operating system error number 
13 in a file operation.
2018-08-21T10:08:04.839349Z 0 [ERROR] InnoDB: The error means mysqld does 
not have the access rights to the directory.
2018-08-21T10:08:04.839360Z 0 [ERROR] InnoDB: os_file_readdir_next_file() 
returned -1 in directory ./, crash recovery may have failed for some .ibd 
2018-08-21T10:08:04.839385Z 0 [ERROR] InnoDB: Plugin initialization aborted 
with error Generic error
2018-08-21T10:08:05.440529Z 0 [ERROR] Plugin 'InnoDB' init function returned 
2018-08-21T10:08:05.440597Z 0 [ERROR] Plugin 'InnoDB' registration as a 
2018-08-21T10:08:05.440610Z 0 [ERROR] Failed to initialize builtin plugins.
2018-08-21T10:08:05.440617Z 0 [ERROR] Aborting

2018-08-21T10:08:05.440665Z 0 [Note] Binlog end
2018-08-21T10:08:05.440770Z 0 [Note] Shutting down plugin 'CSV'
2018-08-21T10:08:05.441349Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

Secondly, https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04 I followed this url and after that when tried to start mysql keep on getting this error Cant connect to localhost (111) (Error: 2003). This time nothing is showing in mysqld.log file

  • You use InnoDB. I'd recommend to enable innodb_file_per_table configuration option, then explicitly move each user table's tablespace to new location using ALTER TABLE ... TABLESPACE. Do not move system tableset (databases mysql, sys, information_schema, performance_schema), move user databases only. – Akina Aug 22 '18 at 6:43

Here is the key:

2018-08-21T10:08:04.839360Z 0 [ERROR] InnoDB: os_file_readdir_next_file() 
returned -1 in directory ./, crash recovery may have failed for some .ibd 

It says, it can't traverse the directory containing the database files, with the error code -1. Error -1 means

permission denied

The user on which your mysql server is running, should have access to the directory. On most Linuxes, the command is: chown mysql:mysql -Rc /var/lib/mysql as root.

  • That needs to be done on the new partition. – Rick James Aug 30 '18 at 0:45
  • @RickJames Right - such permission problems can happen quite often after things are moved to a new partition, and the movement of the permission metadata wasn't moved correctly on some reason. – peterh Aug 27 '19 at 18:38

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.