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 …

When writing data to disk, for small transactions the cost of writing the commit out do disk dominates the execution time of the script. In order to show that, I wrote a little bit of Python.

The script creates a test table in a database and writes 10.000 rows of test data into it, in commit sizes of 1, 2, 4, …, 1024 rows.

$ ./mysql.py --help
Usage: mysql.py [OPTIONS] COMMAND [ARGS]...

  Test commit sizes.

Options:
  --help  Show this message and exit.

Commands:
  create    Create the demo table empty.
  drop      Drop the demo table
  fill      Write test records into the demo table.
  truncate  Truncate the demo table.

There is a small driver script to run the test. The driver creates the table, truncates it and will then run the fill command of the script over and over again, with growing commit-sizes. All powers of 2 from 0 to 10 are being tried with 10.000 rows of test data.

The test system has a …

Be sure to register for the July 29th  Oracle MySQL Virtual Event: Upgrading to MySQL 8.0! 


Join MySQL's first virtual conference and discover how upgrading to MySQL 8.0 will improve your application performance.  Agenda for the day:

8:00 – 9:00am PDT         Best Practice Tips | Upgrading to 8.0 (LeFred)

9:00 – 10:00am PDT       MySQL 8.0 Through the Eyes of the MySQL Support Team (Megha)

10:00 – 11:00am PDT     Customer Insights …

So you talk to a database, doing transactions. What happens actually, behind the scenes? Let’s have a look.

There is a test table and we write data into it inside a transaction:

CREATE TABLE t (
  id serial,
  data varbinary(255)
)

START TRANSACTION READ WRITE
INSERT INTO t ( id, data ) VALUES (NULL, RANDOM_BYTES(255))
COMMIT

The MySQL test instance we are talking to is running on a Linux machine, and otherwise idle to make observation easier. Also, we configured it with innodb_use_native_aio = false because observing actual physical asynchronous I/O and attributing it to the statement that caused it is really hard.

Setting things up this way, we can use lsof and strace to see things. But before we do this, let’s set some expectations and establish some background knowledge.

The Log/Data Memory/Storage quadrants

Upper half: Log structures, Lower half: Data …

A new release of MySQL was out on July 13 (8.0.21).
Among all the changes, there are some that I have already tested and that I really appreciate.

Who stopped the MySQL server?

In previous releases, I can already see in the error log file who stopped the MySQL server if this was done through the shutdown statement.
In MySQL 8.0.20:

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql> exit;
Bye
# tail -2 /u01/app/mysql/admin/mysqld2/log/mysqld2.err
2020-07-23T20:39:08.049064+02:00 9 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.20).
2020-07-23T20:39:09.796726+02:00 0 [System] [MY-010910] [Server] /u01/app/mysql/product/mysql-8.0.20/bin/mysqld: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.

In MySQL 8.0.21:

mysql> …

A new release of MySQL was out on July 13 (8.0.21).
Among all the changes, there are some that I have already tested and that I really appreciate.

Who stopped the MySQL server?

In previous releases, I can already see in the error log file who stopped the MySQL server if this was done through the shutdown statement.
In MySQL 8.0.20:

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql> exit;
Bye
# tail -2 /u01/app/mysql/admin/mysqld2/log/mysqld2.err
2020-07-23T20:39:08.049064+02:00 9 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.20).
2020-07-23T20:39:09.796726+02:00 0 [System] [MY-010910] [Server] /u01/app/mysql/product/mysql-8.0.20/bin/mysqld: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.

In MySQL 8.0.21:

mysql> …