Score:0

How can I move MySQL's default storage location?

cn flag

Background info:

The default installation of MySQL server 8.0 from APT was filling my root partition.

So I recently installed a new drive on my system intended to hold large datasets, including a sizeable MySQL database specifically. I've added the drive to 'fstab', and I've corroborated it gets automatically mounted on start.

My problem:

I wish to move the default 'datadir' to that mounted drive, but I've had no luck following the instructions in the original MySQL documentation. After I perform the mysqld --initialize --user=mysql --datadir=/mnt/new_drive the MySQL error log shows that the created folder is "not usable". I've also attempted the instructions in this Digital Ocean tutorial and this Youtube video with no luck.

Terminal output:

(base) ➜  ~ sudo chown mysql:mysql /mnt/data/mysql/
(base) ➜  ~ sudo mysqld --initialize-insecure --user=mysql --datadir=/mnt/data/mysql/data
2022-12-05T17:53:54.587829Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.31-0ubuntu0.22.04.1) initializing of server in progress as process 31763
2022-12-05T17:53:54.588736Z 0 [ERROR] [MY-010174] [Server] Can't change data directory owner to mysql
2022-12-05T17:53:54.588738Z 0 [ERROR] [MY-013236] [Server] The designated data directory /mnt/data/mysql/data/ is unusable. You can remove all files that the server added to it.
2022-12-05T17:53:54.588782Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-12-05T17:53:54.588920Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.31-0ubuntu0.22.04.1)  (Ubuntu).

Update 1:

After following steps 1 - 5 as described in the question section of this AskUbuntu thread, the mysqld --initialize --user=mysql --datadir=/mnt/new_drive step seems to complete successfully. So the permission issues were probably related to AppArmor. But now the server won't start.

The logs in /var/log/mysql/error.log show the following:

2022-12-05T19:42:05.982975Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.31-0ubuntu0.22.04.1) initializing of server in progress as process 41772
2022-12-05T19:42:06.022751Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-12-05T19:42:06.164363Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-12-05T19:42:07.137874Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: <random password>

And this happens when I try to start the MySQL service.

(base) ➜  ~ sudo service mysql status 
× mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: failed (Result: exit-code) since Mon 2022-12-05 14:46:11 EST; 6s ago
    Process: 42159 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=1/FAILURE)
        CPU: 5ms

Dec 05 14:46:11 Raza systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
Dec 05 14:46:11 Raza systemd[1]: Stopped MySQL Community Server.
Dec 05 14:46:11 Raza systemd[1]: mysql.service: Start request repeated too quickly.
Dec 05 14:46:11 Raza systemd[1]: mysql.service: Failed with result 'exit-code'.
Dec 05 14:46:11 Raza systemd[1]: Failed to start MySQL Community Server.

System Info:

Ubuntu 22.04 MySQL Server 8.0

Andra avatar
tr flag
how do you mount the new drive?
Omig12 avatar
cn flag
I added an entry with the drive's UUID to the 'fstab' file, similar to how it is described in [this article](https://confluence.jaytaala.com/display/TKB/Mount+drive+in+linux+and+set+auto-mount+at+boot).
Omig12 avatar
cn flag
Eventually, I identified the issue. Although the official MySQL documentation suggests a command like such as `mysqld --initialize --user=mysql --datadir=/mnt/data/mysql/data/`, I've been specifying `datadir=/mnt/data/mysql/` in the `/etc/mysql/mysql.conf.d/mysqld.cnf` config file. So for the initialization to go smoothly, I should have used that same data directory `mysqld --initialize --user=mysql --datadir=/mnt/data/mysql/`.
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.