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:
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:
Replacemysql -u yourusername -p yourdbname
yourusername
with your MySQL username andyourdbname
with the name of your database. You'll be prompted to enter your MySQL password.
- To connect to MySQL, use the following command in your terminal:
Checking Version:
- You can check the MySQL server version using the SQL command:
SELECT VERSION();
- You can check the MySQL server version using the SQL command:
Listing Databases:
- To list all available databases, use the SQL command:
SHOW DATABASES;
- To list all available databases, use the SQL command:
Creating a Database:
- To create a new database for a bookstore, use the SQL command:
CREATE DATABASE bookstore_db;
- To create a new database for a bookstore, use the SQL command:
Dropping a Database:
- To delete (drop) the old customer database, use the SQL command:
DROP DATABASE customer_db;
- To delete (drop) the old customer database, use the SQL command:
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 );
- To create a new table for books in the bookstore database, use the SQL command with column definitions:
Selecting Data:
- To retrieve all books from the books table, use the SQL command:
SELECT * FROM books;
- To retrieve all books from the books table, use the SQL command:
Filtering Data:
- To retrieve books published after 2020, use the SQL command with a
WHERE
clause:SELECT * FROM books WHERE publication_year > 2020;
- To retrieve books published after 2020, use the SQL command with a
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);
- To add a new book to the books table, use the SQL command:
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;
- To change the author of a book, use the SQL command with an
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;
- To remove a book from the books table, use the SQL command with a
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) );
- MySQL supports various data types like
Joins:
- Joins combine data from two or more tables based on a related column. Common types are
INNER JOIN
,LEFT JOIN
, andRIGHT JOIN
. Example:SELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;
- Joins combine data from two or more tables based on a related column. Common types are
Functions:
- MySQL provides various functions like
COUNT()
,SUM()
,AVG()
,MAX()
,MIN()
to perform calculations on data. Example:SELECT AVG(total_amount) FROM orders;
- MySQL provides various functions like
Grouping and Aggregation:
- You can group rows and apply aggregate functions using
GROUP BY
andHAVING
clauses. Example:SELECT category, AVG(price) AS avg_price FROM products GROUP BY category HAVING AVG(price) > 50;
- You can group rows and apply aggregate functions using
Subqueries:
- Subqueries are nested queries within another query. They can be used in
SELECT
,INSERT
,UPDATE
, orDELETE
statements. Example:SELECT product_name FROM products WHERE product_id IN (SELECT product_id FROM cart WHERE user_id = 1);
- Subqueries are nested queries within another query. They can be used in
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;
- Views are virtual tables that can simplify complex queries. They are created using the
User Management:
- User management commands include
CREATE USER
,ALTER USER
,DROP USER
, and more. Example:CREATE USER 'webapp_user'@'localhost' IDENTIFIED BY 'securepassword';
- User management commands include
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';
- MySQL allows you to grant specific privileges to users on databases, tables, or even specific columns. Example:
Database Optimization:
- The
EXPLAIN
statement helps analyze query execution plans, andOPTIMIZE TABLE
helps optimize table storage. Example:EXPLAIN SELECT * FROM orders WHERE order_date > '2023-01-01';
- The
Importing and Exporting Data:
- Use
mysqldump
to export data andmysqlimport
to import data. Example: - Export:
mysqldump -u yourusername -p bookstore_db > bookstore_backup.sql
- Import:
mysqlimport -u yourusername -p bookstore_db < new_data.csv
- Use
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;
- Transactions allow you to group multiple SQL statements into a single unit of work, ensuring data consistency. Example:
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;
- Stored procedures and functions are reusable blocks of SQL code. Example:
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;
- Triggers are actions that automatically execute when specific events occur (e.g.,
Replication:
- MySQL replication allows you to create a copy of a database on another server for redundancy or scaling. Configuration involves
CHANGE MASTER
andSTART SLAVE
commands.
- MySQL replication allows you to create a copy of a database on another server for redundancy or scaling. Configuration involves
Performance Monitoring:
- Commands like
SHOW PROCESSLIST
andSHOW STATUS
help monitor database performance and active connections.
- Commands like
Security:
- Managing security includes using
GRANT OPTION
,
enabling encryption (e.g., SSL/TLS), and securing user passwords.
- Managing security includes using
Backup and Recovery:
- Backup commands include
mysqldump
, and recovery involves restoring from backups.
- Backup commands include
Schema Management:
- Schema changes are made using
ALTER TABLE
statements and can include adding or modifying columns.
- Schema changes are made using
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 );
- Table partitioning can improve performance and manageability of large tables. Example:
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
- Create a backup of a database using
Restore Database:
- Restore a database from a backup file:
mysql -u username -p database_name < backup.sql
- Restore a database from a backup file:
Show Table Structure:
- Display the structure of a table, including columns, types, and constraints:
DESCRIBE table_name;
- Display the structure of a table, including columns, types, and constraints:
Renaming a Table:
- Rename a table within a database:
RENAME TABLE old_table TO new_table;
- Rename a table within a database:
Dropping a Table:
- Delete (drop) a table from a database:
DROP TABLE table_name;
- Delete (drop) a table from a database:
Table Maintenance:
- Optimize and repair a table to improve performance:
OPTIMIZE TABLE table_name; REPAIR TABLE table_name;
- Optimize and repair a table to improve performance:
Index Maintenance:
- Check and rebuild table indexes for optimization:
CHECK TABLE table_name; REPAIR TABLE table_name USE_FRM;
- Check and rebuild table indexes for optimization:
Database Export with Compression:
- Export a database and compress it using
gzip
:mysqldump -u username -p database_name | gzip > backup.sql.gz
- Export a database and compress it using
Database Import from Compressed File:
- Import a compressed database backup using
gzip
:gunzip < backup.sql.gz | mysql -u username -p database_name
- Import a compressed database backup using
Show Storage Engines:
- Display the available storage engines supported by MySQL:
SHOW ENGINES;
- Display the available storage engines supported by MySQL:
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;
- Check and set the character set and collation for a database:
Set SQL Mode:
- Change the SQL mode for a session or globally:
SET SESSION sql_mode = 'modes'; SET GLOBAL sql_mode = 'modes';
- Change the SQL mode for a session or globally:
Server Variables:
- View and modify server variables:
SHOW VARIABLES LIKE 'variable_name'; SET GLOBAL variable_name = 'new_value';
- View and modify server variables:
Server Status:
- View the current server status, including uptime and connections:
SHOW STATUS;
- View the current server status, including uptime and connections:
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;
- Calculate the size of a database in MB or GB:
Flush Privileges:
- Reload the privileges from the grant tables:
FLUSH PRIVILEGES;
- Reload the privileges from the grant tables:
Kill a Query:
- Terminate a running query or connection by its process ID:
KILL QUERY process_id; KILL CONNECTION process_id;
- Terminate a running query or connection by its 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
- Create a database dump with a single transaction for consistency:
Show Long Running Queries:
- Identify and troubleshoot long-running queries:
SHOW FULL PROCESSLIST;
- Identify and troubleshoot long-running queries:
Enable Binary Logging:
- Enable binary logging for replication and point-in-time recovery:
SET GLOBAL log_bin = ON;
- Enable binary logging for replication and point-in-time recovery:
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';
- Analyze slow query logs to identify performance bottlenecks:
Query Profiling:
- Enable query profiling to analyze query execution details:
SET profiling = 1; SHOW PROFILES; SHOW PROFILE FOR QUERY query_id;
- Enable query profiling to analyze query execution details:
Change Buffer Pool Size:
- Adjust the InnoDB buffer pool size for memory management:
SET GLOBAL innodb_buffer_pool_size = size_in_bytes;
- Adjust the InnoDB buffer pool size for memory management:
InnoDB Status:
- Monitor InnoDB engine status and metrics:
SHOW ENGINE INNODB STATUS;
- Monitor InnoDB engine status and metrics:
Query Cache:
- View and manage the query cache:
SHOW VARIABLES LIKE 'query_cache%'; FLUSH QUERY CACHE;
- View and manage the query cache:
Change Thread Pool Size:
- Modify the thread pool size to control concurrent connections:
SET GLOBAL thread_pool_size = size;
- Modify the thread pool size to control concurrent connections:
User Resource Limits:
- Set resource limits for users to prevent overuse:
CREATE USER 'username'@'hostname' WITH MAX_QUERIES_PER_HOUR 100;
- Set resource limits for users to prevent overuse:
View Process List with Threads:
- Display the process list with thread information:
SHOW FULL PROCESSLIST;
- Display the process list with thread information:
Show Open Tables:
- List currently open tables and their details:
SHOW OPEN TABLES WHERE In_use > 0;
- List currently open tables and their details:
InnoDB Deadlock Detection:
- Identify and analyze InnoDB deadlock occurrences:
SHOW ENGINE INNODB STATUS;
- Identify and analyze InnoDB deadlock occurrences:
Change Query Cache Size:
- Adjust the query cache size to improve performance:
SET GLOBAL query_cache_size = size_in_bytes;
- Adjust the query cache size to improve performance:
Temporary Tables:
- Create and manage temporary tables for complex queries:
CREATE TEMPORARY TABLE temp_table (column1 INT, column2 VARCHAR(50));
- Create and manage temporary tables for complex queries:
InnoDB Buffer Pool Flushing:
- Manually flush the InnoDB buffer pool:
SET GLOBAL innodb_buffer_pool_dump_now = ON;
- Manually flush the InnoDB buffer pool:
Storage Engine Conversion:
- Convert a table to a different storage engine:
ALTER TABLE table_name ENGINE = InnoDB;
- Convert a table to a different storage engine:
Change Binary Log Format:
- Modify the binary log format (e.g., ROW, STATEMENT, MIXED):
SET GLOBAL binlog_format = 'ROW';
- Modify the binary log format (e.g., ROW, STATEMENT, MIXED):
Connection Thread Priority:
- Set thread priority for connection handling:
SET GLOBAL thread_handling = 'pool-of-threads';
- Set thread priority for connection handling:
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;
- Adjust the maximum allowed connections to the MySQL server:
InnoDB Doublewrite Buffer:
- Enable or disable the InnoDB doublewrite buffer:
SET GLOBAL innodb_doublewrite = OFF;
- Enable or disable the InnoDB doublewrite buffer:
Query Rewrite Plugin:
- Enable and configure the query rewrite plugin for optimization:
INSTALL PLUGIN query_rewrite SONAME 'query_rewrite.so';
- Enable and configure the query rewrite plugin for optimization:
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';
- Set the default character set and collation for new tables:
Flush Logs:
- Flush binary logs and error logs:
FLUSH BINARY LOGS; FLUSH ERROR LOGS;
- Flush binary logs and 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
- Export a database and compress it using
Database Import from Compressed File (gzipped):
- Import a compressed database backup using
gzip
:gunzip < backup.sql.gz | mysql -u username -p database_name
- Import a compressed database backup using
Show Unique Values in a Column:
- List unique values in a specific column:
SELECT DISTINCT column_name FROM table_name;
- List unique values in a specific column:
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';
- Query information_schema to find databases with a particular table:
Check and Repair All Tables in a Database:
- Check and repair all tables in a specific database:
REPAIR DATABASE database_name;
- Check and repair all tables in a specific database:
Flush Host Cache:
- Clear the internal host cache to reset DNS lookups:
FLUSH HOSTS;
- Clear the internal host cache to reset DNS lookups:
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;
- List the size of all databases on the MySQL server:
Binary Log Rotation:
- Rotate binary logs to manage log file sizes:
PURGE BINARY LOGS TO 'log_file_name';
- Rotate binary logs to manage log file sizes:
Reset User Password:
- Change a user's password:
ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_password';
- Change a user's 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;
- Delete all tables within a database (use with caution):
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%';
- Update data by finding and replacing specific values:
Enable General Query Log:
- Enable the general query log for debugging and analysis:
SET GLOBAL general_log = 1;
- Enable the general query log for debugging and analysis:
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;
- Convert the character set and collation of a database:
Empty a Table:
- Delete all records in a table, keeping the structure intact:
DELETE FROM table_name;
- Delete all records in a table, keeping the structure intact:
InnoDB Deadlock Monitor:
- Enable the InnoDB deadlock monitor to collect information:
SET GLOBAL innodb_print_all_deadlocks = ON;
- Enable the InnoDB deadlock monitor to collect information:
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';
- Delete all views within a database:
Database Binary Logging Control:
- Enable or disable binary logging for the entire server:
SET GLOBAL log_bin = OFF;
- Enable or disable binary logging for the entire server:
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
- Create a backup of a database with a timestamp in the filename:
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;
- Warm up the InnoDB buffer pool by reading all pages:
Show Binlog Events:
- Display binary log events for replication analysis:
SHOW BINLOG EVENTS;
- Display binary log events for replication analysis:
Enable Slow Query Log:
- Enable the slow query log to capture slow queries:
SET GLOBAL slow_query_log = ON;
- Enable the slow query log to capture slow queries:
InnoDB Page Compression Settings:
- View and modify InnoDB page compression settings:
SHOW VARIABLES LIKE 'innodb_compression%';
- View and modify InnoDB page compression settings:
Change MySQL Port:
- Change the MySQL server port number:
SET GLOBAL port = new_port_number;
- Change the MySQL server port number:
Show Query Cache Size:
- Check the size of the query cache: ```sql
SHOW VARIABLES LIKE 'query_cache_size';
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;
- Adjust the size of InnoDB log files:
Show Slave Status:
- Display the status of a MySQL replication slave:
SHOW SLAVE STATUS;
- Display the status of a MySQL replication slave:
InnoDB Flush Method:
- Set the InnoDB flush method (O_DIRECT, O_DSYNC, etc.):
SET GLOBAL innodb_flush_method = 'O_DIRECT';
- Set the InnoDB flush method (O_DIRECT, O_DSYNC, etc.):
Query Cache Clear:
- Clear the query cache to remove cached query results:
RESET QUERY CACHE;
- Clear the query cache to remove cached query results:
Change Max Allowed Packet Size:
- Modify the maximum allowed packet size:
SET GLOBAL max_allowed_packet = size_in_bytes;
- Modify the maximum allowed packet size:
Query Execution Plan Analysis:
- Analyze the execution plan of a query for optimization:
EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';
- Analyze the execution plan of a query for optimization:
InnoDB Buffer Pool Size Adjustment:
- Adjust the InnoDB buffer pool size dynamically:
SET GLOBAL innodb_buffer_pool_size = new_size_in_bytes;
- Adjust the InnoDB buffer pool size dynamically:
Reload MySQL Privileges:
- Reload the privileges from the grant tables:
FLUSH PRIVILEGES;
- Reload the privileges from the grant tables:
Check InnoDB Status:
- Check the status of the InnoDB storage engine:
SHOW ENGINE INNODB STATUS;
- Check the status of the InnoDB storage engine:
Transaction Isolation Level:
- Set the transaction isolation level for a session:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
- Set the transaction isolation level for a session:
Table Fragmentation Check:
- Check for table fragmentation and optimize if needed:
ANALYZE TABLE table_name;
- Check for table fragmentation and optimize if needed:
InnoDB Log File Checkpointing:
- Manually trigger an InnoDB log file checkpoint:
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
- Manually trigger an InnoDB log file checkpoint:
Database Index Usage Analysis:
- Identify which indexes are used in a query:
EXPLAIN SELECT * FROM table_name WHERE indexed_column = 'value';
- Identify which indexes are used in a query:
These additional commands cover various aspects of database management, optimization, security, and troubleshooting, providing a comprehensive list of MySQL commands for DevOps engineers. Remember to use them responsibly and adapt them to your specific requirements and environment.
```