|
|
| (8 dazwischenliegende Versionen von 3 Benutzern werden nicht angezeigt) |
| Zeile 1: |
Zeile 1: |
| =Inbetriebnahme=
| | *[[Mysql allgmein]] |
| ==Installation==
| | *[[Mysql an Grafana]] |
| root@asuka:~# aptitude install mysql-server mysql-client
| | *[[Mysql Password reset]] |
| ==Schnellübersicht==
| | *[[Mysql Kompakt]] |
| *[[mysql-schnell-übersicht]] | | *[[Mysql Logging]] |
| *[[mysql Allgemein]] | | *[[Mysql ODBC]] |
| | |
| ==Login==
| |
| root@asuka:~# mysql --user root -h localhost -p
| |
| Enter password: XXXXXX
| |
| Welcome to the MySQL monitor. Commands end with ; or \g.
| |
| Your MySQL connection id is 48
| |
| Server version: 5.1.41-3ubuntu12 (Ubuntu)
| |
|
| |
| Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
| |
|
| |
| mysql>
| |
| | |
| ==Status überprüfen==
| |
| mysql> status;
| |
| --------------
| |
| mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1
| |
|
| |
| Connection id: 48
| |
| SSL: Not in use
| |
| Current pager: stdout
| |
| Using outfile: ''
| |
| Using delimiter: ;
| |
| Server version: 5.1.41-3ubuntu12 (Ubuntu)
| |
| Protocol version: 10
| |
| Connection: Localhost via UNIX socket
| |
| Client characterset: latin1
| |
| Server characterset: latin1
| |
| UNIX socket: /var/run/mysqld/mysqld.sock
| |
| Uptime: 41 min 4 sec
| |
|
| |
| Threads: 1 Questions: 207 Slow queries: 0 Opens: 476 Flush tables: 1 Open tables: 64 Queries per second avg: 0.84
| |
| --------------
| |
| | |
| ==Hilfe aufrufen==
| |
| mysql> help contents;
| |
| You asked for help about help category: "Contents"
| |
| For more information, type 'help <item>', where <item> is one of the following
| |
| categories:
| |
| Account Management
| |
| Administration
| |
| Compound Statements
| |
| Data Definition
| |
| Data Manipulation
| |
| Data Types
| |
| Functions
| |
| Functions and Modifiers for Use with GROUP BY
| |
| Geographic Features
| |
| Language Structure
| |
| Plugins
| |
| Table Maintenance
| |
| Transactions
| |
| User-Defined Functions
| |
| Utility
| |
| | |
| =Suchen und anzeigen=
| |
| ==Datenbanken anzeigen==
| |
| mysql> show databases;
| |
| +--------------------+
| |
| | Database |
| |
| +--------------------+
| |
| | information_schema |
| |
| | mysql |
| |
| +--------------------+
| |
| 2 rows in set (0.00 sec)
| |
| | |
| ==Datenbank auswählen==
| |
| mysql> use mysql;
| |
| Reading table information for completion of table and column names
| |
| You can turn off this feature to get a quicker startup with -A
| |
|
| |
| Database changed
| |
| | |
| ==Datenbank erstellen==
| |
| mysql> create database world;
| |
| Query OK, 1 row affected (0.00 sec)
| |
| mysql> show databases;
| |
| +--------------------+
| |
| | Database |
| |
| +--------------------+
| |
| | information_schema |
| |
| | mysql |
| |
| | world |
| |
| +--------------------+
| |
| 3 rows in set (0.00 sec)
| |
| | |
| ===Download von Beispieldaten===
| |
| root@asuka:~# wget http://downloads.mysql.com/docs/world.sql.gz
| |
| oder bei problemen hier
| |
| [[Media:World.sql.gz]]
| |
| | |
| root@asuka:~# gunzip World.sql.gz
| |
| | |
| ==Datenbank mit Daten von Backup füllen==
| |
| root@asuka:~# mysql world < world.sql -p
| |
| Enter password:
| |
| | |
| ==Datenbank wechseln==
| |
| mysql> use world
| |
| Reading table information for completion of table and column names
| |
| You can turn off this feature to get a quicker startup with -A
| |
|
| |
| Database changed
| |
| | |
| ==Tabellen einer Datenbank anzeigen==
| |
| mysql> show tables;
| |
| +-----------------+
| |
| | Tables_in_world |
| |
| +-----------------+
| |
| | City |
| |
| | Country |
| |
| | CountryLanguage |
| |
| +-----------------+
| |
| 3 rows in set (0.00 sec)
| |
| | |
| ==Tabellenbeschreibung anzeigen==
| |
| mysql> describe City;
| |
| +-------------+----------+------+-----+---------+----------------+
| |
| | Field | Type | Null | Key | Default | Extra |
| |
| +-------------+----------+------+-----+---------+----------------+
| |
| | ID | int(11) | NO | PRI | NULL | auto_increment |
| |
| | Name | char(35) | NO | | | |
| |
| | CountryCode | char(3) | NO | | | |
| |
| | District | char(20) | NO | | | |
| |
| | Population | int(11) | NO | | 0 | |
| |
| +-------------+----------+------+-----+---------+----------------+
| |
| 5 rows in set (0.00 sec)
| |
| | |
| ==Einträge anzeigen==
| |
| mysql> select * from City;
| |
| +----+------------------+-------------+---------------+------------+
| |
| | ID | Name | CountryCode | District | Population |
| |
| +----+------------------+-------------+---------------+------------+
| |
| | 1 | Kabul | AFG | Kabol | 1780000 |
| |
| | 2 | Qandahar | AFG | Qandahar | 237500 |
| |
| | 3 | Herat | AFG | Herat | 186800 |
| |
| | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
| |
| | 5 | Amsterdam | NLD | Noord-Holland | 731200 |
| |
| (...)
| |
| | |
| ==Nur die ersten 10 Einträge anzeigen==
| |
| mysql> select * from City limit 10;
| |
| +----+----------------+-------------+---------------+------------+
| |
| | ID | Name | CountryCode | District | Population |
| |
| +----+----------------+-------------+---------------+------------+
| |
| | 1 | Kabul | AFG | Kabol | 1780000 |
| |
| | 2 | Qandahar | AFG | Qandahar | 237500 |
| |
| | 3 | Herat | AFG | Herat | 186800 |
| |
| | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
| |
| | 5 | Amsterdam | NLD | Noord-Holland | 731200 |
| |
| | 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
| |
| | 7 | Haag | NLD | Zuid-Holland | 440900 |
| |
| | 8 | Utrecht | NLD | Utrecht | 234323 |
| |
| | 9 | Eindhoven | NLD | Noord-Brabant | 201843 |
| |
| | 10 | Tilburg | NLD | Noord-Brabant | 193238 |
| |
| +----+----------------+-------------+---------------+------------+
| |
| 10 rows in set (0.00 sec)
| |
| | |
| ==Anzeigen von Einträgen mit einer Bedingung==
| |
| mysql> select * from City where Population > 5000000;
| |
| +------+-------------------+-------------+-------------------+------------+
| |
| | ID | Name | CountryCode | District | Population |
| |
| +------+-------------------+-------------+-------------------+------------+
| |
| | 206 | S�o Paulo | BRA | S�o Paulo | 9968485 |
| |
| | 207 | Rio de Janeiro | BRA | Rio de Janeiro | 5598953 |
| |
| | 456 | London | GBR | England | 7285000 |
| |
| | 608 | Cairo | EGY | Kairo | 6789479 |
| |
| | 939 | Jakarta | IDN | Jakarta Raya | 9604900 |
| |
| (...)
| |
| | |
| ==Umstellen der Zeichenkodierung==
| |
| mysql> SET NAMES 'utf8';
| |
| Query OK, 0 rows affected (0.00 sec)
| |
| | |
| Weil:
| |
| mysql> select * from City where Population > 5000000;
| |
| +------+---------------------+-------------+---------------------+------------+
| |
| | ID | Name | CountryCode | District | Population |
| |
| +------+---------------------+-------------+---------------------+------------+
| |
| | 206 | '''São''' Paulo | BRA | '''São''' Paulo | 9968485 |
| |
| | 207 | Rio de Janeiro | BRA | Rio de Janeiro | 5598953 |
| |
| (...)
| |
| | |
| ==Bestimmte Spalten anzeigen aufgrund einer Bedingung==
| |
| mysql> select Name,Population from City where Population > 7000000;
| |
| +-------------------+------------+
| |
| | Name | Population |
| |
| +-------------------+------------+
| |
| | São Paulo | 9968485 |
| |
| | London | 7285000 |
| |
| | Jakarta | 9604900 |
| |
| | Mumbai (Bombay) | 10500000 |
| |
| | Delhi | 7206704 |
| |
| | Tokyo | 7980230 |
| |
| | Shanghai | 9696300 |
| |
| | Peking | 7472000 |
| |
| | Seoul | 9981619 |
| |
| | Ciudad de México | 8591309 |
| |
| | Karachi | 9269265 |
| |
| | Istanbul | 8787958 |
| |
| | Moscow | 8389200 |
| |
| | New York | 8008278 |
| |
| +-------------------+------------+
| |
| 14 rows in set (0.00 sec)
| |
| | |
| ==Bestimmte Spalten anzeigen aufgrund mehrerer Bedingungen==
| |
| mysql> select Name from Country where Continent='Europe' AND Population > 30000000;
| |
| +--------------------+
| |
| | Name |
| |
| +--------------------+
| |
| | United Kingdom |
| |
| | Spain |
| |
| | Italy |
| |
| | Poland |
| |
| | France |
| |
| | Germany |
| |
| | Ukraine |
| |
| | Russian Federation |
| |
| +--------------------+
| |
| 8 rows in set (0.00 sec)
| |
| | |
| ==Ergebnis anzeigen mit Sortierung==
| |
| mysql> select Name,Population from Country where Continent='Europe' AND Population > 30000000 Order by Population;
| |
| +--------------------+------------+
| |
| | Name | Population |
| |
| +--------------------+------------+
| |
| | Poland | 38653600 |
| |
| | Spain | 39441700 |
| |
| | Ukraine | 50456000 |
| |
| (...)
| |
| | |
| | |
| ==Ergebnis anzeigen mit absteigender Sortierung==
| |
| mysql> select Name,Population from Country where Continent='Europe' AND Population > 30000000 Order by Population desc;
| |
| +--------------------+------------+
| |
| | Name | Population |
| |
| +--------------------+------------+
| |
| | Russian Federation | 146934000 |
| |
| | Germany | 82164700 |
| |
| | United Kingdom | 59623400 |
| |
| | France | 59225700 |
| |
| | Italy | 57680000 |
| |
| | Ukraine | 50456000 |
| |
| | Spain | 39441700 |
| |
| | Poland | 38653600 |
| |
| +--------------------+------------+
| |
| 8 rows in set (0.00 sec)
| |
| | |
| =Erstellen von Datenbanken=
| |
| ==Datenbank erstellen und auswählen==
| |
| mysql> create database telefonbuch;
| |
| Query OK, 1 row affected (0.00 sec)
| |
| mysql> use telefonbuch;
| |
| Database changed
| |
| | |
| ==Tabelle erstellen==
| |
| mysql> create table rufnummern (
| |
| -> id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
| |
| -> vorname VARCHAR(128),
| |
| -> nachname VARCHAR(128),
| |
| -> vorwahl VARCHAR(32),
| |
| -> rufnummer VARCHAR(32)
| |
| -> );
| |
| Query OK, 0 rows affected (0.03 sec)
| |
| | |
| ==Datensätze in die Tabelle eintragen==
| |
| mysql> insert into
| |
| -> rufnummern (id,vorname,nachname,vorwahl,rufnummer)
| |
| -> values
| |
| -> (1,
| |
| -> 'Karl'
| |
| -> ,
| |
| -> 'Heinz',
| |
| -> '001',
| |
| -> '123456'
| |
| -> );
| |
| Query OK, 1 row affected (0.00 sec)
| |
| | |
| ==Datensatz anzeigen==
| |
| mysql> select * from rufnummern;
| |
| +----+---------+----------+---------+-----------+
| |
| | id | vorname | nachname | vorwahl | rufnummer |
| |
| +----+---------+----------+---------+-----------+
| |
| | 1 | Karl | Heinz | 001 | 123456 |
| |
| +----+---------+----------+---------+-----------+
| |
| 1 row in set (0.00 sec)
| |
| | |
| ==Weitere Datensätze eintragen==
| |
| mysql> insert into rufnummern (vorname,nachname,vorwahl,rufnummer) values ('xinux' , 'tux', '002', '654321' );
| |
| Query OK, 1 row affected (0.00 sec)
| |
| | |
| Die id wird durch Auto increment eigenständig erhöht.
| |
| | |
| ==Erweiterter Datensatz anzeigen==
| |
| mysql> select * from rufnummern;
| |
| +----+---------+----------+---------+-----------+
| |
| | id | vorname | nachname | vorwahl | rufnummer |
| |
| +----+---------+----------+---------+-----------+
| |
| | 1 | Karl | Heinz | 001 | 123456 |
| |
| | 2 | xinux | tux | 002 | 654321 |
| |
| +----+---------+----------+---------+-----------+
| |
| 2 rows in set (0.00 sec)
| |
| | |
| ==Aktualisierung von Datensätzen==
| |
| mysql> update rufnummern set rufnummer = '777444' where vorname = 'Karl';
| |
| Query OK, 1 row affected (0.00 sec)
| |
| Rows matched: 1 Changed: 1 Warnings: 0
| |
| | |
| ==Aktualisierter Datensatz anzeigen==
| |
| mysql> select * from rufnummern;
| |
| +----+---------+----------+---------+-----------+
| |
| | id | vorname | nachname | vorwahl | rufnummer |
| |
| +----+---------+----------+---------+-----------+
| |
| | 1 | Karl | Heinz | 001 | 777444 |
| |
| | 2 | xinux | tux | 002 | 654321 |
| |
| +----+---------+----------+---------+-----------+
| |
| 2 rows in set (0.00 sec)
| |
| | |
| =Dump erzeugen=
| |
| mysqldump datenbankname -p > datenbankname.dmp
| |
| | |
| =Benutzer=
| |
| | |
| ==Benutzer anzeigen==
| |
| | |
| select * from mysql.user;
| |
| | |
| ==Benutzer anlegen==
| |
| | |
| mysql -p
| |
| | |
| CREATE USER 'benutzername'@'localhost' IDENTIFIED BY 'geheim';
| |
| | |
| GRANT ALL PRIVILEGES ON datenbankname.tabellenname TO 'benutzername'@'localhost';
| |
| | |
| FLUSH PRIVILEGES;
| |
| | |
| ==Benutzer löschen==
| |
| | |
| mysql -p
| |
| | |
| DROP USER benutzername;
| |
| | |
| ==Passwort einer Benutzers ändern==
| |
| | |
| mysql -p
| |
| | |
| UPDATE user SET password=PASSWORD('geheim') where User='benutzername';
| |
| | |
| FLUSH PRIVILEGES;
| |
| | |
| =MySQL Root Passwort neu setzen =
| |
| Alternativ können Sie das neue Passwort auf jeder Plattform auch mit dem Client mysql einstellen (aber diese Methode ist nicht so sicher):
| |
| | |
| 1. Halten Sie mysqld an und starten Sie ihn erneut mit den Optionen --skip-grant-tables --user=root (Windows-Nutzer lassen --user=root weg).
| |
| root@asuka:~# /etc/init.d/mysql stop
| |
| root@asuka:~# mysqld --skip-grant-tables --user=root
| |
| | |
| 2. Verbinden Sie sich mit folgendem Befehl mit dem mysqld-Server:
| |
| shell> mysql -u root
| |
| | |
| 3. Geben Sie im mysql-Client folgende Anweisungen:
| |
| | |
| mysql> UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';
| |
| mysql> FLUSH PRIVILEGES;
| |
| | |
| Ersetzen Sie „newpwd“ durch das root-Passwort, das Sie in Wirklichkeit benutzen möchten.
| |
| mysql> quit
| |
| Bye
| |
| root@asuka:/etc# /etc/init.d/mysql restart
| |
| * Stopping MySQL database server mysqld
| |
| * Starting MySQL database server mysqld
| |
| | |
| 4. Das neue Passwort sollte nun funktionieren.
| |
| | |
| =Datenbank löschen=
| |
| | |
| mysql -p
| |
| | |
| DROP DATABASE datenbankname;
| |
| | |
| =Tabelle löschen=
| |
| | |
| mysql -p
| |
| | |
| DROP TABLE tabellenname;
| |
| | |
| == Siehe auch ==
| |
| * [[PostgresSQL root Passwort neu setzen]] | |