Deframmenta per ottimizzare le tabelle di MySQL

In MySQL, quando si eliminano record da una tabella, lo spazio è riassegnato automaticamente. E ‘come uno spazio vuoto e formando gli allegati nuovi vantaggio.

Il problema è che se una tabella di eseguire molte operazioni di DELETE, lo spazio fisico del tavolo sarà sempre più frammentato e il rendimento è ridotto.

Nel MyISAM InnoDBOPTIMIZE TABLE comando disponibile ad effettuare l’ottimizzazione su qualsiasi tabella che, tra l’altro, esegue una deframmentazione automatica del tavolo.

Si consiglia vivamente di utilizzare questo comando regolarmente in particolare su tabelle che sono più le dichiarazioni di smaltimento dei record.

Per precauzione, di tenere presente che durante l’esecuzione, naturalmente, la tabella è bloccato. Si deve ricordare quando si sta per l’utilizzo con tabelle di grandi dimensioni e occupato.

La sintassi è la seguente:

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE mi_tabla1 [, mi_tabla2] ...;

Per fare un tavolo di ottimizzazione frammentati possono essere selezionati per avere spazio libero, probabilmente a causa di DELETE:
SELECT TABLE_SCHEMA,TABLE_NAME
FROM TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema","mysql") AND
Data_free > 0

Un semplice script per deframmentare in automatico : 
#!/bin/bash

# Get a list of all fragmented tables
FRAGMENTED_TABLES="$( mysql -e 'use information_schema; SELECT TABLE_SCHEMA,TABLE_NAME \
FROM TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema","mysql") AND \
Data_free > 0' | grep -v "^+" | sed "s,\t,.," )"

for fragment in $FRAGMENTED_TABLES; do
   database="$( echo $fragment | cut -d. -f1 )"
   table="$( echo $fragment | cut -d. -f2 )"
   [ $fragment != "TABLE_SCHEMA.TABLE_NAME" ] && mysql -e "USE $database;\
   OPTIMIZE TABLE $table;" > /dev/null 2>&1
done

Mysqldump per prendere solo lo schema di una tabella

Citazione

Mysqldump è uno strumento che viene utilizzato per creare copie di backup (o dump) dei database, incusi dati struttura e schemi. Ci sono una serie di switch (flags) da riga di comando che si possono lanciare per scaricare solo i dati o solo la struttura invece di tutto.

Dump della struttura del database per tutte le tabelle senza dati

Aggiungere il flag -d per indicare che nessun dato deve essere incluso nell’output.

Il seguente comando dump della struttura della tabella per tutte le tabelle nel database specificato MySQL:

mysqldump -d -u -p someuser miodatabase

Il flag-d dice di non inserire i dati nel dump. In alternativa si può usare –no-data che è più semplice da ricordare.
mysqldump --no-data -u  -p someuser miodatabase

Dump della struttura del database per una sola tabella senza dati

Esempio per una tabella

mysqldump-d-u-p someuser miodatabase prodotti

Pratiche di dumping la struttura del database per la tabella con i diversi dati

Questa è la stessa per un tavolo, ma solo specificare tabelle aggiuntive come molti dopo il nome del database come vorreste discarica. Questo comando eseguirà il dump della struttura per i “prodotti” tavoli “categorie” e “utenti”:

mysqldump-d-u-p someuser prodotti miodatabase categorie di utenti

Pratiche di dumping la struttura in un file

Tutti i comandi di esempio sopra scrivere il dump sullo standard output, nel senso che ti scorrere verso l’alto il terminale / finestra prompt dei comandi che non può essere molto utile. Per salvare in un file invece reindirizzare l’output. Per esempio:

mysqldump-d-u-p someuser miodatabase> mydatabase.sql

È possibile utilizzare il contenuto di questo file con il tool comando “mysql” linea per creare tali tabelle stesse in un altro database:
mysql-u-p someuser anotherdatabase 

Creazione instanza mysql parallela

Citazione

… ovvero come faccio ad avere due instanze indipendenti di mysql sullo stesso server ?

Attenzione :

Questo how-to è stato scritto e pensato per Ubuntu/Debian in particolare, alcuni path e directory potrebbero essere differenti nella vostra versione di Linux

1. Creo directory e predispongo i file di log

mkdir /var/lib/mysql2
chown −R mysql.mysql /var/lib/mysql2/
mkdir /var/log/mysql2
chown −R mysql.mysql /var/log/mysql2

2. Creo una nuova configurazione di mysql

cp -R /etc/mysql/ /etc/mysql2

A questo punto dobbiamo modificare il file di configurazione di mysql e cambiare la porta, socket e pid : 
cd /etc/mysql2/
sed −i 's/3306/3307/g' my.cnf
sed −i 's/mysqld.sock/mysqld2.sock/g' my.cnf
sed −i 's/mysqld.pid/mysqld2.pid/g' my.cnf
sed −i 's/var\/lib\/mysql/var\/lib\/mysql2/g' my.cnf
sed −i 's/var\/log\/mysql/var\/log\/mysql2/g' my.cnf

3. Inizializzazione e start

Abbiamo a questo punto dell’how-to due scelte da fare :

1. creiamo una instanza vuota con database di default come se fosse una nuova installazione 

2. Copiamo i database da un’altra instanza 

Nel caso :

1.  mysql_install_db −−user=mysql −−datadir=/var/lib/mysql2/
2. cp −R /var/lib/mysql/* /var/lib/mysql2/*

Possiamo a questo punto far partire l’instanza :
mysqld_safe --defaults-file=/etc/mysql2/my.cnf &

e connetterci in due modi :
mysql -S /var/run/mysqld/mysqld2.sock

o
mysql -h 127.0.0.1 -P 3307

Possiamo a questo punto creare uno script di init (es: mysql2) in /etc/init.d/

 Bash | 
 
 copy code |
?

01
#!/bin/sh
02
# Begin /etc/init.d/mysql
03
 
04
#source /etc/init.d/functions
05
 
06
case "$1" in
07
        start)
08
                echo -n "Starting mysql..."
09
                /usr/bin/mysqld_safe --defaults-file=/etc/mysql2/my.cnf >/dev/null 2>&1  &
10
                ret=$?
11
                ;;
12
 
13
        stop)
14
                echo -n "Stopping mysqld..."
15
#               kill `cat /var/run/mysqld/mysqld-rep.pid`
16
                mysqladmin -S /var/run/mysqld/mysqld2.sock shutdown > /dev/null 2>&1
17
                ret=$?
18
                ;;
19
 
20
        restart)
21
                $0 stop
22
                /usr/bin/sleep 1
23
                $0 start
24
                ;;
25
 
26
        status)
27
                statusproc /usr/bin/mysqld
28
                ;;
29
 
30
        *)
31
                echo "Usage: $0 {start|stop|restart|status}"
32
                exit 1
33
        ;;
34
 
35
esac
36
 
37
exit $?
38

se voglio farlo partire al boot :

update-rc.d mysql2 defaults

Protezione [base] contro DOS

mod_evasive è un altro modulo di Apache in grado di aumentare la sicurezza del sistema proteggendoci da attacchi DOS e D-DOS sulla porta 80.
Gli attacchi di tipo DOS e D-DOS (Denial of Services e Distributed Denial of Services) sono attacchi atti a rendere inaccessibili i sistemi a causa di un intenso traffico dati. Grazie a questo modulo, però, riusciamo a prevenire questo tipo di attacco quando viene rivolto ad Apache in quanto il modulo tiene traccia del numero di connessioni provenienti da un determinato IP e, in caso di superamento della soglia, interviene bloccandole.
Per installare il modulo su Debian/Ubuntu è sufficiente lanciare il comando:

apt-get install libapache2-mod-evasive

Quindi creiamo la directory per i log:
mkdir -p /var/log/apache2/evasive
chown -R www-data:root /var/log/apache2/evasive

Ora creiamo un file di configurazione per il modulo:
/etc/apache2/conf.d/modevasive.conf
<IfModule mod_evasive20.c>
DOSHashTableSize 3097
DOSPageCount 5
DOSSiteCount 100
DOSPageInterval 1
DOSSiteInterval 1
DOSBlockingPeriod 600
DOSLogDir "/var/log/apache2/evasive"
</IfModule>

e riavviamo Apache:
/etc/init.d/apache2 restart

Per collaudare il funzionalmento del modulo, c’è un semplice script perl incluso con la documentazione:
# perl /usr/share/doc/libapache2-mod-evasive/examples/test.pl 
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 200 OK
HTTP/1.1 403 Forbidden
HTTP/1.1 200 OK
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden
HTTP/1.1 403 Forbidden

List tutti i vhost di apache

Ecco uno script perl/bash dove tiro fuori formattati bene tutti i vhost di apache :

Modo più verboso :

 Bash | 
 
 copy code |
?

1
/usr/sbin/apache2ctl&nbsp;&minus;S&nbsp;2>&1&nbsp;|&nbsp;perl&nbsp;&minus;ne&nbsp;'[email protected]*port\s+([0&minus;9]+)\s+\w+\s+(\S+)\s+\((.+):.*@&nbsp;&&&nbsp;do&nbsp;{&nbsp;print&nbsp;"$2:$1\n\t$3\n";&nbsp;$root&nbsp;=&nbsp;qx{grep&nbsp;DocumentRoot&nbsp;$3};&nbsp;$root&nbsp;=~&nbsp;s/^\s+//;&nbsp;print&nbsp;"\t$root\n"&nbsp;};'

Modo meno verboso :

 Bash | 
 
 copy code |
?

1
#!/bin/bash
2
apache2ctl&nbsp;&minus;S&nbsp;2>&1&nbsp;|&nbsp;grep&nbsp;&minus;v&nbsp;Syntax&nbsp;|&nbsp;perl&nbsp;&minus;ne&nbsp;'[email protected]*port\s+([0&minus;9]+)\s+\w+\s+(\S+)\s+\((.+):.*@&nbsp;&&&nbsp;do&nbsp;{&nbsp;print&nbsp;"$2:$1\n";};'

Mysql Proxy

Vi è mai capitato di dover configurare un’applicazione in modo che in un’archiettura mysql master/slave scriva automaticamente sul master e legga dagli slave senza modificare l’applicativo ?

Mysql-proxy è quello che fa per te !

MySQL Proxy è un programma che si interpone fra un client e un server MySQL, per consentire di effettuare operazioni sul traffico di dati, in maniera trasparente per l’utente. MySQL Proxy usa il protocollo client/server esteso di MySQL, introdotto con la versione 4.1. Pertanto, questa applicazione è compatibile con i server dalla versione 4.1 e superiore.

MySQL Proxy è un’applicazione molto leggera (200 KB in compilazione dinamica, 1.5 MB in compilazione statica con le librerie), che è stata disegnata per interporsi fra un client e un server MySQL ed eseguire operazioni sui pacchetti inviati e ricevuti.
Il programma ha poche opzioni di configurazione. La caratteristica che lo rende interessante e funzionale è un interprete Lua integrato, grazie al quale è possibile analizzare ed eventualmente modificare i pacchetti passati fra client e server.
Il client non si accorge del Proxy, che agisce da man in the middle. Il client si connette con le solite credenziali. Il Proxy intercetta la richiesta e si collega al server. Da quel momento, ogni query inviata dal client e i relativi risultati restituiti dal server passano attraverso il Proxy.

Si noti che MySQL Proxy non è un’applicazione di malware, anche se la definizione resa sopra, con la sua possibilità di intercettazione, potrebbe farlo credere. L’uso del Proxy è trasparente per il client, in materia di protocollo, ovvero il client usa il server tramite il Proxy senza avvertire differenze (tranne quando usa funzioni esplicitamente create per il Proxy). Ma il Proxy usa una porta diversa dal server, e pertanto, anche se l’uso è trasparente per l’applicazione, l’amministratore è conscio della sua esistenza. L’uso del Proxy senza consenso di una delle due parti non è possibile. Il Proxy può essere usato dall’amministratore, per ampliare le funzionalità del server, o dall’utente, per lo stesso motivo. Nel primo caso, il Proxy sarà visibile a tutti gli utenti. Nel secondo caso solo l’utente che l’ha installato nella sua macchina è al corrente delle funzioni estese.

Nella sua forma essenziale, MySQL Proxy è uno strumento di ridirezione, che passa un pacchetto di richiesta dal client al server, prende dal server i risultati, e li passa al client. Nel passaggio di dati, il Proxy può intervenire, ed effettuare operazioni sulla query prima che venga inviata, o sui risultati, prima che vengano restituiti. Le modifiche vengono effettuate tramite script in Lua, che usano funzioni predefinite per alterare lo stato dei pacchetti. Le funzioni sono le seguenti:

  • connect_server in cui si può agire al momento della connessione al server;
  • read_handshake che avviene immediatamente dopo la connessione;
  • read_auth in cui si passano al server le credenziali di accesso;
  • read_auth_result dove viene ricevuto il risultato dell’operazione precedente;
  • read_query che si attiva per ogni query inviata al server;
  • read_query_result che viene richiamata quando il Proxy riceve il risultato di una query modificata;
  • disconnect_client che si attiva quando un client viene disconnesso.

Ecco come l’ho configurato ed installato  su Ubuntu per avere uno splitting r/w :

apt-get install mysql-proxy

Mi sono crato uno script dentro /root/scritpt/proxy.sh :
#!/bin/bash

MASTERDB=server1
SLAVEDB01=server2

LUA_PATH="/usr/share/mysql-proxy/?.lua" /usr/sbin/mysql-proxy \
 --daemon \
 --proxy-backend-addresses=$MASTERDB:3306 \
 --proxy-read-only-backend-addresses=$SLAVEDB01:3306 \
 --proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua

e gli ho assegnato i permessi di esecuzione con chmod.
Notare che lo script puo’ essere modificato per avere + slave.

 A questo punto facciamo partire automaticamente lo script creando un file in /etc/init.d/mysql-proxy :

#!/bin/bash
#
# mysql-proxy: Start mysql-proxy in daemon mode
#
# Author: OpenX
#
# chkconfig: - 99 01
# description: Start mysql-proxy in daemon mode with r/w splitting
# processname: mysql-proxy
start(){
 echo "Starting mysql-proxy..."
 /root/script/proxy.sh
}
stop(){
  echo "Stopping mysql-proxy..."
  killall mysql-proxy
}

case "$1" in
start)
start
;;
stop)
stop
;;
restart)
stop
start
;;
*)
echo "Usage: mysql-proxy {start|stop|restart}"
exit 1
esac

Occhio alle perfomance pero’ ! 
http://www.mysqlperformanceblog.com/2009/06/09/mysql-proxy-urgh-performance-and-scalability/ 

Bash script cambio password

Personalmente me lo sono scrittp per cabiare qualcosa come 54 password ftp.

Non volendolo fare a mano sono ricorso al solito bash

Gli utenti nei sistemi Linux sono contenuti in questo file  /etc/passwd
e si presentano in questo modo :

test.it:x:526:526:test.it:/home/test.it:/bin/sh

Per prima cosa elimino tutto quello che non mi serve :
awk -F":" '{ print $1 }' /etc/passwd > user.txt

ed ottengo quindi solo lo user :
test.it

A questo punto ecco che entra in azione lo script vero e proprio :

#!/bin/bash

function randpass
{
echo `</dev/urandom tr -dc A-Za-z0-9 | head -c8`
}

for i in `more ftp_ok`
do
p=`randpass`
echo $i $p

echo $p | passwd –stdin $i
done

Il gioco è fatto !

 

Allineare mysql slave

Cosa succede se si fa una bella insert sul nostro database mysql slave?
Due cose :

  1. Lo slave server si disallinea
  2. Il sistemista bestemmia 
Per controllore lo stato dello slave bisogna lanciare dallo slave :

mysql> show slave status\G 

Lo Slave utilizza piu’ thread. Il primo e’ una connessione remota al Master ed ha il compito di raccogliere i dati dal bin-log (BinLog Dump) ed e’ sempre attivo. Gli altri thread sono locali ed hanno il compito di ricevere il contenuto del bin-log (Slave I/O) e di applicarlo alla base dati (Slave SQL). In caso d’errore nell’inserimento dei dati il thread Slave SQL si interrompe mentre lo Slave I/O continua a raccogliere i dati dal Master. Con show slave status\G si ottiene l’indicazione dell’errore occorso; una volta corretto il problema la replicazione riprende dal punto in cui si era interrotta applicando il relay-log. 

Per risolvere il problema numero 1 bisogna seguire questa facile guida :

1. Stoppare sugli SLAVE SERVER il servizio di replica :

mysql> stop slave;

2. Lokkare le tabelle sul MYSQL MASTER (occhio a non uscire da mysql altrimenti questo comando viene flushato)
mysql> FLUSH TABLES WITH READ LOCK;

3. Effettuare un dump completo e copiarli sullo/i SLAVE/s :
mysqldump --all-database > databases.sql -u root -p 

4. Restore DB sugli SLAVE
mysql -u root -p databases.sql 

5. Controllare lo stato del MYSQL MASTER per vedere fino a che punto è arrivata il transaction bin log : (in un altra sessione ssh, vedi punto 2)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000059 | 17908642 |              |                  |
+------------------+----------+--------------+------------------+

6. Riattivare il MYSQL SLAVE
CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000059', MASTER_LOG_POS = 17148864;

7. Riattivare lo slave
mysql> start slave;

8. Riattivare il MYSQL MASTER in scrittura
UNLOCK TABLES;

Monitorare coda postfix

Un semplice script bash che controlla la coda di postix e ci avverte se supera un determinato limite :

 Bash | 
 
 copy code |
?

01
#!/bin/bash
02
limit=50;
03
notifyemail=luigi.molinaro@gmail.com;
04
servername=$(hostname);
05
 
06
queue_p=$(postqueue -p | grep Request | awk '{print $5}');
07
 
08
if [ $queue_p -ge $limit ] ; then
09
        echo "
10
Dear Server admin,
11
 
12
We have $limit in local mailqueue.
13
" | mail -s "Mailqueue limit exceeding - $servername" $notifyemail
14
 
15
fi
16