SBN - Database Server Notes

A dedicated server Just to run MySQL...

(Now with InfluxDB and Grafana too... Because... Reasons...)

Then you can simply point all the other things that use it to a central location.

i.e.: Wikipedia, WordPress, WeeWX, etc...

Installation

Linux Mint

• Mint 19.3 Seems to get MySQL 5.7.30
• Mint 20 Gets MySQL 8.0.20

• sudo apt install mysql-server mysql-client php-mysql
  • Say Yes to Continue
• See MySQL - Initial Configuration for configuration

SparkyLinux

• Doesn't even have MySQL in the repositories...
• Getting MySQL onto SparkyLinux
  • Which then gets MySQL 8.0.21

• sudo apt install mysql-server mysql-client php-mysql
  • Say Yes to Continue
  • Configuring mysql-community-server
    • Pick a good root password...
    • I tend to select Use Legacy Authentication Method because Use Strong Password Encryption is annoying as hell. (Your choice here...)
• See MySQL - Initial Configuration B for configuration
  • (pretty much same as MySQL - Initial Configuration except the root password is already done...)

NON LOCALHOST ACCESS!

By default, MySQL binds to 127.0.0.1...

This is ugly, but works for now:

• sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

& change

bind-address              = 127.0.0.1

to

bind-address              = Machine.Domain.TLD

Set up at least one user in mysql

• sudo mysql -u root -p
  • You will be asked for 2 passwords, your own & that of the root database user.

CREATE USER 'someone'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
EXIT;

(Hint: This'd be a good time to create yourself as that user with your non-admin password of choice...)

If this is to be a remotely accessed Database Server, 'localhost' will actually be the name/address of the remote machine you'll access from. (Possibly even '*')

Reference Materials

• SQL Tutorial
• Commands
• Basic SQL Commands - The List of Database Queries and Statements You Should Know
• How to Manage MySQL Databases and Users from the Command Line
• Some handy tutorials
  • How to Show Users in MySQL on Linux

Useful Things

• Sign into MySQL
  • sudo mysql -u root -p
• Restart MySQL
  • sudo systemctl restart mysql.service
• Change a user password
  • ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here';
• Create a new database
  • CREATE DATABASE db_name;
• Trash a complete database (WARNING! DANGER! DAMAGE LIKELY!)
  • DROP DATABASE wp_Someblog;
• List existing databases
  • SHOW databases;
• List existing users
  • SELECT user,host FROM mysql.user;

Move a database from one server to another

On the originating server:

• sudo mysql -u root -p
  • GRANT ALL ON *.* TO 'user'@'localhost';
  • FLUSH PRIVILEGES;
  • EXIT;

• mysqldump -u user -p DBname > DBname.sql
• scp DBname.sql user@Machine.Domain.TLD:~

On the destination server:

Create the user & database (following the instructions for the application requiring the database)

• mysql -u user -p DBname < DBname.sql

If using a separate DB Server...

Create the user & database on the DB Server, then log onto the application server &do the following

(You may have to backtrack & SCP the file to the application server... :P )

• mysql -u user -p -h Machine.Domain.TLD DBname < DBname.sql