Server Administration

Start Omhoog Stored Routines Triggers Scheduled Events Performance Import and Export Data Storage Engines Transactions Server Administration Users and Access Control Oefeningen Database Schema's

Database administratie

Een database administrator (DBA) heeft een belangrijke positie in een management informatie systeem (MIS) team van de organisatie. Als de persoon belast wordt met de verantwoordelijkheid om te zorgen voor een soepele en efficiŽnte toegang tot netwerk databases, betekent de functieomschrijving dat er gezorgd wordt dat de databank voor de gebruikers en applicaties een uptime heeft van 24/7/365, het uitvoeren van regelmatige back-ups van gegevens om verlies van gegevens te voorkomen in geval van corruptie of verlies of in het geval van een systeemcrash, het afstellen server parameters voor maximale prestaties en het veiligstellen van de database tegen kwaadaardig gedrag en ongeautoriseerde toegang. Zelfs de losse taken op zich kan kan niet eenvoudig worden genoemd; tezamen vormen ze een van de meest veeleisende en uitdagende posities in de industrie.

Server administratie:

  • Het veiligstellen van de MySQL server tegen ongeoorloofd gebruik;
  • het toewijzen van gebruikers privileges die geschikt zijn voor hun beoogde gebruik van het systeem;
    het uitvoeren van regelmatige controles en back-ups van de MySQL databases om gegevens-corruptie of verlies te voorkomen;
  • het optimaliseren van de server zodat het altijd de meest optimale prestaties levert.
  • Algemene taken:

  • Het starten en stoppen van de server;
  • het verkrijgen van de status van de server;
  • wijziging serverconfiguratie;
  • gebruik maken van de MySQL-log-bestanden;
  • gebruik maken van de INFORMATION_SCHEMA database, die run-time toegang tot informatie over database-objecten voorziet.
  • MySQL Uptime

    MySQL biedt maximale betrouwbaarheid en uptime en is getest en gecertificeerd voor gebruik in high-volume en bedrijfskritische applicaties door bedrijven als SAP, Motorola, Sony, Yahoo !, NASA, en HP.

    Aangezien MySQL heeft een open-source geschiedenis, vindt de ontwikkeling van MySQL plaats in de publieke sfeer. Een laatste versie heeft het unieke voordeel dat het uitvoerig is getest door gebruikers over de hele wereld op een verscheidenheid van verschillende platforms en in diverse verschillende omgevingen, voordat het wordt gecertificeerd voor gebruik in productieomgevingen. Deze benadering heeft geresulteerd in een RDBMS die zowel uiterst stabiel en nagenoeg vrij is van fouten.

    Data backup

    Een vastgesteld backup (en herstel) verloop is van cruciaal belang om ervoor te zorgen dat de gegevens die zijn opgeslagen in databases van een organisatie niet beschadigd raken of verloren gaan in het geval van een defect aan een schijf of een systeem crash.

    MySQL wordt geleverd met een aantal instrumenten die ontwikkeld zijn om dit proces te versnellen en efficiŽnter te maken.

    De belangrijkste daarvan is de mysqldump tool, die het mogelijk maakt om MySQL tabelstructuren, tabelgegevens, of beide om back-up bestanden weg te schrijven in een verscheidenheid van verschillende formaten. De output van mysqldump kan worden gebruikt om snel en gemakkelijk een of meerdere MySQL databases te herstellen, hetzij van de command-line met het hulpprogramma mysqlimport of via de LOAD DATA INFILE opdracht (zie "Werken met gegevens in verschillende formaten").

    In het geval van tabelcorruptie, verbetert MySQL de kansen van gegevensherstel door middel van een suite van herstelprogramma's, die goed zijn in het geheel herstellen van beschadigde tabel of het herstellen van de schade op een punt waar het grootste deel van de gegevens hersteld kunnen worden.

    MySQL wordt ook geleverd met ingebouwde replicatie, die het mogelijk maakt om de wijzigingen op een database-server naar andere servers te schrijven met vooraf gedefinieerde master-slave relaties. Eerdere versies van MySQL ondersteunen enkel one-way replicatie; nieuwere versies ondersteunen nu ook replicatie in twee richtingen, voor meer geavanceerde mirroring en load-balancing.

    Beveiliging en toegangscontrole

    MySQL wordt geleverd met een geavanceerd toegangscontrole en privilege systeem om te voorkomen dat onbevoegde gebruikers toegang krijgen tot het systeem. Dit systeem, geimplementeerd als een 'fivetiered' privilege hiŽrarchie, maakt het mogelijk om uitgebreide toegangsregels te maken die MySQL gebruikt bij de beslissing hoe het met bepaalde gebruikerverzoeken behandelt.

  • Verbindingen tot de server zijn alleen toegestaan indien ze overeenkomen met de in de MySQL privilege systeem vastgelegde toegangscriteria regels. Deze toegangsregels kunnen worden bepaald aan de hand van de gebruiker en/of host en kunnen worden gebruikt om de toegang van hosts buiten een bepaald subnetwerk of IP adresbereik te beperken. Verder is een dergelijke verbindingen met de server zijn alleen toegestaan nadat de gebruiker een geldig wachtwoord voorziet.
  • Zodra de verbinding tot stand is gebracht, controleert MySQL elke actie die door een gebruiker wordt uitgevoerd om na te gaan of hij of zij over voldoende rechten bezit om het uit te voeren. Gebruikers kunnen worden beperkt tot het uitvoeren van bewerkingen op uitsluitend specifieke databases of velden en MySQL maakt het zelfs mogelijk om te bepalen welke soort queries een gebruiker kan uitvoeren: aan de database, tabel of veldniveau.
  • De veiligheid van het systeem wordt verder versterkt door het gebruik van een eenrichtings versleutelingschema voor wachtwoorden. Nieuwere versies van MySQL ondersteunen ook Secure Sockets Layer (SSL), die voor meer veiligheid gebruikt kunnen worden om de gegevens die via MySQL client-server verbinding worden verzonden, zoals zoekresultaten.

    Perfomantie optimalisatie

    Om maximale prestaties uit het RDBMS te halen: zorgen de multithreaded engine en verschillende optimalisatie algoritmes dat deze activiteit minder kritisch dan gebruikelijk is. Een out-of-the-box MySQL installatie is meestal razendsnel en vereist zeer weinig aanpassingen.

    MySQL kan worden afgesteld op specifieke behoeften:

  • Om aan de aangepaste eisen te voldoen, kan een aantal interne parameters worden ingesteld via systeemvariabelen.
  • Veel functies die de prestaties degraderen, zoals transacties, referentiŽle integriteitsvoorwaarden en opgeslagen procedures kunnen in- of uitgeschakeld worden naar de beheofte van de gebruiker, waardoor het mogelijk is om een optimale mix van functies en prestaties per applicatie te selecteren.
  • Commando's als ANALYSE TABLE en EXPLAIN SELECT helpen SQL ontwikkelaars in benchmarking queries en het identificeren van knelpunten,
  • Functies zoals de query cache (die werkt door degemeenschappelijke queries in het geheugen op te slaan en vervolgens die gegevens terug te gebruiken om naar de oproepende instantie te sturen, zonder dat de query elke keer opnieuw uitgevoerd moet worden) helpen bij het verbeteren van de prestaties zonder al te veel extra programmering. (Deze kenmerken zijn reeds besproken.)
  • Perfomantie optimalisatie: schaalbaarheid

    Een belangrijk discussiepunt bij de bespreking van de prestaties is de schaalbaarheid. Te veel database-systemen werken extreem goed met enkele duizenden records, maar zien een aanzienlijke daling van de prestaties wanneer het aantal records in de miljoenen loopt.

    MySQL is gebouwd zeer schaalbaar te zijn, en kan zeer grote en complexe databases (tabellen van meerdere gigabytes met honderdduizenden records) verwerken zonder te moeten inleveren in capaciteit. Dit maakt MySQL geschikt voor alles, variŽrend van eenvoudige inhoud-gebaseerde websites tot zeer grote en gediversifieerde datanetwerken, zoals die wordt gebruikt in etailing, data warehousing, of zakelijk kennisbeheer.

    Basis server administratie

    De MySQL-distributie wordt geleverd met een command-line tool speciaal ontworpen om beheerders te helpen met het uitvoeren van algemene taken, zoals het wijzigen van gebruikerswachtwoorden of privileges.

    Deze tool kan worden gevonden in de 'bin'-directory van je MySQL-installatie:

    C:\Program Files\MySQL\MySQL Server 5.7\bin>

    C:\Program Files\MySQL\MySQL Server 5.7\bin\

    Het heet mysqladmin en wordt gewoonlijk aangeroepen met ťťn of meer commando's:

    C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqladmin version -u root -p

    of in geval van een fout: 'Access denied for user 'ODBC'@'localhost' (using password: NO)Ď :

    C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqladmin version -u root -p

    In vorige versies van MySQL Server, was er binnen de MySQL GUI Tools-pakket een grafische alternatief voor de mysqladmin gereedschap command-line: MySQL Administrator, een visueel hulpmiddel voor gebruikers administratie, database back-up en herstel, log-analyse, en de server fijnafstelling. Echter is de ontwikkeling van MySQL Administrator stopgezet. Veel administratieve commando's zijn nu te vinden in de workbench.

    Commando's ondersteund door mysqladmin

    Commando Beschrijving
    status Geeft informatie over de status van de server
    password Veranderen van gebruikerswachtwoord
    shutdown Sluit de MySQL server af
    reload Herlaad de MySQL 'grant' tables
    refresh Terugstellen van alle caches en logs
    variables Geeft de waarden van alle server variabelen
    version Geeft de versie van de server
    processlist Geeft een lijst van alle actieve processen op de server
    kill Afbreken van een actief server proces
    ping Testen of de server 'live' is

    U kunt aan volledige lijst van beschikbare commando's verkrijgen de het uitvoeren van mysqladmin --help

    Starten en stoppen van de server

    In UNIX wordt MySQL geleverd met een script voor het opstarten en afsluiten, wat de aanbevolen manier is voor het starten en stoppen van de MySQL database server. Dit script, genaamd mysql.server, is beschikbaar in de folder 'support-files' van uw MySQL-installatie en kan als volgt worden aangeroepen om de MySQL server te starten:

    [root@host ~]$ /usr/local/mysql/support-files/mysql.server start

    Terwijl u MySQL kan aanroepen door het direct uitvoeren van mysqld uit de 'bin' folder van uw installatie, wordt dit niet aanbevolen. Het gebruik van de mysqld_safe wrapper wordt beschouwd als een veilige, omdat deze wrapper zorgt voor:

  • automatisch loggen van fouten en runtime-informatie in een bestand
  • herstarten van de MySQL daemon in het geval van een onverwachte afsluiting
  • [root@host ~]$ /usr/local/mysql/bin/mysqld_safe --user=mysql &

    Opmerking: In oudere versies van MySQL, mysqld_safe heet safe_mysqld.

    Nadat de server draait, kunt u deze op elk gewenst moment uitschakelen met de mysqladmin shutdown commando. Op UNIX, ziet het er als volgt uit:

    [root@host ~]$ /usr/local/mysql/bin/mysqladmin shutdown

    U kunt u ook gebruik maken van het meegeleverde script mysql.server om de server af te sluiten, zoals:

    [root@host ~]$ /usr/local/mysql/support-files/mysql.server stop

    In Windows is de eenvoudigste manier om de MySQL server te starten is door in de 'bin' folder van uw MySQL-installatie het programma mysqld.exe uit te voeren.

    C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqld.exe (doesnít work)
    C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqld.exe --defaults-file = "C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" --console --skip-grant-tables (doesnít work)

    U kunt MySQL installeren als Windows-service, zodat het automatisch met Windows opstart en sluit, door het in de 'bin' folder van uw MySQL installatiemap het programma mysqld.exe uit te voeren met het speciale --install argument , zoals:

    C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqld.exe --install

    De eenvoudigste manier om MySQL server te starten is met in de workbench de menuoptie Server > Startup/Shutdown of via het service beheer van windows door de betreffende service in of uit te schakelen. U kunt controleren of de server wordt uitgevoerd met behulp van de mysqladmin ping-opdracht, zoals weergegeven. Dit geeft een statusbericht dat aangeeft of de server actief is:

    C:\Program Files\MySQL\MySQL server 5.7\bin>mysqladmin -u root -p ping
    Enter password: *************************
    mysqld is alive

    Het mysqladmin programma kan ook worden gebruikt om de 'grant' tabellen van de server te herladen, zoals:

    C:\Program Files\MySQL\MySQL server 5.7\bin>mysqladmin -u root -p reload
    Enter password: *************************

    Om de MySQL server af te sluiten kun u gebruik maken van onderstaand commando:

    C:\Program Files\MySQL\MySQL server 5.7\bin>mysqladmin -u root -p shutdown
    Enter password: *************************

    Let op! Sluit MySQL niet abrupt af door het mysqld proces af te sluiten met de kill-commando (UNIX) of de Task Manager (Windows). Het op deze manier voortijdig beŽindigen kan tot gegevensverlies of corruptie leiden als de server op dat moment gegevens naar de schijf aan het schrijven is wanneer het een termineringssignaal ontvangt.

    MySQL server status

    ēU vindt de huidige status van de server (server uptime, queries per seconde, het aantal open tabellen, etc.) via de mysqladmin status commando. Hier is een voorbeeld:

    C:\Program Files\MySQL\MySQL server 5.7\bin>mysqladmin -u root -p shutdown
    Enter password: *************************
    Uptime: 40931 Threads: 1 Questions: 10 Slow queries: 0 Opens: 116 Flush tables: 1 Open tables: 109 Queries per second avg: 0.000

    Het mysqladmin version commando biedt een beknopte samenvatting, met onder andere informatie over de MySQL-server versie:

    C:\Program Files\MySQL\MySQL server 5.7\bin>mysqladmin -u root -p version
    Enter password: *************************
    mysqladmin Ver 8.42 Distrib 5.7.12, for Win64 on x86_64
    Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective
    owners.

    Server version 5.7.12-log
    Protocol version 10
    Connection localhost via TCP/IP
    TCP port 3306
    Uptime: 11 hours 24 min 32 sec

    Threads: 1 Questions: 12 Slow queries: 0 Opens: 116 Flush tables: 1 Open tables: 109 Queries per second avg: 0.000

    Een vergelijkbare manier dat gebruik maakt van de ingebouwde functie is als volgt:

    MySQL
    SELECT VERSION();
    (1 row(s) returned)
    VERSION()
    1 5.7.12-log

    Uitgebreide status informatie ook beschikbaar via het commando mysqladmin extended-status

    C:\Program Files\MySQL\MySQL server 5.7\bin>mysqladmin -u root -p extended-status
    Enter password: *************************
    | Bytes_received | 116 |
    | Bytes_sent | 255 |

    Een vergelijkbare manier dat gebruik maakt van de ingebouwde functie is als volgt:

    MySQL
    SHOW STATUS;
    (356 row(s) returned)
    Variable_name Value
    1 Aborted_clients 0
    2 Aborted_connects 0
    3 Threads_cached 1
    4 Threads_connected 1
    5 Threads_created 2
    6 Threads_running 1
    7 Uptime 12300
    8 Uptime_since_flush_status 12300

    Zoals u kunt zien bestaat dit uitgebreide statusbericht een groot deel uit real-time statusinformatie. Het rapport bevat:

  • de hoeveelheid verkeer dat de server heeft ontvangen sinds het de laatste was gestart, zoals het aantal verzonden en ontvangen bytes
  • de cliŽntverbindingen, opgesplitst van hoeveel gelukte, hoeveel mislukte, en hoeveel er werden afgebroken.
  • Het bevat ook statistieken over:

  • totaal aantal zoekopdrachten dat door de server is verwerkt sinds het opstarten,
  • informatie over het aantal zoekopdrachten van elk type (SELECT, DELETE, INSERT, ...)
  • aantal actieve threads
  • huidig aantal client-verbindingen
  • aantal query's
  • aantal open tabellen
  • Beheren van MySQL cliŽnt processen

    Het is ook mogelijk om een volledige lijst van alle op de server geconnecteerde cliŽntprocessen te verkrijgen met de opdracht SHOW PROCESSLIST:

    MySQL
    SHOW PROCESSLIST;
    (2 row(s) returned)
    Id User Host db Command Time State Info
    1 41 linssenjack HQ-ML-NB-004:1786 db1 Sleep 1164 NULL
    2 42 linssenjack HQ-ML-NB-004:1787 db1 Query 0 Starting SHOW PROCESSLIST
  • Een 'gewone' gebruiker zal alleen in staat zijn om zijn of haar eigen onderwerpen te zien in de uitvoer van SHOW PROCESSLIST.
  • Gebruikers met het PROCES privilege zullen in staat zijn om alle lopende threads te zien.
  • Gebruikers met het SUPER privilege zullen in staat zijn om threads in uitvoering af te sluiten met het KILL commando.
  • MySQL
    KILL 41;
    (0 row(s) affected)

    De mysqladmin tool biedt gelijkwaardige processlist en kill commando's:

    C:\Program Files\MySQL\MySQL server 5.7\bin>mysqladmin -u root -p processlist
    Enter password: *************************
    +----+-------------+-------------------+-----+---------+------+----------+------------------+
    | Id | User | Host | db | Command | Time | State | Info |
    +----+-------------+-------------------+-----+---------+------+----------+------------------+
    | 41 | linssenjack | HQ-ML-NB-004:1786 | db1 | Sleep | 258 | | |
    | 42 | linssenjack | HQ-ML-NB-004:1787 | db1 | Sleep | 249 | | |
    | 47 | root | localhost:60121 | | Query | 0 | starting | show processlist |
    +----+-------------+-------------------+-----+---------+------+----------+------------------+

    Opmerking: Het is belangrijk op te merken dat een thread niet onmiddellijk stopt op het moment dat het een kill-signaal ontvangt. Integendeel, MySQL stelt een kill vlag voor die bepaalde thread in, die door de thread wordt gecontroleerd als het eenmaal is voltooid, ongeacht welke bewerking het momenteel uitvoert. Deze aanpak wordt beschouwd als veiliger dan het onmiddellijke afsluiten, aangezien het de thread in staat stelt af te maken waarmee het bezig was en de vergrendelingen te verwijderen voordat het beŽindigd. Meestal contoleren threads voor een kill vlag na elke significante lees- of schrijfbewerking.

    Meer informatie over het MySQL privilege systeem, samen met instructies over hoe privileges toe te kennen aan gebruikers, zal in later in meer detail besproken worden.

    Wijzigen van de server configuratie

    Het zal niet vaak nodig zijn om de MySQL standaardconfiguratie te veranderen. De software komt vooraf geconfigureerd die aan de meest voorkomende behoeften voldoet. Echter, in het geval dat de standaardconfiguratie niet voldoende is, heeft MySQL een groot aantal variabelen waarvan de waarden kunnen worden aangepast om aan de aangepaste eisen te voldoen. Sommige van deze variabelen kunnen ingesteld worden op het moment van het starten van de MySQL server, anderen kunnen worden ingesteld terwijl de server draait.

    Het gebruik van het optie bestand

    De aanbevolen methode voor het instellen van MySQL opties is door middel van een optie bestand. In wezen een ASCII configuratie bestand met variabele-waarde paren die de MySQL server bij het opstarten leest. MySQL zoekt bij het opstarten op aantal standaard plaatsen naar dit optie bestand.

    In Windows zal MySQL voor het optiebestand my.ini of my.cnf zoeken in

  • de MySQL installatie folder (C:\Program Files\MySQL\MySQL Server 5.7 of C:\ProgramData\MySQL\MySQL Server 5.7)
  • de Windows installatie folder
  • de root folder van de drive
  • In UNIX, zal MySQL voor het optiebestand my.cnf zoeken in

  • /etc, /etc/mysql,
  • de MySQL installatie folder
  • de home folder
  • U kunt aan MySQL client-programma's, zoals mysql en mysqladmin met het --defaults-file optie doorgeven waar het bij het opstarten voor het optiebestand en bestandspad moet zoeken.

    Het formaat van een optie-bestand is vrij eenvoudig, en lijkt op een Windows INI-bestand; Het is opgedeeld in groepen, die elk een variabele-waardepaar bevatten. Elke optie die kan worden gegeven op de opdrachtregel kan in dit bestand worden geplaatst, zonder dat de dit vooraf gegaan moet worden met een dubbel koppelteken.

    Hier is een voorbeeld:

    [mysqld]
    port=3306
    datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data
    character-set-server=utf8
    default-storage-engine=INNODB

    Typisch, zoekt MySQL voor configuratie-opties in de groepen [mysql] en [mysqld]. In UNIX, als je met gebruik maakt van het mysqld_safe wrapper script om MySQL te starten, kunt u ook de [mysqld_safe] groep gebruiken om opties door te geven aan MySQL.

    Alle programma's die met MySQL worden geleverd kunnen opties van een optie bestand lezen. geef gewoon de naam van het programma als groep (door tussen vierkante haken omsluiten het) in de optie bestand en geef vervolgens de variabelen die u wilt instellen.

    MySQL client programma's kunnen ook gebruik maken van een speciale [cliŽnt] groep, die doorgaans gebruikt wordt om de gebruiker en wachtwoord verbindingsparameters op te saan. Hier is een eenvoudig voorbeeld van hoe dit werkt:

    [client]
    user=timothy
    password=greenpeas

    In dit geval, wanneer een MySQL client-programma een verbinding met een MySQL server probeert te maken, zal het standaard verbinden als "Timothy" met wachtwoord "greenpeas".

    Het is belangrijk op te merken dat al deze opties ook opgegeven kunnen worden op de MySQL command line, gewoon door de optie vooraf te laten gaan door een dubbel koppelteken. Het volgende voorbeeld zal dit illustreren:

    C:\Program Files\MySQL\MySQL server 5.7\bin>mysqld --user=mysql --skip-networking

    In het geval dat er meerdere optie bestanden, of dezelfde optie meerdere keren is aangegeven met verschillende waarden, zal MySQL de laatste gevonden waarde gebruiken. Doordat MySQL de optie bestanden eerst leest voordat het de command-line argumenten inleest, betekent dit dat de opties op de opdrachtregel voorrang hebben op opties in een optiebestand.

    Optie Beschrijving
    ansi Gebruik striktere ANSI SQL-99-syntax
    basedir Bepaal de locatie in van de MySQL installatie folder
    datadir Bepaal de locatie in van de MySQL gegevens folder
    debug Aanmaken van een debug bestand
    default-character-set Stelt de default character set in
    default-table-type Stelt het standaard tabeltype in
    init-file Hiermee wordt een bestand met SQL-commando's uit te voeren bij het opstarten
    language Stelt de taal in voor de foutboodschappen
    log Schrijft MySQL foutbooschappen (verbindingen en queries) naar log
    log-error Schrijft kritieke foutboodschappen naar log
    log-warnings Schrijt waarschuwingsberichten naar log
    port Stelt de poort in waarop cliŽntverbindingen worden geaccepteerd
    safe-show-database Laat alleen de databases zien waar de gebruiker toegang tot heeft
    skip-innodb Uitschakelen van de InnoDB tabelverwerker
    skip-grant-tables Omzeilen van de toeganstabellen bij het uitvoeren van toegangscontrole
    skip-networking Laat enkel lokale aanvragen toe; negeert TCP/IP aanvragen
    socket Stelt de naam van de socket/named pipe voor lokale verbindingen in
    transaction-isolation Stelt het standaard isolatieniveau in
    user Specificeer waarin de gebruiker uitgevoerd moet worden
    tmpdir Stelt de locatie van de tijdelijke bestanden in

    Tip: Als uw applicatie op dezelfde fysieke machine bevindt als de MySQL server en u niet hoeft te anticiperen op MySQL cliŽntverbindingen van andere hosts, kan u met behulp van de --skip networking optie het luisteren naar TCP/IP uitschakelen. Dit betekent een aanzienlijke verbetering van de veiligheid van uw MySQL-installatie.

    Het gebruik van het SET commando

    Met MySQL kunt u ook met behulp van de opdracht SET server variabelen wijzigen terwijl de server draait. Hier is een voorbeeld, waarbij de opdracht SET wordt gebruikt om het standaard tabel-type voor nieuwe tabellen in te stellen:

    MySQL
    SET table_type=innodb;
    (0 row(s) affected)

    Variabelen die ingesteld zijn met de opdracht SET kunnen zowel globaal voor alle sessies ingesteld worden, of alleen voor de huidige sessie, door het SET commando te laten volgen met het sleutelwoord GLOBAL of SESSION. De standaard wordt aangenomen SESSION wanneer er geen sleutelwoord is opgegeven. Het volgende voorbeeld beperkt de server tot 10 dient verbindingen op elk moment en stelt de grootte van de lees buffer in op 250KB:

    MySQL
    SET GLOBAL max_user_connections=10, SESSION read_buffer_size=250000;
    (0 row(s) affected)

    Merk op dat het SUPER privilege nodig is voor het instellen van globale variabelen. De volgende lijst bevat een aantal belangrijke variabelen die kunnen worden ingesteld met de opdracht SET (zie de MySQL-handleiding voor de volledige lijst).

    Variabele Beschrijving
    autocommit Schakelt de autocommit modus aan of uit
    key_buffer_size Stelt de indexbuffer in
    table_cache Stelt het totaal aantal tabellen in dat MySQL tegelijk open kan houden
    table_type Stelt het standaard tabeltype in
    concurrent_inserts Laat gelijktijdige INSERT en SELECT statements toe op MyISAM tabellen
    interactive_timeout Stelt de timeout in van interactieve cliŽntverbindingen
    language Stelt de taal in van foutboodschappen
    lower_case_table_names
    sort_buffer_size Stelt de maximale grootte van de buffer in voor het sorteren van resultaten
    read_buffer_size Stelt de buffer in voor leesopdrachten op tabellen
    max_binlog_size Stelt de maximale grootte in voor het binair logbestand
    max_connections Stelt het op ieder moment maximaal toegelaten aantal cliŽntverbindingen
    max_user_connections Stelt het het maximaal verbindingen dat een gebruiker gelijktijdig open kan houden
    max_tmp_tables Stelt het maximaal aantal tijdelijke tabellen in dat een gebruiker tegelijk open kan houden
    query_cache_type Schakelt de query cache aan of uit
    query_cache_size Stelt de maximale grootte van de query cache in
    sql_mode Stelt de SQL modus van de server in
    tmpdir Stelt de locatie van de tijdelijke bestanden in
    tx_isolation Stelt het transactie isolatie niveau in

    Ophalen van variabele waarden

    Zodra een variabele is ingesteld, hetzij via SET of via een opstartoptie, kan de waarde worden opgehaald door het aanroepen van het mysqladmin variables commando:

    C:\Program Files\MySQL\MySQL server 5.7\bin>mysqladmin variables

    of met behulp van het commando SHOW VARIABLES. Aangezien de uitvoer van SHOW VARIABLES is enigszins wonderbaarlijk, MySQL kun je filteren tot slechts de variabele die u wilt door de toevoeging van een LIKE-clausule, zoals:

    MySQL
    SHOW VARIABLES LIKE 'autocommit';
    (1 row(s) returned)
    Variable_name Value
    1 autocommit ON
    SHOW VARIABLES LIKE '%innodb%';
    (131 row(s) returned)
    Variable_name Value
    1 ignore_builtin_innodb OFF
    2 innodb_adaptive_flushing ON
    ...
    131 innodb_write_io_threads 4

    U kunt ook de waarde van een systeemvariabele verkrijgen met behulp van de SELECT @@GLOBAL.variable of SELECT @@LOCAL.variable syntax, zoals:

    MySQL
    SELECT @@GLOBAL.tx_isolation;
    (1 row(s) returned)
    @@GLOBAL.tx_isolation
    1 REPEATABLE-READ

    Instellen van de server's SQL modus

    Met nieuwere versies van MySQL, is het mogelijk om in bepaalde situaties het standaard gedrag van de server aan te passen door door de SQL modus te veranderen.

    Denk aan de SQL-modus als een bepaling hoe de server op bepaalde omstandigheden reageert - bijvoorbeeld wanneer een ongeldige datumwaarde wordt ingevoerd of wanneer een fout door deling door nul optreedt.

    MySQL wordt geleverd met een aantal SQL-modi, waarvan sommige in de volgende tabel worden vermeld.

    SQL-modus Beschrijving
    ANSI Werking volgens ANSI SQL conventies
    STRICT_ALL_TABLES Breekt bewerkingen af die ongeldige waarden bevatten
    STRICT_TRANS_TABLES Breekt bewerkingen af die ongeldige waarden op transactionele tabellen bevatten
    ONLY_FULL_GROUP_BY Verwerp SELECT statements die andere velden bevatten dan diegene die opgegeven zijn in de GROUP BY clausule
    NO_ENGINE_SUBSTITUTION Schakel de automatische vervanging van een opslag engine uit door een andere
    NO_BACKSLASH_ESCAPES Schakelt het gebruik van het backslash (\) karakter naar escape strings uit
    NO_AUTO_CREATE_USER Schakelt de automatische aanmaak vanMySQL gebruikeraccounts uit tenzij een wachtwoord wordt voorzien.
    ERROR_FOR_DIVISION_BY_ZERO Verwerp INSERT/UPDATE statements die een deling door nul bewerking bevatten (alleen in STRICT_ALL_TABLES of STRICT_TRANS_TABLES)
    NO_ZERO_DATE Verwerp datumwaarden die nullwaarden bevatten (alleen in STRICT_ALL_TABLES of STRICT_TRANS_TABLES)
    ALLOW_INVALID_DATES Laat ongeldige datumwaarden toe (alleen in STRICT_ALL_TABLES of STRICT_TRANS_TABLES)

    Deze SQL modi kunnen onafhankelijk van elkaar ingesteld worden. Ze worden beheerd door de sql_mode variabele en kan worden aangepast met behulp van de opdracht SET (scheid meerdere modi met komma's). Hier is een voorbeeld, waarmee de 'strict' controle van de waarden op alle tabellen wordt toegepast:

    MySQL
    SET GLOBAL sql_mode='STRICT_ALL_TABLES';
    (0 row(s) affected)
    SELECT @@GLOBAL.sql_mode;
    (1 row(s) returned)
    @@GLOBAL.sql_mode
    1 STRICT_ALL_TABLES

    Hier is een ander voorbeeld, waarin de server dwingt een fout te genereren wanneer een deling-door-nul bewerking plaatsvindt:

    MySQL
    SET sql_mode ='';
    
    (0 row(s) affected)
    CREATE TEMPORARY TABLE x (f INT);
    (0 row(s) affected)
    INSERT INTO x VALUES (1/0);
    (0 row(s) affected)
    SET sql_mode='ERROR_FOR_DIVISION_BY_ZERO, STRICT_TRANS_TABLES';
    (0 row(s) affected)
    INSERT INTO x VALUES (1/0);
    ERROR 1365 (22012): Division by 0

    Probleemoplossing met behulp van fout-logbestand

    In het geval dat de server moeilijk start, of dat er fouten optreden tijdens de werking ervan, is het altijd een goed idee om de MySQL fout log te controleren om de oorzaak van de fout te achterhalen. Zoals het volgende korte voorbeeld illustreert, slaat dit log bestand informatie op tijdens het opstarten en afsluiten van de server, samen met een lijst van kritieke foutmeldingen en waarschuwingen over corrupte tabellen:

    090224 9:42:52 lnnoDB: Database was not shut down normally! lnnoDB: Starting crash recovery.
    lnnoDB: Reading tablespace information from the .ibd files... lnnoDB: Restoring possible half-written data pages from the doublewrite lnnoDB: buffer...
    090224 9:42:54 lnnoDB: Started; log sequence number 0 359286
    090224 9:42:54 [Note] Event Scheduler: Loaded 0 events
    090224 9:42:54 [Note] C:\Program Files\MySQL\bin\mysqld.exe: ready for
    connections.
    Version: Ď5.1.30-communityí socket: ďport: 3306 MySQL Community
    Server (GPL)

    Standaard wordt dit bestand in Windows en UNIX hostname.err genoemd en bevindt het zich altijd in de MySQL 'data' -directory.

    bijv. MyLaptop.err in C:\ProgramData\MySQL\MySQL Server 5.7\data (Zoek naar * .err in C:\) Het is mogelijk om een andere locatie voor het fout log bestand op te geven door toevoeging van het --log-error argument voor de MySQL server command line, zoals afgebeeld:

    C:\Program Files\MySQL\MySQL server 5.7\bin>mysqld --logerror=/tmp/mysqld.errors --user=mysql

    Meer informatie over andere logbestanden die door MySQL onderhouden worden zullen later worden besproken.

    Verkrijgen van database meta-informatie

    In deze en voorgaande hoofdstukken, heb je verschillende voorbeelden van het MySQL SHOW statement in actie gezien. Dit statement geeft informatie over de verschillende aspecten van een database en de tabellen. Bijvoorbeeld, de SHOW TABLES statement toont een lijst van alle tabellen in een database, terwijl de SHOW CREATE PROCEDURE statement het inwendige van een bepaalde opgeslagen procedure laat zien.

    Tot MySQL 5.0, waren SHOW statements de enige manier om toegang te krijgen tot de zogenaamde 'metainformatie' over database-objecten. Echter, MySQL 5.0 introduceerde een nieuwe database, de INFORMATION_SCHEMA database, die nu dienst doet als een centrale opslagplaats van informatie over alle database-objecten, inclusief tabellen, opgeslagen routines, triggers, events, views, systeem variabelen, user accounts ... simpel gezegd, alles met betrekking tot de database server!

    Deze centrale INFORMATION_SCHEMA database kan worden benaderd met behulp van standaard SELECT statements, waardoor de noodzaak voor SHOW statements en een consistente, aan de standaard onderworpen interface tot meta-informatie van de database voorziet. De informatie in deze database wordt automatisch bijgewerkt door de MySQL server, en biedt dus een momentopname van de database in gebruik op een bepaald moment.

    De INFORMATION_SCHEMA database bevat een aantal tabellen, elk met informatie over een ander type database object. De volgende lijst geeft een overzicht van de tabellen en een beschrijving van wat ieder bevat.

    Tabelnaam Soort informatie
    ANSI Werking volgens ANSI SQL conventies
    STRICT_ALL_TABLES Breekt bewerkingen af die ongeldige waarden bevatten
    STRICT_TRANS_TABLES Breekt bewerkingen af die ongeldige waarden op transactionele tabellen bevatten
    ONLY_FULL_GROUP_BY Verwerp SELECT statements die andere velden bevatten dan diegene die opgegeven zijn in de GROUP BY clausule
    NO_ENGINE_SUBSTITUTION Schakel de automatische vervanging van een opslag engine uit door een andere
    NO_BACKSLASH_ESCAPES Schakelt het gebruik van het backslash (\) karakter naar escape strings uit
    NO_AUTO_CREATE_USER Schakelt de automatische aanmaak vanMySQL gebruikeraccounts uit tenzij een wachtwoord wordt voorzien.
    ERROR_FOR_DIVISION_BY_ZERO Verwerp INSERT/UPDATE statements die een deling door nul bewerking bevatten (alleen in STRICT_ALL_TABLES of STRICT_TRANS_TABLES)
    NO_ZERO_DATE Verwerp datumwaarden die nullwaarden bevatten (alleen in STRICT_ALL_TABLES of STRICT_TRANS_TABLES)
    ALLOW_INVALID_DATES Laat ongeldige datumwaarden toe (alleen in STRICT_ALL_TABLES of STRICT_TRANS_TABLES)

    Ter illustratie, overweeg het volgende voorbeeld, waarin een lijst van alle tabellen in de db1 database wordt gemaakt door het opvragen van gegevens uit de information_schema.TABLES tabel (met een gelijkaardig uitvoer zoals dat van het SHOW TABLES statement):

    MySQL
    SELECT 
        TABLE_NAME, 
        TABLE_TYPE, 
        ENGINE, 
        TABLE_ROWS, 
        AVG_ROW_LENGTH
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'db1';
    (11 row(s) returned)
    TABLE_NAME TABLE_TYPE ENGINE TABLE_ROWS AVG_ROW_LENGHTH TABLE_SCHEMA
    1 aircraft BASE_TABLE MyISAM 16 32 db1
    2 aircrafttype BASE_TABLE MyISAM 6 20 db1
    3 airport BASE_TABLE MyISAM 15 47 db1
    4 class BASE_TABLE MyISAM 3 20 db1
    5 flight BASE_TABLE InnoDB 32 512 db1
    6 flightclass BASE_TABLE InnoDB 7 2340 db1
    7 flightdep BASE_TABLE InnoDB 108 151 db1
    8 log BASE_TABLE MyISAM 0 0 db1
    9 pax BASE_TABLE MyISAM 3 45 db1
    10 route BASE_TABLE MyISAM 29 12 db1
    11 sttats BASE_TABLE MyISAM 8 20 db1

    Hier is een ander voorbeeld, waarin een lijst van alle triggers in de db1 database wordt gemaakt door het opvragen van gegevens uit de information_schema.TRIGGERS tabel (met een gelijkaardige uitvoer zoals dat van het SHOW TRIGGERS ... statement):

    MySQL
    SELECT 
        TRIGGER_NAME,
        EVENT_MANIPULATION,
        ACTION_TIMING
    FROM INFORMATION_SCHEMA.TRIGGERS
    WHERE TRIGGER_SCHEMA = 'db1';
    (7 row(s) returned)
    TRIGGER_NAME EVENT_MANIPULATION ACTION_TIMING
    1 airport_bi INSERT BEFORE
    2 flight_au UPDATE AFTER
    3 flight_ad DELETE AFTER
    4 pax_bi INSERT BEFORE
    5 pax_ai INSERT AFTER
    6 pax_bd DELETE BEFORE
    7 pax_ad DELETE AFTER

    Wenst u een lijst te krijgen van de gebruikersaccounts en privileges van elk, dan kunt u dit opvragen uit de information_schema.USER_PRIVILEGES tabel als volgt:

    MySQL
    SELECT GRANTEE, PRIVILEGE_TYPE
    FROM INFORMATION_SCHEMA.USER_PRIVILEGES;
    (0 row(s) affected)
      GRANTEE PRIVILEGE_TYPE
    1 root@localhost EVENT
    2 root@localhost TRIGGER
    ...    
      mysql.sys@localhost USAGE
    ...    
    56 linssenjack@% TRIGGER
    57 linssenjack@% CREATE_TABLESPACE

    Om een lijst van actieve client-verbindingen te verkrijgen, zoals getoond met het SHOW PROCESSLIST statement,kunt u de informatie als volgt opvragen uit de information_schema.PROCESSLIST tabel:

    MySQL
    SELECT ID, USER, HOST, DB, STATE
    FROM INFORMATION_SCHEMA.PROCESSLIST;
    (6 row(s) returned)
      ID USER HOST DB STATE
    1 129 linssenjack hq-ml-bn-004.milin.cc:12915 db1  
    2 130 linssenjack hq-ml-bn-004.milin.cc:12915 db1  
    3 133 linssenjack hq-ml-bn-004.milin.cc:12915 db1  
    4 134 linssenjack hq-ml-bn-004.milin.cc:12915 db1  
    5 135 HQ-ML-NB-004:1052   db1  
    6 136 HQ-ML-NB-004:1053   db1 executing

    MySQL maakt alleen het alleen mogelijk om gegevens te lezen uit tabellen van de information_schema database. Een poging om nieuwe records toe te voegen of bestaande te wijzigen zullen worden verworpen:

    MySQL
    INSERT INTO INFORMATION_SCHEMA.USER_PRIVILEGES
    VALUES ('dummy@Iocalhost', NULL, 'INSERT', 'YES');
    (Error Code: 1044. Access denied for user 'linssenjack'@'%' to database 'information_schema')

    Het is ook belangrijk op te merken dat MySQL automatisch nota zal nemen van gebruikersrechten bij het weergeven van informatie uit tabellen van de information_schema database. Dus, bijvoorbeeld, als gebruiker linssenjack@Iocalhost alleen toegangsrechten tot de database db1 heeft, zullen SELECT-statements die door deze gebruiker op de information_schema database worden uitgevoerd alleen informatie retourneren die betrekking hebben tot de db1 database en niet voor andere.
    Om dit te illustreren, overweeg dan volgende voorbeelden, die laten zien hoe MySQL voor diverse SELECT queries automatisch de informatie beperkt laat zien aan linssenjack@IocaIhost:

    MySQL
    SELECT CURRENT_USER();
    (1 row(s) returned)
    CURRENT_USER()
    1 linssenjack@%
    SHOW GRANTS FOR 'linssenjack';
    (1 row(s) returned)
    Grants for linssenjack@%
    1 GRANT ALL PRIVILEGES ON *.* TO 'linssenjack'@'%' WITH GRANT OPTION
    SELECT SCHEMA_NAME
    FROM INFORMATION_SCHEMA.SCHEMATA;
    (8 row(s) returned)
    SCHEMA_NAME
    1 information_schema
    2 db1
    3 milin_wiki
    4 mysql
    5 perfromance_schema
    6 sys
    7 vrg
    8 world
    SELECT GRANTEE, PRIVILEGE_TYPE
    FROM INFORMATION_SCHEMA.USER_PRIVILEGES;
    (57 row(s) returned)
    GRANTEE PRIVILEGE_TYPE
    1 'root'@'localhost' SELECT
    2 'root'@'localhost' INSERT
    3 'root'@'localhost' UPDATE
    ...    
    55 'linssenjack'@'%' EVENT
    56 'linssenjack'@'%' TRIGGER
    57 'linssenjack'@'%' CREATE TABLESPACE
    SELECT ID, USER, HOST, DB, STATE 
    FROM INFORMATION_SCHEMA.PROCESSLIST;
    (2 row(s) returned)
    ID USER HOST DB STATE
    1 146 linssenjack hq-ml-nb-004.milin.cc:32034 db1 executing
    2 145 linssenjack hq-ml-nb-004.milin.cc:32033 db1