🌳 My Digital Garden

3910

9 min read

What do I need to know?

The differences between InnoDB vs MyISAM table storage engines

OperationsInnoDBMyISAM
Transactionsyesno
LockingRowTable
Speedwriteconcurrent read
Full Text IndexingYes after 5.6Yes
Foreign Keyyesno

InnoDB is the new default engine used in MySQL, it’s ACID compliant with transactions and support foreign keys. MyISAM is better for reading large amount of data while InnoDB is better for writes. InnoDB started supporting full text indexing (searching for text in blog article) in 5.6.

Where the correct location is for the MySQL configuration file(s) (on Windows)

Windows: C:\ProgramData\MySQL\MySQL Server 8.4\my.ini
Linux: /etc/mysql/my.cnf, /etc/mysql/conf.d/

  • client mysql.cnf
  • server mysqld.cnf

How can you tell if a table is InnoDB or MyISAM?

SHOW TABLE status LIKE 'table_name';
SHOW ENGINE;

What is the standard port for MySQL

3306/tcp

How to setup users, roles and privileges

Create a new user

CREATE USER 'user'@'%' IDENTIFIED BY 'Password';

Create a new role (group)

CREATE ROLE 'admin'@'%';

Grant privileges to roles/users

GRANT INSERT,SELECT,DELETE,UPDATE on db.table TO 'role_name'@'%';

Grant role to user

GRANT 'admin'@'%' TO 'user'@'%';

Activate the roles

SET DEFAULT ROLE ALL TO 'user';

Delete a user/role

DROP USER 'user'@'localhost';
  • user@localhost and user@% are separate users

Check users

SELECT user,host FROM mysql.user;

Check current user

SELECT current_user();

Know what cleanup is required after resetting the MySQL root password to ensure your MySQL service is secure (on Windows and Linux)

Windows

mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.4\my.ini" --init-file=C:\temp\changeRootPassword.sql"
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Password';
  • delete the password file
  • restart the MySQL service

Linux

sudo service mysql stop
sudo mkdir -p /var/run/mysql # temp
sudo chown mysql /var/run/mysql
sudo mysqld_safe --skip-grant-tables --skip-networking --user=mysql &

Start the safe mode to reset password

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD';
FLUSH PRIVILEGES;
  • kill MySQL safe mode service
  • verify it’s killed, and restart
sudo killall mysqld
sudo service mysql start

What is secure_file_priv and how does it relate to importing data using LOAD DATA INFILE (on Windows and Linux)

It’s a parameter MySQL use which can restrict import and export file operations to a specific folder.
Linux: /var/lib/mysql-files (must be owned by mysql)
Windows: C:/ProgramData/MySQL/MySQL Server 8.4/Uploads

How to use LOAD DATA INFILE with different line endings (Windows \r\n, Mac \r and Linux \n)

LOAD DATA INFILE '/var/lib/mysql-files/file.txt' INTO TABLE db.table 
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE n LINES
  (column,@dummy,column);
  • \n, \r\n, \r
  • set delimiter, csv, tsv, a|b|c
  • some words are quoted
  • ignore headers
  • specify order of insertion based on text file layout

How to tell a file has which line ending (in Windows and in Linux)

Windows

  • download Notepad++ or Notepad
  • check the bottom right

Linux

file myfile.txt # ASCII TEXT, UTF-8, etc...
cat -e myfile.txt # $ means LF; \M means CR

What is a deadlock? Can MySQL detect deadlocks? What happens when a deadlock occurs? How can we prevent deadlocks?

A MySQL deadlock occurs usually in a transaction, where one TX acquire a lock and another TX tries to access it, and the first TX tries to access a lock at the same time the second tries to access the first. Where both transaction

-- One
UPDATE ... SET ... WHERE id=1; -- One acquires lock on id=1
-- Two
UPDATE ... SET ... WHERE id=2; -- Two acquires lock on id=2
-- One
UPDATE ... SET ... WHERE id=2; -- One requires two to finish TX, blocked
-- Two
UPDATE ... SET ... WHERE id=1; -- Two requires one to finish which depends on 2 to finish, ^deadlock here

MySQL will automatically rollback transactions to stop a deadlock. To prevent deadlock, keep the transaction short, or use a less safe isolation level like read uncommitted.

What is a Stored Procedure? What is a Stored Function?

These are SQL code stored in the server that can be called.

Example procedure

CREATE PROCEDURE SelectThis()
BEGIN
  SELECT * FROM tables;
END;
 
CALL SelectThis()

Example function

CREATE FUNCTION Count()
RETURN INT
BEGIN
  DECLARE total INT;
  SELECT COUNT(*) INTO total FROM tables;
  RETURN total;
END;
SELECT Count();
  • functions can be called as part of SELECT whereas procedures cannot

What is a Trigger? Event Types? What is the difference between BEFORE/AFTER?

Event Types

  • BEFORE INSERT/UPDATE/DELETE
    • used for data validation (use insert/update)
    • in the lab, we used trigger to validate the price before inserting into the table
  • AFTER INSERT/UPDATE/DELETE
    • used for updating another table, logging changes

Explain the EXPLAIN command. How does the EXPLAIN help optimize the speed of queries?

The explain command is added in front of a query and used to analyze the query and plan it’s execution steps. It does not execute the query. The type column of the explain query is useful to check how rows are accessed

  • ALL: full table scan
  • INDEX: same as table scan but doesn’t sort
  • INDEX USING INDEX: scans the index instead of the data
  • RANGE: partial index scan
    • e.g. WHERE index_column < > n
  • REF: index rows that match a single value but multiple rows
    • e.g. WHERE index_column =
  • CONST: no table access, only one matching row needed
    • e.g. UNIQUE on a indexed column
  • NULL: no table accessed
    • e.g. getting minimum value of an indexed field

What is an index and how do they improve the performance of a query? Which indexes should I add to speed up a query?

Database index create a sorted data structure that allows for faster retrieval of data (binary search). Indexes can be added to columns that are frequently used in WHERE clause. For composite indexes, the order of the columns matter, e.g. WHERE a=1 AND b=2 should use INDEX(a,b). However, indexes can slow down write performance and take up space.

What is the purpose of Summary Tables? Why can’t we just use indexes?

Summary tables are pre-aggregated tables that store the results of complex queries, such as joins and aggregations, to improve aggregate query performances. These tables can be created via scheduled events

Indexes can help with data retrieval but do not reduce computation (sum, count, etc.).

What is the reduction factor? What is a good reduction factor?

Reduction factor: number of rows in the base table divided by rows in summary table
A good reduction factor is 100x to 1000x. If the reduction factor is too low, i.e. the summary table is still large, then there won’t be performance increase. If the reduction factor is too high, it lose valuable information found in the base table.

What do I need to be able to do?

Change the MySQL Port (on Windows and Linux)

Locate the my.cnf or my.ini and edit the port

  • for Windows, change the port [mysqld]; on Linux it’s a separate file mysqld.cnf

Login to MySQL using a non-standard port

mysql -u root -P 3306

Create databases

CREATE DATABASE db;

Create tables

CREATE TABLE tblname (
  id INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL UNIQUE,
  FOREIGN KEY (other_id) REFERENCES other(id)
);

Insert, update and delete data from existing tables

Insert

INSERT INTO tbl (`name`,`value`) VALUES ('name',123);

Update

UPDATE tbl SET `name`=`value` WHERE id=123;

Delete

DELETE FROM tbl WHERE id=123;

Create MySQL users, roles and grant them privileges

How to setup users, roles and privileges

Import Comma-Separated Value file (CSV) data using LOAD DATA INFILE

LOAD DATA INFILE '/var/lib/mysql-files/data.csv' INTO TABLE db.table 
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES
  (column,@dummy,column);

Import SQL Formatted data from existing mysqldump file

mysql -u root -p db_name < backup.sql
USE db;
SOURCE backup.sql

Recover from an accidental delete of data within a database.

  • load the file from backup into another table
  • insert from a select statement (from backup)
INSERT INTO original.table SELECT * FROM backup.table; -- additional joins

Reset the MySQL root password (on Windows and Linux)

Know what cleanup is required after resetting the MySQL root password to ensure your MySQL service is secure (on Windows and Linux)

Create and Modify Views

CREATE VIEW myview AS 
SELECT column1, column2 FROM table WHERE condition;
 
SELECT * FROM myview;

Modify view

CREATE OR REPLACE VIEW myview AS 
SELECT column1, column2 FROM table WHERE updated_condition;

Troubleshoot users and privileges

How to setup users, roles and privileges
Fix Susanna’s privilege so she can access these tables

SHOW GRANTS FOR 'susanna'@'%'; -- or her roles

Create a complete and partial backup of a database using mysqldump and mysqlpump commands

mysqldump -u root -p db_name > backup.sql

Backup everything

mysqldump -u root -p --all-databases --routines --events --triggers > backup.sql

Restore from backup

mysql -u root -p db_name < backup.sql

Reinitialize the database from scratch

sudo service mysql stop
sudo rm -rf /var/lib/mysql/*
sudo mkdir -p /var/lib/mysql && sudo chown mysql /var/lib/mysql
sudo mysqld --initialize --user=mysql
sudo service mysql start
  • change the password for root
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Password';
FLUSH PRIVILEGES;
  • restore from backup

Create a Stored Procedure

CREATE PROCEDURE SelectThis()
BEGIN
  SELECT * FROM tables;
END;
 
CALL SelectThis()

Create a Stored Function

CREATE FUNCTION ADD(a INT, b INT)
RETURN INT
BEGIN
  RETURN a + b;
END;
SELECT ADD(1,2);

Create a Scheduled Event

Set event scheduler on

SET GLOBAL event_scheduler = ON;
DELIMITER $$
CREATE EVENT myevent
ON SCHEDULE EVERY 1 DAY
STARTS '2026-04-23 00:00:00'
DO
  UPDATE table SET column = value WHERE condition;
$$
DELIMITER ;

Check events

SHOW EVENTS;

Disable an event

ALTER EVENT myevent DISABLE;

Scheduled Event Options

  • one-time event ON SCHEDULE AT '2026-04-23 00:00:00'
  • recurring event ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR

Create a Trigger

DELIMITER $$
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
  UPDATE systemvariables
  SET thevalue = thevalue + 1
  WHERE keyname = 'employeeCount';
END$$
DELIMITER ;
  • this trigger update another table, hence using after insert or delete
DELIMITER $$
CREATE PROCEDURE `check_products`(IN buyPrice DECIMAL(10,2), IN MSRP
DECIMAL(10,2))
BEGIN
  DECLARE _messageText varchar(250);
  IF buyPrice < 0 THEN
    BEGIN
      SELECT 'You should add your message here' INTO _messageText;
      SIGNAL SQLSTATE '45001'
      SET MESSAGE_TEXT = _messageText;
    END;
  END IF;
  IF MSRP < buyPrice THEN
    BEGIN
      SELECT CONCAT(MSRP,' bad',buyPrice)
      INTO _messageText;
      SIGNAL SQLSTATE '45002'
      SET MESSAGE_TEXT = _messageText;
    END;
  END IF;
END$$
DELIMITER ;
 
DELIMITER $$
CREATE TRIGGER `products_before_insert` BEFORE INSERT ON `products`
FOR EACH ROW
BEGIN
  CALL check_products(new.buyPrice,new.MSRP);
END$$
DELIMITER ;
  • the trigger uses new to access the values being inserted, to check the old values, use old

Use the EXPLAIN command to prove you can optimize a query

EXPLAIN SELECT * FROM table WHERE column = 'value';
CREATE INDEX idx_column ON table(column);
EXPLAIN SELECT * FROM table WHERE column = 'value';
-- should change from full to index

Graph View

Recent Notes