Personal blog of Yzmir Ramirez

This blog post will help you configure a 3-node Percona XtraDB Cluster using a binary tarball on your local machine. Configuration files are auto-generated with mostly default configurations except for port/IP address details. The tool has the handy script to create configuration files and start multiple Percona XtraDB Cluster nodes on the fly, helping you to start PXC quickly without spending time on startup configuration as well as avoid using any virtual environments.  The script is available in the percona-qa github project. Currently, this script supports PXC binary tarball distributions only.

You can download the appropriate tarball package from the Percona-XtraDB-Cluster-8.0 downloads page. Once you have the packages available on your local machine, unpack the tarball package.

Note: You can use the DBDeployer tool to deploy PXC-5.7 servers easily. pxc-startup.sh script also works with PXC-5.7 packages.

Now we need to run the pxc-startup.sh script from the Percona XtraDB Cluster base directory. It will check out the PXC startup script called start_pxc.

The following steps will help you to start a 3-node PXC in CentOS 7.

1. Checkout pxc-startup.sh repo:

wget https://raw.githubusercontent.com/Percona-QA/percona-qa/master/pxc-tests/pxc-startup.sh

2. Download PXC binary tarball packages for CentOS7 (In this blog we will be using the PXC-8.0 experimental package):

wget https://www.percona.com/redir/downloads/TESTING/Percona-XtraDB-Cluster-8.0/centos7/Percona-XtraDB-Cluster_8.0.15.5-27dev.4.2_Linux.x86_64.ssl102.tar.gz

3. Unpack tarball package and run pxc-startup.sh script from Percona XtraDB Cluster base directory:

tar -xzf Percona-XtraDB-Cluster_8.0.15.5-27dev.4.2_Linux.x86_64.ssl102.tar.gz

$ cd Percona-XtraDB-Cluster_8.0.15.5-27dev.4.2_Linux.x86_64.ssl102/

$ bash ../pxc-startup.sh
Added scripts: ./start_pxc
./start_pxc will create ./stop_pxc | ./*node_cli | ./wipe scripts
$

4. If you want to start the 3-node cluster, please use numeric 3 as parameter with ./start_pxc:

$ ./start_pxc 3
Starting PXC nodes…
Server on socket /home/vagrant/Percona-XtraDB-Cluster_8.0.15.5-27dev.4.2_Linux.x86_64.ssl102/node1/socket.sock with datadir /home/vagrant/Percona-XtraDB-Cluster_8.0.15.5-27dev.4.2_Linux.x86_64.ssl102/node1 started
  Configuration file : /home/vagrant/Percona-XtraDB-Cluster_8.0.15.5-27dev.4.2_Linux.x86_64.ssl102/node1.cnf
Server on socket /home/vagrant/Percona-XtraDB-Cluster_8.0.15.5-27dev.4.2_Linux.x86_64.ssl102/node2/socket.sock with datadir /home/vagrant/Percona-XtraDB-Cluster_8.0.15.5-27dev.4.2_Linux.x86_64.ssl102/node2 started
  Configuration file : /home/vagrant/Percona-XtraDB-Cluster_8.0.15.5-27dev.4.2_Linux.x86_64.ssl102/node2.cnf
Server on socket /home/vagrant/Percona-XtraDB-Cluster_8.0.15.5-27dev.4.2_Linux.x86_64.ssl102/node3/socket.sock with datadir /home/vagrant/Percona-XtraDB-Cluster_8.0.15.5-27dev.4.2_Linux.x86_64.ssl102/node3 started
  Configuration file : /home/vagrant/Percona-XtraDB-Cluster_8.0.15.5-27dev.4.2_Linux.x86_64.ssl102/node3.cnf
$

The start_pxc script will also create shutdown(stop)/wipe/cli sanity scripts.

$ ls -1 *_node_cli wipe *_pxc
1_node_cli
2_node_cli
3_node_cli
start_pxc
stop_pxc
wipe
$

The ./stop_pxc script will stop all cluster nodes.

$ ./stop_pxc
Server on socket /home/vagrant/Percona-XtraDB-Cluster_8.0.15.5-27dev.4.2_Linux.x86_64.ssl102/node3/socket.sock with datadir /home/vagrant/Percona-XtraDB-Cluster_8.0.15.5-27dev.4.2_Linux.x86_64.ssl102/node3 halted
Server on socket /home/vagrant/Percona-XtraDB-Cluster_8.0.15.5-27dev.4.2_Linux.x86_64.ssl102/node2/socket.sock with datadir /home/vagrant/Percona-XtraDB-Cluster_8.0.15.5-27dev.4.2_Linux.x86_64.ssl102/node2 halted
Server on socket /home/vagrant/Percona-XtraDB-Cluster_8.0.15.5-27dev.4.2_Linux.x86_64.ssl102/node1/socket.sock with datadir /home/vagrant/Percona-XtraDB-Cluster_8.0.15.5-27dev.4.2_Linux.x86_64.ssl102/node1 halted
$

The ./[1-3]_node_cli  scripts will help to login to the respective node using the MySQL client.

$ ./1_node_cli
[..]
node1:root@localhost> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

1 row in set (0.03 sec)
node1:root@localhost>

The ./wipe script will trigger stop_pxc script and move the data directory to .PREV.

$ ls  -d1 *.PREV
node1.PREV
node2.PREV
node3.PREV
$

The configuration files will be created in the base directory. You can also add custom configurations in the start_pxc script.

$ ls -1 *.cnf
node1.cnf
node2.cnf
node3.cnf
$

A new Percona Toolkit series has been released: Percona Toolkit 2.2 for MySQL 5.6. Several months in the making and many new features, changes, and improvements make this a great new series. It replaces the 2.1 series for which we plan to do only one more bug fix release (as 2.1.10) then retire. 2.2 is largely backward-compatible with 2.1, but some tools were changed significantly (e.g. pt-query-digest, pt-upgrade, and pt-online-schema-change).  Here are some highlights:

Official support for MySQL 5.6 and Percona XtraDB Cluster

We started beta support for MySQL 5.6 in 2.1.8 when 5.6 was still beta. Now that MySQL 5.6 is GA, so is our support for it. Check out the Percona Toolkit supported platforms and versions. When you upgrade to MySQL 5.6, be sure to upgrade to Percona Toolkit 2.2, too.

We also started beta support for Percona XtraDB Cluster in 2.1.8, but now that support is official in 2.2 because we have had many months to work with PXC and figure out which tools work with it and how. There’s still one noticeable omission: pt-table-sync. It’s still unclear if or how one would sync a cluster that, in theory, doesn’t become out-of-sync. As Percona XtraDB Cluster develops, Percona Toolkit will continue to evolve to support it.

pt-online-schema-change (pt-osc) is much more resilient

pt-online-schema-change 2.1 has been a great success, and people have been using it for evermore difficult and challenging tasks. Consequently, we needed to make it “try harder”, even though it already tried pretty hard to keep working despite recoverable errors and such. Whereas pt-osc 2.1 only retries certain operations, pt-osc 2.2 retries every critical operation, and its tries and wait time between tries for all operations are configurable. Also, we removed –lock-wait-timeout which set innodb_lock_wait_timeout because that now conflicts, or is at least confused with, lock_wait_timeout (introduced in MySQL 5.5) for metadata locks. Now –set-vars is used to set both of these (or any) system variables. For a quick intro to metadata locks and how they may affect you, see Ovais’s article.

In short: pt-online-schema-change 2.2 is far more resilient out of the box. It’s also aware of metadata locks now, whereas 2.1 was not really aware of them. And it’s highly configurable, so you can make the tool try very hard to keep working.

pt-upgrade is brand-new

pt-upgrade was written once long ago and not updated since.  Now that we have four base versions of MySQL (5.0, 5.1, 5.5, and 5.6), plus at least four major forks (Percona Server, MariaDB, Percona XtraDB Cluster, and MariaDB Galera Cluster), upgrades are fashionable, so to speak. Problem is: “original” pt-upgrade was too noisy and too complex. pt-upgrade 2.2 is far simpler and far easier to use. It’s basically what you expect from such a tool. Moreover, it has a really helpful new feature: “reference results”, i.e. saved results from running queries on a server. Granted, this can take a lot of disk space, but it allows you to “run now, compare later.”

If you’re thinking about upgrading, give pt-upgrade a try. It also reads every type of log now (slow, general, binary, and tcpdump), so you shouldn’t have a problem finding queries to run and compare.

pt-query-digest is simpler

pt-query-digest 2.2 has fewer options now. Basically, we re-focused it on its primary objective: analyzing MySQL query logs. So the ability to parse memcached, Postgres, Apache, and other logs was removed. We also removed several options that probably nobody ever used, and changed/renamed other options to be more logical. The result is a simpler, more focused tool, i.e. less overwhelming.

Also, pt-query-digest 2.2 can save results in JSON format (–output=json). This feature is still in development while we determine the optimal JSON structure.

Version check is on by default

In 2.1.4, released September/October 2012, we introduced a feature called “version check” into most tools. It’s like a lot of software that automatically checks for updates, but it’s also more: it’s a free service from Percona that advises when certain programs (Percona Toolkit tools, MySQL, Perl, etc.) are either out of date or are known bad versions. For example, there are two versions of the DBD::mysql Perl module that have problems. And there are certain versions of MySQL that have critical bugs. Version check will warn you about these if your system is running them.

What’s new in 2.2 is that, whereas this feature (specifically, the option in tools: –version-check) was off by default, now it’s on by default. If the IO::Socket::SSL Perl module is installed (easily available through your package manager), it will use a secure (https) connection over the web, else it will use a standard (http) connection.

pt-stalk and pt-mysql-summary have built-in MySQL options

No more “pt-stalk — -h db1 -u me”. pt-stalk 2.2 and pt-mysql-summary 2.2 have all the standard MySQL options built-in, like other tools: –user, –host, –port, –password, –socket, –defaults-file. So now the command line is what you expect: pt-stalk -h dhb1 -u me.

pt-stalk –no-stalk is no longer magical

Originally, pt-stalk –no-stalk was meant to simulate pt-collect, i.e. collect once and exit. To do that, the tool magically set some options and clobbered others, resulting in no way to do repeated collections at intervals. Now –no-stalk means only that: don’t stalk, just collect, respecting –interval and –iterations as usual. So to collect once and exit: pt-stalk –no-stalk –iterations 1.

pt-fk-error-logger and pt-deadlock-logger are standardized

Similar to the pt-stalk –no-stalk changes, pt-fk-error-logger and pt-deadlock-logger received mini overhauls in 2.2 to make their run-related options (–run-time, –interval, –iterations) standard. If you hadn’t noticed, one tool would run forever by default, while the other would run once and exit. And each treated their run-related options a little differently. This magic is gone now: both tools run forever by default, so specify –iterations or –run-time to limit how long they run.

---

There are more changes in addition to those highlights.  For example, three tools were removed, and there were several bug fixes. See https://launchpad.net/percona-toolkit/+milestone/2.2.1 for the full list.  If you upgrade from 2.1 to 2.2, be sure to re-read tools’ documentation to see what has changed.

As the first release in a new series, 2.2 features are not yet finalized. In other words, we may change things like the pt-query-digest –output json format in future releases after receiving real-world feedback.

In summary, Percona Toolkit 2.2 for MySQL is an exciting release with many new and helpful features. Users are encouraged to begin upgrading, particularly given that, except for the forthcoming 2.1.10 release, no more work will be done on 2.1 (unless you’re a Percona customer with a support contract or other agreement).

The post MySQL 5.6 Compatible Percona Toolkit 2.2 Released appeared first on MySQL Performance Blog.