Difference between revisions of "SBN - Database Server Notes"

From The TinkerNet Wiki
Jump to navigation Jump to search
 
(9 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 5: Line 7:
 
i.e.: Wikipedia, WordPress, WeeWX, etc...
 
i.e.: Wikipedia, WordPress, WeeWX, etc...
  
−
= Linux Mint =
+
==Installation==
−
* Mint 19.3 Seems to get MySQL 5.7.30
+
 
−
* Mint 20 Gets MySQL 8.0.20
+
===Linux Mint===
 +
 
 +
*Mint 19.3 Seems to get MySQL 5.7.30
 +
*Mint 20 Gets MySQL 8.0.20
 +
 
 +
*<code>sudo apt install mysql-server mysql-client php-mysql</code>
 +
**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
 +
 
 +
*<code>sudo apt install mysql-server mysql-client php-mysql</code>
 +
**Say Yes to Continue
 +
**<u>Configuring mysql-community-server</u>
 +
***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:
 +
 
 +
*<code>sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf</code>
 +
 
 +
& change
  
−
* <code>sudo apt install mysql-server mysql-client php-mysql</code>
+
<code>bind-address              = 127.0.0.1</code>
−
** Say Yes to Continue
 
−
* See [[TinkerWiki:MySQL - Initial Configuration]] for configuration
 
  
−
= SparkyLinux =
+
to
−
* Doesn't even have MySQL in the repositories...
 
−
* [[TinkerWiki:Getting MySQL onto SparkyLinux]]
 
−
** Which then gets MySQL 8.0.21
 
  
−
* <code>sudo apt install mysql-server mysql-client php-mysql</code>
+
<code>bind-address              = '''Machine.Domain.TLD'''</code>
−
** Say Yes to Continue
 
−
** <u>Configuring mysql-community-server</u>
 
−
*** 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 [[TinkerWiki:MySQL - Initial Configuration B]] for configuration
 
−
** (pretty much same as [[TinkerWiki:MySQL - Initial Configuration]] except the root password is already done...)
 
  
−
= Set up at least one user in mysql =
+
==Set up at least one user in mysql==
  
−
* <code>sudo mysql -u root -p</code>
+
*<code>sudo mysql -u root -p</code>
−
** You will be asked for '''2''' passwords, your own & that of the root database user.
+
**You will be asked for '''2''' passwords, your own & that of the root database user.
  
 
  CREATE USER 'someone'@'localhost' IDENTIFIED BY 'password';
 
  CREATE USER 'someone'@'localhost' IDENTIFIED BY 'password';
Line 36: Line 57:
 
('''Hint:'''  This'd be a good time to create yourself as that user with your non-admin password of choice...)
 
('''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 ''''*'''')
+
If this is to be a remotely accessed Database Server, '<nowiki/>'''localhost'<nowiki/>''' will actually be the name/address of the remote machine you'll access '''from'''. (Possibly even '<nowiki/>'''*'''')
 +
 
 +
==Reference Materials==
 +
 
 +
*[https://www.w3schools.com/sql/default.asp SQL Tutorial]
 +
*[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://www.hostinger.com/tutorials/?s=mysql Some handy tutorials]
 +
**[https://www.hostinger.com/tutorials/mysql-show-users/ How to Show Users in MySQL on Linux]
 +
 
 +
==Useful Things==
 +
 
 +
*Sign into MySQL
 +
**<code>sudo mysql -u root -p</code>
 +
*Restart MySQL
 +
**<code>sudo systemctl restart mysql.service</code>
 +
*Change a user password
 +
**<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!)'''
 +
**<code>DROP DATABASE wp_Someblog;</code>
 +
*List existing databases
 +
**<code>SHOW databases;</code>
 +
*List existing users
 +
**<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)
  
−
= Reference Materials =
+
*<code>mysql -u user -p DBname < DBname.sql</code>
  
−
* [http://g2pc1.bu.edu/~qzpeng/manual/MySQL%20Commands.htm Commands]
+
====If using a separate DB Server...====
−
* [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]
+
Create the user & database on the DB Server, then log onto the application server &do the following
−
* [https://www.hostinger.com/tutorials/?s=mysql Some handy tutorials]
 
−
** [https://www.hostinger.com/tutorials/mysql-show-users/ How to Show Users in MySQL on Linux]
 
  
−
= Useful Things =
+
(You may have to backtrack & SCP the file to the application server...  :P )
  
−
* Restart MySQL
+
*<code>mysql -u user -p -h Machine.Domain.TLD DBname < DBname.sql</code>
−
** <code>sudo systemctl restart mysql.service</code>
 
−
* Change a user password
 
−
** <code>ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here';</code>
 
−
* Trash a complete database '''(WARNING! DANGER! DAMAGE LIKELY!)'''
 
−
** <code>DROP DATABASE wp_Someblog;</code>
 
−
* List existing databases
 
−
** <code>SHOW databases;</code>
 
−
* List existing users
 
−
** <code>SELECT user,host FROM mysql.user;</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’