I wanted to be brave and I installed MySQL Group Replication manually…. it was painful !

Then I realized that managing those servers and especially deal with MySQL Routers was even more painful !

What are my options now ? Is there a solution or do I need to restart from scratch ?

Asking the answer is already answering it… and once again MySQL Shell at the rescue.

MySQL Group Replication

I’ve configured everything manually. I also loaded group_replication and clone plugins and finally after having bootstrapped my Group here is what I have:

mysql> select member_host, member_port port, member_state state, 
       member_role role, member_version version 
       from performance_schema.replication_group_members;
+-------------+------+--------+-----------+---------+
| member_host | port | state  | role      | version | …

After having a look how MySQL handles transactions physically, let’s have a look at what is going on from a logical point of view.

We are using a test table called demo with an id and a counter field, both integer. In it we have 10 counters, all set to 0.

CREATE TABLE `demo` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `counter` int NOT NULL DEFAULT '0',
  UNIQUE KEY `id` (`id`)
)
INSERT INTO `demo` VALUES (1,0);
INSERT INTO `demo` VALUES (2,0);
...
INSERT INTO `demo` VALUES (10,0);

In one session, we start a transaction and modify a counter value. We do not commit anything.

Session1> start transaction read write;
Session1> update demo set counter = 10 where id = 3;

Isolation

In a second session, we check the data and notice a few things:

Session2> select …

Introduction In this article, we are going to see how we can implement an audit logging mechanism using MySQL database triggers to store the old and new row states in JSON column types. Database tables Let’s assume we have a library application that has the following two tables: The book table stores all the books that are found in our library, and the book_audit_log table stores the CDC (Change Data Capture) events that happened to a given book record via an INSERT, UPDATE, or DELETE DML statement. The book_audit_log table is created... Read More

The post MySQL audit logging using triggers appeared first on Vlad Mihalcea.

We all know as SQL professionals that the NULL marker is a special case. Oftentimes, you have NULL‘s stored in your tables, and that’s fine. Suppose you want to present an alternative value in query results where some of the columns have NULL? This is a perfect opportunity (but not the only) to use the IFNULL() function. I find IFNULL() quite useful when exporting query results to a CSV file or other type of flat file, providing something more meaningful than the NULL word itself. However, you only have one substitute for the NULL value when using IFNULL(), so keep that in mind. Continue reading and see examples using IFNULL() in SELECT queries…

Photo by …

Sometimes, you just half to chuckle. A couple folks felt that I didn’t give enough information in my post showing how to configure a small Node.js application that could access a MySQL database. Specifically, they wanted me to explain the following:

1. Configure your Express.js and MySQL development in a single Node.js application.
2. How to convert the list of RowDataPacket objects as elements of data, which is really just simple JavaScript knowledge.
3. How to bind variables into the query.

Like the other blog post, this one assumes you’ve performed a global install of Node.js on a Linux server. If you’re unfamiliar with how to perform a global Node.js installation, I cover how to do it in this …

In my previous Blog i have explained how we can integrate MySQL tools ( Percona Tool kit ) with systemd service, At Mydbops works with multiple clients ,we get various requirements from customers a few may be a complex ones ,one of the requirement was to setup a binlog server and copy the Production binlogs instantly .

To satisfy customer request i have made a simple shell script and integrated in systemd service which does a similar job as of Maxscale Binlog Server ( Pervious blog )

In this blog i will explain how to setup a simple binlog server.

Pre-requites for setting up the Binlog server.

• A standalone remote server with required disk.
• The mysqlclient to be …

I got this question on the “How to Avoid Pitfalls in Schema Upgrade with Percona XtraDB Cluster (PXC)” webinar and wanted to answer it in a separate post.

Will RSU have an effect on GTID consistency if replication PXC cluster to another cluster?

Answer for this: yes and no.

Galera assigns its own GTID for the operations, replicated to all nodes of the cluster. Such operations include DML (

INSERT/UPDATE/DELETE

 ) on InnoDB tables and DDL commands, executed with default TOI method. You can find more details on how GTIDs work in the Percona XtraDB Cluster in this blog post.

However, DDL commands, executed with RSU method, are …

MySQL speaks its own proprietary protocol. It cannot be routed by a HTTP proxy, and a MySQL connection is entire unlike a HTTP connection. Specifically, a lot of state and configuration is tied to a MySQL connection, and it cannot be recovered on disconnect.

What state is tied to a connection?

Transactions

A disconnect implies a ROLLBACK. So if you are in a transaction, all changes to the database that you attempted are lost, rolled back, as if they never happened. It is not enough to retry the last statement, you need to jump back to the begin of the transaction.

This is not unexpected. All transactions can fail, in the middle of a transaction or even on COMMIT (you attempt to COMMIT, but get a ROLLBACK due to a deadlock or a failed transaction certification in Group Replication).

Applications need to be able to detect that and handle that at any …

Last week I received an urgent email asking to move the time of  my performance review up a few hours. And the email sender was not in my management structure and was someone unfamiliar to me. Being a former  Xerox employee, I checked my others emails for notice of a reorganization.  Then I realized the person in question was seeking the other David Stokes at Oracle.  Yup two of us with the same name at the same company. Whew!

Coincidentally MySQL 8.0.21 added a new ability that allows you to store information about an account similar to the way the GCOS field is supposed to be used for in the UNIX/Linux world. Back in time many decades ago, account names were limited in length and the GCOS field was populated with the account's user name, office room number, office phone number, and some other relevant information.  This was the way …

I would say that innodb_rollback_on_timeout is a very important parameter. In this blog, I am going to explain “innodb_rollback_on_timeout” and how it affects the transaction behavior at the MySQL level. I describe two scenarios with practical tests, as it would be helpful to understand this parameter better.

What is innodb_rollback_on_timeout?

The parameter Innodb_rollback_on_timeout will control the behavior of the transaction when a failure occurs with timeout.

• If –innodb-rollback-on-timeout=OFF ( default ) is specified, InnoDB rollbacks only the last statement on a transaction timeout.
• If –innodb-rollback-on-timeout=ON is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction.

Let’s conduct the test with the …