Showing entries 1 to 10 of 177
10 Older Entries »
Displaying posts with tag: Technical Track (reset)
How to Manage Multiple MySQL Binary Installations with SYSTEMD

This blog will go into how to manage multiple MySQL binary installations with SYSTEMD using the systemctl command.  With package installations of MySQL using YUM or APT, it’s quick and easy to manage your server’s state by executing systemctl commands to stop, start, restart, and status.  But what do you do when you want to install MySQL using the binary installation with a single or with multiple MySQL instances? You can still use SYSTEMD to easily manage the MySQL instances. All commands and testing have been done on Debian, and some details may change in other distro’s.

MySQL preparation

These are the steps to set up MySQL with multiple instances. If you currently have a MySQL server package installation using YUM or APT, it will need to be removed first. Make sure you keep your client. I also had to install some base packages for MySQL on Debian

apt install libaio1 libaio-dev numactl

[Read more]
Setting up MySQL Encrypted Replication on MySQL 5.7 with GTID

In this blog post, I’ll walk you through setting up encrypted replication on MySQL 5.7 with GTID enabled. I will walk you through how to create sample certificates and keys, and then configure MySQL to only use replication via an encrypted SSL tunnel.

For simplicity, the credentials and certificates I used in this tutorial are very basic. I would suggest, of course, you use stronger passwords and accounts.

Let’s get started.

Create a folder where you will keep the certificates and keys

mkdir /etc/newcerts/
cd /etc/newcerts/

Create CA certificate

[root@po-mysql2 newcerts]# openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus
e is 65537 (0x10001)
[root@po-mysql2 newcerts]# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem
You are about to be asked to enter …
[Read more]
The Night of the Living Files

Scary movies for teenagers and database administration tasks share some common traits. Usually, the movie starts with a group of teens making unwise choices. Maybe you, as a DBA, are no longer a teenager, but you shouldn’t underestimate your ability to do the same.

$ sudo su -
Darrera entrada: ds jun 2 14:43:53 UTC 2018 des de a a pts/0
[root@mysql ~]# cd /var/lib/mysql
[root@mysql mysql]# df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00 38G 35G 3G 92% /
[root@mysql mysql]#

Back to the movie…today is just another regular day. Checking Jira (or whatever your ticketing system is), answering emails and phone calls. Maybe a couple of meetings. And in the meantime, you will perform some critical tasks on the most important systems for your company. Just the usual stuff!

[root@mysql mysql]# ls -l *.log
ls: cannot access *.log: No such file or directory
[root@mysql mysql]# ls -l *log*
-rw-r-----. 1 …
[Read more]
Log Buffer #548: A Carnival of the Vanities for DBAs

This Log Buffer Edition covers blog posts from Cloud, Oracle, and MySQL.


Google Stackdriver lets you track your cloud-powered applications with monitoring, logging and diagnostics. Using Stackdriver to monitor Google Cloud Platform (GCP) or Amazon Web Services (AWS) projects has many advantages—you can get detailed performance data and can set up tailored alerts.

This post is courtesy of Sam Dengler, AWS Solutions Architect. Message brokers can be used to solve a number of needs in enterprise architectures, including managing workload queues and broadcasting messages to a number of subscribers.

New Cloud …

[Read more]
MySQL Swapping With Fsync

One problem that’s a lot less common these days is swapping. Most of the issues that cause swapping with MySQL have been nailed down to several different key configuration points, either in the OS or MySQL, or issues like the swap insanity issue documented by Jeremy Cole back in 2010. As such, it’s usually pretty easy to resolve these issues and keep MySQL out of swap space. Recently, however, we had tried all of the usual tricks but had an issue where MySQL was still swapping.

The server with the issue was a VM running with a single CPU socket (multiple cores), so we knew it wasn’t NUMA. Swappiness and MySQL were both configured correctly and when you checked the output of free -m it showed 4735M of memory available.

[sylvester@host~]$ free -m
              total        used        free      shared  buff/cache …
[Read more]
Encrypting an existing MySQL/MariaDB RDS Instance

Often it is necessary to convert an unencrypted RDS instance into an encrypted one. And it is usually expected that this process is done with minimum or no downtime. Unfortunately, one can only enable encryption when the instance is created. However, there is still hope, as there are a couple of workarounds to encrypt your existing data.

In this article, I will discuss two different solutions to achieve this result.

Solution 1: Create a snapshot and copy the snapshot to a new encrypted snapshot:

  • Create a manual snapshot of the unencrypted RDS instance
  • Go to Snapshots from the left panel and choose the snapshot just created
  • From the Actions, choose Copy snapshot option and enable encryption
  • Select the new encrypted snapshot
  • Go to Actions and …
[Read more]
Interesting happenstance when installing Ansible dependencies in a MySQL Docker container

I’ve been posting quite a bit about Docker as I’ve been working with it a lot as of late. I thought I would share something interesting I discovered a couple weeks ago while working on setting up a Docker container-based lab environment for training purposes here at Pythian, specifically when attempting to install the MySQL-python package inside the container.

I know what you’re thinking: why is he trying to install a package in a Docker container? Doesn’t that go against the “Docker run and forget about it” philosophy? Sure, but in this case, I’m looking to add orchestration via ansible, which I don’t think is completely out of the question in order to coordinate your Docker containers for something like replication. This requires using the …

[Read more]
Getting started with Orchestrator

Orchestrator is a MySQL high availability and replication management tool. In this blog post, we will cover the first steps for getting started with it on an existing topology.

The code examples assume you are running Centos 7, but the general steps should be similar if you are running other operating system versions/flavors.

Prep work

1. Create a MySQL user on each of your database servers.
Orchestrator will connect with this user to discover the topology and to perform any changes you tell it to make.

CREATE USER 'orchestrator'@'%' IDENTIFIED BY '****';
GRANT SELECT ON mysql.slave_master_info TO 'orchestrator'@'%';
GRANT SELECT ON meta.* TO 'orchestrator'@'%';

Note: Orchestrator reads replication credentials stored in mysql.slave_master_info table, which implies you need to set up your servers with master_info_repository = …

[Read more]
Pythian at DataOps Barcelona

DataOps Barcelona is an event organized by Binlogic and will be taking place June 21-22, 2018 in Barcelona, Spain. Pythian is proud to be supporting this inaugural open source-focused conference in Europe!

DataOps is of course short for “Database + Operations” and contains talks about the various open-source database ecosystems and how they are operationalized with regards to cloud solutions, security, automation, high availability and scaling.

Pythian has a long history of helping companies leverage open source technologies and cloud infrastructure to meet their business requirements. We excel at managing traditional open source relational database (MySQL) and relatively new NoSQL technologies (MongoDB, Cassandra).

Our …

[Read more]
HAProxy MySQL Lag Awareness via systemd

In one of the projects I have been working on, one requirement was the ability to stop traffic from reaching a MySQL host which has been lagging behind its master for longer than a specific amount of time and then bring it back online once the lag has gone away. Of course, this is all automated and no human intervention is required.

In this scenario, we are using HAProxy as the load balancer, and I will walk you through how to configure an agent so we can use HAProxy httpchk to flag the host as up or down, via systemd socket and then automatically set the host as being down/up when applicable, in HAProxy.

I will be setting up a systemd service (I’m running centos7 hosts) and creating a listening socket in the MySQL host we want to monitor so haproxy can have access to replication status.


master: po-mysql1
slaves: po-mysql2, po-mysql3, po-mysql4
secondary slaves: …

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