3910

What do I need to know?

The differences between InnoDB vs MyISAM table storage engines

Operations	InnoDB	MyISAM
Transactions	yes	no
Locking	Row	Table
Speed	write	concurrent read
Full Text Indexing	Yes after 5.6	Yes
Foreign Key	yes	no
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.0\my.ini
Linux: /etc/mysql/my.cnf, /etc/mysql/conf.d/

• client mysql.cnf
• server mysqld.cnf

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'@'%';

Activate the roles

SET DEFAULT ROLE ALL TO 'user';

Delete a user/role

DROP '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.0\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 mysql_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 
  LINES TERMINATED BY `\n`
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  IGNORE n LINES
  (column,@dummy,column);

• \n, \r\n, \n
• 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 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 
  LINES TERMINATED BY `\n`
  FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
  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)