MySQL
From Triangle Wiki
Contents |
Introduction
MySQL is a very fast open source relational database.
Many To Many Relationships
In a lot of cases you will probably require many entities to be in many situations, for instance a Product in an online shop can have many categories, and a category can have many products. To achieve this scenario we create a linking table mechanism in the RDBM (Relational database management system) of choice.
This table simply links the Primary key of the product to the Primary key of the category.
Below we have a Products table
| Product ID | Product Name | Product Status |
| 1 | NS64 | Active |
| 2 | Mario Carts | Active |
Below we have a Category table
| Category ID | Category Name | Category Status |
| 1 | System | Active |
| 2 | Game | Suspended |
| 3 | Racing | Suspended |
Below we have a Linking table
| Link ProductID | Link CategoryID |
| 1 | 1 |
| 2 | 2 |
| 2 | 3 |
As you can see the linking table links the Product ID to the Category ID
Tuning MySQL
MySQL depends heavily on CPU and Disk I/O
- key_buffer_size to at least a quarter, but no more than half, of memory.
Commands
Import Batch SQL Data
To import large SQL data files use:
shell> mysql -u USERNAME -p DBNAME < SQL.sql
Grant Privileges / Change Passwords
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'pwd';
Specify distinct privileges via a comma separated list - select,insert,update,delete etc. See this table for a full list of privileges that can be assigned MySQL Privileges
GRANT ALTER,CREATE,DELETE,DROP,INDEX,INSERT,SELECT,UPDATE ON *.* TO 'USER'@'%' IDENTIFIED BY 'PASS';
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
Repair Table
USE db REPAIR TABLE tablename
