by: Web

MySQL and MariaDB are both free, open-source Relational Databases. MariaDB is a fork of MySQL, created by MySQL’s original developers after Oracle acquired it.

The MariaDB developers aim to maintain compatibility with MySQL as much as possible, making it a great low-risk migration alternative.

BONUS KNOWLEDGE

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

  • Relational (RDB) (SQL): MySQL, MariaDB, Oracle, SQLite, MSSQL, PostgreSQL, AWS Aurora, CockroachDB…
  • Key-Value (single value per entry) (NoSQL): Redis, ElasticCache…
  • Wide Column (multiple values per entry) (NoSQL): ScyllaDB, Apache Cassandra, Apache HBase, AWS DynamoDB, Azure CosmosDB, 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 / MariaDB

Keep the following points in mind, as they often frustrate beginners:

  1. Commands may differ depending on the version of the application.
  2. Forgetting the semicolon at the end of a command will leave the prompt waiting for it.
  3. MySQL and MariaDB are not exactly the same, though they are compatible most of the time.

Before starting, check the application version:

mysql --version

OR

mysql> SELECT VERSION();

If you have MySQL 5.7.6+ or MariaDB 10.1.20+, some commands may differ from older versions.

Log in to 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 databaseName;

Managing users:

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

For newer versions:

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

For older versions:

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 versions:

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

If 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 versions:

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

Grant the privilege to manage users and permissions (Ansible example):

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

Grant the privileges required for management tools (Percona 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 logging in as root:

mysql -u root -p

ADDITIONALLY

Consider periodically running the following commands:

  • ANALYZE TABLE – analyzes the table and updates its statistics, which the MySQL optimizer uses to build query execution plans and make better decisions about how to run queries.
  • OPTIMIZE TABLE – rebuilds the table and its indexes, reclaiming unused space, defragmenting data files, repairing any corruption, and updating the same statistics as ANALYZE TABLE.

Note: these operations can be resource-intensive. Test in a sandbox environment first to avoid impacting database performance.

BONUS

Not all applications support MySQL 8 out of the box.

Follow these steps to install 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: SQLite has unique navigation commands, but its querying is very similar to MySQL.

PostgreSQL

Getting help:

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

Basic navigation and querying:

postgresql=# \l                     -- List databases
postgresql=# CREATE DATABASE abcDB;
postgresql=# \c abcDB               -- Connect 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: PostgreSQL has unique navigation commands, but its querying is very similar to MySQL.

Connecting from the command line:

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

Importing and exporting files:

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

Users, Roles, and Groups:

  • User: an account (user or service) that can log in to a database by default.
  • Role: a collection of privileges that can be assigned to users or other roles, and can optionally be configured to log in like a user.
  • Group: essentially a role that cannot log in. There is no practical advantage to using this over roles.

Schema:

  • Schema: a namespace (logical grouping) for managing database objects, helping avoid naming conflicts and control access to data.
    • Tables, functions, and other objects are created within a schema, not directly in the database (if no schema is specified, the default schema is used).
    • Objects like tables can share the same name within the same database as long as they belong to different schemas.