Mysql Cheat Sheet

SELECT column1, column2 FROM table_name;

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

UPDATE table_name SET column1 = value1 WHERE condition;

DELETE FROM table_name WHERE condition;

CREATE TABLE table_name (column1 datatype, column2 datatype);

ALTER TABLE table_name ADD column_name datatype;

DROP TABLE table_name;

SHOW DATABASES; 
SHOW TABLES;

USE database_name;

DESCRIBE table_name;
DESC table_name;

mysql -u username -p

mysql -u username -ppassword

mysql -h hostname -u username -p

mysql -h hostname -P 3307 -u username -p

mysql -u username -p database_name

mysql -u username -p --socket=/path/to/mysql.sock

mysqladmin -u root -p version

mysql --defaults-file=/path/to/my.cnf

mysql -u username -p --connect-timeout=10

mysql -u username -p --ssl-ca=/path/to/ca.pem

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

DROP USER 'username'@'localhost';

SET PASSWORD FOR 'username'@'localhost' = PASSWORD('new_password');

ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

RENAME USER 'old_username'@'localhost' TO 'new_username'@'localhost';

GRANT SELECT, INSERT ON database_name.* TO 'username'@'localhost';

REVOKE INSERT ON database_name.* FROM 'username'@'localhost';

SHOW GRANTS FOR 'username'@'localhost';

FLUSH PRIVILEGES;

SELECT USER(), CURRENT_USER();

CREATE DATABASE database_name;

CREATE DATABASE database_name 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

DROP DATABASE database_name;

SHOW DATABASES;

USE database_name;

SHOW CREATE DATABASE database_name;

ALTER DATABASE database_name 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

SELECT 
  table_schema AS "Database",
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "database_name"
GROUP BY table_schema;

CREATE DATABASE IF NOT EXISTS database_name;

SELECT * FROM information_schema.SCHEMATA;

CREATE TABLE table_name (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE new_table LIKE existing_table;

DROP TABLE table_name;

TRUNCATE TABLE table_name;

ALTER TABLE table_name 
ADD COLUMN column_name VARCHAR(50);

ALTER TABLE table_name 
MODIFY COLUMN column_name VARCHAR(100);

ALTER TABLE table_name 
DROP COLUMN column_name;

ALTER TABLE table_name 
CHANGE COLUMN old_name new_name VARCHAR(100);

ALTER TABLE table_name 
ADD INDEX idx_name (column_name);

ALTER TABLE table_name 
ADD CONSTRAINT fk_name 
FOREIGN KEY (column_name) 
REFERENCES ref_table(ref_column);

RENAME TABLE old_name TO new_name;

SHOW TABLES;

DESCRIBE table_name;
DESC table_name;

SHOW CREATE TABLE table_name;

SELECT 
  table_name AS "Table",
  round(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "database_name"
  AND table_name = "table_name";

age INT

is_active TINYINT(1)

quantity SMALLINT

population MEDIUMINT

views BIGINT

price DECIMAL(10,2)

temperature FLOAT

scientific_value DOUBLE

state_code CHAR(2)

username VARCHAR(50)

short_note TINYTEXT

description TEXT

content MEDIUMTEXT

full_document LONGTEXT

status ENUM('active', 'inactive', 'pending')

permissions SET('read', 'write', 'execute')

birth_date DATE

appointment_time TIME

registered_at DATETIME

last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

graduation_year YEAR

file_signature BINARY(16)

encryption_key VARBINARY(128)

small_image TINYBLOB

medium_image BLOB

large_image MEDIUMBLOB

file_content LONGBLOB

shape GEOMETRY

location POINT

route LINESTRING

area POLYGON

settings JSON

INSERT INTO table_name (column1, column2) 
VALUES (value1, value2);

INSERT INTO table_name (column1, column2) 
VALUES 
  (value1, value2),
  (value3, value4),
  (value5, value6);

INSERT INTO table_name 
VALUES (value1, value2, value3);

INSERT IGNORE INTO table_name (column1, column2) 
VALUES (value1, value2);

INSERT INTO table_name (id, column1, column2) 
VALUES (1, value1, value2)
ON DUPLICATE KEY UPDATE 
  column1 = value1,
  column2 = value2;

INSERT INTO target_table (column1, column2)
SELECT column1, column2 
FROM source_table
WHERE condition;

REPLACE INTO table_name (column1, column2)
VALUES (value1, value2);

INSERT INTO table_name (name, created_at)
VALUES ('Example', NOW());

INSERT INTO table_name (id, name, created_at)
VALUES (NULL, 'Example', DEFAULT);

INSERT INTO table_name (column1, column2)
VALUES 
  (value1, value2),
  (value3, value4),
  ...
  (valueN-1, valueN);

SELECT column1, column2 FROM table_name;

SELECT * FROM table_name;

SELECT DISTINCT column1 FROM table_name;

SELECT 
  column1 AS alias1,
  column2 AS alias2
FROM table_name;

SELECT t.column1, t.column2 
FROM table_name AS t;

SELECT 
  price,
  quantity,
  price * quantity AS total
FROM orders;

SELECT 
  name,
  UPPER(name) AS uppercase_name,
  LENGTH(name) AS name_length
FROM customers;

SELECT 
  name,
  age,
  CASE 
    WHEN age < 18 THEN 'Minor'
    WHEN age >= 18 AND age < 65 THEN 'Adult'
    ELSE 'Senior'
  END AS age_category
FROM users;

SELECT 
  name,
  IF(active = 1, 'Active', 'Inactive') AS status
FROM users;

SELECT 
  name,
  COALESCE(phone, email, 'No Contact') AS contact
FROM customers;

SELECT * FROM users 
WHERE age > 18;

SELECT * FROM products 
WHERE category = 'Electronics' 
  AND price < 1000;

SELECT * FROM products 
WHERE category IN ('Electronics', 'Computers', 'Accessories');

SELECT * FROM products 
WHERE category NOT IN ('Clothing', 'Furniture');

SELECT * FROM products 
WHERE price BETWEEN 100 AND 500;

SELECT * FROM users 
WHERE name LIKE 'J%'; -- Names starting with J
-- % matches any number of characters
-- _ matches exactly one character

SELECT * FROM users 
WHERE email NOT LIKE '%@gmail.com';

SELECT * FROM users 
WHERE name REGEXP '^[AEI]'; -- Names starting with A, E, or I

SELECT * FROM users 
WHERE phone IS NULL;

SELECT * FROM users 
WHERE phone IS NOT NULL;

SELECT * FROM products 
WHERE category_id IN (
  SELECT id FROM categories 
  WHERE active = 1
);

SELECT * FROM customers 
WHERE EXISTS (
  SELECT 1 FROM orders 
  WHERE orders.customer_id = customers.id
);

SELECT * FROM users 
ORDER BY last_name;

SELECT * FROM products 
ORDER BY price ASC;

SELECT * FROM products 
ORDER BY price DESC;

SELECT * FROM users 
ORDER BY last_name ASC, first_name ASC;

SELECT * FROM products 
ORDER BY category ASC, price DESC;

SELECT *, price * quantity AS total 
FROM order_items 
ORDER BY total DESC;

SELECT * FROM products 
ORDER BY FIELD(status, 'Featured', 'Active', 'Inactive');

SELECT * FROM orders 
ORDER BY 
  CASE 
    WHEN status = 'Processing' THEN 1
    WHEN status = 'Shipped' THEN 2
    ELSE 3
  END;

-- NULL values first
SELECT * FROM users 
ORDER BY phone IS NOT NULL, phone ASC;

-- NULL values last
SELECT * FROM users 
ORDER BY phone IS NULL, phone ASC;

SELECT name, price, category FROM products 
ORDER BY 2 DESC; -- Sort by price (the 2nd column)

SELECT * FROM products 
LIMIT 10;

SELECT * FROM products 
LIMIT 10 OFFSET 20; -- Skip 20 rows, return next 10

SELECT * FROM products 
LIMIT 20, 10; -- Skip 20 rows, return next 10

-- For page 3 with 10 items per page
SELECT * FROM products 
LIMIT 10 OFFSET 20; -- (page - 1) * items_per_page

-- Top 5 most expensive products
SELECT * FROM products 
ORDER BY price DESC 
LIMIT 5;

SELECT * FROM orders 
WHERE customer_id IN (
  SELECT id FROM customers 
  ORDER BY total_spend DESC 
  LIMIT 10
);

SELECT * FROM products 
ORDER BY RAND() 
LIMIT 1;

-- Top 3 categories by total sales
SELECT category, SUM(price * quantity) AS total_sales 
FROM order_items
JOIN products ON order_items.product_id = products.id
GROUP BY category
ORDER BY total_sales DESC
LIMIT 3;

-- Using prepared statements
PREPARE stmt FROM 'SELECT * FROM products LIMIT ?';
SET @limit = 5;
EXECUTE stmt USING @limit;
DEALLOCATE PREPARE stmt;

SELECT * FROM products; -- No limit, returns all rows

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

-- Update all rows (use with caution)
UPDATE products
SET in_stock = 0;

UPDATE products
SET price = price * 1.1 -- Increase price by 10%
WHERE category = 'Electronics';

UPDATE users
SET last_login = NOW(),
    login_count = login_count + 1
WHERE id = 123;

UPDATE orders
SET status = 
  CASE 
    WHEN payment_received = 1 AND shipped = 0 THEN 'Paid'
    WHEN payment_received = 1 AND shipped = 1 THEN 'Completed'
    ELSE 'Pending'
  END;

UPDATE users
SET status = 'inactive'
WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)
LIMIT 100;

UPDATE products
SET featured = 1
WHERE category = 'Electronics'
ORDER BY price DESC
LIMIT 5;

UPDATE employees
SET salary = salary * 1.1
WHERE department_id IN (
  SELECT id FROM departments WHERE performance_rating > 8
);

UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.discount = 0.1
WHERE c.loyalty_points > 1000;

UPDATE IGNORE users
SET email = LOWER(email),
    username = LOWER(username);

DELETE FROM table_name
WHERE condition;

-- Delete all rows (use with extreme caution)
DELETE FROM temporary_logs;

DELETE FROM logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR)
LIMIT 1000;

DELETE FROM logs
WHERE type = 'error'
ORDER BY created_at
LIMIT 100;

DELETE FROM cart_items
WHERE user_id IN (
  SELECT id FROM users
  WHERE last_activity < DATE_SUB(NOW(), INTERVAL 30 DAY)
);

DELETE c, o
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE c.active = 0;

TRUNCATE TABLE logs;

-- DELETE: one by one, can be rolled back, preserves AUTO_INCREMENT
DELETE FROM table_name;

-- TRUNCATE: drops and recreates table, faster, resets AUTO_INCREMENT
TRUNCATE TABLE table_name;

-- Instead of deleting the records
UPDATE users
SET deleted_at = NOW(), active = 0
WHERE id = 123;

DELETE IGNORE FROM products
WHERE category_id = 5;

SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

SELECT o.id, p.name
FROM orders o
RIGHT JOIN products p ON o.product_id = p.id;

-- Emulating FULL JOIN in MySQL
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
UNION
SELECT c.name, o.id
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id
WHERE c.id IS NULL;

SELECT c.name, p.name
FROM customers c
CROSS JOIN products p;

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;

SELECT o.id, c.name, p.name, p.price
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'shipped'
AND c.country = 'USA';

SELECT o.id, c.name
FROM orders o
JOIN customers c USING (customer_id);

SELECT o.id, c.name
FROM orders o
NATURAL JOIN customers c;

SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;

-- Count all rows
SELECT COUNT(*) FROM orders;

-- Count non-NULL values in a column
SELECT COUNT(phone) FROM customers;

SELECT category, SUM(price) AS total_value
FROM products
GROUP BY category;

SELECT category, AVG(price) AS average_price
FROM products
GROUP BY category;

SELECT category, MIN(price) AS lowest_price
FROM products
GROUP BY category;

SELECT category, MAX(price) AS highest_price
FROM products
GROUP BY category;

SELECT category, GROUP_CONCAT(name SEPARATOR ', ') AS products
FROM products
GROUP BY category;

SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING product_count > 5;

SELECT category, subcategory, SUM(price) AS total
FROM products
GROUP BY category, subcategory WITH ROLLUP;

SELECT category,
  COUNT(*) AS count,
  MIN(price) AS min_price,
  MAX(price) AS max_price,
  AVG(price) AS avg_price,
  SUM(price) AS total_value
FROM products
GROUP BY category;

SELECT category, manufacturer, COUNT(*) AS product_count
FROM products
GROUP BY category, manufacturer
ORDER BY category, product_count DESC;

SELECT c.country, COUNT(o.id) AS order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.country
ORDER BY order_count DESC;

SELECT name
FROM products
WHERE category_id IN (
  SELECT id FROM categories
  WHERE parent_id = 5
);

SELECT name, price
FROM products
WHERE price > (
  SELECT AVG(price) FROM products
);

SELECT *
FROM customers
WHERE id IN (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE total > 1000
);

SELECT *
FROM products
WHERE id NOT IN (
  SELECT product_id
  FROM order_items
);

SELECT *
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
  AND o.created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)
);

SELECT *
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM order_items oi
  WHERE oi.product_id = p.id
);

SELECT 
  name,
  price,
  (SELECT AVG(price) FROM products) AS avg_price,
  price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

SELECT p1.name, p1.price
FROM products p1
WHERE p1.price > (
  SELECT AVG(p2.price)
  FROM products p2
  WHERE p2.category_id = p1.category_id
);

SELECT category, avg_price
FROM (
  SELECT category, AVG(price) AS avg_price
  FROM products
  GROUP BY category
) AS category_stats
WHERE avg_price > 100;

SELECT name, price
FROM products
WHERE price > ALL (
  SELECT price
  FROM products
  WHERE category_id = 3
);

SELECT name, price
FROM products
WHERE price > ANY (
  SELECT price
  FROM products
  WHERE category_id = 3
);

SELECT name,
  price,
  (SELECT MAX(price) FROM products) AS max_price,
  price / (SELECT MAX(price) FROM products) * 100 AS percentage_of_max
FROM products;

CREATE VIEW product_details AS
SELECT 
  p.id, 
  p.name, 
  p.price, 
  c.name AS category
FROM products p
JOIN categories c ON p.category_id = c.id;

CREATE OR REPLACE VIEW high_value_customers AS
SELECT 
  id, 
  name, 
  email, 
  total_orders
FROM customers
WHERE total_orders > 10;

DROP VIEW product_details;

DROP VIEW IF EXISTS product_details;

SHOW CREATE VIEW product_details;

ALTER VIEW product_details AS
SELECT 
  p.id, 
  p.name, 
  p.price, 
  p.description,
  c.name AS category
FROM products p
JOIN categories c ON p.category_id = c.id;

SELECT * FROM product_details
WHERE price > 100;

CREATE VIEW active_products AS
SELECT id, name, price, status
FROM products
WHERE status = 'active'
WITH CHECK OPTION;

CREATE VIEW basic_product_info AS
SELECT id, name, price, category_id
FROM products;

CREATE VIEW order_summary AS
SELECT 
  o.id AS order_id,
  c.name AS customer_name,
  o.total AS order_total,
  o.created_at AS order_date
FROM orders o
JOIN customers c ON o.customer_id = c.id;

CREATE VIEW category_stats AS
SELECT 
  category_id,
  COUNT(*) AS product_count,
  AVG(price) AS avg_price,
  MIN(price) AS min_price,
  MAX(price) AS max_price
FROM products
GROUP BY category_id;

CREATE VIEW public_customer_info AS
SELECT id, name, city, country
FROM customers;

-- Then grant access to this view instead of the base table
GRANT SELECT ON public_customer_info TO 'app_user'@'localhost';

CREATE INDEX idx_last_name ON users(last_name);

CREATE UNIQUE INDEX idx_email ON users(email);

CREATE INDEX idx_name_email ON users(last_name, first_name, email);

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  total DECIMAL(10,2),
  INDEX idx_user_id (user_id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

SHOW INDEXES FROM table_name;

DROP INDEX idx_name ON table_name;

ALTER TABLE users
ADD INDEX idx_created_at (created_at);

CREATE INDEX idx_name_prefix ON products(name(10));

CREATE INDEX idx_created_desc ON users(created_at DESC);

CREATE FULLTEXT INDEX idx_content ON articles(title, content);

CREATE SPATIAL INDEX idx_location ON stores(location);

CREATE INDEX idx_test ON users(test_column) INVISIBLE;

CREATE INDEX idx_upper_email ON users((UPPER(email)));

START TRANSACTION;

BEGIN;

COMMIT;

ROLLBACK;

SAVEPOINT my_savepoint;

ROLLBACK TO SAVEPOINT my_savepoint;

RELEASE SAVEPOINT my_savepoint;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;

INSERT INTO orders (customer_id, total)
VALUES (123, 456.78);

-- Get the new order ID
SET @order_id = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES 
  (@order_id, 1, 2, 100.00),
  (@order_id, 2, 1, 256.78);

-- If everything is OK, commit the transaction
COMMIT;

-- On error, you would do:
-- ROLLBACK;

-- Available levels:
-- READ UNCOMMITTED
-- READ COMMITTED
-- REPEATABLE READ (default in MySQL)
-- SERIALIZABLE

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION READ ONLY;

SELECT @@autocommit;

SET autocommit = 0;

SET autocommit = 1;

DELIMITER //

CREATE PROCEDURE get_customers(IN country_filter VARCHAR(50))
BEGIN
  SELECT * FROM customers
  WHERE country = country_filter;
END //

DELIMITER ;

CALL get_customers('USA');

DROP PROCEDURE get_customers;

DELIMITER //

CREATE FUNCTION calculate_discount(
  price DECIMAL(10,2),
  discount_percent INT
) 
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  DECLARE discounted_price DECIMAL(10,2);
  SET discounted_price = price * (1 - discount_percent/100);
  RETURN discounted_price;
END //

DELIMITER ;

SELECT 
  name,
  price,
  calculate_discount(price, 10) AS discounted_price
FROM products;

DROP FUNCTION calculate_discount;

DELIMITER //

CREATE PROCEDURE get_product_count(
  IN category_id INT,
  OUT product_count INT
)
BEGIN
  SELECT COUNT(*) INTO product_count
  FROM products
  WHERE products.category_id = category_id;
END //

DELIMITER ;

SET @count = 0;
CALL get_product_count(5, @count);
SELECT @count;

DELIMITER //

CREATE PROCEDURE double_value(
  INOUT value INT
)
BEGIN
  SET value = value * 2;
END //

DELIMITER ;

SET @val = 5;
CALL double_value(@val);
SELECT @val; -- Will return 10

DELIMITER //

CREATE PROCEDURE insert_product(
  IN p_name VARCHAR(100),
  IN p_price DECIMAL(10,2)
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SELECT 'Error occurred. Transaction rolled back.' AS message;
  END;
  
  START TRANSACTION;
    INSERT INTO products (name, price)
    VALUES (p_name, p_price);
  COMMIT;
  
  SELECT 'Product added successfully.' AS message;
END //

DELIMITER ;

-- View all procedures in the current database
SHOW PROCEDURE STATUS WHERE Db = DATABASE();

-- View all functions in the current database
SHOW FUNCTION STATUS WHERE Db = DATABASE();

-- View procedure code
SHOW CREATE PROCEDURE procedure_name;

-- View function code
SHOW CREATE FUNCTION function_name;

DELIMITER //

CREATE PROCEDURE apply_discount(
  IN order_id INT
)
BEGIN
  DECLARE total DECIMAL(10,2);
  
  SELECT total_amount INTO total
  FROM orders
  WHERE id = order_id;
  
  IF total > 1000 THEN
    UPDATE orders SET discount = 0.15 WHERE id = order_id;
  ELSEIF total > 500 THEN
    UPDATE orders SET discount = 0.1 WHERE id = order_id;
  ELSE
    UPDATE orders SET discount = 0.05 WHERE id = order_id;
  END IF;
END //

DELIMITER ;

DELIMITER //

CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  UPDATE customers
  SET order_count = order_count + 1
  WHERE id = NEW.customer_id;
END //

DELIMITER ;

DELIMITER //

CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  SET NEW.email = LOWER(NEW.email);
  SET NEW.created_at = NOW();
END //

DELIMITER ;

DELIMITER //

CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO order_log (order_id, action, timestamp)
  VALUES (NEW.id, 'Order created', NOW());
END //

DELIMITER ;

DELIMITER //

CREATE TRIGGER before_product_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
  
  IF NEW.price != OLD.price THEN
    INSERT INTO price_history (product_id, old_price, new_price, changed_at)
    VALUES (OLD.id, OLD.price, NEW.price, NOW());
  END IF;
END //

DELIMITER ;

DELIMITER //

CREATE TRIGGER after_status_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
  IF NEW.status != OLD.status THEN
    INSERT INTO order_status_log (order_id, old_status, new_status, changed_at)
    VALUES (OLD.id, OLD.status, NEW.status, NOW());
  END IF;
END //

DELIMITER ;

DELIMITER //

CREATE TRIGGER before_user_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
  INSERT INTO deleted_users (id, name, email, deleted_at)
  VALUES (OLD.id, OLD.name, OLD.email, NOW());
END //

DELIMITER ;

DELIMITER //

CREATE TRIGGER after_product_delete
AFTER DELETE ON products
FOR EACH ROW
BEGIN
  DELETE FROM product_tags WHERE product_id = OLD.id;
END //

DELIMITER ;

DROP TRIGGER before_user_insert;

SHOW TRIGGERS;

-- Or for specific database/table
SHOW TRIGGERS WHERE `Table` = 'users';

-- You need separate triggers for each event
-- This example shows how to create two related triggers

DELIMITER //

CREATE TRIGGER before_inventory_insert
BEFORE INSERT ON inventory
FOR EACH ROW
BEGIN
  SET NEW.last_updated = NOW();
END //

CREATE TRIGGER before_inventory_update
BEFORE UPDATE ON inventory
FOR EACH ROW
BEGIN
  SET NEW.last_updated = NOW();
END //

DELIMITER ;

DELIMITER //

CREATE TRIGGER check_minimum_price
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.price < 0 THEN
    SET NEW.price = 0;
  END IF;
END //

DELIMITER ;

DELIMITER //

CREATE TRIGGER prevent_price_decrease
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.price < OLD.price THEN
    SIGNAL SQLSTATE '45000' 
    SET MESSAGE_TEXT = 'Price cannot be decreased';
  END IF;
END //

DELIMITER ;

mysqldump -u username -p database_name > backup.sql

mysqldump -u username -p --databases db1 db2 > backup.sql

mysqldump -u username -p --all-databases > backup.sql

mysqldump -u username -p database_name table1 table2 > backup.sql

mysqldump -u username -p --single-transaction --routines --triggers database_name > backup.sql

mysqldump -u username -p database_name --ignore-table=database_name.table_to_ignore > backup.sql

mysqldump -u username -p database_name | gzip > backup.sql.gz

mysql -u username -p database_name < backup.sql

gunzip < backup.sql.gz | mysql -u username -p database_name

mysqlpump -u username -p --parallel-schemas=4 database_name > backup.sql

mysqldump -u username -p --verbose database_name > backup.sql

mysqlbinlog binlog.000001 binlog.000002 > binlog_backup.sql

mysqlbackup --user=username --password=password --backup-dir=/backup backup

xtrabackup --backup --target-dir=/backup

xtrabackup --prepare --target-dir=/backup
xtrabackup --copy-back --target-dir=/backup

mysqldump -u username -p database_name | openssl enc -aes-256-cbc -salt -out backup.sql.enc

# Add to crontab (crontab -e):
0 2 * * * mysqldump -u username -p"password" database_name | gzip > /backup/db_$(date +\%Y\%m\%d).sql.gz

LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- Skip header row

LOAD DATA LOCAL INFILE '/path/to/local/data.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

SELECT * FROM table_name
INTO OUTFILE '/path/to/export.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

mysqldump -u username -p database_name table_name > table_export.sql

mysqlimport -u username -p --fields-terminated-by=',' \
  --lines-terminated-by='\n' database_name /path/to/data.csv

SELECT JSON_OBJECT(
  'id', id,
  'name', name,
  'email', email
) AS json_data
FROM users
INTO OUTFILE '/path/to/users.json';

-- Create a table for JSON data
CREATE TABLE json_import (data JSON);

-- Load JSON file
LOAD DATA INFILE '/path/to/data.json'
INTO TABLE json_import;

mysqldump -u username -p --tab=/path/to/directory database_name

-- First, output column headers
SELECT 'id', 'name', 'email' 
UNION ALL
SELECT id, name, email FROM users
INTO OUTFILE '/path/to/users_with_headers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- Using LOAD XML (MySQL 5.5+)
LOAD XML INFILE '/path/to/data.xml'
INTO TABLE table_name
ROWS IDENTIFIED BY 'row';

-- Select with XML functions
SELECT
  CONCAT(
    '<user>',
    '<id>', id, '</id>',
    '<name>', name, '</name>',
    '<email>', email, '</email>',
    '</user>'
  ) AS xml_data
FROM users
INTO OUTFILE '/path/to/users.xml';

mysqldump -u username -p --xml database_name table_name > table_export.xml

LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(@col1, @col2, @col3)
SET 
  id = @col1,
  name = UPPER(@col2),
  email = LOWER(@col3),
  created_at = NOW();

-- Save Excel as CSV first, then:
LOAD DATA INFILE '/path/to/excel_export.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' -- Excel uses CRLF
IGNORE 1 ROWS;

-- Relax constraints during import
SET SESSION sql_mode='';

-- Load data
LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ',';

GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'localhost';

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

GRANT ALL PRIVILEGES ON database_name.* 
TO 'username'@'localhost' IDENTIFIED BY 'password';

GRANT SELECT, UPDATE ON database_name.table_name TO 'username'@'localhost';

GRANT SELECT (id, name), UPDATE (name) 
ON database_name.table_name TO 'username'@'localhost';

GRANT SELECT ON database_name.* 
TO 'username'@'localhost' WITH GRANT OPTION;

REVOKE INSERT ON database_name.* FROM 'username'@'localhost';

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'localhost';

REVOKE GRANT OPTION ON database_name.* FROM 'username'@'localhost';

SHOW GRANTS FOR 'username'@'localhost';

GRANT CREATE USER ON *.* TO 'admin'@'localhost';

-- Data privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON db.* TO 'user'@'localhost';

-- Structure privileges
GRANT CREATE, ALTER, DROP ON db.* TO 'user'@'localhost';

-- Admin privileges
GRANT CREATE USER, RELOAD, PROCESS ON *.* TO 'admin'@'localhost';

-- Create role
CREATE ROLE 'app_read', 'app_write';

-- Grant privileges to role
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';

-- Grant role to users
GRANT 'app_read', 'app_write' TO 'app_user'@'localhost';

-- Set default role
SET DEFAULT ROLE 'app_read', 'app_write' TO 'app_user'@'localhost';

SET ROLE 'app_read';

SELECT User, Host FROM mysql.user;

CREATE USER 'username'@'localhost'
WITH MAX_QUERIES_PER_HOUR 1000
     MAX_UPDATES_PER_HOUR 500
     MAX_CONNECTIONS_PER_HOUR 200
     MAX_USER_CONNECTIONS 10;

SHOW PROCESSLIST;

SHOW FULL PROCESSLIST;

KILL CONNECTION 1234;

KILL QUERY 1234;

SET SESSION sort_buffer_size = 10485760;

SHOW SESSION VARIABLES;

SHOW SESSION VARIABLES LIKE 'max%';

SHOW SESSION STATUS;

SET SESSION SQL_MODE = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES';

SELECT CONNECTION_ID();

SELECT GET_LOCK('my_lock', 10);

SELECT RELEASE_LOCK('my_lock');

SELECT IS_FREE_LOCK('my_lock');

SHOW ENGINE INNODB STATUS;

SET SESSION wait_timeout = 28800;

SET SESSION interactive_timeout = 28800;

Linux: /etc/mysql/my.cnf or /etc/my.cnf
Windows: C:\ProgramData\MySQL\MySQL Server X.Y\my.ini
macOS: /usr/local/mysql/etc/my.cnf

SHOW VARIABLES;

SHOW VARIABLES LIKE 'innodb%';

SHOW GLOBAL VARIABLES;

SET GLOBAL max_connections = 500;

SET PERSIST max_connections = 500;

SHOW STATUS;

SHOW GLOBAL STATUS;

[client] - Client settings
[mysqld] - Server settings
[mysqldump] - Backup tool settings
[mysql] - MySQL client settings

[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

[mysqld]
max_connections = 500
table_open_cache = 4000
query_cache_size = 0 # Disabled in MySQL 8.0+
max_allowed_packet = 64M

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

[mysqld]
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 14
max_binlog_size = 100M

[mysqld]
log_error = /var/log/mysql/error.log
general_log = 0
general_log_file = /var/log/mysql/mysql.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

mysqld --verbose --help

RESET PERSIST max_connections;

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

EXPLAIN FORMAT=JSON SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id WHERE u.status = 'active';

EXPLAIN ANALYZE SELECT * FROM users 
JOIN orders ON users.id = orders.user_id 
WHERE users.created_at > '2022-01-01';

SHOW INDEX FROM users;

CREATE INDEX idx_email ON users(email);

SET profiling = 1;
-- Run your query
SELECT * FROM large_table WHERE complex_condition;
-- Get the profile
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

SHOW STATUS LIKE 'Qcache%';

ANALYZE TABLE users;

OPTIMIZE TABLE orders;

CREATE INDEX idx_user_email_status ON users(email, status);

CREATE INDEX idx_last_first_email ON users(last_name, first_name, email);

-- Instead of selecting all rows
SELECT * FROM large_table LIMIT 1000;

-- Instead of:
SELECT * FROM users;
-- Use:
SELECT id, name, email FROM users;

CREATE INDEX idx_user_id ON orders(user_id);

CREATE TABLE sales (
  id INT NOT NULL,
  sale_date DATE NOT NULL,
  amount DECIMAL(10,2),
  PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p0 VALUES LESS THAN (2020),
  PARTITION p1 VALUES LESS THAN (2021),
  PARTITION p2 VALUES LESS THAN (2022),
  PARTITION p3 VALUES LESS THAN MAXVALUE
);

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- Log queries longer than 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Find queries with full table scans
SELECT digest_text, count_star, sum_timer_wait
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text LIKE '%select%'
ORDER BY sum_timer_wait DESC LIMIT 10;

-- Show top 10 queries by average execution time
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;

SHOW WARNINGS;

SHOW ERRORS;

SHOW COUNT(*) WARNINGS;

DELIMITER //
CREATE PROCEDURE check_negative_balance(IN account_id INT, IN amount DECIMAL(10,2))
BEGIN
  DECLARE current_balance DECIMAL(10,2);
  
  SELECT balance INTO current_balance FROM accounts WHERE id = account_id;
  
  IF current_balance - amount < 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Insufficient funds', 
          MYSQL_ERRNO = 1001;
  END IF;
END //
DELIMITER ;

'00000': Success
'01000': Warning
'23000': Integrity constraint violation
'42000': Syntax error or access rule violation
'45000': Custom user-defined error

DELIMITER //
CREATE PROCEDURE transfer_funds(
  IN from_account INT, 
  IN to_account INT, 
  IN amount DECIMAL(10,2)
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SELECT 'An error occurred. Transaction rolled back.' AS message;
  END;
  
  START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
  COMMIT;
  
  SELECT 'Transfer completed successfully.' AS message;
END //
DELIMITER ;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -- Continue execution
DECLARE EXIT HANDLER FOR SQLEXCEPTION     -- Exit current block
DECLARE UNDO HANDLER FOR SQLEXCEPTION     -- Undo work (rarely used)

-- By SQLSTATE
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' ...

-- By MySQL error code
DECLARE CONTINUE HANDLER FOR 1062 ...  -- Duplicate key

-- By named condition
DECLARE duplicate_key CONDITION FOR 1062;
DECLARE CONTINUE HANDLER FOR duplicate_key ...

DELIMITER //
CREATE PROCEDURE p()
BEGIN
  DECLARE err_msg TEXT;
  DECLARE err_no INT;
  
  -- Execute a statement
  INSERT INTO table_with_unique_constraint VALUES(1, 'Duplicate');
  
  -- Get error info
  GET DIAGNOSTICS CONDITION 1
    err_no = MYSQL_ERRNO,
    err_msg = MESSAGE_TEXT;
    
  SELECT err_no, err_msg;
END //
DELIMITER ;

-- Insert will continue even if some rows cause duplicate key errors
INSERT IGNORE INTO users (id, name) VALUES (1, 'John'), (2, 'Jane');

SHOW VARIABLES LIKE 'log_error';

DELIMITER //
CREATE PROCEDURE validate_and_transfer(from_acc INT, to_acc INT, amount DECIMAL(10,2))
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- Log the error
    INSERT INTO error_log (error_time, procedure_name) 
    VALUES (NOW(), 'validate_and_transfer');
    
    -- Re-raise the error
    RESIGNAL;
  END;
  
  -- Procedure body
  CALL transfer_funds(from_acc, to_acc, amount);
END //
DELIMITER ;

SELECT NOW(); -- 2023-07-19 15:30:45

SELECT CURDATE(); -- 2023-07-19

SELECT CURTIME(); -- 15:30:45

SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y'); -- Wednesday, July 19, 2023
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 2023-07-19 15:30:45

SELECT STR_TO_DATE('July 19, 2023', '%M %d, %Y'); -- 2023-07-19

SELECT UNIX_TIMESTAMP(NOW()); -- 1689778245

SELECT FROM_UNIXTIME(1689778245); -- 2023-07-19 15:30:45

SELECT DATEDIFF('2023-07-30', '2023-07-19'); -- 11

SELECT TIMEDIFF('15:30:45', '10:15:30'); -- 05:15:15

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY); -- 7 days from now
SELECT DATE_ADD('2023-07-19', INTERVAL 3 MONTH); -- 2023-10-19

SELECT DATE_SUB(NOW(), INTERVAL 7 DAY); -- 7 days ago
SELECT DATE_SUB('2023-07-19', INTERVAL 3 MONTH); -- 2023-04-19

SELECT TIMESTAMPDIFF(MONTH, '2022-01-01', '2023-07-19'); -- 18
SELECT TIMESTAMPDIFF(HOUR, '2023-07-19 10:00:00', '2023-07-19 15:30:45'); -- 5

SELECT DAYNAME('2023-07-19'); -- Wednesday

SELECT MONTHNAME('2023-07-19'); -- July

SELECT EXTRACT(YEAR FROM '2023-07-19'); -- 2023
SELECT EXTRACT(MONTH FROM '2023-07-19'); -- 7
SELECT EXTRACT(DAY FROM '2023-07-19'); -- 19

SELECT YEAR('2023-07-19'); -- 2023
SELECT MONTH('2023-07-19'); -- 7
SELECT DAY('2023-07-19'); -- 19

SELECT HOUR('15:30:45'); -- 15
SELECT MINUTE('15:30:45'); -- 30
SELECT SECOND('15:30:45'); -- 45

SELECT LAST_DAY('2023-07-19'); -- 2023-07-31

SELECT MAKEDATE(2023, 200); -- 2023-07-19 (200th day of 2023)

SELECT DATE('2023-07-19 15:30:45'); -- 2023-07-19

SELECT TIME('2023-07-19 15:30:45'); -- 15:30:45

%Y - Year (4 digits)
%y - Year (2 digits)
%M - Month name (January..December)
%m - Month (01..12)
%d - Day of month (01..31)
%H - Hour (00..23)
%h - Hour (01..12)
%i - Minutes (00..59)
%s - Seconds (00..59)
%W - Weekday name (Sunday..Saturday)

SELECT CONCAT('Hello', ' ', 'World'); -- 'Hello World'

SELECT CONCAT_WS('-', 'Hello', 'World', '2023'); -- 'Hello-World-2023'

SELECT LENGTH('Hello'); -- 5

SELECT CHAR_LENGTH('Hello😊'); -- 6 (handles multi-byte chars)

SELECT LEFT('Hello World', 5); -- 'Hello'

SELECT RIGHT('Hello World', 5); -- 'World'

SELECT SUBSTRING('Hello World', 7, 5); -- 'World'
SELECT SUBSTRING('Hello World', 7); -- 'World' (to end)

SELECT LOCATE('World', 'Hello World'); -- 7
SELECT POSITION('World' IN 'Hello World'); -- 7

SELECT INSTR('Hello World', 'World'); -- 7

SELECT REPLACE('Hello World', 'World', 'MySQL'); -- 'Hello MySQL'

SELECT TRIM('  Hello World  '); -- 'Hello World'

SELECT LTRIM('  Hello'); -- 'Hello'
SELECT RTRIM('Hello  '); -- 'Hello'

SELECT LOWER('Hello WORLD'); -- 'hello world'

SELECT UPPER('Hello world'); -- 'HELLO WORLD'

SELECT REPEAT('MySQL ', 3); -- 'MySQL MySQL MySQL '

SELECT REVERSE('Hello'); -- 'olleH'

SELECT INSERT('Hello World', 7, 5, 'MySQL'); -- 'Hello MySQL'

SELECT LPAD('42', 5, '0'); -- '00042'

SELECT RPAD('Hello', 10, '.'); -- 'Hello.....'

SELECT CONCAT('Hello', SPACE(5), 'World'); -- 'Hello     World'

SELECT FORMAT(12345.678, 2); -- '12,345.68' (locale-dependent)

SELECT FIELD('b', 'a', 'b', 'c'); -- 2

SELECT FIND_IN_SET('b', 'a,b,c,d'); -- 2

SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2); -- 'www.mysql'
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2); -- 'mysql.com'

SELECT 'MySQL' REGEXP '^My'; -- 1 (true)
SELECT * FROM users WHERE name REGEXP '^A[a-z]*$';

SELECT REGEXP_REPLACE('Hello 123 World', '[0-9]+', 'number'); -- 'Hello number World'

SELECT REGEXP_LIKE('abc@example.com', '^[a-z]+@[a-z]+\.[a-z]+$'); -- 1

SELECT ABS(-15); -- 15

SELECT CEILING(15.4); -- 16

SELECT FLOOR(15.7); -- 15

SELECT ROUND(15.4); -- 15
SELECT ROUND(15.7); -- 16
SELECT ROUND(15.678, 2); -- 15.68

SELECT TRUNCATE(15.678, 2); -- 15.67
SELECT TRUNCATE(15.678, 0); -- 15

SELECT MOD(15, 4); -- 3
SELECT 15 % 4; -- 3

SELECT POW(2, 3); -- 8
SELECT POWER(2, 3); -- 8

SELECT SQRT(16); -- 4

SELECT EXP(1); -- 2.718281828459045

SELECT LN(2.718281828459045); -- 1

SELECT LOG(2.718281828459045); -- 1
SELECT LOG(10, 100); -- 2 (log base 10 of 100)

SELECT LOG2(8); -- 3

SELECT LOG10(100); -- 2

SELECT PI(); -- 3.141593

SELECT SIN(PI()/2); -- 1
SELECT COS(PI()); -- -1
SELECT TAN(PI()/4); -- 1

SELECT ASIN(1); -- 1.5707963267949 (π/2)
SELECT ACOS(-1); -- 3.141593 (π)
SELECT ATAN(1); -- 0.7853981633974483 (π/4)

SELECT DEGREES(PI()); -- 180

SELECT RADIANS(180); -- 3.141593

SELECT SIGN(-15); -- -1
SELECT SIGN(0); -- 0
SELECT SIGN(15); -- 1

SELECT RAND(); -- 0.123456789
SELECT RAND(42); -- Seeded random number

SELECT FLOOR(RAND() * 10); -- 0 to 9
SELECT FLOOR(RAND() * 10) + 1; -- 1 to 10
SELECT FLOOR(RAND() * (max-min+1)) + min; -- min to max

SELECT 15 DIV 2; -- 7 (integer division)

SELECT 15 / 2; -- 7.5 (floating-point division)

SELECT CONV('A', 16, 10); -- 10 (hex to decimal)
SELECT CONV('1010', 2, 10); -- 10 (binary to decimal)
SELECT CONV('10', 10, 16); -- A (decimal to hex)

SELECT BIN(10); -- '1010'

SELECT OCT(10); -- '12'

SELECT HEX(255); -- 'FF'

SELECT FORMAT(12345.678, 2); -- '12,345.68'

SELECT JSON_ARRAY(1, 'abc', NULL, TRUE); -- [1, "abc", null, true]

SELECT JSON_OBJECT('id', 87, 'name', 'carrot'); -- {"id": 87, "name": "carrot"}

SELECT JSON_QUOTE('hello'); -- "hello"

SELECT JSON_CONTAINS('{"a": 1, "b": 2}', '1', '$.a'); -- 1 (true)

SELECT JSON_EXTRACT('{"id": 14, "name": "Aztec"}', '$.name'); -- "Aztec"

SELECT '{"id": 14, "name": "Aztec"}' -> '$.name'; -- "Aztec"

SELECT '{"id": 14, "name": "Aztec"}' ->> '$.name'; -- Aztec

SELECT JSON_VALID('{"a": 1}'); -- 1 (true)
SELECT JSON_VALID('{"a":'); -- 0 (false)

SHOW DATABASES;

SHOW TABLES;

SHOW CREATE TABLE users;

SHOW COLUMNS FROM users;

DESCRIBE users;
DESC users;

SHOW INDEX FROM users;

SHOW PROCESSLIST;

SHOW STATUS;
SHOW STATUS LIKE 'Threads%';

SHOW VARIABLES;
SHOW VARIABLES LIKE 'max_connections';

SHOW ENGINES;

SHOW ENGINE INNODB STATUS;

SHOW GRANTS FOR 'user'@'localhost';

SHOW MASTER STATUS;

SHOW SLAVE STATUS\G

SHOW WARNINGS;

SHOW ERRORS;

FLUSH PRIVILEGES;

FLUSH LOGS;

FLUSH TABLES;

OPTIMIZE TABLE users;

ANALYZE TABLE users;

CHECK TABLE users;

REPAIR TABLE users;

KILL 123;  -- Kill thread by ID
KILL QUERY 123;  -- Kill just a query

# In /etc/my.cnf or my.ini (master server)
[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW  # or MIXED, STATEMENT
binlog_do_db = db_to_replicate  # Optional: only replicate specific DB
binlog_ignore_db = db_to_ignore  # Optional: don't replicate specific DB

# In /etc/my.cnf or my.ini (slave server)
[mysqld]
server-id = 2  # Must be unique
relay-log = relay-bin
read_only = 1  # Recommended for slave servers
replicate_do_db = db_to_replicate  # Optional
replicate_ignore_db = db_to_ignore  # Optional

-- On master server
CREATE USER 'repl_user'@'slave_ip' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'slave_ip';

-- On master server
SHOW MASTER STATUS;

/* Output:
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 73       | test         |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
*/

# On master server
mysqldump --all-databases --master-data=2 -u root -p > dump.sql

# Transfer dump.sql to slave server
scp dump.sql user@slave_ip:~/

# On slave server
mysql -u root -p < dump.sql

-- On slave server
CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000003',  -- From SHOW MASTER STATUS
  MASTER_LOG_POS=73;                   -- From SHOW MASTER STATUS

-- On slave server
START SLAVE;

-- On slave server
STOP SLAVE;

-- On slave server
SHOW SLAVE STATUS\G

/* Look for:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
*/

# On both master and slave in my.cnf:
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON

# Then on slave:
CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='password',
  MASTER_AUTO_POSITION=1;  -- Use GTID instead of binary log position

-- On slave server (MySQL 5.7+)
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

-- For GTID replication (MySQL 5.7+)
STOP SLAVE;
SET @@GLOBAL.GTID_NEXT='specific_gtid_to_skip';
BEGIN; COMMIT;
SET @@GLOBAL.GTID_NEXT='AUTOMATIC';
START SLAVE;

-- On slave server
CHANGE MASTER TO
  MASTER_HOST='master1_ip',
  MASTER_USER='repl_user1',
  MASTER_PASSWORD='password1',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=73
  FOR CHANNEL 'master1';

CHANGE MASTER TO
  MASTER_HOST='master2_ip',
  MASTER_USER='repl_user2',
  MASTER_PASSWORD='password2',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=120
  FOR CHANNEL 'master2';

START SLAVE FOR CHANNEL 'master1';
START SLAVE FOR CHANNEL 'master2';

-- On slave server
STOP SLAVE;
RESET SLAVE;  -- Or RESET SLAVE ALL to remove all connection info

-- On master server
RESET MASTER;  -- Deletes all binary logs

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'Str0ng_P@ssw0rd!';

-- Instead of:
GRANT ALL ON *.* TO 'user'@'localhost';

-- Better:
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'localhost';
GRANT SELECT ON app_db.sensitive_table TO 'reporting_user'@'localhost';

CREATE ROLE 'app_read', 'app_write';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
GRANT 'app_read' TO 'reporting_user'@'localhost';
GRANT 'app_read', 'app_write' TO 'admin_user'@'localhost';

-- Check for anonymous users
SELECT user, host FROM mysql.user WHERE user = '';

-- Remove anonymous users
DELETE FROM mysql.user WHERE user = '';
FLUSH PRIVILEGES;

DROP DATABASE test;
DELETE FROM mysql.db WHERE db = 'test' OR db LIKE 'test\\_%';
FLUSH PRIVILEGES;

-- Create users with specific host patterns
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'password';

-- Or secure the MySQL port in firewall
-- iptables -A INPUT -p tcp --dport 3306 -s 192.168.1.0/24 -j ACCEPT
-- iptables -A INPUT -p tcp --dport 3306 -j DROP

# In my.cnf
[mysqld]
ssl_ca=ca.pem
ssl_cert=server-cert.pem
ssl_key=server-key.pem

# Create user requiring SSL
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'password' REQUIRE SSL;

# MySQL 8.0+ uses caching_sha2_password by default
# For clients that don't support it:
CREATE USER 'app_user'@'localhost' 
IDENTIFIED WITH mysql_native_password BY 'password';

# In my.cnf
[mysqld]
plugin-load-add=validate_password.so
validate_password_policy=MEDIUM

# Check settings
SHOW VARIABLES LIKE 'validate_password%';

# In my.cnf
[mysqld]
plugin-load-add=audit_log.so
audit_log_file=/var/log/mysql/audit.log
audit_log_policy=ALL

-- MySQL 8.0+
ALTER USER 'app_user'@'localhost' ACCOUNT LOCK;

-- Re-enable later
ALTER USER 'app_user'@'localhost' ACCOUNT UNLOCK;

-- Expire password after 90 days
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Expire password now
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE;

# Create mysql user if not exists
useradd -r -g mysql mysql

# Ensure MySQL data directory permissions
chown -R mysql:mysql /var/lib/mysql

# In my.cnf
[mysqld]
local-infile=0

# Check setting
SHOW VARIABLES LIKE 'local_infile';

# Limit secure_file_priv to a specific directory
# In my.cnf
[mysqld]
secure_file_priv=/var/mysql/secure_uploads

# Or disable completely
secure_file_priv=NULL

# Check setting
SHOW VARIABLES LIKE 'secure_file_priv';

# Using encryption for backups
mysqldump --all-databases | openssl enc -aes-256-cbc -salt > backup.sql.enc

# Decrypt
openssl enc -d -aes-256-cbc -in backup.sql.enc > backup.sql

# In my.cnf
[mysqld]
skip-networking
socket=/var/run/mysqld/mysqld.sock

-- List all users
SELECT user, host FROM mysql.user;

-- Review user privileges
SELECT * FROM mysql.user WHERE user = 'app_user';

# Linux (systemd)
sudo systemctl start mysql

# Linux (init.d)
sudo service mysql start

# Windows
net start mysql

# macOS
sudo launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

# Linux (systemd)
sudo systemctl stop mysql

# Linux (init.d)
sudo service mysql stop

# Windows
net stop mysql

# macOS
sudo launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

# Linux (systemd)
sudo systemctl restart mysql

# Linux (init.d)
sudo service mysql restart

# Windows
net stop mysql && net start mysql

# macOS
sudo launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
sudo launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

# Linux (systemd)
sudo systemctl status mysql

# Linux (init.d)
sudo service mysql status

# Windows
sc query mysql

# macOS
launchctl list | grep mysql

# Basic login
mysql -u root -p

# Specify host and port
mysql -h hostname -P 3306 -u username -p

# Specify database
mysql -u username -p database_name

# Execute SQL from command line
mysql -u root -p -e "SELECT VERSION();"

# Vertical output format
mysql -u root -p -e "SELECT * FROM users LIMIT 2" --vertical

# Batch mode (tab-separated, non-interactive)
mysql -u root -p --batch -e "SELECT * FROM users LIMIT 2"

# XML output
mysql -u root -p -e "SELECT * FROM users LIMIT 2" --xml

# Backup single database
mysqldump -u root -p database_name > backup.sql

# Backup multiple databases
mysqldump -u root -p --databases db1 db2 > backup.sql

# Backup all databases
mysqldump -u root -p --all-databases > full_backup.sql

# Backup structure only (no data)
mysqldump -u root -p --no-data database_name > schema.sql

# Restore a database
mysql -u root -p database_name < backup.sql

# Restore multiple databases
mysql -u root -p < backup.sql

# Linux
/etc/my.cnf
/etc/mysql/my.cnf
/etc/mysql/mysql.conf.d/mysqld.cnf

# Windows
C:\ProgramData\MySQL\MySQL Server x.y\my.ini

# macOS
/usr/local/mysql/etc/my.cnf
/etc/my.cnf

# Find error log location
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_error';"

# Common error log locations
# Linux
/var/log/mysql/error.log
# Windows
C:\ProgramData\MySQL\MySQL Server x.y\Data\hostname.err

# Stop MySQL server
sudo systemctl stop mysql

# Start in safe mode
sudo mysqld_safe --skip-grant-tables --skip-networking &

# Connect without password
mysql -u root

# Set new password
mysql> USE mysql;
mysql> UPDATE user SET authentication_string=PASSWORD('new_password') 
       WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

# Restart server normally
sudo systemctl restart mysql

# Using source command
mysql -u root -p
mysql> USE database_name;
mysql> source /path/to/large_file.sql;

# Using shell pipe
pv large_file.sql | mysql -u root -p database_name

sudo mysql_secure_installation

# View history file
cat ~/.mysql_history

# Clear history
rm ~/.mysql_history
touch ~/.mysql_history

# From command line
mysql -V
mysqladmin -V

# From MySQL client
SELECT VERSION();

• Roles for access control
• Improved data dictionary
• JSON enhancements
• Window functions (OVER clause)
• Common Table Expressions (WITH)
• Invisible indexes
• Descending indexes
• Default to utf8mb4 character set
• Resource groups
• Default authentication: caching_sha2_password

-- JSON functions
SELECT JSON_TABLE(...);

-- Window functions
SELECT name, salary, 
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

-- Common Table Expressions (CTE)
WITH RECURSIVE cte AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

• JSON data type
• Generated columns
• InnoDB as default engine
• GTID improvements
• Native partitioning
• Performance Schema improvements
• sys schema added
• Support for IPv6
• Default authentication: mysql_native_password

-- JSON data type
CREATE TABLE users (
  id INT PRIMARY KEY,
  data JSON
);

-- Generated columns
CREATE TABLE circle (
  radius DOUBLE,
  area DOUBLE AS (PI() * radius * radius) STORED
);

• Global Transaction IDs (GTIDs)
• Improved replication
• Online DDL operations
• EXPLAIN for non-SELECT statements
• Transportable tablespaces
• Improved optimizer
• Improved Performance Schema
• Full-text search for InnoDB

-- EXPLAIN for non-SELECT
EXPLAIN UPDATE users SET status = 'active' WHERE id = 123;

-- Online ALTER TABLE
ALTER TABLE users ADD INDEX idx_email (email), 
ALGORITHM=INPLACE, LOCK=NONE;

• InnoDB storage engine improvements
• Semi-synchronous replication
• Performance Schema
• Improved UTF-8 support
• Plugin authentication
• Partitioning enhancements
• SIGNAL and RESIGNAL support

-- SIGNAL error handling
CREATE PROCEDURE check_value(value INT)
BEGIN
  IF value < 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Value cannot be negative';
  END IF;
END;

• 5.7 to 8.0: Authentication plugin change
• 5.7 to 8.0: Removal of obsolete features
• 5.7 to 8.0: SQL mode changes
• 5.7 to 8.0: Reserved keywords added
• 5.7 to 8.0: Case sensitivity changes

# 5.7 to 8.0 authentication plugin compatibility
CREATE USER 'user'@'localhost' 
IDENTIFIED WITH mysql_native_password BY 'password';

# Check SQL mode
SELECT @@sql_mode;

# Check reserved keywords
SELECT 'group' FROM users;  -- Error in 8.0, must use backticks
SELECT `group` FROM users;  -- Correct in 8.0

• 8.0: CTE and window functions
• 8.0: TABLE statement
• 8.0: EXCEPT and INTERSECT operators
• 8.0: GROUPING function
• 5.7+: JSON functions
• 5.7+: Generated columns
• 5.6+: EXPLAIN FORMAT=JSON

-- MySQL 8.0 only
SELECT * FROM table1 
EXCEPT 
SELECT * FROM table2;

-- TABLE statement (8.0+)
TABLE users;  -- Shorthand for SELECT * FROM users

-- 5.7+ JSON functions
SELECT JSON_EXTRACT(data, '$.name') FROM users;