SBN - Database Server Notes

From The TinkerNet Wiki
Jump to navigation Jump to search

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

SparkyLinux

  • 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

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

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