MySQL Servers

MySQL database servers

Je kunt pvw vragen een MySQL database voor jou te maken op (een van) de departementale database servers.

Geef hierbij zeker de naam voor de database maar ook de namen van de MySQL accounts (zie hieronder) die je wil gebruiken. Geef ook aan vanop welke machines je die database wil gebruiken en hoe (PHP script, perl, ...).
Voor de cs-info map hebben we nog volgende gegevens nodig:

  • mail alias van de verantwoordelijke voor de database
  • einddatum tot wanneer de database moet bewaard blijven
  • project waarvoor de database is aangevraagd

Voorbeeld:

  • Database naam: Voorbeeld
  • Bijhorende accounts:
    • mijnroot
      • Toegang nodig vanop: localhost (dus met phpmyadmin)
      • Toegangsrechten: Select, Insert, Update, Delete, Create, Drop, Index, Alter
    • mijnphp
      • Toegang nodig vanop: externe web server (vanuit php scripts)
      • Toegangsrechten: Select

MySQL accounts

Je hebt per database typisch 1 gebruiker die alle rechten heeft en 1 of meerdere gebruikers die minder rechten hebben ... zo maak je een `root' account per database voor database beheer en accounts voor `normaal' gebruik (vanuit scripts of zo)

De alle-rechten account heeft alle rechten behalve Grant en References op de (tabellen van de) betreffende database (en geen op de andere databases).

De beperkte-rechten account(s) (typisch gebruikt vanuit een PHP script of iets dergelijks) hebben typisch enkel de Select, Insert, Update en Delete rechten en geen andere (soms zelfs enkel Select ;-)

De toegangsrechten zijn bovendien afhankelijk vanaf welke machine je connecteert. Meestal heeft een account slechts toegang vanaf 1 welbepaalde machine.
Vaak is dat `localhost' vermits je zowiezo weinig aan kunt vangen met een MySQL account, als de database machine zelf onbereikbaar is ;-)
Accounts die vanuit/door een web server gebruikt worden, kunnen doorgaans enkel vanaf die web server connecteren, om evidente security redenen.

MySQL accounts en toegang tot database testen/controleren

Als je interactieve toegang hebt (bijvb. met ssh) tot de machine van waarop een MySQL account kan connecteren, kan je die toegang controleren met het mysql commando lijn commando zoals hieronder beschreven.

Een MySQL account die toegang heeft vanop de externe web server, kan je controleren met deze test web pagina.

Meer informatie over (MySQL) toegangsrechten en hoe we die gebruiken op DeptCW vind je hier.

MySQL database repairen

Als er problemen (geweest) zijn, kan het voorvallen dat een tabel gecrasht is. MySQL kan die tabel dan niet meer gebruiken.

Dat wordt gemeld bij het opstarten in syslog, maar ook bij elk mysql commando dat die tabel probeert te gebruiken en ook te zien in de phpmyadmin interface.

Je moet dan eerst de tabel herstellen:

  • controleer met:
    /usr/bin/mysqlcheck --check -p --all-databases
    welke tabellen er allemaal gecrasht zijn ... dit is het gemakkelijkste te vinden door de uitvoer in een bestand te steken en dan er de gecrashte uit te greppen:
    grep -v ' OK$' /tmp/mysql-check
  • herstel die bewuste tabellen dan met:
    /usr/bin/mysqlcheck --repair -p database-naam tabel-naam
    Je kunt meer dan 1 tabel-naam opgeven, maar die moeten dan natuurlijk allemaal in dezelfde database zitten vermits je maar 1 database-naam kunt opgeven.

Enkele nuttige opties:

  • De -p optie moet je gebruiken om het paswoord te kunnen ingeven, zonder die optie wordt geprobeerd te connecteren _zonder_ paswoord.
  • Je kunt ook een -u optie opgeven om de mysql account waarmee geconnecteerd moet worden, op te geven.
  • Default wordt localhost gebruikt, maar je kunt een andere machine opgeven met de -h optie.
  • Er is een optie --auto-repair die automatically repairs when checking ... use with caution of course.
  • Gebruik de optie --help als je nog meer info wil ;-)

MySQL tabel van database repairen

  • Om enkel een tabel van een databank te herstellen, moet eerst de dump van de volledige databank van backup gehaald worden, vermits enkel de databank (/localhost/mysqldump/databases/*) gebackupt wordt en NIET de tabellen afzonderlijk (/var/lib/mysql/*/*).
  • Daarna kan de tabel uit de dump van de databank gecopieerd worden. Gebruik hiervoor awk en selecteer alle lijnen beginnend met 'Table structure for table .xxx.' tot de volgende 'Table structure for table' waarbij xxx de naam is van de tabel.
  • En kan de tabel hersteld worden in de databank.
  • voorbeeld: herstel de tabel publications2 uit nalag

    zcat nalag.gz | \
    awk '/Table structure for table .publications2./,/Table structure for table .publications2_22012011./{print}' > /tmp/nalag.publications2
    mysql -p nalag < /tmp/nalag.publications2

MySQL database beheren

Een MySQL database beheer je met een web interface of met de mysql commando lijn client.

Web-GUI interface

De web interface bereik je via:

https://database-server/phpmyadmin/

(merk op, dat is https, en niet http ;-)

Hiermee kun je vrijwel alle noodzakelijke manipulaties op de database en de tabellen doen. De web interface is feitelijk een GUI om MySQL databases te beheren.

Voorwaarde om deze web interface te kunnen gebruiken, is dat de account waarmee je connecteert, toegang heeft vanaf `localhost'. Het is immers de web interface die de database connectie doet en die doet dat vanaf localhost.
Als je de web interface wil gebruiken, geef je dit best direkt op, bij het aanvragen van de database, zodat pvw hiervoor kan zorgen.

Commando lijn interface

Voor de liefhebbers worden hieronder enkele elementaire operaties in/met/voor de mysql commando lijn client beschreven:

  • Het paswoord van een user veranderen, zonder in te loggen in mysql:
    mysqladmin -p [ -h <naam van de server> ] [ -u <naam van mysql user> ] password <nieuw paswoord>
    Dat komt om het oud paswoord vragen en als dat correct is, wordt dat paswoord veranderd in het nieuwe (dat meegegeven wordt op de commando lijn ;-)

  • Connecteren met een mysql database op een mysql server:
    mysql -p [ -h <naam van server> ] [ -u <naam van mysql user> ] [ <naam van database> ]
    Door de -p optie wordt er om het paswoord gevraagd ... zonder die optie wordt verondersteld dat je zonder paswoord wil inloggen, wat natuurlijk niet kan op onze mysql servers ;-) Zoals je ziet is alles optioneel, behalve die -p optie.

  • Het paswoord veranderen met een SQL commando:
    set password = password('<nieuw paswoord>');
    Als je ergens een geëncrypteerd paswoord van hebt (uit een andere MySQL database of zo), kun je ook rechtstreeks dit gebruiken:
    set password = '<nieuw crypt paswoord>';
  • Zien welke databases er allemaal zijn op een mysql database server:
    show databases;
  • Een nieuwe database creëeren:
    create database <naam van database>;
  • Een bepaalde database gebruiken (anders moet je een tabel steeds benoemen met zijn volledige naam: <database-naam>.<tabel-naam>):
    use <naam van database>;
  • Zien welke tabellen er allemaal zijn in een mysql database:
    show tables;
  • Een nieuwe tabel creëeren: zie mysql documentatie vermits dit nogal complex is: kolom definities/types/default/... ;-)

  • Een record toevoegen in een tabel (e.g. een user toevoegen in de mysql.user tabel):
    insert user (host,user) values ("10.0.37.%","m1234567");
    Voor de kolommen die geen expliciete waarde krijgen, wordt de default kolom-waarde genomen.

  • Een veld in een record van waarde veranderen (in dit geval het paswoord veld, waarvoor een speciale functie moet gebruikt worden om de in te vullen waarde te encrypteren):
    update user set password=password('**********') where user='m1234567';
  • Een record in een tabel wegdoen:
    delete from user where user='m1234567';
  • Typische acties voor de mysql root account:

    • Ofwel onderstaande commando lijn commando's, ofwel phpmyadmin waarin je hetzelfde kunt opzoeken/vinden/doen via een web browser.

    • Een nieuwe user initialiseren : toegang van 1 bepaalde machine op 1 bepaalde database:
      (Dit vervangt bovenstaande primitieve operaties van user manueel toe te voegen in de user en db tabellen ;-)
      GRANT ALL PRIVILEGES
        ON <naam van database>.*
        TO <naam van user>@<naam van machine>
        IDENTIFIED BY '<paswoord>';

      Kijk na dit commando na of alles wel goed is zoals je wil in de mysql.user en mysql.db tabellen ;-)

      Eventueel neem je bepaalde rechten terug af met iets in de aard van:

      REVOKE Create,Drop,Grant,References,Index,Alter
        ON <naam van database>.*
        FROM <naam van user>@<naam van machine>;

      Uiteraard kun je i.p.v. eerste ALL PRIVILEGES te geven en dan terug af te pakken, ook gewoon geven wat je weet dat nodig is:

      GRANT Select, Insert, Update, Delete ON ... TO ...
    • Na veranderingen aan toegangsrechten de nieuwe privileges effectief doorvoeren:
      flush privileges;

      (dit komt overeen met Reload MySQL in phpmyadmin)

    • Als een account het GRANT privilege heeft, kan hij zelf zijn eigen privileges verder geven aan andere accounts ... hij kan uiteraard nooit privileges geven die hij zelf niet heeft. In principe geven we dat GRANT privilege niet per default ... dat zit bovendien niet inbegrepen in de ALL PRIVILEGES van hierboven, het GRANT recht moet apart gegeven worden.

    • Om te zien wie welke privileges op een database heeft:
      select * from db where db like '<naam van database>';
    • Om te zien welke privileges een account heeft:
      select * from db where user like '<naam van user>';