-
Monitoring MySQL with mysqld-exporter
TL; DR: I use mysqld-exporter with Prometheus to monitor my MySQL server. The mysqld-exporter runs as a container in my Kubernetes cluster. A sample manifest looks like: 🙂
-
Cloud SQL MySQL: Best Password is No Password
Security for web applications has come a long way. In the case of database passwords, there is a lot of approaches to keep them safe: But isn’t it the best if there’s no need for a password at all? With IAM authentication for Cloud SQL, the password can be avoided after all. Here’s how to…
-
How to Connect to Cloud SQL via SSH
TL;DR: here’s a handy bash script which can connect to a private Cloud SQL instance(MySQL in this case) via a bastion host. Prerequisites: Google Cloud SQL instance with mTLS certs provisioned Google Cloud SDK installed(the gcloud command) a bash shell(better be V5.0+) OpenSSH and MySQL CLI installed Google Cloud SQL is an RDB as a…
-
A Load Balancer for Cloud SQL Replicas
TL;DR: As of Aug 2022, there’s no out of box load balancing for Google Cloud SQL read replicas. So I built one for MySQL replicas with HAProxy with the ability to dynamically reload when number of replicas has changed. It will be quite straight forward to run a few HAProxy pods in Kubernetes as a…
-
Flyway Container, MySQL and SSL/mTLS
Flyway is a handy utility to manage database schema migrations. Very similar to the schema migration mechanism in Ruby on Rails or Django, but Flyway is a standalone tool. So it’s best suited for some project which doesn’t have database schema management yet. I needed to use Flyway for a project I worked with, the…
-
Use MySQL Trigger To Do Incremental ETL
There’s a huge MySQL table that I need to ETL to Google BigQuery daily, about 1 billion rows. The rows are updated in a random fashion all the time so I can’t do incremental ETL by the recording the max primary key. Then my colleague brought up the trigger idea, which I believe is the…
-
MySQL/ Aurora to Google BigQuery
Google BigQuery(BQ) is Google’s column storage, it’s super fast when you need to query over many gigabytes of data. BQ defaults to utf8, so it makes sense the tables in MySQL/ Aurora are also utf8 encoded. I use Python and SQL Alchemy to load data to BQ. It’s a waste for SA’s ORM features but…
-
MySQL VS. MariaDB 之日期格式差异
MariaDB Foundation 号称 MariaDB 是 MySQL 的替代品, 但我发现它们之间还是有些不兼容的地方(而且不在已列出的清单里). MariaDB 似乎对日期格式要求更为严格. 以下 SQL 的执行结果就很有不同, 在 MySQL 中 changed_at 是正常的 datetime, 但是在 MariaDB 中 changed_at 完全是 ‘0000-00-00 00:00:00’ 的样子. INSERT INTO … SELECT … GREATEST( COALESCE(`inspection`.`ts`, 0), COALESCE(`inspection_details`.`ts`, 0), COALESCE(`open_time_reason`.`ts`, 0) ) AS `changed_at` ON DUPLICATE KEY UPDATE … `changed_at` = VALUES(`changed_at`) 解决方法是添加一个明确的格式转换: CONVERT(… , DATETIME) CONVERT(…