Database Backup

[kedar_dave9002]

How to take auto backup at certain time daily in solo for database ?

 

[admin]

#!/bin/bash

# Set database credentials
USER="username"
PASSWORD="password"
HOST="localhost"
DB_NAME="database_name"

# Set backup directory and filename
BACKUP_DIR="/path/to/backup/directory"
DATE=$(date +%Y-%m-%d-%H-%M-%S)
BACKUP_FILENAME="$DB_NAME-$DATE.sql"

# Create backup directory if it doesn't exist
if [ ! -d "$BACKUP_DIR" ]; then
  mkdir -p "$BACKUP_DIR"
fi

# Use mysqldump to create backup
mysqldump --user=$USER --password=$PASSWORD --host=$HOST $DB_NAME > $BACKUP_DIR/$BACKUP_FILENAME

# Print success message
echo "Database backup created: $BACKUP_DIR/$BACKUP_FILENAME"

 

[admin]

mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

How to resolve above errors
create new file under user home directory /home/user1/.my.cnf

[mysqldump]
user=username
password=password

Now use updated script

#!/bin/bash

# Set database credentials
HOST="localhost"
DB_NAME="database_name"

# Set backup directory and filename
BACKUP_DIR="/path/to/backup/directory"
DATE=$(date +%Y-%m-%d-%H-%M-%S)
BACKUP_FILENAME="$DB_NAME-$DATE.sql"

# Create backup directory if it doesn't exist
if [ ! -d "$BACKUP_DIR" ]; then
  mkdir -p "$BACKUP_DIR"
fi

# Use mysqldump to create backup
mysqldump --defaults-file=~/.my.cnf --host=$HOST $DB_NAME > $BACKUP_DIR/$BACKUP_FILENAME

# Print success message
echo "Database backup created: $BACKUP_DIR/$BACKUP_FILENAME"