MySQL

Install MySQL and Playaround:

To install and start the service

sudo apt install mysql-server
 
sudo systemctl start mysql.service

To run the CLI:

sudo mysql

To see all available databases:

SHOW DATABASES;

The systems databases are:

information_schema: Provides access to database metadata, including information about tables, columns, and permissions, among others. It’s a read-only database useful for querying information about the system and the databases/schema it contains.

mysql: contains user accounts and their global priviliges, alnog with plugin details and other system-level information.

performance_schema: a feature for monitoring MySQL server performance.

sys: a collection of views, functions, and procedures to help MySQL

To see the users in MySQL:

SELECT user, host FROM mysql.user

(from results, the user root is different from the system root)

There is no password in root, assign one (removes sudo mysql access)

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mk';
 
flush privileges;
 
exit;

Access MySQL as a user:

mysql -u <user> -p

Load a sample database into the MySQL:

source /home/mk/Desktop/sampledatabase.sql

How to access from web browser?

MySQL typically runs on port 3306 by default. This is the standard TCP port used for MySQL database connections over the network. However, accessing MySQL directly from a browser is not possible in the traditional sense, as MySQL communicates over a protocol that web browsers do not natively understand. Browsers are designed to communicate over HTTP(S), whereas MySQL uses its own protocol for database operations.

sudo apt install php
sudo apt install phpmyadmin

• choose apache2

Do this (from stackoverflow)

sudo nano /etc/apache2/apache2.conf

add this line somewhere

Include /etc/phpmyadmin/apache.conf

To restart the services:

sudo service mysql restart
sudo service apache2 restart

The configuration files for phpMyAdmin are located in /etc/phymyadmin. The main configuration file is /etc/phpmyadmin/config.in.php. This file contains configuration options that apply globally to phpMyAdmin.

Go to 127.0.0.1/phpmyadmin and login with:

username: root
password: mk

Connect to MySQL with Python without a web-server:

pip3 install mysql-connector-python

Establish a connection:

cnx = mysql.connector.connect(
	user='root', password='mk',
	host='127.0.0.1',
	database='classicmodels'
)
 
cursor = cnx.cursor()

Execute the query:

query = ("SELECT * FROM employees")
cursor.execute(query)