mySQL is een grote database in Linux. De database is één van de 4 kernonderdelen van de LAMP-server: Linux Apache MySQL PHP.
-
mySQL installeren
# apt-get install mysql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
libdbd-mysql-perl libdbi-perl libhtml-template-perl libnet-daemon-perl
libplrpc-perl mysql-client-5.1 mysql-server-5.1 mysql-server-core-5.1
The following NEW packages will be installed:
libdbd-mysql-perl libdbi-perl libhtml-template-perl libnet-daemon-perl
libplrpc-perl mysql-client-5.1 mysql-server mysql-server-5.1 mysql-server-core-5.1
0 upgraded, 10 newly installed, 0 to remove and 12 not upgraded.
Need to get 21.6MB of archives.
After this operation, 51.2MB of additional disk space will be used.
Do you want to continue [Y/n]? y
Get:1 http://ftp.debian.org/debian/ testing/main libnet-daemon-perl all 0.43-1 [46.6kB]
Get:2 http://ftp.debian.org/debian/ testing/main libplrpc-perl all 0.2020-2 [36.0kB]
Get:3 http://ftp.debian.org/debian/ testing/main libdbi-perl i386 1.612-1 [879kB]
Get:4 http://ftp.debian.org/debian/ testing/main libdbd-mysql-perl i386 4.016-1 [138kB]
Get:5 http://ftp.debian.org/debian/ testing/main mysql-client-5.1 i386 5.1.49-1 [8,301kB]
Get:6 http://ftp.debian.org/debian/ testing/main mysql-server-core-5.1 i386 5.1.49-1 [3,933kB]
Get:7 http://ftp.debian.org/debian/ testing/main mysql-server-5.1 i386 5.1.49-1 [7,814kB]
Get:8 http://ftp.debian.org/debian/ testing/main libhtml-template-perl all 2.9-2 [65.9kB]
Get:9 http://ftp.debian.org/debian/ testing/main mysql-server all 5.1.49-1 [64.4kB]
Fetched 21.6MB in 36s (589kB/s)
Preconfiguring packages ...
Selecting previously deselected package libnet-daemon-perl.
(Reading database ... 166436 files and directories currently installed.)
Unpacking libnet-daemon-perl (from .../libnet-daemon-perl_0.43-1_all.deb)
Selecting previously deselected package libplrpc-perl.
Unpacking libplrpc-perl (from .../libplrpc-perl_0.2020-2_all.deb) ...
Selecting previously deselected package libdbi-perl.
Unpacking libdbi-perl (from .../libdbi-perl_1.612-1_i386.deb) ...
Selecting previously deselected package libdbd-mysql-perl.
Unpacking libdbd-mysql-perl (from .../libdbd-mysql-perl_4.016-1_i386.deb)
Selecting previously deselected package mysql-client-5.1.
Unpacking mysql-client-5.1 (from .../mysql-client-5.1_5.1.49-1_i386.deb) ...
Selecting previously deselected package mysql-server-core-5.1.
Unpacking mysql-server-core-5.1 (from .../mysql-server-core-5.1_5.1.49-1_i386.deb) ...
Selecting previously deselected package mysql-server-5.1.
Unpacking mysql-server-5.1 (from .../mysql-server-5.1_5.1.49-1_i386.deb) ...
Selecting previously deselected package libhtml-template-perl.
Unpacking libhtml-template-perl (from .../libhtml-template-perl_2.9-2_all.deb) ...
Selecting previously deselected package mysql-server.
Unpacking mysql-server (from .../mysql-server_5.1.49-1_all.deb) ...
Processing triggers for man-db ...
Processing triggers for menu ...
Setting up libnet-daemon-perl (0.43-1) ...
Setting up libplrpc-perl (0.2020-2) ...
Setting up libdbi-perl (1.612-1) ...
Setting up libdbd-mysql-perl (4.016-1) ...
Setting up mysql-client-5.1 (5.1.49-1) ...
Setting up mysql-server-core-5.1 (5.1.49-1) ...
Setting up mysql-server-5.1 (5.1.49-1) ...
Starting MySQL database server: mysqld.
Setting up libhtml-template-perl (2.9-2) ...
Processing triggers for menu ...
Tijdens de installatie wordt er een root-mysql paswoord gevraagd:
+---------------------------- Configuring mysql-server-5.1 -----------------------------+
| While not mandatory, it is highly recommended that you set a password for the MySQL |
| administrative "root" user. |
| |
| If this field is left blank, the password will not be changed. |
| |
| New password for the MySQL "root" user: sdf12345 |
| ------------------------------------------------------------------------------------- |
| <Ok> |
+---------------------------------------------------------------------------------------+
Als je systeem hier niet om vraagt kun je dit paswoord ook instellen via de shell zoals hieronder in punt 3, hierna volgend.
-
php5 en mySQL laten samenwerken
Om PHP5 en mySQL te laten samenwerken moet je het pakket php5-mysql installeren:
# apt-get install php5-mysql
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following NEW packages will be installed:
php5-mysql
0 upgraded, 1 newly installed, 0 to remove and 12 not upgraded.
Need to get 64.2kB of archives.
After this operation, 242kB of additional disk space will be used.
Get:1 http://ftp.debian.org/debian/ testing/main php5-mysql i386 5.3.2-2 [64.2kB]
Fetched 64.2kB in 0s (253kB/s)
Selecting previously deselected package php5-mysql.
(Reading database ... 166972 files and directories currently installed.)
Unpacking php5-mysql (from .../php5-mysql_5.3.2-2_i386.deb) ...
Processing triggers for libapache2-mod-php5 ...
Reloading web server config: apache2apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.1.1 for ServerName
Setting up php5-mysql (5.3.2-2) ...
Het kan geen kwaad apache2 te herstarten:
# /etc/init.d/apache2 restart
-
mySQL root paswoord instellen
# mysqladmin -u root password sdf12345
-
inloggen in mySQL
# mysql -u root -p mysql
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 48
Server version: 5.1.49-1 (Debian)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
-
bestaande databases opvragen
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| allusers |
| mysql |
| ttest1 |
+--------------------+
4 rows in set (0.00 sec)
-
een nieuwe database creëren
mysql> create database linux31;
Query OK, 1 row affected (0.00 sec)
-
een database voor gebruik selecteren
mysql> use linux31;
Database changed
-
een tabel aanmaken
mysql> create table studenten
-> (
-> naam varchar(20) not null,
-> voornaam varchar(20) not null
-> )
-> ;
Query OK, 0 rows affected (0.01 sec)
-
data invoeren in een tabel
mysql> insert into studenten values ('schneider','mike');
Query OK, 1 row affected (0.00 sec)
mysql> insert into studenten values ('markaj','aferdita');
Query OK, 1 row affected (0.00 sec)
mysql> insert into studenten values ('sensio','denis');
Query OK, 1 row affected (0.00 sec)
mysql> insert into studenten values ('shyaka','bernard');
Query OK, 1 row affected (0.00 sec)
mysql> insert into studenten values ('akram','shoaib');
Query OK, 1 row affected (0.01 sec)
mysql> insert into studenten values ('kubwimana','flora');
Query OK, 1 row affected (0.00 sec)
mysql> insert into studenten values ('viwassi','horatio');
Query OK, 1 row affected (0.00 sec)
-
data opvragen uit een tabel
mysql> select * from studenten;
+-----------+----------+
| naam | voornaam |
+-----------+----------+
| schneider | mike |
| markaj | afrodita |
| sensio | dennis |
| shyaka | bernard |
| akram | shoaib |
| kubwimana | flora |
| viwassi | horatio |
+-----------+----------+
7 rows in set (0.00 sec)
-
structuur van een database opvragen
mysql> show tables;
+-------------------+
| Tables_in_linux31 |
+-------------------+
| studenten |
+-------------------+
1 row in set (0.00 sec)
-
structuur van een tabel opvragen
mysql> describe studenten;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| naam | varchar(20) | NO | | | |
| voornaam | varchar(20) | NO | | | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-
een gebruiker aanmaken met alle rechten
Je moet ingelogd zijn als mysql root gebruiker om dit commando uit te voeren.
mysql> CREATE USER 'user2'@'localhost' IDENTIFIED BY 'sdf12345';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
-
records verwijderen
mysql> DELETE FROM studenten WHERE naam='sensio';
Query OK, 1 row affected (0.00 sec)
mysql> select * from studenten;
+-----------+----------+
| naam | voornaam |
+-----------+----------+
| schneider | mike |
| markaj | aferdita |
| shyaka | bernard |
| akram | shoaib |
| kubwimana | flora |
| viwassi | horatio |
+-----------+----------+
6 rows in set (0.00 sec)
-
een tabel verwijderen
mysql> drop table studenten;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables
-> ;
Empty set (0.00 sec)
-
een database verwijderen
mysql> drop database linux31;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
-
mySQL verlaten
mysql> quit;
Bye
