tachtler:mysql
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende Überarbeitung | |||
tachtler:mysql [2012/05/08 22:56] – [MySQL-Server Zugriff] klaus | tachtler:mysql [Unbekanntes Datum] (aktuell) – gelöscht - Externe Bearbeitung (Unbekanntes Datum) 127.0.0.1 | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
- | ====== MySQL ====== | ||
- | |||
- | [[http:// | ||
- | |||
- | :!: **Hinweis** - **Die nachfolgenden Ausführungen erheben keinen Anspruch auf Vollständigkeit, | ||
- | |||
- | Ab hier werden zur Ausführung nachfolgender Befehle **'' | ||
- | < | ||
- | $ su - | ||
- | Password: | ||
- | </ | ||
- | |||
- | ===== Installation ===== | ||
- | |||
- | Zur Installation eines [[http:// | ||
- | * **'' | ||
- | installiert werden. | ||
- | |||
- | Zur Installation des [[http:// | ||
- | < | ||
- | # yum install mysql-server | ||
- | Loaded plugins: fastestmirror | ||
- | Loading mirror speeds from cached hostfile | ||
- | * base: centos.intergenia.de | ||
- | * extras: centos.intergenia.de | ||
- | * updates: centos.intergenia.de | ||
- | Setting up Install Process | ||
- | Resolving Dependencies | ||
- | --> Running transaction check | ||
- | ---> Package mysql-server.x86_64 0: | ||
- | --> Processing Dependency: mysql = 5.1.52-1.el6_0.1 for package: mysql-server-5.1.52-1.el6_0.1.x86_64 | ||
- | --> Processing Dependency: perl-DBI for package: mysql-server-5.1.52-1.el6_0.1.x86_64 | ||
- | --> Processing Dependency: perl-DBD-MySQL for package: mysql-server-5.1.52-1.el6_0.1.x86_64 | ||
- | --> Processing Dependency: perl(DBI) for package: mysql-server-5.1.52-1.el6_0.1.x86_64 | ||
- | --> Running transaction check | ||
- | ---> Package mysql.x86_64 0: | ||
- | ---> Package perl-DBD-MySQL.x86_64 0: | ||
- | ---> Package perl-DBI.x86_64 0: | ||
- | --> Finished Dependency Resolution | ||
- | |||
- | Dependencies Resolved | ||
- | |||
- | ================================================================================ | ||
- | | ||
- | ================================================================================ | ||
- | Installing: | ||
- | | ||
- | Installing for dependencies: | ||
- | | ||
- | | ||
- | | ||
- | |||
- | Transaction Summary | ||
- | ================================================================================ | ||
- | Install | ||
- | Upgrade | ||
- | |||
- | Total download size: 9.8 M | ||
- | Installed size: 28 M | ||
- | Is this ok [y/N]: y | ||
- | Downloading Packages: | ||
- | (1/4): mysql-5.1.52-1.el6_0.1.x86_64.rpm | ||
- | (2/4): mysql-server-5.1.52-1.el6_0.1.x86_64.rpm | ||
- | (3/4): perl-DBD-MySQL-4.013-3.el6.x86_64.rpm | ||
- | (4/4): perl-DBI-1.609-4.el6.x86_64.rpm | ||
- | -------------------------------------------------------------------------------- | ||
- | Total 1.0 MB/s | 9.8 MB | ||
- | Running rpm_check_debug | ||
- | Running Transaction Test | ||
- | Transaction Test Succeeded | ||
- | Running Transaction | ||
- | Installing | ||
- | Installing | ||
- | Installing | ||
- | Installing | ||
- | |||
- | Installed: | ||
- | mysql-server.x86_64 0: | ||
- | |||
- | Dependency Installed: | ||
- | mysql.x86_64 0: | ||
- | perl-DBI.x86_64 0: | ||
- | |||
- | Complete! | ||
- | </ | ||
- | |||
- | Mit nachfolgendem Befehl, kann der Inhalt des **Hauptpaketes - '' | ||
- | < | ||
- | # rpm -qil mysql-server | ||
- | Name : mysql-server | ||
- | Version | ||
- | Release | ||
- | Install Date: Thu 25 Aug 2011 12:49:51 PM CEST Build Host: c6b6.bsys.dev.centos.org | ||
- | Group : Applications/ | ||
- | Size : 24481725 | ||
- | Signature | ||
- | Packager | ||
- | URL : http:// | ||
- | Summary | ||
- | Description : | ||
- | MySQL is a multi-user, multi-threaded SQL database server. MySQL is a | ||
- | client/ | ||
- | and many different client programs and libraries. This package contains | ||
- | the MySQL server and some accompanying files and directories. | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | / | ||
- | </ | ||
- | |||
- | ===== Dienst/ | ||
- | |||
- | Um einen [[http:// | ||
- | < | ||
- | # chkconfig mysqld on | ||
- | </ | ||
- | |||
- | Eine Überprüfung, | ||
- | < | ||
- | # chkconfig --list | grep mysqld | ||
- | mysqld | ||
- | </ | ||
- | |||
- | ===== iptables Regel ====== | ||
- | |||
- | Damit der [[http:// | ||
- | |||
- | Um die aktuellen '' | ||
- | < | ||
- | # iptables -L -nv --line-numbers | ||
- | Chain INPUT (policy ACCEPT 0 packets, 0 bytes) | ||
- | num pkts bytes target | ||
- | 1 141 15811 ACCEPT | ||
- | 2 0 0 ACCEPT | ||
- | 3 0 0 ACCEPT | ||
- | 4 1 60 ACCEPT | ||
- | 5 2 64 REJECT | ||
- | |||
- | Chain FORWARD (policy ACCEPT 0 packets, 0 bytes) | ||
- | num pkts bytes target | ||
- | 1 0 0 REJECT | ||
- | |||
- | Chain OUTPUT (policy ACCEPT 91 packets, 13277 bytes) | ||
- | num pkts bytes target | ||
- | </ | ||
- | |||
- | Nachfolgender Befehl, fügt folgende '' | ||
- | * < | ||
- | und hier die Befehle: | ||
- | < | ||
- | # iptables -I INPUT 5 -p tcp --dport 3306 -j ACCEPT | ||
- | </ | ||
- | |||
- | Ein erneute Abfrage des '' | ||
- | < | ||
- | # iptables -L -nv --line-numbers | ||
- | Chain INPUT (policy ACCEPT 0 packets, 0 bytes) | ||
- | num pkts bytes target | ||
- | 1 192 19567 ACCEPT | ||
- | 2 0 0 ACCEPT | ||
- | 3 0 0 ACCEPT | ||
- | 4 1 60 ACCEPT | ||
- | 5 0 0 ACCEPT | ||
- | 6 3 96 REJECT | ||
- | |||
- | Chain FORWARD (policy ACCEPT 0 packets, 0 bytes) | ||
- | num pkts bytes target | ||
- | 1 0 0 REJECT | ||
- | |||
- | Chain OUTPUT (policy ACCEPT 6 packets, 840 bytes) | ||
- | num pkts bytes target | ||
- | </ | ||
- | |||
- | Die neue Zeile ist an **Position 5 (INPUT)** | ||
- | < | ||
- | ... | ||
- | 5 0 0 ACCEPT | ||
- | ... | ||
- | </ | ||
- | |||
- | Um diese '' | ||
- | < | ||
- | # service iptables save | ||
- | iptables: Saving firewall rules to / | ||
- | </ | ||
- | |||
- | ===== Basis-Konfiguration ==== | ||
- | |||
- | Bevor der erste Start des [[http:// | ||
- | * **''/ | ||
- | durchgeführt werden. | ||
- | |||
- | Nach der Installation des [[http:// | ||
- | <code ini> | ||
- | [mysqld] | ||
- | datadir=/ | ||
- | socket=/ | ||
- | user=mysql | ||
- | # Disabling symbolic-links is recommended to prevent assorted security risks | ||
- | symbolic-links=0 | ||
- | |||
- | [mysqld_safe] | ||
- | log-error=/ | ||
- | pid-file=/ | ||
- | </ | ||
- | |||
- | :!: **HINWEIS** - **Ein Liste der Möglichen Optionen ist unter nachfolgendem externen Link zu finden:** | ||
- | * [[http:// | ||
- | |||
- | ==== lost+found Konfiguration ==== | ||
- | |||
- | Eins **Beispiel** für eine Anpassung wäre, dass das Dateisystem in dem der [[http:// | ||
- | * **'' | ||
- | trägt. | ||
- | |||
- | Dies kann zu **unerwünschten** Eintragungen im LOG des [[http:// | ||
- | < | ||
- | ... | ||
- | 110825 13:32:20 [ERROR] Invalid (old?) table or database name ' | ||
- | ... | ||
- | </ | ||
- | |||
- | Um dies zu vermeiden, kann der Speicherort im Dateisystem, | ||
- | |||
- | Die Anpassungen sind in der Konfigurationsdatei **''/ | ||
- | < | ||
- | # Tachtler | ||
- | </ | ||
- | gekennzeichnet. | ||
- | |||
- | Hier eine mögliche Anpassung (**komplette Konfigurationsdatei**): | ||
- | <code ini> | ||
- | [mysqld] | ||
- | # Tachtler | ||
- | # default: datadir=/ | ||
- | datadir=/ | ||
- | socket=/ | ||
- | user=mysql | ||
- | # Disabling symbolic-links is recommended to prevent assorted security risks | ||
- | symbolic-links=0 | ||
- | |||
- | [mysqld_safe] | ||
- | log-error=/ | ||
- | pid-file=/ | ||
- | </ | ||
- | |||
- | ===== Erster Start ===== | ||
- | |||
- | Bevor **__weitere__** **Konfigurationsschritte** erfolgen, sollte dem **ersten Start** nichts im Wege stehen, da bereits **hier Konfigurationseinstellungen** durchgeführt werden, was mit nachfolgendem Befehl durchgeführt werden kann: | ||
- | < | ||
- | # service mysqld start | ||
- | Initializing MySQL database: | ||
- | OK | ||
- | Filling help tables... | ||
- | OK | ||
- | |||
- | To start mysqld at boot time you have to copy | ||
- | support-files/ | ||
- | |||
- | PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! | ||
- | To do so, start the server, then issue the following commands: | ||
- | |||
- | / | ||
- | / | ||
- | |||
- | Alternatively you can run: | ||
- | / | ||
- | |||
- | which will also give you the option of removing the test | ||
- | databases and anonymous user created by default. | ||
- | strongly recommended for production servers. | ||
- | |||
- | See the manual for more instructions. | ||
- | |||
- | You can start the MySQL daemon with: | ||
- | cd /usr ; / | ||
- | |||
- | You can test the MySQL daemon with mysql-test-run.pl | ||
- | cd / | ||
- | |||
- | Please report any problems with the / | ||
- | |||
- | | ||
- | Starting mysqld: | ||
- | </ | ||
- | |||
- | ==== MySQL-Server Überprüfung ==== | ||
- | |||
- | Ob der [[http:// | ||
- | < | ||
- | # ps auxwwwf | grep mysqld | ||
- | root 2571 0.0 0.0 103148 | ||
- | root 2455 0.0 0.1 108076 | ||
- | --socket=/ | ||
- | mysql | ||
- | --user=mysql --log-error=/ | ||
- | </ | ||
- | |||
- | Eine weitere Überprüfung, | ||
- | * **''/ | ||
- | durchgeführt werden. | ||
- | |||
- | Ausgabe der LOG-Datei **''/ | ||
- | < | ||
- | # cat / | ||
- | 110825 16:33:13 mysqld_safe Starting mysqld daemon with databases from / | ||
- | InnoDB: The first specified data file ./ibdata1 did not exist: | ||
- | InnoDB: a new database to be created! | ||
- | 110825 16: | ||
- | InnoDB: Database physically writes the file full: wait... | ||
- | 110825 16: | ||
- | InnoDB: Setting log file ./ | ||
- | InnoDB: Database physically writes the file full: wait... | ||
- | 110825 16: | ||
- | InnoDB: Setting log file ./ | ||
- | InnoDB: Database physically writes the file full: wait... | ||
- | InnoDB: Doublewrite buffer not found: creating new | ||
- | InnoDB: Doublewrite buffer created | ||
- | InnoDB: Creating foreign key constraint system tables | ||
- | InnoDB: Foreign key constraint system tables created | ||
- | 110825 16: | ||
- | 110825 16:33:14 [Note] Event Scheduler: Loaded 0 events | ||
- | 110825 16:33:14 [Note] / | ||
- | Version: ' | ||
- | </ | ||
- | |||
- | Durch nachfolgenden Befehl, kann das Verzeichnis in dem die [[http:// | ||
- | < | ||
- | # ll / | ||
- | total 20488 | ||
- | -rw-rw----. 1 mysql mysql 10485760 Aug 25 16:33 ibdata1 | ||
- | -rw-rw----. 1 mysql mysql 5242880 Aug 25 16:33 ib_logfile0 | ||
- | -rw-rw----. 1 mysql mysql 5242880 Aug 25 16:33 ib_logfile1 | ||
- | drwx------. 2 mysql mysql 4096 Aug 25 16:33 mysql | ||
- | drwx------. 2 mysql mysql 4096 Aug 25 16:33 test | ||
- | </ | ||
- | |||
- | ==== MySQL-Server Zugriff ==== | ||
- | |||
- | Direkt nach der Installation und dem ersten Start des [[http:// | ||
- | < | ||
- | # mysqladmin version | ||
- | mysqladmin | ||
- | Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, | ||
- | This software comes with ABSOLUTELY NO WARRANTY. This is free software, | ||
- | and you are welcome to modify and redistribute it under the GPL license | ||
- | |||
- | Server version | ||
- | Protocol version | ||
- | Connection | ||
- | UNIX socket | ||
- | Uptime: | ||
- | |||
- | Threads: 1 Questions: 2 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.3 | ||
- | </ | ||
- | |||
- | Ein **erstes Zugriff** kann mit nachfolgendem Befehl durchgeführt werden, welche eine Ausgabe in etwa wie nachfolgend dargestellt, | ||
- | <code sql> | ||
- | # mysql -e " | ||
- | +------+---------+------+ | ||
- | | Host | Db | User | | ||
- | +------+---------+------+ | ||
- | | % | test | | | ||
- | | % | test\_% | | | ||
- | +------+---------+------+ | ||
- | </ | ||
- | |||
- | ==== MySQL-Monitor ==== | ||
- | |||
- | Der [[http:// | ||
- | |||
- | Der [[http:// | ||
- | < | ||
- | # mysql -h localhost -u root | ||
- | Welcome to the MySQL monitor. | ||
- | Your MySQL connection id is 8 | ||
- | Server version: 5.1.52 Source distribution | ||
- | |||
- | 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 ' | ||
- | |||
- | mysql> | ||
- | </ | ||
- | |||
- | Beendet wird diese Art von '' | ||
- | < | ||
- | mysql> quit | ||
- | Bye | ||
- | </ | ||
- | |||
- | ===== Sicherheits-Konfiguration ==== | ||
- | |||
- | Wie bereits beim **ersten Start** in den **Start-Meldungen** zu lesen war, werden gewisse **sicherheitsrelevanten** Einstellungen nach erfolgreicher Installation **empfohlen**, | ||
- | |||
- | Um nachfolgende **sicherheitsrelevanten** Einstellungen zu realisieren, | ||
- | * Benutzer **'' | ||
- | * **Anonyme Benutzerkonten** entfernen | ||
- | * Deaktivieren des Zugriffs des Benutzers **'' | ||
- | * **Nicht benötigte Datenbanken**, | ||
- | kann das Script, | ||
- | * **''/ | ||
- | genutzt, bzw. ausgeführt werden, was mit nachfolgendem Befehl durchgeführt werden kann: | ||
- | < | ||
- | # / | ||
- | |||
- | |||
- | |||
- | |||
- | NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL | ||
- | SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! | ||
- | |||
- | |||
- | In order to log into MySQL to secure it, we'll need the current | ||
- | password for the root user. If you've just installed MySQL, and | ||
- | you haven' | ||
- | so you should just press enter here. | ||
- | |||
- | Enter current password for root (enter for none): | ||
- | OK, successfully used password, moving on... | ||
- | |||
- | Setting the root password ensures that nobody can log into the MySQL | ||
- | root user without the proper authorisation. | ||
- | |||
- | Set root password? [Y/n] Y | ||
- | New password: | ||
- | Re-enter new password: | ||
- | Password updated successfully! | ||
- | Reloading privilege tables.. | ||
- | ... Success! | ||
- | |||
- | |||
- | By default, a MySQL installation has an anonymous user, allowing anyone | ||
- | to log into MySQL 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. | ||
- | production environment. | ||
- | |||
- | Remove anonymous users? [Y/n] Y | ||
- | ... Success! | ||
- | |||
- | Normally, root should only be allowed to connect from ' | ||
- | ensures that someone cannot guess at the root password from the network. | ||
- | |||
- | Disallow root login remotely? [Y/n] Y | ||
- | ... Success! | ||
- | |||
- | By default, MySQL comes with a database named ' | ||
- | access. | ||
- | before moving into a production environment. | ||
- | |||
- | Remove test database and access to it? [Y/n] Y | ||
- | - 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] Y | ||
- | ... Success! | ||
- | |||
- | Cleaning up... | ||
- | |||
- | |||
- | |||
- | All done! If you've completed all of the above steps, your MySQL | ||
- | installation should now be secure. | ||
- | |||
- | Thanks for using MySQL! | ||
- | |||
- | |||
- | </ | ||
- | |||
- | ===== LOG-Rotate Konfiguration===== | ||
- | |||
- | Um das Anwachsen von LOG-Dateien unter Linux, nicht ins unermessliche hinnehmen zu müssen, kann die LOG-Datei des [[http:// | ||
- | * **''/ | ||
- | wie nachfolgend dargestellt, | ||
- | |||
- | Dazu wird eine Datei mit dem Namen, hier z.B. | ||
- | * **'' | ||
- | mit dem Editor '' | ||
- | |||
- | Der Befehl zum erstellen der Konfiguratiosndatei **''/ | ||
- | < | ||
- | # vim / | ||
- | </ | ||
- | und **könnte** nachfolgend dargestellten Inhalt aufweisen: | ||
- | <code ini> | ||
- | / | ||
- | rotate 4 | ||
- | weekly | ||
- | compress | ||
- | notifempty | ||
- | size 5M | ||
- | missingok | ||
- | create 0640 mysql mysql | ||
- | sharedscripts | ||
- | postrotate | ||
- | /bin/kill -HUP `cat / | ||
- | endscript | ||
- | } | ||
- | </ | ||
- | |||
- | **__Erklärung: | ||
- | |||
- | Es wird die LOG-Datei - **''/ | ||
- | * es werden nur 4 historische LOG-Dateien aufgehoben, bevor die älteste überschrieben wird | ||
- | * und es wird wöchentlich rotiert und dabei | ||
- | * wird die rotierte LOG-Datei komprimiert (gzip) aber | ||
- | * nur wenn diese **nicht leer** ist | ||
- | * oder eine Größe von 5MB hat | ||
- | * mit den Dateirechten 0640 | ||
- | * und den Besitzrechten mysql:mysql | ||
- | * durch Anhalten und Neustart der des [[http:// | ||
- | rotiert! | ||
tachtler/mysql.1336510597.txt.gz · Zuletzt geändert: 2012/05/08 22:56 von klaus