MariaDB – common commands

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

  1. Login to MariaDB:

    mysql -u [username] -p
    

    (You will be prompted for the password.)

  2. Specify a database while logging in:

    mysql -u [username] -p [database_name]
    
  3. Exit MariaDB:

    exit;
    

    or

    quit;
    

Database Management

  1. Show all databases:

    SHOW DATABASES;
    
  2. Create a new database:

    CREATE DATABASE [database_name];
    
  3. Use a specific database:

    USE [database_name];
    
  4. Delete a database:

    DROP DATABASE [database_name];
    

Table Management

  1. Show all tables in the current database:

    SHOW TABLES;
    
  2. Create a table:

    CREATE TABLE [table_name] (
        column1 datatype,
        column2 datatype,
        ...
    );
    
  3. Describe table structure:

    DESCRIBE [table_name];
    

    or

    SHOW COLUMNS FROM [table_name];
    
  4. Delete a table:

    DROP TABLE [table_name];
    
  5. Rename a table:

    RENAME TABLE [old_table_name] TO [new_table_name];
    

Data Management

  1. Insert data into a table:

    INSERT INTO [table_name] (column1, column2, ...) VALUES (value1, value2, ...);
    
  2. Select data from a table:

    SELECT * FROM [table_name];
    

    (You can also specify columns or use conditions like WHERE.)

  3. Update data in a table:

    UPDATE [table_name] SET column1 = value1 WHERE condition;
    
  4. Delete data from a table:

    DELETE FROM [table_name] WHERE condition;
    

User Management

  1. Create a new user:

    CREATE USER '[username]'@'[host]' IDENTIFIED BY '[password]';
    
  2. Grant privileges to a user:

    GRANT ALL PRIVILEGES ON [database_name].* TO '[username]'@'[host]';
    

    For all databases:

    GRANT ALL PRIVILEGES ON *.* TO '[username]'@'[host]';
    
  3. Show user privileges:

    SHOW GRANTS FOR '[username]'@'[host]';
    
  4. Revoke privileges from a user:

    REVOKE [privileges] ON [database_name].* FROM '[username]'@'[host]';
    
  5. Delete a user:

    DROP USER '[username]'@'[host]';
    

Backup and Restore

  1. Export a database:

    mysqldump -u [username] -p [database_name] > [filename].sql
    
  2. Import a database:

    mysql -u [username] -p [database_name] < [filename].sql
    

Performance and System Info

  1. Show server status:

    SHOW STATUS;
    
  2. Show process list:

    SHOW PROCESSLIST;
    
  3. Kill a query:

    KILL [thread_id];
    
  4. Check server version:

    SELECT VERSION();
    
  5. Show current database:

    SELECT DATABASE();
    

Miscellaneous

  1. Show currently logged-in user:

    SELECT USER();
    
  2. Check current date and time:

    SELECT NOW();
    
  3. 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.

Scroll to Top