Note: This document contains fragmented analytical data regarding local installation,
connectivity, and storage engine architecture. Internal classification: Uncategorized.
1.0 INSTALLATION & SERVICE CONTROL
Methodology for Windows NT/10 environments:
# Start / Stop service via Command Line Interface (if version is 8.0)
net start MySQL80
net stop MySQL80
# GUI Access Path
Services.msc -> Right Click -> Start
services and application -> right click -> start
2.0 AUTHENTICATION & REMOTE ACCESS
The standard protocol for local and remote node authentication.
# Local Login (Default: root@localhost, >>cd C:...\MySQL\MySQL Server 8.0\bin)
mysql -h localhost -u root -p
mysql -u root -p
# Remote Node Login
mysql -h 192.168.1.100 -u root -p
2.1 Graphical Management Interface
MySQL Workbench: Formal administrative tool for schema modeling.
phpMyAdmin: Web-based CRUD interface.
Navicat: Commercial integrated development environment.
3.0 DATA DEFINITION & MANIPULATION
Standard SQL Command Set (ISO/IEC 9075):
Category
Operation
Functional Description
Example
DDL
CREATE / DROP / ALTER
Schema and Table definition
CREATE DATABASE database_name;
CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
DROP DATABASE database_name;
DROP TABLE table_name;
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;
DML
SELECT / INSERT
Data retrieval and modification
SELECT column1, column2, ... FROM table_name;
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
DELETE FROM table_name WHERE condition;
DCL
GRANT / REVOKE
Access control and permissions
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';
4.0 STORAGE ENGINE ARCHITECTURE
MySQL 8.0 defaults to the InnoDB engine for transactional integrity.
-- Query available engines
SHOW ENGINES;
InnoDB: Supports ACID transactions and row-level locking.
Merge: This storage engine allows you to create a table that is a collection of other tables, allowing you to query multiple tables as if they were a single table.
ndbcluster: This storage engine is designed for use in MySQL Cluster, a distributed database system that provides high availability and scalability. It uses a shared-nothing architecture and supports transactions and foreign keys.
ndbinfo: This storage engine provides information about the NDB Cluster storage engine, such as the status of nodes and the distribution of data across the cluster. It does not support transactions or foreign keys.
MyISAM: Optimized for high-speed read-heavy operations; no transaction support.
MEMORY: Volatile storage in RAM for transient data sets.
ARCHIVE: High-compression engine for historical log data.
blackhole: This storage engine discards all data that is inserted into it, making it useful for testing and benchmarking purposes. It does not support transactions or foreign keys.
performance_schema: This storage engine provides a way to monitor the performance of MySQL by collecting data about server events and resource usage. It does not support transactions or foreign keys.
csv: This storage engine allows you to create a table that is stored in a CSV file, making it easy to import and export data. It does not support transactions or foreign keys.
5.0 PHYSICAL FILESYSTEM STRUCTURE
Default Data Path: C:\ProgramData\MySQL\MySQL Server 8.0\Data
Internal System Databases:
* /mysql: Core credential and privilege tables, contains the data for the MySQL server itself, such as user accounts and privileges.
* /performance_schema: Low-level event monitoring.
* /information_schema: Global metadata view.
* /sys: Administrative interpretation views.
* /#innodb_temp: a temporary directory used by the InnoDB storage engine for storing temporary files during certain operations, such as sorting or creating temporary tables. It is not a database and does not contain any user data.
* /#innodb_redo: a directory used by the InnoDB storage engine for storing redo log files, which are used for crash recovery and ensuring data integrity. It is not a database and does not contain any user data.
6.0 Create a Database in Navicat
Well, I'd like to create a nebula db.
GUI-based schema generation workflow:
Establish Connection: Execute a [Right Click] on the sidebar connection node and select New Connection. Provide credentials (root/localhost).
Initiate Schema: Once the connection is active [Double Click], perform a [Right Click] on the connection name and select New Database....
Configuration: Define the Database Name (e.g., NGC). Set Character Set to utf8mb4 and Collation to utf8mb4_0900_ai_ci for modern compatibility.
Finalization: Click OK. The new database instance will be persistent within the /Data directory as a physical subdirectory.
Subsequent Object Definition — Tables (Entities):
Table Initiation: [Double Click] the newly created database (e.g., NGC) -> [Right Click] on the Tables node -> select New Table.
Field Specification: Define column names, data types (INT, VARCHAR, DECIMAL, etc.), and constraints.
Example: Creating a 'Nebula_Objects' entity.
-- Logical Schema representation for the 'Nebula_Objects' table:
ID : INT (Primary Key, Auto-Increment)
Catalog_Name : VARCHAR(50) (e.g., 'NGC7023')
Constellation : VARCHAR(30)
Distance_LY : DECIMAL(10, 2)
Discovery_Year : INT
Primary Key Designation: Click the Key icon next to the ID field. This ensures entity integrity within the relational model.
Persistence: Press Ctrl + S to save. Enter the table name: nebula_registry.
Data Population: Once the table is persistent, click Open Table to enter the spreadsheet-like Data View for manual entry, or use the Import Wizard to ingest external CSV/JSON datasets.
Note: While Navicat abstracts the DDL (Data Definition Language), the underlying execution remains: CREATE TABLE nebula_registry (...);
Relational Modeling: To establish connections between multiple tables (e.g., linking 'Nebula_Objects' to 'Observers'), utilize the Foreign Key tab in the Table Designer to map local fields to remote primary keys.