When we started working with a customer they asked us to setup for them more reliable and performant MySQL database. At the time they were using Percona XtraDB cluster with ELB to balance read traffic between PXC nodes. There was no writer redundancy – if a writer node was down, the app was down. The […]
10 Older Entries »
Among the many New features introduced by MySQL 5.7, we can notice a strong trend towards improving the server security by default. Two features stand out in this respect:
- A password-less root is no longer the default for new
installations. Unless you say otherwise, the default installers
mysqld --initializeand the deprecated
mysql_install_dbwill generate a random password which the user needs to change.
- The anonymous accounts are no longer created by default. When you start MySQL, you only get the root user (and a new one: read on).
The above features are a great advance not only for security but also for usability. The anonymous users were a continuous source of mismatched connections, …[Read more]
MySQL-Docker operations. - Part 1: Getting started
with MySQL in DockerMySQL-Docker operations. - Part 2: Customizing
MySQL in Docker
With the material covered in the first two articles, we have all the elements needed to set up replication in Docker. We just need to put the pieces together.
If you want to do everything by hand, it will only take a few minutes. The steps are not complicated. If you have followed the reasoning in the past episodes, you will know what to do.
Or, you can make your life easier by using the ready-made scripts available in Github as MySQL …
After seeing the basics of deploying a MySQL server in Docker, in this article we will lay the foundations to customising a node and eventually using more than one server, so that we can cover replication in the next one.
Enabling GTID: the dangerous approach.To enable GTID, you need to set five variables in the database server:
For MySQL 5.6, you also need to set log-slave-updates, but we
won't deal with such ancient versions here.
Using the method …
Docker is one of the fastest growing trends in IT. It allows fast
deployment of services and applications on a Linux machine (and,
with some limits, on other operating systems). Compared to other
methods of deploying databases, such as virtual machines or application
isolation, it offers faster operations and better
Many people, surprised by the sudden advance of this technology, keep asking What is Docker? And why you should use it?
I will write soon an article with a deep comparison of the three methods (VM, container, sandbox), but for now, we should be satisfied with a few basic facts:
- Docker is a Linux container. It deploys every application as a series of binary …
During the PerconaLive conference in Amsterdam, I attended a
session where I heard a good piece of advice about using GTID. It
amounts to: look at SHOW SLAVE STATUS output, and if you see more
than one line in the Executed_Gtid_Set field, this tells you
immediately if someone has written on a slave database.
This is good advice. Let's dissect it. Here is what a regular slave looks like, when nobody has messed up with it:
SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_SQL_Running_State: Slave has read all relay log; waiting for more …
We remember when we first started auditing MySQL servers, there were very few tools available. In one of our early big gigs, we were battling serious performance issues for a client. At the time, tuning-primer.sh was about the only tool available that could be used to diagnose performance bottlenecks. Fortunately, with a lot of manual interpolation of the raw data it presented, we were able to find the issue with the server and suggest how to resolve them. For that we are very thankful. It was a first step in analyzing MySQL status variables, minimizing the number of formulas to learn and calculate by hand. Obviously doing it by hand takes forever!
Now fast-forward to today. Unfortunately, not much has changed. Many DBAs and developers are still using open source tools such as tuning-primer, mysqltuner.pl, mysqlreport, and so on. Don’t get the wrong; those tools have …[Read more]
Ever get called out for a MySQL issue only to realize that there was no issue? It was a false alarm from the monitor. We sure have and it’s frustrating, especially at 3:00 or 4:00 in the morning!
Many DBAs work in an environment where there is some sort of first level support that gets assigned tickets first. Unfortunately, many of the times these groups are, shall we say, less than skilled in MySQL. As a result, they quickly escalate the ticket onto the primary on-call DBA, even when there is really nothing wrong.
Much of the time, there are multiple types of MySQL topology in these environments: standalone, galera cluster, replication, etc. Writing large runbooks with detailed test cases can be a daunting process and one that will cause many first-level support engineers to give up and simply escalate the issue anyway.
In an effort to avoid undue call outs, we developed a simple bash …[Read more]
This document outlines 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.
Assuming you are loading into InnoDB tables (and you should probably be doing so), you will want to ensure that MySQL is properly performance tuned for loading large amounts of data. Out of the box MySQL configuration is rarely sufficient for performance with MySQL. It is essential that the InnoDB settings, in particular, be set properly.
First of all, consider the InnoDB Buffer Pool. If you are doing a one-time load, it may be a good idea to configure this as large as possible. In fact, we sometimes set this to approximately 90% of the available RAM on the system for the load. This can then be dropped to between 70 and 80% for …[Read more]
In order to simplify the configuration of MySQL for standalone nodes, clusters, and replication configurations, we decided it would be easiest to maintain a common my.cnf file. We have to admit, the idea wasn’t ours; we picked the idea up from www.fromdual.com and thought it was such a great idea, we decided to implement it as well.
Below is our version of a standardized my.cnf implementing
several of our best practices. We hope it will be of
benefit to you.
######################################################################################################## # my.cnf (Configuration file for MySQL) # # Provided by Itchy Ninja Software to implement general best practices for MySQL regardless of server # type. We chose a single file instead of maintaining multiple versions of the configuration file. # Based upon http://www.fromdual.com/mysql-configuration-file-sample # # Sections are included for Percona XTRADB Cluster …[Read more]
10 Older Entries »