Christophe Ti

(Christophe TREMBLAY-GUILLOUX)

Ingénieur système Linux – TJM: 630 € HT

L’impression d’être entouré ou conseillé par des soi-disant « Expert Linux » (avant qu’ils ne disparaissent du radar) ?

Christophe est l’ULTIME expert en système Linux, hébergement web et développement Puppet que vous garderez dans vos contacts… à vie.

MySQL : ERREUR de sauvegarde non visible (avant catastrophe)

Les conseils privés de Christophe Ti

(réservé aux personnes intéressées)

Quelles sont les mystérieuses techniques que j’utilise pour mettre en œuvre un serveur Linux Debian (ou hébergement web), fiable, performant et sécurisé, sans devoir monter la garde jour et nuit ?
(pour le savoir, inscris-toi dans le formulaire ci-contre)

J’ai reçu cet e-mail d’alerte récemment :

				
					mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `wp_af6t2p_postmeta` at row: 42646
				
			

Cette erreur se produisait pendant la sauvegarde automatique de la base de données.

Il a donc fallu que je recherche l’origine.

Voici comment j’ai procédé :

1. Consultation des logs de MariaDB

Dans les logs système de MySQL/MariaDB (/var/log/mysql/error.log), on peut lire aussi :

				
					2023-02-26  2:00:41 692930 [Warning] Aborted connection 692930 to db: 'worprod' user: 'root' host: 'localhost' (Got an error writing communication packets)
				
			

Je décide alors de tester la commande de sauvegarde en ligne de commande.

2. Test de sauvegarde

Il faut remplacer le nom de la base de données (worprod) par la tienne :

				
					mysqldump --opt --flush-logs --single-transaction --ignore-table=mysql.event --skip-triggers --skip-routines worprod > test.sql
				
			

J’obtiens de nouveau l’erreur :

				
					mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `wp_af6t2p_postmeta` at row: 42146
				
			

Quand on cherche sur Internet l’erreur, on obtient plusieurs explications donc une qui parle de la variable max_allowed_packet à augmenter.. Je teste de nouveau en augmentant progressivement cette variable jusque 128M :

				
					mysqldump --opt --flush-logs --single-transaction --ignore-table=mysql.event --skip-triggers --skip-routines --max_allowed_packet=128M worprod > test.sql
				
			

La sauvegarde fonctionne.

Donc maintenant, je vais appliquer la modification de ma configuration Puppet.

3. Correction de ma classe mariadb pour Puppet

J’utilise Puppet pour configurer mes serveurs.

Je change ma classe pour ajouter le paramètre permettant de configurer cette valeur max_allowed_packet :

				
					class webconfig::mariadb (
    String $root_password,
    Hash $override_options,
    String $mysqltuner_version,
    String $maxallowedpacket,
    Integer $service_limitnofile = 32768
) {
    # Installation serveur mariadb
    class {'::mysql::server':
        package_name            => 'mariadb-server',
        root_password           => $root_password,
        remove_default_accounts => true,
        override_options        => $override_options,
        restart                 => true,
    }

    # Limit
    systemd::service_limits { 'mariadb.service':
        limits => {
            'LimitNOFILE' => $service_limitnofile
        }
    }

    # Ajout d'un compte admin (accès root à mysql) pour l'accès via phpmyadmin
    mysql_user { 'admin@localhost':
        ensure        => present,
        password_hash => mysql_password($root_password),
    }

    mysql_grant { 'admin@localhost/*.*':
        ensure     => 'present',
        options    => ['GRANT'],
        privileges => ['ALL'],
        table      => '*.*',
        user       => 'admin@localhost',
    }

    # myssqltuner
    archive { '/usr/local/bin/mysqltuner':
        ensure  => present,
        extract => false,
        source  => "https://github.com/major/MySQLTuner-perl/raw/${mysqltuner_version}/mysqltuner.pl",
        creates => '/usr/local/bin/mysqltuner',
        cleanup => false,
    }
    file { '/usr/local/bin/mysqltuner':
        ensure  => present,
        owner   => 'root',
        group   => 'root',
        mode    => '0755',
        require => Archive['/usr/local/bin/mysqltuner'];
    }

    # BACKUP
    class {'::mysql::server::backup':
        backupdir         => '/var/backups/mysql',
        backupuser        => 'backup',
        backuppassword    => $root_password,
        backupdirmode     => '0750',
        backupdirowner    => 'root',
        backupdirgroup    => 'root',
        backuprotate      => '1',
        file_per_database => true,
        time              => ['2','0'],
        maxallowedpacket  => $maxallowedpacket,
    }
}
				
			

Et dans la configuration hiera pour ce host, j’ajoute :

				
					webconfig::mariadb::maxallowedpacket: '128M'
				
			

Puis après une mise à jour de mon GIT PUPPET, la valeur est modifiée dans mon script de sauvegarde.

4. Script de sauvegarde SQL

Voici le script de sauvegarde à adapter pour ton besoin :

				
					#!/bin/bash
#
# MySQL Backup Script
#  Dumps mysql databases to a file for another backup tool to pick up.
#
# MySQL code:
# GRANT SELECT, RELOAD, LOCK TABLES ON *.* TO 'user'@'localhost'
# IDENTIFIED BY 'password';
# FLUSH PRIVILEGES;
#
##### START CONFIG ###################################################

USER=backup
PASS='xxxxxxxxxxx'
MAX_ALLOWED_PACKET=128M
DIR=/var/backups/mysql
ROTATE=0

# Create temporary mysql cnf file.
TMPFILE=`mktemp /tmp/backup.XXXXXX` || exit 1
echo -e "[client]\npassword=$PASS\nuser=$USER\nmax_allowed_packet=$MAX_ALLOWED_PACKET" > $TMPFILE


# Ensure backup directory exist.
mkdir -p $DIR

PREFIX=mysql_backup_

ADDITIONAL_OPTIONS="--ignore-table=mysql.event"
ADDITIONAL_OPTIONS="$ADDITIONAL_OPTIONS --skip-triggers"
ADDITIONAL_OPTIONS="$ADDITIONAL_OPTIONS --skip-routines"

##### STOP CONFIG ####################################################
PATH=/usr/bin:/usr/sbin:/bin:/sbin

set -o pipefail

cleanup()
{
	find "${DIR}/" -maxdepth 1 -type f -name "${PREFIX}*.sql*" -mtime +${ROTATE} -print0 | xargs -0 -r rm -f
}

mysql --defaults-extra-file=$TMPFILE -s -r -N -e 'SHOW DATABASES' | while read dbname
do
  mysqldump --defaults-extra-file=$TMPFILE --opt --flush-logs --single-transaction \
    ${ADDITIONAL_OPTIONS} \
    ${dbname} | bzcat -zc > ${DIR}/${PREFIX}${dbname}_`date +%Y%m%d-%H%M%S`.sql.bz2
done

if [ $? -eq 0 ] ; then
    cleanup
    touch /tmp/mysqlbackup_success
fi


# Remove temporary file
rm -f $TMPFILE
				
			

Quelle est la mystérieuse ingénierie qui me permet d'obtenir un serveur Linux fiable et performant, sans devoir monter la garde jour et nuit ?