LLI Wiki — My Digital Knowledge Base

Install MariaDB on AlmaLinux and move the data directory

Install MariaDB with dnf, secure the installation, move datadir to another volume and adjust the server and client options.

This guide installs MariaDB on AlmaLinux, then moves the data directory to another volume. Host names and paths are generic examples.

Install MariaDB

sudo dnf update && sudo dnf upgrade -y
sudo dnf install mariadb-server -y
mysql --version
sudo systemctl start mariadb
sudo systemctl enable mariadb
sudo systemctl status mariadb
sudo mysql_secure_installation
mysql -u root -p

systemctl enable also creates mysql.service and mysqld.service aliases that point to mariadb.service.

Read the server defaults

mysqld --help --verbose
mysqld --print-defaults

Move the data directory

  1. Check the current data directory:

    SELECT @@datadir;
    
  2. Stop MariaDB, copy the data to the new volume, and keep the old directory as a backup:

    sudo systemctl stop mariadb
    sudo systemctl status mariadb
    sudo rsync -av /var/lib/mysql /mnt/datavolume
    sudo mv /var/lib/mysql /var/lib/mysql.bak
    
  3. Point the server and the clients to the new directory. In the server options file:

    [mysqld]
    datadir=/mnt/datavolume/mysql
    socket=/mnt/datavolume/mysql/mysql.sock
    

    In the client options file:

    [client]
    port=3306
    socket=/mnt/datavolume/mysql/mysql.sock
    
  4. Start MariaDB and check the new location:

    sudo systemctl start mariadb
    sudo systemctl status mariadb
    
    SELECT @@datadir;
    

Rotate the MySQL logs

The following logrotate configuration, in /etc/logrotate.d/mysqld, rotates the general, slow query and error logs daily and keeps five archives:

/var/log/mysql/general.log
/var/log/mysql/slow_query.log
/var/log/mysql/mysqld.log {
    # create 600 mysql mysql
    notifempty
    daily
    rotate 5
    missingok
    compress
    postrotate
        # just if mysqld is really running
        if test -x /usr/bin/mysqladmin && \
           /usr/bin/mysqladmin ping &>/dev/null
        then
           /usr/bin/mysqladmin flush-logs
        fi
    endscript
}

Check the sql_mode

The sql_mode variable controls validation rules such as ONLY_FULL_GROUP_BY. To view the current value:

SELECT @@GLOBAL.sql_mode;

Notes

Mastodon LLIMastodon SocialFosstodon