Sauvegarde Sonarqube MySQL

Cet article présente le principe de sauvegarde de la base de données MySQL pour Sonarqube à l'aide de l'outil AutoMySQLBackup.

Votre avis

Nobody voted on this yet  You need to enable JavaScript to vote

Tweet

Mise en place

Les scripts de sauvegarde et de configuration sont stockés dans le répertoire /var/opt/backups/sonar/.

#sudo mkdir -p /var/opt/backups/sonar/db
#sudo chown root:backup /var/opt/backups/sonar/db
#sudo chmod 770 /var/opt/backups/sonar
#sudo chmod 770 /var/opt/backups/sonar/db

Scripts

Le script mis en place permet d'exécuter les outils de sauvegarde d'une base de données MySQL en spécifiant l'emplacement du fichier de configuration, à l'emplacement /var/opt/backups/sonar/sonar_automysqlbackup.conf en redirigeant les messages d'exécution dans le fichier /var/log/backups/sonar_backup_db.log.

#!/bin/bash
/var/opt/automysqlbackup/automysqlbackup /var/opt/backups/sonar/sonar_automysqlbackup.conf >> /var/log/backups/sonar_backup_db.log

L'exécution du script sonar_backup.sh est configuré dans les options de l'outil AutoMySQLBackup.

Configuration

La configuration de l'outil de sauvegarde est mis en place dans le fichier /var/opt/backups/sonar/sonar_automysqlbackup.conf.

#version=3.0_rc2
# DONT'T REMOVE THE PREVIOUS VERSION LINE!
#
# Uncomment to change the default values (shown after =)
# WARNING:
# This is not true for UMASK, CONFIG_prebackup and CONFIG_postbackup!!!
#
# Default values are stored in the script itself. Declarations in
# /etc/automysqlbackup/automysqlbackup.conf will overwrite them. The
# declarations in here will supersede all other.

# Edit $PATH if mysql and mysqldump are not located in /usr/local/bin:/usr/bin:/bin:/usr/local/mysql/bin
#PATH=${PATH}:FULL_PATH_TO_YOUR_DIR_CONTAINING_MYSQL:FULL_PATH_TO_YOUR_DIR_CONTAINING_MYSQLDUMP

# Basic Settings

# Username to access the MySQL server e.g. dbuser
CONFIG_mysql_dump_username='backup'

# Password to access the MySQL server e.g. password
CONFIG_mysql_dump_password='<BACKUP_PASSWORD>'

# Host name (or IP address) of MySQL server e.g localhost
CONFIG_mysql_dump_host='localhost'

# "Friendly" host name of MySQL server to be used in email log
# if unset or empty (default) will use CONFIG_mysql_dump_host instead
#CONFIG_mysql_dump_host_friendly=''

# Backup directory location e.g /backups
CONFIG_backup_dir='/var/opt/backups/sonar/db'

# This is practically a moot point, since there is a fallback to the compression
# functions without multicore support in the case that the multicore versions aren't
# present in the system. Of course, if you have the latter installed, but don't want
# to use them, just choose no here.
# pigz -> gzip
# pbzip2 -> bzip2
CONFIG_multicore='yes'

# Number of threads (= occupied cores) you want to use. You should - for the sake
# of the stability of your system - not choose more than (#number of cores - 1).
# Especially if the script is run in background by cron and the rest of your system
# has already heavy load, setting this too high, might crash your system. Assuming
# all systems have at least some sort of HyperThreading, the default is 2 threads.
# If you wish to let pigz and pbzip2 autodetect or use their standards, set it to
# 'auto'.
CONFIG_multicore_threads=2

# Databases to backup

# List of databases for Daily/Weekly Backup e.g. ( 'DB1' 'DB2' 'DB3' ... )
# set to (), i.e. empty, if you want to backup all databases
CONFIG_db_names=( 'sonar' )
# You can use
#declare -a MDBNAMES=( "${DBNAMES[@]}" 'added entry1' 'added entry2' ... )
# INSTEAD to copy the contents of $DBNAMES and add further entries (optional).

# List of databases for Monthly Backups.
# set to (), i.e. empty, if you want to backup all databases
CONFIG_db_month_names=( 'sonar' )

# List of DBNAMES to EXLUCDE if DBNAMES is empty, i.e. ().
#CONFIG_db_exclude=( 'information_schema' )

# List of tables to exclude, in the form db_name.table_name
# You may use wildcards for the table names, i.e. 'mydb.a*' selects all tables starting with an 'a'.
# However we only offer the wildcard '*', matching everything that could appear, which translates to the
# '%' wildcard in mysql.
#CONFIG_table_exclude=()


# Advanced Settings

# Rotation Settings

# Which day do you want monthly backups? (01 to 31)
# If the chosen day is greater than the last day of the month, it will be done
# on the last day of the month.
# Set to 0 to disable monthly backups.
CONFIG_do_monthly="0"

# Which day do you want weekly backups? (1 to 7 where 1 is Monday)
# Set to 0 to disable weekly backups.
CONFIG_do_weekly="0"

# Set rotation of daily backups. VALUE*24hours
# If you want to keep only today's backups, you could choose 1, i.e. everything older than 24hours will be removed.
CONFIG_rotation_daily=1

# Set rotation for weekly backups. VALUE*24hours
#CONFIG_rotation_weekly=35

# Set rotation for monthly backups. VALUE*24hours
#CONFIG_rotation_monthly=150


# Server Connection Settings

# Set the port for the mysql connection
CONFIG_mysql_dump_port=3306

# Compress communications between backup server and MySQL server?
#CONFIG_mysql_dump_commcomp='no'

# Use ssl encryption with mysqldump?
#CONFIG_mysql_dump_usessl='yes'
CONFIG_mysql_dump_usessl='no'

# For connections to localhost. Sometimes the Unix socket file must be specified.
#CONFIG_mysql_dump_socket=''

# The maximum size of the buffer for client/server communication. e.g. 16MB (maximum is 1GB)
#CONFIG_mysql_dump_max_allowed_packet=''

# This option sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with
# transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time
# when BEGIN was issued without blocking any applications.
#
# When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For
# example, any MyISAM or MEMORY tables dumped while using this option may still change state.
#
# While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and
# binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE,
# DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of
# them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table
# contents to obtain incorrect contents or fail.
#CONFIG_mysql_dump_single_transaction='no'

# http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_master-data
# --master-data[=value]
# Use this option to dump a master replication server to produce a dump file that can be used to set up another
# server as a slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates
# the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates
# from which the slave should start replicating after you load the dump file into the slave.
#
# If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only;
# it has no effect when the dump file is reloaded. If the option value is 1, the statement is not written as a comment
# and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1.
#
# This option requires the RELOAD privilege and the binary log must be enabled.
#
# The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless
# --single-transaction also is specified, in which case, a global read lock is acquired only for a short time at the
# beginning of the dump (see the description for --single-transaction). In all cases, any action on logs happens at
# the exact moment of the dump.
# ==================================================================================================================
# possible values are 1 and 2, which correspond with the values from mysqldump
# VARIABLE=    , i.e. no value, turns it off (default)
#
#CONFIG_mysql_dump_master_data=

# Included stored routines (procedures and functions) for the dumped databases in the output. Use of this option
# requires the SELECT privilege for the mysql.proc table. The output generated by using --routines contains
# CREATE PROCEDURE and CREATE FUNCTION statements to re-create the routines. However, these statements do not
# include attributes such as the routine creation and modification timestamps. This means that when the routines
# are reloaded, they will be created with the timestamps equal to the reload time.
#
# If you require routines to be re-created with their original timestamp attributes, do not use --routines. Instead,
# dump and reload the contents of the mysql.proc table directly, using a MySQL account that has appropriate privileges
# for the mysql database.
#
# This option was added in MySQL 5.0.13. Before that, stored routines are not dumped. Routine DEFINER values are not
# dumped until MySQL 5.0.20. This means that before 5.0.20, when routines are reloaded, they will be created with the
# definer set to the reloading user. If you require routines to be re-created with their original definer, dump and
# load the contents of the mysql.proc table directly as described earlier.
#
CONFIG_mysql_dump_full_schema='no'

# Backup status of table(s) in textfile. This is very helpful when restoring backups, since it gives an idea, what changed
# in the meantime.
CONFIG_mysql_dump_dbstatus='yes'

# Backup dump settings

# Include CREATE DATABASE in backup?
CONFIG_mysql_dump_create_database='yes'

# Separate backup directory and file for each DB? (yes or no)
CONFIG_mysql_dump_use_separate_dirs='yes'

# Choose Compression type. (gzip or bzip2)
CONFIG_mysql_dump_compression='gzip'

# Store an additional copy of the latest backup to a standard
# location so it can be downloaded by third party scripts.
#CONFIG_mysql_dump_latest='no'

# Remove all date and time information from the filenames in the latest folder.
# Runs, if activated, once after the backups are completed. Practically it just finds all files in the latest folder
# and removes the date and time information from the filenames (if present).
#CONFIG_mysql_dump_latest_clean_filenames='no'

# Create differential backups. Master backups are created weekly at #$CONFIG_do_weekly weekday. Between master backups,
# diff is used to create differential backups relative to the latest master backup. In the Manifest file, you find the
# following structure
# $filename     md5sum  $md5sum diff_id $diff_id        rel_id  $rel_id
# where each field is separated by the tabular character '\t'. The entries with $ at the beginning mean the actual values,
# while the others are just for readability. The diff_id is the id of the differential or master backup which is also in
# the filename after the last _ and before the suffixes begin, i.e. .diff, .sql and extensions. It is used to relate
# differential backups to master backups. The master backups have 0 as $rel_id and are thereby identifiable. Differential
# backups have the id of the corresponding master backup as $rel_id.
#
# To ensure that master backups are kept long enough, the value of $CONFIG_rotation_daily is set to a minimum of 21 days.
#
CONFIG_mysql_dump_differential='no'


# Notification setup

# What would you like to be mailed to you?
# - log   : send only log file
# - files : send log file and sql files as attachments (see docs)
# - stdout : will simply output the log to the screen if run manually.
# - quiet : Only send logs if an error occurs to the MAILADDR.
#CONFIG_mailcontent='stdout'

# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])
#CONFIG_mail_maxattsize=4000

# Allow packing of files with tar and splitting it in pieces of CONFIG_mail_maxattsize.
#CONFIG_mail_splitandtar='yes'

# Use uuencode instead of mutt. WARNING: Not all email clients work well with uuencoded attachments.
#CONFIG_mail_use_uuencoded_attachments='no'

# Email Address to send mail to? (user@domain.com)
#CONFIG_mail_address='root'


# Encryption

# Do you wish to encrypt your backups using openssl?
CONFIG_encrypt='no'

# Choose a password to encrypt the backups.
#CONFIG_encrypt_password='password0123'

# Other

# Backup local files, i.e. maybe you would like to backup your my.cnf (mysql server configuration), etc.
# These files will be tar'ed, depending on your compression option CONFIG_mysql_dump_compression compressed and
# depending on the option CONFIG_encrypt encrypted.
#
# Note: This could also have been accomplished with CONFIG_prebackup or CONFIG_postbackup.
#CONFIG_backup_local_files=()

# Command to run before backups (uncomment to use)
#CONFIG_prebackup="/etc/mysql-backup-pre"

# Command run after backups (uncomment to use)
CONFIG_postbackup="/var/opt/backups/sonar/sonar_backup.sh"

# Uncomment to activate! This will give folders rwx------
# and files rw------- permissions.
umask 0077

# dry-run, i.e. show what you are gonna do without actually doing it
# inactive: =0 or commented out
# active: uncommented AND =1
#CONFIG_dryrun=1

Les éléments de configuration sont les suivants.

Paramètre	Valeur
CONFIG_mysql_dump_username	backup
CONFIG_mysql_dump_password	Mot de passe du compte backup.
CONFIG_mysql_dump_host	localhost L'utilitaire est installé sur la même machine que MySQL.
CONFIG_backup_dir	/var/opt/backups/sonar/db Ce répertoire est à créer.
CONFIG_multicore	yes
CONFIG_multicore_threads	2 La machine est un bi-processeur avec deux coeurs.
CONFIG_db_names	( 'sonar' ) Seule la base sonar est gérée dans cette configuration. La base mysql est sauvegardée pour conserver les informations sur les schémas. Ceci sera effectué pour toutes les sauvegardes reposant sur le serveur MySQL.
CONFIG_do_monthly	0 Les sauvegardes mensuelles ne sont pas souhaitées dans le cadre de cette utilisation.
CONFIG_do_weekly	0 Les sauvegardes hebdomadaires ne sont pas souhaitées dans le cadre de cette utilisation.
CONFIG_rotation_daily	1 Seule la sauvegarde du jour est conservée. En effet, l'installation faite n'est pas critique et comme les différentiels peuvent être gérés par Duplicity, il sera possible de restaurer des anciennes sauvegardes.
CONFIG_mysql_dump_port	3306
CONFIG_mysql_dump_usessl	no Par défaut la valeur est yes, mais cela entraîne une erreur suite à une mise à jour.
CONFIG_mysql_dump_full_schema	no
CONFIG_mysql_dump_dbstatus	yes
CONFIG_mysql_dump_create_database	yes Ce qui permet d'avoir les scripts de création de la abse.
CONFIG_mysql_dump_use_separate_dirs	yes
CONFIG_mysql_dump_compression	gzip Les sauvegardes seront compressées ainsi.
CONFIG_mysql_dump_differential	no Les sauvegardes sont complêtes à chaque fois.
CONFIG_postbackup	/var/opt/backups/sonar/sonar_backup.sh Ce script sera déclenché à la fin de la sauvegarde, permettant de planifier la synchronisation à partir de Duplicity.
umask	0077 Seul le compte root aura accès aux sauvegardes.

Droits d'accès

Les droits mis en place permettent de modifier et exécuter le script pour le propriétaire. Le groupe ne doit pas pouvoir modifier les fichiers

#sudo chmod 750 /var/opt/backups/sonar/sonar_automysqlbackup.sh
#sudo chmod 640 /var/opt/backups/sonar/sonar_automysqlbackup.conf

Update

Suite à la montée de version de Ubuntu 16.04, le serveur MySql a été mis à jour. La configuration de AutoMySQLBackup mise en place provoquait alors une erreur, mentionnée dans la trace d'exécution.

mysqldump: Got error: 2026: SSL connection error: SSL is required but the server doesn't support it when trying to connect

Et la base de données n'était pas exportée par la commande mysqldump.

Par défaut, AutoMySQLBackup est configuré pour exécuter la commande mysqldump en SSL, paramètre CONFIG_mysql_dump_usessl. Jusqu'à cette mise à jour, cela ne semblait pas être problématique. Dans le cadre de cette installation, il est donc nécessaire de désactiver l'utilisation de SSL en spécifiant la valeur no pour le paramètre.

# Use ssl encryption with mysqldump?
#CONFIG_mysql_dump_usessl='yes'
CONFIG_mysql_dump_usessl='no'