MariaDB, the popular open source database software can be installed and run various types of servers like: standalone machine, virtual machines, cloud, containers etc. It is available with all major Linux distributions such as Debian and Ubuntu. Furthermore it is the default database in RedHat Linux, CentOS, Fedora, openSUSE, SUSE Linux Enterprise etc. The latest tar and binary downloads for Linux, Solaris and Windows are available at the Official MariaBD Download site .

This article discusses about the essential post-installation administrative tasks for managing MariaDB Server effectively.

The MariaDB Server

MariaDB Server binary is named as mysqld to make it compatible with upstream MySQL. There are different methods for running/starting MariaDB:

Running from the Source or Build Directory Use the startup script – mysqld_safe Use the startup script – mysql.server

Once mysqld is up and running the administrator process mysqladmin can be used for performing various administrative tasks. Some common tasks are:

Monitor the client processes.

Start/Stop/Restart Slave processes.

Create and Drop databases.

Read MariaDB system variables.

Flush logs, privileges and tables.

Terminate query threads.

Shutdown mysqld server.

Ping the mysqld server to check its status

The syntax for invoking mysqladmin is:

mysqladmin [options] command [command-arg] [command [command-arg]] ...

mysqladmin Options

The options are either specified along with the mysqladmin command or are specified in the configuration files: my.ini (Windows) and my.cnf (Linux).

While my.ini will be located in the Source/Build directory in Windows, that’s path is added to the PATH environment variable, my.ini can be in or looked up in the order: /etc/my.cnf, /etc/mysql /my.cnf, /usr/etc or ./ my.cnf.

-C, –compress Compress the server – client communication –debug-info Print debug info, CPU and Memory usage statistics -f, –force Execute commands/multiple commands without asking for confirmation, even in the case of errors. -?, –help Display help and exit -h hostname, –host=name Name of the host machine to connect to -p[password], –password[=password] Password for the user who runs the connection. If password is not specified, it is asked at the console -P port_num, –port=port_num Port number to which connection is needed (0 – default, 3306 – built-in default) –protocol=name Name of connection protocol – tcp, socket, pipe, memory –ssl Enable SSL connection -u, –user=user_name User name for login -V, –version Display version information -v, –verbose Write more information about an operation

mysqladmin Commands

Important mysqladmin commands are shown below:

Edit create db_name Create new database drop db_name Delete database flush-logs Flush all the logs flush-privileges Flush user privileges (reload grant tables), issued typically after a privilege grant/revoke operation flush-slow-log Flush slow query log kill id, id, … Kill specified mysql threads password new_password Change password shutdown Stop and shutdown mysqld server status Display server status message (short version) start-all-slaves Start all slaves stop-all-slaves Stop all slaves start-slave Start replication on a slave stop-slave Stop replication on a slave

Example :

Querying for Server status

shell> mysqladmin status

Uptime: 8023 Threads: 1 Questions: 14 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.1

shell> mysqladmin processlist

+—-+——-+———–+—-+———+——+——-+——————+

| Id | User | Host | db | Command | Time | State | Info |

+—-+——-+———–+—-+———+——+——-+——————+

….

+—-+——-+———–+—-+———+——+——-+——————+

MySQL CLI Client – mysql

The shell utility client mysql is used for interacting with the MariaDB server using console commands.

Some example usage is shown below:

1. Connect to the server and select a database

mysql –user=user_name –password=your_password db_name

2. Export/Import a database into/from an SQL file

mysql db_name > script.sql

mysql db_name < script.sql

A full list of options can be obtained with the below command:

mysql –verbose –help

mysql Options

Some important mysql options are:

Edit -?, –help Show help -C, –compress Compress server – client communication –connect-timeout=num Specifying connection timeout -D, –database=name To select current working database -T, –debug-info Display debug info on exit -f, –force Continue without confirmation, even if there are errors -q, –quick Print output row by row –ssl Enable SSL connections -t, –table Display result in table format -u, –user=name Specifying user for login -p, –password[=name] Specifying password for the user to login. If not provided with command, console will prompt for password. -h, –host=name Specifying host to connect -P, –port=num Specifying port number to connect –protocol=name Specifying protocol used for connection (tcp, socket, pipe, memory)

Example: Connect to a database “database1” on host 100.100.100.100 by user “robert”, prompting for password.

mysql –u robert –p –h 100.100.100.100 –D database1

Once mysql command is used to connect to the server, commands can be entered inside the mysql console.

Below are some important mysql console commands: