To connect to MySQL instance:
% /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
-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.
---
No comments:
Post a Comment