MariaDB Installation and Configuration

MariaDB Installation and Configuration

Overview

This page provides step-by-step instructions for installing MariaDB on buzz.deome.net and configuring it to work with applications like BookStack and the photo gallery.

1. Installing MariaDB

1. Update the System:

   • Ensure all packages are up-to-date.

     sudo apt update && sudo apt upgrade -y
     

2. Install MariaDB Server:

   • Use the following command to install MariaDB:

     sudo apt install mariadb-server -y
     

3. Start and Enable MariaDB:

   • Start the MariaDB service and enable it to start on boot:

     sudo systemctl start mariadb
     sudo systemctl enable mariadb
     

4. Verify Installation:

   • Check the MariaDB version to confirm it is installed:

     mysql --version
     

   • You should see output confirming the MariaDB version, as shown in your initial check.

2. Initial MariaDB Configuration

1. Run the Security Script:

   • MariaDB provides a security script to help you configure common security settings.

     sudo mysql_secure_installation
     

   • This script will prompt you to:
     • Set a root password.
     • Remove anonymous users.
     • Disallow remote root login.
     • Remove test databases.
   • Answer each prompt as needed to secure the installation.

2. Log in as the Root User:

   • After configuring security, log in to MariaDB:

     sudo mysql -u root -p
     

3. Create a New Database and User for Each Application:

   • Database for BookStack:

     CREATE DATABASE bookstackdb;
     CREATE USER 'bookstackuser'@'localhost' IDENTIFIED BY 'your_password';
     GRANT ALL PRIVILEGES ON bookstackdb.* TO 'bookstackuser'@'localhost';
     FLUSH PRIVILEGES;
     

   • Database for Photo Gallery:

     CREATE DATABASE gallerydb;
     CREATE USER 'galleryuser'@'localhost' IDENTIFIED BY 'your_password';
     GRANT ALL PRIVILEGES ON gallerydb.* TO 'galleryuser'@'localhost';
     FLUSH PRIVILEGES;
     

   • Replace your_password with secure passwords.

4. Exit MariaDB:

   • Once databases and users are set up, exit MariaDB:

     EXIT;
     

3. Basic MariaDB Configuration Options

1. Optimize Settings (Optional):

   • Open the MariaDB configuration file:

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

   • Modify settings under [mysqld] as needed. Example optimizations:

     max_connections = 100
     innodb_buffer_pool_size = 256M
     

   • Save and close the file.

2. Restart MariaDB:

   • Apply changes by restarting the MariaDB service:

     sudo systemctl restart mariadb
     

4. Testing and Verification

1. Verify Databases and Users:

   • Log in as root and list the databases to confirm they were created:

     sudo mysql -u root -p
     SHOW DATABASES;
     

2. Connect Using Each Application’s Credentials:

   • Use the created usernames and passwords to confirm access for BookStack and the gallery:

     mysql -u bookstackuser -p
     mysql -u galleryuser -p
     

3. Connection from Applications:

   • Ensure that BookStack and the photo gallery application can connect to MariaDB using the specified database names and credentials.

5. Maintenance and Backup

1. Automate Backups:

   • Create a cron job to back up each database:

     mysqldump -u root -p bookstackdb > /path/to/backup/bookstackdb.sql
     mysqldump -u root -p gallerydb > /path/to/backup/gallerydb.sql
     

2. Update MariaDB:

   • Periodically check for updates to keep MariaDB secure and optimized:

     sudo apt update && sudo apt upgrade mariadb-server