MySQL Cheatsheet
MySQL Cheatsheet - Essential Commands & Syntax
This MySQL cheatsheet provides a quick reference for essential commands and syntax for database management. It covers common operations such as managing permissions, creating tables, inserting and updating data, checking and repairing tables, and monitoring performance.
- Changing Tables
- Check and Repair Tables
- Rename Users
- Create Tables
- Delete Data
- Get Database Size in MB
- View Logged-in Users
- Indexes
- Insert Data
- Performance Schema Metrics
- Permissions Management
- Repair Table
- Scripting with MySQL
MySQL Permissions Management
Managing user permissions is crucial for database security. Here are common commands for creating databases, users, and granting privileges.
Create Database:
mysql> CREATE DATABASE my_db;
Create User:
mysql> CREATE USER 'user1'@'%' IDENTIFIED BY 'securepass';
Grant User All Permissions to Database:
mysql> GRANT ALL PRIVILEGES ON my_db.* TO 'user1'@'%';
Grant User All Permissions to All Databases with Grant Option:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' WITH GRANT OPTION;
Grant User Specific Permissions to Database in AWS RDS:
mysql> GRANT SELECT, UPDATE, DELETE, INSERT, CREATE, DROP, INDEX, ALTER, LOCK TABLES, EXECUTE, CREATE TEMPORARY TABLES, EXECUTE, TRIGGER, CREATE VIEW, SHOW VIEW, EVENT ON my_db.* TO user1@'%';
Update Password:
mysql> UPDATE mysql.user SET authentication_string = PASSWORD('foobar') WHERE User = 'demo' AND Host = '%';
# or
mysql> SET PASSWORD FOR 'demo'@'%' = PASSWORD('foobar');
# or
mysql> ALTER USER 'demo'@'%' IDENTIFIED BY 'foobar';
# or
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Flush Privileges:
mysql> FLUSH PRIVILEGES;
Rename Users
You can rename existing MySQL users using the RENAME USER command.
RENAME USER 'john12' TO 'john.permanent';
Create Tables
Define the structure of your data with the CREATE TABLE statement. This section covers creating tables with various options, including primary keys and selecting data from existing tables.
Create a Basic Table:
mysql> CREATE TABLE domains (
id INT(10) NOT NULL AUTO_INCREMENT,
domain varchar(50) NOT NULL,
owner varchar(50),
year_registered int(4)
);
Create a Table with a Primary Key:
mysql> CREATE TABLE domains (
id INT(10) NOT NULL AUTO_INCREMENT,
domain varchar(50) NOT NULL,
owner varchar(50),
year_registered int(4),
PRIMARY KEY (id)
);
Create a Table as a Select Query:
mysql> CREATE TABLE purchases_fnb_2016
AS
SELECT * FROM customers where date >= '2016-01-01 00:00:00'
AND date <= '2016-10-29 00:00:00'
AND bank = 'fnb'
;
Create a Table with a Boolean Data Type:
CREATE TABLE myusers(name VARCHAR(50), matriculated BOOLEAN);
Insert with Boolean Data:
INSERT INTO myusers VALUES('tom', False);
Read Data (Boolean Example):
mysql> select name, matriculated from myusers limit 1;
+------+--------------+
| name | matriculated |
+------+--------------+
| tom | 0 |
+------+--------------+
Use IF Statement and Replace Value (Boolean Example):
mysql> select name, IF(matriculated, 'yes', 'no') matriculated from myusers limit 1;
+------+--------------+
| name | matriculated |
+------+--------------+
| tom | no |
+------+--------------+
Query for People Not Matriculated:
mysql> select name, IF(matriculated, 'yes', 'no') matriculated from myusers where matriculated = false limit 1;
+------+--------------+
| name | matriculated |
+------+--------------+
| tom | no |
+------+--------------+
# You can also use it without the IF statement
View Unique Values:
mysql> select distinct country from people;
+---------+
| country |
+---------+
| MOZ |
| NGA |
| KEN |
| ZWE |
| IND |
| GHA |
+---------+
Create a Boolean Column with Default Value of False:
CREATE TABLE users (
active boolean not null default 0
);
Create a Timestamp Column:
CREATE TABLE users (
time_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Then create an entry with now().
Changing Tables
Modify existing table structures using the ALTER TABLE statement.
Changing the Column Length:
mysql> ALTER TABLE contacts_business CHANGE COLUMN contact_number contact_number varchar(40);
Check and Repair Tables
Maintain data integrity by checking and repairing corrupted tables. The mysqlcheck utility is invaluable for this.
Check All Tables in a Specific Database:
$ mysqlcheck -c mailscanner -u root -p
Check All Tables Across All Databases:
$ mysqlcheck -c -u root -p --all-databases
Check a Single Table:
$ check table maillog;
Analyze a Table:
Analyzes the employee table located in the thegeekstuff database.
$ mysqlcheck -a thegeekstuff employee -u root -p
Optimize a Table:
Optimizes the employee table located in the thegeekstuff database.
$ mysqlcheck -o thegeekstuff employee -u root -p
Repair a Table:
Repairs the employee table located in the thegeekstuff database.
$ mysqlcheck -r thegeekstuff employee -u root -p
Check, Optimize, and Repair All Corrupted Tables in a Database:
$ mysqlcheck -u root -p --auto-repair -c -o thegeekstuff
Optimize and Repair All Tables Across All Databases:
$ mysqlcheck -u root -p --auto-repair -c -o --all-databases
Delete Data
Remove unwanted data from your tables using the DELETE statement. It's often good practice to optimize the table afterward.
mysql> delete from maillog where timestamp < "2012-09-07";
mysql> optimize table maillog;
Get Database Size in MB
Monitor your database storage usage with this query to view the size of each database in megabytes.
SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 /
1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;
View Logged-in Users
See who is currently connected to your MySQL server and their activity.
SELECT SUBSTRING_INDEX(host, ':', 1) AS host_short,
GROUP_CONCAT(DISTINCT user) AS users,
COUNT(*) AS threads
FROM information_schema.processlist
GROUP BY host_short
ORDER BY COUNT(*), host_short;
Or, to count connections per user:
SELECT `USER`, COUNT(*) FROM information_schema.processlist GROUP BY `USER`;
Indexes
Improve query performance by adding indexes to your tables. This is essential for large datasets.
Add an Index to an Existing Table:
Example: Adding an index to the salary column in the salaries table.
mysql> DESCRIBE salaries;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| salary | int(11) | NO | | NULL | |
| from_date | date | NO | PRI | NULL | |
| to_date | date | NO | | NULL | |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
ALTER TABLE salaries ADD INDEX ( salary );
Insert Data
Populate your tables with new records using the INSERT INTO statement.
Insert Data into Our Domain Table:
mysql> INSERT INTO domains (domain,owner,year_registered) VALUES("example.com", "John", 2019);
Update Data
Modify existing records in your tables with the UPDATE statement.
Update a Student's Age and Language:
mysql> UPDATE students SET age = 30, language = 'english' WHERE student_id = 'fz9203493043';
Information Schema
The information_schema database provides metadata about your MySQL server, including connection details and variable settings.
Show Idle Connections:
mysql> select id, user, host, db, command, time from information_schema.processlist where command = "sleep";
+-----------+-------+---------------------+--------+---------+------+
| id | user | host | db | command | time |
+-----------+-------+---------------------+--------+---------+------+
| 659558686 | james | 172.31.27.126:37154 | mydb12 | Sleep | 332 |
Show Connected Sessions:
mysql> select count(*) from information_schema.processlist;
+----------+
| count(*) |
+----------+
| 60 |
+----------+
Or:
mysql> show status where variable_name = 'threads_connected';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 61 |
+-------------------+-------+
Show Connection Related Variables:
mysql> SHOW VARIABLES LIKE '%connections%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| extra_max_connections | 1 |
| max_connections | 1296 |
| max_user_connections | 0 |
+-----------------------+-------+
3 rows in set (0.16 sec)
Or:
mysql> show status like '%onn%';
+-----------------------------------------------+-----------+
| Variable_name | Value |
+-----------------------------------------------+-----------+
| Aborted_connects | 35 |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 66059 |
| Max_used_connections | 502 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Slave_connections | 0 |
| Slaves_connected | 1 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 46 |
| wsrep_connected | OFF |
+-----------------------------------------------+-----------+
17 rows in set (0.16 sec)
Show Cache Information:
mysql> SHOW VARIABLES LIKE 'have_query_cache';
mysql> SHOW STATUS LIKE 'Qcache%';
Show How Long a Connection Can Idle:
This setting determines the maximum time a connection can remain idle before being closed.
AWS Blog on Connection Timeout
mysql> SHOW VARIABLES LIKE 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
Binlog Format:
Check the current binary log format.
mysql> show variables like 'binlog_format';
Performance Schema Metrics
Leverage the Performance Schema to gain deep insights into your MySQL server's performance.
Execution Time of All Statement Types by Each User:
mysql> select * from sys.user_summary_by_statement_type;
+------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| user | statement | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| background | select | 1 | 212.00 us | 212.00 us | 0 ps | 1 | 0 | 0 | 0 |
| root | insert | 175 | 46.26 s | 823.43 ms | 6.33 s | 0 | 0 | 3919027 | 0 |
| root | select | 50 | 12.21 s | 10.00 s | 10.41 ms | 223 | 7498268 | 0 | 17 |
Slowest Statements (95th Percentile by Runtime):
mysql> select * from sys.statements_with_runtimes_in_95th_percentile\G
*************************** 1. row ***************************
query: SELECT `sleep` (?)
db: sys
full_scan:
exec_count: 1
err_count: 0
warn_count: 0
total_latency: 10.00 s
max_latency: 10.00 s
avg_latency: 10.00 s
rows_sent: 1
rows_sent_avg: 1
rows_examined: 0
rows_examined_avg: 0
first_seen: 2019-07-04 11:21:23.155084
last_seen: 2019-07-04 11:21:23.155084
digest: x
Number of Queries That Generated Errors or Warnings:
mysql> SELECT SUM(errors) FROM sys.statements_with_errors_or_warnings;
+-------------+
| SUM(errors) |
+-------------+
| 4 |
+-------------+
Repair Table
Use the REPAIR TABLE command to fix corrupted tables. It's often used in conjunction with OPTIMIZE TABLE.
mysql> repair table x;
mysql> optimize table x;
Scripting with MySQL
Automate database tasks by embedding SQL commands within shell scripts. This example demonstrates creating a database, table, and inserting data using a bash script.
#!/usr/bin/env bash
user="root"
password="rootpassword"
database="my_db"
mysql -h 127.0.0.1 -u"$user" -p"$password" "$database" <<EOF
CREATE DATABASE IF NOT EXISTS foo;
CREATE TABLE IF NOT EXISTS foo.hashes (
id INT AUTO_INCREMENT PRIMARY KEY,
number INT(10) NOT NULL,
hash VARCHAR(50) NOT NULL
);
EOF
for x in {1..10}
do
mysql -h 127.0.0.1 -u"$user" -p"$password" "$database" <<EOF
INSERT IGNORE INTO foo.hashes (id, number, hash) VALUES ('', $x, "$(openssl rand -hex 8)");
EOF
done
mysql -h 127.0.0.1 -u"$user" -p"$password" "$database" <<EOF
SELECT * FROM foo.hashes;
EOF
```