Sauvegardes MySQL avec LVM2

Les avantages du LVM2

La nouvelle mouture du LVM (Logical Volume Manager) qui a vu le jour le 20 février 2002 avec la première version stable apporte les améliorations suivantes :

• compatible avec les noyaux 2.6.x
• compatible avec le LVM1
• mise à jour transactionnelle des metadata (permet une récupération plus rapide dans le cas d'un crash serveur)
• supporte le clustering de fichiers (gfs, ocfs)
• supporte l'extension des volumes strippés
• supporte le mirroring des volumes
• supporte beaucoup plus de volumes physiques et logiques (256 -> 2³²)
• snapshots en lecture/écriture

Sauvegarde MySQL

Les 2 types de sauvegardes possibles vont être étudiées dans le cas où LVM2 est disponible, permettant une interruption de service des plus courtes surtout dans le cas de bases très sollicitées et/ou ayant une volumétrie importante.

Sauvegarde Physique

Pour effectuer une sauvegarde physique il faut que les données sur disque soient dans un état consistent, c'est à dire que les fichiers ne soient pas modifiés durant la sauvegarde. Pour cela, la méthode habituelle consiste à arrête le serveur de base de données et à sauvegarder les fichiers. Cependant, cela nécessite un arrêt de service, et plus les fichiers sont nombreux et de taille importante, plus la sauvegarde durera longtemps. Ceci n'est souvent pas applicable dans les services ayant une activité de 24/24 7j/7. L'utilisation du LVM2 satisfait donc pleinement à cette situation.

Cette étape se déroule en 8 temps :

• vérouiller en lecture toutes les tables avec la commande FLUSH TABLES WITH READ LOCK
• créer un snapshot
• relever la position des binlogs au cas où il faudrait construire un slave à partir de cette sauvegarde
• dévérouiller des tables
• monter le volume logique correspondant au snapshot
• copier les fichiers se trouvant dans le snapshot
• démonter le volume logique
• supprimer le snapshot

Passons à la pratique :

Voilà le volume logique que j'utilises pour stocker mes données MySQL :

 root@McQueen:~# lvscan |grep mysql
ACTIVE            '/dev/vg01/lv_mysqldb' [1.00 GB] inherit

root@McQueen:~# mount|grep mysql
/dev/mapper/vg01-lv_mysqldb on /var/lib/mysql type ext3
(rw,noatime)

Commençer la procédure de sauvegarde en se connectant au serveur :

 mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G
*************************** 1. row ***************************
   File: db10-bin.000005
Position: 1081
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

Garder la connexion précédente ouverte, car sinon le verrou global posé est libéré, et créer le snapshot qui sera utilisé pour la sauvegarde :

root@McQueen:~# lvcreate --snapshot --name mysql_datadir_snapshot
 -L 200M /dev/vg01/lv_mysqldb
Logical volume "mysql_datadir_snapshot" created

Vous avez remarqué que je n'utilise pas la même taille pour mon snapshot (200Mo) que pour la base (1Go). En effet, la taille allouée au snapshot ne sera utilisée que pour les nouvelles écritures et pour les héberger les blocs modifiés sur le volume logique source (pour plus d'information voir http://www.tldp.org/HOWTO/LVM-HOWTO/snapshotintro.html)

Vous pouvez ensuite libérer le verrou acquis en vous déconnectant du serveur MySQL ou en utilisant la commande UNLOCK TABLES

Monter le volume logique correspondant au snapshot créé pour effectuer la sauvegarde physique des fichiers

 mount /dev/vg01/mysql_datadir_snapshot /mnt

Sauvegarder les fichiers MySQL

 tar cvzf mysqlbackup.tgz /mnt /etc/mysql

Démonter le snapshot et le supprimer

 umount /mnt && lvremove -f /dev/vg01/mysql_datadir_snapshot

Sauvegarde Logique

Pour effectuer une sauvegarde logique il faut que le serveur de bases de données soit démarré et acquérir un verrou global à chaque base pour sauvegarder les tables sans qu'elles soient désynchronisées. Selon l'activité de la base et sa volumétrie, l'impact sur les traitements courants et la durée de la sauvegarde peuvent être importants.

Cette étape se déroule en 9 temps :

• vérouiller en lecture de toutes les tables avec la commande FLUSH TABLES WITH READ LOCK
• créer du snapshot
• dévérouiller les tables / se déconnecter
• monter le volume logique correspondant au snapshot
• démarrer un second serveur avec les fichiers se trouvant dans le snapshot
• effectuer la sauvegarde logique
• arrêter le second serveur de base de données
• démonter le volume logique
• supprimer le snapshot

Passons à la pratique :

Voilà le volume logique que j'utilises pour stocker mes données MySQL :

 root@McQueen:~# lvscan |grep mysql
ACTIVE            '/dev/vg01/lv_mysqldb' [1.00 GB] inherit

root@McQueen:~# mount|grep mysql
/dev/mapper/vg01-lv_mysqldb on /var/lib/mysql type ext3
(rw,noatime)

Commençer la procédure de sauvegarde en se connectant au serveur :

 mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

Garder la connexion précédente ouverte, car sinon le verrou global posé est libéré, et créer le snapshot qui sera utilisé pour la sauvegarde :

root@McQueen:~# lvcreate --snapshot --name mysql_datadir_snapshot
-L 200M /dev/vg01/lv_mysqldb
Logical volume "mysql_datadir_snapshot" created

Vous pouvez ensuite libérer le verrou acquis en vous déconnectant du serveur MySQL ou en utilisant la commande UNLOCK TABLES

Monter le volume logique correspondant au snapshot créé

 mount /dev/vg01/mysql_datadir_snapshot /mnt

Démarrer un second serveur de base de données à partir des fichiers du snapshot, en prenant soin de désactiver le réseau puisque l'on se connectera directement en utilisant la nouvelle socket

 mysqld --basedir=/usr --datadir=/mnt --user=mysql --skip-networking
--socket=/mnt/mysqld.sock

Exporter les données

 mysqldump -S /mnt/mysqld.sock -B base1 base2

Il est aussi possible d'utiliser l'option --master-data pour exporter la position du serveur au niveau des fichiers de log binaires pour mettre en place une réplication

Arrêter le second serveur de base de données

 mysqladmin --defaults-file=/etc/mysql/debian.cnf
-S /mnt/mysqld.sock shutdown

Démonter le snapshot et le supprimer

 umount /mnt && lvremove -f /dev/vg01/mysql_datadir_snapshot

Conclusion

Cet article permet de comprendre l'intérêt d'utiliser le LVM2 pour effectuer ses sauvegardes MySQL et éviter d'avoir à interrompre le service utilisant le serveur de base de données. Il faudra cependant considérer l'impact sur les performances que peut induire l'existence d'un snapshot. En effet, les données modifiées sur le volume logique source sont déplacées vers le snapshot ce qui a un coup supplémentaire. Si votre serveur n'est pas déjà submergé d'activité i/o vous n'avez pas à vous inquiéter, dans le cas contraire rabattez vous sur un esclave ou tout autre machine moins sollicitée ou disposant d'une bande passante i/o plus importante.

Query Cache MySQL Vs Oracle

Le Query Cache fait partie intégrante de MySQL depuis la version 3.23 (si ce n'est plus tôt). Il aura fallu attendre jusqu'à la version 11G pour voir arriver cette fonctionnalité chez Oracle. Nous allons tester cette fonctionnalité chez MySQL et Oracle et voir si le fait que l'on utilise le partitionnement apporte quelque chose.

Pour MYSQL (5.1 requis) :

CREATE TABLE table_mysql_partitionned(ident int unsigned, quand date, valeur int unsigned)
ENGINE=INNODB
PARTITION BY RANGE( YEAR(quand) )
(
PARTITION p2006 VALUES LESS THAN (2007),
PARTITION p2007 VALUES LESS THAN (2008),
PARTITION p2008 VALUES LESS THAN (2009),
PARTITION p2009 VALUES LESS THAN (2010)
);

INSERT INTO table_mysql_partitionned VALUES(1,'2008-06-04',20),(2,'2007-05-04',35),(3,'2008-07-04',3),(4,'2009-10-03',11),(1,'2009-12-25',46),
(2,'2008-10-12',43),(10,'2006-05-03',32),(5,'2007-06-12',56);


mysql> select * from table_mysql_partitionned where quand between '2008-01-01' and '2008-10-01';
+-------+------------+--------+
| ident | quand | valeur |
+-------+------------+--------+
| 1 | 2008-06-04 | 20 |
| 3 | 2008-07-04 | 3 |
+-------+------------+--------+
mysql> show global status like 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 20958592 |
| Qcache_hits | 4 |
| Qcache_inserts | 4 |
| Qcache_lowmem_prunes | 0 |
+-------------------------+----------+
8 rows in set (0,00 sec)

mysql> select * from table_mysql_partitionned where quand between '2008-01-01' and '2008-10-01';
+-------+------------+--------+
| ident | quand | valeur |
+-------+------------+--------+
| 1 | 2008-06-04 | 20 |
| 3 | 2008-07-04 | 3 |
+-------+------------+--------+
2 rows in set (0,00 sec)

mysql> show global status like 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 20958592 |
| Qcache_hits | 5 |
| Qcache_inserts | 4 |
| Qcache_lowmem_prunes | 0 |
+-------------------------+----------+
8 rows in set (0,01 sec)

mysql> INSERT INTO table_mysql_partitionned VALUES(1,'2009-06-04',34);
Query OK, 1 row affected (0,04 sec)

mysql> select * from table_mysql_partitionned where quand between '2008-01-01' and '2008-10-01';
+-------+------------+--------+
| ident | quand | valeur |
+-------+------------+--------+
| 1 | 2008-06-04 | 20 |
| 3 | 2008-07-04 | 3 |
+-------+------------+--------+
2 rows in set (0,00 sec)

mysql> show global status like 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 20959616 |
| Qcache_hits | 5 |
| Qcache_inserts | 5 |
| Qcache_lowmem_prunes | 0 |
+-------------------------+----------+
8 rows in set (0,00 sec)

La partition n'a pas été modifiée et pourtant le résultat du query cache a été invalidé :-(


Pour Oracle :

SQLPLUS SCOTT/****

CREATE TABLE table_oracle_partitionned(ident NUMBER, quand date, valeur NUMBER)
PARTITION BY RANGE (quand)
INTERVAL (numtoyminterval(1,'YEAR'))
(
PARTITION p2006 VALUES LESS THAN (to_date('2007-01-01','yyyy-mm-dd'))
);

INSERT INTO table_oracle_partitionned VALUES(1,to_date('2008-06-04','yyyy-mm-dd'),20);
INSERT INTO table_oracle_partitionned VALUES(2,to_date('2007-05-04','yyyy-mm-dd'),35);
INSERT INTO table_oracle_partitionned VALUES(3,to_date('2008-07-04','yyyy-mm-dd'),3);
INSERT INTO table_oracle_partitionned VALUES(4,to_date('2009-10-03','yyyy-mm-dd'),11);
INSERT INTO table_oracle_partitionned VALUES(1,to_date('2009-12-25','yyyy-mm-dd'),46);
INSERT INTO table_oracle_partitionned VALUES(2,to_date('2008-10-12','yyyy-mm-dd'),43);
INSERT INTO table_oracle_partitionned VALUES(10,to_date('2006-05-03','yyyy-mm-dd'),32);
INSERT INTO table_oracle_partitionned VALUES(5,to_date('2007-06-12','yyyy-mm-dd'),56);
EXIT;

SQLPLUS / AS SYSDBA
SQL> EXECUTE dbms_result_cache.flush;
SQL> SELECT * FROM scott.table_oracle_partitionned WHERE quand BETWEEN to_date('2008-01-01','yyyy-mm-dd') AND to_date('2008-10-01','yyyy-mm-dd');

IDENT QUAND VALEUR
---------- -------- ----------
1 04/06/08 20
3 04/07/08 3

Si on lance plusieurs fois la requête et que l'on regarde les informations du result_cache :

SQL> select name, value from v$result_cache_statistics;

NAME VALUE
-------------------- ----------
Create Count Success 1
Create Count Failure 0
Find Count 4
Invalidation Count 0
Delete Count Invalid 0

On s'aperçoit que le résultat est bien récupéré à partir du result_cache.

J'effectue une mise à jour sur la table mais en prenant soin de ne pas intervenir sur les enregistrements de la partition contenant les données de l'année 2008 :

SQL> UPDATE scott.table_oracle_partitionned SET VALEUR=VALEUR+1 WHERE quand BETWEEN to_date('2007-01-01','yyyy-mm-dd') AND to_date('2007-12-30','yyyy-mm-dd');

2 rows updated.

Je relance la même requête pour récupérer les enregistrements de l'année 2008 et je regarde à nouveau les statistiques du result_cache :

SQL> SELECT name, value FROM v$result_cache_statistics;

NAME VALUE
-------------------- ----------
Create Count Success 2
Create Count Failure 0
Find Count 4
Invalidation Count 1
Delete Count Invalid 0

En conclusion :

Le Result Cache d'Oracle se comporte comme le Query Cache de MySQL dans le sens où chaque modification d'une table engendre l'invalidation des SELECTs concernés. J'espérais qu'Oracle aurait su prendre en compte le partitionnement de la table pour obtenir une invalidation plus fine ce qui dans l'exemple plus haut aurait eu pour conséquence de ne pas recréer un enregistrement dans le Result Cache.

ASUS F9S : Mise à jour de Gutsy vers Hardy

J'ai enfin franchi le pas et mis à jour mon ASUS. Après quelques péripéties auxquelles je m'attendaient celà fonctionne.
Voici en résumé les quelques modifications que j'ai effectuées :

- Pour continuer à écouter vos mp3 préférés, ajouter dans le fichier /etc/modprobe.d/alsa-base la ligne suiante :
options snd-hda-intel model=lenovo

- S'assurer que le fichier /etc/default/linux-restricted-modules-common contienne la ligne :
DISABLED_MODULES="nv nvidia"

- Installer les drivers propriétaires nvidia en les récupérant directement sur le site officiel :
cd /tmp
wget -P /tmp/ http://fr.download.nvidia.com/XFree86/Linux-x86/173.14.05/NVIDIA-Linux-x86-173.14.05-pkg1.run
sudo su -
/etc/init.d/gdm stop
chmod u+x /tmp/NVIDIA-Linux-x86-173.14.05-pkg1.run
cd /tmp
./NVIDIA-Linux-x86-173.14.05-pkg1.run

Le reste de l'installation devrait se passer sans souci. sauvegarder votre fichier /etc/X11/xorg.conf et lancer nvidia-xconfig pour regénérer la configuration nvidia pour xorg.

- Mon pauvre gdm étant passé en querty j'ai dû lancer :
sudo dpkg-reconfigure xserver-xorg

- Glipper crash de façon aléatoire
wget -P /tmp http://launchpadlibrarian.net/14965046/glipper_1.0-1ubuntu2%7Eppa1_i386.deb
gdebi-gtk /tmp/glipper_1.0-1ubuntu2~ppa1_i386.deb
sudo sed -i -e 's/time\.sleep([0-9]\+)/time\.sleep(20)/g' /usr/lib/glipper/glipper

- Firefox 3 est inutilisable. Constamment en train d'accéder à mon disque dur ce qui a pour effet de le figer qqs secondes et de m'énerver. Je suis donc repassé à Firefox 2 :
sudo apt-get install firefox-2
sudo dpkg-divert --divert /usr/bin/firefox.ubuntu --rename /usr/bin/firefox
sudo ln -s /usr/bin/firefox-2 /usr/bin/firefox

- Google Desktop Linux ne fonctionne plus depuis la mise à jour :
Se rendre dans System/Preferences/Applications Preferees et modifier le naviguateur web utilisé pour pointer vers firefox (le chemin est incorrect)

- Audacity retourne une erreur du genre AlsaOpen( hostApi, parameters, streamDir, &pcm )' failed in 'src/hostapi/alsa/pa_linux_alsa.c
sudo apt-get remove jackd

- Vmplayer ne fonctionne plus car hardy ne dispose pas des paquets nécessaires (modules noyaux, etc...) :
Télécharger la dernière version sur http://www.vmware.com/download/player/download.html
sudo apt-get remove vmware-player
sudo apt-get install linux-headers-2.6.24-19-generic build-essential
bien sûr vous disposer du gcc installé ou faudra le faire
sudo su -
cd /opt
tar xvzf /tmp/VMware-player-2.0.4-93057.i386.tar.gz
cd vmware-player-distrib
./vmware-install.pl
il suffit ensuite de répondre aux questions et de lancer le player une fois le tout terminé

- Le Wifi s'interrompt régulièrement et impossible de passer en veille correctement (ça semble être corrélé) :
sudo apt-get install linux-backports-modules-hardy-generic
echo "options iwl3945 disable_hw_scan=1" > /etc/modprobe.d/iwl3945

- Une fenêtre active est stickée sur l'espace de travail sur lequel elle a été ouverte. Assez génant sur zim, pidgin et certainement plein d'autres applications :
Un BUG a été identifié relatif à Metacity. Donc en attendant que ce soit corrigé, mieux vaut ne rien faire. Je ne me vois pas du tout me mettre à patcher toutes les applications que j'utilise en attendant que Metacity soit modifié.

Voilà ce sont les quelques modifications que j'ai dû effectuer ce week end pour pouvoir continuer à utiliser mon portable.