MYSQL

Introduction

This page is just a reminder of the way to approach MYSQL set up. This guide a was written when using

lsb_release -a 
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 20.10
Release:	20.10
Codename:	groovy

Using mysql

dpkg -l |grep ^ii\ \ mysql
ii  mysql-client                         8.0.25-0ubuntu0.20.10.1                            all          MySQL database client (metapackage depending on the latest version)
ii  mysql-client-8.0                     8.0.25-0ubuntu0.20.10.1                            amd64        MySQL database client binaries
ii  mysql-client-core-8.0                8.0.25-0ubuntu0.20.10.1                            amd64        MySQL database core client binaries
ii  mysql-common                         5.8+1.0.5ubuntu2                                   all          MySQL database common files, e.g. /etc/mysql/my.cnf
ii  mysql-server                         8.0.25-0ubuntu0.20.10.1                            all          MySQL database server (metapackage depending on the latest version)
ii  mysql-server-8.0                     8.0.25-0ubuntu0.20.10.1                            amd64        MySQL database server binaries and system database setup
ii  mysql-server-core-8.0                8.0.25-0ubuntu0.20.10.1                            amd64        MySQL database server binaries

Creating a Database

Create a local database

 CREATE DATABASE mydb
 CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'mydbpassword';
 GRANT ALL PRIVILEGES ON mydb.* TO 'newuser'@'localhost';

Connect Remotely

X Plugin

The default setup for MYSQL is to use the X Plugin which means the port open is the default port x 10. The default for mysql is 3306.

Checking the Listening Port

By default you cannot connect remotely as the listening port is 127.0.0.1:33060

ss -l |grep 3306
>tcp   LISTEN 0      70                                      127.0.0.1:33060                       0.0.0.0:*

Change the interface

The configuration is in /etc/mysql/mysql.conf.d/mysqld.cnf and specifies 127.0.0.1 by default. If you are on the network then just change it to be 0.0.0.0. Remember anyone on the network can now access this instance if they have the correct credentials.

# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir                = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
#mysqlx-bind-address    = 127.0.0.1
bind-address            = 0.0.0.0
mysqlx-bind-address     = 0.0.0.0

We can not restart the server and check it is still running and on the right interface. I would restart apps known to be using the instance too. E.g. postfix and dovecot.

sudo systemctl restart mysql
sudo systemctl status mysql

ss -l |grep 3306
>tcp   LISTEN 0      70                                        0.0.0.0:33060                       0.0.0.0:*

Create User of Remote Server

On the remote machine which the mysql instance resides create a user for your machine

create user mydb_admin@192.168.1.11 IDENTIFIED by 'mydbpassword';
GRANT ALL PRIVILEGES ON mydb.* TO 'mydb_admin'@'192.168.1.11' WITH GRANT OPTION;