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
