L'un des problèmes récurrents des architectures mutualisés (ou non) utilisant du MySQL est la disponibilité mais aussi la performance de ce dernier. Quelques habitudes existent, chacun avec leurs lots d'avantages et inconvénients. Je vais tenter de vous proposer une mise en oeuvre qui me semble être un bon compromis entre les différents points à prendre en compte : haute disponibilité, performance, type de table MySQL géré, compétences en interne, coût, facilité de déploiement.

MySQL en haute disponibilité

MySQL, beaucoup le critique à tord ou à raison. Le premier à le faire ? Michael Widenius, fondateur de MySQL qui s'affirme avec MariaDB. De manière générale, on notera certaines fonctionnalités (voire performances) en retrait par rapport à d'autres moteurs, que cela soit PostgreSQL, Oracle ou MSSQL. Reste que ces derniers sont cher à mettre en oeuvre et que PostgreSQL nécessite une compétence qui est bien trop rare dans nos contrées. De fait, MySQL reste quelque part une valeur sûre si l'on prend le temps de travailler correctement avec : définir une bonne architecture, le superviser et l'optimiser au fil du temps.

Quand on parle de haute disponibilité, je balaie d'office le fonctionnement basique maître-esclave disponible nativement. En effet, il n'apporte aucune haute disponibilité et le bricolage nécessaire pour inverser les rôles n'est tout bonnement pas industrialisable et encore moins réaliste. Que reste-t-il alors comme solution ?

• MySQL Cluster
  • solution opensource mais un support de la part d'Oracle est conseillé
  • avantage(s) : supporté par l'éditeur (Oracle) ; performance
  • inconvénient(s) : un seul type de moteur de table (NDB)
• Percona XtraDB
  • solution opensource qui dispose d'un support mais où l'on peut se débrouiller ; il s'agit d'une version lourdement mise à jour de MySQL (utilise la librairie Galera)
  • avantage(s) : support de l'éditeur (Percona) ; extension du moteur InnoDB (renommé XtraDB) ; synchro temps réel
  • inconvénients : principalement au niveau des LOCK (mais il y a une solution embarquée pour cela) ; que InnoDB pour le moment ; les statements DDL sont encore problématiques (mais en cours de correction)
• Multi Master Replication
  • solution opensource qui repose sur un abus d'utilisation d'une fonction de mysql (une instance peut être à la fois maître et esclave)
  • avantage(s) : natif à MySQL ; tous les formats de table de MySQL (MyISAM, InnoDB, ...)
  • inconvénients : peu stable sur le long terme - lié au fonctionnement en boucle, nécessite du bricolage et l'implémentation d'outils tiers (par ex, MMM)

InnoDB étant plutôt conseillé et apprécié par rapport à MyISAM, j'ai choisi la solution de Perconna. En effet, elle me semble sur le papier très industrialisable avec des inconvénients que l'on peut facilement contourner ou qui vont venir à disparaître.

Percona XtraDB Cluster en pratique

L'environnement utilisé est un environnement de type Prod + PRA. Le principe s'applique facilement dans les autres environnements (Prod + PCA, Prod seule, ...). Il faut juste noter un impératif de PXC : il faut minimum trois noeuds.

L'intérêt dans l'architecture est multiple :

• tous les noeuds communiquent ensemble
• on voudra séparer les flux "lecture/écriture" des flux "lecture seule"

Dans ce mode, on va dispatcher des VIP par site :

• PRDVIPRO - lecture seule PRD : 192.168.2.5
• PRDVIPRW - lecture/écriture PRD : 192.168.2.6
• PRAVIPRO - lecture seule PRA : 192.168.12.5
• PRAVIPRW - lecture/écriture PRA : 192.168.12.6

Ainsi, on va isoler les utilisateurs sur les différentes VIP. On pourra définir des super-alias qui pointent sur un site ou l'autre en fonction des besoins et disponibilités. Pour répondre à ces VIP, on supposera les machines suivantes :

• PRDMYSQL01 : 192.168.1.1/24 (management) + 192.168.2.1/24 (métier)
• PRDMYSQL02 : 192.168.1.2/24 (management) + 192.168.2.1/24 (métier)
• PRAMYSQL01 : 192.168.11.1/24 (management) + 192.168.12.1/24 (métier)
• PRAMYSQL02 : 192.168.11.2/24 (management) + 192.168.12.2/24 (métier)

La séparation des droits (RO/RW) ne se fera pas au niveau des rôles des noeuds (côté MySQL) mais au niveau de l'assignation des VIP et définitions des comptes utilisateurs. Il faut donc être attentif de ce côté.

Installation de Percona XtraDB Cluster

On commence par déployer tous les packages nécessaires

gpg --keyserver  hkp://keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
gpg -a --export CD2EFD2A | sudo apt-key add -
echo << EOF >> /etc/apt/sources.list
deb http://repo.percona.com/apt squeeze main
deb-src http://repo.percona.com/apt squeeze main
EOF
apt-get update
apt-get install libnet-daemon-perl libplrpc-perl libdbi-perl libaio1 libmysqlclient18 percona-xtradb-cluster-server-5.5 percona-xtradb-cluster-client-5.5 percona-xtradb-cluster-common-5.5 percona-xtrabackup xtrabackup netcat-openbsd rsync

On continue par la configuration du mysql. La configuration est une base pour un serveur avec 8 Go Ram et 4 coeurs. A faire évoluer donc.

echo << EOF > /etc/mysql/my.cnf
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
server_id=1
wsrep_node_name=prdmysql01
auto_increment_offset = 4
auto_increment_increment = 4
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.2.2,192.168.12.1,192.168.12.2
#wsrep_cluster_address=gcomm://
wsrep_slave_threads=16
wsrep_sst_method=xtrabackup
wsrep_cluster_name=pxc
wsrep_sst_auth=root:secret
wsrep_sst_receive_address=192.168.2.1:4444
wsrep_provider_options ="gmcast.listen_addr=tcp://192.168.2.1:4567; ist.recv_addr=192.168.2.1:4568;"
binlog_format=ROW
log_bin=mysql-bin
log_slave_updates
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_buffer_pool_size=400M
innodb_log_file_size=64M
innodb_data_file_path = ibdata1:10M:autoextend:max:128M
performance_schema
key_buffer = 1M
max_allowed_packet = 4M
table_cache = 64K
query_cache_limit = 64M
sort_buffer_size = 2M
net_buffer_length = 64K
read_buffer_size = 4M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
low_priority_updates = 1
old_passwords = 0
max_connections = 200
max_user_connections = 100
join_buffer_size = 256K
long_query_time = 2
slow-query_log_file = /var/log/mysql/mysql.slow.log
thread_cache_size = 5
query_cache_size = 0
query_cache_type = 1
tmp_table_size = 128M
max_heap_table_size = 128M
concurrent_insert = 2
delay_key_write = all
wait_timeout = 30
interactive_timeout = 30
key_buffer_size = 2G 
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
bind-address = 0.0.0.0
myisam-recover = BACKUP
expire_logs_days = 10
max_binlog_size = 100M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
EOF
chmod 600 /etc/mysql/my.cnf

Les lignes en gras sont à adapter pour chaque serveur :

• un server-id unique à chaque fois
• l'IP métier du serveur abstente du cluster-adress
• le mot de passe root mysql

ATTENTION : ne pas utiliser le même mot de passe root en mysql et sur le système

Une fois le premier serveur mis en place, on arrête le mysql (via service ou init) sur TOUS les serveurs. Ensuite, on duplique le fichier /etc/mysql/debian.cnf et tout le contenu de /var/lib/mysql du premier vers les trois autres.

Une petite subtilité existe sur la version Debian/Ubuntu du package Percona. Au démarrage du premier node d'un cluster (au sens que tout le cluster est arrêté), il faut éditer le fichier my.cnf pour commenter la ligne

wsrep_cluster_address=gcomm://IP,IP,IP

et décommenter la ligne

wsrep_cluster_address=gcomm://

Ensuite, on peut démarrer simplement le service sur la machine puis remodifier le fichier my.cnf. C'est rébarbatif, mais il faut le savoir. Après, on n'aura à le refaire qu'en cas de relance complète depuis zéro du cluster.

Ensuite, on va déployer quelques modifications sur les droits MySQL :

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.2.%' IDENTIFIED BY 'secret';
mysql> CREATE USER 'perconha'@'localhost' IDENTIFIED BY 'secret';
mysql> FLUSH PRIVILEGES;

Ceci permettra aux différents nodes de communiquer pour la synchro et de disposer d'un user perconha qui n'a accès qu'en consultation aux variables applicatives de MySQL.

Nous n'avons plus qu'à démarrer les différents noeuds MySQL. Sur les versions Debian/Ubuntu, le premier démarrage pourra se faire en erreur. En effet, le script d'init considère que le serveur ne démarre pas car le binaire lui renvoie une erreur. En fait d'erreur, il s'agit juste d'un retour qui signale que le serveur est désynchro. On lui laisse le temps de bien démarrer (on peut consulter syslog pour cela) puis, à volonté, redémarrer le service une dernière fois.

Le cluster est maintenant installé.

VIP & Monitoring

Pour répondre au besoin de la gestion d'une VIP sans outil tiers ou de load balancer, ainsi qu'au monitoring complémentaire de PXC, j'ai mis à disposition des scripts sur mon github.

Le script pour Nagios est relativement simple à utiliser :

usage: ./check_percona -H $HOSTADDRESS$ -p $PORT$ -w $ARG1$ -c $ARG2$ -t $ARG3$ \\(-U $ARG4$ \\(-P $ARG5$\\)\\)
-H hostname or IP
-p service port
-w warning in seconds for replication delay
-c critical in seconds for replication delay
-t timeout for command input
-U user if needed
-P password if needed

Il ne nécessite que la création d'un compte (comme le compte perconha) pour permettre à Nagios de s'y connecter. Attention, il vient en complément du check_mysql disponible de base.

Concernant la VIP, le script va vérifier l'état d'un noeud dans le cluster (mysql disponible, intégré au cluster, synchro avec le cluster) pour assigner les VIP à la machine. Les VIP sont séparés niveau logique entre une pour l'écriture et l'autre pour la lecture. Pour définir le "rôle" d'un noeud, il suffit de créer des fichiers vide "RO" (lecture seule) ou "RW" (lecture/écriture) dans le dossier /etc/perconHa/. une fois placé en cron, le script fera le reste.

Il vérifie bien évidemment qu'une IP n'est pas déjà utilisée avant de l'assigner. Notez qu'il faut éditer le fichier pour y définir les informations relatives à la connexion à MySQL, les VIP ainsi que l'interface métier.

Conclusion

On a donc pu créer rapidement un cluster MySQL autonome, avec une haute disponibilité, une performance certaine mais liée au fine tuning du MySQL (qui se fait tout au long de la vie du cluster) et souple. La base présentée est simple, viable mais reste une base. La partie VIP par exemple serait mieux gérer via un load balancer qui serait plus réactif que la cron. Mais on peut réutiliser le script pour se faire. De la même manière, on pourrait superviser de manière plus fine la consommation en ressource du cluster.