Niniejszy wpis powstał przy rozwiązywaniu konkretnego problemu. Jest tu opis polecenia LOAD DATA INFILE (import danych z pliku) oraz sposób wykonania konwersji daty podczas importu danych, co może komuś się przydać (niekoniecznie przy walce z takim samym problemem).
(więcej…)
Wpisy otagowane ‘MySQL’
MySQL: konwersja daty i import plików – studium przypadku
środa, 13 Styczeń 2010Trochę Lua w mysql-proxy
poniedziałek, 2 Listopad 2009
Replikacja baz MySQL działa jako tako. Jeśli to typ MASTER – SLAVE, to na ogół ten drugi serwer się nudzi. Skoro się nudzi, można wykorzystać go do zapytań, które nie zmieniają niczego (SELECT). Jeśli oddamy go w takim stanie programiście, to pewne jest, że prędzej czy później będzie to jego serwer testowy i dziać się będą na nim różne rzeczy.
(więcej…)
nagios: check_mysql-replication.pl
czwartek, 22 Październik 2009
Do monitorowania replikacji MySQL pod Nagios postanowiłem użyć wtyczki mysql_check-replication.pl, ktrej autorem jest Erwan Labynocle Ben Souiden. Instrukcja mówi o nadaniu prawa REPLICATION CLIENT użytkownikowi, który łączył się będzie z bazą. Sam skrypt napisany jest w Perl, gdzie w DSN podana jest baza, do której użytkownik będzie się podłączał. Wspomniane prawa dają tylko możliwość wywołania SHOW MASTER STATUS oraz SHOW SLAVE STATUS i nie dają praw do żadnej bazy. Tym samym skrypt się nie wykona.
Proponuję (wersja 0.1) przerobić DSN (linia 298) i wyrzucić nazwę bazy zupełnie (pozostawić `:’ – dwukropek) lub zmienić nazwę na information_schema, bo do tego prawo ma każdy użytkownik (dla wersji > 5.0).
--- /usr/lib64/nagios/plugins/check_mysql-replication.pl 2009-10-22 13:17:26.000000000 +0200
+++ check_mysql-replication.pl 2009-10-22 13:17:33.000000000 +0200
@@ -295,7 +295,7 @@ EOT
# ------------------------------
sub request_executor() {
my ($host,$port,$user,$pwd,$request) = @_;
- my $dsn = "DBI:mysql:mysql;host=$host:$port";
+ my $dsn = "DBI:mysql:;host=$host:$port";
my $dbh = DBI->connect($dsn, $user, $pwd) or die "connexion failed $DBI::errstr\n";
my $sth = $dbh->prepare($request);
$sth->execute();
Autor obiecał, że wprowadzi stosowną poprawkę – tymczasem można łatać.
MySQL slow_queries
wtorek, 10 Luty 2009Już kiedyś płakałem z tego powodu – Analiza log-slow-queries (z życia wzięte…), a tu stary klient narzeka na wydajność swojego serwera. I co my tu mamy?
- tabela 1 – 117′608 wierszy,
- tabela 2 – 730 wierszy,
- tabela 3 – 709 wierszy.
Niby niewiele, ale da się z tego zrobić taki wynik:
# Query_time: 11'005 Lock_time: 0 Rows_sent: 1 Rows_examined: 88'891'086
Dla porównania 10 miesięcy temu to samo zapytanie:
# Query_time: 53 Lock_time: 0 Rows_sent: 615 Rows_examined: 8'674'949
Nie mam pytań. Zdecydowanie potrzeba nowego procesora, więcej pamięci i szybszych dysków. Programisty przecież nie przekonamy…
SQL Relay
czwartek, 4 Wrzesień 2008Zajmował się ktoś SQL Relay? Wygląda obiecująco, i te wsparcie dostawców – Oracle, MySQL, mSQL, PostgreSQL, Sybase, MS SQL Server (oba przez FreeTDS), IBM DB2, Interbase, SQLite, ODBC, MS Access (MDB Tools)…
Postanowiłem przyjrzeć się temu w połączeniu z PostgreSQL i MySQL z C/C++ i Perl API. Wersja 0.39.4.
Jest w portach dla FreeBSD, nie ma w paczkach dla Gentoo. Potrzebuje dodatkowej biblioteki Rudiments (devel/rudiments we FreeBSD, dev-cpp/rudiments w Gentoo) i jeśli chcemy jakiegoś API, to odpowiednio: Perl, PHP, Python, Ruby, TCL, Java lub Zope. GTK+ i glib da nam GUI.
Konfiguracja nietrudna – w pliku XML. W tagu instance definiujemy proces nasłuchu. Tag user to definicje użytkowników wraz z hasłami. Na koniec tag connection, który wskazuje konfigurację do konkretnej bazy.
W bardziej złożonej konfiguracji mamy jeszcze router i filter. Pierwszy tag odpowiada za kierowanie zapytań do odpowiedniej bazy, drugi – za filtrowanie zapytań (zdefiniowane nie będą w ogóle przesyłane do serwera, a klient dostanie błąd). W obu przypadkach jest możliwość dopasowania zapytań za pomocą wyrażeń regularnych.
Jeżeli uruchamiamy SQL Relay za pomocą sqlr-start, musimy pamiętać, że wszystkie programy, które wywołuje, np. sqlr-listener muszą być w ścieżce ($PATH). Baza również musi działać. Jeśli nie, pozostaje walka z czyszczeniem shared memory segment i semaphore (ipcs, ipcrm), bo SQL Relay nie sprząta po sobie.
Na tym skończyłem walkę. To znaczy próbowałem uruchomić SQL Relay zarówno z MySQL jak i PostgreSQL, ale niestety zakończyło się to niepowodzeniem (SQL Relay nasłuchiwał, przy połączeniu cisza). Mam nadzieję, że kiedyś wrócę do tego tematu.
Przepraszam, jeśli ktoś był pełen nadzieji, że czegoś się dowie z tego artykułu. Zmieniłem pracę i już nie robię tego co robiłem, więc wiele rzeczy pozostało niedokończonych…
Bezpieczne połączenia z MySQL (C API)
niedziela, 10 Sierpień 2008Ostatnio coraz więcej kodu w C piszę z użyciem MySQL API, a że dane przy jednym z projektów są poufne i serwer nie jest lokalny, przyjrzałem się połączeniom SSL. W zasadzie po stronie kodu to nic wielkiego – wystarczy wywołać mysql_ssl_set() przed mysql_real_connect() (mysql_connect() jest deprecated). Oczywiście pod warunkiem, że klient jest skompilowany ze wsparciem OpenSSL (lub yaSSL, ale nigdy nie testowałem).
Uruchomienie samego serwera (również przy założeniu, że skompilowany ze wsparciem SSL) sprowadza się do podania dodatkowych parametrów w linii komend. Przykładowo może to wyglądać tak:
mysqld --defaults-file=/etc/mysql/my.cnf --ssl --ssl-ca=</path/to/CA.crt> --ssl-cert=</path/to/file.crt> --ssl-key=</path/to/file.key>
Ewentualnie odpowiednie parametry podać w pliku konfiguracyjnym (my.cnf):
ssl-ca = </path/to/CA.crt>
ssl-cert =</path/to/file.crt>
ssl-key =</path/to/file.key>
Pamiętać należy, że w przypadku, gdy certyfikat jest zabezpieczony hasłem, MySQL może być jedynie uruchomiony ręcznie. Korzystając ze skryptów startowych (na pewno w Gentoo Linux) czy nawet wrzucając proces w tło (&) serwer nie skorzysta z certyfikatu i nie nawiążemy szyfrowanej transmisji. Próba takiego uruchomienia zakończy się podobnym wpisem w logach:
[Warning] Failed to setup SSL
Testowe połączenie do serwera z konsoli mysql możemy wykonać podając jedynie ścieżkę do certyfikatu CA (format PEM), np.
mysql -h <host> -u <user> -p --ssl-ca=</path/to/CA.crt> <database>
Wywołując STATUS możemy sprawdzić czy bieżące połączenie ma wsparcie SSL:
SSL: Cipher in use is DHE-RSA-AES256-SHA
czy też nie:
SSL: Not in use
Wymuszanie szyfrowanych połączeń możliwe tylko poprzez REQUIRE SSL przy nadawaniu praw użytkownikowi (GRANT). W takim przypadku połączenie bez SSL zakończy się komunikatem podobnym jak w przypadku np. podania błędnego hasła:
ERROR 1045 (28000): Access denied for user 'exg'@'192.168.1.101' (using p assword: YES)
Więcej o używaniu SSL w MySQL można poczytać tutaj:
Wracając do samej funkcji mysql_ssl_set(), to jako parametry przyjmuje: uchwyt połączenia zwrócony przez mysql_init(), ścieżkę do pliku klucza, certyfikatu i certyfikatu CA oraz ścieżkę do katalogu z certyfikatami CA w formacie PEM, a także listę wspieranych algorytmów.
Nieużywane parametry powinny przyjmować wartość NULL.
Funkcja zawsze zwraca 0, o błędach SSL informuje mysql_real_connect().
ERROR 2026 (HY000): SSL connection error
Na koniec łyżka dziegciu – libmysql (i klient mysql) dla Windows (ten z binarnej paczki) nie jest skompilowany z obsługą SSL. Aby korzystać z bezpiecznych połączeń należy pobrać źródła i samodzielnie dokonać kompilacji. Mi się nie udało… Może przez zbyt stare Visual C++.
Analiza log-slow-queries (z życia wzięte…)
sobota, 26 Lipiec 2008Jedna z tabel klienta wygląda tak:
CREATE TABLE `mix_stats` (
`id` int(11) NOT NULL auto_increment,
`id_produktu` decimal(20,0) NOT NULL default ‘0′,
`data` date default NULL,
`count` decimal(1,0) NOT NULL default ‘0′,
`session` varchar(255) NOT NULL default ”,
`ip` varchar(20) NOT NULL default ”,
`referer` varchar(255) NOT NULL default ”,
PRIMARY KEY (`id`)
) TYPE=MyISAM
MySQL 3.23, w logach następujące wpisy:
# Time: 080726 10:40:51
# User@Host: xxx[xxx] @ localhost []
# Query_time: 48 Lock_time: 0 Rows_sent: 1 Rows_examined: 639552
SELECT count(id) FROM mix_stats WHERE id_produktu = ‘84092324382e93e’ AND session = ”;
Jeżeli ktoś jest na tyle cierpliwy, by czekać 48 sekund na wynik, to musi mu bardzo zależeć…
Analizę bazy wykonywałem na MySQL 5.0.60, więc mam większe pole do popisu, np.:
mysql> SELECT count(id) FROM mix_stats WHERE id_produktu = '84092324382e93e' AND session = ''; +-----------+ | count(id) | +-----------+ | 0 | +-----------+ 1 row in set, 65535 warnings (0.99 sec) mysql> SHOW WARNINGS LIMIT 3; +---------+------+-----------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: '84092324382e93e' | | Warning | 1292 | Truncated incorrect DOUBLE value: '84092324382e93e' | | Warning | 1292 | Truncated incorrect DOUBLE value: '84092324382e93e' | +---------+------+-----------------------------------------------------+ 3 rows in set (0.00 sec)
Na początek polecam: DECIMAL. Choć pewnie skończy się przejściem do innego usługodawcy, gdzie mocniejsza maszyna bazodanowa przemieli to szybciej (ostrzeżenia i błędy i tak użytkowników nie interesują)…