# How to configure and enable MariaDB logs

MariaDB logs are powerful tools for monitoring and optimising your database instances. This guide explains the main types of logs, how they work and how to configure and enable them.

## What logs are available in MariaDB?

[MariaDB](https://www.ionos.co.uk/digitalguide/server/know-how/what-is-mariadb/) provides **four main log types**. Each records a different aspect of database activity:

### Error log

The error log **records critical events** such as server start and stop, crashes and other serious errors. It is enabled by default and is essential for diagnosing server problems.

### General query log

The general query log **records every connection to the server and logs each SQL command that is run**. It is useful for error analysis and monitoring user activity.

### Binary log

A binary log **records all changes to the database**, including both data changes and structural modifications. It is essential for replication and point in time recovery.

### Slow query log

The slow query log **records SQL queries** that take longer than a defined execution time. It is an important tool for identifying performance bottlenecks and optimising queries.

## How to configure and use MariaDB logs

The steps below show you how to activate, configure and review the four main [log files](https://www.ionos.co.uk/digitalguide/online-marketing/web-analytics/log-files-recording-computer-processes/) in MariaDB. These include the error log, general query log, binary log and slow query log, along with the MariaDB log file location.

### Prerequisites

- A server with a current version of **Ubuntu** or another Linux distribution
- A user account with [sudo](https://www.ionos.co.uk/digitalguide/server/configuration/linux-sudo-command/) privileges
- **No MySQL installation** on the same server, as it can conflict with MariaDB configuration files and ports
- Basic SQL knowledge

### Step 1: Display and configure the error log

Make sure that [MariaDB is installed](https://www.ionos.co.uk/digitalguide/hosting/technical-matters/install-mysql-mariadb/). To do so, run the following command:

```bash
sudo apt update
sudo apt install mariadb-server
```

The first command updates your system’s package list to ensure you install the latest available version. The second command installs the MariaDB server package on your system.

To connect to MariaDB, run the following command:

```bash
sudo mariadb
```

This opens the interactive SQL console with administrative rights so you can run SQL commands directly.

In the next step, check where MariaDB writes error messages. Do this with this SQL statement:

```sql
SHOW VARIABLES LIKE '%log_error%';
```

If `log_error` has no value, error messages are written to the central system log (`syslog`) by default.

To view the system log, run the following command:

```bash
journalctl -u mariadb.service
```

This displays a **chronological list of system messages** generated by the MariaDB service. This is useful for investigating startup problems or other critical events.

To use a **custom error log file** instead, you need to edit the MariaDB configuration file. Open it with a text editor like nano:

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

In the `[mysqld]` section, add or uncomment:

```txt
log_error = /var/log/mariadb/error.log
```

You now need to ensure that the directory you specified in the configuration exists and has the correct permissions. Create it and assign the appropriate ownership so that MariaDB can write to it:

```bash
sudo mkdir /var/log/mariadb
sudo chown mysql:mysql /var/log/mariadb
```

The first command creates the `/var/log/mariadband` directory if it does not already exist. The second command changes its ownership to the `mysql` user and group, so that the MariaDB server process has the necessary write permissions for logging.

Restart MariaDB to apply the changes:

```bash
sudo systemctl restart mariadb
```

You can now view the error log:

```bash
sudo cat /var/log/mariadb/error.log
```

This file contains information such as start and stop times, warnings and plug-in errors.

### Step 2: Enable general query log

Check whether the general query log is currently active and where it is stored:

```sql
SHOW VARIABLES LIKE '%general%';
```

Look for the `general_log` variable to see if it is enabled (`general_log = ON`). The `general_log_file` variable shows the file path where entries are saved.

To permanently enable these MariaDB logs, edit the configuration file again:

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

In the `[mysqld]` section, add the following lines:

```ini
general_log = 1
general_log_file = /var/log/mariadb/general-query.log
```

This turns on general query logging and defines the file location for storing the log entries. Restart the MariaDB service so the new settings come into effect:

```bash
sudo systemctl restart mariadb
```

You can then view the log with:

```bash
sudo cat /var/log/mariadb/general-query.log
```

The file contains all SQL statements and connection attempts, including timestamps. This makes it easy to track precisely when and which queries were executed.

### Step 3: Enable the binary log

To configure the Binary Log, open the same configuration file as before:

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

Add the following parameters in the `[mysqld]` section:

```txt
log_bin = /var/log/mariadb/binary.log
server_id = 1
binlog_format = ROW
```

The `server_id` is required for replication, and every server must have a unique ID. The `binlog_format = ROW` setting records every change at the row level, which ensures accurate replication of data changes.

Restart MariaDB to apply the configuration:

```bash
sudo systemctl restart mariadb
```

Then check the binary log is active:

```sql
SHOW BINARY LOGS;
```

The active binary log files will then appear there with filenames and size information.

To view the contents of a binary log file, use:

```bash
sudo mysqlbinlog /var/log/mariadb/binary.000001
```

This shows a chronological list of all recorded changes in the log.

### Step 4: Enable the slow query log

Edit the configuration file again

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

Add the following:

```txt
slow_query_log = 1
slow_query_log_file = /var/log/mariadb/slow-query.log
long_query_time = 10
```

`long_query_time = 10` means that any query taking longer than 10 seconds will be recorded.

Then, restart MariaDB:

```bash
sudo systemctl restart mariadb
```

Check the status of the slow query log:

```sql
SHOW VARIABLES LIKE '%slow_query_log%';
```

Run a deliberately slow query to test the log:

```sql
SELECT SLEEP(12);
```

This forces the server to wait for 12 seconds, so it should appear in the slow query log.

Finally, view the log:

```bash
sudo cat /var/log/mariadb/slow-query.log
```

You will see details such as timestamps, duration of the query and the SQL statement that was executed.


This is a markdown version of: [https://www.ionos.co.uk/digitalguide/hosting/technical-matters/mariadb-logs/](https://www.ionos.co.uk/digitalguide/hosting/technical-matters/mariadb-logs/) for AI/LLM consumption.