MariaDB Commnads
Starting and Stopping MariaDB
On Linux (using systemd
or service
):
Start MariaDB:
sudo systemctl start mariadb
or
sudo service mariadb start
Stop MariaDB:
sudo systemctl stop mariadb
or
sudo service mariadb stop
Restart MariaDB:
sudo systemctl restart mariadb
or
sudo service mariadb restart
Enable MariaDB to start on boot:
sudo systemctl enable mariadb
Check MariaDB status:
sudo systemctl status mariadb
On macOS (using Homebrew):
Start MariaDB:
brew services start mariadb
Stop MariaDB:
brew services stop mariadb
Restart MariaDB:
brew services restart mariadb
**
General Commands
Login to MariaDB:
mysql -u [username] -p
(You will be prompted for the password.)
Specify a database while logging in:
mysql -u [username] -p [database_name]
Exit MariaDB:
exit;
or
quit;
Database Management
Show all databases:
SHOW DATABASES;
Create a new database:
CREATE DATABASE [database_name];
Use a specific database:
USE [database_name];
Delete a database:
DROP DATABASE [database_name];
Table Management
Show all tables in the current database:
SHOW TABLES;
Create a table:
CREATE TABLE [table_name] ( column1 datatype, column2 datatype, ... );
Describe table structure:
DESCRIBE [table_name];
or
SHOW COLUMNS FROM [table_name];
Delete a table:
DROP TABLE [table_name];
Rename a table:
RENAME TABLE [old_table_name] TO [new_table_name];
Data Management
Insert data into a table:
INSERT INTO [table_name] (column1, column2, ...) VALUES (value1, value2, ...);
Select data from a table:
SELECT * FROM [table_name];
(You can also specify columns or use conditions like
WHERE
.)Update data in a table:
UPDATE [table_name] SET column1 = value1 WHERE condition;
Delete data from a table:
DELETE FROM [table_name] WHERE condition;
User Management
Create a new user:
CREATE USER '[username]'@'[host]' IDENTIFIED BY '[password]';
Grant privileges to a user:
GRANT ALL PRIVILEGES ON [database_name].* TO '[username]'@'[host]';
For all databases:
GRANT ALL PRIVILEGES ON *.* TO '[username]'@'[host]';
Show user privileges:
SHOW GRANTS FOR '[username]'@'[host]';
Revoke privileges from a user:
REVOKE [privileges] ON [database_name].* FROM '[username]'@'[host]';
Delete a user:
DROP USER '[username]'@'[host]';
Backup and Restore
Export a database:
mysqldump -u [username] -p [database_name] > [filename].sql
Import a database:
mysql -u [username] -p [database_name] < [filename].sql
Performance and System Info
Show server status:
SHOW STATUS;
Show process list:
SHOW PROCESSLIST;
Kill a query:
KILL [thread_id];
Check server version:
SELECT VERSION();
Show current database:
SELECT DATABASE();
Miscellaneous
Show currently logged-in user:
SELECT USER();
Check current date and time:
SELECT NOW();
Flush privileges (apply changes):
FLUSH PRIVILEGES;
These commands cover common tasks for interacting with MariaDB databases. You can use HELP;
in the MariaDB CLI for more options.
Should You Install phpMyAdmin or Use TablePlus?**
phpMyAdmin
Pros:
- Web-based, so you can manage your database from anywhere via a browser.
- Great for shared hosting environments or remote servers.
- Offers a graphical interface for SQL queries, backups, and user management.
- Easy to set up if you're already running a web server (Apache or NGINX + PHP).
Cons:
- Slightly outdated interface compared to modern apps.
- Security risks if not properly configured (e.g., exposing it to the internet without protection).
- Adds more complexity if you're already comfortable with local tools.
Ideal for:
- When you need to manage MariaDB remotely or on a shared/hosted server.
TablePlus
Pros:
- Modern, fast, and intuitive GUI for managing databases.
- Local app, so no additional server-side setup is needed.
- Supports multiple databases (MySQL, MariaDB, PostgreSQL, SQLite, etc.).
- Better performance and usability compared to phpMyAdmin.
Cons:
- Requires installation on your local machine.
- Not suitable for remote access unless you expose MariaDB to your local machine (which can pose security risks).
Ideal for:
- Local development.
- When you manage databases on your local machine or via an SSH tunnel.
Verdict
- For Local Development: Use TablePlus (or similar tools like DBeaver, HeidiSQL, or Sequel Pro if on macOS).
- For Remote Server Management: Consider phpMyAdmin, but only if you need web-based access. Otherwise, stick to CLI tools or an SSH-based GUI like TablePlus.
If you’re comfortable with CLI commands, phpMyAdmin might feel redundant. However, it can be helpful for quick backups, restores, or database browsing.