MySQL ST_Distance_Sphere Polyfill

One of the cool things about MySQL 5.7 is the fact that it sports a few spatial convenience functions (since 5.7.6), allowing one to do operations on geometric values. One of those convenience functions is ST_Distance_Sphere, which allows one to calculate the (spherical) distance between two points. Recently I was working an project where I […]

Context aware MySQL pools via HAProxy

At GitHub they use MySQL as their main datastore. The setup is a typical “single-writer-multiple-readers” design. They loadbalance between server pools using HAProxy, with some cleverness built in: Instead [of checking whether a MySQL server is live with mysql-check], we make our HAProxy pools context aware. We let the backend MySQL hosts make an informed […]

Why Uber Engineering Switched from Postgres to MySQL

Postgres served us well in the early days of Uber, but we ran into significant problems scaling Postgres with our growth. In this article, we’ll explore some of the drawbacks we found with Postgres and explain the decision to build Schemaless and other backend services on top of MySQL. Why Uber Engineering Switched from Postgres […]

MySQL behaviour you should be aware of

I suggest changing the syntax for NOT NULL to “NOT” NULL. If you’re not checking all values in your code logic (viz. PHP code or something like that) before attempting to perform the database manipulation you can fix it by (manually) add triggers to your tables to actually prevent a faulty insert/update: mysql> CREATE TRIGGER […]

WebScaleSQL

WebScaleSQL is a collaboration among engineers from several companies that face similar challenges in running MySQL at scale, and seek greater performance from a database technology tailored for their needs. Those “several companies” are Facebook, Google, LinkedIn, and Twitter. And the collaboration mentioned is a true collaboration: For example, to introduce a code change, a […]

Backup local MySQL Databases

Nice script by Xavez. Put it in a daily cronjob if you like, or extend it to copy the backup to another machine: #!/bin/bash # Path to where you want to backup mysql databases. opath=/Users/username/Sites/Backups/mysql/ # Local mysql details. Make a username with only read access. Allow SELECT, LOCK TABLES. mysqlhost=127.0.0.1 username=read_only_user password=read_only_password # Get […]

node-orm2 — Node.js Object Relational Mapping

ORM Package for Node.js. Works with MySQL, PostgreSQL and SQLite. var orm = require(‘orm’); orm.connect("mysql://username:[email protected]/database", function (err, db) { if (err) throw err; var Person = db.define(‘person’, { name : String, surname : String, age : Number, male : Boolean, continent : [ ‘Europe’, ‘America’, ‘Asia’, ‘Africa’, ‘Australia’, ‘Antartica’ ], // ENUM type photo : […]

MySQL: Get next AUTO_INCREMENT value from/for table

Note to self: To get the next auto_increment value from a table run this query: SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = "databaseName" AND TABLE_NAME = "tableName" Don’t forget it (again). Did this help you out? Like what you see?Consider donating. I don’t run ads on my blog nor do I do this for profit. […]