Installing MariaDB Server

= Installing MariaDB Server =

MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system (RDBMS). It is a popular choice for web applications and is often used as a drop-in replacement for MySQL. This guide will walk you through the installation and basic configuration of MariaDB server on a Linux system, along with tips for migrating from MySQL.

Prerequisites

• A Linux server with root or sudo privileges. Dedicated servers from PowerVPS provide the necessary root access for full control.
• Internet connectivity to download packages.
• Basic understanding of the Linux command line.

Installing MariaDB Server

The installation process varies slightly depending on your Linux distribution.

Debian/Ubuntu

1. Update package lists:

sudo apt update

2. Install MariaDB server and client packages:

sudo apt install mariadb-server mariadb-client

3. Start and enable the MariaDB service:

sudo systemctl start mariadb

sudo systemctl enable mariadb

CentOS/RHEL/Fedora

1. Update package lists:

sudo dnf update -y

(Or `sudo yum update -y` on older CentOS/RHEL versions)

2. Install MariaDB server and client packages:

sudo dnf install mariadb-server mariadb-client -y

(Or `sudo yum install mariadb-server mariadb-client -y`)

3. Start and enable the MariaDB service:

sudo systemctl start mariadb

sudo systemctl enable mariadb

Securing MariaDB Installation

After installation, it's crucial to secure your MariaDB server. MariaDB provides a script for this purpose.

1. Run the security script:

sudo mariadb-secure-installation

This script will guide you through several important security steps: * Setting the root password for MariaDB. * Removing anonymous users. * Disallowing root login remotely. * Removing the test database and access to it. * Reloading privilege tables.

Follow the prompts carefully. For the root password, choose a strong, unique password.

Basic MariaDB Configuration

MariaDB's main configuration file is typically located at `/etc/mysql/my.cnf` or `/etc/my.cnf`. For systemd-based systems, it's common to have configuration snippets in `/etc/mysql/mariadb.conf.d/`.

Default Configuration

By default, MariaDB is configured for optimal performance on most systems. You can find the main configuration file and include directories using:

mysqld --verbose --help grep -A 1 "Default options"

This will show you the order in which configuration files are read.

Adjusting Settings (Example: Buffer Pool Size)

For performance tuning, you might want to adjust settings like `innodb_buffer_pool_size`. This setting is critical for InnoDB performance as it caches data and indexes.

1. Locate the configuration directory: On Debian/Ubuntu, this is often `/etc/mysql/mariadb.conf.d/`. On CentOS/RHEL/Fedora, it might be `/etc/my.cnf.d/`.

2. Create or edit a configuration file: You can create a new file (e.g., `50-server.cnf`) or edit an existing one.

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

(Use `vi` or your preferred editor)

3. Add or modify relevant settings: Under the `[mysqld]` section, you can add or change parameters. For example, to set the buffer pool size to 512MB (adjust based on your server's RAM, typically 50-70% of available RAM):

```ini [mysqld] innodb_buffer_pool_size = 512M ```

4. Restart MariaDB to apply changes:

sudo systemctl restart mariadb

Creating a New Database and User

It's best practice to create specific databases and users for your applications rather than using the root MariaDB user.

1. Log in to MariaDB as root:

sudo mariadb -u root -p

(Enter the MariaDB root password you set during `mariadb-secure-installation`)

2. Create a new database:

CREATE DATABASE your_database_name;

3. Create a new user and grant privileges: Replace `your_username`, `localhost` (or the IP address/hostname your application connects from), and `your_password` accordingly.

CREATE USER 'your_username'@'localhost' IDENTIFIED BY 'your_password';

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

4. Flush privileges to ensure changes take effect:

FLUSH PRIVILEGES;

5. Exit MariaDB:

EXIT;

Migrating from MySQL to MariaDB

MariaDB is designed to be a drop-in replacement for MySQL, meaning most applications will work without modification. The migration process is generally straightforward.

1. Backup your MySQL databases: This is the most critical step. Use `mysqldump` to create a full backup of your MySQL databases.

mysqldump -u root -p --all-databases > mysql_backup_$(date +%Y%m%d).sql

(Enter your MySQL root password when prompted)

2. Install MariaDB server (as described in the installation section above).

3. Stop the MySQL service:

sudo systemctl stop mysql

(Or `mysqld`, depending on your distribution)

4. Import the backup into MariaDB: Log in to MariaDB and import the dump file.

sudo mariadb -u root -p

(Enter your MariaDB root password)

SOURCE /path/to/your/mysql_backup_YYYYMMDD.sql;

(Replace `/path/to/your/` with the actual path to your backup file)

5. Verify data and application functionality: Test your applications thoroughly to ensure they connect to MariaDB and function as expected. You might need to update database connection strings if `localhost` or user credentials have changed.

Troubleshooting

• MariaDB service not starting:
Check the MariaDB error logs. The location varies by distribution, but common places include `/var/log/mysql/error.log` or `/var/log/mariadb/mariadb.log`.

sudo journalctl -u mariadb

(For systemd systems)
• Cannot connect to MariaDB:
* Ensure the MariaDB service is running: `sudo systemctl status mariadb`. * Check firewall rules: Make sure port 3306 (default MariaDB port) is open if you're connecting remotely. * Verify user privileges: Ensure the user you're connecting with has the correct host and password defined in MariaDB.
• Access denied for user:
This usually means the username, password, or host doesn't match what's configured in MariaDB. Log in as root and check user grants:

SHOW GRANTS FOR 'your_username'@'your_host';

Related Articles

• Linux System Administration
• Web Server Setup
• Database Optimization

Category:Database Setup Category:MariaDB Category:Server Administration