You may have heard about the term “failover” in the context of MySQL replication.

Maybe you wondered what it is as you are starting your adventure with databases.

Maybe you know what it is but you are not sure about potential problems related to it and how they can be solved?

In this blog post we will try to give you an introduction to failover handling in MySQL & MariaDB.

We will discuss what the failover is, why it is unavoidable, what the difference is between failover and switchover. We will discuss the failover process in the most generic form. We will also touch a bit on different issues that you will have to deal with in relation to the failover process.

What does “failover” mean?

MySQL replication is a collective of nodes, each of them may serve one role at …

Introduction –

              MySQL 8.0.10 GPL came out with more changes and advanced features. We have changes on InnoDB physical file layout ( MySQL data directory ) too. This blog will provide the information about the MySQL 8 InnoDB physical files.

MySQL system tables are completely InnoDB now ?

              Yes, Previously, we don’t have too many InnoDB tables on MySQL system tables. We have the innodb_index_stats, innodb_table_stats, slave_master_info, slave_relay_log_info and slave_worker_info in MySQL 5.7 as InnoDB tables. But, now all the MySQL System  tables were converted to InnoDB from MySQL 8.0 . It helps in the transactional DDL’s .

Below are the list of InnoDB physical files on MySQL 8.0 .

• ibdata1
• .ibd …

Join MC Brown, VP Products at Continuent, on August 8th for our new webinar on high availability and disaster recovery for MySQL, MariaDB and Percona Server with Continuent Clustering. Learn how Bluefin Payment Systems provides 24/7/365 operation and application availability for their PayConex payment gateway and Decryptx decryption-as-a-service, essential for Point-Of-Sale solutions in retail, mobile, call centers and kiosks.

We’ll discuss why Bluefin uses Continuent Clustering, and how Bluefin runs two co-located data centers with multimaster replication between each cluster in each data center, with full fail-over within the cluster and between clusters, handling 350 million records each month.

Tune in …

The choice of good InnoDB primary keys is a critical performance tuning decision. This post will guide you through the steps of choosing the best primary key depending on your workload.

As a principal architect at Percona, one of my main duties is to tune customer databases. There are many aspects related to performance tuning which make the job complex and very interesting. In this post, I want to discuss one of the most important one: the choice of good InnoDB primary keys. You would be surprised how many times I had to explain the importance of primary keys and how many debates I had around the topic as often people have preconceived ideas that translate into doing things a certain way without further thinking.

The choice of a good primary key for an InnoDB table is extremely important and can have huge performance impacts. When you start working with a customer using an overloaded x1.16xlarge RDS instance, with close …

Everyone should have a disaster recovery plan for MySQL & MariaDB!

Watch the replay of our webinar with Vinay Joosery, CEO at Severalnines, on Disaster Recovery Planning for MySQL & MariaDB with ClusterControl; especially if you’re unsure about RTO and RPO or whether you should have a secondary datacenter, or are concerned about disaster recovery in the cloud …

Organizations need an appropriate disaster recovery plan in order to mitigate the impact of downtime. But how much should a business invest? Designing a highly available system comes at a cost, and not all businesses and certainly not all applications need five 9s availability; though several 9s are always good to have ;-)

In this webinar, Vinay explains key disaster recovery concepts and walks us through the relevant options from …

Hello Moll,
I have a serious issue with one of my logs table.
The table is increasing with a pace of almost 50k records per day.

The problem is while generating reports and the query always times out or takes forever to load.

What I have in place :

– All the indexes are in place.
– A replication DB from where the reports are generated.
– Because of this issue; I started Archiving the table and now the primary table contains data of the last 30 days only (Now report has only last 30 days data). That still doesnt work.

I thought of sharding but the table has a foreign key which is used while generating the reports.

Can you help me with an efficient solution such that I need not archive the past data and also the query works smoothly.

…

In our previous blog, we have explained that to configure external replica for a CloudSQL instance. Here we are going to see how to configure external master for CloudSQL. Unlike native replication or the method which we explained in our previous blog, setting up external master for CloudSQL is pretty different way. Lets see how we can achive this.

Process Overview:

• Launch VM and configure MySQL 5.7(it should be 5.5+).
• Initiate the dump along with the binlog filename and its position.
• Upload the Backup file to Google Storage.
• Enable the CloudSQL Admin API.
• In the CloudSQL console, use migrate data.
• Select the CloudSQL instance type and its network.
• Provide the Master server’s IP …

In this blog post, we talk about the Automatic Reconnect feature in the Tungsten Connector.

Automatic reconnect enables the Connector to re-establish a connection in the event of a transient failure. Under specific circumstances, the Connector will also retry the query.

Connector automatic reconnect is enabled by default in Proxy and Smartscale modes.

Use the following tpm command option on the command line (remove the leading hyphens inside INI files):

--connector-autoreconnect=false

to disable automatic reconnect.

This feature is not available while running in Bridge Mode. Use the tpm command option --connector-bridge-mode=false to disable Bridge mode.

Automatic reconnect enables retries of statements under the following circumstances:

• not in bridge mode
• not inside a transaction
• no temp table has been created
…

CloudSQL is fully managed MySQL / PostgreSQL database system. CloudSQL reduces the workload for DBAs and anyone can easily manage even without a DBA. In many cases, people wants to have a replica of their production database for Testing purpose or even some other purpose. CloudSQL provides the flexibility to have external replicas on VM or On-prem. In this blog we are going to configure external replica for CloudSQL.

MySQL with GTID:

CloudSQL 2nd generation support GTID based replication and binlog file based replication. But GTID is more consistent. But 1'st generation only support binlog replication.

A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup. There is a 1-to-1 mapping between …

Today I'll share a few slides I prepared last year for a presentation delivered at Oracle Open World. This is a quick and easy hands-on lab for fresh MySQL Server DBAs. I chose 5 among the most relevant topics when tuning and scaling a MySQL Server using InnoDB tables.

In particular, in this hands-on, I will talk about:

• Scaling connections
• The threads model
• InnoDB REDO log
• InnoDB Buffer Pool
• The Execution plan

MySQL Performance Tuning 101 from Mirko Ortensi

Rate at which MySQL is delivering new features and improvements is impressive, in fact MySQL Server 8 boosts performance in many fields, especially regarding …