Home

Welcome to the Database wiki!

Initialization (will be moved)

Step 1 - Installing MariaDB

Install the package:

sudo apt install mariadb-server

Step 2 - Configuring MariaDB

Ensure that MariaDB is running with the systemctl start command.

sudo mysql_secure_installation

No need to set root password, then I recommend choosing yes to all the following options.

Step 3 - Creating an Admin user

sudo mariadb

GRANT ALL ON *.* TO '<username>'@'localhost' IDENTIFIED BY '<password>' WITH GRANT OPTION;

FLUSH PRIVILEGES;

exit

Useful MariaDB commands

Login

Login with the following command:

mysql -u <username> -p

From a remote location:

mysql -u <username> -p -h <ip_adress>

List the databases

All the databases:

SHOW DATABASES;

Only the created (unrequired) databases:

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

Create a new database

CREATE DATABASE <db_name>;

Select a database

USE <database>;

Show the tables

Tables list:

SHOW tables;

Table configuration:

DESCRIBE <table_name>

Use files

Create the tables from an SQL file

mysql -u <username> -p < <filename.sql>

OR

myslq -u <username> -p

use <db_name>;

source <filename.sql>;

Backup and restore a database

mysqldump -u <username> -p <db_name> > <db_backup_name>.sql

gunzip <db_backup_name>.sql | mysqldump -u <username> -p <db_name>

Backup and restore a table

mysqldump -u <username> -p <table1_name> <table2_name> | gzip > <table_backup_name>.sql.gz

gunzip < <table_backup_name>.sql.gz | mysql -u <username> -p <table_name>