Showing entries 1 to 10 of 37984
10 Older Entries »
What is MySQL Partitioning?

In this blog, we’ll quickly look at MySQL partitioning.

Partitioning is a way in which a database (MySQL in this case) splits its actual data down into separate tables, but still get treated as a single table by the SQL layer.

When partitioning, it’s a good idea to find a natural partition key. You want to ensure that table lookups go to the correct partition or group of partitions. This means that all SELECT, UPDATE, DELETE should include that column in the WHERE clause. Otherwise, the storage engine does a scatter-gather, and queries ALL partitions in a UNION that is not concurrent.

Generally, you must add the partition key into the primary key along with the auto increment, i.e., PRIMARY KEY (part_id,id). If you …

[Read more]
MySQL Group Replication: who is the primary master – updated!

Some time ago I wrote this article explaining how to find which node was the Primary Master in a MySQL Group Replication cluster running in Single-Primary Mode.

In the latest release of Group Replication, MySQL 8.0.2 dmr, Jaideep improved the visibility of Group Replication extending the performance_schema tables (see his article).

Thanks to these improvements, it’s now very easy to find which host is acting a Primary-Master. This is the query you can use:

mysql-sql> SELECT MEMBER_HOST as `PRIMARY` 
           FROM performance_schema.replication_group_members 
           WHERE MEMBER_ROLE='PRIMARY';
+---------+
| PRIMARY |
+---------+
| mysql3  |
+---------+
1 row in set (0.00 sec) …
[Read more]
https://t.co/cOsoUYcYGU Great post on on using kitchen to test infrastructure @cevo @stevemac

https://t.co/cOsoUYcYGU #devops Great post on on using kitchen to test infrastructure @cevo @stevemac

— Trent Hornibrook (@mysqldbahelp) July 26, 2017
Source: @mysqldbahelp July 27, 2017 at 08:48AM
More info Your Website/Page Anchor Text Here

High Availability with Multi-Source Replication in MariaDB Server

High Availability with Multi-Source Replication in MariaDB Server gerrynarvaja Wed, 07/26/2017 - 19:29

Multi-source replication is a handy way to simplify your high availability database setup – especially as compared to the regular MySQL replication approach. Let's look at how it works.

(Note: This post is a revision of one from 2014, with minor updates made.)

The Basics

For our example scenario, we'll use a three-server topology, which is commonly employed for simple failover. (I used the same setup in my post on how to enable GTIDs in MariaDB Server.) A is the active master, B is a standby master set up to replicate from A, and C is a multi-purpose slave replicating from A. I also set up A to replicate from B. This way, if A fails, the system will start writing to B, and once A comes …

[Read more]
Enabling GTIDs for Server Replication in MariaDB Server 10.2

Enabling GTIDs for Server Replication in MariaDB Server 10.2 gerrynarvaja Wed, 07/26/2017 - 18:56

I originally wrote this post in 2014, after the release of MariaDB Server 10.0. Most of what was in that original post still applies, but I've made some tweaks and updates since replication and high availability (HA) remain among the most popular MariaDB/MySQL features.

Replication first appeared on the MySQL scene more than a decade ago, and as replication implementations became more complex over time, some limitations of MySQL’s original replication mechanisms started to surface. To address those limitations, MySQL v5.6 introduced the concept of global transaction identifiers (GTIDs), which enable some advanced replication features. MySQL DBAs were happy with this, but complained that in order to implement GTIDs you needed to stop all the servers in the replication group and restart them with the feature enabled. There are …

[Read more]
MariaDB Galera Cluster 5.5.57 and Connector/C 3.0.2 now available

The MariaDB project is pleased to announce the availability of MariaDB Galera Cluster 5.5.57 as well as MariaDB Connector/C 3.0.2. See the release notes and changelogs for details. Download MariaDB Galera Cluster 5.5.57 Release Notes Changelog What is MariaDB Galera Cluster? MariaDB APT and YUM Repository Configuration Generator Download MariaDB Connector/C 3.0.2 Release Notes Changelog […]

The post MariaDB Galera Cluster 5.5.57 and Connector/C 3.0.2 now available appeared first on MariaDB.org.

What is innodb_autoinc_lock_mode and why should I care?

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 …

[Read more]
Replicating from a higher to lower MySQL version

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 …

[Read more]
Tungsten Replicator and Clustering 5.2.0 Released

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 …
[Read more]
DevOps Considerations for Production-ready Database Deployments

MySQL is easy to install and use, it has always been popular with developers and system administrators. On the other hand, deploying a production-ready MySQL environment for a business-critical enterprise workload is a different story. It can be a bit of a challenge, and requires in-depth knowledge of the database. In this blog post, we’ll discuss some of the steps which have to be taken before we can consider our MySQL deployment production-ready.

High Availability

If you belong to those lucky ones who can accept hours of downtime, you can stop reading here and skip to the next paragraph. For 99.999% of business-critical systems, it would not be acceptable. Therefore a production-ready deployment has to include high availability measures. Automated failover of the database instances, as well as a proxy layer which detects changes in topology and state of MySQL and routes traffic accordingly, would be a main requirement. There …

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