韌館-LearnHouse

Ubuntu 20.04設定datadir改變MySQL資料庫Data路徑

想更改MySQL的資料路徑,想當然的就是修改/etc/mysql/mysql.conf.d/mysqld.cnf
將datadir設定成你要的路徑

//mysqld.cnf
...
datadir=/home2/mysql
...

之前不知道為什麼,直接更改後重啟MySQL都沒有問題,資料也都也寫入到我指定的路徑,但一直到某天突然MySQL一直起不來,而一直出現以下錯誤

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

執行systemctl status mysql.service,也看不出個所以然

● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: failed (Result: exit-code) since Fri 2021-04-30 22:53:39 CST; 4s ago
Process: 8286 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
Process: 8294 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
Main PID: 8294 (code=exited, status=1/FAILURE)
Status: "Server startup in progress"
Error: 13 (拒絕不符權限的操作)

4月 30 22:53:39 yuchin-Revo-M1-601 systemd[1]: Failed to start MySQL Community Server.
4月 30 22:53:39 yuchin-Revo-M1-601 systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
4月 30 22:53:39 yuchin-Revo-M1-601 systemd[1]: Stopped MySQL Community Server.
4月 30 22:53:39 yuchin-Revo-M1-601 systemd[1]: mysql.service: Start request repeated too quickly.
4月 30 22:53:39 yuchin-Revo-M1-601 systemd[1]: mysql.service: Failed with result 'exit-code'.
4月 30 22:53:39 yuchin-Revo-M1-601 systemd[1]: Failed to start MySQL Community Server.

又偏偏不知道為什麼,從/var/log/mysql/error.log下都沒有任何紀錄。根本就無法找出問題,在無奈之際只好重灌並重新安裝MySQL
這次重裝後,只要修改datadir路徑,就直接會出現上述的錯誤;不過好在這次error.log裡有進一步的描述:

mysqld: File './binlog.index' not found (OS errno 13 - Permission denied)
2021-04-29T11:34:46.993237Z 0 [Warning] [MY-010091] [Server] Can't create test file /home2/mysql/mysqld_tmp_file_case_insensitive_test.lower-test
2021-04-29T11:34:46.993452Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23-0ubuntu0.20.04.1) starting as process 2483
2021-04-29T11:34:47.003975Z 0 [Warning] [MY-010091] [Server] Can't create test file /home2/mysql/mysqld_tmp_file_case_insensitive_test.lower-test
2021-04-29T11:34:47.004003Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /home2/mysql/ is case insensitive
2021-04-29T11:34:47.006374Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-04-29T11:34:47.006726Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.23-0ubuntu0.20.04.1) (Ubuntu).

才發現看起來是權限的問題,但就是不知道是什麼權限問題,因為都設成777還是無法解決
網路上解法很多,但都沒有效。後來才發現原來還有一個AppArmor需要設定,修改/etc/apparmor.d/tunables/alias,加入

alias /var/lib/mysql/ -> /home2/mysql/,

重啟AppArmor

sudo systemctl restart apparmor

這樣就能成功啟動囉~

參考資料:https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04

2021年4 月 posted by admin in Linux and have Comment (1)

One Response to “Ubuntu 20.04設定datadir改變MySQL資料庫Data路徑”

  1. xingyuan表示:

    写的太好了,解决了我的朋友。谢谢

Place your comment

Please fill your data and comment below.
名稱:
信箱:
網站:
您的評論: