MySQL

Installation

Steps taken (mixed) from this link and this link.

• Run the following commands to install MySQL on Ubuntu server and to check its status:

sudo apt install -y mysql-server
sudo systemctl status mysql

Configuration

• Secure the MySQL installation by running the following command:

sudo mysql_secure_installation
# set VALIDATE PASSWORD COMPONENT to y
# Password validation policy -> 2 (STRONG)
# Remove anonymous users? -> y
# Disallow root login remotely? -> y
# Remove test database and access to it? -> y
# Reload privilege tables now? -> y

• Log into MySQL service as root user using sudo mysql command.
• Create and set password for piratedev user:

-- Create piratedev user
CREATE USER piratedev IDENTIFIED BY '<password>';
-- Password found in iCloud keychain

• Create database with same name and set owner:

CREATE DATABASE piratedev;
GRANT ALL PRIVILEGES ON piratedev.* TO piratedev;

• You should now be able to log into MySQL service as new user using mysql -u piratedev -p command.

• Update mysqld.cnf file:

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
# Set user to piratedev
# Set bind-address to 192.168.1.100

• Restart MySQL server to take effect:

sudo systemctl restart mysql

Test Connection

• Test your connection from any SQL client on another machine within the LAN network.
  • Use host IP as 192.168.1.100 and DB user as piratedev.
  • [DBeaver] Change allowPublicKeyRetrieval to TRUE in SQL Client's driver properties.