Master MariaDB: Step-by-Step Installation, Core Commands, and User Management

1. Installation Methods and Steps

MariaDB can be installed using RPM packages, yum, binary packages, or source compilation. This tutorial follows the binary package approach.

Download and extract the desired version: tar xf mariadb-5.5.48-linux-86_64.tar.gz -C /usr/local/ Create a symbolic link, set ownership, and prepare the data directory:

cd /usr/local

ln -sv mariadb-5.5.48 mysql

chown -R root.mysql mysql

groupadd -r mysql

useradd -r -g mysql mysql

mkdir -pv /mydata/data

chown -R mysql.mysql /mydata/data

Provide configuration and startup scripts:

cp /usr/local/mysql/support-files/my-large.cnf /etc/my.cnf

cp /usr/local/mysql/support-files/my.server /etc/init.d/mysqld

chkconfig --add /etc/init.d/mysqld

chkconfig mysqld on

Add essential settings to /etc/my.cnf:

datadir=/mydata/data

skip-name-resolve=ON

innodb-file-per-table=ON

Initialize and start the database:

/usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/mydata/data

service mysqld start

Secure the installation:

mysql_secure_installation

2. MariaDB Basics

Configuration file search order: /etc/my.cnf → /etc/mysql/my.cnf → ~/.my.cnf Command‑line client options: -u username (default root) -h host (default localhost)

-p password

-D database_name

(select database) -e "SQL statement" (execute directly)

Example:

mysql -uroot -hlocalhost -p -e "show databases;"

3. Server Commands

Data Definition Language (DDL) manages database objects such as schemas, tables, indexes, views, users, and stored procedures. Common commands:

create database db_name;

alter database db_name;

drop database db_name;

Show supported character sets and collations:

show character set;

show collation;

Table operations (create, alter, drop, add column, modify column, add primary key, add index, etc.) are demonstrated with examples throughout the guide.

Data Manipulation Language (DML) handles data within tables.

Insert: insert into students (id,name) values (1,'alren'); Select: select * from students where id=1; Update: update students set age=age+10 where name like '%lren'; Delete:

delete from students where id>10;

4. Creating Users and Managing Privileges

Create a user account: create user 'username'@'host' identified by 'password'; Delete a user: DROP USER 'username'@'host'; Grant privileges:

grant ALL on db_name.* to 'username'@'host' identified by 'password';

Privilege scopes: *.* – all databases, all tables db_name.* – all tables in a specific database db_name.tbl_name – a specific table

Test the granted privileges by logging in remotely and performing allowed operations.

Revoke privileges when needed:

revoke INSERT, UPDATE on db_name.* from 'username'@'host';