Introduction to MySQL DB

 

To connect to MySQL instance:

/opt/homebrew/opt/mysql-client/bin/mysql \

-u USER \

-p \

-hDB_HOST

or

% /opt/homebrew/opt/mysql-client/bin/mysql \    
-u USER \
-pPASS \
-h DB_HOST \
DB_NAME

 
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1198
Server version: 8.0.35 Source distribution
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 

To show all databases in this MySQL server:

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| my_wordpress    |

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

5 rows in set (0.09 sec)

To change the current database:

mysql> use my_wordpress;

Database changed

To list all columns in some table:

mysql> describe my_table;

+------------+---------------------+------+-----+---------------------+----------------+

| Field      | Type                | Null | Key | Default             | Extra          |

+------------+---------------------+------+-----+---------------------+----------------+

| id         | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment 

| post_id    | bigint(20) unsigned | NO   | MUL | 0                   |                

| post_type  | varchar(20)         | NO   |     | post                |                

| created_at | datetime            | NO   |     | 0000-00-00 00:00:00 |                

| author_id  | bigint(20) unsigned | NO   | MUL | 0                   |                

| new        | tinyint(1)          | YES  |     | 0                   |                

+------------+---------------------+------+-----+---------------------+----------------+

6 rows in set (0.003 sec)

Another way to find out the names of all columns:

mysql> select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='wp_postmeta';

+-------------+

| COLUMN_NAME |

+-------------+

| meta_id     |

| post_id     |

| meta_key    |

| meta_value  |

+-------------+

4 rows in set (0.08 sec)

Note that columns returned might not be listed in the order that they are in the table. On some other MySQL instance, the same query returned the columns listed in different order:

MySQL [my_db]> select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='wp_postmeta';

+-------------+

| COLUMN_NAME |

+-------------+

| meta_id     |

| meta_key    |

| meta_value  |

| post_id     |

+-------------+

4 rows in set (0.002 sec)

To make sure you're using the right values for the right column, the best is to see how e.g. 1st row looks like:

MySQL [my_db]> select * from wp_postmeta where meta_key like '%custom_string_%' LIMIT 1;

+---------+---------+---------------------------+------------+

| meta_id | post_id | meta_key                  | meta_value |

+---------+---------+---------------------------+------------+

|  225532 |    2289 | _custom_string_enabled    | 0          |

+---------+---------+---------------------------+------------+

1 row in set (0.034 sec)

To list all records (rows) that contain field/attribute value that ends with some string e.g. "origin" (% means any character):

 

mysql> select * from wp_postmeta where meta_key like '%origin';

+---------+---------+-----------------------------+---------------------+

| meta_id | post_id | meta_key                    | meta_value          |

+---------+---------+-----------------------------+---------------------+

| 1085763 |    8845 | origin  |                     |

| 1085764 |    8845 | _origin | field_5d99d579566f1 |

...

| 5836494 |   88486 | origin  | 1                   |

| 5836495 |   88486 | _origin | field_5d99d579566f1 |

+---------+---------+-----------------------------+---------------------+

2980 rows in set (2.08 sec)

Sometimes we're interested only in the number of returned rows:

mysql> select count(*) from wp_postmeta where meta_key like '%origin';

+----------+

| count(*) |

+----------+

|     6444 |

+----------+

1 row in set (1.364 sec)

To delete rows above:

mysql> delete from wp_postmeta where meta_key like '%origin';

Query OK, 2980 rows affected (2.81 sec)

After deleting rows from a table, it's recommended to optimize the affected table:

mysql> optimize table wp_postmeta;

+-----------------------------+----------+----------+-------------------------------------------------------------------+

| Table                       | Op       | Msg_type | Msg_text                                                          |

+-----------------------------+----------+----------+-------------------------------------------------------------------+

| my_wordpress.wp_postmeta | optimize | note     | Table does not support optimize, doing recreate + analyze instead |

| my_wordpress.wp_postmeta | optimize | status   | OK                                                                |

+-----------------------------+----------+----------+-------------------------------------------------------------------+

2 rows in set (34.30 sec)

To exit from the interactive terminal:

mysql> exit

Bye

To find out which tables contain some string:

% /opt/homebrew/opt/mysql-client/bin/mysqldump \
-u USER \
-pPASS \
-h DB_HOST \
--no-create-info \
--extended-insert=FALSE \
DB_NAME | grep STRING  > dump_STRING.txt

mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
Warning: A dump from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that were executed during its extraction and might not be represented in the dumped data. This might result in an inconsistent data dump. 
In order to ensure a consistent backup of the database, pass --single-transaction or --lock-all-tables or --source-data. 

User Management

To list all users:

SELECT User, Host FROM mysql.user;

If we want to do it in a single command without entering the MySQL shell:

/opt/homebrew/opt/mysql-client/bin/mysql -u root -p -e "SELECT User, Host FROM mysql.user;"

For more detailed information about users:

SELECT User, Host, authentication_string, plugin FROM mysql.user;

• The -e flag lets you execute a query directly from the command line
• If we want a vertical output format (easier to read), add \G at the end of the query instead of ;

We can also check currently connected users with: 

SELECT USER(); 

or 

SHOW PROCESSLIST;

To delete user:

DROP USER 'bojan'@'%';

User needs to have CREATE USER permission in order to be able to delete users.

---