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

Introduction

In this example we will be connection from a machine with IP 192.168.1.11 to a MYSQL instance on a different machine and will be referred to as the MYSQL server.

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 machine running the MYSQL server create a user for your remote machine. In this instance the remote machine is 192.168.1.11.

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;
FLUSH PRIVILEGES;

Connect on the Client

On the client machine (192.168.1.11) you should now be able to connect with

mysql -h <mysqlserverip> -u my_admin  -p mydb

Example SQL

Useful commands

use database mydb;
show databases;
show tables;
desc table COVID_DATA;

Create Table

Here is an example to create a table with MYSQL

 CREATE TABLE COVID_DATA (
     ID int NOT NULL AUTO_INCREMENT,
     YEAR int,
	 DAY int,
	 ALPHA2CODE varchar(3),
	 NEW_CASES int,
	 NEW_DEATHS int ,
	 NEW_RECOVERED int ,
	 TOTAL_CASES int ,
	 TOTAL_DEATHS int ,
	 TOTAL_RECOVERED int,
	 RECORD_DATE_TIME datetime,
     PRIMARY KEY (ID)
 );

VS Code

I use SQLTools for VS Code. It does not work by default. Here is the secret source.

   ALTER USER 'admin'@'localhost' IDENTIFIED WITH mysql_native_password BY 'youarejokingmrcallan i.e. not a real password'