MariaDB is an open source RDBMS released under GPL License. Currently there are millions of MariaDB installations across the world. MariaDB’s popularity is due to its speed, security and scalability and capability to handle massive data sets. This article discusses about basic to intermediate MariaDB Administration tasks. More details are available at the MariaDB official knowledgebase.

MariaDB is available for installation in the form of tarballs and binaries for Linux, Solaris and Windows. There are packages for Linux distributions also available at MariaDB Downloads website. MariaDB can also be upgraded from an existing MySQL installation. There are various clients and utilities available for MariaDB configuration, optimization and administration. Some important categories of these clients and utilities are:

  1. mysql command line client (SQL shell)
  2. Aria table clients and utilities
  3. Backup, restore and import clients
  4. MyISAM clients and utilities
  5. EXPLAIN Analyzer
  6. innochecksum
  7. mysqladmin
  8. mysqlaccess
  9. mysqlbinlog
  10. mysqlcheck
  11. mysqldumpslow
  12. mysql_install_db
  13. mysql_plugin
  14. mysqlreport
  15. mysql_secure_installation
  16. mysql_setpermission
  17. mysqlshow
  18. mysqlslap
  19. mysql-test
  20. mysql_upgrade

Additionally Graphical and Enhanced clients are also available. A few of them are:

Database Workbench, dbForge Studio for MySQL, HeidiSQL, JPDB Admin for MariaDB, MONyog and SQLyog, mycli, phpMyAdmin, Sequel Pro, DBeaver, TOAD for MySQL, etc.

Starting and Stopping MariaDB

The MariaDB Server Binary is called mysqld. There are many ways to start it.

1. Starting from the source directory:

shell > ./mysqld &

2. Use the startup script mysqld_safe, from the MariaDB installation directory:

shell > mysqld_safe & mysqld_safe starts mysqld.

3. Use the mysql.server startup script located on the /etc/init.d directory to start/stop mysqld:

mysql.server start mysql.server stop

Once the server is started, the client utility mysql can be used to perform various tasks. To start mysql client and connect to a database:

shell > mysql --user=user_name --password=your_password db_name

Once mysql client starts running the command line prompt changes to mysql >, where you can execute SQL queries.

mysql> SELECT * FROM users where user_name = ‘Robert’

MariaDB User Account Management

1. Create a user from a host 180.180.180.180 if already does not exist:

CREATE USER IF NOT EXISTS ‘sam’@ ‘180.180.180.180’ IDENTIFIED BY 'password';

Query OK, 0 rows affected (0.00 sec)

Valid host names include:

‘sam’@ ‘localhost’ – local server only
‘sam’@ ‘%’ – any host
‘sam’@ ‘190.190.190.%’ – any host from the network mask 190.190.190.0
‘sam’@ ‘ip_addr’
‘sam’@ ‘host1’ – from the host – host1

2. Create a user, replace if already exists:

CREATE OR REPLACE USER ‘sam’@ ‘localhost’ IDENTIFIED BY 'password';

Query OK, 0 rows affected (0.00 sec)

3. Change or alter a user

To force a user to use SSL connections:

ALTER USER sam IDENTIFIED BY 'password' REQUIRE SSL;

Set or restrict connection variables for a user:

ALTER USER sam WITH MAX_QUERIES_PER_HOUR 10
MAX_UPDATES_PER_HOUR 20
MAX_CONNECTIONS_PER_HOUR 30
MAX_USER_CONNECTIONS 40;

4. Change password

SET PASSWORD FOR 'sam'@'%.loc.gov' = PASSWORD('newpassword');

5. Rename a user

RENAME USER 'donald' TO 'tom'@'localhost';

6. Grant/Revoke privileges for the user

Granting all privileges:

CREATE USER 'bob'@'localhost'; GRANT ALL PRIVILEGES ON *.* to 'bob'@'localhost' WITH GRANT OPTION;

There are different levels of privileges from Global to Procedure privileges.

  1. Global privileges
  2. Database privileges
  3. Table privileges
  4. Column privileges
  5. Function privileges
  6. Procedure privileges

The ALL PRIVILEGES privilege grants all privileges. The clause WITH GRANT OPTION gives the user ability to grant privileges for other user.

Giving all privileges on a specific database db1 only.

GRANT ALL PRIVILEGES ON db1.* to 'bob'@'localhost;

7. Revoke privileges from a user

REVOKE ALL PRIVILEGES FROM sam;

8. Remove a user

DROP USER IF EXISTS bob;

Query OK, 0 rows affected (0.00 sec)

After all user management operations, the privileges table needs to be reloaded:

FLUSH PRIVILEGES;

MariaDB Backup and Restore

The mysqldump client utility can be used for backup and restore.

Backup to a sql file:

shell> mysqldump db_name > backup-file.sql

Restore the backup

shell> mysql db_name < backup-file.sql

Creating/Selecting/Dropping Databases

create database mydb; use mydb; drop database m
Post Views: 7,679

Leave a Reply