Tag: mysql

  • 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( […]

  • 关于 mysql2 gem 和 MariaDB

    MariaDB 可以被看作是 MySQL 的增强版, 在绝大多数情况下用 MariaDB 替代 MySQL 都能取得不错的效果. 只是一些接口程序依旧把 MariaDB 当作 MySQL (这也没什么错吧), 例如安装 mysql2 gem 的时候, 我不得不如此才能把这个 gem 正常安装: sudo apt-get install libmariadb-client-lgpl-dev libmariadb-client-lgpl-dev-compat gem install mysql2 -v ‘0.3.14’ — –with-mysql-include=/usr/include/mariadb/ 🙂

  • 利用 bash 命令做 SQL 数据修补

    如果是把一个 DB 或者一个 table 从一个服务器送到另一个, 那么使用 mysqldump 命令就可以了. 但有的时候, 需要的数据可能只是若干条记录, 就不能用 mysqldump 了. 奇怪的是我 google 了一番居然也没找到很方便的方法. 求人不如求自己吧, 下面是用 bash 命令的解决方法: mysql <database> -uroot -e “select * from <table> where <condition>…” |tail -n +2 |sed -e ‘s/^/(“/g’ -e ‘s/$/”)/g’ -e ‘s/\t/”,”/g’ |while read values; do echo “insert into <table> values $values”; done 然后把输出的 SQL 在目标服务器上运行即可. 记得把< […]