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:

  1. Running from the Source or Build Directory
  2. Use the startup script – mysqld_safe
  3. 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:
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:
-?, –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 by user “robert”, prompting for password.
mysql –u robert –p –h –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:
clear, \c Erase the current input
exit, \q, quit, \q Exit from current connection and mysql
help, \h Display help
source file_name.sql, \. file_name Execute the SQL file
status, \s Display server status info
use database_name, \u database_name Select another database

Leave a Reply