Breaking and unbreaking your data

Recently at FOSDEM, Maciej presented “Breaking and unbreaking your data”, a presentation about the potential problems you can incur regarding character encoding whilst working with MySQL. In short, there are a myriad of places where character encoding can be controlled, which gives ample opportunity for the system to break and for text to become unrecoverable.

The slides from the presentation are available on slideshare.

Character Encoding – MySQL DevRoom – FOSDEM 2015 from …

As before, let me see if I can or not. I’ll mail you privately.
Thanks,
K.

MySQL Installation Process Checklist

All DBAs, regardless of experience level, should follow a written process when setting up a new server.  There are just too many steps to neglect doing so and many of the steps you are likely to forget have little to do with MySQL.

Naturally, every company has a different process.  The process we outline below is one we have used in the past and focuses on working through the Change Management process, setting up backups and monitoring, and focusing on good communication with team members and clients as well as ensuring documentation of your work.  Hopefully this article will give you some ideas on implementing your own process document.

Below are the steps we have documented in the past when creating a new installation of MySQL:

• Initial Change Management Processes
  • Edit the ticket and set to Waiting on Customer
  …

https://lkml.org/lkml/2015/2/9/534

Building on the original kSplice idea and combining the efforts of the work done at Red Hat and SuSE, common infrastructure is now ready to be put into the Linux 3.20 mainline kernel – Red Hat and SuSE have already committed to using this.

I still reckon it’s freaky trickery, but heck – it works, and it’s great for server environments that have no redundancy (I prefer to fix that issue!) and can’t afford any downtime.

Introduction

This article details the proper method of load balancing either a Percona XTRADB Cluster (PXC) or MariaDB Cluster.  Both of these solutuions utilize the same Galera plugin to facilitate clustering.  This information deatails what ports must be open and how they are to be configured as well as how to route traffic to the database nodes.

There are generally a total of three (3) nodes in this cluster, along with a single load balancer. This is the minimum number of nodes required for a cluster. This does not mean that the cluster is down if one or two nodes are lost – it only means that at least three nodes are required for normal cluster operation.

Data Centers

One of the most common questions we receive is whether the cluster can span two data centers.  The short answer is “Yes, but…”   While it is possible for a cluster to span two data centers, it …

One common job for a DBA is working with a Development Team member on a batch loading process that is taking more time than expected.

Often once you start probing into what has already been done to performance tune the load process, it seems you often learn that very little has, in fact, been done to improve efficiency and speed.

Since there are so many things that can be done to improve this process, it really is outside the scope of this article to cover them all.  Hopefully this will serve as a guide to get you started in the process.

Let’s outline best practices for loading data into MySQL very quickly.  While this is not a comprehensive list of loading methods and configuration, it is a good starting point.

MySQL Configuration

Assuming you are loading into InnoDB tables (and you should probably be doing so), you will want to ensure that MySQL is properly …

The Brainiac Corner is a format where we talk with some of the smartest minds in the system, database, devops, and IT world. If you have opinions on pirates, or anything else related, please don’t hesitate to contact us.

Today, we interview Charity Majors, the production engineering manager for Parse at Facebook. Prior to the acquisition, she built Parse’s infrastructure. Shes hates/loves/hates databases and loves whiskey.

How did you get from stork to brainiac (i.e. what do you do today and how did you get there)?

I manage a team of production engineers working on Parse at Facebook. We are responsible for the performance, reliability, scalability, and database operations for 500,000+ mobile apps.

I started out doing classical piano performance, but I fell in love with computers in college. Since then I’ve worked all …

Sometimes data sets are so large, a mysqldump to load a slave is just not practical.  With some of the systems we have administrated, we have had data so large it would have taken days to load the slave when it became out of sync with the master.  When this happens, we usually rely upon Percona’s XTRABackup utility which allows us to make a hot/online backup of the master to use for loading the slave.

In the old days we had to rely upon a third-party tool called ibbackup, or InnoDB Hot Backup utility to do this task. In many ways XTRABackup is a replacement for this tool and has in fact surpassed the ibbackup utility in features and function.

The most efficient way we have found to transfer that data to the slave is the use of the netcat utility.

We also use the screen command since we expect this could take quite some time and don’t want to take the chance that a network connection issue, or a dropped VPN, …

Sometimes you encounter a server with multiple tables of a particular storage engine which you need to convert to another storage engine.  For us, this often happens when we find systems running MyISAM and we want to get these over to InnoDB.

There are a number of reasons to consider converting a table to another storage engine, such as performance, gaining additional features such as Foreign Keys, and so on.  You should, however, stop to consider that not all storage engines are created the same and do not offer the same features.

If there are hundreds of tables, the process can be very time consuming so we put together a simple bash script to automate this process.

#!/bin/sh

MY_USER="root"
MY_PASSWORD="mypassword"
MY_HOST="127.0.0.1"
MY_PORT=3306
NEW_ENGINE="InnoDB"

TABLES=`mysql -u$MY_USER -p$MY_PASSWORD -h$MY_HOST -P$MY_PORT -e"SELECT CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) AS 'TABLE' FROM …

Hi Keith, Kindly please share the demo kit application. It will helps us in testing and validation of our MySQL CGE edition setup.