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
