Skip to content

MariaDB Tutorial

Setup

MariaDB, the database i am using currently, is a fork of MySQL, and drop in compatible. To install it on your linux machine, you need to follow those steps.

  1. Install mariaDB server and client on your machine.
# Arch Linux
sudo pacman -Ss mariadb
mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

# Ubuntu
sudo apt update
sudo apt install mariadb-server
  1. It is recommended that in a production environment you run this command, which will go through some steps to increase the security of your mariaDB install.
sudo mysql_secure_installation
  1. Now you can start and/or enable the mariadb service
sudo systemctl start mariadb
# and/or
sudo systemctl enable mariadb

Commands

Flush privileges

The flush privileges command ist used to tell the server to reload the grant tables.

Privileges assigned through GRANT option do not need FLUSH PRIVILEGES to take effect - MySQL server will notice these changes and reload the grant tables immediately.

From MySQL documentation:

If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE, your changes have no effect on privilege checking until you either restart the server or tell it to reload the tables. If you change the grant tables directly but forget to reload them, your changes have no effect until you restart the server. This may leave you wondering why your changes seem to make no difference!

To tell the server to reload the grant tables, perform a flush-privileges operation. This can be done by issuing a FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges or mysqladmin reload command.

If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.

Access MySQL

sudo mysql
# or
mysql -u user -p password

Create database

CREATE DATABASE [database_name];

Show databases

SHOW DATABASES;

Select database

USE [database_name];

Show all users, hosts, passwords

SELECT user,host,authentication_string FROM mysql.user;

Create user

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

Grant privileges on user

The newly created user does not have any privilege on any database. In fact, you would not be able to access the MySQL shell either. Then, the root user has to grant certain privileges to this new user.

There are several privileges, and their assignment will depend on the server administrator. Some of them are:

  • ALL PRIVILEGES: Assigns all permissions on the chosen database.
  • CREATE: This privilege allows you to create new tables or databases.
  • DROP: In this case, it allows you to delete tables and databases.
  • DELETE: It allows deleting records from the tables.
  • INSERT: With this privilege, you can create records in the tables.
  • SELECT: This privilege is required to read the records from the tables.
  • UPDATE: It allows you to update the records of a table.
  • GRANT OPTION: With this privilege, the user can remove privileges from certain users.

The syntax for granting a privilege is as follows:

GRANT [privilege1, privilege2] ON [database].[table] TO '[user]'@'localhost';

For example:

GRANT CREATE ON test.* TO 'user'@'localhost';

In this case, the user can only create tables in the database called test.

On the contrary, if you want to grant all permissions on all tables in a single call for the test database, the best option is ALL PRIVILEGES. This is an example:

GRANT ALL PRIVILEGES ON test.* TO 'user'@'localhost';

Delete user

DROP USER 'user'@'localhost';

See current user grants

SHOW GRANTS;

See grants for specific user

SHOW GRANTS FOR 'user'@'localhost';

Change password

Changing a MySQL user’s password is a task handled by the database administrator (root).

Once the MySQL user’s password is changed, you should update the user’s privileges.

  1. Log into MySQL server as root
$ mysql -u root -p
  1. Use ‘mysql’ database
USE mysql;
  1. Change password for user John
update user set password=PASSWORD('NEW-PASSWORD-HERE') where User='John';
  1. Reload privileges
flush privileges;

Make table

CREATE TABLE tablename (entry1 datatype, entry2 datatype, ...);
# f.e.
CREATE TABLE employees (employee_nr int(4) NOT NULL, ame varchar(20), gender ENUM('M','F'), birthday DATE);

Set primary key

When creating a table, you should also create a column with a unique key for each record.

This can be done by defining a PRIMARY KEY.

We use the statement "INT AUTO_INCREMENT PRIMARY KEY" which will insert a unique number for each record. Starting at 1, and increased by one for each record.

Create primary key when creating the table:

CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255));

Add primary key to existing table:

ALTER TABLE employees ADD CONSTRAINT employee_nr_pk PRIMARY KEY (employee_nr);

employee_nr_pk is the name of the primary key.

Note: Primary keys can consist of multiple columns, f.e. first and last name.

Alter table

ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

Delete table

DROP TABLE table_name;

Write to table

INSERT INTO table_name (column1, column2, column3,..) VALUES ( value1, value2, value3,..);

Read from table

Read specific columns from table:

SELECT column1, column2, ... FROM table_name;

Read everything from table:

SELECT * FROM table_name;

Read from table with condition

SELECT * FROM table_name WHERE [condition]

For example, if I wanted to read all the fields where the id is 1, I would use the following statement:

SELECT * FROM employees WHERE Id=1;

Update data

You may have inserted an incorrect data, so you need to update it. To do this, we need to use the Update statement.

First, you need to know which record needs to be updated; it is necessary to add a small condition. Otherwise, it will update all records. So you need to be careful.

The basic syntax is as follows:

UPDATE table_name SET column1 = value1, column2 = value2 WHERE [condition];

Continuing with the previous example, if you want to update the “last_name” column of the data whose Id is “1”, you would do it in the following way:

UPDATE employees SET last_name = 'Clark' WHERE Id=1;

Delete data

DELETE FROM table_name WHERE condition;

f.e.

DELETE from employees WHERE Id=1;

Import database

MySQL allows you to import a database backup in SQL format. It is a great advantage if we want to move a database to a new server.

The first requirement is to create a database where the SQL file will be imported.

CREATE DATABASE example2;

Then, we can exit the console using the following command:

exit;

After that, we can start importing the file with the following command as the root user:

sudo mysql -u username -p [database] < [sql_file_path]

Note that it is necessary to write the full path where the SQL file is located. In our case, the file “file.sql” is located on the home folder.

Export database

If MySQL can import a database, then it can also export a database to an SQL file.

To do this, without being in the MySQL console, we run the following command:

sudo mysqldump -u username -p database_to_export > file.sql