MySQL

Johdanto

Tämä sivu käsittelee MySQL-tietokannan käsittelyä erityisesti PHP-ohjelmointikiellä. Ilmainen MySQL-tietokanta soveltuu hyvin pienten ja suurempienkin nettisivustojen pyörittämiseen. Tietokantaa tarvitsevat esimerkiski Drupal, CMS Made Simple, Mediawiki ja Moodle. On myös olemassa muita tietokantoja MySQL:n sijaan.

Jotta MySQL voidaan käyttää selaimella paikallisesti omassa tietokoneessa jollain ohjelmistolla, tulee käyttöönotettavaksi myös esim. Apache ja PHP-ohjelmointikieli. Näiden asennus on sivulla PHP asennus.

Wikikon PHP-pääsivu

PHP asennus

MySQL virallinen kotisivu

MySQL asennus

Käynnistys pääteohjelmalla: brew services start mysql
Pysäyttäminen pääteohjelmalla: brew services stop mysql
Uudelleenkäynnistys pääteohjelmalla: brew services restart mysql

• Käynnistys ohjauspaneelin kautta, jos MySQL on asennettu sellaisen sisältävänä.
• Käynnistys pääteohjelmalla: sudo /usr/local/mysql/support-files/mysql.server start
• Poistuminen MySQL-interaktiivisesta tilasta mysql> kirjoittamalla: \q tai exit;
• Versio: /usr/local/mysql/bin/mysql -v -uroot -p ja root-salasana p-kirjaimeen yhteen kirjoitettuna.
• Polku: /Users/käyttäjänimesi/Sites/ (Kts. pwd -komennolla pääteohjemalla kun olet Sites hakemistossa).
• Asennetun mysql version tarkistus komentoriviltä: /usr/local/mysql/bin/mysql -v tai mysql --version

Linkkejä

Python ja MySQL

Sequoia

1. Asennetaan Mac vinkit#Homebrew
2. brew update
3. brew upgrade
4. brew install mysql Asennetaan viimeisin versio.
   1. Asennus ilmoittaa mm.,
      1. We've installed your MySQL database without a root password. To secure it run: mysql_secure_installation
      2. MySQL is configured to only allow connections from localhost by default
      3. To connect run: mysql -u root
      4. To start mysql now and restart at login: brew services start mysql
      5. Or, if you don't want/need a background service you can just run: /opt/homebrew/opt/mysql/bin/mysqld_safe --datadir\=/opt/homebrew/var/mysql
5. brew services start mysql Käynnistetään MySQL. Komento luo samalla toiminnon, joka käynnistää tietokannan aina tietokone käynnistettäessä. Tästä tulee ilmoitus Background Items Added "mysqld_safe" is an item that can run in the background. You can manage this in Login Items & Extensions.
6. mysql -u root Kytkeydytään tietokantaan root-käyttäjänä.
7. Asetetaan root-käyttäjälle salasana
   1. ALTER USER 'root'@'localhost' IDENTIFIED BY 'salasana';
   2. flush privileges;
8. Lisätään uusi käyttäjätunnus ja salasana. Jos tulee virheilmoitus, tarkista, että '-merkit ovat oikeat! Onnistunut komento ilmoittaa Query OK, 0 rows affected (0.00 sec)
   1. CREATE USER 'käyttäjätunnus'@'localhost' IDENTIFIED BY 'salasana';
   2. GRANT ALL PRIVILEGES ON * . * TO 'käyttäjätunnus'@'localhost' WITH GRANT OPTION;
   3. FLUSH PRIVILEGES;
   4. SELECT User FROM mysql.user; (tarkistetaan näkyykö käyttäjä)
   5. exit; (Jos käytössä on F-Securen DeepGuard, ponnahdusikkunassa klikkaa Deny)

• Kirjautuminen halutulla käyttäjätunnuksella on mysql -u käyttäjätunnus -psalasana (salasanan eteen p-kirjain yhteenkirjoitettuna)
• Käyttäjä voidaan poistaa komennolla DROP USER 'käyttäjätunnus'@'localhost';

Catalina

https://wpbeaches.com/install-apache-mysql-php-on-macos-11-big-sur-and-earlier-macos/

1. Lataa sivulta https://dev.mysql.com/downloads/mysql x86, 64-bit DMG-versio ja asenna. Esimerkiksi macOS 11 8.0.27 asentui 12/2021.
   1. Kirjautuminen ei ole pakollinen, valitse No thanks, just start my download.
2. Lisätään polku lisäämällä tiedosto .zshrc komennolla: cd ; nano .zshrc ja sinne: export PATH="/usr/local/mysql/bin:$PATH"
   1. nano -editorissa tallennus ja poistuminen ctrl-x ja Y ja uuden tiedoston ollessa kyseessä enter. Pelkkä tallennus ctrl-o ja enter. Uutta tiedostoa luotaessa on lisäksi painettava Y-kirjaimen jälkeen enter. Jos et ole tehnyt muutoksia ctrl-x poistuu suoraan. Ctrl-w etsii ja kun painat ctrl-w ja vain enter se siirtyy seuraavaan aikaisemmin haettuun kohtaan samalla hakusanalla.
3. Anna komento: source ~/.zshrc
4. Korjaa 2002 MySQL Socker error
   1. sudo mkdir /var/mysql
   2. sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

Yosemite

• Päivitettäessä Mavericks Yosemiteen, riittää kun kytketään se vain päälle uudestaan.
• Pikavinkkejä
  • Polku: /Users/käyttäjänimesi/Sites/ (Kts. pwd -komennolla pääteohjemalla kun olet Sites hakemistossa). Tätä voi tarvita esm. Smarty:n kanssa kun tavoittelet sitä esm. Sites -hakemistossa.

Alkuperäinen ohje: http://coolestguidesontheplanet.com/get-apache-mysql-php-phpmyadmin-working-osx-10-10-yosemite/

1. Lataa sivulta http://dev.mysql.com/downloads/mysql/ paketti Mac OS X 10.9 (x86, 64-bit), DMG Archive. Kirjautumista ei vaadita, valitse No thanks, just start my download.
2. Asenna paketti
3. Käynnistäminen Järjestelmäasetuksista johon ilmestyy oma paneeli tai Pääte-ohjelmalla: sudo /usr/local/mysql/support-files/mysql.server start
4. Muutetaan polku yksinkertaisemmaksi lisäämällä .bash_profile -tiedostoosi ohjaus:

cd ; nano .bash_profile
export PATH="/usr/local/mysql/bin:$PATH"
ctrl-x ja sitten y
source ~/.bash_profile

1. Testataan mysql -v
2. Asennetaan juurisalasana tietokannalle komennolla
   1. /usr/local/mysql/bin/mysqladmin -u root password 'salasanatähän'
3. Korjataan 2002 MySQL Socket error
   1. sudo mkdir /var/mysql
   2. sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

Virheilmoituksia ja ongelmia

• ERROR 1064 (42000) at line 19: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=MyISAM' at line x
  • Saattaa viitata siihen että yrität tuoda tauluja MySQL ohjelmistoon, joka on uudempi kuin tauluista ottamasi kopio
  • Korvaa tekstieditorilla kaikki taulun TYPE=MyISAM kohdat ENGINE=MyISAM kohdiksi

MySQL ei käynnisty tietokoneen käynnistyessä

Kts. http://coolestguidesontheplanet.com/get-apache-mysql-php-phpmyadmin-working-osx-10-10-yosemite/

Mavericks

Paljon käytetty MySQL ei ole oletuksena Mavericks:ssa. Huomaa että jos käytät Pythonin kanssa MySQL-tietokantaa se ei välttämättä toimi kuin 32-bittisen version kanssa (12/2013).

• Lataa uusin 32 tai 64-bittinen DMG-versio MySQL-sivustosta. Alareunassa on vaihtoehto No thanks, just start my download. 10.7 (joulu 2013).
• Asenna kaikki kolme eli mysql..pkg, MySQLStart..pkg ja MySQL.prefPane
  • klikkaa hiiren oikeaa painiketta .pkg-tiedoston päällä ja valitse Avaa, jolla ohitat Applen Gatekeeper -toiminnon.
• Järjestelmäasetukset valikkoon tulee uusi kohta nimeltä MySQL josta tietokanta kytketään päälle ja pois.
  • Pääte-ohjelmassa

päälle kytkeminen: sudo /usr/local/mysql/support-files/mysql.server start 
versiotieto: /usr/local/mysql/bin/mysql -v
versiotiedosta pääset pois \q komennolla eli shift-alt-7 ja q ja enter

• Jotta em. /usr/local.. polkua ei tarvitse joka kerta antaa Pääte-ohjelmassa, anna seuravat komennot Pääte-ohjelmassa

Avaa editori komennolla:   cd ; nano .bash_profile 
Lisää editoriin rivi:   export PATH="/usr/local/mysql/bin:$PATH"
Poistu editorista ctrl-x, paina y ja enter
Aktivoi muutos:   source ~/.bash_profile

• Aseta MySQL-tietokannalle oma juuri-käyttäjän salasana (root password).

/usr/local/mysql/bin/mysqladmin -u root password 'salasanatahan'

• Korjaa 2002 MySQL Socket virhe

Anna komento ja pyydettäessä Mac-pääkäyttäjätilisi ylläpitosalasana:   sudo mkdir /var/mysql
Anna komento:   sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

• Palomuuria ei ole syytä avata mysqld:lle, koska ohjelmat yleensä suoraan itse luovat yhteyden, mutta käyttäjät (asiakkaat) eivät saisi muodostaa suoraa yhteyttä.
  • Järjestelmäasetukset > Suojaus ja yksityisyys > Palomuurivalinnat... > mysqld aseta Estä saapuvat yhteydet > OK

Ubuntu

Asennettu Raspberry Pi 5, jossa käyttäjärjestelmänä Ubuntu 24.04 (asennettu 4/2025).

1. sudo apt update && sudo apt upgrade
2. sudo apt install mysql-server
   1. palvelin käynnistyy automaattisesti
3. Testataan toimivuus
   1. sudo service mysql status    Tulee näyttää active (running)
   2. sudo ss -tap | grep mysql
4. Tarvittaessa yritetään uudelleen käynnistää sudo service mysql restart
   1. Virheloki sudo journalctl -u mysql
5. Asetetaan root-käyttäjän salasana. Oletuksena salasanaa ei ole, ja sisään voidaan kirjautua komennolla mysql -uroot Tarvittaessa salasana löytyy komennolla grep ‘temporary’ /var/log/mariadb/mariadb.log ja kirjautuminen tällöin mysql -uroot -pSALASANA
6. Vaihtoehto 1
   1. mysql -uroot
      1. ALTER USER root@localhost IDENTIFIED BY 'SALASANA';
      2. FLUSH PRIVILEGES;
      3. exit;
7. Vaihtoehto 2 (jos mikään muu ei toimi)
   1. sudo mysql -u root mysql
   2. UPDATE user SET plugin='mysql_native_password' WHERE User='root';
      1. FLUSH PRIVILEGES;
      2. ALTER USER root@localhost IDENTIFIED BY 'SALASANA';
      3. exit;
   3. Vain tarvittaessa sudo mysql_secure_installation -toimenpide
   4. sudo systemctl restart mysql
8. Vaihtoehto 3
   1. sudo service mysql stop
   2. sudo killall mysqld
   3. nano /home/käyttäjänimi/salasanasql.txt
      1. ALTER USER root@localhost IDENTIFIED BY 'SALASANA';
   4. sudo mysqld --init-file=/home/käyttäjänimi/salasanasql.txt
   5. sudo service mysql start

• sudo mysql -u root --skip-password

Lähde Install and configure a MySQL server

MySQL asennuksen poistaminen

Ilmeisesti onnistuu tällä:

• Ota varmuuskopiot tietokannoista
• Pysäytä tietokantapalvelin
• sudo rm /usr/local/mysql
• sudo rm -rf /usr/local/mysql*
• sudo rm -rf /Library/StartupItems/MySQLCOM
• sudo rm -rf /Library/PreferencePanes/My*
• sudo vi /etc/hostconfig ja poista rivi MYSQLCOM=-NO-
• rm -rf ~/Library/PreferencePanes/My*
• sudo rm -rf /Library/Receipts/mysql*
• sudo rm -rf /Library/Receipts/MySQL*
• sudo rm -rf /private/var/db/receipts/*mysql*

Missä MySQL tietokanta sijaitsee

• phpMyAdmin-ohjelmassa komento SELECT @@datadir, @@innodb_data_home_dir
• macOS:ssa tietokannat sijaitsevat yleensä hakemistossa /usr/local/mysql/data/tietokannannimi
• Palvelimella komentorivillä seuraava komento ilmoittaa esimerkiksi mysql is /usr/bin/mysql: type -a mysql

MySQL-tietokannan varmuuskopiointi

Tietokannan koon kasvaessa gigatavuihin, saattaa phpMyAdminin kautta palvelimelta ladattaessa lautaus pysähtyä. Näin esimerkiksi macOS Safari-selaimella. Toisen selaimen käyttämistä kannattaa kokeilla. Kyse voi olla myös phpMyAdminin "aikarajoituksesta" (timeout) tai tiedostokokorajoituksesta. Aikarajoituksessa palvelin lopettaa viennin ennen kuin se on päättynyt. set_time_limit(0); ylimpänä yliajaa PHP-asetuksen. Jos arvo on nollaa suurempi, se on sekunteja. Lisätietoa esimerkiksi hakemalla 'phpmyadmin timeout export'. Myös PHP:n max_execution_time tai memory_limit.

Suoria SSH-komentorivikomentoja

mysqldump -u käyttäjätunnus -p tietokantanimi > tiedostonimi.sql
mysqldump -u käyttäjätunnus -psalasana tietokantanimi > tiedostonimi.sql
mysqldump -u cPanelkäyttäjätunnus -p tietokantanimi > tiedostonimi.sql
mysqldump -u cPanelkäyttäjätunnus -p --databases tietokantanimi1 tietokantanimi2 > tiedostonimi.sql
mysqldump -u cPanelkäyttäjätunnus -p --all-databases > tiedostonimi.sql
/usr/bin/mysql/mysqldump -u cPanelkäyttäjätunnus -p tietokantanimi > tiedostonimi.sql
mysqldump -u cPanelkäyttäjätunnus -p | gzip > tiedostonimi.sql.gz
mysqldump --databases -user=käyttäjätunnus --salasanana tietokantanimi > tiedostonimi.sql
mysqldump -u cPanelkäyttäjätunnus -p tietokantanimi taulunimi > tiedostonimi.sql

• Palvelimilla usein käyttäjätunnus on cPaneliin kirjautumisen käyttäjätunnus ja salasana. Ei siis itse tietokannan salasana. Jos salasana annetaan -p jälkeen, siinä ei tule olla välissä välilyöntiä!
• Virheilmoitukset:
  • mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
    • --no-tablespaces lisääminen komentoon on yksinkertainen ratkaisu, mutta silloin MySQL-tietokannan tablespaces eivät kopioidu varmuuskopioon. https://anothercoffee.net/how-to-fix-the-mysqldump-access-denied-process-privilege-error
  • mysqldump: Got error: 1044: Access denied for user 'käyttäjätunnus'@'localhost' to database 'tietokantanimi' when selecting the database
  • mysqldump: Error: Access denied for user 'cPanelkäyttäjätunnus'@'localhost' to database 'tietokantanimi' when using LOCK TABLES
    • lisää --single-transaction eli mysqldump --single-transaction --databases -user=käyttäjätunnus --salasanana tietokantanimi > tiedostonimi.sql

SSH-kirjautuminen tietokantaan

1. which mysql missä mysql sijaitsee
2. mysql -u käyttäjätunnus -p
   1. mysql -u käyttäjätunnus -psalasana
   2. Käyttäjätunnus voi olla esim. cPanel-käyttäjätunnus, root tai aiemmin tietokantaan lisätty muu käyttäjätunnus. Salasana voi olla esim. cPanel-salasana.
3. syötetään käyttäjätunnuksen salasana

Peruskomentoja

Kirjaudutaan ensin tietokantaan pääteohjelmalla.

• mysql> show databases;     näyttää tietokannat
• mysql> use tietokantanimi;      valitsee tietokannan
• mysql> ctrl-d     tietokantayhteyden sulkeminen.

• sudo du -h /var/lib/mysql     tietokantojen koot.
• Tietokannan tuonti olemassa olevaan tietokantaan
  • mysql> use tietokannannimi;   valitaan tietokanta johon tuodaan
  • mysql> source /home/ubuntu/polku/tietokannannimi.sql;
  • Komentoriviltä: mysql -u käyttäjätunnus -p tietokannannimi < /home/ubuntu/polku/tietokannannimi.sql

Tila ->

• Komentojen lopussa on oltava puolipiste (;) tai sen puuttuessa siirrytään -> -tilaan, jossa MySQL odottaa SQL-komentoja kunnes annetaan viimeinen komento. Komennoissa käytetään puolipistettä lopussa. Poistutaan komennolla: \d ;

MySQL Workbench asennus

Tietokannan muokkaamiseen voit valita esimerkiksi MySQL Workbench tai phpMyAdmin -ohjelman tai kummatkin.

• Yosemite
  • Asenna dmg-tidostona kotisivuilta ladattuna
• Jos et saa muodostettua yhteyttä tietokantaan toimi seuraavasti:

1. Asensithan ensin MySQL tietokannan käyttökuntoon ja sitten vasta Workbench:in
2. Asensithan MySQL tietokannan asennuspaketista MySQL Server, preference pane ja startup
3. Tarkista Järjestelmäasetuspaneelista että MySQL on käynnissä! Jos asetuspaneelia ei näy, ei Workbench toimi!
4. Anna pääte-ohjelmassa komento mysql > SHOW GLOBAL VARIABLES LIKE 'PORT';
   1. tarkista mitä porttia MySQL käyttää, todennäköisesti 3307 eikä 3306
   2. löytyy myös Macintosh HD > Kirjasto > LaunchDaemons > com.oracle.oss.mysql.mysqld.plist > --port=
5. Lisää UUSI yhteys
6. Coonection Name: localhost > Connection Method: Standard (TCP/IP) > Hostname: localhost > Port: 3307 > Username: root
7. Paina nyt Test Connection ja lisää salasana root -tunnukselle ja VALITSE AVAINNIPPUUN TALLENTAMINEN
   1. tarvittaessa paina Clear, jotta kysyy salasanaa tai aloita alusta uudella yhteydellä
   2. jos root ei ole tietokantasi käyttäjätunnus, käytä sitä mikä on ja sen salasanaa

phpMyAdmin asennus

Katso Phpmyadmin

Tietokanta teoriaa

• Taulukäsittelijät. Tällainen on esimerkiksi InnoDB. Eri taulukäsittelijävaihtoehdoista tulisi valita se joka parhaiten soveltuu tarpeeseen. MySQL voi käyttää samanaikaisesti tietokannassa eri käsittelijöitä eri tauluille.
• Select-kyselyt ja tulokset ovat välimuistissa, joten aina ei tietokannasta välttämättä haeta. Välimuistia tyhjennetään ja täydennetään automaattisesti.

Tietokannan luominen ja poistaminen

Tietokantoja voi olla useita tai yksi tietokanta voi sisältää monen eri ohjelman tauluja. Tällöin nämä taulut eritellään toisistaan etuliitteellä, esim. drupal13_ Samaa tietokantaa voidaan samanaikaisesti kutstua eri ohjelmista, jotka on tehty myös eri ohjelmointikielillä. MySQL-palvelimella voi olla useita tietokantoja ja näissä tietokannoissa useita tauluja. Tietokanta voidaan luoda esimerkiksi phpMyAdmin -ohjelmalla tai komentoriviltä päätteen avulla.

1. Luodaan tietokanta

2. Luodaan tietokantaan taulu(t) (esim. osoitetiedot) ja samassa komennossa tauluun kentät (sarakkeet, esim. Etunimi, Sukunimi).

3. Lisätään tauluihin tietueet (rivit, esim. Matti, Virtanen)

Luominen cPanel ohjelmalla

1. Databases > MySQL Database Wizard
2. Create A Database > Anna tietokannalle nimi > Next Step
3. Create Database Users > Anna käyttäjänimi ja erittäin vaikea salasana > Create User
4. Add User to Database > Valitse All Priviledges > Next Step > valmis.

• Kirjoita muistiin hostname (yleensä localhost), käyttäjänimi, tietokannannimini, salasana

Luominen pääteohjelman avulla

$ mysql -u root -p
syötä MySQL:n rootsalasana.
mysql> CREATE DATABASE tietokannannimi;
mysql> GRANT ALL PRIVILEGES ON tietokannannimi.* TO "käyttäjänimi"@"localhost";
mysql> FLUSH PRIVILEGES;
mysql> EXIT

• Turvallisempaa olisi käyttää root -käyttäjän sijasta sitä käyttäjätunnusta, jota on käytetty mysql yhteydessä sitä asennettaessa.
• ?Käyttäjänimi näkyy internetissä?

Luominen phpMyAdmin ohjelmalla

• Koti (mökin kuva yläkulmassa) > Tietokannat -välilehti
  • Luo tietokanta: Anna nimi, Valitse Aakkosjärjestys ja paina Luo
    • utf8_general_ci sorts by stripping away all accents and sorting as if it were ASCII
    • utf8_unicode_ci uses the Unicode sort order, so it sorts correctly in more languages
• Koti > Valitse tietokanta > Käyttöoikeudet > Lisää käyttäjä
  • Syötä käyttäjänimi, erittäin vaikea salasana, tarkista että "Anna tietokannalle "x" kaikki oikeudet on valittuna. > Siirry
• Return to the Privileges screen and click the Check privileges icon on the user you've just created for database. In the Database-specific privileges section, select the database you've just created for your application under the Add privileges to the following database dropdown. The page will refresh with privileges for that database. Click Check All to select all privileges, and click Go.

TAI

• Koti (mökin kuva yläkulmassa)
• SQL -välilehti
• CREATE DATABASE tietokannannimi; ja paina Siirry

Lisätietoa Python ja MySQL.

Poistaminen pääteohjelman avulla

$ mysql -u root -p
Anna kysyttäessä MySQL:n rootsalasana.
mysql> DROP DATABASE tietokannannimi;

Poistaminen phpMyAdmin ohjelmalla

• Valitse vasemmalta poistettava tietokanta
• Toiminnot -välilehti
• Tuhoa tietokanta ja vahvista OK

TAI

• Koti (mökin kuva yläkulmassa)
• SQL -välilehti
• DROP DATABASE tietokannannimi; ja paina Siirry (saattaa ilmoittaa että oikeudet eivät riitä, joten silloin edellinen vaihtoehto saattaa toimia)
  • oikeuksien asettaminen tähän....

Taulun luonti

• Valitse tietokanta ja sitten välilehti SQL
• Kirjoita seuraava ja paina Siirry
• Lisätään seuraavassa taulu nimeltä taulunimi ja siihen kentät numerokentta, tekstikentta ja liukuluku. int(10) tarkoittaa kokonaislukua jonka maksimipituus on 10 merkkiä.

CREATE TABLE taulunimi (numerokentta int(10) NOT NULL, tekstikentta varchar(50), liukuluku float, PRIMARY KEY(numerokentta));

• Huomaa, että voidaksesi käyttää PRIMARY KEY, tulee sille määrätä sarake edellä samalla nimellä. Esimerkissä se on nimi "numerokentta".

TAI

• Valitse tietokanta ja Rakenne -välilehti
• Anna taululle nimi ja kenttien määrä ja paina Siirry. Anna nimi ja täydennä ominaisuudet:
• Tyyppi. Minkälaista tietoa kenttään tallennetaan.
• Pituus. Kentän pituus.
• Oletusarvo. Mahdollinen kentän oletusarvo
• Aakkosjärjestys (collation): kts. yllä
• Attribuutit
  • BINARY - the collation for the field will be binary, for example utf8_bin;
  • UNSIGNED - the field numeric values will be positive or 0;
  • UNSIGNED ZEROFILL - the field numeric values will be positive or 0 and leading zeros will be added to a number;
  • ON UPDATE CURRENT_TIMESTAMP - the value for a data type field has the current timestamp as its default value, and is automatically updated;
• Tyhjä. Voiko arvo olla nolla
• Indeksi
• Auto_Increment
• Kommentit. Vapaamuotoinen teksti
• Taulun kommentit. Vapaamuotoinen teksti
• Tallennusmoottori. Usein InnodDB Miksi?
• Aakkosjärjestys.
• Partition-määritelmä

Tietueen (rivin) lisääminen tauluun

Tällä sivulla käytetään seuraavanlaista tietokantaa:

Tietokantanimi: tietokannannimi ja taulu: taulunimi

	Numerokentta	Tekstikentta	Liukuluku
rivi1 (tietue)	123456789	Teksti1	10.00
rivi2	23456789	Teksti2	20.00
rivi3	3456789	Teksti3	30.00

• Valitse taulu ja sitten välilehti SQL
• Kirjoita seuraava ja paina Siirry
• Lisääminen tapahtuu INSERT -komennolla ja jokaiselle kentälle on annettava arvo.
• Lisätään seuraavassa tauluun nimeltä taulunimi rivit (tietueet)

INSERT INTO taulunimi VALUES(123456789,"Teksti1",10.00);
INSERT INTO taulunimi VALUES(23456789,"Teksti2",20.00);
INSERT INTO taulunimi VALUES(3456789,"Teksti3",30.00);

TAI

• Valitse taulu ja sitten välilehti Lisää rivi ja syötä arvot.

Perusjärjestys

1. Luodaan yhteys tietokantapalveluun

2. Luodaan yhteys tietokantaan

3. Luetaan / kirjoitetaan / muutetaan / poistetaan tietoa tauluista

4. Suljetaan yhteys

Yhteys tietokantapalvelimeen PHP:llä

Ensimmäisenä muodostetaan yhteys tietokantapalvelimeen.

• TCP-yhteys muodostetaan MySQL-palvelimelle seuraavalla funktiolla. Jos yhteys ei onnistu, tulee virheilmoitus, muutoin ei tule mitään näkyviin.
• Porttinumero on oletuksena 3306 ja tarvittaessa muu porttinumero annetaan muodossa localhost:porttinumero
• Yhteys katkaistaan mysql_close();
• Käyttäjätunnus ja salasana ovat PALVELIMEN käyttäjätunnus ja salasana. localhost -kohtaan muutetaan tarvittaessa palvelimen osoite, muutoin localhost yhdistää sille palvelimelle, jossa php-ohjelma suoritetaan. Omalla palvelimella pelkästää mysql_connect(); saattaa riittää.
• die -komennolla tulostetaan virhe ja suljetaan xhtml-koodi. Huomaa että tällöin mysql_connect -rivin lopussa ei saa olla puolipistettä koska rivi on vain selkeyden vuoksi jaettu kahdelle riville tässä. Jos die-komentoa ei käytetä, silloin ; -merkki tietysti pitää rivin lopussa olla. Virheilmoitusta kannattaa käyttää aina tietokantaan kohdistuvien käskyjen jälkeen, jolloin tiedetään jos virheitä tapahtuu. Virheriville voi vaikka suomeksi määritellä minkä toiminnon jälkeen virhe on tapahtunut.

    $tietokanta = mysql_connect("localhost", "käyttäjätunnus", "salasana")
        or die("Virhe: ".mysql_error()"</body></html>"); // Näyttää viimeisimmän tietokannan virheilmoituksen.

• Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in ... .php on line -virheilmoitus voi viitata siihen että et ole antanut käyytäjätunnusta ja salasanaa yhteyden avaamisessa. Vaikka samalla palvelimella pelkkä connect() tai "localhost" pitäisi kyllä riittää.

Yhteys tietokantaan

Toiseksi muodostetaan yhteys haluttuun tietokantaan. Jos tietokantakahvaa ei määritellä, muodostetaan viimeksi avattua yhteyttä. Jos yhteys ei onnistu, tulee virheilmoitus, muutoin ei tule mitään näkyviin.

    mysql_select_db ("tietokannannimi", $tietokanta );

Kysely tietokannasta ja yleisesti

• Kyselyssä tietokannasta haetaan rivejä määritellyillä ehdoilla. Tämän jälkeen haettua joukkoa voidaan käsitellä tarpeen mukaan. Tämä ei siis tallenna tai vahingoita tietokantaa mitenkään. Tietokannasta ei kannata hakea kuin se mitä tarvitaan eli turha hakea koko taulukkoa jos tarvitaan yksi rivi.
• PHP:llä on oma komentonsa mysql_query tietokannan kanssa kommunikointiin. Itse tietokannalle annetaan komennot SQL-kielellä eli SELECT, UPDATE jne. Haettaessa tietoa muodostetaan uusi relaatio, joka voi sisältää tietoa useasta taulusta ja taulujen eri kentistä. Kentän voi ajatella taulukon sarakkeiksi ja tallennetun tiedon riveiksi. Erilainen tieto kannattaa sijoittaa eri tauluihin ja toisiinsa kuuluva tieto merkitä kuhunkin tauluun esimerkiksi tunnistenumeron avulla. Esimerkiksi osoitetiedot yhdessä taulukossa ja verkkokauppatilaukset toisessa, kummassakin taulussa yksi kenttä on asiakasnumero. Tästä tarkemmin myöhemmin sivun alaosassa kohdassa Tietokannan perusteet.
• Jos tietokantakahvaa ei määritellä, muodostetaan viimeksi avattua yhteyttä.
• Rivit eivät välttämättä ole tietokannassa siinä järjestyksessä kuin ne syötetään. Siksi ne on sieltä luettaessa tarvittaessa järjestettävä.
• Esimerkistä nähdään kuinka haku tehdään mysql_query funktion avulla. Haku muodostaa joukon ja sitä joudutaan käsittelemään mysql-funktiolla.

    // Haetaan taulusta nimeltä taulunimi sen kenttä1:n kaikki rivit (*) eli arvot
    $haku1 = mysql_query("SELECT * FROM taulunimi WHERE kentta1", $tietokanta);
    
    // Tulostetaan silmukan avulla rivi riviltä kunnes False. Huomaa että print käyttää taulun kenttää
    while($muuttuja1 = mysql_fetch_array($haku1)){
        print ($muuttuja1['numerokentta']."<br/>");
    }
    mysql_close($tietokanta);

    // Sijoitetaan SQL-komento muuttujaan
    $kysely = "SELECT * FROM taulunimi";
    $haku1 = mysql_query($kysely, $tietokanta);
    // tulostetaan numerokentästä toinen rivi mysql_result -funktiolla. 
    // laskenta alkaa nollasta ja numerokentta voidaan korvata kentän numerolla.
    print mysql_result($haku1, 1, "numerokentta");

SELECT

Järjestys on oltava: SELECT DISTINCT FROM WHERE GROUP BY ORDER BY LIMIT

* on jokerimerkki eli tarkoittaa kaikkia
'teksti' merkkijono laitetaan heittomerkkeihin
= < > AND OR jne. operaattorit
funktiot

// Haetaan kaikki kentät (sarakkeet) ja rivit (tietueet) eli koko taulu
$haku1 = mysql_query("SELECT * FROM taulunimi", $tietokanta);

// Haetaan kaikki rivit joihin sisältyy kentta1 ja kentta2
$haku1 = mysql_query("SELECT kentta1, kentta2 FROM taulunimi", $tietokanta);

// Haetaan kaikki rivit joissa kentta1 arvo on teksti
$haku1 = mysql_query("SELECT * FROM taulunimi WHERE kentta1 LIKE 'teksti'", $tietokanta);

// Haetaan koko taulu ja järjestetään se kentta1 mukaan suurimmasta pienimpään
$haku1 = mysql_query("SELECT * FROM taulunimi ORDER BY kentta1 DESC", $tietokanta);

// Haetaan kentta1 kaikki rivit ja järjestetään ne pienimmästä suurimpaan kentta3 mukaan
$haku1 = mysql_query("SELECT * FROM taulunimi WHERE kentta1 ORDER BY kentta3", $tietokanta);
 
// Haetaan kaikki rivit joissa kentta1 arvo 10 ja kentta2 arvo suurempi kuin 50
$haku1 = mysql_query("SELECT * FROM taulunimi WHERE kentta1 = 10 AND kentta2 > 50", $tietokanta);

// Haetaan 5 ensimmäistä riviä
$haku1 = mysql_query("SELECT * FROM taulunimi LIMIT 5", $tietokanta);

// Haetaan kentta1 rivit 6 7 ja 8
$haku1 = mysql_query("SELECT * FROM taulunimi WHERE kentta1 LIMIT 5,3", $tietokanta);

// Haetaan rivien määrä
$haku1 = mysql_query("SELECT COUNT(*) FROM taulunimi", $tietokanta);

// Haetaan kentta1 rivien erilaiset arvot
$haku1 = mysql_query("SELECT DISTINCT kentta1 FROM taulunimi", $tietokanta);

// Haetaan kentta1 rivien 5 ensimmäistä merkkiä
$haku1 = mysql_query("SELECT LEFT(kentta1, 5) FROM taulunimi", $tietokanta);

// Haetaan kentta1 suurin arvo
$haku1 = mysql_query("SELECT MAX(kentta1) FROM taulunimi", $tietokanta);

// Haetaan kentta1 arvot ja kerrotaan kahdella ja tulostetaan kolmella desimaalilla
$haku1 = mysql_query("SELECT ROUND(kentta1 * 2, 3) FROM taulunimi", $tietokanta);

// Haetaan kentta1 yhteenlaskettu arvo
$haku1 = mysql_query("SELECT SUM(kentta1) FROM taulunimi", $tietokanta);

Rivin lisääminen tauluun

• SQL -käsky INSERT muuttaa tietokantaa! Jos yrität ajaa kahteen kertaan saman syötteen, saat todennäköisesti virheilmoituksen.

    // Lisätään arvot 10, Teksti1 ja viimeiseen kenttään tyhjä
    mysql_query("INSERT INTO taulunimi VALUES(10, 'Teksti1', null)")

• Rivin lisääminen suoraan nettisivun lomakkeesta tietokantaan:

    // Esimerkki 1
    $tietue = <<<MERKKI1
    INSERT INTO taulunimi VALUES(
    "{$_POST['lomakekenttanimi1']}", 
    "{$_POST['lomakekenttanimi2']}";
MERKKI1;

    // Esimerkki 2
    // INSERT INTO taulunimi VALUES ("arvo1", "arvo2");
    $tietue = 'INSERT INTO taulunimi VALUES(' .
    "\"{$_POST['lomakenttanimi1']}\", " .
    "\"{$_POST['lomakekenttanimi2']}\");";

    // Viedään edellinen lauseke tietokantaan
    mysql_query($tietue);

INSERT

// Lisätään kenttiin arvoja samassa järjestyksessä
mysql_query("INSERT INTO taulunimi(kentta1, kentta2, kentta3) VALUES(10, 'Teksti1', null)")

Tietokantaan kirjoittaminen ei onnistu

• last_login DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP jota käytetään taulussa, jossa on automaattinen id-numero ja tekstikenttiä, estää jostain syystä uuden rivin lisääminen https://www.tutorialrepublic.com/php-tutorial/php-mysql-login-system.php -skriptissä.

Muuttaminen

UPDATE

// Vaihdetaan kentta1 arvoksi 50 kaikkiin riveihin joissa kentta2 arvona on Teksti1
mysql_query("UPDATE taulunimi SET kentta1 = 50 WHERE kentta2 = 'Teksti1'")

// Lisätään kentta1 arvoa yhdella kaikilla riveillä joissa kentta2 arvona on Teksti1
mysql_query("UPDATE taulunimi SET kentta1 = kentta1 +1 WHERE kentta2 = 'Teksti1'")

• Jos taululle on asetettu oletuksena johonkin sarakkeeseen, että riviä muutettaessa kyseinen päiväysarvo muuttuu automaattisesti. Tämä voidaan estää tapauskohtaisesti, esimerkissä joku_sarake:

$sql = "UPDATE kayttajat SET kayttajanimi = ?, joku_sarake = joku_sarake WHERE id = ?";

• Jos tietokannan kellonaika on esim. 1 tunti jäljessä päivitysaikaa

$sql = "UPDATE kayttajat SET kayttajanimi = ?, joku_sarake = DATE_ADD(now(), INTERVAL 1 HOUR) WHERE id = ?";

Rivin poistaminen taulusta

DELETE

// Poistetaan rivit, joiden kentta1 arvo on Teksti1 mysql_query("DELETE FROM taulunimi WHERE kentta1 = 'Teksti1'")

Sarakkeen lisääminen tauluun

• Esimerkiksi phpMyAdmin SQL-komento:

ALTER TABLE taulunnimi
ADD sarakkeennimi MÄÄRITYKSET;

• Esimerkiksi lisätään päivämäärä, jonka oletusarvo on null: ADD muutospv DATETIME NULL

Tietokannan perusteet

kirjoitetaan jossain vaiheessa

MySQL varmuuskopiointi

Katso varmuuskopioinnin ajastus sivulta Cron.

Komennon valinnat

MediaWiki

• tietokanta sisältää sivut, käyttäjät, asetukset jne. Tiedostojärjestelmä sisältää kuvat, ulkoasut, laajennukset jne.
• Vaihtoehtona MediaWikissä on kopiointi suoraan sivustosta viemällä sivut xml-muodossa. Katso ohje sivulta MediaWiki.
• $wgReadOnly = 'Varmuuskopioidaan tietokantaa. Yritä uudestaan hetken kuluttua.'; asetettuna LocalSettings.php -tiedostoon estää tietokantaan kirjoittamisen varmuuskopioinnin aikana.
• MediaWikissä suositellaan käyttämään binary merkistöä, joka sallii suuremmat merkkimäärät. Tietokannan merkistö saattaa olla siis binary joka tarkoittaa että varmuuskopiossa ääkköset näkyvät oudosti. Mysqldump käyttää oletuksena utf8, vanhat versiot käyttivät latin1. Näet tietokannan käyttämän koodauksen LocalSettings.php -tiedostosta kohdasta $wgDBTableOptions DEFAULT CHARSET

mysqldump --user=käyttäjätunnus --password=salasana tietokannannimi > varmuuskopio.sql
mysqldump -ukäyttäjätunnus -psalasana tietokannannimi > | gzip > ~/osoite/varmuuskopio.sql.gz
mysqldump -ukäyttäjätunnus -psalasana --xml tietokannannimi > ~/osoite/varmuuskopio.xml
mysqldump -ukäyttäjätunnus -psalasana --xml tietokannannimi | gzip > ~/osoite/varmuuskopio.xml.gz
mysqldump -ukäyttäjätunnus -psalasana --default-character-set=utf8 --xml tietokannannimi > ~/osoite/varmuuskopio.xml

nice -n 19 mysqldump -ukäyttäjätunnus --password=salasana tietokannannimi -c | nice -n 19 gzip -9 > ~/osoite/mediawiki-tietokkanannimi-$(date '+%Y%m%d').sql.gz

Ohje

http://www.mediawiki.org/wiki/User:Flominator/Backup_MW

XML varmuuskopio

• XML-varmuuskopio sisältää MediaWikin sisällön, mutta ei käyttäjätilejä, kuvien metatietoja, lokitietotja jne. Kyseessä ei siis ole tietokannan kopiointi vaan sisällön kopiointi jonka voi tuoda uudempiin MediaWikin versioihin. Sijainti /maintenance/dumpBackup.php

mysqldump --user=käyttäjätunnus --password=salasana tietokannannimi --xml > varmuuskopio.xml
Sivut historian kanssa
php ~/Sites/mediawiki/maintenance/dumpBackup.php --full > ~/osoite/mediawiki-dump-tietokkanannimi-$(date '+%Y%m%d').xml
Vain viimeisin versio sivusta
php ~/Sites/mediawiki/maintenance/dumpBackup.php --current > ~/osoite/mediawiki-dump-tietokkanannimi-$(date '+%Y%m%d').xml

Ohje