1

Earlier this month I inherited a MySQL Server 5.6 hosted on a minute Azure VM with a single 30GB HDD. The Database was consuming 22GB; disk usage was at 89%.

Migrate /var/lib/mysql to mounted disk with symlink

lsblk && df
sudo mkdir /media/sdc
fdisk /dev/sdc
     n p 1 2048 209715199 w
sudo mkfs.ext3 /dev/sdc1
sudo mount /dev/sdc1 /media/sdc
sudo systemctl stop mysqld
sudo cp -dpR /var/lib/mysql /media/sdc/mysql
sudo mv /var/lib/mysql /var/lib/mysql.old
sudo ln -s /media/sdc/mysql /var/lib/mysql
sudo systemctl start mysqld

I mounted an external drive, migrated the database files to the external drive; afterwards, and changed the original mysql directory into a symlink pointing to the new location. Initially I thought it may be a reference problem with mysql not traversing the symlink. So I changed my tactic to mount the external drive directly to /var/lib/mysql.

Mount disk to /var/lib/mysql and migrate data.

#Remove the migrated files (I still have mysql.old)
sudo rm /var/lib/mysql
sudo rm -R /media/sdc/mysql

#Mount the drive to /var/lib/mysql directly.
sudo mount /dev/sdc1 /var/lib/mysql
sudo cp -dpR /var/lib/mysql.old /var/lib/mysql
sudo systemctl restart mysql

This didn't work either. In both cases, mysql refuses to start for the same reasons:

sudo systemctl restart mysql
Job for mysqld.service failed because a timeout was exceeded. See "systemctl status mysqld.service" and "journalctl -xe" for details

systemctl status mysqld.service

systemctl status mysqld.service
● mysqld.service - MySQL Community Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: activating (start-post) since Tue 2017-07-18 20:58:12 UTC; 4min 37s ago
Process: 32798 ExecStart=/usr/bin/mysqld_safe (code=exited, status=0/SUCCESS)
Process: 32787 ExecStartPre=/usr/bin/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 32798 (code=exited, status=0/SUCCESS); : 32799 (mysql-systemd-s)
CGroup: /system.slice/mysqld.service
└─control
├─32799 /bin/bash /usr/bin/mysql-systemd-start post
└─33965 sleep 1

journalctl -xe

~
…
~
...skipping...
~
…
~

Additionally, I also tried various methods to start the server to no avail (same results):

sudo systemctl start mysqld
sudo systemctl start mysql.service
sudo systemctl start mysql
service mysql restart

ps aux | grep mysql

admin_u+ 58206  0.0  0.0 112648   976 pts/0    R+   14:19   0:00 grep --color=auto mysql 

How do I resolve this issue and start mysql?

Did I overlook something? I thought it may also be the datadir setting as in this question; but, both methods I took does not modify the datadir path...

  • Look in MySQL's log. That's where you usually find the reason MySQL can't start. – yoonix Jul 18 '17 at 23:05
  • How many MYSQL instances is running ps aux | grep mysql? Is selinux enabled? – Mikhail Khirgiy Jul 19 '17 at 5:32
  • According to ps aux: 1 instance. running system ctl stop mysqd, mysql.service, and mysql didn't close the process. See my edit for the full output. – KareemElashmawy Jul 19 '17 at 14:28
2

Yoonix suggested I look at the MySQL Log.

mysqld.log

...
017-07-19 13:45:13 0 [Note] Binlog end
170719 13:45:13 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
170719 13:55:13 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2017-07-19 13:55:13 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
/usr/sbin/mysqld: Error on realpath() on '/var/lib/mysql-files' (Error 2 - No such file or directory)
2017-07-19 13:55:13 0 [ERROR] Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/lib/mysql-files
2017-07-19 13:55:13 0 [ERROR] Aborting

Solution:

When mounting the drive and moving files over to the external drive, I had also moved mysql-files too. Upon moving it back, I failed to rename it back to /mysql-files. Instead I renamed it to /mysqlfiles. Renaming the directory fixed the issue.

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.