GAMBAS BOOK 3.19.5

If you want to edit MySQL databases on your system, you will necessarily need a MySQL database server. MySQL is a well-known and widely used database system for client-server architectures.

• This description describes the installation, configuration and a proposal for an initial test of the MySQL database server for Linux Mint 20.2.
• Version 8.0.30 of the MySQL database server is used.
• Reference is only made to the documentation for the MySQL database server.
• The MySQL client `mysql` is used in a console for many administrative tasks and tests.
• MariaDB was developed as a ‘drop-in’ replacement for MySQL. This means that both software packages are functionally equivalent and interchangeable.

This allows you to determine which MySQL packages are already installed on your system. The output could look like this:

hans@mint20:~$ dpkg --list | grep mysql
ii  gambas3-gb-db-mysql 3.17.3+git202207211120... amd64  MySQL driver for the Gambas database
ii  gambas3-gb-mysql    3.17.3+git202207211120... amd64  Gambas MySQL component
ii  libmysqlclient21:amd64    8.0.30-0ubuntu0.20.04.2   amd64  MySQL database client library
ii  mysql-common 5.8+1.0.5ubuntu2 all MySQL database common files, e.g. /etc/mysql/my.cnf

The MySQL server can be installed via the application management or you can enter the following lines in a terminal:

$ sudo apt-get update
$ sudo apt-get upgrade
$ sudo apt-get install mysql-server

Good to know:
During the installation, the MySQL console client `mysql` is also automatically installed, although it is displayed as ‘not installed’ in the Linux Mint software manager.

Please note that the MySQL server has already been started at the end of the installation, which you can check with the following command in a console:

$ systemctl status mysql | grep 'Status'
  Status: "Server is operational"

During the installation of the MySQL database server (version 8), an account for the MySQL administrator root@localhost is automatically created - but no MySQL administrator password is requested.

In the following section, a password is added to the administrator account and the authentication method is changed so that a database user can also log on to the MySQL server with the MySQL client in a console.

Start the MySQL client `mysql` in a console. Enter the following command and then your system password:

$ sudo mysql

You must now set the password for the MySQL administrator root@localhost and change the MySQL authentication plugin from the standard plugin auth_socket (at least) to the plugin mysql_native_password:

$ mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<root-password>';
$ mysql> FLUSH PRIVILEGES; -- Berechtigungstabellen neu laden und Änderungen übernehmen

The traditional mysql_native_password plugin for authentication is not very secure as it only uses a hash of the password, but it is compatible with older DB drivers. Alternatively, you can switch to another plugin later, which you can explore under the link: https://dev.mysql.com/doc/refman/8.0/en/authentication-plugins.html.

Now check whether you have switched to the authentication plugin mysql_native_password:

$ mysql> SELECT user, host, plugin FROM mysql.user WHERE user='root';
+------+-----------+-----------------------+
| user | host      | plugin                |
+------+-----------+-----------------------+
| root | localhost | mysql_native_password |
+------+-----------+-----------------------+
1 row in set (0,00 sec)

mysql> quit
Bye
$

You can now log in to the MySQL server locally with your DB account (root@localhost, <root-password>) using the MySQL client programme mysql, which is no longer possible with $ sudo mysql!

The following five commands for connecting the MySQL client and MySQL server are equivalent:

$ mysql -u root -p                             ' Standard-Host ist `localhost` und Standard-Port `3306`
$ mysql -h localhost --user=root root -p
$ mysql -h localhost --port=3306 -u root -p
$ mysql --host=<hostname> --port=3306 -u root -p
$ mysql --host=<hostname> --port=3306 --user=root --password

For example, you can display the version of the installed MySQL database server:

$ mysql -h localhost -u root -p
mysql> SELECT VERSION();
+-------------------------+
| VERSION()               |
+-------------------------+
| 8.0.30-0ubuntu0.20.04.2 |
+-------------------------+
1 row in set (0,00 sec)
mysql> quit
Bye
$

The installed security script is called to secure the MySQL server installation:

$ sudo mysql_secure_installation

• Enter the root password that you previously set using the ALTER_USER_command.
• In the first step, you will be asked once whether you want to use the ‘VALIDATE PASSWORD COMPONENT’. If you answer ‘y’ at this point - which is recommended - then select the desired ‘Password Policy’ immediately afterwards and set a correspondingly strong password. In this excursus, `1 = Medium` is selected.
• As the password policy has been set to ‘Medium’, a valid DB password (for all DB users) must therefore correspond to this policy:

1. The password must be at least 8 characters long. - The password has at least 1 lower case letter and at least 1 upper case letter. - The minimum number of digits is 1 - The minimum number of special characters is 1

• The strength of the password used is determined and displayed in the dialogue.

• If the value is less than 100, answer the question as to whether you want to change the password with `y` and enter a strong password such as adminPW9#, which corresponds to the password policy `Medium`. Otherwise you can accept the existing strong password with `n`.

Otherwise, you should enter ‘y’ for all further queries in the dialogue to define secure default settings for the MySQL server.

Comprehensive documentation on the MySQL database server can be found at

LINK1:	 https://dev.mysql.com/doc/refman/8.0/en/

You can view the last entries of the current error log file of the MySQL database server with the first command in a console; the complete content with the second command:

$ tail -f /var/log/mysql/error.log
$ xed /var/log/mysql/error.log

Attention! The MySQL database server is started automatically every time the system is started (default). You can change this with these two commands:

$ sudo systemctl disable mysql  	-- MySQL aus der Autostart-Liste entfernen
$ sudo systemctl enable mysql 	-- MySQL der Autostart-Liste wieder hinzufügen

If the MySQL database server is not allowed at system startup, you can start it yourself.

The following calls in a terminal control the MySQL database server with the parameters shown in the list:

$ sudo systemctl parameter mysql {start|stop|restart|reload|force-reload|status}   oder
$ sudo service mysql parameter   {start|stop|restart|reload|force-reload|status}

Examples:

$ sudo systemctl start mysql
$ sudo service mysql stop
$ systemctl status mysql  		'-- Aufruf ohne erhöhte Berechtigungen nur bei der Statusabfrage

The MySQL server is (pre-)configured so that the DB administrator can only log on to the MySQL server locally. This restriction is not cancelled even if the binding is changed, as it is determined by the account type - in this case root@localhost!

To allow other DB users to connect to the MySQL server from any host, the following must be added * with increased authorisations, a corresponding binding must be entered in the (main) configuration file (from Ubuntu 16) /etc/mysql/mysql.conf.d/mysqld.cnf. To do this, the IP address 127.0.0.1 in the variable ‘bind-address’ must be replaced by the IP address at which the MySQL server can be reached. If the line bind-address = ::, then the MySQL server accepts TCP/IP connections via all IPv4 and IPv6 interfaces of the server host. If, on the other hand, bind-address = 192.168.100.12 is specified, then the MySQL server only listens on this IP address. After you have changed the bind address, do not forget to restart your MySQL server!

• in the DB account name of a DB user, the IP address(es) from which the user is authorised to access the MySQL server must be specified. Here are some examples:

user_name@localhost		Zugriff nur von localhost,
user_name@192.168.0.3		Zugriff nur von 192.168.0.3,
user_name@192.168.0.%	Zugriff aus dem IP-Bereich 192.168.0.1 bis 192.168.0.254,
user_name@%			Zugriff von jedem Host.

To determine which IP addresses the MySQL server is listening to or bound to:

$ sudo netstat -tupan | grep mysql
[sudo] Passwort für hans:
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
----------------------------------------------------------------------------------------------------------
tcp        0      0 127.0.0.1:33060         0.0.0.0:*               LISTEN      8678/mysqld
tcp        0      0 127.0.0.1:37926         127.0.0.1:3306          VERBUNDEN   9478/mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      8678/mysqld
tcp6       0      0 127.0.0.1:3306          127.0.0.1:37926         VERBUNDEN   8678/mysqld
$

These outputs above correspond to the output of the following command in the MySQL client:

$ mysql -h localhost -u root -p
mysql> SHOW variables WHERE variable_name LIKE 'bind%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| bind_address  | *     |
+---------------+-------+
1 row in set (0,00 sec)
mysql>

Detailed information on binding can be found at https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_bind_address

The exercise - performed in a console with the MySQL monitor `psql` (client) - consists of repeating important commands again and using

• first determine the status of the MySQL database server,
• then start the server (on the standard port 3306) if necessary,
• Next, re-read the configuration of the MySQL server,
• then start the MySQL client mysql and log in as the DB user `root`,
• then find out which databases exist,
• then call up the MySQL help and ask how to create a new database
• and finally exit the MySQL client with the command quit or exit or \q.

$ systemctl status mysql
$ sudo systemctl start mysql 	-- Option
$ sudo service mysql force-reload
$ mysql -h localhost --port=3306 -u root -p
mysql> SHOW DATABASES;  		-- Achten Sie hier auf das Semikolon
mysql> HELP				-- Jede folgende Zeile einzeln abarbeiten
mysql> HELP CREATE DATABASE
mysql> quit
Bye

If you call up a specific help function - such as ‘HELP CREATE DATABASE’ - a suitable link such as https://dev.mysql.com/doc/refman/8.0/en/create-database.html is also displayed after ‘URL:’, which you can immediately open interactively in the context menu with the right mouse button!

In the event of a de-installation of the database server, it is always a good idea to create a complete backup of the structure and data of the previously used databases or of selected databases:

$ mysqldump --comments --dump-date --no-tablespaces --user root --password --all-databases > all-databases.sql
$ mysqldump --comments --dump-date --no-tablespaces --user root --password --databases DB1Name DB2Name > db1db2.sql

If you want to part with MySQL - here in version 8 - then please only uninstall the MySQL server and the MySQL client; either via the Synaptic package management or in a console with the following command:

$ sudo apt-get remove --purge mysql-client mysql-client-8.0 mysql-client-core-8.0 mysql-server mysql-server-8.0 mysql-server-core-8.0

You can then delete the following directories:

$ sudo rm -rf /var/lib/mysql* /var/log/mysql

Note

└── mysql
    ├── conf.d
    │   ├── mysql.cnf
    │   └── mysqldump.cnf
    ├── my.cnf -> /etc/alternatives/my.cnf
    └── my.cnf.fallback

These folders and directories usually already exist before MySQL was installed and should therefore not be deleted!