Debian - install MariaDB server from scratch

Download Markdown

Table of Contents

Generic Proxmox VM creation

  • Download the most current image from https://cdimage.debian.org/debian-cd/current/amd64/iso-cd/ → file name is debian-XX-Y-Z-amd64-netinst.iso.
  • Upload it within Proxmox under local (nodeX) → ISO images.
  • Create a VM
  • During installation, leave the disk partitioning to Guided partitioning with just one partition (too much hassle otherwise).

Sudoers (optional)

  • Initially, before SSH is set up, you can use the Proxmox built-in console.

  • This is useful if there will be more users connecting to the VM, as you can use admin rights (sudo) using your account, which makes it easier to identify who executed which commands and who is using system resources.

    su -                          # Elevate to root
apt-get install sudo          # Install the sudoers package
usermod -aG sudo <username>   # Add your user name into the sudo group
gpasswd -a <username> sudo    # An alternative way if the above does not work
exit                          # Go back to your account

  • A full exit and a new SSH connection may need to start for the change to take effect.

  • In the future, instead of having to elevate yourself via 'su -', you can run 'sudo ', authenticate using your password, and voila!

  • If you prefer to log in as root, you can run 'sudo su'.

SSH Config

  • To modify the default SSH port, edit the following file (do not accidentally swap it with 'ssh_config', as that one will not lead to the desired change).

    sudo nano /etc/ssh/sshd_config    # In case you do not have sudoers (above) installed, use 'su -' and then run this command without sudo

  • In the sshd_config, change Port from 2222 to 22.

  • Exit and run the following to restart the service and check that the system is expecting a different port number.

systemctl restart ssh
ss -tulpn | grep 22        # You should see 2222
  • Then try connecting to the VM via SSH.

Change default IP address

  • Fetch the MAC address of your VM either from the hypervisor network settings or by running the following:
ip addr | grep ether
  • Log into your DHCP/firewall and check which IP address would be available. Assign the MAC address to it.
  • Restart the VM.

Install the qemu agent (if applicable)

  • Install a qemu agent (Proxmox) for better monitoring and soft shutdowns (important, especially if you will be running a database on it as well).
sudo apt install qemu-guest-agent
sudo systemctl enable qemu-guest-agent
  • Then on the VM, run 'shutdown now' and make sure the VM properly switches off.
  • On the Proxmox node, go to the VM:

1 install the qemu agent if

  • Start the VM again and run the following command to ensure that the agent is running.
systemctl status qemu-guest-agent
  • You should now be able to see more details about your VM in the Proxmox console under 'Summary'.
  • If unsure, SSH into the Proxmox host and run:
qm agent <vmid> ping    # No errors means it got through
qm agent <vmid> info    # Prints out all kinds of details about the VM

Install ufw (firewall)

  • You might ask why you'd install a firewall on a local machine that is already behind a firewall. Simply run the following command to see what services are running on the machine and therefore what ports may be opened:
# List services running on your server
cat /etc/services

# List opened ports that are ready to receive a payload over TCP and UDP:
ss -utl
  • Depending on what services you have enabled, you might see the default port 21 for FTP and some others. For a DB server, that is not recommended. So let's secure it to only the necessary ones, such as SSH (in our case, TCP/2222) and SQL (TCP/3306).
  • It is recommended at this point to make a snapshot of the VM/container in case you make a mistake and lock yourself out from SSH (although the Proxmox console will still work).
  • To install ufw, run the following:
apt-get install ufw -y
  • To open ports on your machine to match the custom SSH port and SQL, run the following (assuming your local subnet is 192.168.10.0/24):

ufw status # To show the status, which is off by default after installation

ufw allow from 192.168.10.0/24 proto tcp to any port 3306 # This will allow web servers to connect to this web server
ufw allow from 192.168.10.0/24 proto tcp to any port 2222 # This will allow SSH clients to connect from that subnet
ufw deny 22/tcp # Block the default SSH port since we are not using it
ufw deny http # We are not running a web server here so no need
ufw deny https # Same as above
ufw deny ftp # No FTP server needed here

ufw enable # Confirm 'y' as yes if asked
  • If you see that your server is listening on ports 80, 443 or other web-related ones, then please run 'whereis nginx' and 'whereis apache2' (and others if you use them) and uninstall them (apt-get purge -y) as we want to separate these services and not run them all in one machine.

Install MariaDB

  • Update and Install the following packages:
apt update && apt upgrade
apt-get install mariadb-server mariadb-common -y
  • At this point you might want to take a snapshot of the VM/container in Proxmox and mark it as a 'maria_clean_install'.
  • You could also convert the VM into a template in Proxmox in case you plan to deploy more of them. However, keep in mind that the OS data will age soon and there may be a new major version of Debian and the related packages in the next few months, so then you would be spending a lot of time upgrading.
    • Note: if you right click on the VM and choose 'Convert to template', you can no longer use it. You will then need to create a new VM from the template. This process is supposedly irreversible, although there is a manual workaround to reverse it from the Proxmox shell.

Connect to MariaDB to create a database

  • Let's assume that you want to create a DB for a WordPress site.
  • Run the following command to get into your database's shell:
mysql -u root -p
  • Enter the root password and you will get into the MariaDB shell.
  • A few commands to play around:
SHOW DATABASES;                        # show a list of all databases on the host
SHOW DATABASES LIKE 'info%';           # show only dbs starting with 'info'
USE information_schema;                # connect to the db called 'information_schema'
SHOW TABLES;                           # display all the tables within the database
SELECT USER from mysql.user;           # shows all users connected to any db
  • Now to actually create a database with a user for a WP instance, run the following:

GRANT ALL PRIVILEGES ON my_special_db.* TO "my_special_db_wp_user"@"localhost" IDENTIFIED BY "YourStrongPasswordHere";
SHOW DATABASES;
SELECT User FROM mysql.db WHERE Db = 'bachelor_tech_com_db';  # Show usernames of users that have access to a particular database
FLUSH PRIVILEGES;
EXIT;
  • Now you have a ready-made DB and can use it for your WP instance (typically set up in the 'wp-config.php' file).

More in MariaDB & Galera