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 'password';
 GRANT ALL PRIVILEGES ON mydb.* TO 'newuser'@'localhost';

Connect Remotely

Check 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. I did look up what the X Plugin is but just went with changing both.

# 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

root@denise:/etc/mysql# ss -l |grep 3306
>tcp   LISTEN 0      70                                        0.0.0.0:33060                       0.0.0.0:*