rand(Ø)

> beautiful code & photos

about <

Posts tagged: database

MySQL: silent insecure password warning

By Joris Berthelot Published January 22nd 2021 Tagged server, bash, process substitution, cli, console, devops, hack, mysql, zsh, trick, shell, protip, mariadb, linux, hint, howto, database, docker

I won't invent anything new here but I'll share a quick improvement on an answer I've found on Stack Overflow.

If you are doing batch actions and don't want to write in .mylogin.cnf file, there's the trick: use a process substitution!

$ mysqldump --defaults-extra-file=<(printf "[client]\nhost = host.rds.amazonaws.com\nuser = username\npassword = \$PassW0rd\$") \
    database > dump.sql

This also works for any other file input, like Docker env file run param:

> Continue Reading

MySQL: Table count for all databases

By Joris Berthelot Published February 22nd 2018 Tagged mysql, protip, sql, database, english, count, table

Answers I found on the internet were all based on the table information_schema.tables but truth is, this table contains table schemas of databases, not the database list.

What happens when you also need to include databases with no table?
Answer with this query:

SELECT SCHEMA_NAME AS 'Database', COUNT(information_schema.tables.TABLE_SCHEMA) AS 'Table Count'
FROM information_schema.SCHEMATA
LEFT JOIN information_schema.tables ON information_schema.tables.TABLE_SCHEMA = information_schema.SCHEMATA.SCHEMA_NAME
WHERE SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'performance_schema', 'tmp')
GROUP BY SCHEMA_NAME;

+----------+-------------+
| Database | Table Count |
+----------+
> Continue Reading

MySQL: adding an index on a big-ass table

By Joris Berthelot Published July 28th 2016 Tagged mysql, howto, english, code, sql, database

Okay, I'll admit it: SQL and big data things aren't at the top of my skillset.

No earlier than today, I resolved an issue we had in our customer databases: adding a missing index.
Seems easy at first but if the table is HUGE (yea, 8GB+ is big for me) with about 246MM rows (!!), it gets damn slow and the downtime doesn't last for an eye blink.

Reminder: ALTER TABLE allows read operations while altering the table then blocks reads and writes ops when the alteration is done and needs to go live. This may vary according to the configuration

> Continue Reading