Posts Tagged ‘MySQL’

MySQL 5.7: root password is not in mysqld.log

I came across this issue today when working on an ansible playbook with MySQL 5.7. Old habits die hard and I was still trying to use mysql_install_db to initialise my instance. It seems a few others have been doing the same. The effect of using mysql_install_db in more recent version of MySQL is that we end […]

Bash: Count the number of databases in a gzip compressed mysqldump

A simple bash one-liner! ?View Code BASHgunzip -c /path/to/backup/mysqldump.sql.gz | grep -E "^CREATE DATABASE" | wc -l Breaking this down.. This prints the contents of a gzip compressed mysqldump to the terminal ?View Code BASHgunzip -c /path/to/backup/mysqldump.sql.gz Grep for lines that start with CREATE DATABASES… ?View Code BASHgrep -E "^CREATE DATABASE" Count the number of […]

MariaDB: subquery causes table scan

I got asked today to look at some slow queries on a MariaDB 10 instance. Here are the anonymized results of the investigation I did into this and how I solved the issue… There were a bunch of queries, expected to run very fast, that were taking 10-15 seconds to execute. The queries were all […]

Recover a single table from a mysqldump

I needed to recover the data, from a single table, from a mysqldump containing all the databases from an entire instance. A quick google yielded this result. This produced a nifty little sed one-liner… ?View Code BASHsed -n -e ‘/CREATE TABLE.*your_table_name/,/CREATE TABLE/p’ mysqldump_file.sql > your_table_name.sql I also wanted to import the data into a different […]

Better “read_only” slaves in MariaDB / MySQL

UPDATE: As of MySQL 5.7.8 there is super_read_only so use this instead of this trick. It’s always been an annoyance that read_only in MySQL actually means “read only apart from those with the SUPER priv”. Now I know it’s best practice not to give this permission out to users but sometimes we are stuck with the choices […]