root@ubuntu:/# mount /dev/sdb1 /mnt/usb
I measured the SSD with sysbench and mounted it as an external hard drive.
root@ubuntu:/mnt/usb/sysbench-master# rsync -av /var/lib/mysql /mnt/usb
And I installed sysbench in /mnt/usb and copied mysql DB.
root@ubuntu:/mnt/usb# chown -R mysql:mysql mysql
root@ubuntu:/mnt/usb# ls -al
total 2232
drwxr-xr-x 4 root root 60 Aug 18 10:01 .
drwxr-xr-x 3 root root 4096 Aug 18 09:55 ..
-rw-r--r-- 1 root root 2272688 Aug 18 09:57 master.zip
drwx------ 6 mysql mysql 4096 Aug 18 09:31 mysql
drwxr-xr-x 14 root root 4096 Aug 18 10:00 sysbench-master
And I changed the entire mysql folder to mysql privileges.
root@ubuntu:/mnt/usb# systemctl status mysql.service
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2020-08-18 09:24:40 KST; 38min ago
Main PID: 5622 (mysqld)
CGroup: /system.slice/mysql.service
└─5622 /usr/sbin/mysqld
Mysql is running and I have paused to fix the vi code.
root@ubuntu:/mnt/usb# systemctl stop mysql
root@ubuntu:/mnt/usb# systemctl status mysql.service
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: inactive (dead) since Tue 2020-08-18 10:04:36 KST; 11s ago
Main PID: 5622 (code=exited, status=0/SUCCESS)
Check mysql stop
vi /etc/mysql/mysql.conf.d/mysqld.cnf
Go to /mnt/usb vi code
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
#datadir = /var/lib/mysql
datadir = /mnt/usb/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
I modified the datadir part.
root@ubuntu:/mnt/usb# cd /var/lib
root@ubuntu:/var/lib# cp mysql mysql-backup -r
root@ubuntu:/var/lib# rm mysql -rf
ln -s /mnt/usb/mysql /var/lib/mysql
root@ubuntu:/var/lib# ls -al | grep mysql
root@ubuntu:/var/lib# chown -R mysql:mysql mysql
This is the process of removing the existing local DB, creating a symbolic link, and changing its privileges to mysql.
lrwxrwxrwx 1 mysql mysql 14 Aug 18 10:12 mysql -> /mnt/usb/mysql
drwx------ 6 root root 4096 Aug 18 10:07 mysql-backup
drwx------ 2 mysql mysql 4096 Aug 18 09:24 mysql-files
drwx------ 2 mysql mysql 4096 Aug 18 09:24 mysql-keyring
drwxr-xr-x 2 root root 4096 Jul 21 00:30 mysql-upgrade
like this.
root@ubuntu:/var/lib# cd /etc/apparmor.d
root@ubuntu:/etc/apparmor.d# cp usr.sbin.mysqld usr.sbin.mysqld-backup
root@ubuntu:/etc/apparmor.d# vi usr.sbin.mysqld
Change the mysqld vi code of apparmor.
# Allow data dir access
# /var/lib/mysql/ r,
# /var/lib/mysql/** rwk,
/mnt/usb/mysql/ r,
/mnt/usb/mysql/** rwk,
Add /mnt/usb related part to allow data access space and comment out the existing one.
root@ubuntu:/etc/apparmor.d# cd /etc/apparmor.d/tunables
root@ubuntu:/etc/apparmor.d/tunables# vi alias
Modify apparmor's alias.
# alias /var/lib/mysql/ -> /home/mysql/,
alias /ver/lib/mysql/ -> /mnt/usb/mysql/,
I added it to the bottom line.
root@ubuntu:/etc/apparmor.d/tunables# /etc/init.d/apparmor reload
[ ok ] Reloading apparmor configuration (via systemctl): apparmor.service.
root@ubuntu:/etc/apparmor.d/tunables# systemctl start mysql
Reloaded apparmor profile and restarted mysql service.
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.
However, the mysql service does not start when such a message appears.
root@ubuntu:/etc/apparmor.d/tunables# systemctl status mysql.service
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: activating (start-post) (Result: exit-code) since Tue 2020-08-18 10:20:07 KST; 17s ago
Process: 7368 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
Process: 7360 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Main PID: 7368 (code=exited, status=1/FAILURE); : 7369 (mysql-systemd-s)
CGroup: /system.slice/mysql.service
└─control
├─7369 /bin/bash /usr/share/mysql/mysql-systemd-start post
└─7468 sleep 1
Aug 18 10:20:07 ubuntu systemd[1]: Starting MySQL Community Server...
Aug 18 10:20:08 ubuntu systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
The status of mysql.
How should I solve this problem? And if you need additional code, I'll add it above.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysql.service has failed.
--
-- The result is failed.
Aug 18 14:48:52 ubuntu systemd[1]: mysql.service: Unit entered failed state.
Aug 18 14:48:52 ubuntu systemd[1]: mysql.service: Failed with result 'exit-code'.
Aug 18 14:48:52 ubuntu systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Aug 18 14:48:52 ubuntu systemd[1]: Stopped MySQL Community Server.
-- Subject: Unit mysql.service has finished shutting down
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysql.service has finished shutting down.
Aug 18 14:48:52 ubuntu systemd[1]: Starting MySQL Community Server...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysql.service has begun starting up.
Aug 18 14:48:52 ubuntu audit[5176]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/mnt/usb/mysql/ubuntu.lower-test" pid=5176 comm="mysqld" requested_mask="c" denie
Aug 18 14:48:52 ubuntu audit[5176]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/mnt/usb/mysql/ubuntu.lower-test" pid=5176 comm="mysqld" requested_mask="c" denie
Aug 18 14:48:52 ubuntu audit[5176]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/mnt/usb/mysql/ubuntu.lower-test" pid=5176 comm="mysqld" requested_mask="c" denie
Aug 18 14:48:52 ubuntu kernel: audit: type=1400 audit(1597729732.633:2173): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/mnt/usb/mysql/ubuntu.lower-test" pid=5176 co
Aug 18 14:48:52 ubuntu kernel: audit: type=1400 audit(1597729732.637:2174): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/mnt/usb/mysql/ubuntu.lower-test" pid=5176 co
Aug 18 14:48:52 ubuntu kernel: audit: type=1400 audit(1597729732.637:2175): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/mnt/usb/mysql/ubuntu.lower-test" pid=5176 co
Aug 18 14:48:52 ubuntu audit[5176]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/mnt/usb/mysql/ibdata1" pid=5176 comm="mysqld" requested_mask="wr" denied_mask="wr
Aug 18 14:48:52 ubuntu kernel: audit: type=1400 audit(1597729732.657:2176): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/mnt/usb/mysql/ibdata1" pid=5176 comm="mysqld"
Aug 18 14:48:53 ubuntu systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Aug 18 14:49:22 ubuntu systemd[1]: Failed to start MySQL Community Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysql.service has failed.
--
-- The result is failed.
Aug 18 14:49:22 ubuntu systemd[1]: mysql.service: Unit entered failed state.
Aug 18 14:49:22 ubuntu systemd[1]: mysql.service: Failed with result 'exit-code'.
Aug 18 14:49:22 ubuntu systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
Aug 18 14:49:22 ubuntu systemd[1]: Stopped MySQL Community Server.
-- Subject: Unit mysql.service has finished shutting down
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysql.service has finished shutting down.
Aug 18 14:49:22 ubuntu systemd[1]: Starting MySQL Community Server...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit mysql.service has begun starting up.
Aug 18 14:49:23 ubuntu audit[5354]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/mnt/usb/mysql/ubuntu.lower-test" pid=5354 comm="mysqld" requested_mask="c" denie
Aug 18 14:49:23 ubuntu kernel: audit: type=1400 audit(1597729763.134:2177): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/mnt/usb/mysql/ubuntu.lower-test" pid=5354 co
Aug 18 14:49:23 ubuntu audit[5354]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/mnt/usb/mysql/ubuntu.lower-test" pid=5354 comm="mysqld" requested_mask="c" denie
Aug 18 14:49:23 ubuntu audit[5354]: AVC apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/mnt/usb/mysql/ubuntu.lower-test" pid=5354 comm="mysqld" requested_mask="c" denie
Aug 18 14:49:23 ubuntu kernel: audit: type=1400 audit(1597729763.138:2178): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/mnt/usb/mysql/ubuntu.lower-test" pid=5354 co
Aug 18 14:49:23 ubuntu kernel: audit: type=1400 audit(1597729763.138:2179): apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" name="/mnt/usb/mysql/ubuntu.lower-test" pid=5354 co
Aug 18 14:49:23 ubuntu audit[5354]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/mnt/usb/mysql/ibdata1" pid=5354 comm="mysqld" requested_mask="wr" denied_mask="wr
Aug 18 14:49:23 ubuntu kernel: audit: type=1400 audit(1597729763.158:2180): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/mnt/usb/mysql/ibdata1" pid=5354 comm="mysqld"
Aug 18 14:49:23 ubuntu systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Output of journalctl -xe
root@ubuntu:/mnt/usb/mysql# ls -al
total 188496
drwx------ 6 mysql mysql 4096 Aug 18 09:31 .
drwxr-xr-x 4 root root 60 Aug 18 10:01 ..
-rw-r----- 1 mysql mysql 56 Aug 18 09:24 auto.cnf
-rw------- 1 mysql mysql 1676 Aug 18 09:24 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Aug 18 09:24 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Aug 18 09:24 client-cert.pem
-rw------- 1 mysql mysql 1676 Aug 18 09:24 client-key.pem
-rw-r--r-- 1 mysql mysql 0 Aug 18 09:24 debian-5.7.flag
-rw-r----- 1 mysql mysql 431 Aug 18 09:24 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 Aug 18 09:48 ibdata1
-rw-r----- 1 mysql mysql 50331648 Aug 18 09:48 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Aug 18 09:48 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Aug 18 09:24 ibtmp1
drwxr-x--- 2 mysql mysql 8192 Aug 18 09:24 mysql
drwxr-x--- 2 mysql mysql 8192 Aug 18 09:24 performance_schema
-rw------- 1 mysql mysql 1680 Aug 18 09:24 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Aug 18 09:24 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Aug 18 09:24 server-cert.pem
-rw------- 1 mysql mysql 1680 Aug 18 09:24 server-key.pem
drwxr-x--- 2 mysql mysql 8192 Aug 18 09:24 sys
drwxr-x--- 2 mysql mysql 20 Aug 18 09:45 sysbench
Above are the permissions of /mnt/usb/mysql.
root@ubuntu:/var/log/mysql# tail /var/log/mysql/error.log
2020-08-18T06:59:29.409935Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-08-18T06:59:30.012515Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-08-18T06:59:30.012727Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-08-18T06:59:30.012759Z 0 [ERROR] Failed to initialize builtin plugins.
2020-08-18T06:59:30.012804Z 0 [ERROR] Aborting
2020-08-18T06:59:30.012829Z 0 [Note] Binlog end
2020-08-18T06:59:30.012896Z 0 [Note] Shutting down plugin 'MyISAM'
2020-08-18T06:59:30.013257Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
error log
2020-08-19T03:45:12.552678Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2020-08-19T03:45:12.552713Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2020-08-19T03:45:12.699482Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-08-19T03:45:12.699547Z 0 [Warning] Can't create test file /mnt/usb/mysql/ubuntu.lower-test
2020-08-19T03:45:12.699568Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.31-0ubuntu0.16.04.1) starting as process 14230 ...
2020-08-19T03:45:12.701284Z 0 [Warning] Can't create test file /mnt/usb/mysql/ubuntu.lower-test
2020-08-19T03:45:12.701296Z 0 [Warning] Can't create test file /mnt/usb/mysql/ubuntu.lower-test
2020-08-19T03:45:12.702859Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-08-19T03:45:12.702871Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-08-19T03:45:12.702874Z 0 [Note] InnoDB: Uses event mutexes
2020-08-19T03:45:12.702876Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-08-19T03:45:12.702878Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2020-08-19T03:45:12.702880Z 0 [Note] InnoDB: Using Linux native AIO
2020-08-19T03:45:12.703008Z 0 [Note] InnoDB: Number of pools: 1
2020-08-19T03:45:12.703068Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-08-19T03:45:12.703916Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-08-19T03:45:12.708529Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-08-19T03:45:12.709810Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-08-19T03:45:12.720506Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2020-08-19T03:45:12.720573Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2020-08-19T03:45:12.720584Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-08-19T03:45:13.327058Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-08-19T03:45:13.327111Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-08-19T03:45:13.327122Z 0 [ERROR] Failed to initialize builtin plugins.
2020-08-19T03:45:13.327128Z 0 [ERROR] Aborting
2020-08-19T03:45:13.327151Z 0 [Note] Binlog end
2020-08-19T03:45:13.327236Z 0 [Note] Shutting down plugin 'MyISAM'
2020-08-19T03:45:13.327712Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
detail error log
Best Answer
To fix this, you need to give permission in the OS separately.
and