Differences

This shows you the differences between two versions of the page.

--- linux:mysql_bkp [2021/01/01 21:25]
127.0.0.1 external edit
+++ linux:mysql_bkp [2022/01/14 14:43] (current)
manu
@@ Line 1: / Line 1: @@
 ====== MySQL backup ======
+===== script dump =====
+==== script 1 ====
+<code>
+#!/bin/bash
+# Mettre un date au nom du fichier
+DATE=$(date +"%Y%m%d")
+# Dossier de sauvegarde
+BACKUP_DIR="/backup/mysqldump"
+# MySQL username/password
+MYSQL_USER="root"
+MYSQL_PASSWORD="YOURSECUREPASSWORD"
+# Commandes MySQL nécessaires pour faire le backup
+MYSQL=/usr/bin/mysql
+MYSQLDUMP=/usr/bin/mysqldump
+# Bases de données MySQL que vous voulez ignorer
+SKIPDATABASES="information_schema|performance_schema|mysql|database1|database2"
+# Rétention des données (les jours pour garder les backups)
+RETENTION=5
+# Création d'un nouveau dossier avec la date du jour
+mkdir -p $BACKUP_DIR/$DATE
+# Donne la liste de toutes les bases des données.
+databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "($SKIPDATABASES)"`
+# Copier les bases des données distinctes et fait un fichier gzip pour chaque database.
+for db in $databases; do
+echo $db
+$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --skip-lock-tables --events --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"
+done
+# Efface les fichiers plus vieux que votre date de RETENTION
+find $BACKUP_DIR/* -mtime +$RETENTION -delete
+</code>
+==== script 2 ====
+<code>
+#!/bin/sh
+#----------------------------------------------------------
+# a simple mysql database backup script.
+# version 2, updated March 26, 2011.
+# copyright 2011 alvin alexander, http://alvinalexander.com
+#----------------------------------------------------------
+# This work is licensed under a Creative Commons
+# Attribution-ShareAlike 3.0 Unported License;
+# see http://creativecommons.org/licenses/by-sa/3.0/
+# for more information.
+#----------------------------------------------------------
+# (1) set up all the mysqldump variables
+FILE=minime.sql.`date +"%Y%m%d"`
+DBSERVER=127.0.0.1
+DATABASE=XXX
+USER=XXX
+PASS=XXX
+# (2) in case you run this more than once a day, remove the previous version of the file
+unalias rm     2> /dev/null
+rm ${FILE}     2> /dev/null
+rm ${FILE}.gz  2> /dev/null
+# (3) do the mysql database backup (dump)
+# use this command for a database server on a separate host:
+#mysqldump --opt --protocol=TCP --user=${USER} --password=${PASS} --host=${DBSERVER} ${DATABASE} > ${FILE}
+# use this command for a database server on localhost. add other options if need be.
+mysqldump --opt --user=${USER} --password=${PASS} ${DATABASE} > ${FILE}
+# (4) gzip the mysql database dump file
+gzip $FILE
+# (5) show the user the result
+echo "${FILE}.gz was created:"
+ls -l ${FILE}.gz
+</code>
 ===== Backup using LVM snapshot =====

wiki

User Tools

Site Tools

Differences

Page Tools