Awhile ago I found myself analyzing a MySQL fabric installation to understand why a group member was occasionally being marked as FAULTY even when the server was up and running and no failures were observed.  

         
                         server_uuid     address  status       mode weight
------------------------------------ ----------- ------- ---------- ------
ab0b0653-6121-11c5-55a0-007543445454 mysql1:3306 PRIMARY READ_WRITE    1.0
f34dd331-2432-11f4-a2d3-006754678533 mysql2:3306 FAULTY  READ_ONLY     1.0

Upon reviewing mysqlfabric logs, I found the following warnings were being logged from time to time:

[WARNING] 1442221217.920115 - FailureDetector(xc_grp_1) - Server (f34dd331-2432-11f4-a2d3-006754678533) in group (xc_grp_1) is unreachable

Since I was not clear under which circumstances a server is marked as FAULTY, I decided to review MySQL Fabric code (Python) to better …

Replication between Tungsten clusters

The process I will describe in this post will allow you to configure replication between Tungsten clusters. The most common use case I have seen for this is a dedicated ETL cluster.

The setup will look like this:

So we will have a composite datasource (compositeprod) composed of east and west clusters, and two ETL clusters, one on each side.

The described setup allows failover or switchover within a single datacenter (e.g. db1.east -> db2.east) or to the Disaster Recovery Site, (e.g. db1.east -> db1.west) for the core cluster.

At the time of this article’s publication it is not possible to replicate between two composite clusters, so each ETL cluster needs to be standalone. This feature is expected for Tungsten 5.0 release.

We will install a standalone replicator on the ETL hosts to bring data in …

Overview

Cloud storage is becoming more and more popular for offsite storage and DR solutions for many businesses. This post will help with those people that want to perform this process for MySQL backups directly into Amazon S3 Storage. These steps can probably also be adapted for other processes that may not be MySQL oriented.

Steps

In order to perform this task we need to be able to stream the data, encrypt it, and then upload it to S3. There are a number of ways to do each step and I will try and dive into multiple examples so that way you can mix and match the solution to your desired results.  The AWS S3 CLI tools that I will be using to do the upload also allows encryption but to try and get these steps open for customization, I am going to do the encryption in the stream.

1. Stream MySQL backup
2. Encrypt the stream
3. Upload the stream to AWS S3

Step 1 : …

Since documentation is not very clear to me on the topic of InnoDB flushing in combination with Linux IO (specifically the write system call), I decided to put together this article in hopes of shedding some light on the matter.

How Linux does I/O

By default, the write() system call returns after all data has been copied from the user space file descriptor into the kernel space buffers. There is no guarantee that data has actually reached the physical storage.

The fsync() call is our friend here. This will block and return only after the data and metadata (e.g. file size, last update time) is completely transferred to the actual physical storage.

There is also fdatasync() which only guarantees the data …

It is not uncommon to come across MySQL databases where reserved words are in use as identifiers for any kind of database objects.

Perhaps when the application schema was implemented, the words were not reserved yet, and they became reserved later on a subsequent MySQL release.

It is a good practice to check reserved words usage prior to doing any database upgrades, as any newly reserved keywords will cause syntax errors on the new version.

This is usually not a problem if proper quoting is used for referencing the objects, as described on the official manual page.

The actual steps to do this depend on the environment; for example, the following can be configured to tell Hibernate to escape identifiers:

property name="hibernate.globally_quoted_identifiers" value="true"

This does …

I’ve previously evaluated MariaDB’s 10.1 implementation of data encryption at rest (https://www.pythian.com/blog/data-encryption-rest), and recently did the same for Oracle’s implementation (https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html) in their MySQL 5.7.

First, here’s a walkthrough of enabling encryption for MySQL 5.7:

1. Install keyring plugin.

1a. Add the following to the [mysqld] section of /etc/my.cnf:

View the code on Gist.

1b. Restart the server:

...
service mysqld restart

1c. Verify:

... …

Overview

DDL (Data Definition Language) statements create, alter, and remove database objects. These types of changes can be a very dangerous action to take on such a critical piece of your infrastructure. You want to make sure that the command that you are executing has been given proper thought and testing.

In this post I go through multiple version of MySQL and verify the best course of action to take in regards to executing DDL statements.  There are many things that you have to consider when making these types of changes, such as disk space, load on the database server, slave replication, the type of DDL statement you are executing, and if it will lock the table. 

Because of these risks, there are tools that can be used to help mitigate some of the dangers. But unless you have tested and verified their functionality, these tools in themselves can cause trouble. Whenever in doubt, take the time to test …

Maintaining mission critical databases on our pitchfork wielding brother, the “Daemon” of FreeBSD, seems quite daunting, or even absurd, from the perspective of a die-hard Linux expert, or from someone who has not touched it in a long time. The question we ask when we see FreeBSD these days is “why?”.  Most of my own experience with FreeBSD was obtained 10-15 years ago.  Back then, in the view of the team I was working on, a custom compiled-from-source operating system like FreeBSD 5.x or 6.x was superior to a Linux binary release.

Package managers like YUM and APT were not as good.  They did not always perform MD5 checks and use SSL like today’s versions. RedHat wasn’t releasing security updates 5 minutes after a vulnerability was discovered. Ubuntu didn’t exist. Debian stable would get so very old before receiving a new version upgrade. FreeBSD was a great choice for a maintainable, secure, free open …

Today I start this MySQL blog which will apply a focus on all things MySQL and attempt to bring out their practical usage.  In this particular blog I will give an introduction to MySQL by helping us all get a simple way to test new MySQL features or capabilities through the usage of Vagrant, Virtual Box and then setting… Read More »

This blog post was co-authored by Peter Sylvester and Valerie Parham-Thompson

Introduced in version 10.1.3 (and with substantial changes in 10.1.4), the MariaDB data encryption at rest feature allows for transparent encryption at the tablespace level for various storage engines, including InnoDB and Aria.

Before now, there have been only two widely accepted encryption methods for MySQL/MariaDB: encryption at the file system level, or encryption at the column level. For comparison, we’ll do a brief overview of how these work, as well as the pros and cons typically associated with each option.

File System Encryption

This is performed by setting a file system to be encrypted at the block level within the operating system itself, and then specifying that the encrypted volume should be the location of the data directory for MySQL/MariaDB. You can also use encrypted volumes to store MariaDB binary logs. …