MySQL 8.0 Ubuntu server failed to start after moving datadir

Question

I have a MySQL server on Ubuntu (18.04). The database is growing and I add another disk (sdb1) to the system. The server failed to start after moving the datadir.

The new disk is mounted at /mnt/tb. I tried to move MySQL datadir to the new disk, so I shutdown the server and copied the directory with permissions: cp -rp /var/lib/mysql /mnt/tb/. I also edited /etc/mysql/mysql.conf.d/mysqld.cnf so datadir is the new path.

However I can't start the server with the new directory. If I switch back to the /var/lib/mysql path, the server can start. The two directories have identical user/group read/write permissions. The Ubuntu server has no SELinux installed.

Here's my error.log with 2 attempts to start the server. The first succeeded with /var/lib/mysql and the second failed with /mnt/tb/mysql. I read the OS errno 13 - Permission denied before. I thought it was because it can't find binlog files so I added log_bin_basename = /mnt/tb/mysql/binlog in the cnf file. It still cant' find binlog.index.

2020-07-24T09:13:36.223195Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2020-07-24T09:13:36.223250Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21) starting as process 3249
2020-07-24T09:13:40.341593Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-07-24T09:14:21.929887Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-07-24T09:14:22.355022Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2020-07-24T09:14:22.464814Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-07-24T09:14:22.464999Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-07-24T09:14:22.484964Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.21'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
2020-07-24T09:17:10.464004Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.21).
2020-07-24T09:17:34.911849Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21)  MySQL Community Server - GPL.
mysqld: File './binlog.index' not found (OS errno 13 - Permission denied)
2020-07-24T09:19:08.547269Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2020-07-24T09:19:08.547324Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21) starting as process 3480
2020-07-24T09:19:08.550104Z 0 [Warning] [MY-010091] [Server] Can't create test file /mnt/tb/mysql/mysqld_tmp_file_case_insensitive_test.lower-test
2020-07-24T09:19:08.550117Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /mnt/tb/mysql/ is case insensitive
2020-07-24T09:19:08.550686Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-07-24T09:19:08.550814Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21)  MySQL Community Server - GPL.

I don't know which part went wrong. Thanks for any suggestion!

Answer 1

The problem that prevents MySQL from initializing is AppArmor, a Mandatory Access Control (MAC) system like SELinux. There are 3 ways to deal with it:

1. modify /etc/apparmor.d/usr.sbin.mysqld, change /var/lib/mysql to the custom path, then reload apparmor

2. disable apparmor for mysql

    sudo ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/
    apparmor_parser -R /etc/apparmor.d/disable/usr.sbin.mysqld
   

3. disable apparmor completely

   sudo systemctl disable apparmor