Managing MariaDB with command-line

In this article, we explain the basics of managing MariaDB using command-line (e.g. on VPSs running Ubuntu, Debian, or CentOS). Would you like to install MariaDB first? In our tutorials below we explain the installation and basic configuration for various OSs.

• Installing MariaDB in Ubuntu 16.04 or 18.04
• Installing MariaDB in Debian 9
• Installing MariaDB in CentOS 7

In order to manage MariaDB using command-line you make use of a SQL shell. The SQL shell is an interactive interface which is called upon using via command-line with the command:

mysql -u root -p

-u stands for user, root is the user with which your starting the SQL shell and -p ensures you're prompted for a password.

Commonly used commands

After starting the SQL shell you can create and edit databases and their content. Below follow several examples of commands for creating and editing databases.

• Creating a database:

  CREATE DATABASE yourdatabase;

  Replace 'yourdatabase' with the name of the database that you want to create.
   
• Creating a user:

  CREATE USER 'username'@'LOCALHOST' IDENTIFIED BY 'password';

  Replace 'username' with the desired username and 'password' with the desired password for that user.
   
• Granting permissions/rights to a database:

  Insert your awesome code here!

  Replace 'ALL' with the rights you want to give, for example, 'SELECT'. A full overview can be found on this page.
  Replace 'yourdatabase' with the name of the desired database (or * for all databases) and 'username' with the username that you want to give access to.
   
• Applying changes to permissions / creating new users
  When you create a new user, or change permissions, these changes are only applied after using the following command:

  FLUSH PRIVILEGES;

• Show the status of MariaDB:

  STATUS;

• Selecting a database:

  USE yourdatabase;

  Where you replace 'yourdatabase' with the actual name of the database. Selecting a database is a necessary step before you can actually make changes in a database. Only after this step, can you use the commands that follow.
   
• Creating a table:
  We explain the creation of a table based on an example:

  CREATE TABLE movielist (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(40), year INT);

  In this example, you create a table with the name 'movielist' in the database you selected with the previous command (SELECT yourdatabase;). It contains three columns which respectively numbers the list (id), contains film titles (title) and year of publication (year) .
   
  • INT : stands for 'integer' and indicates that the corresponding column consists of numbers.
  • NOT NULL : the column's value may not be empty. You're free to use this for each column (e.g. title VARCHAR(40) NOT NULL).
  • AUTO_INCREMENT : After adding a new value to the table (e.g. a movie title and year), the value of the number that's added in the id-column is automatically increased by 1.
  • PRIMARY KEY : indicates that the ID column is a unique identifier for each record (i.e. each row) in the table.
     
• Adding data to a table:

  INSERT INTO movielist (id, title, year) VALUES (1, 'The Avengers: Endgame', 2019);
  INSERT INTO movielist (id, title, year) VALUES (2, 'Star Trek', 2009);

  Adds a new row to the movielist database, where in the first command the id of this specific row of data is 1, the title is 'The Avengers: Endgame' and the year of publication is 2019.
   
• Showing data from a table:

  SELECT * FROM movielist;

  Shows the entire contents of the movielist table. Replace * with the name of one column to show only that column, or use for the content of a specific row, for example:

  SELECT title FROM movielist WHERE id=2;

• Removing databases:

  DROP DATABASE yourdatabase;

  Replace 'yourdatabase' with the name of the database you're removing.
   
• Removing a table:

  DROP TABLE yourtable
  

  Replace 'yourtable' with the name of the table you'd like to delete.
   
• Removing data from a table:
  When removing data, it's easies to do so per row. You remove the 2nd row in a table for example using:

  DELETE FROM yourtable WHERE id=2;

  Here we assume that the name of the first column in your table is called 'id'.
  
  Would you like to remove multiple rows? The quickest method is to remove multiple rows with the same properties. Let's say that in our earlier example you'd like to remove all movies from 2009 from the movielist table, you'd use:

  DELETE FROM movielist WHERE year LIKE '2009'; 

  You can also use wildcards in the search criterium behind 'LIKE'.

There are of course more commands possible. Are there any you'd like us to explain in more detail? Let us know by leaving a message under 'Comments'.

Should you have any questions left regarding this article, do not hesitate to contact our support department. You can reach them via the ‘Contact Us’ button at the bottom of this page.