Welcome to VK6KFK - Linux Tips

Linux Command Line & System Tips

Creating backups for databases and web directory

Posted on: 03/09/2026 10:10

Create the Backup Script

Terminal Window

sudo nano /root/backup.sh

Paste the following script into the file.

Terminal Window

#!/bin/bash

# Configuration
BACKUP_DIR="/root/backups"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
MYSQL_USER="root"
# Leave empty if using auth socket plugin, or fill in password if required
MYSQL_PASSWORD="your_password" 

# Create backup directory if it does not exist
mkdir -p "$BACKUP_DIR"

# 1. Backup all MySQL databases
if [ -z "$MYSQL_PASSWORD" ]; then
    mysqldump --all-databases | gzip > "$BACKUP_DIR/all_databases_$TIMESTAMP.sql.gz"
else
    mysqldump -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" --all-databases | gzip > "$BACKUP_DIR/all_databases_$TIMESTAMP.sql.gz"
fi

# 2. Backup /var/www/html/ folder
tar -czf "$BACKUP_DIR/html_backup_$TIMESTAMP.tar.gz" -C /var/www/html .

# Optional: Delete backups older than 7 days to save space
find "$BACKUP_DIR" -type f -mtime +7 -name "*.gz" -delete

Run a test

Terminal Window

sudo /root/backup.sh

Message: mysqldump: [Warning] Using a password on the command line interface can be insecure.

You can eliminate this warning completely by moving the password to a secure configuration file that only the root user can read.

Terminal Window

sudo nano /root/.my.cnf

Place into cnf file

Terminal Window

[mysqldump]
user=root
password=your_password_here

Ensure no other users on the server can read this file:

Terminal Window

sudo chmod 600 /root/.my.cnf

Update your backup script

Terminal Window

sudo nano /root/backup.sh

replace it with this

Terminal Window

#!/bin/bash

# Configuration
BACKUP_DIR="/root/backups"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")

# Create backup directory if it does not exist
mkdir -p "$BACKUP_DIR"

# 1. Backup all MySQL databases securely
mysqldump --all-databases | gzip > "$BACKUP_DIR/all_databases_$TIMESTAMP.sql.gz"

# 2. Backup /var/www/html/ folder
tar -czf "$BACKUP_DIR/html_backup_$TIMESTAMP.tar.gz" -C /var/www/html .

# Optional: Delete backups older than 7 days to save space
find "$BACKUP_DIR" -type f -mtime +7 -name "*.gz" -delete

Make the Script Executable

Terminal Window

sudo chmod 700 /root/backup.sh

Create the Daily Cron Job

Terminal Window

sudo crontab -e

Add the following line at the very bottom of the file to run the backup every single day at 2:00 AM:

Terminal Window

0 2 * * * /bin/bash /root/backup.sh

Run the following command as the root user:

Terminal Window

sudo /root/backup.sh

Verify the Backups Created Successfully

Terminal Window

sudo ls -lh /root/backups

Question, I want to be able to backup each one of these databases on there own

Terminal Window

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| database1          |
| database2          |
| database3          |
| database4          |
+--------------------+
8 rows in set (0.01 sec)

mysql>

To back up each database into its own separate .sql.gz file automatically, we can update your backup script with a loop. This loop will dynamically read your databases and create individual files for database1, database2, database3, and database4, while skipping system databases like information_schema.

Open the Script File

Terminal Window

sudo nano /root/backup.sh

Replace the Content with the Multi-DB Script

Terminal Window

#!/bin/bash

# Configuration
BACKUP_DIR="/root/backups"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")

# Create backup directory if it does not exist
mkdir -p "$BACKUP_DIR"

# 1. Get a list of all databases and loop through them
DATABASES=$(mysql -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)")

for DB in $DATABASES; do
    echo "Backing up database: $DB"
    mysqldump "$DB" | gzip > "$BACKUP_DIR/${DB}_$TIMESTAMP.sql.gz"
done

# 2. Backup /var/www/html/ folder
tar -czf "$BACKUP_DIR/html_backup_$TIMESTAMP.tar.gz" -C /var/www/html .

# Optional: Delete backups older than 7 days to save space
find "$BACKUP_DIR" -type f -mtime +7 -name "*.gz" -delete

Create the Config FileRun this command to open the MySQL config file:

Terminal Window

[client]
user=root
password=YOUR_ACTUAL_PASSWORD

[mysqldump]
user=root
password=YOUR_ACTUAL_PASSWORD

Lock Down PermissionsFor security, you must restrict this file so only the root system user can read it:

Terminal Window

sudo chmod 600 /root/.my.cnf

Create New USer in MySQL

Posted on: 31/08/2026 00:43

Adding a new user to MySQL

Terminal Window

sudo mysql -u root -p

Create the User

Terminal Window

CREATE USER 'new_user'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'ChooseAPassword123!';

Give the User Access to a Certain Database

Terminal Window

GRANT ALL PRIVILEGES ON your_database_name.* TO 'new_user'@'localhost';

Save the Changes

Terminal Window

FLUSH PRIVILEGES;

To see all the users in your MySQL database

Terminal Window

mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| new_user         | localhost |
| new_user2        | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

You cannot see the actual passwords in MySQL

Terminal Window

SELECT user, host, plugin, authentication_string FROM mysql.user;

To change the password for a user on a specific database named your_data

Terminal Window

ALTER USER 'your_username'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'YourNewPassword123!';

Save the changes

Terminal Window

FLUSH PRIVILEGES;

(Note: In MySQL, passwords belong to the user account, not to the database itself. Changing this will update the password the user uses to log into the whole MySQL system).

1 2 3