In this blog post, we’ll look at what innodb_autoinc_lock_mode is and how it works.

I was recently discussing innodb_autoinc_lock_mode with some colleagues to address issues at a company I was working with.

This variable defines the lock mode to use for generating auto-increment values. The permissible values are 0, 1 or 2 (for “traditional”, “consecutive” or “interleaved” lock mode, respectively). In most cases, this variable is set to the default of 1.

We recommend setting it to 2 when the BINLOG_FORMAT=ROW. With interleaved, INSERT statements don’t use the table-level AUTO-INC lock and multiple statements can execute at the same time. Setting it to 0 or 1 can cause a huge hit in concurrency for certain workloads.

Interleaved (or 2) is the fastest and most scalable lock mode, but it is not safe if using STATEMENT-based replication or recovery scenarios when SQL …

As we know, replication is only supported officially between consecutive major MySQL versions, and only from a lower version master to a higher version slave.

This means for example, the following scenario is supported:
5.6 master –> 5.7 slave

while these two scenarios are not supported:
5.5 master –> 5.7 slave
5.7 master –> 5.6 slave

That being said, in some contexts (e.g a MySQL upgrade) it can be valuable to be able to replicate from a master that is using a newer version of MySQL to an older version slave.

This could be used as part of a rollback strategy, and/or be needed in the case of upgrading a master-master replication topology.

The idea of the article is to provide you …

As we know, replication is only supported officially between consecutive major MySQL versions, and only from a lower version master to a higher version slave.

This means for example, the following scenario is supported:
5.6 master –> 5.7 slave

while these two scenarios are not supported:
5.5 master –> 5.7 slave
5.7 master –> 5.6 slave

That being said, in some contexts (e.g a MySQL upgrade) it can be valuable to be able to replicate from a master that is using a newer version of MySQL to an older version slave.

This could be used as part of a rollback strategy, and/or be needed in the case of upgrading a master-master replication topology.

The idea of the article is to provide you …

Continuent are pleased to announce the release of Tungsten Replicator and Tungsten Clustering 5.2.0

This release is one of our most exciting new releases for a while, as it contains some significant new features and lays the groundwork for some additional new functionality in the upcoming 5.3.0 and 6.0 releases due later this year.

In particular, this release includes the following new features:

• New replicator filtering environment to make filtering quicker and easier to use, and more flexible
  • New filter configuration standard for new filters
  • New filter to make replication out of a cluster easier
  • New filters for filtering events and data
• New applier for sending Apache Kafka messages directly from an incoming data stream
• New applier for adding incoming records directly to Elasticsearch for indexing
• New …

Recently, we have implemented a set of new commands in ProxySQL Tools to manage MySQL users. You can create, delete, and modify users from the command line. Following commands are available: [crayon-5cbe24b510204552116729/] You can set a user’s password at the time of creation with —-password option, or later on with the set_password sub-command. Create user Let’s add a […]

The post MySQL Users Management Using ProxySQL Tools appeared first on TwinDB.

Recently, my friend Marco Tusa(MySQL Daddy or the Grinch) wrote his first impression on MySQL Group Replication (part of InnoDB Cluster). And his conclusion was not that positive. But when I analyze his setup, I understand that his assumptions were not so right.

Let's try to explain what were the issues and why his test wasn't correct.

Before commenting Marco's tests, I would like to clarify the flow-control implementation in Group Replication:

We designed the flow-control feature in Group Replication as a safety measure for delaying writer nodes when they consistently exceed the write capacity of the Group, so that a large backlog would not make it hard to switch over from a member to another.

Flow-control is …

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

Register Now

In the case of a failure, do you know how long it will take to restore your database? Do you know how old the backup will be? In this presentation, we will cover the basics of best practices for backup, restoration and business continuity. Don’t put your company on the line due to bad data retention and backup policies.

…

Embedding Structured Query Language (SQL) within PHP, or other programming languages, has been problematic for some. Mixing two programming languages together is just plainly not aesthetically pleasing. Especially when you have a declarative language (SQL) mixed with a procedural-object oriented language. But now, with the MySQL XDevAPI PECL extension, PHP developers can now stop mixing the two languages together together. MySQL Document StoreThe MySQL Document Store eliminates the heavy burden for SQL skills. It is designed to be a high speed, schema-less data store and is based on the MySQL JSON data type. This gives you roughly a gigabyte of store in a document format to do with as needed. So you do not need to architect you data before hand when you have no idea how it will evolve. No need to normalize your data. Now behind the scenes …

When you need to work with a database that you are not 100% familiar with, you can be overwhelmed by the hundreds of metrics available. Which ones are the most important? What should I monitor, and why? What patterns in metrics should ring some alarm bells? In this blog post we will try to introduce you to some of the most important metrics to keep an eye on while running MySQL or MariaDB in production.

Com_* status counters

We will start with Com_* counters - those define the number and types of queries that MySQL executes. We are talking here about query types like SELECT, INSERT, UPDATE and many more. It is quite important to keep an eye on those as sudden spikes or unexpected drops may suggest something went wrong in the system.

Our all-inclusive database management system ClusterControl shows you this data related to the most common query types in the “Overview” section.

Handler_* status counters

Error Message:
ERROR 1040 (HY000): Too many connections

Reason for this error:
This error occurs when connection reaches the maximum limit as defined in the configuration file. The variables holding this value is max_connections
To check the current value of this variable, login as root user and run the following command:
show global variables like max_connections;