You have a MySQL instance? Great. You want to take it to a cloud? Nothing new. You want to do it fast, minimizing downtime / service outage? “I wish” I hear you say. Pull up a chair. Let’s have a chinwag. Given the objective above, i.e. “I have a database server on premise and I want the data in the...

First of all, pt-table-checksum is perfect for checking if the master and its slaves are in-sync. It answers the question “Are the slaves consistent with the master?”. However, if they’re not, pt-table-checksum doesn’t actually tell you what exactly is wrong.

master# pt-table-checksum -q
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
09-03T22:21:10 0 1 2 1 0 0.013 mysql.proxies_priv
09-03T22:21:10 0 1 9 1 0 0.010 mysql.user

From pt-table-checksum to pt-table-sync

Nevertheless, pt-table-sync may give you a partial answer. It can print SQL statements to sync the replication cluster. Thus, reading the SQL code, you may guess what records were missing/extra or differ.

master# pt-table-sync --print --replicate percona.checksums localhost
DELETE FROM `mysql`.`proxies_priv` WHERE `host`='slave.box' AND `user`='root' AND `proxied_host`='' AND …

ProxySQL participará en la próxima edición del evento open source “ESLIBRE”, que tendrá lugar los días 18th & 19th de Septiembre. La fecha coincidirá con el “Software Freedom Day”, día que celebra el Free Software en el mundo entero.

El desarrollador senior de ProxySQL, Javier Jaramago Fernández, expondrá “Introducción a ProxySQL”, haciendo hincapié en la importancia de limitar el downtime tanto como sea posible, además de, exponer que otras herramientas ofrece ProxySQL y cómo podemos hacer uso de ellas para mejorar nuestra infraestructura.

La charla se centrará en ProxySQL como proyecto, sus características, y la topología básica de su uso.
Se expondrán ejemplos en tiempo real de cómo usar estas características, como:

• Cambios de configuración con Zero-downtime – ProxySQL posee tres capas de …

ProxySQL will be taking part in the upcoming EsLibre, open source event on the 18th & 19th of September. The date also coincides with ‘Software Freedom Day’ which celebrates Free Software, all over the world.

ProxySQL’s Senior Developer, Javier Jaramango Fernandez, will be discussing ‘Introduction to ProxySQL’, focusing on the importance of limiting downtime as much as possible, as well as, the tools ProxySQL offers and how you can use them to improve your infrastructure.

The talk will focus on the introduction of ProxySQL as a project, its characteristics, and the basic topology for its use.
Real-time examples and how to use them will also be discussed, such as:

• Zero-downtime configuration changes – ProxySQL offers three configuration layers: memory, disk and runtime. Each of these layers can be configured dynamically, with …

These are the slides of the “Efficient Use Of Indexes In MySQL” talk we delivered on the SFMySQL Meetup.

This is an introductory talk for developers on MySQL indexes. In my opinion, it’s quite important to understand how InnoDB organizes data. If you know how MySQL accesses data, it’s easier to write optimal queries.

When working with queries, I imagine secondary indexes as a table with records sorted by secondary key fields. This is a powerful concept that helps to understand the MySQL logic. It’s also easy to understand complex optimizations like loose index scan.

For example, for index (last_name, rank) the secondary index table looks like:

Enjoy the slides!

…

As a Database Engineer in Mydbops. We tend to solve multiple complex problems for our esteemed customers. To control the System resources and scale up /down based on needed we are evaluating Dockers and Kubernetes.

Docker is a set of platform as a service products that uses OS-level virtualization to deliver software in packages called Containers. Containers are isolated from one another and bundle their own software, libraries and configuration files; they can communicate with each other through well-defined channels.It’s more lightweight than standard Containers and boots up in seconds.

Docker also is easy to use when you need a simple, single instance. What is great about Docker though is that it allows configuring multiple versions of MySQL.

Docker Installation:

Docker can be installed with yum repository or apt-get repository based on your linux …

Why Does InnoDB Need A Dictionary

An InnoDB dictionary is a set of internal tables InnoDB uses to maintain various information about user tables. It serves as API between a human and the database. While the humans refer to tables by their names, InnoDB works with integer identifiers. The dictionary stores correspondence between table name and index_id.

The dictionary tables are normal InnoDB tables, but they’re not visible to a user. However, some versions of MySQL provide read-only access to the dictionary in information_schema database.

The dictionary is stored in ibdata1. Root page of SYS_TABLES, for example, has id 8, so it’s the eighth page from the beginning of ibdata1.

The dictionary pages are in REDUNDANT format even if you use MySQL 5.6. I hope to write more about record formats in future posts. For now, it’s enough to mention that REDUNDANT is the oldest record format. It was available since 4.0 …

In a previous blog post on Data Consistency for RDS for MySQL, we presented a workaround to manage run pt-table-checksum on RDS instances. However, if your instance is running a MySQL 8.0.X version, there’s a simpler way to check data consistency.

Starting with 8.0.1, MySQL introduced something called “Dynamic Privileges” which is a solution to grant more granulated privileges to the users, instead of the almighty SUPER privilege.

So what was the issue with pt-table-checksum and RDS again? Since there’s no SUPER privileges for any user, there was no way for the tool to change the binlog_format to STATEMENT… but not anymore.

The solution when using 8.0 is …

One of the questions you often will be faced with operating a Linux-based system is managing memory budget. If a program uses more memory than available you may get swapping to happen, oftentimes with a terrible performance impact, or have Out of Memory (OOM) Killer activated, killing process altogether.

Before adjusting memory usage, either by configuration, optimization, or just managing the load, it helps to know how much memory a given program really uses.

If your system runs essentially a single user program (there is always a bunch of system processes) it is easy.  For example, if I run a dedicated MySQL server on a system with 128GB of RAM I can use “used” as a good proxy of what is used and “available” as what can still be used.

root@rocky:/mnt/data2/mysql# free -h …

Watch the replay of our Geo-Scale MySQL webinar: Guaranteed Global Game Access for Hundreds of Millions of Players Worldwide - Cloud-Based Active/Passive Tungsten MySQL Clusters @ Riot Games.

Tags:  Webinar MySQL use case tungsten clustering mysql cluster geo-scale geo-distributed MySQL Riot Games …