MariaDB Database Management and Usage

MariaDB is an open-source relational database management system derived from MySQL, known for its high performance, scalability, and reliability. ServBay comes with MariaDB, and this document will detail how to manage and use MariaDB databases within ServBay, including installation, configuration, backup, restoration, and performance optimization.

Installing and Configuring MariaDB

Open ServBay, click on the left navigation Services - MariaDB, and select the MariaDB version you need to install. Currently, ServBay supports MariaDB 10.4 - MariaDB 11.7.

Starting and Managing MariaDB Services

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 - MariaDB.
3. Find the MariaDB service and perform start, stop, or restart operations.

Using 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 provides a powerful graphical interface for configuring the MariaDB service. Please refer to Modify MariaDB Configuration to learn how to modify and optimize MariaDB configurations.

Connecting to MariaDB

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

Connecting via Command Line

1. Using TCP/IP connection:

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

2. Using Socket connection:

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

Connecting via phpMyAdmin and Adminer

ServBay comes with phpMyAdmin and Adminer by default, and you can access them as follows:

• Access URL: https://servbay.host

On this page, you'll find the entries for phpMyAdmin and Adminer. Log in using the username and password obtained from the ServBay management panel.

Database Management

Creating Databases and Users

1. Create a Database:

   CREATE DATABASE mydatabase;

2. Create a User and Grant Permissions:

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

Backup and Restoration

Backup Database

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

/Applications/ServBay/backup/mariadb

Use the mysqldump tool to back up databases:

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

Restore Database

Use the mysql tool to restore databases:

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

Performance Optimization

MariaDB offers multiple performance optimization options. Below are some common optimization methods.

Index Optimization

Ensure indexes are created for columns used frequently in queries. For example:

CREATE INDEX idx_column_name ON your_table_name(column_name);

Query Optimization

Use the EXPLAIN command to analyze query performance and optimize accordingly. For example:

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

Configuration Optimization

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

[mysqld]
innodb_buffer_pool_size = 1G

Security Management

Ensuring database security is crucial. Here are some security management suggestions.

Setting Strong Passwords

Set strong passwords for all database users:

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

Regular Backups

Regularly back up databases to prevent data loss.

Restrict Access

Restrict database user access and grant 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

Password Errors

Please refer to the article: How to Reset MariaDB Database Root Password

Unable to Connect to MariaDB

1. Check if MariaDB is Running:

   servbayctl status mariadb 11.3

2. Check Firewall Settings: Ensure the firewall allows MariaDB’s 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;

Conclusion

MariaDB is a powerful and flexible database management system. ServBay comes with MariaDB, making database management and usage more convenient. Through this introduction, you can easily perform operations such as installing, configuring, connecting, managing, backing up, restoring, and optimizing the performance of MariaDB, ensuring efficient operation and security of the database.