tachtler:mysql
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
| Beide Seiten der vorigen RevisionVorhergehende ÜberarbeitungNächste Überarbeitung | Vorhergehende Überarbeitung | ||
| tachtler:mysql [2012/05/08 22:55] – 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, | ||
| - | < | ||
| - | # 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.1336510536.txt.gz · Zuletzt geändert: 2012/05/08 22:55 von klaus