Difference between revisions of "MySQL"
From Dietrich Blog (Strato)
(→Konfiguration) |
(→Konfiguration) |
||
| Line 18: | Line 18: | ||
== Konfiguration == | == Konfiguration == | ||
| + | === my.ini === | ||
Die wichtigsten Einträge in der Konfigurationsdatei <b>my.ini</b> sind: | Die wichtigsten Einträge in der Konfigurationsdatei <b>my.ini</b> sind: | ||
| Line 43: | Line 44: | ||
<pre> | <pre> | ||
| + | # SERVER SECTION | ||
| + | # ---------------------------------------------------------------------- | ||
| + | # | ||
| + | # The following options will be read by the MySQL Server. Make sure that | ||
| + | # you have installed the server correctly (see above) so it reads this | ||
| + | # file. | ||
| + | # | ||
[mysqld] | [mysqld] | ||
| + | |||
| + | # The TCP/IP Port the MySQL Server will listen on | ||
port=3306 | port=3306 | ||
| − | basedir="D:/ | + | |
| − | datadir=" | + | |
| + | #Path to installation directory. All paths are usually resolved relative to this. | ||
| + | basedir="D:/bin/MySQL/" | ||
| + | |||
| + | #Path to the database root | ||
| + | datadir="D:/bin/MySQL/Data/" | ||
| + | |||
| + | # The default character set that will be used when a new schema or table is | ||
| + | # created and no character set is defined | ||
default-character-set=utf8 | default-character-set=utf8 | ||
| − | + | ||
| + | # The default storage engine that will be used when create new tables when | ||
default-storage-engine=INNODB | default-storage-engine=INNODB | ||
| − | |||
| − | |||
</pre> | </pre> | ||
| + | === my.cnf === | ||
Beispiel einer '''my.cnf''': | Beispiel einer '''my.cnf''': | ||
<pre> | <pre> | ||
Revision as of 13:49, 28 March 2008
MySQL das führende Open-Source-Datenbanksystem
MySQL ist in meiner Anwendungsarchitektur das Datenbank-System der ersten Wahl (siehe auch: OpenSourceSoftware).
Siehe auch: Unicode
Installation Server
- Definitive Software Library ID: MySQL
- Name: MySQL
- Version: 5.0.41-community-nt (ComputerGraumann) und 5.0.41-community-nt (ComputerKragenbaer)
- Hersteller/Bezugsquelle: http://www.mysql.com
- Installationsordner: D:\bin\MySQL
- Konfigurationsdateien: D:\bin\MySQL\my.ini (unter CVS ???)
- Datenbank-Ordner: E:\var\MySQL\data ---> Datensicherung
- default-character-set=latin1
- Programmstart: Windows-Service MySQL
- InnoDB tablespace: InstallDir......
Konfiguration
my.ini
Die wichtigsten Einträge in der Konfigurationsdatei my.ini sind:
# CLIENT SECTION # ---------------------------------------------------------------------- # # The following options will be read by MySQL client applications. # Note that only client applications shipped by MySQL are guaranteed # to read this section. If you want your own MySQL client program to # honor these values, you need to specify it as an option during the # MySQL client library initialization. # [client] port=3306 [mysql] default-character-set=utf8
und
# SERVER SECTION # ---------------------------------------------------------------------- # # The following options will be read by the MySQL Server. Make sure that # you have installed the server correctly (see above) so it reads this # file. # [mysqld] # The TCP/IP Port the MySQL Server will listen on port=3306 #Path to installation directory. All paths are usually resolved relative to this. basedir="D:/bin/MySQL/" #Path to the database root datadir="D:/bin/MySQL/Data/" # The default character set that will be used when a new schema or table is # created and no character set is defined default-character-set=utf8 # The default storage engine that will be used when create new tables when default-storage-engine=INNODB
my.cnf
Beispiel einer my.cnf:
[client] port = 3306 socket = mysql default-character-set=utf8 [mysqld] port = 3306 socket = mysql skip-locking init_connect='SET collation_connection = utf8_general_ci' init_connect='SET NAMES utf8' default-character-set=utf8 character-set-server = utf8 collation-server = utf8_general_ci [mysql] default-character-set=utf8 and, mysql> show variables like "%character%";show variables like "%collation%"; +--------------------------+------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | \xampp\mysql\share\charsets/ |
+--------------------------+------------------------------+ 7 rows in set (0.00 sec)
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+ 3 rows in set (0.00 sec) And, php default_charset ="utf-8" httpd.conf DefaultLanguage ko AddDefaultCharset UTF-8
Datenbankadministration
- User-Id für Administrator (root) mit Passwort versehen
- User-Id (ghost) gelöscht
- Datenbank-Backup / Datensicherung : d:\var\jobs\backupMySQL.bat (unter CVS)
Installalation ODBC-Treiber
Der aktuelle ODBC-Treiber für MySQL kommt direkt von www.mysql.com und trägt die Bezeichnung MySQL Connector/ODBC 3.5.1.
- Definitive Software Library ID: MySQL_ODBC
- Name: MySQL Connector/ODBC
- Version: 3.5.1
- Hersteller/Bezugsquelle: http://www.mysql.com/downloads
Installation JDBC-Treiber
Der berühmte Treiber von Mark Matthews (1998) (OpenSourceSoftware "MM.MySQL") wird seit August 2002 von MySQL unter der Bezeichnung MySQL Connector/J 3.0 offiziell unterstützt (Java). Mark Matthews verstärkt jetzt das MySQL-Team.
- Definitive Software Library ID: MySQL_JDBC
- Name: MySQL Connector/J
- Version: 3.0.15
- Hersteller/Bezugsquelle: http://www.mysql.com/downloads
- Installations-Ordner: {$JAVA_HOME}\jre\lib\ext Beispielsweise d:\Programme\Java\jdk1.5.0_04\jre\lib\ext
- Installation: Kopieren der Datei mysql-connector-java-3.0.15-ga-bin.jar in den Installationsordner
- Informationen zur Benutzung
- Klasse: com.mysql.jdbc.Driver
- URL: jdbc:mysql://localhost/datenbankname
Problems and Solutions
My SQL 4.1 verwendet zum Passwort-Hashing SHA1 (160 Bit = 40 Zeichen) Infos: http://pajhome.org.uk/crypt/md5/
Workaround
Angeblich soll das Problem mit der PHP MySQL client library (libmysql.dll) auch durch ein Upgrade von PHP nicht gelöst werden können. Daher habe ich das von MySql veröffentlichte Workaraound [[1]] benutzt:
- Reset the password to pre-4.1 style for each user that needs to use a pre-4.1 client program. This can be done using the SET PASSWORD statement and the OLD_PASSWORD() function:
- mysql> SET PASSWORD FOR wikiuser@localhost = OLD_PASSWORD('newpwd');
- Tell the server to use the older password hashing algorithm:
- Start mysqld with the --old-passwords option. (Eingetragen in: my.ini ???)
| User | Password | Hash | Application |
| root | lotusnotes | SHA1 | ... |
| knowhowuser | ... | old | Know... |
| mediauser | ... | old | MediaWiki |
| linkuser | ... | old | PhpLinks |
| owluser | ... | old | Owl |
| pmauser | ... | old | PhpMyAdmin |
| wikiuser | ... | old | Wiki |
MySql Clients
Siehe: DatenbankFrontends
- PHP 4.3.9 kann es nicht
- PhpMyAdmin 2.6.0 kann es nicht (wegen PHP 4.3.9 ???)
- MySQL-Front 3.0 kann es
- SQLyog v3.11 kann es nicht, v4.1 kann es
- Navicat 6.0.7 kann es
- DBManager professional 2.2.0 kann es nicht, Version 3.1 kann es
- DbVisualizer Free 4.3.4 kann es
Datenbanken
| Datenbankname | Nutzung |
| asom | Application Sizing Modell |
| fruits | Für SQL-Workshop im PhpWorkshop |
| gmo | Migrierte GMO-Access-Datenbanken |
| joomla | für WebsiteJoomla |
| knowhow | Mein KnowlegeManagement: phpKnowhow |
| medien | MediaLibrary mit Videos etc. |
| mysql | Die Standard-Datenbank von MySQL |
| owl | Owl DokumentenManagement |
| phplinks | Anwendung PhpLinks (Link-Management) |
| phpwiki | Mein altes phpwiki (migriert nach TWiki) |
| telefonbuch | Telefonnummern zur Inverssuche (u.a.) |
| test | Die Standard-Test-Datenbank von MySQL |
| wikicons | MediaWiki WebsiteWikiConsulting |
| wikifaulkner | MediaWiki WebsiteMonikaFaulkner |
| wikikracht | MediaWiki WebsiteWiki |
| wikilexikon | MediaWiki WebsiteLexikon |
| wordpress | WordPress |
| xanadu | Meine Archiv-Datenbank (Anwendung Archivierung) |
Notizen zu MySQL
Volltext-Index
Anlegen einer Tabelle mit Volltext-Index:
CREATE TABLE articles ( id INT UNSIGGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT(title,body) );
Beispielhafte Such-Anfragen über den Volltext-Index:
SELECT * FROM articles WHERE MATCH(title,body) AGAINST('nadel');
SELECT *,MATCH(title,body) AGAINST('nadel') as Score FROM articles WHERE MATCH(title,body) AGAINST('nadel');
SELECT * FROM articles WHERE MATCH(title,body) AGAINST('+nadel -streifen' IN BOOLEAN MODE);
-- Main.DietrichKracht - 17 May 2004
