MYSQL

Introduction

This page is just a reminder of the way to approach MYSQL set up.

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:*