Difference between revisions of "SBN - Database Server Notes"

From The TinkerNet Wiki
Jump to navigation Jump to search
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
A dedicated server Just to run MySQL...
 
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.
 
Then you can simply point all the other things that use it to a central location.
Line 35: Line 37:
 
This is ugly, but works for now:
 
This is ugly, but works for now:
  
−
* <code>sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf</code>
+
*<code>sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf</code>
  
 
& change  
 
& change  
Line 43: Line 45:
 
to  
 
to  
  
−
<code>bind-address              = '''Actual IP Address'''</code>
+
<code>bind-address              = '''Machine.Domain.TLD'''</code>
  
 
==Set up at least one user in mysql==
 
==Set up at least one user in mysql==
Line 59: Line 61:
 
==Reference Materials==
 
==Reference Materials==
  
 +
*[https://www.w3schools.com/sql/default.asp SQL Tutorial]
 
*[http://g2pc1.bu.edu/~qzpeng/manual/MySQL%20Commands.htm Commands]
 
*[http://g2pc1.bu.edu/~qzpeng/manual/MySQL%20Commands.htm Commands]
 +
*[https://www.freecodecamp.org/news/basic-sql-commands/ Basic SQL Commands - The List of Database Queries and Statements You Should Know]
 
*[https://linuxize.com/post/how-to-manage-mysql-databases-and-users-from-the-command-line/ How to Manage MySQL Databases and Users from the Command Line]
 
*[https://linuxize.com/post/how-to-manage-mysql-databases-and-users-from-the-command-line/ How to Manage MySQL Databases and Users from the Command Line]
 
*[https://www.hostinger.com/tutorials/?s=mysql Some handy tutorials]
 
*[https://www.hostinger.com/tutorials/?s=mysql Some handy tutorials]
Line 66: Line 70:
 
==Useful Things==
 
==Useful Things==
  
 +
*Sign into MySQL
 +
**<code>sudo mysql -u root -p</code>
 
*Restart MySQL
 
*Restart MySQL
 
**<code>sudo systemctl restart mysql.service</code>
 
**<code>sudo systemctl restart mysql.service</code>
 
*Change a user password
 
*Change a user password
 
**<code>ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here';</code>
 
**<code>ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here';</code>
 +
*Create a new database
 +
**<code>CREATE DATABASE db_name;</code>
 
*Trash a complete database '''(WARNING! DANGER! DAMAGE LIKELY!)'''
 
*Trash a complete database '''(WARNING! DANGER! DAMAGE LIKELY!)'''
 
**<code>DROP DATABASE wp_Someblog;</code>
 
**<code>DROP DATABASE wp_Someblog;</code>
Line 76: Line 84:
 
*List existing users
 
*List existing users
 
**<code>SELECT user,host FROM mysql.user;</code>
 
**<code>SELECT user,host FROM mysql.user;</code>
 +
 +
===Move a database from one server to another===
 +
 +
====On the originating server:====
 +
 +
*<code>sudo mysql -u root -p</code>
 +
**<code>GRANT ALL ON *.* TO 'user'@'localhost';</code>
 +
**<code>FLUSH PRIVILEGES;</code>
 +
**<code>EXIT;</code>
 +
 +
*<code>mysqldump -u user -p DBname > DBname.sql</code>
 +
*<code>scp DBname.sql user@Machine.Domain.TLD:~</code>
 +
 +
====On the destination server:====
 +
Create the user & database (following the instructions for the application requiring the database)
 +
 +
*<code>mysql -u user -p DBname < DBname.sql</code>
 +
 +
====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 )
 +
 +
*<code>mysql -u user -p -h Machine.Domain.TLD DBname < DBname.sql</code>

Latest revision as of 00:29, 5 February 2023

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
Retrieved from ‘https://wiki.tinkernet.ca/index.php?title=SBN_-_Database_Server_Notes&oldid=1860’