MySQL is the #1 open source database. Because of MySQL’s high
performance, scalability, and availability, it has been widely popular for Web
applications and has become the foundation for IT platforms that support
high-traffic social media portals, e-commerce applications, and fast-growing
organizations. As business... [Read More
I published my 2014 presentation “Making MySQL highly available using Oracle Grid Infrastructure” in Slideshare. You can view it here:
At last night's WWCode Cincinnati panel, I recommended that developers talk to their DBA about what advanced capabilities their RDBMS can offer, so that they don't end up reimplementing functionality in the app that are already available (better and more efficiently) in the database itself. Devs can waste a lot of effort by thinking of databases as dumb, inert data boxes.
I was asked an excellent question: "Where can a dev quickly familiarize herself with (more...)
I last wrote about Couchbase in November, 2012, around the time of Couchbase 2.0. One of the many new features I mentioned then was secondary indexing. Ravi Mayuram just checked in to tell me about Couchbase 4.0. One of the important new features he mentioned was what I think he said was Couchbase’s “first version” of secondary indexing. Obviously, I’m confused.
Now that you’re duly warned, let me remind you of aspects of (more...)
directory is filesystem directory created at root level of mounted drive for ext file systems. It is used by file system check tools (fsck) for file recoveries.
In MySql world it can cause confusion or possible problems with synchronisation in Galera cluster configuration. Let’s check some examples.
I have MySQL database with datadir=/data in configuration file. I have deleted lost+found directory and restarted MySQL service.
When I list my databases this is result:
Percona Toolkit is collection of command-line tools to perform many MySQL tasks like creating backups, finding duplicate indexes, managing replication, etc.
In this post I will talk about how to measure replication lag and check/fix replication inconsistencies with this tools: pt-heartbeatpt-table-checksumpt-table-sync
I am using environment from previous blog post.
Master-Master replication with MariaDB 10.0.16 database on Debian 7.
Install Percona Toolkit
on both nodes:
$ sudo wget percona.com/get/percona-toolkit.deb
The simplest and probably most common replication method is master-slave
replication. Basically, data is replicated from master database to the slave. In case of master database failure you must get the slave database up-to-date before failover and then promote slave to be new master.
Another method is to set up replication in both directions called master-master
replication. But you must be aware that this setup brings some potential issues as data changes are happening on (more...)
I’m taking a few weeks defocused from work, as a kind of grandpaternity leave. That said, the venue for my Dances of Infant Calming is a small-but-nice apartment in San Francisco, so a certain amount of thinking about tech industries is inevitable. I even found time last Tuesday to meet or speak with my clients at WibiData, MemSQL, Cloudera, Citus Data, and MongoDB. And thus:
1. I’ve been sloppy in my terminology around “geo-distribution”, in (more...)
I've never had a tool I really liked that would extract a chunk of a large production database for testing purposes while respecting the database's foreign keys. This past week I finally got to write one: rdbms-subsetter.
rdbms-subsetter postgresql://user:passwd@host/source_db postgresql://user:passwd@host/excerpted_db 0.001
Getting it to respect referential integrity "upward" - guaranteeing every needed parent record would be included for each child row - took less than a day. Trying to get it to also guarantee (more...)
A Pandas DataFrame has a nice to_sql(table_name, sqlalchemy_engine) method that saves itself to a database.
The only trouble is that coming up with the SQLAlchemy Engine object is a little bit of a pain, and if you're using the IPython %sql magic, your %sql session already has an SQLAlchemy engine anyway. So I created a bogus
PERSIST pseudo-SQL command that simply calls
to_sql with the open database connection:
%sql PERSIST mydataframe
The result is (more...)
PyOhio gave my lightning talk on ddlgenerator a warm reception, and Brandon Lorenz got me thinking, and PyOhio sprints filled my with py-drenaline, and now ddlgenerator can inspect your data and spit out SQLAlchemy model definitions for you:
$ cat merovingians.yaml
name: Clovis I
name: Childebert I
$ ddlgenerator --inserts sqlalchemy merovingians.yaml
from sqlalchemy import create_engine, Column, Integer, Table, Unicode
I've had it on github for a while, but I finally released ddlgenerator to PyPI.
I've been frustrated for years that there was no good open-source way to set up RDBMS tables from flat data files. Sure, you could import the data - after setting up the DDL by hand. ddlgenerator handles that; in fact, you can go from zero, setting up and populating a table in a single line. Nothing up my sleeve:
Sometimes people want to connect to MySQL from Oracle and copy table data between the databases. You can do that with Oracle Hetrogenous Services via ODBC. This post will show how to create an odbc connection to your MySQL database which is the first part of this.
For my example, (more...)
I find myself installing and running mysql of different versions in different places for different reasons all the time (well often enough to do it and not remember the little things that cost time when setting up) Its with that in mind, I'm making notes for myself and you (more...)
For my newest ipython-sql trick, I needed to compare some queries run across different databases. How hard would it be to get side-by-side results into tidy IPython Notebook output?
Not hard at all, it turns out, if you're willing to violate basic principles of human decency.
That's an itty-bitty image, (more...)
When to consider sharding a MySQL database
Why should you not just design your applications from the start to account for data sharding? Sharding a database, that is horizontally partitioning tables among multiple databases, creates an added level of complexity that is not desirable in an application. However, there are times when sharding is required to maintain acceptable performance. So when is the right time to shard your database?
When to consider sharding a MySQL database Why should you not just design your applications from the start to account for data sharding? Sharding a database, that is horizontally partitioning tables among multiple databases, creates an added level of complexity that is not desirable in an application. However, there are times when sharding is […]