Skip to content

MySQL

Schemes aka databases

Create

1
CREATE DATABASE `schema`;

Delete

1
DROP DATABASE `schema`;

Use

1
USE `schema`;

Tables

Schema

If the schema was selected before, then the schema before table is not needed.

Show

1
DESCRIBE `schema`.`table`;

Users

Show

1
SELECT `user` FROM `mysql`.`user`;

One user can exist per host, so it makes sense to display the host as well.

1
SELECT `user`, `host` FROM `mysql`.`user`;

Create

Create a new user for a host with a password.

1
CREATE USER `user`@`host` IDENTIFIED BY 'password';

Change password

Change the password of an existing user.

1
ALTER USER `user`@`host` IDENTIFIED BY 'password';

For the changes to take effect directly, the privileges needs to be flushed.

1
FLUSH PRIVILEGES;

Delete

Delete an existing user.

1
DROP USER `user`@`host`;

Host

The host limits the servers from which the user may log in. The % character can be used as a wildcard for any host.

Privileges

Privilege Description
ALL PRIVILEGES A wildcard for all permissions on the selected database object, with a *.* on all databases.
CREATE Allows a user to create new databases.
DROP Allows a user to delete databases.
DELETE Allows a user to delete individual rows in a table.
INSERT Allows a user to write new rows to a table.
SELECT Read permissions on a database or table.
UPDATE Permission to update a line.
GRANT OPTION Allows a user to set or revoke the rights of other users.

The * is a wildcard e.g., if you want to assign permissions to all tables of a database.

Show

1
SHOW GRANTS FOR `user`@`host`;

Assign

1
GRANT [ALL PRIVILEGES|[CREATE|DROP|DELETE|INSERT|SELECT|UPDATE|GRANT OPTION]] ON `schema`.`table` TO `user`@`host`;

Clear cache

The rights are kept in the working memory, therefore this should be emptied once after the rights are set, so that the newly set rights take effect directly.

1
FLUSH PRIVILEGES;

Server status

The command status displays information about the current MySQL server status.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--------------
mysql  Ver 8.0.0-0.0 for Linux on x86_64

Connection id:          174
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.0-0.0 MySQL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/run/mysqld/mysqld.sock
Binary data as:         Hexadecimal
Uptime:                 02 min 35 sec

Threads: 10 Questions: 64 Slow queries: 0 Opens: 200 Flush tables: 5 Open tables: 50 Queries per second avg: 0.027
--------------

Console output

If you add an \G to a command for an output, the output is displayed more legibly.

1
SHOW TABLES\G;

Solve problems

If problems occur, it is recommended to run a MySQL upgrade first, which checks the tables, fixes any errors and updates them.

1
mysql_upgrade -u root -p