MySQL
Aussprache des Namens:
Die offizielle Aussprache ist Mei-es-kju-ell. Leute, die „Mei Sequel“ sagen, werden aber nicht strafrechtlich verfolgt.
Das offizielle Handbuch für die Version 5.1 finden Sie hier:
MySQL Proxy:
passwortlosen Login
~/.my.cnf
[client] host = localhost user = root password = geheim socket = /var/run/mysqld/mysqld.sock
speziell bei Debian kann man das (als „root“) auch so machen:
# ln -s /etc/mysql/debian.cnf ~/.my.cnf
meine_zugangsdaten.cnf
[client] user = root password = ganzgeheim socket = /var/run/mysqld/mysqld.sock
Das kann man so machen, zu beachten ist, dass die Option –defaults-file= die erste sein muss:
# mysqlshow --defaults-file=meine_zugangsdaten.cnf -t -hmeinedb
Start mit MySQL
MySQL commands:
Note that all text commands must be first on line and end with ';' help (\h) Display this help. ? (\?) Synonym for `help'. clear (\c) Clear command. connect (\r) Reconnect to the server. Optional arguments are db and host. edit (\e) Edit command with $EDITOR. ego (\G) Send command to mysql server, display result vertically. exit (\q) Exit mysql. Same as quit. go (\g) Send command to mysql server. nopager (\n) Disable pager, print to stdout. notee (\t) Don't write into outfile. pager (\P) Set PAGER [to_pager]. Print the query results via PAGER. print (\p) Print current command. quit (\q) Quit mysql. rehash (\#) Rebuild completion hash. source (\.) Execute a SQL script file. Takes a file name as an argument. status (\s) Get status information from the server. tee (\T) Set outfile [to_outfile]. Append everything into given outfile. use (\u) Use another database. Takes database name as argument.
DB-Benutzer löschen
# echo "DROP USER fritz" | mysql -uroot -p mysql
DB-Benutzer anlegen
/home/bin/add_db_user.sh
#!/bin/bash
# Mit diesem Script wird ein unpreveligierter User angelegt,
# der nur die Rechte SELECT, INSERT und UPDATE für genau eine
# DB bekommt.
while [ "${#}" -ne "0" ]; do
case "$1" in
-dbh)
DBHOST="${2}"
shift
;;
-dbn)
DBNAME="${2}"
shift
;;
-dbu)
DBUSER="${2}"
shift
;;
-dbp)
DBPASS="${2}"
shift
;;
-rp)
RTPASS="${2}"
shift
;;
-h)
echo "Usage: $0 -h" >&2
echo "Usage: $0 -dbh [DB-Host] -dbn [DB-Name] -dbu [Benutzer] -dbp [Passwort] -rp [root-Passwort]" >&2
echo "Usage: $0 -dbh meine.datenbank.de -dbn db_name -dbu fritz -dbp geheim -rp ganzgeheim" >&2
exit 1
;;
*)
if [ "$(echo "${1}"|egrep '^-')" ] ; then
echo "Der Parameter '${1}' wird nicht unterstützt!"
fi
shift
;;
esac
done
if [ -z "${DBHOST}" -o -z "${DBNAME}" -o -z "${DBUSER}" -o -z "${DBPASS}" -o -z "${RTPASS}" ] ; then
echo "
DBHOST='${DBHOST}'
DBNAME='${DBNAME}'
DBUSER='${DBUSER}'
DBPASS='${DBPASS}'
RTPASS='${RTPASS}'
"
${0} -h
exit 2
fi
ping -c3 ${DBHOST}
if [ "$?" != "0" ] ; then
echo "
${DBHOST} ist nicht erreichbar...
"
exit 3
fi
# Benutzer anlegen
echo "CREATE USER ${DBUSER} IDENTIFIED BY '${DBPASS}';" | mysql -t -h ${DBHOST} -uroot -p${RTPASS} mysql
# Blle Rechte weg
echo "REVOKE ALL PRIVILEGES,GRANT OPTION FROM ${DBUSER};" | mysql -t -h ${DBHOST} -uroot -p${RTPASS}
# bekommt nur die Rechte: SELECT, INSERT und UPDATE
echo "GRANT SELECT ON * TO ${DBUSER}; FLUSH PRIVILEGES;" | mysql -t -h ${DBHOST} -uroot -p${RTPASS} ${DBNAME}
echo "GRANT INSERT ON * TO ${DBUSER}; FLUSH PRIVILEGES;" | mysql -t -h ${DBHOST} -uroot -p${RTPASS} ${DBNAME}
echo "GRANT UPDATE ON * TO ${DBUSER}; FLUSH PRIVILEGES;" | mysql -t -h ${DBHOST} -uroot -p${RTPASS} ${DBNAME}
# Test
mysqlshow -t -h${DBHOST} -u${DBUSER} -p${DBPASS}
mysqlshow -t -h${DBHOST} -u${DBUSER} -p${DBPASS} ${DBNAME}
alle MySQL-DB-Back-Ends reparieren
# mysqlcheck --repair -uBENUTZER -pPASSWORT meine_datenbank meine_tabelle # mysqlcheck --analyze -uBENUTZER -pPASSWORT meine_datenbank meine_tabelle # mysqlcheck --optimize -uBENUTZER -pPASSWORT meine_datenbank meine_tabelle
nur MyISAM reparieren
Fehlerüberprüfung
Überprüfung:
# myisamchk -c /var/lib/mysql/meine_datenbank/meine_tabelle.MYI
Reparatur möglichkeiten
Wenn bei der obigen Überprüfung Fehler aufgetreten sind, dann wie folgt weiter.
alte Reparaturreste entfernen:
# rm -f /var/lib/mysql/meine_datenbank/meine_tabelle.TMD
schnelle Reparatur
# myisamchk -r /var/lib/mysql/meine_datenbank/meine_tabelle.MYI
genaue Reparatur
Wenn bei der Schnellreparatur mir “-r“ diese Fehlermeldung kommt:
# myisamchk -r /var/lib/mysql/meine_datenbank/meine_tabelle.MYI myisamchk: error: myisam_sort_buffer_size is too small
dann hilft nur noch die Option “-o“:
# myisamchk -o /var/lib/mysql/meine_datenbank/meine_tabelle.MYI
Backup
Sicherung ([database].sql) erstellen
Dump von allen Datenbanken erstellen:
# mysqldump -uroot -pgeheim -A > [database].sql
Dump von einer bestimmten Datenbank erstellen:
# mysqldump -uroot -pgeheim -B [database] > [database].sql
Datenbank löschen:
# mysqladmin DROP [database]
Sicherung ([database].sql) einlesen
User anlegen, Datenbank anlegen + dump einlesen:
# mysql -u[user] -p[passwort] -e "INSERT INTO user (host,user) VALUES ('localhost','[user]');" mysql
# mysql -u[user] -p[passwort] -e "UPDATE user SET password=password('[passwort]') WHERE User='[user]';" mysql
# mysql -u[user] -p[passwort] reload
# mysql -u[user] -p[passwort] -e "CREATE DATABASE [database];" mysql
# cat [database].sql | mysql -u[user] -p[passwort] [database]
ODER
# mysql -u[user] -p[passwort] -e "CREATE DATABASE [database];" mysql # mysql -u[user] -p[passwort] [database] < [database].sql
Backup auf Basis von LVM Snapshots
Möglicherweise wird die Implementierung eines mysql Backup auf Basis von LVM Snapshots, doch nicht allzu schwer.
Allerdings wurde das ganze hier auf einem Single System umgesetzt und nicht auf einem Master Slave System. Man müsste das ganze dann noch um das Stoppen des Sync vom Master erweitern. Im zweiten Link hat jemand das ganze über ein kleines PHP Script realisiert, angeblich ebenfalls auf einem Singlesystem ohne merkbare Aussetzer.
Und hier ein Link zu einer Master Slave Konstellation:
Das Script sieht ebenfalls recht überschaubar aus und sollte mit relativ wenig Aufwand an unsere Umgebung anpassbar sein.
Daten direkt zwischen Datenbanken kopieren
DB-Layout sichern
Layout der Tabelle „SystemEvents“ aus der DB „Syslog“ sichern:
# mysqldump --no-data Syslog SystemEvents > leere_tabelle.sql
Tabellenname ändern
# sed -i 's/SystemEvents/temp/g' leere_tabelle.sql
leere temp-Tabelle anlegen
# cat leere_tabelle.sql | mysql -t Syslog
alles aus „SystemEvents“ nach „temp“ kopieren:
echo " INSERT INTO SystemEvents SELECT * FROM temp ORDER BY ReceivedAt " | mysql -t Syslog
oder nur bestimmte Spalten aus „SystemEvents“ nach „temp“ kopieren:
echo " INSERT INTO SystemEvents (ReceivedAt,FromHost,Message,SysLogTag) SELECT ReceivedAt,FromHost,Message,SysLogTag FROM temp ORDER BY ReceivedAt " | mysql -t Syslog
Jetzt kann man „temp“ dumpen und in die Ziel-DB einspielen und dort dann in die entgültige Tabelle kopieren.
SQL
Benutzer anlegen und löschen
# echo "INSERT INTO user (host,user) VALUES ('%','fritz');" | mysql -uroot -p[passwort] mysql
# echo "DROP USER fritz;" | mysql -uroot -p[passwort] mysql
Passwort ändern
Passwort ändern:
# mysql -uroot [-pgeheim]
> use mysql;
> update user set password=password('geheim') where User='root';
> \q
# mysqladmin reload
Jetzt sollte ein:
# mysql -uroot -pgeheim
gehen.
ODER
# mysql -uroot -e "update user set password=password('geheim') where User='root';" mysql
# mysqladmin reload
ODER
# mysqladmin -uroot -p'geheim' # mysqladmin reload
dem Benutzer alle Rechte geben
# echo "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'geheim';FLUSH PRIVILEGES;" | mysql -u root -p
dem Benutzer nur bestimmte Rechte geben
als erstes nehmen wir dem neuen User ALLE Rechte:
# echo "REVOKE ALL PRIVILEGES, GRANT OPTION FROM fritz;" | mysql -hdbrechner -uroot -pgeheim datenbank
jetzt bekommt er nur Leserechte auf seine Datenbanken:
# echo "GRANT SELECT ON * TO fritz;FLUSH PRIVILEGES;" | mysql -hdbrechner -uroot -pgeheim datenbank
zum Schluss bekommt er noch etwas Schreibrechte auf einzelne Tabellen:
# echo "GRANT INSERT ON tabelle01 TO fritz;FLUSH PRIVILEGES;" | mysql -hdbrechner -uroot -pgeheim datenbank
Es kann immer nur eine einzige Tabelle angegeben werden!
jetzt lassen wir uns seine aktuellen Rechte noch einmal anzeigen, damit wir sicher sind, dass alles nach unserem Wunsch ausgeführt wurde:
# echo "SHOW GRANTS FOR 'fritz' \G;" | mysql -hdbrechner -uroot -pgeheim
oder
# echo "SHOW GRANTS FOR 'fritz';" | mysql -hdbrechner -uroot -pgeheim
DB-User mit eingeschränkten Rechten anlegen
# echo "GRANT SELECT,UPDATE,INSERT,DELETE ON Tabelle TO 'Benutzer'@'ClientRechner' IDENTIFIED BY 'geheim'; FLUSH PRIVILEGES;" | mysql --defaults-file=meine_zugangsdaten.cnf -t -hServerRechner Datenbank
Hier ist ein Beispiel, in dem der User fritz mit dem Passwort geheim und den Rechten SELECT,UPDATE,INSERT,DELETE für die DB datenbank auf dem DBMS-Host dbrechner angelegt wird:
# echo "DROP USER fritz;CREATE USER fritz IDENTIFIED BY 'geheim';REVOKE ALL PRIVILEGES, GRANT OPTION FROM fritz; GRANT SELECT,UPDATE,INSERT,DELETE ON * TO 'fritz'@'%' IDENTIFIED BY 'geheim';FLUSH PRIVILEGES;" | mysql -uroot -p -hdbrechner datenbank
Datenbank anlegen und löschen
# echo "CREATE DATABASE IF NOT EXISTS meinedb;" | mysql -hdbrechner -uroot -pgeheim datenbank # echo "DROP DATABASE IF EXISTS meinedb;" | mysql -hdbrechner -uroot -pgeheim datenbank
Tabelle anlegen und löschen
eine neue Tabelle anlegen:
# echo "CREATE TABLE IF NOT EXISTS tabelle01;" | mysql -hdbrechner -uroot -pgeheim datenbank
die ganze Tabelle wird komplett gelöscht:
# echo "DROP TABLE IF EXISTS tabelle01;" | mysql -hdbrechner -uroot -pgeheim datenbank
Datensätze anlegen, ändern und löschen
einen Datensatz anlegen bzw. in die Tabelle schreiben:
# echo "INSERT INTO tabelle01 (spalte01,spalte02) VALUES ('wert01','wert02');" | mysql -uroot -pgeheim datenbank
einen Datensatz ändern:
# echo "UPDATE tabelle01 SET spalte01 = 'neuer Wert' WHERE spalte02 = 'Bedingungswert';" | mysql -h myserver -u fritz -pgeheim datenbank
alle Datensätze mit „alte Daten“ in der Spalte „spalte“, werden gelöscht:
# echo "DELETE FROM tabelle01 WHERE spalte='alte Daten';" | mysql -hdbrechner -uroot -pgeheim datenbank
alle Datensätze, die die Zeichenkette „neu“ in der Spalte „spalte“ haben werden nicht gelöscht, der Rest wird gelöscht:
# echo "DELETE FROM tabelle01 WHERE spalte NOT LIKE '%neu%';" | mysql -hdbrechner -uroot -pgeheim datenbank
Die „DELETE“-Anweisung benötigt IMMER eine „WHERE“-Bedingung!
sonstiges
Einen passwortlosen User mit sehr eingeschränkten Rechten anlegen:
# echo "DROP USER fritz;DROP DATABASE IF EXISTS meinedb;" | mysql -uroot -p[passwort] mysql
# echo "CREATE DATABASE meinedb;" | mysql -uroot -p[passwort] mysql
# echo "INSERT INTO user (host,user) VALUES ('%','fritz');" | mysql -uroot -p[passwort] mysql
# echo "FLUSH PRIVILEGES;" | mysql -uroot -p[passwort] mysql
# echo "REVOKE ALL ON *.* TO fritz;" | mysql -uroot -p[passwort] meinedb
# echo "GRANT SELECT ON * TO fritz;" | mysql -uroot -p[passwort] meinedb
# echo "FLUSH PRIVILEGES;" | mysql -uroot -p[passwort] meinedb
„fritz“ kann jetzt nur die Tabellen aus „meinedb“ lesen, sonst nix.
einen Benutzer anlegen:
# CREATE USER fritz IDENTIFIED BY 'Geheim';
ihm alle Rechte nehmen:
# REVOKE ALL PRIVILEGES ON *.* FROM webteam;
ihm Leserechte für die DB „meinedb“ geben:
# GRANT SELECT ON meinedb.* TO 'fritz'@'%' IDENTIFIED BY 'Geheim';
Datenbanken und Tabellen anzeigen:
zeigt alle Datenbanken:
# mysqlshow -uroot -pgeheim
zeigt alle Tabellen von [DATENBANK]:
# mysqlshow -uroot -pgeheim [DATENBANK]
ODER
in die „Master-Datenbank“ gehen:
> use mysql;
zeigt alle Datenbanken:
> show databases;
zeigt alle Tabellen von mysql:
> show tables;
den Inhalt der Tabelle “[tabelle]“ anzeigen:
> SELECT * FROM [tabelle];
User anlegen, DB anlegen und dem User die Zugriffsrechte auf die DB geben:
# echo "CREATE USER dbuser IDENTIFIED BY 'geheim'; CREATE DATABASE IF NOT EXISTS [Datenbankname]; GRANT ALL ON [Datenbankname].* TO dbuser@localhost;" | mysql -u root -p
MySQL starten, ohne das Passwörter abgefragt werden:
# /usr/bin/safe_mysqld --skip-grant-tables
oder
# /usr/bin/mysqld_safe --skip-grant-tables
Passwort ändern:
# echo "SET PASSWORD FOR dbuser = PASSWORD('geheim');" | mysql -u root -p
User löschen:
# echo "DROP USER [böserjunge];" | mysql -t -h localhost -u root -pgeheim
Tabellenstruktur anzeigen:
# echo "SHOW CREATE TABLE [Tabellenname]" | mysql -t -h localhost -uroot -p [Datenbankname]
Datenbank anlegen:
# echo "CREATE DATABASE [Datenbankname];" | mysql -t -h localhost -uroot -p # echo "CREATE DATABASE IF NOT EXISTS [Datenbankname];" | mysql -t -h localhost -uroot -p
Datenbank löschen:
# echo "DROP DATABASE [Datenbankname];" | mysql -t -h localhost -uroot -p [Datenbankname] # echo "DROP DATABASE IF EXISTS [Datenbankname];" | mysql -t -h localhost -uroot -p [Datenbankname]
Tabelle anlegen:
# echo "CREATE TABLE [Tabellenname] (timestamp DATETIME)" | mysql -t -h localhost -uroot -p [Datenbankname]
In der Praxis sieht eine Tabelle eher etwas komplexer aus:
# echo "CREATE DATABASE IF NOT EXISTS [Datenbankname]; USE [Datenbankname]; CREATE TABLE IF NOT EXISTS [Tabellenname] ( timestamp DATETIME, resource_id SMALLINT, hostname VARCHAR(254) default NULL, ip4 VARCHAR(4096) default NULL, mac VARCHAR(2048) default NULL, bios_release_date VARCHAR(256) default NULL, bios_manufacturer VARCHAR(256) default NULL, bios_product_name VARCHAR(256) default NULL, bios_serial_number VARCHAR(256) default NULL, cpu_anzahl SMALLINT, bus VARCHAR(8) default NULL, vm VARCHAR(8) default NULL, htt SMALLINT, cpu_family VARCHAR(256) default NULL, cpu_manufacturer VARCHAR(256) default NULL, cpu_id VARCHAR(256) default NULL, cpu_signature VARCHAR(256) default NULL, cpu_core_count SMALLINT, cpu_core_enabled SMALLINT, memtotal VARCHAR(256) default NULL, hdd VARCHAR(256) default NULL, PRIMARY KEY (resource_id), KEY (bios_serial_number), KEY mac (mac) ) TYPE=MyISAM;" | mysql -t -h localhost -uroot -p
Tabelle löschen:
# echo "DROP TABLE [Tabellenname];" | mysql -t -h localhost -uroot -p [Datenbankname]
Alle Datensätze löschen:
# echo "DELETE FROM [Tabellenname];" | mysql -t -h localhost -uroot -p [Datenbankname]
Einen Datensatz löschen:
# echo "DELETE FROM [Tabellenname] WHERE [Spalte]='Wert';" | mysql -t -h localhost -uroot -p [Datenbankname]
Weitere Beispiele:
DB-Client-Programm: mysql DB-Server : myserver DB-Namen : [DatenBank] TAB-Namen : [DBTabelle]
Tabelle als Datei exportieren, hierbei gelten die MySQL-Standardwerte für das Dateiformat:
# echo "SELECT *
INTO OUTFILE '/tmp/[DBTabelle].txt'
FROM [DBTabelle] LIMIT 10;" | mysql -h myserver -u${DATABASE_USER} -p${DATABASE_PASSWORD} [DatenBank]
Tabelle als CSV-Datei exportieren:
# echo "SELECT *
INTO OUTFILE '/tmp/[DBTabelle].csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM [DBTabelle] LIMIT 10;" | mysql -h myserver -u${DATABASE_USER} -p${DATABASE_PASSWORD} [DatenBank]
Tabelle als CSV-Datei (Semikolon getrennt) exportieren:
# echo "SELECT *
INTO OUTFILE '/tmp/[DBTabelle].csv'
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
FROM [DBTabelle] LIMIT 10;" | mysql -h myserver -u${DATABASE_USER} -p${DATABASE_PASSWORD} [DatenBank]
Datenbanken zeigen:
# echo "SHOW DATABASES" | mysql -h myserver -u fritz # mysqlshow -h myserver -u fritz -p # mysqlshow -t -h myserver -u fritz -p
Tabellen zeigen:
# echo "SHOW TABLES" | mysql -h myserver -u fritz [DatenBank] # mysqlshow -h myserver -u fritz [DatenBank] -p # mysqlshow -t -h myserver -u fritz [DatenBank] -p
Tabelleninhalt zeigen:
# echo "SELECT * FROM [DBTabelle]" | mysql -h myserver -u fritz -p [DatenBank] # echo "SELECT * FROM [DBTabelle]" | mysql -t -h myserver -u fritz [DatenBank] -p
SUBSTRING:
# echo "SELECT hostname FROM belastungswaechter LIMIT 1;" | mysql -N -ufritz -pgeheim -hserver datenbank rechnername.domain.de # echo "SELECT SUBSTRING_INDEX(hostname,'.',1) FROM belastungswaechter LIMIT 1;" | mysql -N -ufritz -pgeheim -hserver datenbank rechnername # echo "SELECT SUBSTRING_INDEX(hostname,'.',2) FROM belastungswaechter LIMIT 1;" | mysql -N -ufritz -pgeheim -hserver datenbank rechnername.domain # echo "SELECT SUBSTRING_INDEX(hostname,'.',-2) FROM belastungswaechter LIMIT 1;" | mysql -N -ufritz -pgeheim -hserver datenbank domain.de
IP-Adressen in numerischer Reihenfolge anzeigen:
# echo "SELECT * FROM [DBTabelle] ORDER BY INET_ATON(ip_address) LIMIT 10;" | mysql -h myserver -u${DATABASE_USER} -p${DATABASE_PASSWORD} [DatenBank]
Tabelleninhalt nach der 3. Spalte sortieren (nur die ersten 10 Zeilen:
# echo "SELECT spalte1,spalte2,spalte3 FROM [DBTabelle] ORDER BY 3 ASC LIMIT 10" | mysql -h myserver -u fritz -p [DatenBank]
Tabelleninhalt in umgekehrtsortierter Reihenfolge zeigen (nur die letzten 10 Zeilen):
# echo "SELECT spalte1,spalte2,spalte3 FROM [DBTabelle] ORDER BY 3 DESC LIMIT 10" | mysql -h myserver -u fritz -p [DatenBank]
eine Liste aller unterschiedlichen Einträge aus der Spalte 3 zeigen (nur die letzten 10 Zeilen):
# echo "SELECT spalte1,spalte2,spalte3 FROM [DBTabelle] GROUP BY 3 ORDER BY 3 DESC LIMIT 10" | mysql -h myserver -u fritz -p [DatenBank]
Wichtig ist hierbei, dass „GROUP BY“ immer vor „ORDER BY“ stehen muss!
MySQL kann auch mit RegEx umgehgen, hier mal zwei Beispiele:
die letzten 50 Einträge anzeigen, die älter als 7 Tage sind (Spalte „DeviceReportedTime“) und eine IP in der Spalte „Message“ enthalten:
# echo "SELECT ID,DeviceReportedTime,Facility,FromHost,Message,SysLogTag FROM SystemEvents WHERE DeviceReportedTime < '$(date -d-7days +'%F %T')' AND Message REGEXP '[0-9]{1,3}[.][0-9]{1,3}[.][0-9]{1,3}[.][0-9]{1,3}' ORDER BY ID DESC LIMIT 50;" | mysql -t -ursyslog -p Syslog
Alle Datensätze löschen, die älter als 180 Tage sind (Spalte „DeviceReportedTime“) und eine IP in der Spalte „Message“ enthalten:
# echo "DELETE FROM SystemEvents WHERE DeviceReportedTime < '$(date -d-180days +'%F %T')' AND Message REGEXP '[0-9]{1,3}[.][0-9]{1,3}[.][0-9]{1,3}[.][0-9]{1,3}';"
Weitere Beispiele gibt es hier: http://dev.mysql.com/doc/refman/5.1/de/regexp.html und hier http://gnosis.cx/publish/programming/regular_expressions.html
Anzeige der Anzahl der Einträge pro Spalte3:
# echo "SELECT spalte3,COUNT(*) FROM [DBTabelle] GROUP BY 1 ORDER BY 1 DESC LIMIT 10" | mysql -h myserver -u fritz -p [DatenBank]
Will man den Wert in der „Spalte1“ auf „neuer Wert“ setzen, wenn die Bedingung erfüllt ist, dass der Wert „Bedingungswert“ in der „Spalte2“ vorhanden ist, dann geht das so:
# echo "UPDATE [DBTabelle] SET [Spalte1] = 'neuer Wert' WHERE [Spalte2] = 'Bedingungswert';" | mysql -h myserver -u fritz -p [DatenBank]
Infos zu den Tabellen abrufen:
# echo "SELECT table_name, table_type, engine FROM information_schema.tables;"|mysql -t -h localhost -uroot -p openqrm +---------------------------------------+-------------+--------+ | table_name | table_type | engine | +---------------------------------------+-------------+--------+ | CHARACTER_SETS | SYSTEM VIEW | MEMORY | | COLLATIONS | SYSTEM VIEW | MEMORY | | COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW | MEMORY | | COLUMNS | SYSTEM VIEW | MyISAM | | COLUMN_PRIVILEGES | SYSTEM VIEW | MEMORY | | KEY_COLUMN_USAGE | SYSTEM VIEW | MEMORY | | PROFILING | SYSTEM VIEW | MEMORY | | ROUTINES | SYSTEM VIEW | MyISAM | | SCHEMATA | SYSTEM VIEW | MEMORY | | SCHEMA_PRIVILEGES | SYSTEM VIEW | MEMORY | | STATISTICS | SYSTEM VIEW | MEMORY | | TABLES | SYSTEM VIEW | MEMORY | ... | appliance_info | BASE TABLE | MyISAM | | cloud_appliance | BASE TABLE | MyISAM | | cloud_config | BASE TABLE | MyISAM | | cloud_image | BASE TABLE | MyISAM | | cloud_ipgroups | BASE TABLE | MyISAM | | cloud_iplc | BASE TABLE | MyISAM | | cloud_iptables | BASE TABLE | MyISAM | | cloud_irlc | BASE TABLE | MyISAM | | cloud_nat | BASE TABLE | MyISAM | | cloud_private_image | BASE TABLE | MyISAM | | cloud_requests | BASE TABLE | MyISAM | | cloud_selector | BASE TABLE | MyISAM | | cloud_transaction | BASE TABLE | MyISAM | | cloud_users | BASE TABLE | MyISAM | | cloud_users_limits | BASE TABLE | MyISAM | | deployment_info | BASE TABLE | MyISAM | ...
Schema einer Tabelle anzeigen:
# echo "desc cloud_ipgroups;"|mysql -t -h localhost -uroot -p openqrm +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | ig_id | int(5) | YES | | NULL | | | ig_name | varchar(50) | YES | | NULL | | | ig_network | varchar(50) | YES | | NULL | | | ig_subnet | varchar(50) | YES | | NULL | | | ig_gateway | varchar(50) | YES | | NULL | | | ig_dns1 | varchar(50) | YES | | NULL | | | ig_dns2 | varchar(50) | YES | | NULL | | | ig_domain | varchar(50) | YES | | NULL | | | ig_activeips | int(5) | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+
# echo "desc cloud_iptables;"|mysql -t -h localhost -uroot -p openqrm +-----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+-------------+------+-----+---------+-------+ | ip_id | int(5) | YES | | NULL | | | ip_ig_id | int(5) | YES | | NULL | | | ip_appliance_id | int(5) | YES | | NULL | | | ip_cr_id | int(5) | YES | | NULL | | | ip_active | int(5) | YES | | NULL | | | ip_address | varchar(50) | YES | | NULL | | | ip_subnet | varchar(50) | YES | | NULL | | | ip_gateway | varchar(50) | YES | | NULL | | | ip_dns1 | varchar(50) | YES | | NULL | | | ip_dns2 | varchar(50) | YES | | NULL | | | ip_domain | varchar(50) | YES | | NULL | | +-----------------+-------------+------+-----+---------+-------+
Zeitangaben in SQL
# echo "SELECT DATE_SUB(NOW(), INTERVAL 2 HOUR);" | mysql -t -h localhost -uroot -p +---------------------------+ | (NOW() + INTERVAL 2 HOUR) | +---------------------------+ | 2012-02-15 23:36:38 | +---------------------------+ # echo "SELECT DATE_SUB(NOW(), INTERVAL 2 HOUR);" | mysql -t -h localhost -uroot -p +----------------------------------+ | DATE_SUB(NOW(), INTERVAL 2 HOUR) | +----------------------------------+ | 2012-02-15 19:36:41 | +----------------------------------+
Heute:
# echo "SELECT DATE(NOW());" | mysql -t -h localhost -uroot -p +-------------+ | DATE(NOW()) | +-------------+ | 2012-03-12 | +-------------+
# echo "SELECT CURDATE();" | mysql -t -h localhost -uroot -p +------------+ | CURDATE() | +------------+ | 2012-03-12 | +------------+
Gestern:
# echo "SELECT DATE(DATE_SUB(NOW(), INTERVAL 24 HOUR));" | mysql -t -h localhost -uroot -p +-----------------------------------------+ | DATE(DATE_SUB(NOW(), INTERVAL 24 HOUR)) | +-----------------------------------------+ | 2012-03-11 | +-----------------------------------------+
# echo "SELECT CURDATE() - interval 1 day;" | mysql -t -h localhost -uroot -p +----------------------------+ | CURDATE() - interval 1 day | +----------------------------+ | 2012-03-11 | +----------------------------+
Datenmenge bzw. Größe einer DB ausgeben
mysql> SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema; +--------------------+----------------------+ | Data Base Name | Data Base Size in MB | +--------------------+----------------------+ | information_schema | 0.00781250 | | mysql | 0.61825848 | | Syslog | 1607.33359146 | +--------------------+----------------------+ 3 rows in set (0.09 sec)
Index
Die Regel sagt, dass man einen Index auf einer Tabelle dort braucht, wo man eine WHERE-Bedingung im Statement verwende.
Das heißt, das folgende SQL-Statement:
# SELECT * FROM Syslog.SystemEvents WHERE FromHost = 'fritz01' AND LEFT(ReceivedAt,10) = '2011-10-05' AND Facility = 1
benötigt einen Index auf den spalten „FromHost“, „ReceivedAt“ und „Facility“.
Um die Tabelle entsprechend zu ändern, muss folgendes SQL-Statement abgesetzt werden:
# ALTER TABLE Syslog.SystemEvents ADD INDEX SearchIdx(Facility, FromHost, ReceivedAt);
damit wird ein einzener Index über die drei betreffenden spalten gelegt. Dann sollte man bei einer Abfrage immer alle drei Spalten als WHERE-Bedingung angeben!
Alternativ kann man auch jeweils einen einzelnen index pro spalte verwenden:
# ALTER TABLE Syslog.SystemEvents ADD INDEX facilityIdx(Facility), ADD INDEX fromHostIdx(FromHost), ADD INDEX receivedAt(ReceivedAt);
Das ist besser, wenn man nicht immer alle drei Spalten als WHERE-Bedingung angeben möchte.
Ein Index kann aber auch Nachteile haben: INSERT-Statements werden, mit steigender anzahl von Indizies, langsamer.
Man sollte es also nicht übertreiben und nur dort einen Index setzen, wo es wirklich nötig ist.
MySQL Tuning
/etc/mysql/conf.d/tuning.cnf
neu
# # tuning # [mysqld] # # key_buffer=128M # table_cache=512 # sort_buffer=15M # key_buffer = 256M # key buffer bei Bedarf ehoehen table_cache = 512 #sort_buffer = 32M #sort_buffer_size = 5M #read_buffer_size = 128K # key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = K bytes of memory # #innodb_buffer_pool_size = 3092M #innodb_log_file_size = 512M #innodb_flush_log_at_trx_commit = 2 # #skip-networking #max_allowed_packet= 64M #thread_stack= 128K #thread_cache_size = 64K #thread_concurrency= 4 #concurrent_insert = 2 #low_priority_updates= 1 max_connections = 900 connect_timeout = 10 #read_rnd_buffer_size= 4M #read_buffer_size= 4M tmp_table_size= 256M max_heap_table_size = 256M query_cache_limit = 256M query_cache_size= 256M query_cache_type= 1
alt
# # tuning # [mysqld] # # Empfehlung für 2GB RAM - systeme # key_buffer=128M # table_cache=512 # sort_buffer=15M # key_buffer = 1024M table_cache = 7000 sort_buffer = 128M # innodb_buffer_pool_size = 16000MB innodb_log_file_size = 256MB innodb_flush_log_at_trx_commit = 2 # #skip-networking max_allowed_packet= 64M thread_stack= 128K thread_cache_size = 64K thread_concurrency= 4 concurrent_insert = 2 low_priority_updates= 1 max_connections = 2500 connect_timeout = 10 read_rnd_buffer_size= 4M read_buffer_size= 4M tmp_table_size= 512M max_heap_table_size = 512M query_cache_limit = 512M query_cache_size= 512M query_cache_type= 1
MySQL mit Replikation
MySQL Bin-Log's in separate Partition schreiben
In diesem Beispiel lege ich das neue Bin-Log-Verzeichnis in ein LVM.
LVM anlegen:
# fdisk /dev/sda # pvcreate /dev/sda2 # vgcreate system /dev/sda2
LV anlegen und formatieren:
# lvcreate -L 100G -n mysqlbinlog system # mkfs -t ext4 -L mysqlbinlog /dev/system/mysqlbinlog
LV mounten:
# mkdir /mysqlbinlog # echo "LABEL=mysqlbinlog /mysqlbinlog ext4 defaults 0 0" >> /etc/fstab # mount /mysqlbinlog
Apparmor anpassen:
# vi /etc/apparmor.d/usr.sbin.mysqld ... /mysqlbinlog/** rw, }
# /etc/init.d/apparmor restart
MySQL-Konfiguration anpassen, hier muss der Variable log_bin ein absoluter Pfad übergeben werden:
# vi /etc/mysql/conf.d/default.cnf [mysqld] pid_file = /mysqlbinlog/mysql.pid log_bin = /mysqlbinlog/mysql-bin relay_log = /mysqlbinlog/mysql-relay-bin expire_logs_days = 7 ...
MySQL DB in eine separate Partition schreiben
# mkdir /mysqldb # echo "LABEL=mysqldb /mysqldb ext4 defaults 0 0" >> /etc/fstab # mount /mysqldb # mkdir /mysqldb/datadir # chown -R mysql:mysql /mysqldb
MySQL-Konfiguration anpassen, hier muss der Variable log_bin ein absoluter Pfad übergeben werden:
# vi /etc/mysql/conf.d/default.cnf [mysqld] datadir = /mysqldb/datadir ...
Apparmor anpassen:
# vi /etc/apparmor.d/usr.sbin.mysqld ... /mysqldb/** rw, /mysqldb/datadir/** rw, }
# /etc/init.d/apparmor restart
MySQL Bin-Log's
MySQL Bin-Log's ansehen
# du -shc /var/lib/mysql/mysql-*bin.* # echo "SHOW BINARY LOGS;" | mysql -t -u root -pgeheim # echo "SHOW MASTER STATUS;" | mysql -t -u root -pgeheim # mysqlbinlog /var/lib/mysql/mysql-bin.000004 # echo "SHOW BINLOG EVENTS IN 'mysql-bin.000004';" | mysql -t -uroot -pgeheim
überflüssige MySQL Bin-Log's löschen
#!/bin/bash
DBPASSWD="geheim"
DBMASTER="${1}"
if [ -z "${DBMASTER}" ] ; then
echo "${0} [DB-Master-Host]"
exit 1
fi
DBSLAVES="$(echo "SHOW SLAVE HOSTS \G;" | mysql -uroot -p${DBPASSWD} -h${DBMASTER} | awk '/^[ ]*Host:/ {print $NF}')"
BINLOGNR="$(echo "SHOW MASTER STATUS \G;" | mysql -uroot -p${DBPASSWD} -h${DBMASTER} | awk '/^[ ]*File:/ {gsub("[.]"," ");print $NF}')"
for i in ${DBSLAVES}
do
BINLOGNR="${BINLOGNR}
$(echo "SHOW SLAVE STATUS \G;" | mysql -uroot -p${DBPASSWD} -h${i} | awk '/^[ ]*Master_Log_File:/ {gsub("[.]"," ");print $NF}')"
done
MAXBINLOG="$(echo "${BINLOGNR}" | awk '/^[0-9]/ {print $1}' | sort -n | head -n1)"
BINLOGDEL="PURGE BINARY LOGS TO 'mysql-bin.$(echo "${MAXBINLOG}" | sed 's/.*/000000&/' | rev | cut -c 1-6 | rev)'"
### hier wird tatsaechlich geloescht!!!
echo "${BINLOGDEL};" | mysql -uroot -p${DBPASSWD} -h${DBMASTER}
MySQL-Proxy
MySQL-Cluser
MySQL Cluster ist eine Technologie für das Clustering von speicherresidenten Datenbanken in einem Share-Nothing-System. Mit der Share-Nothing-Architektur kann das System auf sehr preiswerter Hardware laufen und es gibt keine besonderen Anforderungen an Hardware oder Software. Überdies ist kein Single Point of Failure vorhanden, da jede Komponente ihren eigenen Arbeitsspeicher und ihre Festplatte hat.
MySQL Cluster integriert den normalen MySQL Server in eine speicherresidente geclusterte Speicher-Engine namens NDB. In unserer Dokumentation bezeichnet der Begriff NDB den Teil des Setups, der für die jeweilige Speicher-Engine spezifisch ist, während mit „MySQL Cluster“ die Kombination aus MySQL und der Speicher-Engine NDB gemeint ist.
Ein MySQL Cluster besteht aus mehreren Computern, auf denen jeweils eine Anzahl Prozesse laufen, darunter MySQL Server, Datenknoten für NDB Cluster, Management-Server und (möglicherweise) besondere Programme für den Datenzugriff.
