HOW-TO Easily Install and Secure MariaDB MySQL Server

August 1, 2024

This HOW-TO is an easy step-by-step guide to getting a MySQL server working on a Raspberry Pi including setting up user authentication.

If you haven’t yet set up your Pi here’s a HOW-TO: http://tinab.blog/how-to-headless-pi-easy-setup

You can skip this step but it’s a useful base that ensures a good start: http://tinab.blog/how-to-install-a-standard-base-linux-server-on-raspberry-pi

Start by updating (if you haven’t followed the steps above), having enabled SSH and logged in:

sudo apt update && sudo apt -y upgrade

Then obtain a certificate [instructions to follow] and copy your certificate files into an accessible folder on the Pi – if you’ve followed the steps above then copy it in to the smb_transfer_folder using your favourite file manager (ie you’ll need to know how to do that from Windows or Mac, etc, by Googling if you don’t know already).

Next, install MariaDB server and start the configuration tool

sudo apt install mariadb-server
sudo mysql_secure_installation

The secure installation configuration tool will ask a series of questions. At the time of writing the questions were as follows, answer them with the bold content (selecting a password you can remember when requested)

Enter current password for root (enter for none): <ENTER>
Switch to unix_socket authentication [Y/n]: n
Change the root password? [Y/n]: Y
Remove anonymous users? [Y/n]: Y
Disallow root login remotely? [Y/n]: Y
Remove test database and access to it? [Y/n]: Y
Reload privilege tables now? [Y/n]: Y

Once completed you should be greeted with a message along the lines of “All done! Thanks for using MariaDB”

Next create some users and set some privileges – substitute your selected usernames and passwords for the items in <angle_brackets>. First, start the command line client…you’ll need the password you remember from above

sudo mysql -u root -p

then create three users, one for administration, one for accessing and modifying data and one for automated access by a backup script. Each should be entered at the “MariaDB [(none)]>” prompt. When you are done, exit the CLI with Ctrl+D

CREATE USER '<admin_user>'@'%' IDENTIFIED BY '<admin_password>';
CREATE USER '<data_access_user>'@'192.168.%' IDENTIFIED BY '<data_password>';
CREATE USER '<backup_user>'@'localhost' IDENTIFIED BY '<backup_password>';
GRANT ALL PRIVILEGES ON *.* TO '<admin_user>'@'%';
GRANT SELECT, UPDATE, INSERT, DELETE ON *.* TO '<data_access_user>'@'192.168.%';
GRANT SUPER, SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO '<backup_user>'@'localhost';
FLUSH PRIVILEGES;

Next are a few configuration changes. Firstly to allow access from network devices (eg for allowing something like Node-RED running on another box to access MySQL), and also to enable binary logging which allows incremental backups to be taken

Make a directory for the binary logs and set suitable permissions

sudo mkdir /var/log/mysql
sudo chown -R mysql:mysql /var/log/mysql

Amend the config file…first open in the nano editor

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Replace/add the following lines:

bind-address = 0.0.0.0
and do the below bit:log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_format = mixed

Close and save with Ctrl+X, yes, <ENTER>. Then restart the service

sudo systemctl restart mariadb.service

This should be all that’s required, but it’s worth establishing that a few things are as they should be. Firstly ensure binary logging was enabled by entering the MySQL CLI as above, then executing the following

show binary logs;

If the system responds with anything other than “You are not using binary logging” then all’s well.

Finally, configure and log in using a client (I like MySQLWorkbench). It’s worth testing both network access and secure access over SSH. Firstly test directly using the following parameters

Standard TCP/IP
MySQL hostname: <your_pi_IP_address>:22
MySQL username: <admin_user> or <data_access_user>
Password: DON'T ENTER SO AS TO ALLOW ENTRY AT RUNTIME

You should receive a positive response – usually a blank new window allowing you to enter a query. If it hasn’t worked there’ll be an error message with useful information about where to look for faults.

And with an SSH login

Standard TCP/IP over SSH
SSH hostname: <your_pi_IP_address>:22
SSH username: <the username of the user you'd normally SSH in with>
Password/keychain: DON'T ENTER SO AS TO ALLOW ENTRY AT RUNTIME
MySQL hostname: localhost
MySQL server port: 3306
Username: <admin_user> or <data_access_user>
Password: LEAVE BLANK TO ENTER AT RUNTIME

If you set up your Pi using the links at the start then there’s an easy way to backup your MySQL data and get it off the box into iDrive online by adding a couple of scripts to place backups in your backed-up folder. The scripts essentially extend the functionality of those created in the HOW-TO linked above by copying a backup into the backed-up folder which is then backed-up by iDrive.

The first script runs daily and takes a full backup

sudo touch /home/<username>/backups/scripts/daily_mysql_backups.sh
sudo chmod 755 /home/<username>/backups/scripts/daily_mysql_backups.sh
sudo nano /etc/crontab

Add the following line to the block of scheduled tasks

43 2    * * *   root    /home/<username>/backups/scripts/daily_mysql_backups.sh

Then edit the script

sudo nano /home/<username>/backups/scripts/daily_mysql_backups.sh

and copy the following in before Ctrl+X, yes, <ENTER> to exit and save (replace <username> on line 3 with the username used throughout, above.

#!/bin/bash
# username
username_to_backup=<username>
#path to backup storage directory
backup_folder=/home/$username_to_backup/backups
## MySQL full backup (and flush binary logs and initialise a new binary log)
mysqldump --defaults-extra-file=/home/$username_to_backup/mysqlpassword.cnf -u backup_user --flush-logs --delete-master-logs --single-transaction --all-databases | gzip > $backup_folder/MySQL-full_$(date +"%y%m%d_%H%M%S").gz

To allow the script access to the MySQL server the above script accesses a saved password and will need the following file creating and editing

sudo touch ~/mysqlpassword.cnf
sudo chmod 600 ~/mysqlpassword.cnf
sudo nano ~/mysqlpassword.cnf

Add the following content, updating <your_password> to match the backup user password provided in the CREATE USER steps above

[mysqldump]
# the following password will be sent to mysqldump
password="<your_password>"
	
[mysql]
# the following password will be sent to mysql
password="<your_password>"

Test that the script runs by executing the following and then checking the backup folder for new contents

sudo ./backups/scripts/daily_mysql_backups.sh

To add an hourly incremental backup follow these steps

sudo touch /home/<username>/backups/scripts/hourly_mysql_backups.sh
sudo chmod 755 /home/<username>/backups/scripts/hourly_mysql_backups.sh
sudo nano /etc/crontab

Add the following line to the block of scheduled tasks

47 *    * * *   root    /home/<username>/backups/scripts/hourly_mysql_backups.sh

Then edit the script

sudo nano /home/<username>/backups/scripts/hourly_mysql_backups.sh

and copy the following in before Ctrl+X, yes, <ENTER> to exit and save (replace <username> on line 3 with the username used throughout, above.

#!/bin/bash
# username
username_to_backup=<username>
#path to backup storage directory
backup_folder=/home/$username_to_backup/backups
#path to directory with binary log files
binlogs_path=/var/log/mysql/
#start writing to new binary log file
mysql --defaults-extra-file=/home/$username_to_backup/mysqlpassword.cnf -u root -E --execute='FLUSH BINARY LOGS;' mysql
#get list of binary log files
binlogs=$(mysql --defaults-extra-file=/home/$username_to_backup/mysqlpassword.cnf -u root -E --execute='SHOW BINARY LOGS;' mysql | grep Log_name | sed -e 's/Log_name://g' -e 's/^[[:space:]]*//' -e 's/[[:space:]]*$//')
#get list of binary log for backup (all but the last one)
binlogs_without_Last=`echo "${binlogs}" | head -n -1`
#get the last active binary log file (which you do not have to copy)
binlog_Last=`echo "${binlogs}" | tail -n -1`
#form full path to binary log files
binlogs_fullPath=`echo "${binlogs_without_Last}" | xargs -I % echo $binlogs_path%`
#compress binary logs into archive
zip $backup_folder/MySQL-inc_$(date +"%y%m%d_%H%M%S").zip $binlogs_fullPath
#delete saved binary log files
echo $binlog_Last | xargs -I % mysql --defaults-extra-file=/home/$username_to_backup/mysqlpassword.cnf -u root -E --execute='PURGE BINARY LOGS TO "%";' mysql

Test that the script runs by executing the following and then checking the backup folder for new contents

sudo ./backups/scripts/hourly_mysql_backups.sh

Leave a Reply Cancel reply