Here’s a MariaDB FAQ (Frequently Asked Questions) covering common topics and queries related to installation, usage, and troubleshooting, updated for MariaDB 11.4+:
MariaDB is an open-source relational database management system (RDBMS) that is a fork of MySQL. It was created by the original developers of MySQL after concerns about Oracle’s acquisition of MySQL in 2010. It is fully compatible with MySQL in terms of functionality, but MariaDB offers new features, storage engines, and performance improvements. With MariaDB 11.4+, security is enhanced by default with SSL enabled automatically.
While MariaDB started as a fork of MySQL and remains mostly compatible, it has diverged by offering:
You can check the version of MariaDB using the following command:
mariadb --version
# or
mysql -V
Or from within the MariaDB shell:
SELECT VERSION();
Yes, MariaDB is a drop-in replacement for MySQL. It is designed to be fully compatible with MySQL in terms of features, functionality, and tools, so you can generally migrate from MySQL to MariaDB without any issues.
For basic usage, MariaDB requires:
You can install MariaDB on Debian/Ubuntu-based systems using:
sudo apt update
sudo apt install mariadb-server
For other systems, such as Windows or Red Hat-based Linux, refer to the official installation documentation.
On Linux systems that use systemd:
sudo systemctl start mariadb
sudo systemctl stop mariadb
To enable MariaDB to start automatically on boot:
sudo systemctl enable mariadb
To change the root password, first log in as root:
sudo mariadb -u root
# or for newer versions with secure root account
mariadb -u root -p
Then change the password:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;
Alternatively, you can use mysql_secure_installation to reset the root password.
Yes! MariaDB 11.4+ has enhanced security by default:
--ssl-verify-server-cert enabledYou can secure your MariaDB by:
mysql_secure_installation.Starting with MariaDB 11.4, SSL is enabled by default with automatic self-signed certificate generation. For production environments with custom certificates:
mariadb.conf.d/50-server.cnf file:[mysqld]
ssl-ca = /etc/mysql/certs/ca-cert.pem
ssl-cert = /etc/mysql/certs/server-cert.pem
ssl-key = /etc/mysql/certs/server-key.pem
ssl-cipher = 'TLSv1.2'
require_secure_transport = ON
sudo systemctl restart mariadb
User privileges in MariaDB are managed using the GRANT and REVOKE commands:
GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost';
REVOKE ALL PRIVILEGES ON database_name.* FROM 'user'@'localhost';
MariaDB 11.4+ introduces several security enhancements:
--ssl-verify-server-cert enabledIf you’ve lost the root password, follow these steps:
sudo systemctl stop mariadb
sudo mysqld_safe --skip-grant-tables &
mariadb -u root
UPDATE mysql.global_priv SET priv=json_set(priv, '$.auth.plugin', 'mysql_native_password', '$.auth.password', PASSWORD('new_password')) WHERE User='root';
FLUSH PRIVILEGES;
sudo systemctl restart mariadb
You can check the status of MariaDB using the systemd command:
sudo systemctl status mariadb
This will display whether the service is active, running, or if there are any errors.
To recover a corrupted table, you can try the following steps:
CHECK TABLE command to diagnose the corruption:CHECK TABLE table_name;
REPAIR TABLE table_name;
Alternatively, you can use the myisamchk tool for MyISAM tables or innodb_force_recovery in the configuration for InnoDB tables.
To enable general query logging:
/etc/mysql/mariadb.conf.d/50-server.cnf):[mysqld]
general_log = 1
general_log_file = /var/log/mysql/query.log
sudo systemctl restart mariadb
The default storage engine for MariaDB is InnoDB. InnoDB is highly reliable and supports transactions, foreign keys, and crash recovery. However, MariaDB supports other engines, such as Aria, TokuDB, and MyISAM.
To improve MariaDB performance:
innodb_buffer_pool_size to 70-80% of available RAM if dedicated).Enable slow query logging by adding the following lines to the configuration file:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1.0
log_queries_not_using_indexes = 0
This will log all queries that take longer than 1 second to execute.
MariaDB 11.4+ includes several performance improvements:
To upgrade from MariaDB 10.11 to 11.4:
mariadb-dump -u root -p --all-databases > backup.sql
mariadb-upgrade after starting the service to update system tablesWhen migrating from MySQL to MariaDB: