1

I need to backup the data of a specific table by week and then restore it but without truncate table . This table contains the fechaRegistro

I prepared the following script but I'm not sure about the way to declare date variables, could you help me about it, please?

#!/bin/bash
# VARIABLES
FECHA=$(date +"%Y%m%d")
SERVIDOR="$(hostname)"
PASSWORD='PASSWORD'
BD=ordenes
TABLA="backlogPedidos"
ARCHIVO="$SERVIDOR-${BD^^}${TABLA^^}-$FECHA.sql"
RUTA_NAS="/tmp/"

Establecer variables para calcular el rango semanal


INICIO_SEMANA=$(date -d "last sunday - 6 days" +"%Y-%m-%d 00:00:00")
FIN_SEMANA=$(date -d "last saturday" +"%Y-%m-%d 23:59:59")


Se generar el backup por rangoo de una semana


mysqldump --databases $BD --tables $TABLA --where="fechaRegistro BETWEEN '$INICIO_SEMANA' AND '$FIN_SEMANA'" --skip-comments --compact --single-transaction --default-character-set=UTF8 --insert-ignore --complete-insert --skip-triggers --skip-routines --no-create-info --disable-keys --set-gtid-purged=OFF -q --lock-tables=false --user=backup -p$PASSWORD > /tmp/$ARCHIVO


echo "Backup semanal generado: $ARCHIVO"
Carolina
  • 47
  • 5

1 Answers1

1

I need to backup the data of a specific table by week and then restore it but without truncate table

If we are talking about previous week based on current date then to find the starting day(Monday) use,

ebasha:~ # date --date="last monday - 7 days" +%Y-%m-%d
2024-11-18

End the previous week(Sunday)

ebasha:~ # date --date="last sunday" +%Y-%m-%d
2024-11-24

I created an example.

Change/add variables to the script and commands to the dump as per your needs, the following is just a demostration

The file /etc/db_connect.conf contains db credentials such as:

DB_USER="user"
DB_PASSWORD="password"
DB_NAME="dbName"

Give proper permissions to the script and db ccredentials , in my case:

ebasha:~ # chmod 600 /etc/db_connect.conf
ebasha:~ # chmod 600 /root/backupTable.sh

Script,

#!/bin/bash

#db credentials
source /etc/db_connect.conf


TABLE_NAME="admin_login_log"
DATE_COLUMN="last_login_date"
BACKUP_DIR="/root/backup"
BACKUP_FILE="${BACKUP_DIR}/${TABLE_NAME}backup$(date +%Y-%m-%d).sql"


start and end date of the previous week


START_DATE=$(date --date="last monday - 7 days" +%Y-%m-%d)" 00:00:00"
END_DATE=$(date --date="last sunday" +%Y-%m-%d)" 23:59:59"


ensure the backup directory exists


mkdir -p "$BACKUP_DIR"


Perform the backup


mysqldump -u "$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" "$TABLE_NAME" --where="$DATE_COLUMN BETWEEN '$START_DATE' AND '$END_DATE'" > "$BACKUP_FILE"


Check if the backup was successful


if [[ $? -eq 0 ]]; then
    echo "Backup successful: $BACKUP_FILE"
else
    echo "Backup failed."
    exit 1
fi

After executing

ebasha:~ # bash /root/backupTable.sh
Backup successful: /root/backup/admin_login_log_backup_2024-11-27.sql
Ergest Basha
  • 5,369
  • 3
  • 7
  • 22