Création d'un cluster MySQL haute disponibilité

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, 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.

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é.

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=1wsrep_node_name=prdmysql01 auto_increment_offset = 4 auto_increment_increment = 4 wsrep_provider=/usr/lib64/libgalera_smm.sowsrep_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=pxcwsrep_sst_auth=root:secretwsrep_sst_receive_address=192.168.2.1:4444wsrep_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é.

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.

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.