• Performance Settings

• The always relevant Arch Wiki

shell> mysqldump --databases db1 db2 db3 > dump.sql

mysqldump --all-databases > dump.sql

• JSONB Cheat Sheet

You can query with the `@>` operator on `metadata`. This operator can compare partial JSON strings against a JSONB column. It’s the containment operator. For this case you may need to add a GIN index on `metadata` column.

SELECT * FROM users WHERE metadata @> '{"country": "Peru"}';

The `→>` operator gets a JSON object field as text. Use it if you want to query a simple field in a JSONB column. You might add a B-tree index on `metadata→>'country'`.

SELECT * FROM users WHERE metadata->>'country' = 'Peru';

Once again, the `→>` operator gets a JSON object field as text. Just use directly it in the `SELECT`.

SELECT metadata->>'country' FROM users;

You can use the `→>` operator with the classic operator you use on text: `=`,`<>`,`IS NULL`, etc. Do not forget to index `metadata→>'country'` with a B-tree index.

 SELECT * FROM users WHERE metadata->>'country' IS NOT NULL; 

You can use both `@>` or `→>`, just like for first level attribute. Add an index according to your choice.

SELECT * FROM users WHERE metadata->'company'->>'name' = "Mozilla"; SELECT * FROM users WHERE metadata @> '{"company":{"name": "Mozilla"}}';

Remembering `@>` operator checks containment in a JSONB column, you can query on an array like `{“x”: [“a”, “b”, “c”]“}` by just passing `{“x”:[“a”]}` to the `WHERE` clause:

SELECT * FROM users WHERE metadata @> '{"companies": ["Mozilla"]}';

Sometimes, we may need to select items where the attributes inside a JSONB column matches a bunch of possible values.

SELECT * FROM users WHERE metadata->>'countries' IN ('Chad', 'Japan');

Use `UPDATE … SET` as usual and pass the whole object as JSON.

UPDATE users SET metadata = '{"country": "India"}';

Use the `||` operator to concatenate the actual data with the new data. It will update or insert the value.

UPDATE users SET metadata = metadata || '{"country": "Egypt"}';

The operator - removes a key from an object.

UPDATE users SET metadata = metadata - 'country';

MariaDB [(none)]> show global variables like 'slow%log%';
+---------------------+-------------------------+
| Variable_name       | Value                   |
+---------------------+-------------------------+
| slow_query_log      | ON                      |
| slow_query_log_file | /var/log/mysql/slow.log |
+---------------------+-------------------------+

MariaDB [(none)]> set global slow_query_log_file='/var/log/mysql/slow.log';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> set global slow_query_log=ON;
Query OK, 0 rows affected (0.001 sec)