Moving MySQL datadir on development machine

Question

Operating System: Ubuntu 18.04
MySQL version: Ver 8.0.19 for Linux on x86_64 (MySQL Community Server - GPL)

My system is set-up so that Ubuntu is running on a 128gb SSD, with a separate 1TB SSD for storage. This system is dual-boot with Windows 10 Pro and Ubuntu 18.04. The storage drive is NTFS formatted.

I would like to move the MySQL datadir over to the storage drive, because my OS drive isn't big enough. I've been following the tutorial on this URL: https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04

I've gotten to "Step 4 — Restarting MySQL" in the tutorial. Up to this point, I've followed everything to a T, no missed commas, forward slashes, etc. All done exactly as described (I've checked to make sure this is true numerous times)

When I enter the terminal command

sudo systemctl start mysql

I get the following output

Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

Following this output, I enter the following command into the terminal

systemctl status mysql.service

and receive the following output

● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Tue 2020-03-31 22:36:46 CDT; 38s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 7368 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
  Process: 7316 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 7368 (code=exited, status=1/FAILURE)
   Status: "Server startup in progress"
    Error: 13 (Permission denied)

Mar 31 22:36:46 user-desktop mysqld[7368]: 2020-04-01T03:36:46.436713Z 0 [Warning] [MY-010091] [Server] Can't create test file /media/user/storage/mysql_datadir/mysql/mysqld_tmp_file_case_insensitive_test.lower-test
Mar 31 22:36:46 user-desktop mysqld[7368]: 2020-04-01T03:36:46.436796Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19) starting as process 7368
Mar 31 22:36:46 user-desktop mysqld[7368]: 2020-04-01T03:36:46.439912Z 0 [Warning] [MY-010091] [Server] Can't create test file /media/user/storage/mysql_datadir/mysql/mysqld_tmp_file_case_insensitive_test.lower-test
Mar 31 22:36:46 user-desktop mysqld[7368]: 2020-04-01T03:36:46.439923Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /media/user/storage/mysql_datadir/mysql/ is case insensitive
Mar 31 22:36:46 user-desktop mysqld[7368]: 2020-04-01T03:36:46.439961Z 0 [ERROR] [MY-013276] [Server] Failed to set datadir to '/media/user/storage/mysql_datadir/mysql/' (OS errno: 13 - Permission denied)
Mar 31 22:36:46 user-desktop mysqld[7368]: 2020-04-01T03:36:46.440049Z 0 [ERROR] [MY-010119] [Server] Aborting
Mar 31 22:36:46 user-desktop mysqld[7368]: 2020-04-01T03:36:46.440146Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19)  MySQL Community Server - GPL.
Mar 31 22:36:46 user-desktop systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Mar 31 22:36:46 user-desktop systemd[1]: mysql.service: Failed with result 'exit-code'.
Mar 31 22:36:46 user-desktop systemd[1]: Failed to start MySQL Community Server.

Seeing that this was a permissions error (which should have been avoided using the -av tags when copying the original directory to the new drive) I entered in the following command to give open permissions to the entire drive.

sudo chmod a+rwx /media/user/storage/

to check the permissions I enter

ls -ls /media/user/storage/mysql_datadir

with output

total 12
12 drwxrwxrwx 1 root root 12288 Mar 31 02:45 mysql

I've also made sure the full file path to the new datadir is lowercase to avoid conflict between the two file systems.

I still cannot get MySQL to start. I'd appreciate any help. Thank you in advance.

Edit:

namei -mo /media/user/storage/mysql_datadir


f: /media/user/storage/mysql_datadir
 drwxr-xr-x root root /
 drwxr-xr-x root root media
 drwxr-x--- root root user
 drwxrwxrwx root root storage
 drwxrwxrwx root root mysql_datadir

Answer 1

I had the same problem/solution as Pratik, after following the instructions to define a new datadir in

/etc/mysql/mysql.conf.d/mysqld.conf and /etc/apparmor.d/usr.sbin.mysqld

I modified my fstab to mount to

/mnt

instead of

/media/[username]

and mysql start up without any errors. no additional permissions to set other than the mysql directory. My issue was on Ubuntu 18.04 must be something special about the /media mount point

Answer 2

Based on your post seems that you are having permission issue along with owner of the "data directory" must always be mysql.

STOP your mysql service if it's running.

Consider below example: my.cnf looks:

#
# Original path change to avoid space issue on server
#
datadir=/var/lib/mysql

Now you change this datadir in your my.cnf file like:

#
# Original path change to avoid space issue on server
#
#datadir=/var/lib/mysql
datadir=/media/user/storage/mysql_datadir

After this check the permission:

cd /media/user/storage/mysql_datadir

ls -al

output:

drwxr-x--x 40 mysql mysql 4096 Mar 27 00:01 mysql

If this is not output then execute below command:

chmod -Rf 0751 /media/user/storage/mysql_datadir

chown -Rf mysql:mysql /media/user/storage/mysql_datadir

Finally START your mysql service now.