Personal blog of Yzmir Ramirez

When MySQL client connects to the server it uses one of the authentication plugins. On the server-side, plugins are located in the directory, specified by the option plugin-dir that defaults to

BASEDIR/lib/plugin

  where

BASEDIR

  is the base directory of your MySQL installation. This perfectly works whenever you install MySQL using package manager, or from the Linux tarball.

However, authentication plugins should be also loaded on the client-side. If you installed MySQL into the custom location, you may end up with an error when trying to connect using any plugin requiring a separate client library.

For example, let’s set the Test authentication plugin to authenticate a user:

mysql> INSTALL PLUGIN test_plugin_server SONAME 'auth_test_plugin.so';         
Query OK, 0 rows affected (0,01 sec)

mysql> CREATE USER 'sveta'@'%' IDENTIFIED WITH test_plugin_server;
Query OK, 0 rows affected (0,01 sec)

Then, if you run the

mysql

  client without option

--plugin-dir

 , you will get an error:

$ mysql -usveta
ERROR 2059 (HY000): Authentication plugin 'auth_test_plugin' cannot be loaded: /usr/local/Percona-Server-8.0.25-15-Linux.x86_64.glibc2.17/lib/plugin/auth_test_plugin.so: cannot open shared object file: No such file or directory

To avoid this error, specify

--plugin-dir

 either in the configuration file or as a command-line parameter:

$ mysql -usveta --plugin-dir=/home/sveta/mysqlpackages/Percona-Server-8.0.25-15-Linux.x86_64.glibc2.17/lib/plugin
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 8.0.25-15 Percona Server (GPL), Release 15, Revision a558ec2

Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \q
Bye

This issue is applicable to all plugins that require loading an external library at the client-side, including the PAM authentication plugin. In this case, you will get the error

ERROR 2059 (HY000): Authentication plugin 'dialog' cannot be loaded: /usr/local/Percona-Server-8.0.25-15-Linux.x86_64.glibc2.17/lib/plugin/dialog.so: cannot open shared object file: No such file or directory

Oracle-compatible auth_pam_compat plugin does not experience this issue, because, instead of

dialog.so

 it uses the built-in

mysql_clear_password

  plugin.

See also:

PAM plugin does not enforce plugin_dir when installed from tarball

MySQL and MariaDB authentication against pam_unix

A while back, I made some changes to the plugin interface for pt-online-schema-change which allows custom replication checks to be written. As I was adding this functionality, I also added the --plugin option to pt-table-checksum. This was released in Percona Toolkit 2.2.8.

With these additions, I spent some time writing a plugin that allows Percona Toolkit tools to use Tungsten Replicator to check for slave lag, you can find the code at https://github.com/grypyrg/percona-toolkit-plugin-tungsten-replicator

Requirements

The plugin uses the perl JSON::XS module (perl-JSON-XS rpm package, http://search.cpan.org/dist/JSON-XS/XS.pm), make sure it’s available or the plugin will not work.

Preparation

We need to use the --recursion-method=dsns as the Percona Toolkit tools are not able to automatically find the tungsten replicator slaves that are connected to the master database. (I did add a blueprint on launchpad to make this possible https://blueprints.launchpad.net/percona-toolkit/+spec/plugin-custom-recursion-method)

The dsns recursion-method gets the list of slaves from a database table you specify:

CREATE TABLE `percona`.`dsns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

Here one slave node3 is replicating from the master:

node1 mysql> select * from percona.dsns;
+----+-----------+---------+
| id | parent_id | dsn     |
+----+-----------+---------+
|  2 |      NULL | h=node3 |
+----+-----------+---------+

Configuration

Currently, it is not possible to specify extra options for the plugin with Percona Toolkit, so some manual editing of the perl file is still necessary to configure it.

So before we can run a checksum, we need to configure the plugin:

## CONFIGURATION
# trepctl command to run
my $trepctl="/opt/tungsten/installs/cookbook/tungsten/tungsten-replicator/bin/trepctl";
# what tungsten replicator service to check
my $service="bravo";
# what user does tungsten replicator use to perform the writes?
# See Binlog Format for more information
my $tungstenusername = 'tungsten';

Running A Checksum

Here I did a checksum of a table with pt-table-checksum. During the checksum process, I brought the slave node offline and brought it back online again:

# pt-table-checksum
        -u checksum
        --no-check-binlog-format
        --recursion-method=dsn=D=percona,t=dsns
        --plugin=/vagrant/pt-plugin-tungsten_replicator.pl
        --databases app
        --check-interval=5
        --max-lag=10
Created plugin from /vagrant/pt-plugin-tungsten_replicator.pl.
PLUGIN get_slave_lag: Using Tungsten Replicator to check replication lag
Tungsten Replicator status of host node3 is OFFLINE:NORMAL, waiting
Tungsten Replicator status of host node3 is OFFLINE:NORMAL, waiting
Replica node3 is stopped.  Waiting.
Tungsten Replicator status of host node3 is OFFLINE:NORMAL, waiting
Replica lag is 125 seconds on node3.  Waiting.
Replica lag is 119 seconds on node3.  Waiting.
Checksumming app.large_table:  22% 00:12 remain
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
07-03T10:49:54      0      0  2097152       7       0 213.238 app.large_table

I recommend to change the check-interval higher than the default 1 second as running trepctl takes a while. This could slow down the process quite a lot.

Making Schema Changes

The plugin also works with pt-online-schema-change:

# pt-online-schema-change
        -u schemachange
        --recursion-method=dsn=D=percona,t=dsns
        --plugin=/vagrant/pt-plugin-tungsten_replicator.pl
        --check-interval=5
        --max-lag=10
        --alter "add index (column1) "
        --execute D=app,t=large_table
Created plugin from /vagrant/pt-plugin-tungsten_replicator.pl.
Found 1 slaves:
  node3
Will check slave lag on:
  node3
PLUGIN get_slave_lag: Using Tungsten Replicator to check replication lag
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `app`.`large_table`...
Creating new table...
Created new table app._large_table_new OK.
Waiting forever for new table `app`.`_large_table_new` to replicate to node3...
Altering new table...
Altered `app`.`_large_table_new` OK.
2014-07-03T13:02:33 Creating triggers...
2014-07-03T13:02:33 Created triggers OK.
2014-07-03T13:02:33 Copying approximately 8774670 rows...
Copying `app`.`large_table`:  26% 01:21 remain
Copying `app`.`large_table`:  50% 00:59 remain
Replica lag is 12 seconds on node3.  Waiting.
Replica lag is 12 seconds on node3.  Waiting.
Copying `app`.`large_table`:  53% 02:22 remain
Copying `app`.`large_table`:  82% 00:39 remain
2014-07-03T13:06:06 Copied rows OK.
2014-07-03T13:06:06 Swapping tables...
2014-07-03T13:06:06 Swapped original and new tables OK.
2014-07-03T13:06:06 Dropping old table...
2014-07-03T13:06:06 Dropped old table `app`.`_large_table_old` OK.
2014-07-03T13:06:06 Dropping triggers...
2014-07-03T13:06:06 Dropped triggers OK.
Successfully altered `app`.`large_table`.

As you can see, there was some slave lag during the schema changes.

Binlog Format & pt-online-schema-change

pt-online-schema-change uses triggers in order to do the schema changes. Tungsten Replicator has some limitations with different binary log formats and triggers (https://code.google.com/p/tungsten-replicator/wiki/TRCAdministration#Triggers_and_Row_Replication).

In Tungsten Replicator, ROW based binlog events will be converted to SQL statements, which causes triggers to be executed on the slave as well, this does not happen with traditional replication.

Different settings:

• STATEMENT based binary logging works by default
• ROW based binary logging works, the plugin recreates the triggers and uses the technique documented at https://code.google.com/p/tungsten-replicator/wiki/TRCAdministration#Triggers_and_Row_Replication
• MIXED binary logging does not work, as there is currently no way to determine whether an event was written to the binary log in statement or row based format, so it’s not possible to know if triggers should be run or not. The tool will exit and and error will be returned:
  

  Error creating --plugin: The master it's binlog_format=MIXED,
  pt-online-schema change does not work well with
  Tungsten Replicator and binlog_format=MIXED.

Be Warned

The binlog_format can be overriden on a per session basis, make sure that this does NOT happen when using pt-online-schema-change.

Summary

The documentation on the Continuent website already mentions how you can compare data with pt-table-checksum.

I believe this plugin is a good addition to it. The features in Percona Toolkit that monitor replication lag can now be used with Tungsten Replicator and therefore gives you control on how much replication lag is tolerated while using those tools.

The post Check for MySQL slave lag with Percona Toolkit plugin for Tungsten Replicator appeared first on MySQL Performance Blog.