Connecting to MySQL/MariaDB, 101 stuff

• Basic client-server architecture and “local-machine” usage
  • Installation
  • Creating a database
  • Changing the password of a user
  • Connect to the database on the same machine
• Remote connectivity
  • At the user level
  • At the global level
  • Using mysql on the command line to test connectivity
• sql clients
  • command line mysql
  • phpMyAdmin
  • dBeaver

Basic client-server architecture and “local-machine” usage

Installation

This will install the server, and the client as a dependency, then start¹ it

apt install mariadb-server
systemctl enable --now mariadb 

If you intend to use it on a server open to the world, you should also run :

mysql_secure_installation

which will suggest to add a strong root password, remove non-local (remote) root access and remove the test database.

Creating a database

Let’s create a database named someNameForMyDB a user named joeUser with a password superSikr3t.

CREATE DATABASE someNameForMyDB;
GRANT ALL ON someNameForMyDB.* TO 'joeUser' IDENTIFIED BY 'superSikr3t';

Changing the password of a user

One way to change it is :

ALTER USER 'user-name'@'localhost' IDENTIFIED BY 'NEW_USER_PASSWORD';

Beware that there’s a .mysql_history in your home dir (or root’s home dir).

Documentation :

• https://mariadb.com/kb/en/alter-user/ for the above method
• https://mariadb.com/kb/en/set-password/ for (older) systems lacking “ALTER USER” support.

Connect to the database on the same machine

You can connect with : mysql -u joeUser -p

This prompts for the password, then you can try a few commands :

use someNameForMyDB;
SHOW TABLES;

(there are none at this point, obviously)

Remote connectivity

At the user level

On the server

SELECT host, user, password, plugin FROM mysql.user;

The host column will probably have localhost or %. The former means only localhost connectivity. The latter means anyone (also remote) can connect (but see below for a global setting).

The plugin column will have unix_socket if local connection can be made without a password. On my Debian 10 this is the case for the root user (i.e. the root Linux user can connect as the root MariaDB user without the MariaDB password). This is cool : it means you can set an impossible password for root (e.g. pwgen -s 20 1), and forget it immediately. You probably never actually need it.

The password column contains a hash of the password.

At the global level

On my Debian, most of the stuff is configured in the file /etc/mysql/mariadb.conf.d/50-server.cnf

In the default configuration, the following line forbids all remote connections :

bind-address = 127.0.0.1

It’s a good setting if all SQL clients are on the same machine.

Using mysql on the command line to test connectivity

If you’re on a different machine, use the -h flag to specify the hostname / server address.

mysql -h server-address -u username -p

(The -p flags means: ask for a password.)

sql clients

command line mysql

Simply mysql on the command line. Basic relevant options are

• -u username
• -p – this asks a password
• -h hostname/address

phpMyAdmin

I do not use it anymore. (it’s a php app that has to be secured.)

dBeaver

Multiplatform client. Works for me. It will connect to mySQL databases as well as many others, including MS Access databases.