Showing entries 1 to 10 of 41
10 Older Entries »
Displaying posts with tag: best practices (reset)
Simple shell script is bad idea

Internet is full of simple shell backup scripts. Every DBA and system administrators (including myself) has written one. Bash is a bad tool for this job and in this post I will explain why. Let’s find out why the need to write anything arises at first place. There is a lot of great backup tools […]

The post Simple shell script is bad idea appeared first on TwinDB.

dbdeployer GA and semantic versioning

dbdeployer went into release candidate status a few weeks ago. Since then, I added no new features, but a lot of tests. The test suite now runs 3,000+ tests on MacOS and a bit more on Linux, for a grand total of 6,000+ tests that need to run at least twice: once with concurrency enabled and once without. I know that testing can't prove the absence of bugs, but I am satisfied with the results, since all this grinding has allowed me to find several bugs and fix them.

In this framework, I felt that dbdeployer could exit candidate status and get to version 1.0. This happened on March 26th. An immediate side effect of this change is that from this point on, dbdeployer must adhere to the semantic versioning principles:

A version number is made of Major, Minor, and Revision. When changes are applied, the following happens:

[Read more]
MySQL security for real users

Security features overview

One of Oracle's tenets is the focus on security. For this reason, when it took over the stewardship of MySQL, it started addressing the most common issues. It was not quick acting, but we have seen real progress:

  1. MySQL 5.7 has removed the anonymous accounts, which was the greatest threat to security. Because of those accounts, and the default privileges granted to them, users without any privileges could access the "test" database and do serious damage. Additionally, because of the way the privilege engine evaluates accounts, anonymous users could hijack legitimate users, by preventing them to work properly.
  2. The "root" account now comes with a password defined during …
[Read more]
How we use ProxySQL Tools

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 […]

The post How we use ProxySQL Tools appeared first on TwinDB.

Default users in MySQL 5.7

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 --initialize and the deprecated mysql_install_db will 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 3: MySQL replication in Docker

Previous Episodes:

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 …

[Read more]
MySQL-Docker operations. - Part 2: Customizing MySQL in Docker

Previous Episodes:

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:

  • master-info-repository=table
  • relay-log-info-repository=table
  • enforce-gtid-consistency
  • gtid_mode=ON
  • log-bin=mysql-bin

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 …

[Read more]
MySQL-Docker operations. - Part 1: Getting started with MySQL in Docker

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 performance.
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 …
[Read more]
Sound advice for GTID, with caveats

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:

*************************** 1. row ***************************
Master_Server_Id: 1
Master_UUID: 00013454-1111-1111-1111-111111111111
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more …
[Read more]
Advanced MySQL Server Auditing

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, 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,, mysqlreport, and so on.  Don’t get the wrong; those tools have …

[Read more]
Showing entries 1 to 10 of 41
10 Older Entries »