50 MySQL commands of user management for Devops Enginner

Creating Users and Managing Privileges:

Create a new user:

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

Grant all privileges on a database to a user:

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

Grant specific privileges on a database to a user:

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

Revoke privileges from a user:

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

Remove a user:
```
 DROP USER 'username'@'hostname';
```

Changing Passwords and Security:

Change a user's password:

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

Require users to change their password upon next login:
```
 ALTER USER 'username'@'hostname' PASSWORD EXPIRE;
```

Reset a user's password and require them to change it:

 ALTER USER 'username'@'hostname' PASSWORD EXPIRE; 
 ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_password';

Allow or disallow login for a user:

 ALTER USER 'username'@'hostname' ACCOUNT LOCK;
 ALTER USER 'username'@'hostname' ACCOUNT UNLOCK;

Listing and Viewing User Information:

List all users:
```
 SELECT user, host FROM mysql.user;
```

Show privileges for a user:

 SHOW GRANTS FOR 'username'@'hostname';

Display user roles (MySQL 8.0+):

 SELECT user, host, role_name FROM mysql.user;

User Authentication and Hostname Matching:

Create a user that can connect from any host:

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

Create a user with wildcard hostname matching:

 CREATE USER 'username'@'192.168.%' IDENTIFIED BY 'password';

Create a user with a domain-based hostname:

 CREATE USER 'username'@'%.example.com' IDENTIFIED BY 'password';

Renaming Users and Hostnames:

Rename a user (MySQL 5.7+):

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

Change a user's hostname (MySQL 5.7+):

 UPDATE mysql.user SET host = 'new_hostname' WHERE user = 'username' AND host = 'old_hostname';
 FLUSH PRIVILEGES;

User Account Expiry:

Set an account expiration date for a user:

 ALTER USER 'username'@'hostname' ACCOUNT EXPIRE DATE 'YYYY-MM-DD';

Remove account expiration for a user:

 ALTER USER 'username'@'hostname' ACCOUNT EXPIRE NEVER;

Password Expiry and Complexity:

Set a user's password expiration policy (MySQL 5.7+):

 ALTER USER 'username'@'hostname' PASSWORD EXPIRE INTERVAL N DAY;

Set a global password policy (MySQL 5.7+):

 SET GLOBAL validate_password.policy = MEDIUM;

These commands should help you manage users and privileges in MySQL effectively. Make sure to adjust the 'username', 'hostname', 'password', and 'database_name' placeholders in the commands to match your specific use case.

Here are some examples of MySQL user accounts using hostnames:

Creating a User with Hostname 'localhost': This user can only connect to the MySQL server from the same machine where MySQL is running (localhost).
```
 CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
```
Creating a User with a Specific IP Address: This user can connect only from a specific IP address (e.g., 192.168.1.100).
```
 CREATE USER 'myuser'@'192.168.1.100' IDENTIFIED BY 'mypassword';
```
Creating a User with a Partial IP Address Match: This user can connect from any IP address that starts with '192.168.1.'.
```
 CREATE USER 'myuser'@'192.168.1.%' IDENTIFIED BY 'mypassword';
```
Creating a User with a Domain-Based Hostname: This user can connect from any hostname ending with '.example.com'.
```
 CREATE USER 'myuser'@'%.example.com' IDENTIFIED BY 'mypassword';
```
Creating a User with Any Hostname ('%'): This user can connect from any hostname, which is less restrictive but should be used with caution.
```
 CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypassword';
```
Revoking Privileges for a Specific Host: You can also revoke privileges from a user for a specific hostname or IP address.
```
 REVOKE ALL PRIVILEGES ON database_name.* FROM 'myuser'@'192.168.1.100';
```
Viewing Hostname-Related Privileges: To view the privileges granted to a user for a specific hostname, you can use the SHOW GRANTS statement.
```
 SHOW GRANTS FOR 'myuser'@'192.168.1.%';
```
Renaming a User and Changing Hostname: You can rename a user and change their hostname using the RENAME USER statement.
```
 RENAME USER 'myuser'@'localhost' TO 'newuser'@'192.168.1.%';
```

These examples illustrate how hostnames are used in MySQL to control user access to the database server based on the source of the connection. By specifying hostnames in user accounts, you can define fine-grained access control rules.

Commands and examples using more real-world-sounding database and table names. Here are the MySQL commands with more realistic names:

Basic Syntax and Commands (10 Commands):

Connecting to MySQL:
- To connect to MySQL, use the following command in your terminal:
```
  mysql -u yourusername -p yourdbname
```
  Replace yourusername with your MySQL username and yourdbname with the name of your database. You'll be prompted to enter your MySQL password.
Checking Version:
- You can check the MySQL server version using the SQL command:
```
  SELECT VERSION();
```
Listing Databases:
- To list all available databases, use the SQL command:
```
  SHOW DATABASES;
```
Creating a Database:
- To create a new database for a bookstore, use the SQL command:
```
  CREATE DATABASE bookstore_db;
```
Dropping a Database:
- To delete (drop) the old customer database, use the SQL command:
```
  DROP DATABASE customer_db;
```

Creating a Table:

To create a new table for books in the bookstore database, use the SQL command with column definitions

  CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(255),
    author VARCHAR(255),
    publication_year INT
  );

Selecting Data:
- To retrieve all books from the books table, use the SQL command
```
  SELECT * FROM books;
```
Filtering Data:
- To retrieve books published after 2020, use the SQL command with a WHERE clause:
```
  SELECT * FROM books WHERE publication_year > 2020;
```

Inserting Data:

To add a new book to the books table, use the SQL command

  INSERT INTO books (book_id, title, author, publication_year) VALUES (1, 'The Great Novel', 'John Author', 2022);

Updating Data:
- To change the author of a book, use the SQL command with an UPDATE statement:
```
  UPDATE books SET author = 'Jane Author' WHERE book_id = 1;
```
Deleting Data:
- To remove a book from the books table, use the SQL command with a DELETE statement:
```
  DELETE FROM books WHERE book_id = 1;
```

Intermediate Concepts and Commands (20 Commands):

Data Types and Casting:

MySQL supports various data types like INT, VARCHAR, DATE, etc., to define columns in tables. Example:

  CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date DATE,
    total_amount DECIMAL(10, 2)
  );

Joins:

Joins combine data from two or more tables based on a related column. Common types are INNER JOIN, LEFT JOIN, and RIGHT JOIN. Example:

  SELECT orders.order_id, customers.customer_name
  FROM orders
  INNER JOIN customers ON orders.customer_id = customers.customer_id;

Functions:
- MySQL provides various functions like COUNT(), SUM(), AVG(), MAX(), MIN() to perform calculations on data. Example:
```
  SELECT AVG(total_amount) FROM orders;
```
Grouping and Aggregation:
- You can group rows and apply aggregate functions using GROUP BY and HAVING clauses. Example:
```
  SELECT category, AVG(price) AS avg_price
  FROM products
  GROUP BY category
  HAVING AVG(price) > 50;
```
Subqueries:
- Subqueries are nested queries within another query. They can be used in SELECT, INSERT, UPDATE, or DELETE statements. Example:
```
  SELECT product_name FROM products WHERE product_id IN (SELECT product_id FROM cart WHERE user_id = 1);
```
Views:
- Views are virtual tables that can simplify complex queries. They are created using the CREATE VIEW statement. Example:
```
  CREATE VIEW high_value_orders AS SELECT * FROM orders WHERE total_amount > 1000;
```
User Management:
- User management commands include CREATE USER, ALTER USER, DROP USER, and more. Example:
```
  CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'securepassword';
```
Privileges:
- MySQL allows you to grant specific privileges to users on databases, tables, or even specific columns. Example:
```
  GRANT SELECT, INSERT ON bookstore_db.* TO 'webapp_user'@'localhost';
```
Database Optimization:
- The EXPLAIN statement helps analyze query execution plans, and OPTIMIZE TABLE helps optimize table storage. Example:
```
  EXPLAIN SELECT * FROM orders WHERE order_date > '2023-01-01';
```
Importing and Exporting Data:
- Use mysqldump to export data and mysqlimport to import data. Example:
- Export: mysqldump -u yourusername -p bookstore_db > bookstore_backup.sql
- Import: mysqlimport -u yourusername -p bookstore_db < new_data.csv

Advanced Topics and Commands (20 Commands):

Transactions:
- Transactions allow you to group multiple SQL statements into a single unit of work, ensuring data consistency. Example:
```
  BEGIN;
  -- SQL statements
  COMMIT;
```

Stored Procedures and Functions:

Stored procedures and functions are reusable blocks of SQL code. Example:

  CREATE PROCEDURE sp_get_order_total(IN order_id INT)
  BEGIN
    SELECT total_amount FROM orders WHERE order_id = order_id;
  END;

Triggers:

Triggers are actions that automatically execute when specific events occur (e.g., BEFORE INSERT, AFTER UPDATE). Example:

  CREATE TRIGGER log_order_changes
  AFTER UPDATE ON orders
  FOR EACH ROW
  BEGIN
    INSERT INTO order_history (order_id, change_date) VALUES (NEW.order_id, NOW());
  END;

Replication:
- MySQL replication allows you to create a copy of a database on another server for redundancy or scaling. Configuration involves CHANGE MASTER and START SLAVE commands.
Performance Monitoring:
- Commands like SHOW PROCESSLIST and SHOW STATUS help monitor database performance and active connections.
Security:
- Managing security includes using GRANT OPTION,

enabling encryption (e.g., SSL/TLS), and securing user passwords.

Backup and Recovery:
- Backup commands include mysqldump, and recovery involves restoring from backups.
Schema Management:
- Schema changes are made using ALTER TABLE statements and can include adding or modifying columns.

Partitioning:

Table partitioning can improve performance and manageability of large tables. Example:

  CREATE TABLE logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    log_date DATE,
    log_message TEXT
  ) PARTITION BY RANGE (YEAR(log_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN MAXVALUE
  );

JSON Data Type:
- MySQL supports JSON data types and functions to work with JSON data. Example: ```sql CREATE TABLE product_reviews ( review_id INT AUTO_INCREMENT PRIMARY KEY, review_data JSON );

SELECT review_data->'$.rating' FROM product_reviews WHERE product_id = 123; ```

These commands use more realistic database and table names to provide a clearer understanding of how MySQL can be used in real-world scenarios. Feel free to adapt these examples to your specific needs and projects.

MySQL commands that a DevOps engineer may encounter in their daily tasks, covering topics like database maintenance, troubleshooting, and optimization:

Database Maintenance and Administration (20 Commands):

Backup Database:
- Create a backup of a database using mysqldump:
```
  mysqldump -u username -p database_name > backup.sql
```
Restore Database:
- Restore a database from a backup file:
```
  mysql -u username -p database_name < backup.sql
```
Show Table Structure:
- Display the structure of a table, including columns, types, and constraints:
```
  DESCRIBE table_name;
```
Renaming a Table:
- Rename a table within a database:
```
  RENAME TABLE old_table TO new_table;
```
Dropping a Table:
- Delete (drop) a table from a database
```
  DROP TABLE table_name;
```
Table Maintenance:
- Optimize and repair a table to improve performance:
```
  OPTIMIZE TABLE table_name;
  REPAIR TABLE table_name;
```
Index Maintenance:
- Check and rebuild table indexes for optimization:
```
  CHECK TABLE table_name;
  REPAIR TABLE table_name USE_FRM;
```
Database Export with Compression:
- Export a database and compress it using gzip:
```
  mysqldump -u username -p database_name | gzip > backup.sql.gz
```
Database Import from Compressed File:
- Import a compressed database backup using gzip:
```
  gunzip < backup.sql.gz | mysql -u username -p database_name
```
Show Storage Engines:
- Display the available storage engines supported by MySQL:
```
  SHOW ENGINES;
```
Database Character Set and Collation:
- Check and set the character set and collation for a database:
```
  ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```

Set SQL Mode:

Change the SQL mode for a session or globally:

  SET SESSION sql_mode = 'modes';
  SET GLOBAL sql_mode = 'modes';

Server Variables:

View and modify server variables:

  SHOW VARIABLES LIKE 'variable_name';
  SET GLOBAL variable_name = 'new_value';

Server Status:
- View the current server status, including uptime and connections:
```
  SHOW STATUS;
```

Database Size:

Calculate the size of a database in MB or GB:

  SELECT table_schema AS "Database Name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.tables GROUP BY table_schema;

Flush Privileges:
- Reload the privileges from the grant tables:
```
  FLUSH PRIVILEGES;
```
Kill a Query:
- Terminate a running query or connection by its process ID:
```
  KILL QUERY process_id;
  KILL CONNECTION process_id;
```
Database Dump with Single Transaction:
- Create a database dump with a single transaction for consistency:
```
  mysqldump --single-transaction -u username -p database_name > backup.sql
```
Show Long Running Queries:
- Identify and troubleshoot long-running queries:
```
  SHOW FULL PROCESSLIST;
```
Enable Binary Logging:
- Enable binary logging for replication and point-in-time recovery:
```
  SET GLOBAL log_bin = ON;
```

Troubleshooting and Optimization (20 Commands):

Examine Slow Queries:

Analyze slow query logs to identify performance bottlenecks:

  SHOW VARIABLES LIKE 'slow_query_log';
  SHOW VARIABLES LIKE 'long_query_time';

Query Profiling:

Enable query profiling to analyze query execution details:

  SET profiling = 1;
  SHOW PROFILES;
  SHOW PROFILE FOR QUERY query_id;

Change Buffer Pool Size:
- Adjust the InnoDB buffer pool size for memory management:
```
  SET GLOBAL innodb_buffer_pool_size = size_in_bytes;
```
InnoDB Status:
- Monitor InnoDB engine status and metrics:
```
  SHOW ENGINE INNODB STATUS;
```

Query Cache:

View and manage the query cache:

  SHOW VARIABLES LIKE 'query_cache%';
  FLUSH QUERY CACHE;

Change Thread Pool Size:
- Modify the thread pool size to control concurrent connections:
```
  SET GLOBAL thread_pool_size = size;
```

User Resource Limits:

Set resource limits for users to prevent overuse:

  CREATE USER 'username'@'hostname' WITH MAX_QUERIES_PER_HOUR 100;

View Process List with Threads:
- Display the process list with thread information:
```
  SHOW FULL PROCESSLIST;
```
Show Open Tables:
- List currently open tables and their details:
```
  SHOW OPEN TABLES WHERE In_use > 0;
```
InnoDB Deadlock Detection:
- Identify and analyze InnoDB deadlock occurrences:
```
  SHOW ENGINE INNODB STATUS;
```
Change Query Cache Size:
- Adjust the query cache size to improve performance:
```
  SET GLOBAL query_cache_size = size_in_bytes;
```

Temporary Tables:

Create and manage temporary tables for complex queries:

  CREATE TEMPORARY TABLE temp_table (column1 INT, column2 VARCHAR(50));

InnoDB Buffer Pool Flushing:
- Manually flush the InnoDB buffer pool:
```
  SET GLOBAL innodb_buffer_pool_dump_now = ON;
```
Storage Engine Conversion:
- Convert a table to a different storage engine:
```
  ALTER TABLE table_name ENGINE = InnoDB;
```
Change Binary Log Format:
- Modify the binary log format (e.g., ROW, STATEMENT, MIXED):
```
  SET GLOBAL binlog_format = 'ROW';
```
Connection Thread Priority:
- Set thread priority for connection handling:
```
  SET GLOBAL thread_handling = 'pool-of-threads';
```
InnoDB Page Compression:
- Enable and configure InnoDB page compression: ```sql SET GLOBAL innodb_compression_algorithm =

'zlib'; ```

Change Max Connections:
- Adjust the maximum allowed connections to the MySQL server:
```
  SET GLOBAL max_connections = number_of_connections;
```
InnoDB Doublewrite Buffer:
- Enable or disable the InnoDB doublewrite buffer:
```
  SET GLOBAL innodb_doublewrite = OFF;
```
Query Rewrite Plugin:
- Enable and configure the query rewrite plugin for optimization:
```
  INSTALL PLUGIN query_rewrite SONAME 'query_rewrite.so';
```

These commands cover database maintenance, troubleshooting, and optimization tasks that a DevOps engineer may encounter while managing MySQL databases in a production environment. Please adapt them as needed for your specific use cases and configurations.

Certainly! Here are more MySQL commands, bringing the total to 100, covering additional database management, optimization, security, and troubleshooting tasks:

Database Management and Administration (20 Commands):

Change Default Character Set and Collation:

Set the default character set and collation for new tables:

  SET GLOBAL character_set_server = 'utf8mb4';
  SET GLOBAL collation_server = 'utf8mb4_unicode_ci';

Flush Logs:

Flush binary logs and error logs:

  FLUSH BINARY LOGS;
  FLUSH ERROR LOGS;

Database Export with Compression (gzipped):
- Export a database and compress it using gzip directly:
```
  mysqldump -u username -p database_name | gzip > backup.sql.gz
```
Database Import from Compressed File (gzipped):
- Import a compressed database backup using gzip:
```
  gunzip < backup.sql.gz | mysql -u username -p database_name
```
Show Unique Values in a Column:
- List unique values in a specific column
```
  SELECT DISTINCT column_name FROM table_name;
```
Find All Databases Containing a Specific Table:
- Query information_schema to find databases with a particular table:
```
  SELECT table_schema FROM information_schema.tables WHERE table_name = 'your_table_name';
```
Check and Repair All Tables in a Database:
- Check and repair all tables in a specific database:
```
  REPAIR DATABASE database_name;
```
Flush Host Cache:
- Clear the internal host cache to reset DNS lookups:
```
  FLUSH HOSTS;
```

Database Size for All Databases:

List the size of all databases on the MySQL server:

  SELECT table_schema "Database Name", SUM(data_length + index_length) / 1024 / 1024 "Size (MB)" FROM information_schema.tables GROUP BY table_schema;

Binary Log Rotation:
- Rotate binary logs to manage log file sizes:
```
  PURGE BINARY LOGS TO 'log_file_name';
```

Reset User Password:

Change a user's password:

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

Drop All Tables in a Database:
- Delete all tables within a database (use with caution):
```
  DROP DATABASE IF EXISTS database_name;
  CREATE DATABASE database_name;
```

Find and Replace Data:

Update data by finding and replacing specific values:

  UPDATE table_name SET column_name = REPLACE(column_name, 'find_this', 'replace_with_this') WHERE column_name LIKE '%find_this%';

Enable General Query Log:
- Enable the general query log for debugging and analysis:
```
  SET GLOBAL general_log = 1;
```
Database Charset and Collation Conversion:
- Convert the character set and collation of a database:
```
  ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```
Empty a Table:
- Delete all records in a table, keeping the structure intact:
```
  DELETE FROM table_name;
```
InnoDB Deadlock Monitor:
- Enable the InnoDB deadlock monitor to collect information:
```
  SET GLOBAL innodb_print_all_deadlocks = ON;
```

Drop All Views in a Database:

Delete all views within a database:

  SELECT CONCAT('DROP VIEW ', table_name, ';') FROM information_schema.views WHERE table_schema = 'database_name';

Database Binary Logging Control:
- Enable or disable binary logging for the entire server:
```
  SET GLOBAL log_bin = OFF;
```
Backup Database with Timestamp:
- Create a backup of a database with a timestamp in the filename:
```
  mysqldump -u username -p database_name > backup_$(date +\%Y\%m\%d_\%H\%M\%S).sql
```

Optimization and Troubleshooting (20 Commands):

InnoDB Buffer Pool Warm-up:

Warm up the InnoDB buffer pool by reading all pages:

  SET GLOBAL innodb_buffer_pool_load_at_startup = ON;
  SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON;

Show Binlog Events:
- Display binary log events for replication analysis:
```
  SHOW BINLOG EVENTS;
```
Enable Slow Query Log:
- Enable the slow query log to capture slow queries:
```
  SET GLOBAL slow_query_log = ON;
```
InnoDB Page Compression Settings:
- View and modify InnoDB page compression settings:
```
  SHOW VARIABLES LIKE 'innodb_compression%';
```
Change MySQL Port:
- Change the MySQL server port number:
```
  SET GLOBAL port = new_port_number;
```
Show Query Cache Size:
- Check the size of the query cache: ```sql

SHOW VARIABLES LIKE 'query_cache_size';

COPY


67. **MySQL Configuration File Location:**
    - Find the location of the MySQL configuration file:
    ```sql
    SHOW VARIABLES LIKE 'config_file';

InnoDB Log File Size:

Adjust the size of InnoDB log files:

  SET GLOBAL innodb_log_file_size = new_size_in_bytes;

Show Slave Status:
- Display the status of a MySQL replication slave:
```
  SHOW SLAVE STATUS;
```
InnoDB Flush Method:
- Set the InnoDB flush method (O_DIRECT, O_DSYNC, etc.):
```
  SET GLOBAL innodb_flush_method = 'O_DIRECT';
```
Query Cache Clear:
- Clear the query cache to remove cached query results:
```
  RESET QUERY CACHE;
```
Change Max Allowed Packet Size:
- Modify the maximum allowed packet size:
```
  SET GLOBAL max_allowed_packet = size_in_bytes;
```
Query Execution Plan Analysis:
- Analyze the execution plan of a query for optimization:
```
  EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';
```
InnoDB Buffer Pool Size Adjustment:
- Adjust the InnoDB buffer pool size dynamically:
```
  SET GLOBAL innodb_buffer_pool_size = new_size_in_bytes;
```
Reload MySQL Privileges:
- Reload the privileges from the grant tables:
```
  FLUSH PRIVILEGES;
```
Check InnoDB Status:
- Check the status of the InnoDB storage engine:
```
  SHOW ENGINE INNODB STATUS;
```
Transaction Isolation Level:
- Set the transaction isolation level for a session:
```
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
```
Table Fragmentation Check:
- Check for table fragmentation and optimize if needed:
```
  ANALYZE TABLE table_name;
```
InnoDB Log File Checkpointing:
- Manually trigger an InnoDB log file checkpoint:
```
  SET GLOBAL innodb_flush_log_at_trx_commit = 2;
```
Database Index Usage Analysis:
- Identify which indexes are used in a query:
```
  EXPLAIN SELECT * FROM table_name WHERE indexed_column = 'value';
```

50 MySQL commands of user management for Devops Enginner

Table of contents

Here are some examples of MySQL user accounts using hostnames:

Commands and examples using more real-world-sounding database and table names. Here are the MySQL commands with more realistic names:

MySQL commands that a DevOps engineer may encounter in their daily tasks, covering topics like database maintenance, troubleshooting, and optimization:

Certainly! Here are more MySQL commands, bringing the total to 100, covering additional database management, optimization, security, and troubleshooting tasks: