MySQL Forums
Forum List  »  Install & Repo

MySQL (5.6.4) won't start after moving from /var/lib/mysql to /home/mysql
Posted by: Charles Marburg
Date: September 06, 2018 10:10AM

Hello,

running MySQL 5.6.40 on Centos 6.5.

Usual story of /var filling up and wanting to move myqsl to another filesystem.

Heavy research here and and on stackflow gave me a solid process to follow.

(all done sudo' to root)
mysql dump databases
drop databases (except mysql, an _schemas)
shutdown msyql
backup ibdata1, ib_logfile* just in case
copy to new /home: cp -rp /var/lib/mysql /home/mysql
backup current mysql: mv /var/lib/msyql /var/lib/mysql.back
start mysql !FAILs

The error I get in the command window is:

mysqld_safe Directory '/home/mysql' for UNIX socket file don't exists.
MySQL Daemon failed to start.

But it does exist. Here is namei of it under /home and /var:

[root@mail mysql]# namei -l /home/mysql/mysql
f: /home/mysql/mysql
dr-xr-xr-x root root /
drwxr-xr-x root root home
drwxrwxrwx mysql mysql mysql
drwx------ mysql mysql mysql
[root@mail mysql]# namei -l /var/lib/mysql/mysql
f: /var/lib/mysql/mysql
dr-xr-xr-x root root /
drwxr-xr-x root root var
drwxr-xr-x root root lib
drwxr-xr-x mysql mysql mysql
drwx------ mysql mysql mysql

Message log says:
2018-09-03 00:04:31 10371 [Note] /usr/sbin/mysqld: Shutdown complete

2018-09-03 00:04:51 8526 [Note] Plugin 'FEDERATED' is disabled.
2018-09-03 00:04:51 8526 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-09-03 00:04:51 8526 [Note] InnoDB: The InnoDB memory heap is disabled
2018-09-03 00:04:51 8526 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-09-03 00:04:51 8526 [Note] InnoDB: Memory barrier is not used
2018-09-03 00:04:51 8526 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-09-03 00:04:51 8526 [Note] InnoDB: Using Linux native AIO
2018-09-03 00:04:51 8526 [Note] InnoDB: Using CPU crc32 instructions
2018-09-03 00:04:51 8526 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-09-03 00:04:51 8526 [Note] InnoDB: Completed initialization of buffer pool
2018-09-03 00:04:51 8526 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2018-09-03 00:04:52 8526 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2018-09-03 00:04:52 8526 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2018-09-03 00:04:52 8526 [Warning] InnoDB: New log files created, LSN=258142186
2018-09-03 00:04:52 8526 [Note] InnoDB: Highest supported file format is Barracuda.
2018-09-03 00:04:52 8526 [Note] InnoDB: 128 rollback segment(s) are active.
2018-09-03 00:04:52 8526 [Note] InnoDB: Waiting for purge to start
2018-09-03 00:04:53 8526 [Note] InnoDB: 5.6.40 started; log sequence number 258142220
2018-09-03 00:04:53 8526 [Note] Server hostname (bind-address): '*'; port: 3306
2018-09-03 00:04:53 8526 [Note] IPv6 is available.
2018-09-03 00:04:53 8526 [Note] - '::' resolves to '::';
2018-09-03 00:04:53 8526 [Note] Server socket created on IP: '::'.
2018-09-03 00:04:53 8526 [Note] Event Scheduler: Loaded 0 events
2018-09-03 00:04:53 8526 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.40' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
2018-09-03 00:05:18 8526 [Note] /usr/sbin/mysqld: Normal shutdown

Last statements are strange since I edited my.cnf pointing to new datadir.

my.cnf for the new /home/mysql:

datadir=/home/mysql
socket=/home/mysql/mysql.sock
user=mysql
max_allowed_packet = 256M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


my.cnf for /var/lib/mysql:

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
max_allowed_packet = 256M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Any other attempts to free up space included adding to my.cnf
innodb_data_home_dir = /home/mysql
innodb_data_file_path = ibdata1:1000M:autoextend

But that gives the error:
2018-09-06 01:26:01 7492 [Note] InnoDB: Completed initialization of buffer pool
2018-09-06 01:26:01 7492 [ERROR] InnoDB: .\ibdata1 can't be opened in read-write mode
2018-09-06 01:57:01 7492 [ERROR] InnoDB: The system tablespace must be writable!

from mysqld.log
2018-09-06 01:26:55 18981 [Note] Plugin 'FEDERATED' is disabled.
2018-09-06 01:26:55 18981 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-09-06 01:26:55 18981 [Note] InnoDB: The InnoDB memory heap is disabled
2018-09-06 01:26:55 18981 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-09-06 01:26:55 18981 [Note] InnoDB: Memory barrier is not used
2018-09-06 01:26:55 18981 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-09-06 01:26:55 18981 [Note] InnoDB: Using Linux native AIO
2018-09-06 01:26:55 18981 [Note] InnoDB: Using CPU crc32 instructions
2018-09-06 01:26:55 18981 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-09-06 01:26:55 18981 [Note] InnoDB: Completed initialization of buffer pool
2018-09-06 01:26:55 7feefd319720 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.
2018-09-06 01:26:55 18981 [ERROR] InnoDB: os_file_get_status() failed on '/home/mysql/ibdata1'. Can't determine file permissions
2018-09-06 01:26:55 18981 [ERROR] InnoDB: The system tablespace must be writable!
2018-09-06 01:26:55 18981 [ERROR] Plugin 'InnoDB' init function returned error.
2018-09-06 01:26:55 18981 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-09-06 01:26:55 18981 [ERROR] Unknown/unsupported storage engine: InnoDB
2018-09-06 01:26:55 18981 [ERROR] Aborting

Just severely scratching my head as the new file permissions match the old. Could something somewhere be forcing mysql to only use /var as location?

Options: ReplyQuote


Subject
Written By
Posted
MySQL (5.6.4) won't start after moving from /var/lib/mysql to /home/mysql
September 06, 2018 10:10AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.