by: Web

MySQL and MariaDB are both free open-source Relational Databases. In fact, MariaDB is a fork of MySQL by original developers of MySQL when it was acquired by Oracle.

The intention of the MariaDB developers is to maintain compatibility to MySQL as much as possible, which makes a great low-risk alternative for migration.

BONUS KNOWLEDGE

Besides SQL and NoSQL definitions, databases can be sorted 7 database paradigms (but not limited to):

  • Relational (RDB) (SQL): MySQL, MariaDB, Oracle, SQLite, MSSQL, PostgreeSQL, AWS Aurora, CockroachDB…
  • Key-Value (single value per entry) (NoSQL): Redis, ElasticCache…
  • Wide Column (multiple values per entry) (NoSQL): ScyllaDB, Apache Cassandra, Pache HBase, AWS DynamoDB, Azure CostmosDB, Google BigTable…
  • Document (JSON format) (NoSQL): MongoDB, Firestore, CouchDB…
  • Graph (NoSQL): Neo4j, DGraph, Janus Graph…
  • Search Engine (NoSQL): ElasticSearch, Algolia, MeiliSearch…
  • Multi-model (SQL and/or NoSQL): FaunaDB, CosmosDB, MongoDB, Redis…

MANAGING MySQL / MariaSQL

Make notes of the following points that might frustrate beginners:

  1. One is when the version of the application has different commands.
  2. Another is when you forget to type the semicolon at the end (it will hand waiting for the character that ends the command).
  3. Do not forget that MySQL and MariaDB are not exactly the same but compatible (most times).

Before starting check the version of the application:

mysql --version

OR

mysql> SELECT VERSION();

If you have MySQL 5.7.6+ or MariaDB 10.1.20+ the commands may be different than if they are older than that.

Log-in the MySQL as root:

mysql -u root -p

Managing databases:

mysql> SHOW DATABASES;
mysql> CREATE DATABASE databaseName;
mysql> CREATE DATABASE IF NOT EXISTS databaseName;
mysql> DROP DATABASE databaseName;
mysql> DROP DATABASE IF EXISTS databaseNname;

Managing users:

mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
mysql> CREATE USER IF NOT EXISTS 'user'@'localhost' IDENTIFIED BY 'password';

For newer:

mysql> ALTER USER 'user'@'localhost' IDENTIFIED BY 'password';

For older:

mysql> SET PASSWORD FOR 'user'@'localhost' = PASSWORD('password');

Listing and deleting users:

mysql> SELECT user, host FROM mysql.user;
mysql> DROP USER 'user'@'localhost';
mysql> DROP USER IF EXISTS 'user'@'localhost';

Managing privileges:

mysql> GRANT ALL PRIVILEGES ON databaseName.* TO 'user'@'localhost';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';
mysql> GRANT ALL PRIVILEGES ON databaseName.tableName TO 'user'@'localhost';
mysql> GRANT SELECT, INSERT, DELETE ON databaseName.* TO 'user'@'localhost';
mysql> REVOKE ALL PRIVILEGES ON databaseName.* FROM 'user'@'localhost';
mysql> SHOW GRANTS FOR 'user'@'localhost';

If you forgot your root password:

sudo systemctl stop mysql
sudo systemctl stop mariadb
sudo mysqld_safe --skip-grant-tables &
mysql -u root

For newer:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;

In case ALTER USER does not work try:

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('password') WHERE User = 'root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;

For older:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MY_NEW_PASSWORD');
mysql> FLUSH PRIVILEGES;

Grant the privilege of manage users and grant permissions (for Ansible, in this example):

GRANT ALL ON *.* TO '_Ansible'@'%' WITH GRANT OPTION;

Grant the privileges required for management tools (for Percona, in this example):

CREATE USER 'pmm'@'%' IDENTIFIED BY '*********' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD ON *.* TO 'pmm'@'%';

Show users and grants:

SELECT user,host FROM mysql.user;
SHOW GRANTS;
SHOW GRANTS FOR user@localhost;

Restart the service:

sudo systemctl restart mysql
sudo systemctl restart mariadb

Try to enter as root now:

mysql -u root -p

ADDITIONALLY

Consider periodically executing the following commands:

  • ANALYZE TABLE – analyzes the table and updates the table statistics, which the MySQL optimizer uses to make query execution plans and influences the optimizer to make good decisions about how to execute a query.
  • OPTIMIZE TABLE – it rebuilds the table and its indexes, which can reclaim unused space + defragment the data files, check and repair any corruption in the table, and updates the statistics that ANALYZE TABLE updates.

Note: it can be resources intense. Sandbox tests and validation are necessary to prevent killing the resources of the DB.

BONUS

Unfortunately not all applications are capable of working with MySQL 8 out of the box.

Follow the installation steps for MySQL 5.7 on Ubuntu 20.04 and 22.04:

wget https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.12-1_all.deb
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys B7B3B788A8D3785C
sudo apt update && sudo apt-cache policy mysql-server
sudo apt install -f mysql-client=5.7* mysql-community-server=5.7* mysql-server=5.7* -y
sudo mysql_secure_installation

SQLite

sqlite3 database_filename.db
sqlite> .help
sqlite> .tables
sqlite> PRAGMA table_info(table_name);
sqlite> SELECT * FROM table_name;
sqlite> .quit

Note: the navigation is unique for this DB but the querying has lots of similarities to MySQL.

PostgreSQL

Gathering help:

psql --help
postgresql=# help
postgresql=# \?

Basic navigation and querying:

postgresql=# \l                     -- List databases
postgresql=# CREATE DATABASE abcDB;
postgresql=# \c abcDB               -- Connects to a DB
postgresql=# CREATE TABLE tableName ( id int NOT NULL PRIMARY KEY, column1 varchar(10) NOT NULL, column2 TIMESTAMP );
postgresql=# \d                     -- List tables
postgresql=# \dt                    -- List tables in the current schema
postgresql=# \d tableName           -- Describe table
postgresql=# INSERT INTO tableName ( column1, column2 ) VALUES ( 'ABC', NOW() );
postgresql=# SELECT * FROM tableName;
postgresql=# DROP DATABASE abcDB;

Note: the navigation is unique for this DB but the querying has lots of similarities to MySQL.

Connecting from the command line:

psql -h localhost -p 5432 -U userName -W abcDB

Importing and exporting from and to files:

postgresql=# \i importFile.sql
postgresql=# \c ( SELECT * FROM tableName ) TO 'exportFile.sql' DELIMITER ',' CSV HEADER;

Users, Roles, and Groups:

  • User: a user account or a service account that by default can login to a database.
  • Role: a collection of privileges/permissions/grants that can be assigned to users or other roles and can be set to login like a user.
  • Groups: are essentially roles that cannot login. No advantage in using this.

Schema:

  • Schema: a namespace (logical organisation) for managing database objects, helping to avoid naming conflicts and control access to data.
    • Tables, functions, and other objects are not created directly to a DB but to a schema in the database (if not specified it uses schema called the default).
    • Objects like tables can have the exact same name in the same database as long as they are in different schema.