Differences

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

--- linux_faq:backup_all_mysql_databases_separate_files_to_ftp [2019/04/11 15:53] – admin
+++ linux_faq:backup_all_mysql_databases_separate_files_to_ftp [2019/04/12 07:23] (current) – admin
@@ Line 1: / Line 1: @@
+====== Скрипт бекапа баз mysql ======
+Скрипт выполняет бекап всех баз, за исключением системных (**mysql**,**performance_schema**,**information_schema**,**sys**) с локального сервера **mysql** в отдельные файлы,  и потом отправляет бекапы на **FTP**. Также скрипт удаляет с **FTP** старые бекапы, если их количество превышает заданное. \\
+Для работы нужны **pigz** и **lftp**.
+<code>#!/bin/bash
+backup_name_prefix=mysql_databases
+ftpserver=ftp.comp.local
+username=ftp_user_login_name
+password=ftp_user_pass
+currentdate=$(date +%d-%m-%Y_%H_%M)
+remote_www_dir=/backup_dir
+backup_depth=10
+#Get list of directories with full encrypted backups
+dirs=`echo "lftp -e 'set ssl:verify-certificate no ssl:check-hostname false net:connection-limit 4; cls --sort=date $remote_www_dir/$backup_name_prefix; bye;' ftp://$username:$password@$ftpserver" | /bin/bash`
+dirs=(${dirs// / })
+#check number of directories and delete oldest
+if [[ ${#dirs[@]} -gt $backup_depth ]]; then
+        for ((i = $backup_depth; i < ${#dirs[@]}; i++))
+        {
+                echo "lftp -e 'set ssl:verify-certificate no ssl:check-hostname false net:connection-limit 4; rm -r -f ${dirs[$i]}; bye;' ftp://$username:$password@$ftpserver" | /bin/bash
+        }
+fi
+#Make temporary directory
+mkdir --parents /tmp/$currentdate
+#Make archives
+databases=`mysql --execute='show databases;' | sed 's/ //g' | grep -v 'Database\|mysql\|performance_schema\|information_schema\|sys'`
+for db in $databases
+{
+       mysqldump --databases $db | pigz > /tmp/$currentdate/$db-mysqldatabase-$currentdate.sql.gz
+}
+#Make put archives to ftp
+cd /tmp/$currentdate/
+echo "lftp -e 'set ssl:verify-certificate no ssl:check-hostname false net:connection-limit 4; mkdir $remote_www_dir/$backup_name_prefix; mkdir $remote_www_dir/$backup_name_prefix/$currentdate; mirror -c -R /tmp/$currentdate $remote_www_dir/$backup_name_prefix/$currentdate; bye;' ftp://$username:$password@$ftpserver" | /bin/bash
+# Housekeeping
+rm -Rf /tmp/$currentdate
+</code>