Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen Revision Vorhergehende Überarbeitung Nächste Überarbeitung | Vorhergehende Überarbeitung | ||
database_server [2018/02/13 14:40] admin |
database_server [2018/02/16 00:32] (aktuell) admin |
||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
====== Datenbank-Server ====== | ====== Datenbank-Server ====== | ||
- | Initialisieren des Datenbankservers (root und weitere User sowie Parameter) einrichten: | + | ===== Installation ===== |
+ | <code|user@host:~#> | ||
+ | apt-get install mysql-server | ||
+ | </code> | ||
+ | ===== Konfiguration ===== | ||
+ | Um den MySQL-Server zu konfigurieren wird ein Tool mitgeliefert, welches | ||
+ | direkt nach der Installation ausgeführt werden sollte: | ||
<code|user@host:~#> | <code|user@host:~#> | ||
mysql_secure_installation | mysql_secure_installation | ||
+ | ------------------------------------------------------------------ | ||
+ | |||
+ | NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB | ||
+ | SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! | ||
+ | |||
+ | In order to log into MariaDB to secure it, we'll need the current | ||
+ | password for the root user. If you've just installed MariaDB, and | ||
+ | you haven't set the root password yet, the password will be blank, | ||
+ | so you should just press enter here. | ||
+ | |||
+ | Enter current password for root (enter for none): | ||
+ | </code> | ||
+ | Hier wird man direkt nach einem Passwort für den ''root''-User gefragt. | ||
+ | Achtung, damit ist nicht der ''root''-User der Linux-Maschine gemeint, | ||
+ | sondern der Datenbank-''root''. Da man während der Installation nicht | ||
+ | um die Einrichtung eines Passwortes für den ''root'' gefragt wurde, lässt | ||
+ | man hier das Passwort leer und drückt enter: | ||
+ | <code|user@host:~#> | ||
+ | OK, successfully used password, moving on... | ||
+ | |||
+ | Setting the root password ensures that nobody can log into the MariaDB | ||
+ | root user without the proper authorisation. | ||
+ | |||
+ | Set root password? [Y/n] | ||
+ | </code> | ||
+ | Da man nun ein Passwort setzen möchte, drückt man auf enter und tippt | ||
+ | das neue Passwort ein: | ||
+ | <code|user@host:~#> | ||
+ | New password: | ||
+ | Re-enter new password: | ||
+ | Password updated successfully! | ||
+ | Reloading privilege tables.. | ||
+ | ... Success! | ||
+ | |||
+ | |||
+ | By default, a MariaDB installation has an anonymous user, allowing anyone | ||
+ | to log into MariaDB without having to have a user account created for | ||
+ | them. This is intended only for testing, and to make the installation | ||
+ | go a bit smoother. You should remove them before moving into a | ||
+ | production environment. | ||
+ | |||
+ | Remove anonymous users? [Y/n] | ||
+ | </code> | ||
+ | Die anonymen user entfernen wir mal an dieser Stelle mit enter. Danach | ||
+ | wird man gefragt, ob man den Fernzugriff als ''root'' deaktivieren möchte. | ||
+ | Ja, besser ist es.\\ | ||
+ | <code|user@host:~#> | ||
+ | ... Success! | ||
+ | |||
+ | Normally, root should only be allowed to connect from 'localhost'. This | ||
+ | ensures that someone cannot guess at the root password from the network. | ||
+ | |||
+ | Disallow root login remotely? [Y/n] | ||
+ | </code> | ||
+ | |||
+ | Die Testdatenbank kann ruhig auch entfernt werden: | ||
+ | <code|user@host:~#> | ||
+ | ... Success! | ||
+ | |||
+ | By default, MariaDB comes with a database named 'test' that anyone can | ||
+ | access. This is also intended only for testing, and should be removed | ||
+ | before moving into a production environment. | ||
+ | |||
+ | Remove test database and access to it? [Y/n] | ||
+ | </code> | ||
+ | Am Ende lade man die Privilegien neu und dann wars das auch schon: | ||
+ | <code|user@host:~#> | ||
+ | - Dropping test database... | ||
+ | ... Success! | ||
+ | - Removing privileges on test database... | ||
+ | ... Success! | ||
+ | |||
+ | Reloading the privilege tables will ensure that all changes made so far | ||
+ | will take effect immediately. | ||
+ | |||
+ | Reload privilege tables now? [Y/n] | ||
+ | ... Success! | ||
+ | |||
+ | Cleaning up... | ||
+ | |||
+ | All done! If you've completed all of the above steps, your MariaDB | ||
+ | installation should now be secure. | ||
+ | |||
+ | Thanks for using MariaDB! | ||
+ | </code> | ||
+ | |||
+ | ===== Test ===== | ||
+ | Man mann sich nun auf dem Datenbankserver in die MySQL-Konsole als root einloggen. | ||
+ | ''-p'' ohne Passwort bewirkt dabei, dass man nach dem Passwort geprompted wird, statt es der Kommandozeile als Argument zu übergeben. \\ | ||
+ | Wichtig!! Passwörter niemals in Kommandos eintippen! | ||
+ | <code|user@host:~#> | ||
+ | mysql -u root -p | ||
+ | -------------------------------------------------------------------- | ||
+ | Enter password: | ||
+ | Welcome to the MariaDB monitor. Commands end with ; or \g. | ||
+ | Your MariaDB connection id is 10 | ||
+ | Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1 | ||
+ | |||
+ | Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. | ||
+ | |||
+ | Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. | ||
+ | |||
+ | MariaDB [(none)]> | ||
+ | </code> | ||
+ | Hier kann man sich anschauen, welche Datenbanken vorhanden sind: | ||
+ | <code|user@host:~#> | ||
+ | MariaDB [(none)]> show databases; | ||
+ | ------------------------------------------------------------------ | ||
+ | +--------------------+ | ||
+ | | Database | | ||
+ | +--------------------+ | ||
+ | | information_schema | | ||
+ | | mysql | | ||
+ | | performance_schema | | ||
+ | +--------------------+ | ||
+ | 3 rows in set (0.00 sec) | ||
+ | </code> | ||
+ | Als nächstes kann man sich die Berechtigungen aller User ansehen: | ||
+ | <code|user@host:~#> | ||
+ | MariaDB [(none)]> select * from information_schema.user_privileges; | ||
+ | ------------------------------------------------------------------- | ||
+ | +--------------------+---------------+-------------------------+--------------+ | ||
+ | | GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE | | ||
+ | +--------------------+---------------+-------------------------+--------------+ | ||
+ | | 'root'@'localhost' | def | SELECT | YES | | ||
+ | | 'root'@'localhost' | def | INSERT | YES | | ||
+ | | 'root'@'localhost' | def | UPDATE | YES | | ||
+ | | 'root'@'localhost' | def | DELETE | YES | | ||
+ | | 'root'@'localhost' | def | CREATE | YES | | ||
+ | | 'root'@'localhost' | def | DROP | YES | | ||
+ | | 'root'@'localhost' | def | RELOAD | YES | | ||
+ | | 'root'@'localhost' | def | SHUTDOWN | YES | | ||
+ | | 'root'@'localhost' | def | PROCESS | YES | | ||
+ | | 'root'@'localhost' | def | FILE | YES | | ||
+ | | 'root'@'localhost' | def | REFERENCES | YES | | ||
+ | | 'root'@'localhost' | def | INDEX | YES | | ||
+ | | 'root'@'localhost' | def | ALTER | YES | | ||
+ | | 'root'@'localhost' | def | SHOW DATABASES | YES | | ||
+ | | 'root'@'localhost' | def | SUPER | YES | | ||
+ | | 'root'@'localhost' | def | CREATE TEMPORARY TABLES | YES | | ||
+ | | 'root'@'localhost' | def | LOCK TABLES | YES | | ||
+ | | 'root'@'localhost' | def | EXECUTE | YES | | ||
+ | | 'root'@'localhost' | def | REPLICATION SLAVE | YES | | ||
+ | | 'root'@'localhost' | def | REPLICATION CLIENT | YES | | ||
+ | | 'root'@'localhost' | def | CREATE VIEW | YES | | ||
+ | | 'root'@'localhost' | def | SHOW VIEW | YES | | ||
+ | | 'root'@'localhost' | def | CREATE ROUTINE | YES | | ||
+ | | 'root'@'localhost' | def | ALTER ROUTINE | YES | | ||
+ | | 'root'@'localhost' | def | CREATE USER | YES | | ||
+ | | 'root'@'localhost' | def | EVENT | YES | | ||
+ | | 'root'@'localhost' | def | TRIGGER | YES | | ||
+ | | 'root'@'localhost' | def | CREATE TABLESPACE | YES | | ||
+ | +--------------------+---------------+-------------------------+--------------+ | ||
+ | 28 rows in set (0.00 sec) | ||
+ | </code> | ||
+ | Da die Datenbank frisch ist, sollte hier nur der ''root''-User stehen und alle | ||
+ | Privilegien besitzen. \\ | ||
+ | Man kann sich auch anschauen auf welchem wege sich ein User gegen die Datenbank authentifiziert, mit ''show grants;'' für den aktuell eingeloggten User oder mit ''show grants for heinz;'' für heinz: | ||
+ | <code|user@host:~#> | ||
+ | MariaDB [(none)]> show grants; | ||
+ | ------------------------------------------------------------------ | ||
+ | +------------------------------------------------------------------------------------------------+ | ||
+ | | Grants for root@localhost | | ||
+ | +------------------------------------------------------------------------------------------------+ | ||
+ | | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION | | ||
+ | | GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION | | ||
+ | +------------------------------------------------------------------------------------------------+ | ||
+ | 2 rows in set (0.00 sec) | ||
</code> | </code> | ||
+ | Hier sieht man, dass der ''root'' sich über einen | ||
+ | [[https://en.wikipedia.org/wiki/Unix_domain_socket|unix socket]] | ||
+ | authentifiziert.\\ | ||
+ | Nun kann man mit der Installation von [[phpmyadmin|phpMyAdmin]] fortfahren um | ||
+ | die Datenbanken auf dem Datenbankenserver über eine Weboberfläche zu administrieren. | ||