Archives de catégorie : DBMS

Mysql Clone test prod

#!/bin/bash
#llk
#Recuperation des dumps effectues sur le filer depuis jn1132 + import sur ce serveur
#lance par /etc/crontab

starttime=$("date")

#Recuperation du dernier backup
backup=$(ls -lastr /filer_backup/mylvmbackup/backup-*.tar.gz | tail -1 | awk '{print $10}')


echo "Demarrage de l'import des donnees Mysql le $starttime, le backup restore est $backup" > /filer_backup/import_dump.log

#cleanning mysql avant import
/etc/init.d/mysql stop;

if [ $? = 0 ]
then
        echo "Arret du service mysql : OK" >> /filer_backup/import_dump.log
else
        echo "Arret du service mysql : KO" >> /filer_backup/import_dump.log
fi;

sleep 15;

rm -rf /var/lib/mysql/data/*;

if [ $? = 0 ]
then
        echo "Supression des donnees dans /var/lib/mysql/data/ : OK" >> /filer_backup/import_dump.log
else
        echo "Supression des donnees dans /var/lib/mysql/data/ : KO" >> /filer_backup/import_dump.log
fi;

#import data sql
tar -I pigz -xvf $backup -C /var/lib/mysql/data/;

if [ $? = 0 ]
then
        echo "Decompression de l'archive $backup : OK" >> /filer_backup/import_dump.log
else
        echo "Decompression de l'archive $backup : KO" >> /filer_backup/import_dump.log
fi;

#mise en place des donnees importees
mv /var/lib/mysql/data/backup/* /var/lib/mysql/data/.;

if [ $? = 0 ]
then
        echo "Import des donnees : OK" >> /filer_backup/import_dump.log
else
        echo "Import des donnees : KO" >> /filer_backup/import_dump.log
fi;

#start mysql
/etc/init.d/mysql start

if [ $? = 0 ]
then
        echo "Demarrage du service Mysql : OK" >> /filer_backup/import_dump.log
else
        echo "Demarrage du service Mysql : KO" >> /filer_backup/import_dump.log
fi;

sleep 15;

# cmd mysql suite demande client
#mysql -e 'cmd mysql specifique au client;'
#if [ $? = 0 ]
#then
#        echo "Cmd mysql update : OK" >> /filer_backup/import_dump.log
#else
#        echo "Cmd mysql update ne s est pas deroulee correctement" >> /filer_backup/import_dump.log
#fi;

endtime=$("date")

echo "Fin du process d import des donness sql le $endtime" >> /filer_backup/import_dump.log

#rapport client

mail -s staggin1 your@mail.fr < /filer_backup/import_dump.log

Backup MySQL avancé

#!/bin/sh

date=`date +%Y%m%d`
day=`date +%u`
week=`date +%Y%U`
user=root
pass=password
dest=/var/backup_mysql
daily=$dest/data-day-$date

bases="mysql base1 base2"

if [ ! -d $daily ]
then
       mkdir -p $daily
fi

cd $daily
for base in $bases
do
	mysqldump -l --user $user --password=$pass --opt --databases $base > $base.sql
	tar -czf $base.tgz $base.sql
	rm $base.sql
done

mysqladmin -p$pass flush-tables

if [ $day == 1 ]
then
	weekly=$dest/data-week-$week
	if [ ! -d $weekly ]
	then
		mkdir $weekly
	fi
	cp $daily/*.tgz $weekly
fi

# On laisse 7 jours de backup
date=`date -d '7 days ago' +%Y%m%d`
rm -rf $dest/data-day-$date

# On laisse 6 mois de backup hebdo
date=`date -d '6 month ago' +%Y%U`
rm -rf $dest/data-week-$date

Backup MySQL Simple

#!/bin/sh

## Options de backup
opts="--skip-opt --event --triggers --routines --quick --extended-insert --create-options --set-charset --disable-keys"

# pour chaque base sauf performance_schema

for DBNAME in `mysql -u root -ppassword -B -e "show databases;" | sed 1d | grep -v performance_schema`
do
# on fait le backup et on compresse
        /usr/bin/mysqldump $opts -u root -ppassword $DBNAME | bzip2 > /mnt/mysql_backup/$DBNAME-$(date +%d%m%Y).sql.bz2
done

PostgreSQL

Requête SQL bloquée ou en attente

Donne le pid d’une requête SQL dans PostgreSQL en idle depuis plus de 30 secondes

SELECT procpid
FROM
(
    SELECT DISTINCT age(now(), query_start) AS age, procpid
    FROM pg_stat_activity, pg_locks
    WHERE pg_locks.pid = pg_stat_activity.procpid
) AS foo
WHERE age > '30 seconds'
ORDER BY age DESC
LIMIT 1 ;

Ensuite on peut tuer le PID donnée par cette requête.

kill -9 $pid

Affichage des statistiques des processus

La table pg_stat_activity contient la collection de statistiques sur les processus

Affichage mode classique

SELECT * FROM pg_stat_activity ;   

Postgres style

TABLE pg_stat_activity;

Aller plus loin http://gpdb.docs.pivotal.io/4350/ref_guide/system_catalogs/pg_stat_activity.html

MySQL Cluster : Problème de réplication

Contexte

Un slave MySQL tourne sur mysqld-04 et réplique en temps réel les modifications apportées au Cluster. Ce slave peut au choix utiliser comme master mysqld-01 ou mysqld-04 (sur mysqld-04 il y a donc le slave + le master qui tournent).

mysqld-04 est configuré comme suit slave port 3306 et master port 3307.

Le problème de désynchronisation vient du fait que la base de données du slave et celle du cluster ont des très légères différences (au niveau des procédures stockées). Si une modification est faite sur cette zone sur le cluster, lorsque le slave tentera de la répliquer, il ne trouvera pas la zone à modifier dans sa propre base et stoppera la réplication. Il faudra alors sauter l’erreur et relancer la réplication.

Oui je sais c’est pas courant de mettre un master avec un slave mais bon ça permet de voir un cas particulier qui est le multi mysqld.

Il y a aussi 2 data nodes et 2 api node mais on s’en fout, ils tournent tout seul.

Récupération de l’erreur

Sur mysqld-04, se connecter au slave et identifier le master et l’erreur au moment de la coupure :

mysql -p
mysql> show slave status \G

Type d’erreurs :

The incident LOST_EVENTS occured on the master. Message: error writing to the binary log ⇒ voir Switch Master

Error ‘Unknown storage engine ‘ndbcluster » on query. Default database: ‘XXX’. Query: ‘XXX’ ⇒ voir Skip Error

Switch Master

Le master qui a planté est Mysql-01

Il faut relancer la réplication sur un autre master. On récupère le dernier epoch sur le slave mysqld-04

mysql -u root -p

mysql> SELECT @latest:=MAX(epoch) FROM mysql.ndb_apply_status;
+---------------------+
| @latest:=MAX(epoch) |
+---------------------+
|  292125486287421459 |
+---------------------+
1 row in set (0,00 sec)

Sur un autre noeud master, on récupère la position dans les binlogs ainsi que le nom du fichier correspondant à l’epoch récupéré plus haut (exemple avec le master de mysqld-04.On utilise la master socket on peut tout aussi bien utiliser l’host et le port.

mysql -S /var/lib/mysql_master/mysql_master.sock -p
mysql> SELECT @file:=SUBSTRING_INDEX(next_file, '/', -1), @pos:=next_position FROM mysql.ndb_binlog_index WHERE epoch  = 292125486287421459 ORDER BY epoch ASC LIMIT 1;
+--------------------------------------------+---------------------+
| @file:=SUBSTRING_INDEX(next_file, '/', -1) | @pos:=next_position |
+--------------------------------------------+---------------------+
| mysql-bin.001996                           |           688465217 |
+--------------------------------------------+---------------------+
1 row in set (0,00 sec)

Puis sur le slave, on change de master en précisant le binlog et la position récupérée plus haut (exemple avec le master de mysqld-04, attention que l’IP/port correspondent) :

mysql> stop slave;
Query OK, 0 rows affected (0,01 sec)
mysql> CHANGE MASTER TO MASTER_HOST='localhost', MASTER_PORT=3307, MASTER_USER='replication',
MASTER_PASSWORD='passwd_replication', MASTER_LOG_FILE='mysql-bin.001996', MASTER_LOG_POS=688465217 ;
Query OK, 0 rows affected, 2 warnings (0,01 sec)

On regarde les warnings avec

mysql>show warnings
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely  insecure.                                                                                                                                         |
| Note  | 1760 | Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives. |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

On s’en balance.

Et on relance la réplication :

mysql> start slave;
Query OK, 0 rows affected (0,01 sec)

On vérifie que la réplication ait bien repris et que le slave rattrape son retard :

mysql> show slave status \G
      Seconds_Behind_Master: 2471
    ...
              Last_IO_Errno: 0
              Last_IO_Error: 
             Last_SQL_Errno: 0
             Last_SQL_Error: 

Skip Error

Saut de l’erreur, celle-ci n’étant pas critique.

Il se peut qu’il y est plusieurs erreurs mettre SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 5

Sur le serveur mysql slave :

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
Query OK, 0 rows affected (0,01 sec)
mysql> start slave;
Query OK, 0 rows affected (0,01 sec)}}}

Suivi du bon rétablissement avec :

mysql> show slave status \G
      Seconds_Behind_Master: 2471
              Last_IO_Errno: 0
              Last_IO_Error: 
             Last_SQL_Errno: 0
             Last_SQL_Error: 
mysql> show slave status \G
      Seconds_Behind_Master: 0
              Last_IO_Errno: 0
              Last_IO_Error: 
             Last_SQL_Errno: 0
             Last_SQL_Error: 

Incident réplication binlog Master

L’alerte en question est MySQL Cluster Replication : Master binlog interrupted mysqld-04

Contrairement aux erreurs ci-dessus, celle-ci n’est détectée que par les alertes du système de monitoring En effet, un show slave status ne remonte aucune erreur.

Pour confirmer l’erreur, dans un process list, on voit que l’instance master de mysqld-04 est en attente: Ceci n’est cependant pas obligatoire, car cela va dépendre de ce que le master foutait avant de se toler.

mysql> show processlist;
+--------+-------------+-----------+------+---------+---------+----------------------------------------------------------------------
| Id     | User        | Host      | db   | Command | Time    | State                                                                       | Info             |
+--------+-------------+-----------+------+---------+---------+----------------------------------------------------------------------
|      1 | system user |           |      | Daemon  |   34476 | Waiting for schema epoch                                                    | NULL             |
|      3 | system user |   | NULL | Connect | 2073040 | Slave has read all relay log; waiting for the slave I/O thread to update it 
| NULL             |
| 220997 | root        | localhost | NULL | Query   |       0 | init                                                                        | show processlist |
+--------+-------------+-----------+------+---------+---------+----------------------------------------------------------

On peut également constater que les dernier epoch entre mysqld-04 et mysqld-01 sont différents, ce qui sert également à confirmer l’erreur:

Sur Mysqld-04, on utilise la socket Mysql pour se connecter afin d’être sur le master.

mysql -S /var/lib/mysql_master/mysql_master.sock -p

mysql> SELECT @file:=SUBSTRING_INDEX(next_file, '/', -1), @pos:=next_position, 
epoch FROM mysql.ndb_binlog_index ORDER BY epoch DESC LIMIT 1;
+--------------------------------------------+---------------------+--------------------+
| @file:=SUBSTRING_INDEX(next_file, '/', -1) | @pos:=next_position | epoch              |
+--------------------------------------------+---------------------+--------------------+
| mysql-bin.002514                           |           418531181 | 310467702470737931 |
+--------------------------------------------+---------------------+--------------------+

Et sur mysqld-01

mysql -p
mysql> SELECT @file:=SUBSTRING_INDEX(next_file, '/', -1), @pos:=next_position, 
epoch FROM mysql.ndb_binlog_index ORDER BY epoch DESC LIMIT 1;

+--------------------------------------------+---------------------+--------------------+
| @file:=SUBSTRING_INDEX(next_file, '/', -1) | @pos:=next_position | epoch              |
+--------------------------------------------+---------------------+--------------------+
| mysql-bin.002645                           |           118810291 | 310541124936663043 |
+--------------------------------------------+---------------------+--------------------+

Avant de procéder au redémarrage de l’instance master de mysqld-04, il faut effectuer un changement de master du slave sur mysqld-01. Voir change master au dessus.

Une fois le changement effectué, on peut procéder au redémarrage, pour connaitre l’id de l’instance visée, se rendre dans le fichier my.cnf souvent dans /etc.

mysqld_multi stop 1

Si les instances ne s’arrêtent pas, on kill les processus (Attention à bien tuer les processus de l’instance master !!!)

ps faux

root     24522  0.0  0.0   9980   328 ?        S     2014   0:00 /bin/sh /usr/bin/mysqld_safe --skip-slave-start   --default-storage-engine=innodb --explicit_defaults_for_t 

mysql    26110  210 37.1 11110332 9153496 ?    Sl    2014 1133795:12  \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql/ --plugin-dir=/usr/lib64/mysql/plugin --

root     11520  0.0  0.0  10200   340 ?        S     2014   0:00 /bin/sh /usr/bin/mysqld_safe --skip-innodb --default-storage-engine=NDBCLUSTER --default_tmp_storage_engi

mysql    18226  2.1  1.2 706924 311516 ?       Sl   Apr14 755:37  \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql_master/ --plugin-dir=/usr/lib64/mysql/plugin
kill -9 11520 18226

Dans les logs, on constate le bon redémarrage de l’instance :

2015-05-08 10:15:39 18226 [Note] /usr/sbin/mysqld: Normal shutdown
2015-05-08 10:15:39 18226 [Note] Giving 2 client threads a chance to die gracefully
2015-05-08 10:15:39 18226 [Note] Event Scheduler: Purging the queue. 0 events
2015-05-08 10:15:39 18226 [Note] Shutting down slave threads
2015-05-08 10:15:39 18226 [Note] Error reading relay log event: slave SQL thread was killed
2015-05-08 10:15:41 18226 [Note] Forcefully disconnecting 1 remaining clients
2015-05-08 10:17:52 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql_master/
2015-05-08 10:17:53 16055 [Note] Plugin 'InnoDB' is disabled.
2015-05-08 10:17:53 16055 [Note] Plugin 'FEDERATED' is disabled.
2015-05-08 10:17:53 16055 [Note] NDB: NodeID is 171, management server 'xxx.xxx.xxx.xxx:1186'
2015-05-08 10:17:54 16055 [Note] NDB[0]: NodeID: 171, all storage nodes connected
2015-05-08 10:17:54 16055 [Note] NDB[1]: NodeID: 170, all storage nodes connected
2015-05-08 10:17:54 16055 [Note] Starting Cluster Binlog Thread
2015-05-08 10:17:54 16055 [Note] Recovering after a crash using mysql-bin

Rapidement, le master récupère son retard et on a de nouveau le même epoch sur les 2 serveurs.

Corruption de la base ndb_binlog_index

le master est mysqld-01, un problème de réplication master se produit dessus. Nous voulons changer de master sur le slave pour passer sur mysql-04.

Mais la une surprise attends :

mysql> SELECT @file:=SUBSTRING_INDEX(next_file, '/', -1), @pos:=next_position 
FROM mysql.ndb_binlog_index WHERE epoch = 341260363125227536 ORDER BY epoch ASC LIMIT 1;

retourne

ERROR 145 (HY000): Table './mysql/ndb_binlog_index' is marked as crashed and should be repaired

Pour s’en sortir.

repair table mysql.ndb_binlog_index;

ou en fait préférer celle-ci pour le ne pas altérer les binlogs

REPAIR NO_WRITE_TO_BINLOG TABLE mysql.ndb_binlog_index;

MySQL Cluster : Analyse Incident

Contexte

Les commandes suivantes, loin d’être exhaustive permettent de diagnostiquer un cluster Mysql, ou une instance seule.

Elle peuvent également être utilisées dans le cadre d’un audit.

Je ne traiterais pas de méthodologie ici car c’est dépendant du type de problème rencontré ou de l’expérience de l’auditeur. Je vous recommande https://www.percona.com/downloads/percona-toolkit

Pour le .deb installation

dpkg -i percona-toolkit-x-x-x.deb

Analyse des binlog

Lors de la récupération de l’epoch lors d’un problème de réplication, on récupère également un numéro de binlog et une position dans celui-ci. Pour voir la requête incriminée on peut faire.

mysqlbinlog -u root -p /var/lib/mysql/mysql-bin.00yyyy -j xxxxxxx --base64-output=decode-rows -v

où mysql-bin.00yyyy est le fichier binlog et -j xxxxxxx la position. Ceci est valable pour des binlog en RAW.

Ça renvoi la requête précise.

De manière plus générale, lorsque qu’on ne connait pas le binlog et la position, on requête autour de la date de l’incident. Ici entre le 31/10/2015 à 00h20 et 00h27.

Essayer d’être le plus proche possible de la fenêtre d’incident car le résultat peut être très volumineux.

mysqlbinlog -u root -p /var/lib/mysql/mysql-bin.00**** --base64-output=decode-rows -v  --start-datetime="2015-10-31 00:20:00" --stop-datetime="2015-10-31 00:27:00" >> /root/binlog.txt

Ensuite utiliser le parser suivant pour formater tout ça en un truc lisible.

cat /root/binlog.txt | awk \
'BEGIN {s_type=""; s_count=0;count=0;insert_count=0;update_count=0;delete_count=0;flag=0;} \
{if(match($0, /#15.*Table_map:.*mapped to number/)) {printf "Timestamp : " $1 " " $2 " Table : " $(NF-4); flag=1} \
else if (match($0, /(### INSERT INTO .*..*)/)) {count=count+1;insert_count=insert_count+1;s_type="INSERT"; s_count=s_count+1;}  \
else if (match($0, /(### UPDATE .*..*)/)) {count=count+1;update_count=update_count+1;s_type="UPDATE"; s_count=s_count+1;} \
else if (match($0, /(### DELETE FROM .*..*)/)) {count=count+1;delete_count=delete_count+1;s_type="DELETE"; s_count=s_count+1;}  \
else if (match($0, /^(# at) /) && flag==1 && s_count>0) {print " Query Type : "s_type " " s_count " row(s) affected" ;s_type=""; s_count=0; }  \
else if (match($0, /^(COMMIT)/)) {print "[Transaction total : " count " Insert(s) : " insert_count " Update(s) : " update_count " Delete(s) : " \
delete_count "] \n+----------------------+----------------------+----------------------+----------------------+"; \
count=0;insert_count=0;update_count=0; delete_count=0;s_type=""; s_count=0; flag=0} } '

Ça renvoi un truc du style :

Timestamp : #151031 0:20:11 Table : `base1`.`PRICE`Timestamp : #151031 0:20:11 Table : `base1`.`SYNCINFO`Timestamp : #151031 0:20:11 Table : `base1`.`SERVICESTATS`Timestamp : #151031 0:20:11 Table : `mysql`.`ndb_apply_status` Query Type : UPDATE 1651 row(s) affected
[Transaction total : 1651 Insert(s) : 2 Update(s) : 1649 Delete(s) : 0]

 Timestamp : #151031 0:21:36 Table : `base1`.`PRICE`Timestamp : #151031 0:21:36 Table : `base1`.`SYNCINFO`Timestamp : #151031 0:21:36 Table : `mysql`.`ndb_apply_status` Query Type : UPDATE 1220 row(s) affected
[Transaction total : 1220 Insert(s) : 1 Update(s) : 1219 Delete(s) : 0] 

Timestamp : #151031 0:21:36 Table : `base1`.`PRICE`Timestamp : #151031 0:21:36 Table : `base1`.`SYNCINFO`Timestamp : #151031 0:21:36 Table : `mysql`.`ndb_apply_status` Query Type : UPDATE 1214 row(s) affected
[Transaction total : 1214 Insert(s) : 1 Update(s) : 1213 Delete(s) : 0] 

Ca c’est pour la méthode arawak, sinon j’ai découvert un toolkit fourni par Percona qui est pas mal.

https://www.percona.com/software/mysql-tools/percona-toolkit

On s’en servira dans l’analyse des slow query.

Analyse de slow query

Attention : on n’effectuera pas l’analyse sur le serveur car cela peut le surcharger. Ces commandes sont gourmandes.

Si les slow query ne sont pas activées faire ( Mysql >5.1 ) :

mysql> set global slow_query_log=1;
mysql> set global long_query_time=5;

Les slow query logguées seront d’un temps supérieur ou égal à 5 sec

Avec outils fournit par Mysql ( mysql-client package ) Trier par occurrence ( -s c ) et affichage du top 10 ( -t 10 ).

 mysqldumpslow -s c -t 10 mysql.slow

trier par le temps en sec qu’a mis chaque requête (-s t ) sinon voir man mysqldumpslow

 mysqldumpslow -s t -t 10 mysql.slow

Le fichier mysql.slow peut être très volumineux

Pour l’avoir sur une journée :

grep -A5  "151031" mysql.slow  > mysql_day_31.slow
mysqldumpslow -s t -t 10 mysql_day_31.slow

Exemple de rendu

Count: 22  Time=72.40s (1592s)  Lock=0.00s (0s)  Rows=0.0 (0), exportXML[exportXML]@xxxx.xxxxx.fr
  update XML_STAY set state=N, lastModif=null where state!=N limit N

Count: 1  Time=746.53s (746s)  Lock=0.00s (0s)  Rows=0.0 (0), base1[base1]@yxxxx.xxxxx.fr
  call base1.resetPrice()

Percona toolkit

pt-query-digest mysql.slow | less

Beaucoup plus lent que mysqldumpslow sur un gros fichier.

Analyse sur une plage réduite, beaucoup plus rapide que mysqldumpslow -g REGEXP qui est bug (grep)

pt-query-digest --since '2015-10-31 00:20:00' --until '2015-10-31 00:30:00' mysql.slow

Exemple de rendu :

# 164.8s user time, 980ms system time, 35.91M rss, 99.38M vsz
# Current date: Sun Nov  8 16:59:39 2015
# Hostname: xxxxxxx
# Files: mysql.slow
# Overall: 46 total, 9 unique, 0.08 QPS, 2.02x concurrency _______________
# Time range: 2015-10-31 00:20:06 to 00:29:48
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          1177s      1s    747s     26s      3s    115s      1s
# Lock time             4s    33us      1s    87ms   356ms   215ms   972us
# Rows sent            690       0     669      15    0.99   95.12       0
# Rows examine       2.13M       0   2.11M  47.52k   2.06k 305.93k   26.08
# Query size        22.85k      23   1.25k  508.72  511.45  209.51  511.45

# Profile
# Rank Query ID           Response time  Calls R/Call   V/M   Item
# ==== ================== ============== ===== ======== ===== ============
#    1 0xF909A1007AFA8E5F 746.5286 63.4%     1 746.5286  0.00 CALL base.resetPrice
#    2 0x18DE522EBCC5AA94 360.0182 30.6%     1 360.0182  0.00 SELECT
#    3 0x65972C267C7778A3  43.2164  3.7%    25   1.7287  0.22 SELECT
# MISC 0xMISC              27.4046  2.3%    19   1.4423   0.0 <6 ITEMS>

Ensuite vient le détails de toutes les requêtes qui font parti du top. Les CALL sont des procédures stockées.

les requêtes contenant SQL_NO_CACHE sont des sauvegardes à moins que le dév soit complétement con.

Analyse de configuration

Voir la configuration live du cluster, fonctionne que sur un node de management.

#ndb_mgm
 ndb_mgm> show

20 est l’ID d’un datanodes.

ndb_mgm> 20 REPORT MEMORYUSAGE

donne la mémoire ( RAM ) utilisée pour le stockage des databases

voir

 pt-mysql-summary

config.ini est le fichier du cluster il est présent sur tous les nodes

my.cnf est la configuration d’un node Mysql ou API

Voici les paramètres intéressants :

log-error=/var/log/mysql.err
ndb-cluster-connection-pool=16
ndb_cache_check_time=2     // check cohérence des données en sec
long_query_time=5
slow_query_log=1
slow_query_log_file=/var/log/mysql.slow
query_cache_type=0  // désactive le cache les autres query_cache_* ne sont pas pris en compte.
max-connections=512  // déjà bien 1024 que sur les heavy prod

#binlogs conf
log-bin=mysql-bin
binlog-format=row
ndb-log-update-as-write=0
expire_logs_days=5

#engine
default-storage-engine=NDBCLUSTER
default_tmp_storage_engine=NDBCLUSTER

#Slave R/O 
server-id=1
slave_allow_batching=1
relay-log=mysql-relay-bin
replicate-wild-do-table=database_name.%
replicate-wild-do-table=replication_test.%
replicate-do-table=mysql.ndb_apply_status
slave-exec-mode=IDEMPOTENT
read_only=1

Analyse des logs

Sur les nodes de management

recherche des alertes dans les logs du cluster.

grep ALERT cluster.log

ou

ndb_mgm> 20 REPORT EVENTLOG

Sur les nodes les logs sont généralement dans /var/log/mysql/ et se nomme mysql.err

grep "cluster failure" /var/log/mysql.err |tail

Analyse couche SQL

Renvoi des tables qui n’utilise pas le moteur ndbcluster dans un cluster ndb.

mysql>use information_schema
mysql>select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,ENGINE from tables where ENGINE != "ndbcluster" ;

Exemple avec deux moteurs pour une base :

| def           | exportXML          | XML_FTP                               | InnoDB |
| def           | exportXML          | XML_PARAM                             | InnoDB |
| def           | exportXML          | XML_SITE                              | MyISAM |
| def           | exportXML          | XML_STAY                              | MyISAM |
| def           | exportXML          | XML_TARIFF                            | MyISAM |

Voir la liste des procédures stockées

mysql> select name from mysql.proc where db='database_name';

Voir le contenu d’une procédure

mysql>use information_schema;
mysql>select ROUTINE_DEFINITION FROM ROUTINES WHERE SPECIFIC_NAME='Nom_Procedure' AND ROUTINE_SCHEMA='Nom_Database';

Voir les triggers et les tablespaces en mode brut

mysqldump -p -u root --no-data --no-create-info database_name 

Taille d’une base en mode je connais que awk

mysql  -p database_name -e "show table status" 2>/dev/null |awk ' { SUM += $7; SUM += $9  } END { print SUM/1024/1024 }' | less

Taille en SQL

SELECT
 table_schema AS $DBNAME, 
 ROUND(SUM( data_length + index_length ) / 1024 / 1024, 2) AS BaseDonneesMo 
 FROM information_schema.TABLES
 GROUP BY TABLE_SCHEMA;
 

Autre méthode

SELECT 
 TABLE_NAME,
 CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024), 2), 'Mo') AS TailleMo 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = '$DBNAME'

Attention dans le cas d’un MysqlCluster, c’est les datanodes qui contiennent vraiment les données. Par conséquent un

du -sf /var/lib/mysql/*

N’aura aucun sens, à part si d’autres bases sont présentes en InnDB ou MyIsam.

Voir les types de moteur supportés

mysql> show engines ;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster         | DEFAULT | Clustered, fault-tolerant tables                               | YES          | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ndbinfo            | YES     | MySQL Cluster system information storage engine                | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)

Analyse couche système

Recherche d’un manque de ressource système, la colonne la plus importante est failcount. marche uniquement sur un container openvz

cat /proc/user_beancounters

Nombre de threads Mysql, utile surtout sur les node mysqld et api

ps -Lfp `pidof mysqld` | wc -l

Les traditionnelles

  top 
  vmstat 
  free -h 
  cat /proc/cpu_info
  df -h 
  iostat -x

MySQLCluster : Installation

Installation

L’installation se fera dans /usr/local/mysql sur tous les nodes du cluster.

apt-get install libaio1
groupadd mysql
useradd -r -g mysql mysql

dans /etc/passwd ⇒ /bin/false

cd /usr/local 
tar xvzf mysql-cluster-gpl-7.4.7-linux-glibc2.5-x86_64.tar.bz
ln -s /usr/local/mysql-cluster-gpl-7.4.7-linux-glibc2.5-x86_64 mysql
chown -R mysql /usr/local/mysql/*

Lancement de l’Oracle installeur

cd /bin 
./nbd_setup.py

Setup configuration

Mysql-01   1.12 mysqld ndb_mgmd
Mysql-02   1.14 ndbd
Mysql-03   1.13 mysqld
Mysql-04   1.23 ndbd

Présences des fichiers

config.ini sur tous les nodes
my.cnf pour les mysqld
ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=1	@192.168.1.14  (mysql-5.6.25 ndb-7.4.7, Nodegroup: 0)
id=2	@192.168.1.23  (mysql-5.6.25 ndb-7.4.7, Nodegroup: 0, *)

[ndb_mgmd(MGM)]	1 node(s)
id=49	@192.168.1.12  (mysql-5.6.25 ndb-7.4.7)

[mysqld(API)]	4 node(s)
id=50	@192.168.1.12  (mysql-5.6.25 ndb-7.4.7)
id=51 (not connected, accepting connect from 192.168.1.12)
id=52 (not connected, accepting connect from 192.168.1.13)
id=53	@192.168.1.13  (mysql-5.6.25 ndb-7.4.7)

Lancement des Processus

management node + 1 mysql node

 /usr/local/mysql/bin/ndb_mgmd --initial --ndb-nodeid=49 --config-dir= /usr/local/mysql/MySQL_Cluster/49/ --c
 /usr/local/mysql/bin/mysqld --defaults-file= /usr/local/mysql/MySQL_Cluster/50/my.cnf

data node multitread

/usr/local/mysql/bin/ndbmtd --ndb-nodeid=1 --ndb-connectstring=192.168.1.12:1186,
/usr/local/mysql/bin/ndbmtd --ndb-nodeid=1 --ndb-connectstring=192.168.1.12:1186,

SQL (Api) node

/usr/local/mysql/bin/mysqld --defaults-file= /usr/local/mysql/MySQL_Cluster/53/my.cnf

Configuration

root@Mysql-03: /usr/local/mysql/MySQL_Cluster/53# cat my.cnf 
#
# Configuration file for Warsong_Cluster
# Generated by mcc
#

[mysqld]

log-error=mysqld.53.err datadir= »/usr/local/mysql/MySQL_Cluster/53/ » tmpdir= »/usr/local/mysql/MySQL_Cluster/53/tmp » basedir= »/usr/local/mysql/ » port=3306 ndbcluster=on ndb-nodeid=53 ndb-connectstring=192.168.1.12:1186, socket= »/usr/local/mysql/MySQL_Cluster/53/mysql.socket »

root@Mysql-01:/usr/local/mysql/MySQL_Cluster/50# cat my.cnf 
#
# Configuration file for Warsong_Cluster
# Generated by mcc
#

[mysqld]

log-error=mysqld.50.err datadir= »/usr/local/mysqlMySQL_Cluster/50/ » tmpdir= »/usr/local/mysqlMySQL_Cluster/50/tmp » basedir= »/usr/local/mysql/ » port=3306 ndbcluster=on ndb-nodeid=50 ndb-connectstring=192.168.1.12:1186, socket= »/usr/local/mysql/MySQL_Cluster/50/mysql.socket » root@Mysql-01:/usr/local/mysql/MySQL_Cluster/49# cat config.ini # # Configuration file for Warsong_Cluster # [NDB_MGMD DEFAULT] Portnumber=1186 [NDB_MGMD] NodeId=49 HostName=192.168.1.12 DataDir=/usr/local/mysql/MySQL_Cluster/49/ Portnumber=1186 [TCP DEFAULT] SendBufferMemory=2M ReceiveBufferMemory=2M [NDBD DEFAULT] BackupMaxWriteSize=1M BackupDataBufferSize=16M BackupLogBufferSize=4M BackupMemory=20M BackupReportFrequency=10 MemReportFrequency=30 LogLevelStartup=15 LogLevelShutdown=15 LogLevelCheckpoint=8 LogLevelNodeRestart=15 DataMemory=1M IndexMemory=1M MaxNoOfTables=4096 MaxNoOfTriggers=3500 NoOfReplicas=2 StringMemory=25 DiskPageBufferMemory=64M SharedGlobalMemory=20M LongMessageBuffer=32M MaxNoOfConcurrentTransactions=16384 BatchSizePerLocalScan=512 FragmentLogFileSize=64M NoOfFragmentLogFiles=16 RedoBuffer=32M MaxNoOfExecutionThreads=2 StopOnError=false LockPagesInMainMemory=1 TimeBetweenEpochsTimeout=32000 TimeBetweenWatchdogCheckInitial=60000 TransactionInactiveTimeout=60000 HeartbeatIntervalDbDb=15000 HeartbeatIntervalDbApi=15000 [NDBD] NodeId=1 HostName=192.168.1.14 DataDir=/usr/local/mysql/MySQL_Cluster/1/ [NDBD] NodeId=2 HostName=192.168.1.23 DataDir=/usr/local/mysql/MySQL_Cluster/2/ [MYSQLD DEFAULT] [MYSQLD] NodeId=50 HostName=192.168.1.12 [MYSQLD] NodeId=53 HostName=192.168.1.13 [API] NodeId=51 HostName=192.168.1.12 [API] NodeId=52 HostName=192.168.1.13

Attentions les paramètres de ce fichier sont pour une toute petite instance

Seul les mgmt node et les data node peuvent restart via ndb_mgm

la socket se trouve dans

mysql -S /usr/local/mysql/MySQL_Cluster/50/mysql.socket

MySQL taille des tables

Affichage de la taille des bases de donnée

Taille en Mo

SELECT
 table_schema AS "Nom de la base", 
 ROUND(SUM( data_length + index_length ) / 1024 / 1024, 2) AS "Taille en Mo" 
 FROM information_schema.TABLES
 GROUP BY TABLE_SCHEMA;
Exemple de rendu :
+--------------------+--------------+
| Nom de la base     | Taille en Mo |
+--------------------+--------------+
| information_schema |         0.17 |
| mysql              |         0.65 |
| performance_schema |         0.00 |
+--------------------+--------------+
3 rows in set (0.01 sec)

Taille en Go

SELECT
 table_schema AS "Nom de la base", 
 ROUND(SUM( data_length + index_length ) / 1024 / 1024 /1024 , 2) AS "Taille en Go" 
 FROM information_schema.TABLES
 GROUP BY TABLE_SCHEMA;

MySQL Query Cache

Note : Le gain de performance de l’activation du cache des requêtes est très dépendant de l’applicatif, il sera très efficace pour les requêtes en lecture identiques. Et cela sera d’autant plus vrai pour des tables MyISAM. L’effet sera contre productif sur des charges d’écriture importante.

Demande si le cache pour les requêtes est activé

SHOW VARIABLES LIKE 'have_query_cache';

Résultat

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

Paramétrage du query cache

SHOW GLOBAL VARIABLES LIKE 'query_cache%';

Résultat :

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_strip_comments   | OFF      |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
6 rows in set (0.00 sec)

Désactivation du Qcache

SET GLOBAL query_cache_type=0; 
SET GLOBAL query_cache_size=0; 

Activation du Qcache

SET GLOBAL query_cache_type=1; 
SET GLOBAL query_cache_size = 16777216;

Statistiques du cache hit

SHOW STATUS LIKE "qcache%";

Résultat :

+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 0      |
| Qcache_free_memory      | 0      |
| Qcache_hits             | 130025 |
| Qcache_inserts          | 22837  |
| Qcache_lowmem_prunes    | 1915   |
| Qcache_not_cached       | 8382   |
| Qcache_queries_in_cache | 0      |
| Qcache_total_blocks     | 0      |
+-------------------------+--------+
8 rows in set (0.00 sec)

Paramétrage via my.cnf

Nécessite la relance de Mysql :

A VENIR

MySQL sauvegarde

Dump et restauration

Dump de database

mysqldump -u username -p DB_name > DB_name_save.sql

Restore via dump de la base

mysql -u username -p  DB_name < DB_name_save.sql

Dump avec plus d’options

mysqldump -u username -p  --single-transaction --flush-logs --events --ignore-table=mysql.event --all-databases > full_backup_db.sql

Compression du dump

mysqldump -u username -p  --single-transaction --flush-logs --events --ignore-table=mysql.event --all-databases | gzip > full_backup_db.sql.gz