mysqllinux

MySQL won't start - error: su: warning: cannot change directory to /nonexistent: No such file or directory

New to development & self-teaching (thanks Covid) so this could be sloppy :( sorry...

let me start off by saying I don't care about the data in the database - if it is easier to wipe it and start fresh, I'm good with that (don't know how to do that but I'm ok with it)

Not sure what caused the issue but one day MySQL wouldn't start. Using service MySQL Restart fixed it... two days later it happened again with this error

sarcasticsnark@LB-HP-LT:~/Projects/FMS$ sudo service mysql start
 * Starting MySQL database server mysqld
su: warning: cannot change directory to /nonexistent: No such file or directory

I've tried a bit of "solutions"

  1. I've tried restarting MySQL

  2. I gave myself file permissions to the mysql files (then attempted to reverse that)

  3. I've moved the MySQL directory (then reversed it - hence the copy of the folder "mysql" named "mysql2" below)

My files now look like this and I'm not sure I got the permissions quite right.

sarcasticsnark@LB-HP-LT:/var/lib$ ls
AccountsService  command-not-found  fwupd            logrotate  mysql          mysql2,   private  systemd                  ucf                  usbutils
PackageKit       dbus               git              man-db     mysql-files    pam       python   tpm                      unattended-upgrades  vim
apt              dhcp               initramfs-tools  mecab      mysql-keyring  plymouth  snapd    ubuntu-advantage         update-manager
boltd            dpkg               landscape        misc       mysql-upgrade  polkit-1  sudo     ubuntu-release-upgrader  update-notifier
sarcasticsnark@LB-HP-LT:/var/lib$ cd mysql
sarcasticsnark@LB-HP-LT:/var/lib/mysql$ ls
'#ib_16384_0.dblwr'   TestingGround_development   binlog.000009   binlog.000013   binlog.000017   client-cert.pem   mysql.ibd            server-cert.pem   undo_002
'#ib_16384_1.dblwr'   TestingGround_test          binlog.000010   binlog.000014   binlog.index    client-key.pem    performance_schema   server-key.pem
'#innodb_temp'        auto.cnf                    binlog.000011   binlog.000015   ca-key.pem      debian-5.7.flag   private_key.pem      sys
 FMS_development      binlog.000008               binlog.000012   binlog.000016   ca.pem          mysql             public_key.pem       undo_001
  1. I've re-initialized MySQL (when not running sudoku it errors the below)
2020-07-20T02:29:41.520132Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 4096 (requested 8161)
2020-07-20T02:29:41.520141Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 1967 (requested 4000)
2020-07-20T02:29:41.520561Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.20-0ubuntu0.20.04.1) initializing of server in progress as process 2570
2020-07-20T02:29:41.522888Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
2020-07-20T02:29:41.522921Z 0 [ERROR] [MY-010187] [Server] Could not open file '/var/log/mysql/error.log' for error logging: Permission denied
2020-07-20T02:29:41.523139Z 0 [ERROR] [MY-013236] [Server] The designated data directory /var/lib/mysql/ is unusable. You can remove all files that the server added to it.
2020-07-20T02:29:41.523187Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-07-20T02:29:41.523313Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20-0ubuntu0.20.04.1)  (Ubuntu).

/var/log/mysql - does exist and the permissions for it are:

-rw-r----- 1 mysql adm 62273 Jul 19 19:36 error.log

Here is mysql/error.log

2020-07-20T01:50:07.952988Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2020-07-20T01:50:07.986416Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2020-07-20T01:50:08.000603Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)
2020-07-20T01:50:08.000610Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)
2020-07-20T01:50:08.262922Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20-0ubuntu0.20.04.1) starting as process 1608
2020-07-20T01:50:08.281623Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-07-20T01:50:08.322464Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2020-07-20T01:50:08.322818Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2020-07-20T01:50:08.322947Z 1 [ERROR] [MY-012594] [InnoDB] If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2020-07-20T01:50:08.323017Z 1 [ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation
2020-07-20T01:50:08.323105Z 1 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.
2020-07-20T01:50:08.972320Z mysqld_safe mysqld from pid file /var/lib/mysql/LB-HP-LT.pid ended

And the permissions for /var/lib/mysql

sarcasticsnark@LB-HP-LT:/var/lib/mysql$ cd /var/lib
sarcasticsnark@LB-HP-LT:/var/lib$ sudo ls -l mysql
[sudo] password for sarcasticsnark: 
total 58048
-rw-r----- 1 mysql mysql   196608 Jul 19 16:34 '#ib_16384_0.dblwr'
-rw-r----- 1 mysql mysql  8585216 Jul 11 22:54 '#ib_16384_1.dblwr'
drwxr-x--- 2 mysql mysql     4096 Jul 19 16:35 '#innodb_temp'
drwxr-x--- 2 mysql mysql     4096 Jul 15 18:06  FMS_development
drwxr-x--- 2 mysql mysql     4096 Jun 20 09:04  TestingGround_development
drwxr-x--- 2 mysql mysql     4096 Jun 22 20:07  TestingGround_test
-rw-r----- 1 mysql mysql       56 Jun 10 17:43  auto.cnf
-rw-r----- 1 mysql mysql   210461 Jul 15 17:01  binlog.000008
-rw-r----- 1 mysql mysql      179 Jul 15 17:30  binlog.000009
-rw-r----- 1 mysql mysql      156 Jul 15 17:43  binlog.000010
-rw-r----- 1 mysql mysql     2798 Jul 19 15:55  binlog.000011
-rw-r----- 1 mysql mysql      179 Jul 19 15:56  binlog.000012
-rw-r----- 1 mysql mysql      179 Jul 19 16:11  binlog.000013
-rw-r----- 1 mysql mysql      179 Jul 19 16:25  binlog.000014
-rw-r----- 1 mysql mysql      179 Jul 19 16:27  binlog.000015
-rw-r----- 1 mysql mysql      179 Jul 19 16:27  binlog.000016
-rw-r----- 1 mysql mysql      179 Jul 19 16:34  binlog.000017
-rw-r----- 1 mysql mysql      160 Jul 19 16:27  binlog.index
-rw------- 1 mysql mysql     1680 Jun 10 17:43  ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 Jun 10 17:43  ca.pem
-rw-r--r-- 1 mysql mysql     1112 Jun 10 17:43  client-cert.pem
-rw------- 1 mysql mysql     1680 Jun 10 17:43  client-key.pem
-rw-r--r-- 1 mysql mysql        0 Jun 12 15:54  debian-5.7.flag
drwxr-xr-x 2 mysql mysql     4096 Jun 10 17:43  mysql
-rw-r----- 1 mysql mysql 25165824 Jul 19 16:28  mysql.ibd
drwxr-x--- 2 mysql mysql     4096 Jun 10 17:43  performance_schema
-rw------- 1 mysql mysql     1680 Jun 10 17:43  private_key.pem
-rw-r--r-- 1 mysql mysql      452 Jun 10 17:43  public_key.pem
-rw-r--r-- 1 mysql mysql     1112 Jun 10 17:43  server-cert.pem
-rw------- 1 mysql mysql     1676 Jun 10 17:43  server-key.pem
drwxr-x--- 2 mysql mysql     4096 Jun 10 17:43  sys
-rw-r----- 1 mysql mysql 12582912 Jul 19 16:34  undo_001
-rw-r----- 1 mysql mysql 12582912 Jul 19 16:34  undo_002
Solution

  • mysql user is looking for a home directory, which seems to have not been assigned. To do that, you can execute:

    sudo systemctl stop mysql.service
sudo usermod -d /var/lib/mysql/ mysql
sudo systemctl start mysql.service

    or

    sudo service mysql stop
sudo usermod -d /var/lib/mysql/ mysql
sudo service mysql start

    Based on a response found here