MariaDB Database Management and Usage

MariaDB is an open-source relational database management system derived from MySQL, offering high performance, scalability, and reliability. ServBay includes MariaDB, and this article will detail how to manage and use the MariaDB database in ServBay, including installation, configuration, backup, recovery, and performance optimization.

Installing and Configuring MariaDB

ServBay includes MariaDB, so you just need to ensure it is running and configured as needed.

Starting and Managing MariaDB Service

You can manage the MariaDB service through ServBay's management platform or the command-line tool servbayctl.

Using the ServBay Management Platform

1. Open the ServBay management platform.
2. Navigate to "Services".
3. Find the MariaDB service and perform start, stop, or restart operations.

Using the Command-line Tool servbayctl

# Start MariaDB service
servbayctl start mariadb 11.3

# Stop MariaDB service
servbayctl stop mariadb 11.3

# Restart MariaDB service
servbayctl restart mariadb 11.3

Configuring MariaDB

ServBay's default MariaDB configuration file is already optimized. If users need to modify the configuration file, they can find the my.cnf file at the following path:

/Applications/ServBay/etc/mariadb/11.3/my.cnf

The configuration file is in different folders depending on the MariaDB version.

Connecting to MariaDB

You can connect to MariaDB using the command-line tool mysql or graphical tools like phpMyAdmin and Adminer.

Using Command-line to Connect

1. Connecting via TCP/IP:

   mysql -u your_username -p -h localhost -P 3306

2. Connecting via Socket:

   mysql -u your_username -p -S /Applications/ServBay/tmp/mysql.sock

Using phpMyAdmin and Adminer to Connect

ServBay already comes with phpMyAdmin and Adminer. You can access them through the following way:

• Access URL: https://servbay.host

On this page, you will find the entrances to phpMyAdmin and Adminer. Use the username and password obtained from the ServBay management panel to login.

Database Management

Creating Databases and Users

1. Creating a Database:

   CREATE DATABASE mydatabase;

2. Creating a User and Granting Permissions:

   CREATE USER 'your_username'@'localhost' IDENTIFIED BY 'your_password';
   GRANT ALL PRIVILEGES ON mydatabase.* TO 'your_username'@'localhost';
   FLUSH PRIVILEGES;

Backup and Recovery

Backup Database

It is recommended to store backup files in the following directory:

/Applications/ServBay/backup/mariadb

Use the mysqldump tool to backup the database:

mysqldump -u your_username -p mydatabase > /Applications/ServBay/backup/mariadb/mydatabase.sql

Restore Database

Use the mysql tool to restore the database:

mysql -u your_username -p mydatabase < /Applications/ServBay/backup/mariadb/mydatabase.sql

Performance Optimization

MariaDB provides various performance optimization options. Here are some common optimization methods.

Index Optimization

Ensure that columns commonly used in queries have indexes. For example:

CREATE INDEX idx_column_name ON your_table_name(column_name);

Query Optimization

Use the EXPLAIN command to analyze query performance and make corresponding optimizations. For example:

EXPLAIN SELECT * FROM your_table_name WHERE column_name = 'value';

Configuration Optimization

Adjust parameters in the configuration file to enhance performance. For example, adjust the buffer pool size:

[mysqld]
innodb_buffer_pool_size = 1G

Security Management

Ensuring the security of the database is very important. Here are some security management suggestions.

Set Strong Passwords

Set strong passwords for all database users:

SET PASSWORD FOR 'your_username'@'localhost' = PASSWORD('your_new_password');

Regular Backups

Regularly backup your database to prevent data loss.

Restrict Access

Restrict database user access permissions, granting only necessary permissions:

REVOKE ALL PRIVILEGES ON *.* FROM 'your_username'@'localhost';
GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO 'your_username'@'localhost';

Common Issues and Solutions

Unable to Connect to MariaDB

1. Check if MariaDB is Running:

   servbayctl status mariadb 11.3

2. Check Firewall Settings: Ensure the firewall allows the MariaDB port (default 3306) to pass through.

Permission Issues

1. Check User Permissions:

   SHOW GRANTS FOR 'your_username'@'localhost';

2. Grant Necessary Permissions:

   GRANT ALL PRIVILEGES ON mydatabase.* TO 'your_username'@'localhost';
   FLUSH PRIVILEGES;

Summary

MariaDB is a powerful and flexible database management system. ServBay includes MariaDB, making database management and usage more convenient. This article provides instructions on installing, configuring, connecting, managing, backing up, restoring, and optimizing the performance of MariaDB, ensuring efficient operation and security of the database.