https://mariadb.com/docs/server/ref/cs10.6/cli/mariadbd/
https://www.geeksforgeeks.org/how-to-install-mysql-mariadb-in-linux/
https://mariadb.com/docs/server/deploy/topologies/single-node/community-server-10-6/
https://computingpost.medium.com/how-to-install-mariadb-10-6-on-almalinux-9-cbc419de57eb
First set up the repository
sudo yum install curl
curl -LsSO https://r.mariadb.com/downloads/mariadb_repo_setup
echo "6083ef1974d11f49d42ae668fb9d513f7dc2c6276ffa47caed488c4b47268593 mariadb_repo_setup" \
| sha256sum -c -
chmod +x mariadb_repo_setup
sudo ./mariadb_repo_setup \
--mariadb-server-version="mariadb-10.6"
Then install the community server and backup
sudo yum install MariaDB-server MariaDB-backup
vim /etc/my.cnf.d/kwantu.cnf
[server]
datadir=/usr/local/data/mysql
tmpdir=/usr/local/data/mysql_tmp
socket=/usr/local/data/mysql/mysql.sock
user=mysql
bind-address=154.0.175.99
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
character-set-server=utf8
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# Additional options
lower_case_table_names=1
vim /etc/my.cnf.d/kwantu.cnf
Here specify the first 3 lines
[server]
datadir=/usr/local/data/mysql
tmpdir=/usr/local/data/mysql_tmp
socket=/usr/local/data/mysql/mysql.sock
vim /etc/my.cnf.d/client.cnf
Here specify the first 3 lines
[client]
socket=/usr/local/data/mysql/mysql.sock
sudo systemctl edit mariadb
or
vim /etc/systemd/system/mariadb.service.d/override.conf
Add the following lines.
[Service]
# Allow writes to your data directory
ReadWritePaths=/usr/local/data/mysql /usr/local/data/mysql_tmp
# Disable PrivateTmp (to avoid isolated /tmp)
PrivateTmp=false
# Reduce ProtectSystem to "strict" (allows writes to non-core directories)
ProtectSystem=strict
Note that you need to make sure that the temp directory has been created
sudo mkdir /usr/local/data/mysql
sudo chown -R mysql:mysql /usr/local/data/mysql
sudo chmod -R 750 /usr/local/data/mysql
sudo mkdir /usr/local/data/mysql_tmp
sudo chown -R mysql:mysql /usr/local/data/mysql_tmp
sudo chmod -R 750 /usr/local/data/mysql_tmp
Then you need to reinitialise the new data directory
sudo mv /usr/local/data/mysql /usr/local/data/mysql.bak
sudo mariadb-install-db --user=mysql --datadir=/usr/local/data/mysql
sudo chown -R mysql:mysql /usr/local/data/mysql
sudo systemctl daemon-reload
sudo systemctl restart mariadb
sudo systemctl start mariadb
sudo systemctl enable mariadb
sudo systemctl status mariadb
Test that you can access it by typing mariadb. Then run the securing script
/usr/bin/mariadb-secure-installation --socket=/usr/local/data/mysql/mysql.sock
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.
You already have your root account protected, so you can safely answer 'n'.
Switch to unix_socket authentication [Y/n] n
... skipping.
You already have your root account protected, so you can safely answer 'n'.
Change the root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --reload
Open the mariadb client
mariadb -u root -S /usr/local/data/mysql/mysql.sock -p
Specify the root and kwantu user access
-- Grant remote access to root from any IP
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'your_strong_password_here';
-- Flush privileges to apply changes
FLUSH PRIVILEGES;
For a large-scale MariaDB deployment handling ~1 million records per day, you need to optimize performance, reliability, and logging while ensuring efficient resource usage. Below are recommended server configurations and logging settings for a production environment.
🔧 Recommended MariaDB Server Configuration
CPU: 8+ cores (preferably Intel/AMD with high clock speed)
RAM: 32GB+ (adjust innodb_buffer_pool_size accordingly)
Storage: NVMe SSD (for high IOPS) or fast SAS/SATA SSD in RAID 10 (avoid HDDs)
OS: Linux (Ubuntu 22.04 LTS / RHEL 8+ / Debian 11+)
[mysqld]
# General Settings
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
# Performance & Memory
innodb_buffer_pool_size = 16G # ~70-80% of total RAM
innodb_buffer_pool_instances = 8 # For multi-core scaling
innodb_log_file_size = 2G # Larger log files reduce disk I/O
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 2 # Balance durability & speed (1 for full ACID)
innodb_flush_method = O_DIRECT # Bypass OS cache for InnoDB
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 2000 # Higher for SSDs
innodb_io_capacity_max = 4000
# Query Optimization
query_cache_type = 0 # Disable query cache (often harmful in MariaDB 10+)
query_cache_size = 0
table_open_cache = 4000
thread_cache_size = 100
max_connections = 300 # Adjust based on application needs
tmp_table_size = 256M
max_heap_table_size = 256M
# Replication & High Availability (if applicable)
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW # Recommended for data consistency
sync_binlog = 1 # Durability (set to 0 for performance if needed)
expire_logs_days = 7 # Auto-clean old binary logs
[mysqld]
# Error Log (for debugging crashes/errors)
log_error = /var/log/mysql/mysql-error.log
# Slow Query Log (identify performance bottlenecks)
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2 # Log queries taking >2 sec
log_queries_not_using_indexes = 1 # Log queries without index usage
# General Log (Avoid in production unless debugging)
general_log = 0
general_log_file = /var/log/mysql/mysql-general.log
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 7
sync_binlog = 1 # Slower but safer (0 for performance)
sudo nano /etc/logrotate.d/mysql
Add:
/var/log/mysql/mysql-error.log
/var/log/mysql/mysql-slow.log
{
daily
rotate 7
missingok
compress
delaycompress
notifempty
create 640 mysql mysql
postrotate
/usr/bin/mysqladmin flush-logs
endscript
}
Use Partitioning if dealing with large tables (e.g., time-based partitioning).
Optimize Indexes (avoid over-indexing, use EXPLAIN to analyze queries).
Enable Monitoring (Prometheus + Grafana, or mytop/pt-query-digest).
Use mariabackup (Percona XtraBackup) for hot backups.
Schedule daily backups + binlog retention.
Consider Replication (Master-Slave or Galera Cluster for HA).