Tag Archives: mysql

logoMysql

Kurztipp: mysqldump + Views

Problem: “DEFINER”

Jeder MySQL-Dump welcher mit dem Befehl “mysqldump” erstellt wird beinhalte die Einstellung zum “DEFINER”.

z.B.: DEFINER=`root`@`localhost` SQL SECURITY DEFINER

Dies bedeutet, dass man diesen SQL-Dump nicht einfach z.B. auf dem Ziel-Server nutzen kann, da die Applikation wahrscheinlich / hoffentlich keinen Root-Zugriff auf die Datenbank hat.

Lösung: via “grep”

Als schnelle Lösung für diese Problem entferne ich einfach den entsprechenden DEFINER-Eintrag.

mysqldump -u your_user --skip-comments --skip-opt --complete-insert --databases your_db | grep -v 'SQL SECURITY DEFINER' > ~/your_dump.sql

http://gist.suckup.de/tpl_default.php?pid=1#sec_fb936cf7878c32a5f0b0

Tipp im Tipp

Falls man mit Windows arbeiten muss, empfehle ich die Git-Bash (MinGW + GIT) zu installieren und das “mysql/bin” Verzeichnis im Windows PATH (System -> Erweiterte Systemeinstellungen -> Umgebungsvariablen) einzutragen. So kann man den oben genannten Befehl (und vieles mehr) auch unter Windows nutzen!

 

Links & Quellen:

– http://dev.mysql.com/doc/refman/5.1/de/create-view.html
– http://www.sitepoint.com/mysql-views/
– http://www.mingw.org/
– http://msysgit.github.io/

perror – Explain Error Codes für MySQL in der Shell

for i in `seq 1 180`; do perror ${i}; done | less

Info: http://dev.mysql.com/doc/refman/5.0/en/perror.html

 

Ausgabe:

OS error code   1:  Operation not permitted
OS error code   2:  No such file or directory
OS error code   3:  No such process
OS error code   4:  Interrupted system call
OS error code   5:  Input/output error
OS error code   6:  No such device or address
OS error code   7:  Argument list too long
OS error code   8:  Exec format error
OS error code   9:  Bad file descriptor
OS error code  10:  No child processes
OS error code  11:  Resource temporarily unavailable
OS error code  12:  Cannot allocate memory
OS error code  13:  Permission denied
OS error code  14:  Bad address
OS error code  15:  Block device required
OS error code  16:  Device or resource busy
OS error code  17:  File exists
OS error code  18:  Invalid cross-device link
OS error code  19:  No such device
OS error code  20:  Not a directory
OS error code  21:  Is a directory
OS error code  22:  Invalid argument
OS error code  23:  Too many open files in system
OS error code  24:  Too many open files
OS error code  25:  Inappropriate ioctl for device
OS error code  26:  Text file busy
OS error code  27:  File too large
OS error code  28:  No space left on device
OS error code  29:  Illegal seek
OS error code  30:  Read-only file system
OS error code  31:  Too many links
OS error code  32:  Broken pipe
OS error code  33:  Numerical argument out of domain
OS error code  34:  Numerical result out of range
OS error code  35:  Resource deadlock avoided
OS error code  36:  File name too long
OS error code  37:  No locks available
OS error code  38:  Function not implemented
OS error code  39:  Directory not empty
OS error code  40:  Too many levels of symbolic links
OS error code  42:  No message of desired type
OS error code  43:  Identifier removed
OS error code  44:  Channel number out of range
OS error code  45:  Level 2 not synchronized
OS error code  46:  Level 3 halted
OS error code  47:  Level 3 reset
OS error code  48:  Link number out of range
OS error code  49:  Protocol driver not attached
OS error code  50:  No CSI structure available
OS error code  51:  Level 2 halted
OS error code  52:  Invalid exchange
OS error code  53:  Invalid request descriptor
OS error code  54:  Exchange full
OS error code  55:  No anode
OS error code  56:  Invalid request code
OS error code  57:  Invalid slot
OS error code  59:  Bad font file format
OS error code  60:  Device not a stream
OS error code  61:  No data available
OS error code  62:  Timer expired
OS error code  63:  Out of streams resources
OS error code  64:  Machine is not on the network
OS error code  65:  Package not installed
OS error code  66:  Object is remote
OS error code  67:  Link has been severed
OS error code  68:  Advertise error
OS error code  69:  Srmount error
OS error code  70:  Communication error on send
OS error code  71:  Protocol error
OS error code  72:  Multihop attempted
OS error code  73:  RFS specific error
OS error code  74:  Bad message
OS error code  75:  Value too large for defined data type
OS error code  76:  Name not unique on network
OS error code  77:  File descriptor in bad state
OS error code  78:  Remote address changed
OS error code  79:  Can not access a needed shared library
OS error code  80:  Accessing a corrupted shared library
OS error code  81:  .lib section in a.out corrupted
OS error code  82:  Attempting to link in too many shared libraries
OS error code  83:  Cannot exec a shared library directly
OS error code  84:  Invalid or incomplete multibyte or wide character
OS error code  85:  Interrupted system call should be restarted
OS error code  86:  Streams pipe error
OS error code  87:  Too many users
OS error code  88:  Socket operation on non-socket
OS error code  89:  Destination address required
OS error code  90:  Message too long
OS error code  91:  Protocol wrong type for socket
OS error code  92:  Protocol not available
OS error code  93:  Protocol not supported
OS error code  94:  Socket type not supported
OS error code  95:  Operation not supported
OS error code  96:  Protocol family not supported
OS error code  97:  Address family not supported by protocol
OS error code  98:  Address already in use
OS error code  99:  Cannot assign requested address
OS error code 100:  Network is down
OS error code 101:  Network is unreachable
OS error code 102:  Network dropped connection on reset
OS error code 103:  Software caused connection abort
OS error code 104:  Connection reset by peer
OS error code 105:  No buffer space available
OS error code 106:  Transport endpoint is already connected
OS error code 107:  Transport endpoint is not connected
OS error code 108:  Cannot send after transport endpoint shutdown
OS error code 109:  Too many references: cannot splice
OS error code 110:  Connection timed out
OS error code 111:  Connection refused
OS error code 112:  Host is down
OS error code 113:  No route to host
OS error code 114:  Operation already in progress
OS error code 115:  Operation now in progress
OS error code 116:  Stale NFS file handle
OS error code 117:  Structure needs cleaning
OS error code 118:  Not a XENIX named type file
OS error code 119:  No XENIX semaphores available
OS error code 120:  Is a named type file
MySQL error code 120: Didn't find key on read or update
OS error code 121:  Remote I/O error
MySQL error code 121: Duplicate key on write or update
OS error code 122:  Disk quota exceeded
OS error code 123:  No medium found
MySQL error code 123: Someone has changed the row since it was read (while the table was locked to prevent it)
OS error code 124:  Wrong medium type
MySQL error code 124: Wrong index given to function
OS error code 125:  Operation canceled
MySQL error code 125: Undefined handler error 125
OS error code 126:  Required key not available
MySQL error code 126: Index file is crashed
OS error code 127:  Key has expired
MySQL error code 127: Record file is crashed
OS error code 128:  Key has been revoked
MySQL error code 128: Out of memory in engine
OS error code 129:  Key was rejected by service
MySQL error code 129: Undefined handler error 129
OS error code 130:  Owner died
MySQL error code 130: Incorrect file format
OS error code 131:  State not recoverable
MySQL error code 131: Command not supported by database
MySQL error code 132: Old database file
MySQL error code 133: No record read before update
MySQL error code 134: Record was already deleted (or record file crashed)
MySQL error code 135: No more room in record file
MySQL error code 136: No more room in index file
MySQL error code 137: No more records (read after end of file)
MySQL error code 138: Unsupported extension used for table
MySQL error code 139: Too big row
MySQL error code 140: Wrong create options
MySQL error code 141: Duplicate unique key or constraint on write or update
MySQL error code 142: Unknown character set used in table
MySQL error code 143: Conflicting table definitions in sub-tables of MERGE table
MySQL error code 144: Table is crashed and last repair failed
MySQL error code 145: Table was marked as crashed and should be repaired
MySQL error code 146: Lock timed out; Retry transaction
MySQL error code 147: Lock table is full;  Restart program with a larger locktable
MySQL error code 148: Updates are not allowed under a read only transactions
MySQL error code 149: Lock deadlock; Retry transaction
MySQL error code 150: Foreign key constraint is incorrectly formed
MySQL error code 151: Cannot add a child row
MySQL error code 152: Cannot delete a parent row
MySQL error code 153: No savepoint with that name
MySQL error code 154: Non unique key block size
MySQL error code 155: The table does not exist in engine
MySQL error code 156: The table already existed in storage engine
MySQL error code 157: Could not connect to storage engine
MySQL error code 158: Unexpected null pointer found when using spatial index
MySQL error code 159: The table changed in storage engine
MySQL error code 160: There's no partition in table for the given value
MySQL error code 161: Row-based binlogging of row failed
MySQL error code 162: Index needed in foreign key constraint
MySQL error code 163: Upholding foreign key constraints would lead to a duplicate key error in some other table
MySQL error code 164: Table needs to be upgraded before it can be used
MySQL error code 165: Table is read only
MySQL error code 166: Failed to get next auto increment value
MySQL error code 167: Failed to set row auto increment value
MySQL error code 168: Unknown (generic) error from engine
MySQL error code 169: Record is the same
MySQL error code 170: It is not possible to log this statement
MySQL error code 171: The event was corrupt, leading to illegal data being read
MySQL error code 172: The table is of a new format not supported by this version
MySQL error code 173: The event could not be processed no other hanlder error happened
MySQL error code 174: Got a fatal error during initialzaction of handler
MySQL error code 175: File to short; Expected more data in file
MySQL error code 176: Read page with wrong checksum
MySQL error code 177: Too many active concurrent transactions
MySQL error code 178: Index column length exceeds limit
MySQL error code 179: Index corrupted
MySQL error code 180: Undo record too big

MySQL-Server optimieren

Zurück zur “Webseiten beschleunigen” – Übersicht

7.) MySQL optimieren

cd  /root
wget  http://mysqltuner.com/mysqltuner.pl
wget  http://www.day32.com/MySQL/tuning-primer.sh
chmod +x mysqltuner.pl
chmod +x tuning-primer.sh
./mysqltuner.pl
./tuning-primer.sh

Link:
rackerhacker.com/mysqltuner/ – mysqltuner
www.day32.com/MySQL/ – tuning-primer


Beispiel:

Wenn wir unserem MySQL-Server z.B. 64 MB zur Verfügung stellen möchten ->

key_buffer = 12M + (read_buffer = 4M + sort_buffer = 4 M) x max_connections = 64 MB

12 + (4 + 4) x max_connections = 64


Formel nach max_connections umstellen

max_connections = (RAM – key_buffer) : (read_buffer + sort_buffer)

max connections = (64 – 12) : (4 + 4)

max_connections = 52 : 8

max_connections = 6,5


benötigen wir mehr als 6,5 max_connections, müssen wir mehr RAM zur Verfügung stellen oder read_buffer und sort_buffer kleiner einstellen…


Regel

Je weniger RAM zur Verfügung steht und je mehr max_connections benötigt werden, desto kleiner müssen read_buffer, sort_buffer u. key_buffer sein.


my.cnf – Konfigurationsdatei überprüfen ob der key_buffer so groß ist, dass alle Indizies in den Buffer passen und table_cache in etwa der Anzahl der Tabellen entspricht.


Zudem sollte man darauf achten, dass MySQL-Cache eingeschaltet ist.

-> query_cache_type=1


Als Anwendungsbeispiel werde ich an dieser Stelle die MySQL-Konfiguration meines Servers überprüfen ->

/root/mysqltuner.pl

Ich werde hier jedoch nur die Fehler, welches das Skript in meiner Konfig gefunden hat auflisten…

  • [!!] Total fragmented tables: 2

-> das nachfolgende Kommando schafft hier Abhilfe (könnte man so, oder so ähnlich auch als cronjob (crontab) laufen lassen

crontab -e



30 * * * * /usr/bin/mysqlcheck -u USER -pPASSWORT --auto-repair --check --optimize --all-databases > /dev/null 2>&1

In diesem Beispiel werden alle Datenbanken, jede 30 Minuten defragmentiert bzw. optimiert.



  • [!!] Key buffer size / total MyISAM indexes: 10.0M/13.7M

-> key_buffer = 15M erhöhen


7.1) MariaDB (MySQL-Fork)

In den letzen Wochen habe ich auch mit einem Mysql-Fork herumgespielt (MariaDB), dieser soll einige Vorteile in der Geschwindigkeit bringen. Man kann die Software über die Debian Pakete installieren nachdem man folgendes in seine “sources.list” aufgenommen hat.

deb http://mirror.ourdelta.org/deb lenny mariadb-ourdelta
deb-src http://mirror.ourdelta.org/deb lenny mariadb-ourdelt

… danach noch ein …

apt-get update

… und schon kannst du die Software gegen MySQL austauchen, dies funktioniert z.B. mit “aptitude” (mariadb-server) auch sehr gut, jedoch hatte ich das Problem, dass mein ISPConfig (eine Server Verwaltungsoberfläche) keine neuen Benutzer anlegen konnte, da sich die Syntax des Befehls ein wenig unterscheidet.


greenSQL

GreenSQL – MySQL Firewall

Was ist eine MySQL Firewall und wie funktioniert dies?

GreenSQL ist eine Open-Source-Datenbank-Firewall, um Datenbanken von SQL-Injection-Attacken zu schützen. Diese arbeitet als Proxy für SQL-Kommandos und hat eine eingebaute MySQL Unterstützung. Die Logik beruht auf der Auswertung von SQL-Befehlen, mit Hilfe eines Scoring-Risiko-Matrix sowie Sperrung bekannt db Verwaltungs-Befehle.

GreenSQL arbeitet als Reverse Proxy für MySQL-Verbindungen. Dies bedeutet, dass du dich anstatt direkt mit den MySQL-Server, zuvor mit GreenSQL verbindest. GreenSQL analysiert die SQL-Abfragen und wenn sie sicher sind, werden diese an den MySQL-Server durch-gereicht.

greenSQL
greenSQL

HowTo: Wie installiere ich die MySQL Firewall?

1. Download der Software

Das passende Paket von dieser Webseite herunterladen:

www.greensql.net/download

2. Installiere das entsprechende Paket

Debian/Ubuntu:

sudo dpkg -i PACKAGE_NAME.deb

Redhat/Fedora/CentOS/SuSe:

rpm -ihv PACKAGE_NAME.rpm

FreeBSD:

pkg_add PACKAGE_NAME.tbz

3. Datenbank anlegen & Konfiguration anpassen

Um die Konfiguration anzupassen und die Datenbank anzulegen, führst du nun folgendes Skript aus…

/usr/sbin/greensql-create-db.sh

und nun können wie die Firewall bereits starten

/etc/init.d/greensql* restart

4. Status der Firewall testen

um die Verbindung durch den MySQL-Proxy zu testen, führen wir folgendes Kommando aus…

mysql -h 127.0.0.1 -P 3305 -u root -p

5. Webseite mit einbinden

nun wird es Zeit, unsere Webseite in das Konstrukt mit einzubinden, dafür muss die Datei, wo die Verbindungseinstellungen zu MySQL eingestellt werden editiert werden.

Folgendes sollte in eine PHP-Datei zu finden sein und muss nun nur noch angepasst werden…

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');

… wird zu …

$link = mysql_connect('127.0.0.1:3305', 'mysql_user', 'mysql_password');

Wichtiger Hinweis:
Keine Verbindung zu “localhost”, verwende stattdessen immer “127.0.0.1”.

Capture - libtcmalloc

MySQL-Server beschleunigen

Nutze Google’s perftools um die Geschwindigkeit deines MySQL-Server zu erhöhen.

Das Google Perftools, insbesondere tcmalloc (Thread Caching Malloc), kann helfen z.B. den MySQL-Server zu beschleunigen…

TCMalloc ist schneller als die glibc 2.3 malloc (Wiki). Die Geschwindigkeitsvorteile durch die verbesserte Speicherzuordnung, kannst du wie folgt in MySQL integrieren.

Vorausgesetzt du nutzt Debian 5 (Lenny) oder neuer… fügen wir als erstes neue Repositories in der sources.list ein.

echo 'deb http://dotdeb.netmirror.org/ stable all' >> /etc/apt/sources.list
echo 'deb-src http://dotdeb.netmirror.org/ stable all' >> /etc/apt/sources.list

und aktualisieren ggf. den MySQL-Server auf Version 5.1

apt-get update
aptitude install mysql-server-5.1
aptitude install libtcmalloc-minimal0

Seit den mainstream MySQL-Paketen sind diese nicht mit den tcmalloc compiliert, daher müssen wir einen dynamischen OS’ Linker einbauen, indem wir folgende Zeile in dem MySQL init-Skript einfügen:

vim /etc/init.d/mysql

export LD_PRELOAD=”/usr/lib/libtcmalloc_minimal.so.0″

Capture - libtcmalloc
Capture - libtcmalloc

Nach einen restart des MySQL-Servers durch das init-Skript, profitiert dieser nun von der schnellern Speicherzuteilung.

/etc/init.d/mysql restart

Link:
www.dotdeb.org

MySQL root Passwort zurücksetzen

Wer sein root MySQL-Server Passwort vergessen hat, kann es mit folgendem HowTo wieder zurücksetzen…

füge folgenden Inhalt in eine neue Datei ein… ->

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MeinNeuesPasswort');"

nun müssen wir den MySQL-Server stoppen ->

/etc/init.d/mysql stop

und den MySQL-Server mit dem neuen Passwort starten ->

mysqld_safe --init-file=/PFADZUMFILE/FILE

nun nur noch ein restart des MySQL-Servers und Fertig…

/etc/init.d/mysql restart

alternativ kann man dies auch umgekehrt machen…

wir müssen den MySQL-Server wieder stoppen ->

/etc/init.d/mysql stop

dann den MySQL Daemon im safe-Mode starten ->

mysqld_safe --skip-grant-tables

per root in MySQL einloggen… ->

mysql --user=root mysql

und folgende Kommandos ausführen ->

UPDATE USER SET PASSWORD=PASSWORD('MeinNeuesPasswort') WHERE USER='root';
flush privilesges;
exit;

nun nur noch mysql-Server neu-starten und fertig…

/etc/init.d/mysql restart

jetzt nur nicht wieder das Passwort vergessen ;)