Error after changing/moving mysql datadir in ubuntu sys

Question

I followed the answer in the Q&A Moving the MySQL data directory Ubuntu 16.04.

However, it's not working and gives me the following error:

mysql.service - MySQL Community Server  
  Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)  
  Active: failed (Result: exit-code) since Sat 2023-05-20 01:41:21 CST; 9s ago  
 Process: 41185 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)  
 Process: 41193 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)  
Main PID: 41193 (code=exited, status=1/FAILURE)  
  Status: "Server shutdown complete"  
   Error: 13 (Permission denied)  
evol-System-Product-Name systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.  
evol-System-Product-Name systemd[1]: Stopped MySQL Community Server.  
evol-System-Product-Name systemd[1]: mysql.service: Start request repeated too quickly.  
evol-System-Product-Name systemd[1]: mysql.service: Failed with result 'exit-code'.  
evol-System-Product-Name systemd[1]: Failed to start MySQL Community Server.  

What am I missing?
What have I done wrong?

Answer 1

Complete working procedure of moving MySQL datadir from /var/lib/mysql/ to /srv/data/mysql/

MySQL & server version and datadir path

gesti@asterisktest:~$ sudo lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 22.04.2 LTS
Release:        22.04


mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 8.0.33-0ubuntu0.22.04.2 |
+-------------------------+

mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+

1.Create the new directory for MySQL data

gesti@asterisktest:~$ sudo mkdir /srv/data/mysql

2.Set ownership of new directory to match the existing one

gesti@asterisktest:~$ sudo chown --reference=/var/lib/mysql /srv/data/mysql

3.Set permissions on new directory to match existing one

gesti@asterisktest:~$ sudo chmod --reference=/var/lib/mysql /srv/data/mysql

4.Stop MySQL before copying over files

sudo systemctl stop mysql.service

5.Copy all files in default directory, to new one, retaining perms (-p)

gesti@asterisktest:~$ sudo cp -rp /var/lib/mysql/. /srv/data/mysql/

6.Edit the /etc/mysql/mysql.conf.d/mysqld.cnf file, and under [mysqld] add the following line (note you might have different location or file name):

gesti@asterisktest:~$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
datadir        = /srv/data/mysql

7.Edit apparmor usr.sbin.mysqld file

sudo nano /etc/apparmor.d/usr.sbin.mysqld

Replace,

# Allow data dir access
  /var/lib/mysql/ r,
  /var/lib/mysql/** rwk,

With,

# Allow data dir access
  /srv/data/mysql/ r,
  /srv/data/mysql/** rwk,

8.Restart apparmor

gesti@asterisktest:~$ systemctl restart apparmor.service

9.Start MySQL service

gesti@asterisktest:~$ systemctl start mysql.service

10.Login and verify new datadir

gesti@asterisktest:~$ sudo mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.33-0ubuntu0.22.04.2 (Ubuntu)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@datadir;
+------------------+
| @@datadir        |
+------------------+
| /srv/data/mysql/ |
+------------------+
1 row in set (0.00 sec)