Storage location aanpassen (MySQL): verschil tussen versies
(Nieuwe pagina aangemaakt met 'Ik wil databases voortaan in m'n Dropbox-account opslaan. Komt-ie: <pre> sudo service mysql stop sudo cp -rap /var/lib/mysql /home/strompf/Dropbox/.hp2013/mysql su...') |
|||
(54 tussenliggende versies door dezelfde gebruiker niet weergegeven) | |||
Regel 1: | Regel 1: | ||
− | + | Aantrekkelijk idee om MySQL-bestanden op te slaan in m'n Dropbox, maar vermoedelijk gaat dat niet werken ivm. allerlei bestandsrechten-issues. | |
+ | |||
+ | == Oorspronkelijke situatie == | ||
+ | |||
+ | <pre> | ||
+ | mysql> show variables like "datadir"; | ||
+ | |||
+ | +----------------+-----------------+ | ||
+ | | Variable_name | Value | | ||
+ | +----------------+-----------------+ | ||
+ | | datadir | /var/lib/mysql/ | | ||
+ | +----------------+-----------------+ | ||
+ | </pre> | ||
+ | |||
+ | Bestand <code>/etc/mysql/mysql.conf.d/mysqld.cnf</code>: | ||
<pre> | <pre> | ||
− | + | ... | |
− | + | [mysqld] | |
− | + | ... | |
+ | datadir = /var/lib/mysql ← Zonder schuine streep, zonder " | ||
</pre> | </pre> | ||
− | + | == 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 <code>-p</code> 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 <code>/etc/apparmor.d/tunables/alias</code> - Vergeet de komma niet! | ||
+ | |||
+ | alias /var/lib/mysql -> home/strompf/Dropbox/.mysql/storage, | ||
+ | |||
+ | Bestand <code>/etc/apparmor.d/usr.sbin.mysqld</code> - Voeg nieuwe paden toe. | ||
+ | |||
+ | Na deze wijzigingen moet je AppArmor herstarten. Er wordt oa. <code>sudo /etc/init.d/apparmor reload</code> gesuggereerd, maar wat voor mij beslist werkte; | ||
+ | |||
+ | sudo service apparmor reload | ||
+ | |||
+ | === Mysql herstarten === | ||
sudo service mysql start | 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 === | ||
+ | |||
+ | {| | ||
+ | |[[file:20180519-1700.png|thumb|Datadir-variabele verwijst naar niet-standaardlocatie]] | ||
+ | |[[file:20180519-1701.png|thumb|De bovenliggende map ''.mysql'' is van mij]] | ||
+ | |[[file:20180519-1702.png|thumb|De eigenlijke storage-map: 700 & ''mysql/mysql'' - Precies wat ik online als advies vond]] | ||
+ | |[[file:20180519-1703.png|thumb|En de rechten binnen de map ''storage'']] | ||
+ | |} | ||
+ | |||
+ | === journalctl & AppArmor === | ||
+ | |||
+ | Toch wilde MySQL niet starten. Uitvoer <code>journalctl -xe</code>: | ||
+ | |||
+ | <pre> | ||
+ | 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 | ||
+ | </pre> | ||
+ | |||
+ | ''' 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 ''' | ||
+ | |||
+ | <pre> | ||
+ | # 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, | ||
+ | </pre> | ||
+ | |||
+ | gevolgd door <code>sudo service apparmor reload</code> en <code>sudo service mysql start</code> | ||
+ | |||
+ | ''' Nieuwe ronde - nieuwe fouten ''' | ||
+ | |||
+ | <pre> | ||
+ | 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 | ||
+ | </pre> | ||
+ | |||
+ | Toevoeging ''/etc/apparmor.d/usr.sbin/mysqld'': <code>/sys/devices/system/node/node0/meminfo r,</code> 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 <code>[mysqld]</code> 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.: | ||
+ | |||
+ | <pre> | ||
+ | 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 | ||
+ | </pre> | ||
+ | |||
+ | ''' 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 [https://askubuntu.com/questions/916009/mysql-wont-start-because-of-apparmor deze] zegt echter iemand: | ||
+ | |||
+ | <pre> | ||
+ | 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 | ||
+ | </pre> | ||
+ | |||
+ | 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.: | ||
+ | |||
+ | <pre> | ||
+ | /etc/init.d/apparmor stop | ||
+ | /etc/init.d/apparmor teardown | ||
+ | etc/init.d/apparmor status | ||
+ | apt install apparmor-utils | ||
+ | aa-complain /usr/sbin/mysqld | ||
+ | </pre> | ||
+ | |||
+ | == Zie ook == | ||
+ | |||
+ | * [[Configuratiebestanden (MySQL)]] | ||
+ | * [[Cp | cp]] | ||
+ | |||
+ | == 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 |
Huidige versie van 21 jul 2020 om 06:31
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