MySQL Server generates several logs that can help you monitor the activities of the server. However, once these logs are enabled, they can grow in size and start taking up too much disk space. This is why it’s important to have an automated way of archiving and preserving MySQL log files for a certain duration, as well as deleting the old ones. In this blog post, we describe some best practices for setting up and managing MySQL error logs, general logs and slow query logs for your MySQL deployments.

Setting Up MySQL Server Logging

Let’s look at how to setup the following 3 types of logs:

Error Log

Logs all the problems encountered during starting, running, or stopping mysqld. This log can be enabled by having the following option in /etc/my.cnf file:

…

“Hey, what’s going on with my applications? I installed a newer version of MySQL. I have queries that perfectly run with the older version and now I have a lot of errors.”

This is a question some customers have asked me after upgrading MySQL. In this article, we’ll see what one of the most frequent causes of this issue is, and how to solve it.

We are talking about this error:

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause 
and contains nonaggregated column 'test.web_log.user_id' which is not functionally 
dependent on columns in GROUP BY clause; this is incompatible 
with sql_mode=only_full_group_by

Have you ever seen it?

SQL_MODE

As the first thing let me introduce the concept of SQL_MODE.

MySQL can work using different SQL modes that affect the syntax of the queries and validation checks. Based on the configured value of the …

It’s not long now before we’ll be gathering in Austin TX for this year’s Percona Live event. It’s the only conference of its type dedicated entirely to open source databases – of all marques. From a community perspective, we have a few updates to share.

Community Dinner

We’re happy to say that PlanetScale have stepped up to the plate (!) and are sponsoring this year’s community dinner alongside Percona. This year we are taking to the water and consequently numbers are limited to 100 attendees. If you are keen to join us, be sure to not miss the boat.  😉

The speakers’ perspective

For speakers who also enjoy writing, there’s an …

In a previous post I showed that by default when authentication_ldap_simple communicates with a Windows Domain Controller (or any other LDAP service), then the password is transmitted unencrypted during authentication.

This time I’ll demonstrate how to close this loophole.  A pre-requisite is that the Domain Controller needs to be configured to accept secure connections. This is done by installing a certificate, the process is well documented elsewhere so I won’t repeat it here.

There are two different ways to configure secure communication.  The first method is to set  …

Thumb rule to choose Percona-Server ssl Linux-Generic package

Here below are my thumb rules to choose which ssl linux-generic package, you should download for your system.

Like here we can see if we need to download Percona-Server Linux-Generic package then We can see here we have couple of packages available with different ssl(100/101/102) versions.

https://www.percona.com/downloads/Percona-Server-5.7/LATEST/binary/tarball/

Here you should choose below package based on your operating system.

Debian …

PORP LAB : ProxySQL/Orchestrator/Replication/PMM Summary PORP Lab will create 4 different nodes. Each node will have below packages/applications/db installed.

app
-- Percona Server 5.7
-- Percona Toolkit 
-- Percona XtraBackup 
-- Sysbench 
-- ProxySQL
-- Orchestrator 
-- PMM

mysql1 / mysql2 / mysql3
-- Percona Server 5.7
-- Percona Toolkit
-- pmm-client
-- Replication

PORP LAB have ProxySQL,Orchestrator and PMM properly configured, we can just create this lab and use it.

Install VirtualBox

Version 5.2.22 or Later

Install Vagrant

Version 2.2.2 or Later

Install Vagrant plugin hostmanager

vagrant plugin install vagrant-hostmanager

Update Vagrant Plugin

vagrant plugin update

Clone the repo

git clone …

We would like to remind that you can find MySQL at OpenSource Day, Poland in Warsaw next Tuesday, May 14. Please find details below:

• Name: OpenSource Day
• Date: Tue, May 14, 2019
• Place: Warsaw, Poland
• MySQL Session: "MySQL InnoDB Cluster: High Availability with no stress!" given by Vittorio Cioe, the MySQL Senior Sales Consultant

This year we are sharing our resources with our friends from Oracle Linux team. You can find the Linux session just after MySQL one in the online agenda. We are also going to share the booth in expo area, so if you have any queries for either MySQL or Linux products, do not hesitate to come to talk to us to our booth in expo area! We are looking forward to talking to you in Warsaw!

Master has purged binary logs containing GTIDs that the slave requires Problem :  Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

Solution : stop slave;
Query OK, 0 rows affected (0.00 sec)

CHANGE MASTER TO MASTER_AUTO_POSITION = 0;
Query OK, 0 rows affected (0.01 sec)

start slave;
Query OK, 0 rows affected (0.00 sec)

At the end of the month, some engineers of the MySQL Team will be present in Austin, TX !

We will attend the first edition of Percona Live USA in Texas.

During that show, you will have the chance to meet key engineers, product managers, as well as Dave and myself.

Let me present you the Team that will be present during the conference:

The week will start with the MySQL InnoDB Cluster full day tutorial by Kenny and myself. This tutorial is a full hands-on tutorial where we will start by migrating a classical asynchronous master-replicas topology to a new MySQL InnoDB Cluster. We will then experience …

Overview The Skinny

From time to time we are asked how to check whether or not there are data discrepancies between Master/Slave nodes within a MySQL (or MariaDB) cluster that’s managed with Tungsten Clustering. This is always a challenging task, not least because we hope and believe that our replication mechanism would avoid such occurrences, that said there can be factors outside of our control that can appear to “corrupt” data – such as inadvertent execution of DML against a slave using a root level user account.

Tungsten Replicator, the core replication component in our Tungsten Clustering solution for MySQL (& MariaDB), is just that, a replicator – it takes transactions from the binary logs and replicates them around. The replicator isn’t a data synchronisation tool in that respect, the …