Written by 10:01 pm Cloud Infrastructure, How-tos, Show me the code

Install MariaDB on Ubuntu 20.04

A very good tutorial can be found at Digital Ocean.

Installation

First you may want to do a system update


# facilitates to manage the repositories
# that you install software from
sudo apt-get install software-properties-common

sudo apt-get update -y

And then install MariaDB.

sudo apt-get install mariadb-server  mariadb-client -y

MariaDB is installed now and you may check its version.

mysql -V

# additional check
sudo mysqladmin version

To check the MariaDB service status

sudo systemctl status mariadb

Security

Set the security options with the following

sudo mysql_secure_installation

The above will go you through a series of questions. The first prompt asks you to enter the current password for the root account of the database. There is no password yet, so you press enter.

I answer the next questions as following.

# Change the root password? [Y/n] n
# Remove anonymous users? [Y/n] Y
# Disallow root login remotely? [Y/n] n
# Remove test database and access to it? [Y/n] n
# Reload privilege tables now? [Y/n] Y

Users

You may want to add a new user, say admin, with admin privileges.

# run MariaDB in the terminal
sudo mysql -u root

CREATE USER 'admin'@localhost IDENTIFIED BY 'MyPassword';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@localhost IDENTIFIED BY 'MyPassword';

You may now want to set the password for the root database user.

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyPassword');

To exit the MariaDB session

exit;

To enter again using the root user and the password

# prompts for password
sudo mysql -u root -p

ERROR 1698 (28000): Access denied for user ‘root’@’localhost’

For this error see: Stackoverflow

On some systems, like Ubuntu, MySQL is using the Unix auth_socket plugin by default. Make it to use the mysql_native_password plugin

sudo mysql -u root -p

mysql> USE mysql;

# display users and the associated auth plugin for each user
mysql> SELECT User, Host, plugin FROM mysql.user;

# set the mysql_native_password for user root
mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

# restart the service
service mysql restart

Install DBeaver

DBeaver is a free multi-platform database tool for developers, database administrators, analysts and all people who need to work with databases. Supports all popular databases: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Apache Hive, Phoenix, Presto, etc.

# install and config java
sudo apt update
sudo apt -y install default-jdk

java -version

# add Debian repository
wget -O - https://dbeaver.io/debs/dbeaver.gpg.key | sudo apt-key add -
echo "deb https://dbeaver.io/debs/dbeaver-ce /" | sudo tee /etc/apt/sources.list.d/dbeaver.list

# update the apt list and install DBeaver CE
sudo apt update
sudo apt install dbeaver-ce

# check DBeaver version
apt policy  dbeaver-ce

Password character-set problem

Sometimes using a text editor, e.g. VSCode, creates root password with different character set tha causes problems. In order to correct the situation do the following:

  • open DBeaver. NOTE: Be sure that DBeaver > Window > Preferences > General > Workspace > Text File Encoding is set to UTF-8
  • create a new MariaDB connection, without Database and Password. Name it MariaDB
  • test and open the connection
  • go to MariaDB connection > Users folder
  • set the password of the root@localhost user to whatever is appropriate and click Save

ERPNext collation

ERPNext is a free and open source ERP base on Frappe framework.

If you’re going to install ErpNext or the Frappe framework you need to configure MariaDB’s character-set and collation.

Use a text editor to open the file /etc/mysql/my.cnf and add the following lines.

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
default-character-set = utf8mb4

Save the file and restart the MariaDB service.

service mysql restart

Tested on:

  • Ubuntu 20.04 with GNOME 3.36.8
(Visited 20 times, 1 visits today)
Share the Post
Tags: , , , , Last modified: May 6, 2022
Close