MySQL is an open-source relational database management system based on SQL (Structured Query Language).

Some features include cross-platform support, high performance, security, and scalability.

1. Download MySQL Installer
2. Run setup and follow wizard
3. Configure root password and start service

sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation

sudo systemctl start mysql
sudo systemctl enable mysql

mysql -u root -p

SHOW DATABASES;
CREATE DATABASE testdb;
USE testdb;

MySQL Workbench is a GUI tool for managing MySQL databases. It includes tools for query, design, and admin.

Use language-specific libraries like:
- PHP: mysqli, PDO
- Python: mysql-connector-python

MySQL is widely used due to ease of use and community support, though others like PostgreSQL offer advanced features.

CREATE DATABASE company;

USE company;

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

DESCRIBE employees;

ALTER TABLE employees ADD COLUMN department VARCHAR(50);

DROP TABLE employees;

DROP DATABASE company;

RENAME TABLE employees TO staff;

SHOW TABLES;

SHOW ENGINES;

INT, DECIMAL, FLOAT, DOUBLE

CHAR, VARCHAR, TEXT

DATE, DATETIME, TIMESTAMP

CREATE TABLE example (name VARCHAR(50) NOT NULL);

CREATE TABLE example (active BOOLEAN DEFAULT TRUE);

CREATE TABLE example (id INT PRIMARY KEY);

CREATE TABLE example (email VARCHAR(100) UNIQUE);

CREATE TABLE example (age INT CHECK (age > 18));

CREATE TABLE orders (id INT, user_id INT, FOREIGN KEY(user_id) REFERENCES users(id));

CREATE TABLE example (id INT AUTO_INCREMENT PRIMARY KEY);

INSERT INTO employees (name, age) VALUES ('Alice', 30);

INSERT INTO employees (name, age) VALUES ('Bob', 25), ('Charlie', 28);

SELECT * FROM employees;

SELECT * FROM employees WHERE age > 25;

SELECT * FROM employees ORDER BY age DESC;

SELECT * FROM employees LIMIT 5;

SELECT * FROM employees WHERE name LIKE 'A%';

SELECT * FROM employees WHERE age BETWEEN 25 AND 35;

SELECT * FROM employees WHERE department IN ('HR', 'IT');

SELECT * FROM employees WHERE department IS NULL;

UPDATE employees SET age = 32 WHERE name = 'Alice';

UPDATE employees SET age = 33, department = 'HR' WHERE id = 1;

UPDATE employees SET department = 'Sales' WHERE department IS NULL;

DELETE FROM employees WHERE name = 'Bob';

DELETE FROM employees WHERE age < 25;

TRUNCATE TABLE employees;

SET SQL_SAFE_UPDATES = 0;

SELECT ROW_COUNT();

-- DELETE can use WHERE clause, TRUNCATE removes all rows faster

START TRANSACTION;
DELETE FROM employees;
ROLLBACK;

SELECT UPPER('hello'), LOWER('WORLD');

SELECT ABS(-5), ROUND(3.14159, 2);

SELECT CURDATE(), NOW();

SELECT CAST('2023-01-01' AS DATE);

SELECT COUNT(*), AVG(age), MAX(age) FROM employees;

SELECT POW(2, 3), SQRT(16);

SELECT IF(age > 30, 'Senior', 'Junior') FROM employees;

SELECT JSON_OBJECT('name', 'Alice', 'age', 30);

SELECT IFNULL(NULL, 'Default');

SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 2;

SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id;

SELECT * FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;

SELECT * FROM orders RIGHT JOIN customers ON orders.customer_id = customers.id;

SELECT * FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
UNION
SELECT * FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;

SELECT A.name, B.name FROM employees A, employees B WHERE A.manager_id = B.id;

SELECT * FROM employees CROSS JOIN departments;

SELECT * FROM employees NATURAL JOIN departments;

SELECT E.name, D.name FROM employees E JOIN departments D ON E.dept_id = D.id;

SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
JOIN products ON orders.product_id = products.id;

SELECT * FROM employees E JOIN departments D ON E.dept_id = D.id WHERE D.name = 'IT';

CREATE INDEX idx_name ON employees(name);

CREATE INDEX idx_name_age ON employees(name, age);

CREATE UNIQUE INDEX idx_email ON employees(email);

DROP INDEX idx_name ON employees;

CREATE VIEW view_employees AS SELECT name, age FROM employees;

SELECT * FROM view_employees;

CREATE OR REPLACE VIEW view_employees AS SELECT name, age, department FROM employees;

DROP VIEW view_employees;

-- Not supported in MySQL directly, use indexed base tables for performance.

-- MySQL does not support materialized views natively.

START TRANSACTION;

COMMIT;

ROLLBACK;

SAVEPOINT sp1;

ROLLBACK TO SAVEPOINT sp1;

RELEASE SAVEPOINT sp1;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

UPDATE employees SET age = age + 1;

LOCK TABLES employees WRITE;

UNLOCK TABLES;

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

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';

REVOKE ALL PRIVILEGES ON *.* FROM 'newuser'@'localhost';

DROP USER 'newuser'@'localhost';

SHOW GRANTS FOR 'newuser'@'localhost';

ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'newpassword';

CREATE ROLE 'developer';

GRANT 'developer' TO 'newuser'@'localhost';

SET DEFAULT ROLE 'developer' TO 'newuser'@'localhost';

DROP ROLE 'developer';

DELIMITER //
CREATE PROCEDURE getEmployees()
BEGIN
  SELECT * FROM employees;
END //
DELIMITER ;

CALL getEmployees();

CREATE PROCEDURE getAge(IN emp_id INT)
BEGIN
  SELECT age FROM employees WHERE id = emp_id;
END;

CREATE PROCEDURE getCount(OUT total INT)
BEGIN
  SELECT COUNT(*) INTO total FROM employees;
END;

CREATE PROCEDURE addFive(INOUT num INT)
BEGIN
  SET num = num + 5;
END;

DROP PROCEDURE getEmployees;

SHOW PROCEDURE STATUS WHERE Db = 'testdb';

IF age > 30 THEN SET level = 'Senior'; ELSE SET level = 'Junior'; END IF;

LOOP statements can be added using REPEAT or WHILE blocks.

DECLARE cursor CURSOR FOR SELECT name FROM employees;

CREATE FUNCTION square(x INT) RETURNS INT RETURN x * x;

SELECT square(5);

CREATE FUNCTION age_group(age INT) RETURNS VARCHAR(10)
RETURN IF(age < 18, 'Minor', 'Adult');

DROP FUNCTION square;

SHOW FUNCTION STATUS WHERE Db = 'testdb';

CREATE FUNCTION total_employees() RETURNS INT
BEGIN
  DECLARE total INT;
  SELECT COUNT(*) INTO total FROM employees;
  RETURN total;
END;

SELECT square(total_employees());

-- MySQL functions do not support default parameter values directly

SELECT name, age_group(age) FROM employees;

Keep functions pure: avoid changes to database state inside them.

CREATE TRIGGER before_insert_emp
BEFORE INSERT ON employees
FOR EACH ROW SET NEW.created_at = NOW();

CREATE TRIGGER after_insert_log
AFTER INSERT ON employees
FOR EACH ROW INSERT INTO log_table(action) VALUES('inserted');

CREATE TRIGGER before_update_emp
BEFORE UPDATE ON employees
FOR EACH ROW SET NEW.updated_at = NOW();

CREATE TRIGGER after_update_log
AFTER UPDATE ON employees
FOR EACH ROW INSERT INTO log_table(action) VALUES('updated');

CREATE TRIGGER before_delete_emp
BEFORE DELETE ON employees
FOR EACH ROW SET @deleted_name = OLD.name;

CREATE TRIGGER after_delete_log
AFTER DELETE ON employees
FOR EACH ROW INSERT INTO log_table(action) VALUES('deleted');

SHOW TRIGGERS;

DROP TRIGGER before_insert_emp;

-- Use conditions within trigger body to prevent unintended execution

-- Use SIGNAL to raise custom errors in triggers

SET GLOBAL event_scheduler = ON;

CREATE EVENT ev_backup
ON SCHEDULE EVERY 1 DAY
DO INSERT INTO backup_log(run_time) VALUES(NOW());

CREATE EVENT ev_once
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO DELETE FROM temp_data;

ALTER EVENT ev_backup DISABLE;

DROP EVENT ev_backup;

SHOW EVENTS;

CREATE DEFINER = 'admin'@'localhost' EVENT ev_def...

GRANT EVENT ON testdb.* TO 'scheduler'@'localhost';

-- Use log table to track event execution manually.

Use clear names and monitor their performance.

mysqldump -u root -p testdb > testdb_backup.sql

mysql -u root -p testdb < testdb_backup.sql

mysqldump -u root -p testdb employees > emp_backup.sql

mysqldump -u root -p --triggers testdb > full_backup.sql

mysqldump -u root -p --routines testdb > procs.sql

mysqldump -u root -p --all-databases > alldb.sql

mysql -u root -p -e "CREATE DATABASE testdb_restore"

-- Included automatically in dump unless --skip-triggers

Use MySQL Workbench > Data Import/Restore

Use cron jobs in Linux or Task Scheduler in Windows to run dump script daily.

EXPLAIN SELECT * FROM employees WHERE age > 30;

CREATE INDEX idx_age ON employees(age);

SHOW VARIABLES LIKE 'slow_query_log';

SET GLOBAL query_cache_size = 1048576;

EXPLAIN SELECT * FROM employees JOIN departments ON employees.dept_id = departments.id;

-- Normalize tables to avoid repeating data

SET PROFILING = 1; SELECT * FROM employees; SHOW PROFILES;

ANALYZE TABLE employees;

-- Use MEMORY engine for faster temp tables

SELECT * FROM employees LIMIT 10;

CREATE USER 'secure_user'@'localhost' IDENTIFIED BY 'S@feP@ss';

ALTER USER 'secure_user'@'localhost' PASSWORD EXPIRE INTERVAL 30 DAY;

ALTER USER 'secure_user'@'localhost' ACCOUNT LOCK;

-- Enable SSL and set REQUIRE SSL for users

GRANT SELECT ON sensitive_db.* TO 'reader'@'192.168.1.%';

SHOW VARIABLES LIKE 'secure_file_priv';

-- Use general_log or third-party plugin

-- Enable InnoDB encryption support

SELECT CONCAT(LEFT(ssn,3), '-**-****') FROM users;

mysqldump -u root -p testdb | openssl enc -aes-256-cbc -out backup.sql.enc

-- Replication copies data from one MySQL server to another.

CHANGE MASTER TO MASTER_HOST='master_host_ip', MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

START SLAVE;

SHOW SLAVE STATUS\G;

STOP SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;

-- Statement-based, Row-based, Mixed replication.

CHANGE MASTER TO MASTER_DELAY=3600;

-- Allows a slave to replicate from multiple masters.

SHOW PROCESSLIST;

-- Partitioning splits a large table into smaller pieces for performance.

-- RANGE, LIST, HASH, KEY partitioning.

CREATE TABLE orders (
  id INT,
  order_date DATE
)
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2019 VALUES LESS THAN (2020),
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

CREATE TABLE employees (
  id INT,
  region VARCHAR(20)
)
PARTITION BY LIST COLUMNS(region) (
  PARTITION p_north VALUES IN ('North'),
  PARTITION p_south VALUES IN ('South')
);

CREATE TABLE users (
  id INT,
  username VARCHAR(50)
)
PARTITION BY HASH(id)
PARTITIONS 4;

ALTER TABLE orders REORGANIZE PARTITION pmax INTO (PARTITION p2021 VALUES LESS THAN (2022), PARTITION pmax VALUES LESS THAN MAXVALUE);

ALTER TABLE orders DROP PARTITION p2019;

SELECT * FROM orders WHERE order_date BETWEEN '2020-01-01' AND '2020-12-31';

-- Improved query performance and easier maintenance.

-- Not all storage engines support partitioning.

-- Full-text search allows searching text-based data efficiently.

CREATE FULLTEXT INDEX idx_content ON articles(content);

SELECT * FROM articles WHERE MATCH(content) AGAINST('database');

SELECT * FROM articles WHERE MATCH(content) AGAINST('+database -mysql' IN BOOLEAN MODE);

SELECT * FROM articles WHERE MATCH(content) AGAINST('database');

SELECT *, MATCH(content) AGAINST('database') AS relevance FROM articles ORDER BY relevance DESC;

-- Words ignored in full-text search (e.g., 'the', 'and').

-- Minimum word length and stopwords can affect search results.

OPTIMIZE TABLE articles;

-- Use Elasticsearch or Sphinx for advanced search features.

-- A virtual table representing the result of a query.

CREATE VIEW employee_names AS SELECT id, name FROM employees;

SELECT * FROM employee_names;

CREATE OR REPLACE VIEW employee_names AS SELECT id, name, age FROM employees;

DROP VIEW employee_names;

-- Views based on single table and without aggregates can be updatable.

GRANT SELECT ON employee_names TO 'readonly_user'@'localhost';

-- Views cannot contain ORDER BY without LIMIT in MySQL.

-- MySQL does not support materialized views natively; simulate with tables and triggers.

-- Views do not store data; performance depends on underlying queries.

DELIMITER $$ 
CREATE PROCEDURE GetEmployeeCount()
BEGIN
  SELECT COUNT(*) FROM employees;
END$$
DELIMITER ;

CALL GetEmployeeCount();

DELIMITER $$
CREATE PROCEDURE GetEmployeesByDept(IN dept_id INT)
BEGIN
  SELECT * FROM employees WHERE dept_id = dept_id;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE GetDeptEmployeeCount(IN dept_id INT, OUT emp_count INT)
BEGIN
  SELECT COUNT(*) INTO emp_count FROM employees WHERE dept_id = dept_id;
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS GetEmployeeCount;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  -- Handle error
END;

DECLARE total INT DEFAULT 0;

CALL GetEmployeesByDept(1);

-- Stored procedures reduce client-server communication overhead.

GRANT EXECUTE ON PROCEDURE GetEmployeeCount TO 'user'@'localhost';

-- A sequence of SQL statements executed as a single unit.

START TRANSACTION;

COMMIT;

ROLLBACK;

SAVEPOINT sp1;

ROLLBACK TO SAVEPOINT sp1;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET autocommit=0;

BEGIN
  START TRANSACTION;
  -- statements
  COMMIT;
END;

Avoid long transactions to reduce locking and contention.

mysqldump -u root -p database_name > backup.sql

-- Copy data directory files while server is stopped

-- Use binary logs for point-in-time recovery

crontab -e
0 2 * * * /usr/bin/mysqldump -u root -p password database_name > backup.sql

mysql -u root -p database_name < backup.sql

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

-- Store backups in encrypted or secure locations

-- Keep backups for defined periods and remove old ones

mysqlbinlog binlog.000001 | mysql -u root -p database_name

-- Regularly test backups and restore procedures

-- A feature for monitoring MySQL server performance.

SHOW VARIABLES LIKE 'performance_schema';

SELECT * FROM performance_schema.events_statements_summary_by_digest;

SELECT * FROM performance_schema.events_waits_summary_global_by_event_name;

SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage;

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE '%wait%';

-- Can introduce overhead; use selectively in production.

-- Many tools like Percona Monitoring integrate with Performance Schema.

SELECT * FROM performance_schema.events_waits_summary_by_instance WHERE EVENT_NAME LIKE '%mutex%';

Enable only needed instruments and consumers to minimize overhead.

mysqldump -u root -p database_name > backup.sql

mysqlpump -u root -p database_name > backup_pump.sql

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

crontab -e
0 3 * * * /usr/bin/mysqldump -u root -p password database_name > backup.sql

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

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

mysql -u root -p database_name < backup.sql

-- Percona XtraBackup supports incremental backups.

mysqlcheck -u root -p --check database_name

-- Consider data size, downtime, and backup type requirements.

CREATE USER 'secure_user'@'localhost' IDENTIFIED BY 'StrongPass123!';

GRANT SELECT, INSERT ON mydb.* TO 'secure_user'@'localhost';

REVOKE DELETE ON mydb.* FROM 'secure_user'@'localhost';

-- Enforce password complexity and expiration.

-- Configure MySQL for encrypted client connections.

-- Enable audit plugins or use general query log.

-- Store backups securely and encrypt if necessary.

-- Use prepared statements and parameterized queries.

ALTER USER 'secure_user'@'localhost' WITH MAX_USER_CONNECTIONS 5;

-- Periodically review user privileges and logs.

-- An index speeds up data retrieval operations.

CREATE INDEX idx_name ON employees(name);

DROP INDEX idx_name ON employees;

CREATE INDEX idx_name_age ON employees(name, age);

CREATE UNIQUE INDEX idx_email ON employees(email);

EXPLAIN SELECT * FROM employees WHERE name = 'John';

SHOW INDEX FROM employees;

-- Indexes that contain all columns needed by a query.

ANALYZE TABLE employees;

-- Avoid indexing columns with low cardinality or frequent updates.

EXPLAIN SELECT * FROM employees WHERE age > 30;

CREATE INDEX idx_age ON employees(age);

SELECT name, age FROM employees WHERE age > 30;

-- MySQL query cache is deprecated in latest versions.

SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;

SELECT * FROM employees ORDER BY age DESC LIMIT 10;

-- Use indexed columns without wrapping them in functions.

SHOW SLOW LOGS;

PREPARE stmt FROM 'SELECT * FROM employees WHERE age > ?';
SET @age = 30;
EXECUTE stmt USING @age;

-- Regularly review and optimize slow and frequent queries.

-- Copying data from one MySQL server to another.

-- Configure master with log-bin and server-id in my.cnf

CHANGE MASTER TO MASTER_HOST='master_host', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  4;
START SLAVE;

SHOW SLAVE STATUS\G;

-- Use replicate-do-db or replicate-ignore-db to filter databases

-- Ensures master waits for at least one slave to acknowledge.

STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;

-- Slave can replicate from multiple masters.

SHOW SLAVE STATUS\G; -- Check Seconds_Behind_Master

-- Use SSL and restrict replication user privileges.

-- Splitting a large table into smaller, more manageable pieces.

-- RANGE, LIST, HASH, KEY partitioning methods.

CREATE TABLE orders (
  order_id INT,
  order_date DATE
)
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p0 VALUES LESS THAN (2018),
  PARTITION p1 VALUES LESS THAN (2020),
  PARTITION p2 VALUES LESS THAN MAXVALUE
);

CREATE TABLE employees (
  emp_id INT,
  dept_id INT
)
PARTITION BY LIST (dept_id) (
  PARTITION p0 VALUES IN (1,2),
  PARTITION p1 VALUES IN (3,4)
);

CREATE TABLE logs (
  log_id INT,
  user_id INT
)
PARTITION BY HASH(user_id)
PARTITIONS 4;

ALTER TABLE orders REORGANIZE PARTITION p2 INTO (
  PARTITION p2 VALUES LESS THAN (2022),
  PARTITION p3 VALUES LESS THAN MAXVALUE
);

ALTER TABLE orders DROP PARTITION p0;

-- Improves query performance and maintenance.

-- Not all storage engines support partitioning; certain queries might not benefit.

SHOW CREATE TABLE orders;

-- Searching text data efficiently using indexes.

CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT(title, body)
);

SELECT * FROM articles WHERE MATCH(title, body) AGAINST('database');

SELECT * FROM articles WHERE MATCH(title, body) AGAINST('+database -mysql' IN BOOLEAN MODE);

SELECT * FROM articles WHERE MATCH(title, body) AGAINST('database' IN NATURAL LANGUAGE MODE);

-- Minimum word length and stopwords affect results.

OPTIMIZE TABLE articles;

-- Supported from MySQL 5.6 and later.

SELECT id, MATCH(title, body) AGAINST('database') AS score FROM articles ORDER BY score DESC;

Use appropriate modes and maintain indexes regularly.

CREATE TABLE products (
  id INT PRIMARY KEY,
  data JSON
);

INSERT INTO products (id, data) VALUES
(1, '{ "name": "Laptop", "price": 799 }');

SELECT data->>'$.name' AS product_name FROM products;

SELECT JSON_EXTRACT(data, '$.price') AS price FROM products;

UPDATE products SET data = JSON_SET(data, '$.price', 899) WHERE id = 1;

UPDATE products SET data = JSON_ARRAY_APPEND(data, '$.tags', 'new') WHERE id = 1;

SELECT * FROM products WHERE JSON_CONTAINS(data, '"Laptop"', '$.name');

CREATE INDEX idx_name ON products ((CAST(data->>'$.name' AS CHAR(100))));

SELECT JSON_VALID(data) FROM products;

Use JSON for flexible schemas but prefer normalized tables for structured data.

CREATE VIEW employee_view AS
SELECT id, name, department FROM employees;

SELECT * FROM employee_view;

CREATE OR REPLACE VIEW employee_view AS
SELECT id, name, department, salary FROM employees;

DROP VIEW employee_view;

-- Views that can be used with INSERT, UPDATE, DELETE.

CREATE VIEW emp_dept AS
SELECT e.name, d.name AS dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;

-- Restrict access to specific columns via views.

-- Views do not store data; performance depends on underlying queries.

-- Use tables and scheduled events to simulate materialized views.

Use views to simplify complex queries and enhance security.

-- A sequence of SQL statements executed as a single unit.

START TRANSACTION;

COMMIT;

ROLLBACK;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET autocommit = 0;

SAVEPOINT sp1;
ROLLBACK TO sp1;

-- InnoDB supports ACID-compliant transactions.

-- Detect and retry transactions on deadlock errors.

Keep transactions short and avoid user interaction within them.

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123';

GRANT SELECT, INSERT ON mydb.* TO 'user1'@'localhost';

REVOKE INSERT ON mydb.* FROM 'user1'@'localhost';

ALTER USER 'user1'@'localhost' IDENTIFIED BY 'newpassword';

DROP USER 'user1'@'localhost';

SHOW GRANTS FOR 'user1'@'localhost';

CREATE USER 'user2'@'192.168.1.%' IDENTIFIED BY 'password';

ALTER USER 'user1'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

CREATE ROLE 'reporting';
GRANT SELECT ON mydb.* TO 'reporting';
GRANT 'reporting' TO 'user1'@'localhost';

Use least privilege principle and strong passwords.

SET GLOBAL slow_query_log = 'ON';
SHOW VARIABLES LIKE 'slow_query_log_file';

EXPLAIN SELECT * FROM employees WHERE age > 30;

CREATE INDEX idx_age ON employees(age);

SET GLOBAL innodb_buffer_pool_size = 1073741824; -- 1GB

-- Deprecated in MySQL 8.0

SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC;

SELECT e.name, d.dept_name FROM employees e JOIN departments d ON e.dept_id = d.id;

SHOW GLOBAL STATUS LIKE 'Threads_connected';

-- Split large tables to improve query efficiency.

Regularly analyze query performance and update indexes.

mysqldump -u root -p mydb > mydb_backup.sql

-- Use Percona XtraBackup for hot physical backups.

mysqlpump -u root -p mydb > mydb_pump.sql

mysqldump mydb | gzip > mydb_backup.sql.gz

-- Use binary logs for incremental backup.

-- Schedule backups with cron jobs or Windows Task Scheduler.

mysql -u root -p mydb < mydb_backup.sql

mysql -u root -p mydb < mydb_backup.sql

Keep backups secure, test restores regularly.

-- Consider cloud providers for offsite backups.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'StrongP@ssw0rd!';

GRANT SELECT ON mydb.* TO 'user'@'localhost';

-- Configure SSL in MySQL server and clients.

-- Use MySQL Enterprise Audit plugin or third-party tools.

-- Keep MySQL server up to date with security patches.

-- Restrict access to MySQL port via firewall.

UPDATE mysql.user SET host='localhost' WHERE user='root' AND host!='localhost'; FLUSH PRIVILEGES;

-- Use InnoDB tablespace encryption.

-- Use prepared statements and input validation.

-- Periodically review logs and user privileges.

SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC;

SHOW PROCESSLIST;

SHOW GLOBAL STATUS;

SELECT * FROM information_schema.tables WHERE table_schema = 'mydb';

SET GLOBAL slow_query_log = 'ON';

-- Check slow query log file at the path set by slow_query_log_file variable.

-- Tool for monitoring MySQL performance in real time.

SET profiling = 1;
SELECT * FROM employees WHERE age > 30;
SHOW PROFILES;

SELECT * FROM sys.schema_unused_indexes;

Regularly monitor and analyze performance metrics for optimization.

mysqldump -u root -p mydb > mydb_backup.sql

-- Use Percona XtraBackup for hot physical backups.

-- Use binary logs for incremental backups.

-- Apply binary logs to restore to a specific point in time.

mysql -u root -p mydb < mydb_backup.sql

-- Use cron jobs or scheduled tasks to automate backup routines.

-- Test restores regularly to ensure backup integrity.

-- Use cloud storage for offsite backups.

mysqldump mydb | gzip > mydb_backup.sql.gz

Maintain multiple backup copies and document recovery procedures.

-- Copying data from one MySQL server (master) to another (slave).

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

START SLAVE;

SHOW SLAVE STATUS\G;

STOP SLAVE;
START SLAVE;

-- Statement-based, row-based, and mixed formats.

-- Skip errors or fix and restart slave.

-- Provides better durability guarantees.

-- Slave replicates from multiple masters.

Monitor replication lag and test failover procedures.

-- Dividing a large table into smaller, manageable pieces called partitions.

CREATE TABLE sales (
  id INT,
  sale_date DATE
)
PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p2019 VALUES LESS THAN (2020),
  PARTITION p2020 VALUES LESS THAN (2021)
);

CREATE TABLE employees (
  id INT,
  dept_id INT
)
PARTITION BY LIST (dept_id) (
  PARTITION p1 VALUES IN (1,2),
  PARTITION p2 VALUES IN (3,4)
);

CREATE TABLE logs (
  id INT,
  user_id INT
)
PARTITION BY HASH(user_id) PARTITIONS 4;

CREATE TABLE sessions (
  id INT,
  session_key VARCHAR(32)
)
PARTITION BY KEY(session_key) PARTITIONS 4;

ALTER TABLE sales DROP PARTITION p2019;

ALTER TABLE sales ADD PARTITION (
  PARTITION p2021 VALUES LESS THAN (2022)
);

SELECT * FROM sales WHERE sale_date >= '2020-01-01';

-- Partition pruning improves query speed.

Choose partition type based on data and query patterns.

DELIMITER //
CREATE PROCEDURE GetEmployeeCount()
BEGIN
  SELECT COUNT(*) FROM employees;
END //
DELIMITER ;

CALL GetEmployeeCount();

CREATE PROCEDURE GetEmployeesByDept(IN dept_id INT)
BEGIN
  SELECT * FROM employees WHERE department_id = dept_id;
END;

CREATE PROCEDURE UpdateSalary(IN emp_id INT, IN new_salary DECIMAL(10,2))
BEGIN
  UPDATE employees SET salary = new_salary WHERE id = emp_id;
END;

DROP PROCEDURE IF EXISTS GetEmployeeCount;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  ROLLBACK;
END;

DECLARE total INT;
SELECT COUNT(*) INTO total FROM employees;

WHILE total < 10 DO
  SET total = total + 1;
END WHILE;

CALL AnotherProcedure();

Keep procedures modular and use transactions for data integrity.

-- Data structure that improves query speed.

CREATE INDEX idx_name ON employees(name);

CREATE UNIQUE INDEX idx_email ON employees(email);

CREATE INDEX idx_name_dept ON employees(name, department_id);

DROP INDEX idx_name ON employees;

-- Primary key automatically creates a unique index.

-- Create indexes to improve JOIN performance.

-- Avoid on small tables or columns with low cardinality.

SHOW INDEX FROM employees;

Create indexes based on query patterns and analyze regularly.

-- A virtual table based on the result of a query.

CREATE VIEW emp_view AS SELECT name, department_id FROM employees;

SELECT * FROM emp_view;

CREATE OR REPLACE VIEW emp_view AS SELECT name, department_id, salary FROM employees;

DROP VIEW emp_view;

-- Views that allow INSERT, UPDATE, DELETE operations if they meet certain criteria.

GRANT SELECT ON emp_view TO 'user1'@'localhost';

CREATE VIEW emp_dept_view AS
SELECT e.name, d.dept_name FROM employees e JOIN departments d ON e.department_id = d.id;

-- Views do not store data, performance depends on underlying queries.

Use views to simplify complex queries and control data access.

-- A sequence of SQL statements executed as a single unit of work.

START TRANSACTION;

COMMIT;

ROLLBACK;

SAVEPOINT savepoint1;

ROLLBACK TO SAVEPOINT savepoint1;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET autocommit = 0;

BEGIN
  START TRANSACTION;
  -- SQL statements
  COMMIT;
END;

Keep transactions short to reduce locking and contention.

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

GRANT SELECT, INSERT ON mydb.* TO 'newuser'@'localhost';

REVOKE INSERT ON mydb.* FROM 'newuser'@'localhost';

DROP USER 'newuser'@'localhost';

SHOW GRANTS FOR 'newuser'@'localhost';

ALTER USER 'newuser'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

ALTER USER 'newuser'@'localhost' ACCOUNT LOCK;

-- Configure validate_password plugin for complexity rules.

CREATE ROLE 'read_only';
GRANT SELECT ON mydb.* TO 'read_only';
GRANT 'read_only' TO 'newuser'@'localhost';

Use least privilege principle and regularly review user accounts.

mysqldump -u root -p mydb > mydb_backup.sql

-- Copy raw data files with server offline for physical backup.

-- Use binary logs to perform incremental backups.

-- Restore backup and replay binary logs up to a point in time.

mysql -u root -p mydb < mydb_backup.sql

-- Schedule backups using cron or Windows Task Scheduler.

-- Test backup files by restoring to a test server.

mysqldump mydb | gzip > mydb_backup.sql.gz

-- Store backups offsite or in cloud storage for disaster recovery.

Maintain backup schedules, keep multiple copies, and document recovery procedures.

EXPLAIN SELECT * FROM employees WHERE age > 30;

CREATE INDEX idx_age ON employees(age);

-- Use appropriate indexes and join types.

SET GLOBAL slow_query_log = ON;

-- Tune parameters like innodb_buffer_pool_size, query_cache_size.

-- Use query cache and external caches like Redis or Memcached.

-- Divide large tables into partitions for faster access.

-- Reduce network overhead by using server-side code.

SHOW GLOBAL STATUS;

Continuously monitor, profile queries, and adjust indexes.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'StrongP@ssw0rd!';

GRANT SELECT ON mydb.* TO 'readonly'@'localhost';

-- Configure MySQL for SSL encrypted connections.

-- Enable audit plugins to log user activity.

-- Use prepared statements in application code.

-- Keep MySQL server updated to patch security vulnerabilities.

UPDATE mysql.user SET Host='localhost' WHERE User='root' AND Host='%'; FLUSH PRIVILEGES;

-- Restrict MySQL port access with firewall rules.

-- Use InnoDB tablespace encryption.

Regularly review security settings and monitor access logs.

-- Stores JSON documents efficiently.

CREATE TABLE products (
  id INT,
  attributes JSON
);

INSERT INTO products (id, attributes) VALUES
(1, '{"color": "red", "size": "M"}');

SELECT attributes->>'$.color' AS color FROM products;

UPDATE products SET attributes = JSON_SET(attributes, '$.size', 'L') WHERE id = 1;

CREATE INDEX idx_color ON products ((CAST(attributes->>'$.color' AS CHAR(20))));

SELECT JSON_VALID(attributes) FROM products;

SELECT JSON_EXTRACT(attributes, '$.size') FROM products;

-- Nested JSON with arrays and objects.

Use JSON for flexible schemas but consider performance impacts.

-- Search text-based data efficiently.

CREATE FULLTEXT INDEX idx_content ON articles(content);

SELECT * FROM articles WHERE MATCH(content) AGAINST('database');

SELECT * FROM articles WHERE MATCH(content) AGAINST('+database -mysql' IN BOOLEAN MODE);

SELECT * FROM articles WHERE MATCH(content) AGAINST('database');

-- Minimum word length, stopwords, and indexing delays.

SELECT * FROM articles WHERE MATCH(content) AGAINST('database' WITH QUERY EXPANSION);

ALTER TABLE articles DROP INDEX idx_content, ADD FULLTEXT INDEX idx_content(content);

-- Use full-text search for efficient text search features.

Use appropriate indexes and be aware of limitations.

-- Copying data from one MySQL server (master) to another (slave).

-- Configure my.cnf with server-id and binary logging.

CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='replica', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  4;

START SLAVE;

SHOW SLAVE STATUS\G;

STOP SLAVE;

-- Use SQL_THREAD or IO_THREAD status to troubleshoot.

-- Statement-based, row-based, mixed replication.

-- Global Transaction Identifiers simplify failover.

Ensure network reliability and monitor lag regularly.

-- Dividing large tables into smaller, manageable pieces.

-- RANGE, LIST, HASH, KEY partitioning methods.

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

SELECT * FROM sales WHERE sale_date BETWEEN '2019-01-01' AND '2020-12-31';

ALTER TABLE sales ADD PARTITION (
  PARTITION p3 VALUES LESS THAN (2023)
);

ALTER TABLE sales DROP PARTITION p0;

-- Improves query performance and maintenance.

-- Not supported on all storage engines and for all queries.

SHOW CREATE TABLE sales;

Choose partition keys wisely based on query patterns.

DELIMITER //
CREATE PROCEDURE GetEmployeeCount()
BEGIN
  SELECT COUNT(*) FROM employees;
END;
//
DELIMITER ;

CALL GetEmployeeCount();

DELIMITER //
CREATE PROCEDURE GetEmployeesByDept(IN dept_id INT)
BEGIN
  SELECT * FROM employees WHERE department_id = dept_id;
END;
//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE GetTotalSalary(OUT total DECIMAL(10,2))
BEGIN
  SELECT SUM(salary) INTO total FROM employees;
END;
//
DELIMITER ;

DROP PROCEDURE GetEmployeeCount;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  -- Error handling code
END;

DECLARE emp_count INT;
SELECT COUNT(*) INTO emp_count FROM employees;

DECLARE i INT DEFAULT 0;
WHILE i < 10 DO
  -- loop body
  SET i = i + 1;
END WHILE;

-- Use SQL SECURITY DEFINER or INVOKER as needed.

Keep procedures simple, document well, and handle exceptions.

CREATE VIEW emp_sales AS
SELECT e.name, s.amount, s.sale_date
FROM employees e
JOIN sales s ON e.id = s.employee_id;

-- Views with joins may not be updatable.

CREATE VIEW active_employees AS
SELECT * FROM employees WHERE status = 'active'
WITH CHECK OPTION;

-- MySQL does not support indexed views (materialized views).

DROP VIEW IF EXISTS emp_sales;

GRANT SELECT ON active_employees TO 'readonly'@'localhost';

-- Views execute underlying queries each time they are called.

-- Use procedures to encapsulate complex logic with views.

CREATE VIEW nested_view AS SELECT * FROM emp_sales;

Keep views simple and test performance impacts.

-- Data structure to speed up data retrieval.

CREATE INDEX idx_name ON employees(name);

CREATE UNIQUE INDEX idx_email ON employees(email);

CREATE INDEX idx_name_age ON employees(name, age);

DROP INDEX idx_name ON employees;

-- Often automatically created for foreign keys.

EXPLAIN SELECT * FROM employees WHERE name = 'John';

-- Avoid on small tables or columns with low cardinality.

OPTIMIZE TABLE employees;

Create indexes based on query patterns and monitor performance.

START TRANSACTION;

COMMIT;

ROLLBACK;

SAVEPOINT savepoint1;

RELEASE SAVEPOINT savepoint1;

LOCK TABLES employees WRITE;

UNLOCK TABLES;

-- InnoDB supports row-level locking automatically.

-- Detect and resolve deadlocks with proper transaction design.

Keep transactions short and handle errors carefully.

mysqldump -u root -p mydatabase > backup.sql

mysqlbinlog mysql-bin.000001 > incremental_backup.sql

0 2 * * * /usr/bin/mysqldump -u root -p mydatabase > /backups/backup.sql

mysqldump -u root -p mydatabase | gzip > backup.sql.gz

mysql -u root -p mydatabase < backup.sql

-- Physical hot backup tool for MySQL.

-- Keep backups for a defined period and clean old files.

-- Test restoring backups on a separate server.

-- Use cloud storage or remote servers for disaster recovery.

Regularly schedule, verify, and document your backup processes.