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 |
| 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 |
| -?, –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) |
| 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 |