I usually do not fear updates, but when it comes to databases, I am getting really careful with updating them. It wouldn’t be the first time when a database goes plonk after this.
So what happened? My MySQL docker got updated becuase I simply misclicked and of course, the thing broke. The update script, which was set to “latest”, performed an upgrade to 9.x instead of sticking to the latest 8.x version.
After some research, I read that one can enter “mysql:8.2.0” in the repository field. After entering this and starting the docker, the software got reinstalled with the proper version and everything worked again. I must’ve been lucky that the database itself was not migrated to v9 already, I guess.
Just another snippet of code that can be implemented somewhere:
set-perms.sh
#!/bin/bash
# Function to change ownership of the www folder
change_ownership() {
sudo chown -R "$1":www-data /var/www
echo "Ownership of the www folder has been set to $1:www-data."
}
# Loop until a valid username is provided
while true; do
# Prompt the user to enter the desired username
read -p "Enter the username for permissions: " username
# Check if the username provided exists
if id "$username" &>/dev/null; then
change_ownership "$username"
break # Exit the loop if a valid username is provided
else
echo "Error: User $username does not exist."
fi
done
echo "done!"
Just digging deeper in automating thing, so I made two scripts:
backup_cron.sh
#!/bin/bash
# Define the path to the backup script
BACKUP_SCRIPT="/backup_mysql.sh"
# Define the cron job command to run the backup script once a week (every Sunday at midnight)
CRON_COMMAND="0 0 * * 0 $BACKUP_SCRIPT"
# Add the cron job to the crontab
(crontab -l 2>/dev/null; echo "$CRON_COMMAND") | crontab -
echo "Backup cron job set up successfully."
And the script that executes the backup every Sunday:
backup_mysql.sh
#!/bin/bash
# Define the directory where backup files will be stored
BACKUP_DIR="/var/backups/mysql"
# Ensure the backup directory exists
mkdir -p "$BACKUP_DIR"
# Define the filename for the backup file (include date in the filename)
BACKUP_FILE="$BACKUP_DIR/mysql_backup_$(date +%Y-%m-%d_%H-%M-%S).sql"
# Define the compressed filename
COMPRESSED_FILE="$BACKUP_FILE.gz"
# Define MySQL username and password (replace with your MySQL credentials)
DB_USER="USERNAME"
DB_PASS="PASSWORD"
# Dump all databases into a single SQL file
mysqldump -u "$DB_USER" -p"$DB_PASS" --all-databases > "$BACKUP_FILE"
# Add permissions to the backup file
chmod 600 "$BACKUP_FILE"
# Compress the backup file
gzip "$BACKUP_FILE"
echo "Database backup completed. Backup stored in: $COMPRESSED_FILE"
Currently successfullyneeds more testing on a Debian OS 11.5.0 installation. (I now remember why I didn’t want to touch this stuff any more. But oh well, I may as well completely overhaul this script again. The current one does work, but not well enough for me)
Step-by-step is functioning, will now wipe the server to start from scratch again. Also, the previous article will now be deleted.
Note:
If you are using this on a fresh Debian install, SUDO might not have been installed, nor has the user (so not the root) been add to SUDO. This is how you do that if you are logged in as user:
You need to exit twice so you log out as root and as user. After that, you need to log back in as user so you can start using sudo from now on.
Let’s begin! You can name the script something like
setup.sh
#!/bin/bash
# Update system
sudo apt update
sudo apt upgrade -y
# Install necessary packages
sudo apt install gnupg nginx mariadb-server php7.4-fpm php7.4-mysql php7.4-curl php7.4-gd php7.4-mbstring php7.4-xml php7.4-zip ssh ufw nano fail2ban curl wget sudo openssl net-tools unzip -y
# Add repository key for PHP 8.0
sudo apt install software-properties-common -y
sudo wget -O /etc/apt/trusted.gpg.d/php.gpg https://packages.sury.org/php/apt.gpg
# Add repository for PHP 8.0
echo "deb https://packages.sury.org/php/ $(lsb_release -sc) main" | sudo tee /etc/apt/sources.list.d/php.list
sudo apt update
# Install PHP 8.0 and extensions
sudo apt install php8.0-fpm php8.0-mysql php8.0-curl php8.0-gd php8.0-mbstring php8.0-xml php8.0-zip -y
# Configure UFW to allow web traffic and SSH
sudo ufw allow OpenSSH
sudo ufw allow 'Nginx Full'
sudo ufw allow 3306/tcp
# Enable UFW
sudo ufw enable
# Configure automatic security updates
echo 'APT::Periodic::Update-Package-Lists "1";' | sudo tee -a /etc/apt/apt.conf.d/10periodic
echo 'APT::Periodic::Download-Upgradeable-Packages "1";' | sudo tee -a /etc/apt/apt.conf.d/10periodic
echo 'APT::Periodic::AutocleanInterval "7";' | sudo tee -a /etc/apt/apt.conf.d/10periodic
echo 'APT::Periodic::Unattended-Upgrade "1";' | sudo tee -a /etc/apt/apt.conf.d/20auto-upgrades
# Disable root login via SSH
sudo sed -i 's/#PermitRootLogin prohibit-password/PermitRootLogin no/' /etc/ssh/sshd_config
sudo systemctl restart sshd
# Generate SSH key pairs
ssh-keygen -t rsa -b 4096
# Configure Nginx
sudo systemctl start nginx
sudo systemctl enable nginx
# Configure PHP 7.4
sudo systemctl start php7.4-fpm
sudo systemctl enable php7.4-fpm
# Configure PHP 8.0
sudo systemctl start php8.0-fpm
sudo systemctl enable php8.0-fpm
# Configure MariaDB
sudo mysql_secure_installation
# Allow Nginx to use PHP
sudo sed -i 's/;cgi.fix_pathinfo=1/cgi.fix_pathinfo=0/' /etc/php/7.4/fpm/php.ini
sudo sed -i 's/;cgi.fix_pathinfo=1/cgi.fix_pathinfo=0/' /etc/php/8.0/fpm/php.ini
# Restart Nginx and PHP services
sudo systemctl restart nginx
sudo systemctl restart php7.4-fpm
sudo systemctl restart php8.0-fpm
# Create a temporary file to store server and PHP info
tmpfile=$(mktemp)
# Get server info
uname -a > "$tmpfile"
# Get PHP version
php -v >> "$tmpfile"
# Append server and PHP info to index.html
cat "$tmpfile" | sudo tee /var/www/html/index.html >/dev/null
# Remove temporary file
rm "$tmpfile"
# Install adminer
sudo apt install adminer -y
sudo ln -s /usr/share/adminer/adminer.php /var/www/html/adminer.php
sudo chown -R www-data:www-data /usr/share/adminer /var/www/html/adminer.php
# Set the permissions correct for www
sudo chmod g+w /var/www
sudo chown -R :www-data www
sudo chmod g+s www
change_ownership() {
sudo chown -R "$1":www-data /var/www
echo "Ownership of the www folder has been set to $1:www-data."
}
# Loop until a valid username is provided
while true; do
# Prompt the user to enter the desired username
read -p "Enter the username for permissions: " username
# Check if the username provided exists
if id "$username" &>/dev/null; then
change_ownership "$username"
break # Exit the loop if a valid username is provided
else
echo "Error: User $username does not exist."
fi
done
echo "LEMP setup completed successfully."
echo "Check if Adminer is working by opening a browser, and entering http://IP_address/adminer.php in the address bar"
To make the script executable:
sudo chmod +x setup.sh
and run it with
sudo ./setup.sh
When things like PHP fail to work (becuase y’know… PHP)
These are the files that have to look this way to make PHP happen.
sudo nano /etc/nginx/sites-available/default
##
# You should look at the following URL's in order to grasp a solid understanding
# of Nginx configuration files in order to fully unleash the power of Nginx.
# https://www.nginx.com/resources/wiki/start/
# https://www.nginx.com/resources/wiki/start/topics/tutorials/config_pitfalls/
# https://wiki.debian.org/Nginx/DirectoryStructure
#
# In most cases, administrators will remove this file from sites-enabled/ and
# leave it as reference inside of sites-available where it will continue to be
# updated by the nginx packaging team.
#
# This file will automatically load configuration files provided by other
# applications, such as Drupal or WordPress. These applications will be made
# available underneath a path with that package name, such as /drupal8.
#
# Please see /usr/share/doc/nginx-doc/examples/ for more detailed examples.
##
# Default server configuration
#
server {
listen 80 default_server;
listen [::]:80 default_server;
# SSL configuration
#
# listen 443 ssl default_server;
# listen [::]:443 ssl default_server;
#
# Note: You should disable gzip for SSL traffic.
# See: https://bugs.debian.org/773332
#
# Read up on ssl_ciphers to ensure a secure configuration.
# See: https://bugs.debian.org/765782
#
# Self signed certs generated by the ssl-cert package
# Don't use them in a production server!
#
# include snippets/snakeoil.conf;
root /var/www/html;
# Add index.php to the list if you are using PHP
index index.php index.html index.htm index.nginx-debian.html;
server_name _;
location / {
# First attempt to serve request as file, then
# as directory, then fall back to displaying a 404.
try_files $uri $uri/ =404;
}
# pass PHP scripts to FastCGI server
#
location ~ \.php$ {
include snippets/fastcgi-php.conf;
# # With php-fpm (or other unix sockets):
fastcgi_pass unix:/run/php/php8.0-fpm.sock;
#non-standard addition
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
include fastcgi_params;
}
# # With php-cgi (or other tcp sockets):
# fastcgi_pass 127.0.0.1:9000;
#}
# deny access to .htaccess files, if Apache's document root
# concurs with nginx's one
#
#location ~ /\.ht {
# deny all;
#}
}
# Virtual Host configuration for example.com
#
# You can move that to a different file under sites-available/ and symlink that
# to sites-enabled/ to enable it.
#
#server {
# listen 80;
# listen [::]:80;
#
# server_name example.com;
#
# root /var/www/example.com;
# index index.html;
#
# location / {
# try_files $uri $uri/ =404;
# }
#}
Something spoopy was going on in my database. All was working fine, but it kept spewing out the following error:
Cannot load from mysql.proc. The table is probably corrupted
So this was fixed by going into the database terminal and entering:
mysql_upgrade -u root -p
Make sure you have the root/privileged password ready. Depending on how many databases you have and how large they are; it can take a bit of time. Have patience.
Probably caused by utilizing the wrong method in an older version, where one had to manually enter the paid amount instead of selecting “mark invoice paid”
Step 1. Check the affected clients and make sure that the actual paid sum is correct, regardless what the “minus” amount says.
Step 2. Make a backup, or find a proper host where you can do an easy roll-back in case you mess up.
Step 3. Open the database of Invoice Ninja and find the “clients” table
Step 4. In the column “Balance” you will see a negative amount. Double-click in that field, enter 0.00 (zero-point-zero-zero) and press enter.
If the admin user is still defined, the simplest option is to change the password in the database to a known value. This requires that you have access to the MySQL database using phpMyAdmin.
“what, no hacking?”, I hear you say. No. Again, you need access to the MySQL database by using phpMyAdmin.
Navigate to phpMyAdmin and select the database for the Joomla site in the left-hand drop-down list box. This will show the database tables on the left side of the screen.
Click on the table “jos_users” in the list of tables.
Click on the “Browse” button in the top toolbar.
Find the user whose password you want to change and press the Edit icon for this row. A form will display that allows you to edit the password field.
Copy the following value into the password field and press the Go button.