Storage location aanpassen (MySQL)
Aantrekkelijk idee om MySQL-bestanden op te slaan in m'n Dropbox, maar vermoedelijk gaat dat niet werken ivm. allerlei bestandsrechten-issues.
Oorspronkelijke situatie
mysql> show variables like "datadir"; +----------------+-----------------+ | Variable_name | Value | +----------------+-----------------+ | datadir | /var/lib/mysql/ | +----------------+-----------------+
Bestand /etc/mysql/mysql.conf.d/mysqld.cnf
:
... [mysqld] ... datadir = /var/lib/mysql ← Zonder schuine streep, zonder "
Procedure - Algemeen
Stop MySQL
Verplaatsen schijn het beste te gebeuren zonder werkende server. Daarom:
sudo service mysql stop
Kopiëer database-bestanden naar nieuwe locatie
sudo cp -pr /var/lib/mysql /home/strompf/Dropbox/.mysql/storage
Hierbij zorgt -p
dat er allerlei attributen (eigenaar, groep, etc.) behouden blijven.
Pas my.cnf aan
Nogal vreemd, maar je moet écht eerste die link verwijderen. Anders wil-ie daarna niet opstarten. Vervang 'm daarn door de placeholder waar-ie naar verwijst
sudo rm /etc/mysql/my.cnf sudo cp /etc/mysql/mysql.cnf my.cnf
Open my.cnf:
sudo /etc/mysql/my.cnf
Voeg toe:
datadir = /home/strompf/Dropbox/.mysql/storage
AppArmor Aanpassen
Bestand /etc/apparmor.d/tunables/alias
- Vergeet de komma niet!
alias /var/lib/mysql -> home/strompf/Dropbox/.mysql/storage,
Bestand /etc/apparmor.d/usr.sbin.mysqld
- Voeg nieuwe paden toe.
Na deze wijzigingen moet je AppArmor herstarten. Er wordt oa. sudo /etc/init.d/apparmor reload
gesuggereerd, maar wat voor mij beslist werkte;
sudo service apparmor reload
Mysql herstarten
sudo service mysql start
Voorbeeld - Mei 2018
Dit betreft een installatie op een virtuele computer en op m'n werkstation. De screenshot zijn een combinatie, maar dat mag de pret niet drukken.
So far - so gooed
journalctl & AppArmor
Toch wilde MySQL niet starten. Uitvoer journalctl -xe
:
May 19 17:30:21 dell2016 audit[25569]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/25569/status" pid=25569 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=125 ouid=125 May 19 17:30:21 dell2016 audit[25569]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=25569 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=125 ouid=0 May 19 17:30:21 dell2016 audit[25569]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/25569/status" pid=25569 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=125 ouid=125 May 19 17:30:21 dell2016 kernel: audit: type=1400 audit(1526743821.024:703): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/25569/status" pid=25569 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=125 ouid May 19 17:30:21 dell2016 kernel: audit: type=1400 audit(1526743821.024:704): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=25569 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=1 May 19 17:30:21 dell2016 kernel: audit: type=1400 audit(1526743821.024:705): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/25569/status" pid=25569 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=125 ouid May 19 17:30:21 dell2016 mysqld[25569]: mysqld: Can't change dir to '/home/strompf/Dropbox/.mysql/storage/' (Errcode: 13 - Permission denied) May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.030757Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.030806Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.160228Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.160286Z 0 [Warning] Can't create test file /home/strompf/Dropbox/.mysql/storage/dell2016.lower-test May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.160311Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.22-0ubuntu0.16.04.1) starting as process 25569 ... May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.162106Z 0 [Warning] Can't create test file /home/strompf/Dropbox/.mysql/storage/dell2016.lower-test May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.162118Z 0 [Warning] Can't create test file /home/strompf/Dropbox/.mysql/storage/dell2016.lower-test May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.162391Z 0 [ERROR] failed to set datadir to /home/strompf/Dropbox/.mysql/storage/ May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.162398Z 0 [ERROR] Aborting May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.162416Z 0 [Note] Binlog end May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.162442Z 0 [Note] /usr/sbin/mysqld: Shutdown complete May 19 17:30:21 dell2016 systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Wat ik denk dat het zegt
- mysql mag /proc/25569/status niet lezen
- mysql mag /sys/devices/system/node/ niet lezen
Toevoegingen /etc/apparmor.d/usr.sbin/mysqld
# Strompf - Errors after changing storage location ######################################################## # # * May 2018 # * https://askubuntu.com/questions/916009/mysql-wont-start-because-of-apparmor # * http://wiki.devliegendebrigade.nl/Storage_location_aanpassen_(MySQL)#journalctl_-xe # /proc/*/status r, /sys/devices/system/node/ r,
gevolgd door sudo service apparmor reload
en sudo service mysql start
Nieuwe ronde - nieuwe fouten
May 19 17:58:00 dell2016 kernel: audit: type=1400 audit(1526745480.883:181): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/node0/meminfo" pid=7189 comm="mysqld" requested_mask="r" denied_mask
Toevoeging /etc/apparmor.d/usr.sbin/mysqld: /sys/devices/system/node/node0/meminfo r,
en opnieuw opstarten. Geen foutmeldingen meer, behalve dat mysqld nog steeds geen testbestand kan aanmaken.
Debuggen
systemctl status mysql.service
Dit commando geeft goede info - Helemaal onderaan. Paar voorbeelden:
mysql-systemd-start[10325]: my_print_defaults: [ERROR] Found option without preceding group in config file /etc/mysql/my.cnf at line 30!
Vergeten om [mysqld]
in te voegen.
mysqld: Can't change dir to '/home/strompf/Dropbox/.mysql/storage/' (Errcode: 13 - Permission denied)
De storage-map zelf moet bes
journalctl -xe
Dit commando geeft informatie omtrent processendie door AppArmor worden gestopt. Bv.:
May 19 17:30:21 dell2016 audit[25569]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/25569/status" pid=25569 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=125 ouid=125 May 19 17:30:21 dell2016 audit[25569]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=25569 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=125 ouid=0 May 19 17:30:21 dell2016 audit[25569]: AVC apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/25569/status" pid=25569 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=125 ouid=125 May 19 17:30:21 dell2016 kernel: audit: type=1400 audit(1526743821.024:703): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/25569/status" pid=25569 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=125 ouid May 19 17:30:21 dell2016 kernel: audit: type=1400 audit(1526743821.024:704): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/sys/devices/system/node/" pid=25569 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=1 May 19 17:30:21 dell2016 kernel: audit: type=1400 audit(1526743821.024:705): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/proc/25569/status" pid=25569 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=125 ouid May 19 17:30:21 dell2016 mysqld[25569]: mysqld: Can't change dir to '/home/strompf/Dropbox/.mysql/storage/' (Errcode: 13 - Permission denied) May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.030757Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.030806Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.160228Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.160286Z 0 [Warning] Can't create test file /home/strompf/Dropbox/.mysql/storage/dell2016.lower-test May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.160311Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.22-0ubuntu0.16.04.1) starting as process 25569 ... May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.162106Z 0 [Warning] Can't create test file /home/strompf/Dropbox/.mysql/storage/dell2016.lower-test May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.162118Z 0 [Warning] Can't create test file /home/strompf/Dropbox/.mysql/storage/dell2016.lower-test May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.162391Z 0 [ERROR] failed to set datadir to /home/strompf/Dropbox/.mysql/storage/ May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.162398Z 0 [ERROR] Aborting May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.162416Z 0 [Note] Binlog end May 19 17:30:21 dell2016 mysqld[25569]: 2018-05-19T15:30:21.162442Z 0 [Note] /usr/sbin/mysqld: Shutdown complete May 19 17:30:21 dell2016 systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Wat ik denk dat het zegt
- mysql mag /proc/25569/status niet lezen
- mysql mag /sys/devices/system/node/ niet lezen
Of zegt dit niets?
In deze zegt echter iemand:
Note to future readers: the apparmor denied messages are just warnings, and MySQL is likely exiting for a different reason - see/var/log/mysql/error.log. MySQL should still be able to run even if it cannot access the above /proc and /sys files
In mijn geval (mei 2018) staat er echter niets zinnigs in /var/log/mysql/error.log - Alsof-ie niet eens de kans krijgt om te starten.
/var/log/mysql/error.log (niet!)
Volledigheidshalve nog een keer 't adres van 't logboek. In mijn geval (mei 2018) was deze min-of-meer leeg. Blijkbaar kreeg-ie niet eens de kans dit op te starten.
Nieuwe locatie uit-commentariseren?
Zet een # voor de betreffende regel in /etc/mysql/my.cnf. Doet-ie 't nu wel?
Storage opnieuw kopiëren?
Dit commando kun je zonder probleem herhalen:
sudo cp -pr /var/lib/mysql/* /home/strompf/Dropbox/.mysql/storage
Welke gebruiker is mysql?
Als ik mysql start vanuit su, wordt het dan een andere gebruiker?
AppArmor uitzetten
Bv.:
/etc/init.d/apparmor stop /etc/init.d/apparmor teardown etc/init.d/apparmor status apt install apparmor-utils aa-complain /usr/sbin/mysqld
Zie ook
Bronnen
- https://stackoverflow.com/questions/1795176/how-to-change-mysql-data-directory → Commentaren bij eerste posting uitklappen
- https://askubuntu.com/questions/75059/mysqld-gives-errcode13-because-it-cant-create-test-files
- https://askubuntu.com/questions/790685/cannot-set-a-different-database-directory-for-mysql-errcode-13-permission-d
- https://askubuntu.com/questions/916009/mysql-wont-start-because-of-apparmor