Tag Archives: Percona Software

Percona Server 5.5.34-32.0 is now available

by

Percona Server version 5.5.34-32.0

Percona Server version 5.5.34-32.0

Percona is glad to announce the release of Percona Server 5.5.34-32.0 on October 28th, 2013 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.5.34, including all the bug fixes in it, Percona Server 5.5.34-32.0 is now the current stable release in the 5.5 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.5.34-32.0 milestone at Launchpad.

New Features:

  • Percona Server has extended the SELECT INTO ... OUTFILE and SELECT INTO DUMPFILE to add the support for UNIX sockets and named pipes.
  • Percona Server now provides additional information in the slow query log when log_slow_rate_limit variable is enabled.
  • A new variable slow_query_log_always_write_time has been introduced. It can be used to specify an additional execution time threshold for the slow query log, that, when exceeded, will cause a query to be logged unconditionally, that is, log_slow_rate_limit will not apply to it.
  • Utility user feature has been extended by adding a new utility_user_privileges that allows a comma separated value list of extra access privileges that can be granted to the utility user.

Bugs Fixed:

  • Due to an incompatible upstream change that went in unnoticed, the log tracker thread would attempt to replay any file operations it encountered. In most cases this were a no-op, but there were race conditions for certain DDL operations that would have resulted in server crash. Bug fixed #1217002.
  • apt-get upgrade of Percona Server would fail in post-installation step if server failed to start. Bug fixed #1002500.
  • Fixed the libssl.so.6 dependency issues in binary tarballs releases. Bug fixed #1172916.
  • Error in install_layout.cmake could cause that some library files, during the build, end up in different directories on x86_64 environment. Bug fixed #1174300.
  • Percona Server could crash while accessing BLOB or TEXT columns in InnoDB tables if Support for Fake Changes was enabled. Bug fixed #1188168.
  • Memory leak was introduced by the fix for bug #1132194. Bug fixed #1204873.
  • The unnecessary overhead from persistent InnoDB adaptive hash index latching has been removed, potentially improving stability of the Multiple Adaptive Hash Search Partitions feature as well. Upstream bug fixed #70216, bug fixed #1218347.
  • Fixed the incorrect dependency with libmysqlclient18-dev from Percona Server 5.5.33-31.1. Bug fixed #1237097.
  • A memory leak in Utility user feature has been fixed. Bug fixed #1166638.
  • Expanded Program Option Modifiers did not deallocate memory correctly. Bug fixed #1167487.
  • A server could crash due to a race condition between a INNODB_CHANGED_PAGES query and a bitmap file delete by PURGE CHANGED_PAGE_BITMAP or directly on the file system. Bug fixed #1191580.
  • Percona Server could not be built with Thread Pool feature and -DWITH_PERFSCHEMA_ENGINE=OFF option. Bug fixed #1196383.
  • Building Percona Server with -DHAVE_PURIFY option would result in an error. Fixed by porting the close_socket function from MariaDB. Bug fixed #1203567.
  • Adaptive hash index memory size was incorrectly calculated in SHOW ENGINE INNODB STATUS and Innodb_mem_adaptive_hash status variable. Bug fixed #1218330.
  • Some Expanded Program Option Modifiers didn’t have an effect if they were specified in non-normalized way (innodb_io_capacity vs innodb-io-capacity). Bug fixed #1233294.
  • Enabling Enforcing Storage Engine feature could lead to error on Percona Server shutdown. Bug fixed #1233354.
  • Storage engine enforcement (enforce_storage_engine) is now ignored when the server is started in either bootstrap or skip-grant-tables mode. Bug fixed #1236938.
  • Fixed the build warnings caused by User Statistics code on non-Linux platforms. Bug fixed #711817.
  • Adaptive hash indexing partitioning code has been simplified, potentially improving performance. Bug fixed #1218321.

Other bugs fixed: bug fixed #1239630, bug fixed #1191589, bug fixed #1200162, bug fixed #1214449, and bug fixed #1190604.

Release notes for Percona Server 5.5.34-32.0 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

The post Percona Server 5.5.34-32.0 is now available appeared first on MySQL Performance Blog.

Percona XtraBackup 2.1.5 is now available

by

Percona XtraBackup for MySQL Percona is glad to announce the release of Percona XtraBackup 2.1.5 on September 19th, 2013. Downloads are available from our download site here and Percona Software Repositories.

This release is the current GA (Generally Available) stable release in the 2.1 series. Percona XtraBackup is an open source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. Percona XtraBackup is an open source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases.

New Features:

  • Percona XtraBackup now supports new form of incremental backups for Percona Server 5.6 that uses Log Archiving for XtraDB feature.
  • Percona XtraBackup now supports new innobackupex –version-check option. When specified, innobackupex will perform a version check against the server on the backup stage after creating a server connection.

Bugs Fixed:

  • Percona XtraBackup did not close temporary files created when preparing a compact backup, which would lead to excessive disk space usage until the prepare process finished. Bug fixed #1111380.
  • Depending on the subroutine innobackupex could sometimes leave the child processes running in case of the error. innobackupex now makes sure that all child processes are killed if an error occurs in the script. Bug fixed #1135441.
  • The 5.6-based binary ( xtrabackup_56), which is used to backup both MySQL 5.6 and Percona Server 5.6 servers, did not support Percona Server-specific innodb_log_block_size option in Percona Server 5.6.11+ and would fail when trying to backup a server with a non-default innodb_log_block_size value. Bug fixed #1194828.
  • Percona XtraBackup would stop in case log block numbers had to wrap around, which only happens once per 1 GB of log writes, and the wrap-around point was between the last checkpoint and the current log tail at the time the backup starts. Bug fixed #1206309.
  • xtrabackup_56 binary would fail to create a suspend file, which would result in an error. Bug fixed #1210266.
  • Regression was introduced in Percona XtraBackup 2.1.4 which lead to cp utility being used to copy metadata files even if the innobackupex –rsync option was used. Bug fixed #1211263.

Other bugs fixed: bug fixed #1214272, bug fixed #1214730, bug fixed #1213102, bug fixed #1213036, bug fixed #1204045, bug fixed #1154476, bug fixed #1195402, bug fixed #1195055.

Release notes with all the bugfixes for Percona XtraBackup 2.1.5 are available in our online documentation. All of Percona‘s software is open source and free, all the details of the release can be found in the 2.1.5 milestone at Launchpad. Bugs can be reported on the launchpad bug tracker.

The post Percona XtraBackup 2.1.5 is now available appeared first on MySQL Performance Blog.

Percona XtraDB Cluster: Setting up a simple cluster

by

Percona XtraDB Cluster (PXC) is different enough from async replication that it can be a bit of a puzzle how to do things the Galera way.  This post will attempt to illustrate the basics of setting up 2 node PXC cluster from scratch.

Requirements

Two servers (could be VMs) that can talk to each other.  I’m using CentOS for this post.  Here’s a dirt-simple Vagrant setup: https://github.com/jayjanssen/two_centos_nodes to make this easy (on Virtualbox).

These servers are talking over the 192.168.70.0/24 internal network for our example.

jayj@~/Src $ git clone https://github.com/jayjanssen/two_centos_nodes.git
jayj@~/Src $ cd two_centos_nodes
jayj@~/Src/two_centos_nodes $ vagrant up
 Bringing machine 'node1' up with 'virtualbox' provider...
 Bringing machine 'node2' up with 'virtualbox' provider...
 [node1] Importing base box 'centos-6_4-64_percona'...
 [node1] Matching MAC address for NAT networking...
 [node1] Setting the name of the VM...
 [node1] Clearing any previously set forwarded ports...
 [node1] Creating shared folders metadata...
 [node1] Clearing any previously set network interfaces...
 [node1] Preparing network interfaces based on configuration...
 [node1] Forwarding ports...
 [node1] -- 22 => 2222 (adapter 1)
 [node1] Booting VM...
 [node1] Waiting for machine to boot. This may take a few minutes...
 [node1] Machine booted and ready!
 [node1] Setting hostname...
 [node1] Configuring and enabling network interfaces...
 [node1] Mounting shared folders...
 [node1] -- /vagrant
 [node2] Importing base box 'centos-6_4-64_percona'...
 [node2] Matching MAC address for NAT networking...
 [node2] Setting the name of the VM...
 [node2] Clearing any previously set forwarded ports...
 [node2] Fixed port collision for 22 => 2222. Now on port 2200.
 [node2] Creating shared folders metadata...
 [node2] Clearing any previously set network interfaces...
 [node2] Preparing network interfaces based on configuration...
 [node2] Forwarding ports...
 [node2] -- 22 => 2200 (adapter 1)
 [node2] Booting VM...
 [node2] Waiting for machine to boot. This may take a few minutes...
 [node2] Machine booted and ready!
 [node2] Setting hostname...
 [node2] Configuring and enabling network interfaces...
 [node2] Mounting shared folders...
 [node2] -- /vagrant

Install the software

These steps should be repeated on both nodes:

jayj@~/Src/two_centos_nodes $ vagrant ssh node1
Last login: Tue Sep 10 14:15:50 2013 from 10.0.2.2
[root@node1 ~]# yum localinstall http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
Loaded plugins: downloadonly, fastestmirror, priorities
Setting up Local Package Process
percona-release-0.0-1.x86_64.rpm | 6.1 kB 00:00
Examining /var/tmp/yum-root-t61o64/percona-release-0.0-1.x86_64.rpm: percona-release-0.0-1.x86_64
Marking /var/tmp/yum-root-t61o64/percona-release-0.0-1.x86_64.rpm to be installed
Determining fastest mirrors
epel/metalink | 15 kB 00:00
* base: mirror.atlanticmetro.net
* epel: mirror.seas.harvard.edu
* extras: centos.mirror.netriplex.com
* updates: mirror.team-cymru.org
base | 3.7 kB 00:00
base/primary_db | 4.4 MB 00:01
epel | 4.2 kB 00:00
epel/primary_db | 5.5 MB 00:04
extras | 3.4 kB 00:00
extras/primary_db | 18 kB 00:00
updates | 3.4 kB 00:00
updates/primary_db | 4.4 MB 00:03
Resolving Dependencies
--> Running transaction check
---> Package percona-release.x86_64 0:0.0-1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================================================================
Package Arch Version Repository Size
================================================================================================================================
Installing:
percona-release x86_64 0.0-1 /percona-release-0.0-1.x86_64 3.6 k
Transaction Summary
================================================================================================================================
Install 1 Package(s)
Total size: 3.6 k
Installed size: 3.6 k
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : percona-release-0.0-1.x86_64 1/1
Verifying : percona-release-0.0-1.x86_64 1/1
Installed:
percona-release.x86_64 0:0.0-1
Complete!
[root@node1 ~]# yum install -y Percona-XtraDB-Cluster-server
Loaded plugins: downloadonly, fastestmirror, priorities
Loading mirror speeds from cached hostfile
* base: mirror.atlanticmetro.net
* epel: mirror.seas.harvard.edu
* extras: centos.mirror.netriplex.com
* updates: mirror.team-cymru.org
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package Percona-XtraDB-Cluster-server.x86_64 1:5.5.31-23.7.5.438.rhel6 will be installed
--> Processing Dependency: xtrabackup >= 1.9.0 for package: 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64
--> Processing Dependency: Percona-XtraDB-Cluster-client for package: 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64
--> Processing Dependency: libaio.so.1(LIBAIO_0.4)(64bit) for package: 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64
--> Processing Dependency: rsync for package: 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64
--> Processing Dependency: Percona-XtraDB-Cluster-galera for package: 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64
--> Processing Dependency: nc for package: 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64
--> Processing Dependency: Percona-XtraDB-Cluster-shared for package: 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64
--> Processing Dependency: libaio.so.1(LIBAIO_0.1)(64bit) for package: 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64
--> Processing Dependency: libaio.so.1()(64bit) for package: 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64
--> Running transaction check
---> Package Percona-XtraDB-Cluster-client.x86_64 1:5.5.31-23.7.5.438.rhel6 will be installed
---> Package Percona-XtraDB-Cluster-galera.x86_64 0:2.6-1.152.rhel6 will be installed
---> Package Percona-XtraDB-Cluster-shared.x86_64 1:5.5.31-23.7.5.438.rhel6 will be obsoleting
---> Package libaio.x86_64 0:0.3.107-10.el6 will be installed
---> Package mysql-libs.x86_64 0:5.1.69-1.el6_4 will be obsoleted
---> Package nc.x86_64 0:1.84-22.el6 will be installed
---> Package percona-xtrabackup.x86_64 0:2.1.4-656.rhel6 will be installed
--> Processing Dependency: perl(DBD::mysql) for package: percona-xtrabackup-2.1.4-656.rhel6.x86_64
--> Processing Dependency: perl(Time::HiRes) for package: percona-xtrabackup-2.1.4-656.rhel6.x86_64
---> Package rsync.x86_64 0:3.0.6-9.el6 will be installed
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:4.013-3.el6 will be installed
--> Processing Dependency: perl(DBI::Const::GetInfoType) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Processing Dependency: perl(DBI) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: perl-DBD-MySQL-4.013-3.el6.x86_64
---> Package perl-Time-HiRes.x86_64 4:1.9721-131.el6_4 will be installed
--> Running transaction check
---> Package Percona-Server-shared-compat.x86_64 0:5.5.33-rel31.1.566.rhel6 will be obsoleting
---> Package perl-DBI.x86_64 0:1.609-4.el6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================================================================
Package Arch Version Repository Size
================================================================================================================================
Installing:
Percona-Server-shared-compat x86_64 5.5.33-rel31.1.566.rhel6 percona 3.4 M
replacing mysql-libs.x86_64 5.1.69-1.el6_4
Percona-XtraDB-Cluster-server x86_64 1:5.5.31-23.7.5.438.rhel6 percona 15 M
Percona-XtraDB-Cluster-shared x86_64 1:5.5.31-23.7.5.438.rhel6 percona 648 k
replacing mysql-libs.x86_64 5.1.69-1.el6_4
Installing for dependencies:
Percona-XtraDB-Cluster-client x86_64 1:5.5.31-23.7.5.438.rhel6 percona 6.3 M
Percona-XtraDB-Cluster-galera x86_64 2.6-1.152.rhel6 percona 1.1 M
libaio x86_64 0.3.107-10.el6 base 21 k
nc x86_64 1.84-22.el6 base 57 k
percona-xtrabackup x86_64 2.1.4-656.rhel6 percona 6.8 M
perl-DBD-MySQL x86_64 4.013-3.el6 base 134 k
perl-DBI x86_64 1.609-4.el6 base 705 k
perl-Time-HiRes x86_64 4:1.9721-131.el6_4 updates 47 k
rsync x86_64 3.0.6-9.el6 base 334 k
Transaction Summary
================================================================================================================================
Install 12 Package(s)
Total download size: 35 M
Downloading Packages:
(1/12): Percona-Server-shared-compat-5.5.33-rel31.1.566.rhel6.x86_64.rpm | 3.4 MB 00:04
(2/12): Percona-XtraDB-Cluster-client-5.5.31-23.7.5.438.rhel6.x86_64.rpm | 6.3 MB 00:03
(3/12): Percona-XtraDB-Cluster-galera-2.6-1.152.rhel6.x86_64.rpm | 1.1 MB 00:00
(4/12): Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64.rpm | 15 MB 00:04
(5/12): Percona-XtraDB-Cluster-shared-5.5.31-23.7.5.438.rhel6.x86_64.rpm | 648 kB 00:00
(6/12): libaio-0.3.107-10.el6.x86_64.rpm | 21 kB 00:00
(7/12): nc-1.84-22.el6.x86_64.rpm | 57 kB 00:00
(8/12): percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm | 6.8 MB 00:03
(9/12): perl-DBD-MySQL-4.013-3.el6.x86_64.rpm | 134 kB 00:00
(10/12): perl-DBI-1.609-4.el6.x86_64.rpm | 705 kB 00:00
(11/12): perl-Time-HiRes-1.9721-131.el6_4.x86_64.rpm | 47 kB 00:00
(12/12): rsync-3.0.6-9.el6.x86_64.rpm | 334 kB 00:00
--------------------------------------------------------------------------------------------------------------------------------
Total 1.0 MB/s | 35 MB 00:34
warning: rpmts_HdrFromFdno: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona
Importing GPG key 0xCD2EFD2A:
Userid : Percona MySQL Development Team <mysql-dev@percona.com>
Package: percona-release-0.0-1.x86_64 (@/percona-release-0.0-1.x86_64)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-percona
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : libaio-0.3.107-10.el6.x86_64 1/13
Installing : 1:Percona-XtraDB-Cluster-shared-5.5.31-23.7.5.438.rhel6.x86_64 2/13
Installing : 1:Percona-XtraDB-Cluster-client-5.5.31-23.7.5.438.rhel6.x86_64 3/13
Installing : Percona-XtraDB-Cluster-galera-2.6-1.152.rhel6.x86_64 4/13
Installing : nc-1.84-22.el6.x86_64 5/13
Installing : 4:perl-Time-HiRes-1.9721-131.el6_4.x86_64 6/13
Installing : perl-DBI-1.609-4.el6.x86_64 7/13
Installing : rsync-3.0.6-9.el6.x86_64 8/13
Installing : Percona-Server-shared-compat-5.5.33-rel31.1.566.rhel6.x86_64 9/13
Installing : perl-DBD-MySQL-4.013-3.el6.x86_64 10/13
Installing : percona-xtrabackup-2.1.4-656.rhel6.x86_64 11/13
Installing : 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64 12/13
ls: cannot access /var/lib/mysql/*.err: No such file or directory
ls: cannot access /var/lib/mysql/*.err: No such file or directory
130917 14:43:16 [Note] WSREP: Read nil XID from storage engines, skipping position init
130917 14:43:16 [Note] WSREP: wsrep_load(): loading provider library 'none'
130917 14:43:16 [Note] WSREP: Service disconnected.
130917 14:43:17 [Note] WSREP: Some threads may fail to exit.
130917 14:43:17 [Note] WSREP: Read nil XID from storage engines, skipping position init
130917 14:43:17 [Note] WSREP: wsrep_load(): loading provider library 'none'
130917 14:43:18 [Note] WSREP: Service disconnected.
130917 14:43:19 [Note] WSREP: Some threads may fail to exit.
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h node1 password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
Please report any problems with the /usr/bin/mysqlbug script!
Percona recommends that all production deployments be protected with a support
contract (http://www.percona.com/mysql-suppport/) to ensure the highest uptime,
be eligible for hot fixes, and boost your team's productivity.
/var/tmp/rpm-tmp.rVkEi4: line 95: x0: command not found
Percona XtraDB Cluster is distributed with several useful UDFs from Percona Toolkit.
Run the following commands to create these functions:
mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"
See http://code.google.com/p/maatkit/source/browse/trunk/udf for more details
Erasing : mysql-libs-5.1.69-1.el6_4.x86_64 13/13
Verifying : 1:Percona-XtraDB-Cluster-server-5.5.31-23.7.5.438.rhel6.x86_64 1/13
Verifying : 1:Percona-XtraDB-Cluster-client-5.5.31-23.7.5.438.rhel6.x86_64 2/13
Verifying : perl-DBD-MySQL-4.013-3.el6.x86_64 3/13
Verifying : Percona-Server-shared-compat-5.5.33-rel31.1.566.rhel6.x86_64 4/13
Verifying : rsync-3.0.6-9.el6.x86_64 5/13
Verifying : perl-DBI-1.609-4.el6.x86_64 6/13
Verifying : percona-xtrabackup-2.1.4-656.rhel6.x86_64 7/13
Verifying : 1:Percona-XtraDB-Cluster-shared-5.5.31-23.7.5.438.rhel6.x86_64 8/13
Verifying : 4:perl-Time-HiRes-1.9721-131.el6_4.x86_64 9/13
Verifying : nc-1.84-22.el6.x86_64 10/13
Verifying : libaio-0.3.107-10.el6.x86_64 11/13
Verifying : Percona-XtraDB-Cluster-galera-2.6-1.152.rhel6.x86_64 12/13
Verifying : mysql-libs-5.1.69-1.el6_4.x86_64 13/13
Installed:
Percona-Server-shared-compat.x86_64 0:5.5.33-rel31.1.566.rhel6 Percona-XtraDB-Cluster-server.x86_64 1:5.5.31-23.7.5.438.rhel6
Percona-XtraDB-Cluster-shared.x86_64 1:5.5.31-23.7.5.438.rhel6
Dependency Installed:
Percona-XtraDB-Cluster-client.x86_64 1:5.5.31-23.7.5.438.rhel6 Percona-XtraDB-Cluster-galera.x86_64 0:2.6-1.152.rhel6
libaio.x86_64 0:0.3.107-10.el6 nc.x86_64 0:1.84-22.el6
percona-xtrabackup.x86_64 0:2.1.4-656.rhel6 perl-DBD-MySQL.x86_64 0:4.013-3.el6
perl-DBI.x86_64 0:1.609-4.el6 perl-Time-HiRes.x86_64 4:1.9721-131.el6_4
rsync.x86_64 0:3.0.6-9.el6
Replaced:
mysql-libs.x86_64 0:5.1.69-1.el6_4
Complete!

Disable IPtables and SElinux

It is possible to run PXC with these enabled, but for simplicity here we just disable them (on both nodes!):

[root@node1 ~]# echo 0 > /selinux/enforce
[root@node1 ~]# service iptables stop
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Unloading modules:                               [  OK  ]

Configure the cluster nodes

Create a my.cnf file on each node and put this into it:

[mysqld]
datadir = /var/lib/mysql
binlog_format = ROW
wsrep_cluster_name = twonode
wsrep_cluster_address = gcomm://192.168.70.2,192.168.70.3
wsrep_node_address = 192.168.70.2
wsrep_provider = /usr/lib64/libgalera_smm.so
wsrep_sst_method = xtrabackup
wsrep_sst_auth = sst:secret
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2

Note that the wsrep_node_address should be the proper address on each node.  We only  need this because in this environment we are not using the default NIC.

Bootstrap node1

Bootstrapping is simply starting up the first node in the cluster.  Any data on this node is taken as the source of truth for the other nodes.

[root@node1 ~]# service mysql bootstrap-pxc
Bootstrapping PXC (Percona XtraDB Cluster)Starting MySQL (Percona XtraDB Cluster).. SUCCESS!
[root@node1 mysql]# mysql -e "show global status like 'wsrep%'"
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | 43ac4bea-1fa8-11e3-8496-070bd0e5c133 |
| wsrep_protocol_version     | 4                                    |
| wsrep_last_committed       | 0                                    |
| wsrep_replicated           | 0                                    |
| wsrep_replicated_bytes     | 0                                    |
| wsrep_received             | 2                                    |
| wsrep_received_bytes       | 133                                  |
| wsrep_local_commits        | 0                                    |
| wsrep_local_cert_failures  | 0                                    |
| wsrep_local_bf_aborts      | 0                                    |
| wsrep_local_replays        | 0                                    |
| wsrep_local_send_queue     | 0                                    |
| wsrep_local_send_queue_avg | 0.000000                             |
| wsrep_local_recv_queue     | 0                                    |
| wsrep_local_recv_queue_avg | 0.000000                             |
| wsrep_flow_control_paused  | 0.000000                             |
| wsrep_flow_control_sent    | 0                                    |
| wsrep_flow_control_recv    | 0                                    |
| wsrep_cert_deps_distance   | 0.000000                             |
| wsrep_apply_oooe           | 0.000000                             |
| wsrep_apply_oool           | 0.000000                             |
| wsrep_apply_window         | 0.000000                             |
| wsrep_commit_oooe          | 0.000000                             |
| wsrep_commit_oool          | 0.000000                             |
| wsrep_commit_window        | 0.000000                             |
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced                               |
| wsrep_cert_index_size      | 0                                    |
| wsrep_causal_reads         | 0                                    |
| wsrep_incoming_addresses   | 192.168.70.2:3306                    |
| wsrep_cluster_conf_id      | 1                                    |
| wsrep_cluster_size         | 1                                    |
| wsrep_cluster_state_uuid   | 43ac4bea-1fa8-11e3-8496-070bd0e5c133 |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
| wsrep_local_index          | 0                                    |
| wsrep_provider_name        | Galera                               |
| wsrep_provider_vendor      | Codership Oy <info@codership.com>    |
| wsrep_provider_version     | 2.6(r152)                            |
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+

We can see the cluster is Primary, the size is 1, and our local state is Synced.  This is a one node cluster!

Prep for SST

SST is how new nodes (post-bootstrap) get a copy of data when joining the cluster.  It is in essence (and reality) a full backup.  We specified Xtrabackup as our backup and a username/password (sst:secret).  We need to setup a GRANT on node1 so we can run Xtrabackup against it to SST node2:

[root@node1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.5.31 Percona XtraDB Cluster (GPL), wsrep_23.7.5.r3880
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
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> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sst'@'localhost' IDENTIFIED BY 'secret';
Query OK, 0 rows affected (0.00 sec)

This GRANT should not be necessary to re-issue more than once if you are adding more nodes to the cluster.

Start node2

Assuming you’ve installed the software and my.cnf on node2, then it should be ready to start up:

[root@node2 ~]# service mysql start
Starting MySQL (Percona XtraDB Cluster).....SST in progress, setting sleep higher. SUCCESS!

If we check the status of the cluster again:

[root@node1 mysql]# mysql -e "show global status like 'wsrep%'"
+----------------------------+--------------------------------------+
| Variable_name              | Value                                |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid     | 43ac4bea-1fa8-11e3-8496-070bd0e5c133 |
| wsrep_protocol_version     | 4                                    |
| wsrep_last_committed       | 0                                    |
| wsrep_replicated           | 0                                    |
| wsrep_replicated_bytes     | 0                                    |
| wsrep_received             | 6                                    |
| wsrep_received_bytes       | 393                                  |
| wsrep_local_commits        | 0                                    |
| wsrep_local_cert_failures  | 0                                    |
| wsrep_local_bf_aborts      | 0                                    |
| wsrep_local_replays        | 0                                    |
| wsrep_local_send_queue     | 0                                    |
| wsrep_local_send_queue_avg | 0.000000                             |
| wsrep_local_recv_queue     | 0                                    |
| wsrep_local_recv_queue_avg | 0.000000                             |
| wsrep_flow_control_paused  | 0.000000                             |
| wsrep_flow_control_sent    | 0                                    |
| wsrep_flow_control_recv    | 0                                    |
| wsrep_cert_deps_distance   | 0.000000                             |
| wsrep_apply_oooe           | 0.000000                             |
| wsrep_apply_oool           | 0.000000                             |
| wsrep_apply_window         | 0.000000                             |
| wsrep_commit_oooe          | 0.000000                             |
| wsrep_commit_oool          | 0.000000                             |
| wsrep_commit_window        | 0.000000                             |
| wsrep_local_state          | 4                                    |
| wsrep_local_state_comment  | Synced                               |
| wsrep_cert_index_size      | 0                                    |
| wsrep_causal_reads         | 0                                    |
| wsrep_incoming_addresses   | 192.168.70.3:3306,192.168.70.2:3306  |
| wsrep_cluster_conf_id      | 2                                    |
| wsrep_cluster_size         | 2                                    |
| wsrep_cluster_state_uuid   | 43ac4bea-1fa8-11e3-8496-070bd0e5c133 |
| wsrep_cluster_status       | Primary                              |
| wsrep_connected            | ON                                   |
| wsrep_local_index          | 1                                    |
| wsrep_provider_name        | Galera                               |
| wsrep_provider_vendor      | Codership Oy <info@codership.com>    |
| wsrep_provider_version     | 2.6(r152)                            |
| wsrep_ready                | ON                                   |
+----------------------------+--------------------------------------+

We can see that there are now 2 nodes in the cluster!

The network connection is established over the default Galera port of 4567:

[root@node1 ~]# netstat -ant
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address               Foreign Address             State
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN
tcp        0      0 0.0.0.0:4567                0.0.0.0:*                   LISTEN
tcp        0      0 10.0.2.15:22                10.0.2.2:61129              ESTABLISHED
tcp        0    108 192.168.70.2:4567           192.168.70.3:50170          ESTABLISHED
tcp        0      0 :::22                       :::*                        LISTEN

Summary

In these steps we:

  • Installed PXC server package and dependencies
  • Did the bare-minimum configuration to get it started
  • Bootstrapped the first node
  • Prepared for SST
  • Started the second node (SST was copied by netcat over port 4444)
  • Confirmed both nodes were in the cluster

The setup can certainly be more involved in this, but this gives a simple illustration at what it takes to get things rolling.

The post Percona XtraDB Cluster: Setting up a simple cluster appeared first on MySQL Performance Blog.

Experimental GIT Mirrors of Percona XtraBackup, Percona Server plus Oracle MySQL trees

by

I recently blogged on setting up Experimental Git mirror of Oracle MySQL trees up on GitHub. I’m now happy to announce that there are also mirrors for:

I’ve also updated the Oracle MySQL GIT mirror to include MySQL 5.7 and the (now abandoned) MySQL 6.0. I include the abandoned 6.0 tree as it can provide useful archaeology as to where some code came from.

I’d love to hear about any positive/negative experiences using these mirrors. Hopefully shortly I’ll fix up the last two bits of missing metadata in the transfer: bzr branch names for each commit and the “bugs fixed” by each commit (what’s added with “bzr commit –fixes lp:1234″).

The post Experimental GIT Mirrors of Percona XtraBackup, Percona Server plus Oracle MySQL trees appeared first on MySQL Performance Blog.

Want to archive tables? Use Percona Toolkit’s pt-archiver

by

pt-archiverPercona Toolkit’s pt-archiver is one of the best utilities to archive the records from large tables to another tables or files. One interesting thing is that pt-archiver is a read-write tool. It deletes data from the source by default, so after archiving you don’t need to delete it separately.

As it is done by default, you should take care before actually running it on then production server. You can test your archiving jobs with the – dry-run  OR you can use the –no-delete option if you’re not sure about. The purpose of this script is mainly to archive old data from the table without impacting OLTP queries and insert the data into another table on the same/different server OR into a file in a format which is suitable for LOAD DATA INFILE.

How does pt-archiver select records to archive? 

Pt-archiver uses the index to select records from the table. The index is used to optimize repeated accesses to the table. Pt-archiver remembers the last row it retrieves from each SELECT statement, and uses it to construct a WHERE clause. It does this using the columns in the specified index that should allow MySQL to start the next SELECT where the last one ended – rather than potentially scanning from the beginning of the table with each successive SELECT.

If you want to run pt-archiver with a specific index you can use the “-i” option in –source DSN options. The “-i” option tells pt-archiver which index it should scan to archive. This appears in a FORCE INDEX or USE INDEX hint in the SELECT statements that are used to fetch rows to archive. If you don’t specify anything, pt-archiver will auto-discover a good index, preferring a PRIMARY KEY if one exists. Most of the time, without “-i” option, pt-archiver works well.

How to run pt-archiver?

For archive records into normal file, you can run something like

pt-archiver --source h=localhost,D=nil,t=test --file '/home/nilnandan/%Y-%m-%d-tabname' --where "name='nil'" --limit-1000

From archive records from one table to another table on same server or different, you can run something like

pt-archiver --source h=localhost,D=nil,t=test --dest h=fedora.vm --where "name='nil'" --limit-1000

Please check this before you use default file option (-F) in –source  http://www.percona.com/doc/percona-toolkit/2.1/pt-archiver.html#cmdoption-pt-archiver–dest

Archiving in a replication environment:

In the replication environment it’s really important that the slave should not lag for a long time. So for that, there are two options which we can use while archiving to control the slave lag on slave server.

–check-slave-lag : Pause archiving until the specified DSN’s slave lag is less than –max-lag. In this option, you can give slave details to connect slave lag. (i.e –check-slave-lag h=localhost,S=/tmp/mysql_sandbox29784.sock)

–max-lag : Pause archiving if the slave given by –check-slave-lag lags.

This options causes pt-archiver to look at the slave every time when it’s about to fetch another row. If the slave’s lag is greater than the option’s value, or if the slave isn’t running (so its lag is NULL), pt-archiver sleeps for –check-interval seconds and then looks at the lag again. It repeats until the slave is caught up, then proceeds to fetch and archive the row.

Some useful options for pt-archiver:

–for-update/-share-lock  : Adds the FOR UPDATE/LOCK IN SHARE MODE  modifier to SELECT statements.

–no-delete : Do not delete archived rows.

–plugin : Perl module name to use as a generic plugin.

–progress : Print progress information every X rows.

–statistics : Collect and print timing statistics.

–where : WHERE clause to limit which rows to archive (required).

nilnandan@nil:~$ pt-archiver --source h=localhost,D=nil,t=test,S=/tmp/mysql_sandbox29783.sock --file '/home/nilnandan/%Y-%m-%d-tabname' --where "name='nilnandan'" --limit=50000 --progress=50000 --txn-size=50000 --statistics --bulk-delete --max-lag=1 --check-interval=15 --check-slave-lag h=localhost,S=/tmp/mysql_sandbox29784.sock
TIME ELAPSED COUNT
2013-08-08T10:08:39 0 0
2013-08-08T10:09:25 46 50000
2013-08-08T10:10:32 113 100000
2013-08-08T10:11:41 182 148576
Started at 2013-08-08T10:08:39, ended at 2013-08-08T10:11:59
Source: D=nil,S=/tmp/mysql_sandbox29783.sock,h=localhost,t=test
SELECT 148576
INSERT 0
DELETE 148576
Action Count Time Pct
print_file 148576 18.2674 9.12
bulk_deleting 3 8.9535 4.47
select 4 2.9204 1.46
commit 3 0.0005 0.00
other 0 170.0719 84.95
nilnandan@nil:~$

Percona Toolkit’s pt-archiver works with Percona XtraDB Cluster (PXC) 5.5.28-23.7 and newer, but there are three limitations you should consider before archiving on a cluster. You can get more information here.

pt-archiver is extensible via a plugin mechanism. You can inject your own code to add advanced archiving logic that could be useful for archiving dependent data, applying complex business rules, or building a data warehouse during the archiving process. Follow this URL for more info on that.

Bugs related to pt-archiver: https://bugs.launchpad.net/percona-toolkit/+bugs?field.tag=pt-archiver

More details about pt-archiver: http://www.percona.com/doc/percona-toolkit/2.2/pt-archiver.html

The post Want to archive tables? Use Percona Toolkit’s pt-archiver appeared first on MySQL Performance Blog.

Percona celebrates its 7th anniversary by giving to open source ecosystem

by

Percona celebrates its 7th anniversaryToday we’re celebrating Percona’s 7th anniversary.  A lot has changed in these past 7 years – we have grown from a two-person outfit focused exclusively on consulting to a 100-person company with teammates in 22 different countries and 18 different states, now providing Support, Consulting, RemoteDBA, Server Development and Training services.

We also made our mark in open source software development, creating some of the most popular products for the MySQL ecosystem – Percona Toolkit, Percona Xtrabackup, Percona XtraDB Cluster, Percona Server and others. Additionally, we’re into our second year of hosting the Percona Live conference series for the MySQL community. We have grown to serve over 2,000 customers and I’m proud to say we could do it all in bootstrap mode without attracting outside investors and keeping the company owned by its employees.

So how are we celebrating our anniversary? We decided to celebrate by supporting the open source ecosystem, making donations to a number of open source initiatives that have helped us through all these years. We would not be here without you!

As such we’re supporting:

  • MariaDB Foundation for supporting MariaDB, one of the MySQL alternatives that we fully support at Percona.
  • Free Software Foundation as an organization instrumental to the success of the open source movement.
  • Linux Foundation for supporting Linux, by far the most popular platform among our customers.
  • Debian for creating a foundation for some of the most popular Linux distributions out there.
  • Jenkins for the Continuous Integration server we use for our development projects.
  • OpenSSH for software that helps us to access customer systems securely.
  • Drupal for powering our website as well as the websites of many of our customers.

We’re happy to enjoy the growth that’s allowing us to support other projects in our ecosystem. If you have the chance I encourage you do the same. There is a tremendous amount of work going into open source software, which is made free to use, but it is by far not free to create and maintain.

The post Percona celebrates its 7th anniversary by giving to open source ecosystem appeared first on MySQL Performance Blog.

Percona XtraBackup 2.1.4 is now available

by

Percona XtraBackup for MySQL Percona is glad to announce the release of Percona XtraBackup 2.1.4 on August 9th, 2013. Downloads are available from our download site here and Percona Software Repositories.

This release is the current GA (Generally Available) stable release in the 2.1 series. Percona XtraBackup is an open source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases.

New Features:

  • Percona XtraBackup has introduced additional options to handle the locking during the FLUSH TABLES WITH READ LOCK. These options can be used to minimize the amount of the time when MySQL operates in the read-only mode.
  • Percona XtraBackup has now been rebased on MySQL versions 5.1.70, 5.5.30, 5.6.11 and Percona Server versions 5.1.70-rel14.8 and 5.5.31-rel30.3 server versions.
  • In order to speed up the backup process, slave thread is not stopped during copying non-InnoDB data when innobackupex –no-lock option is used as using this option requires absence of DDL or DML to non-transaction tables during backup.
  • Source tarball (and Debian source) now include all MySQL source trees required for the build. This means internet connection during package build isn’t required anymore.
  • Two new options options, innobackupex –decrypt and innobackupex –decompress, have been implemented to make decryption and decompression processes more user friendly.

Bugs Fixed:

  • There were no 2.1.x release packages available for Ubuntu Raring. Bug fixed #1199257.
  • During the backup process loading tablespaces was started before the log copying, this could lead to a race between the datafiles state in the resulting backup and xtrabackup_logfile. Tablespace created at a sensitive time would be missing in both the backup itself and as the corresponding log record in xtrabackup_logfile, so it would not be created on innobackupex –apply-log either. Bug fixed #1177206.
  • Fixed the libssl.so.6 dependency issues in binary tarballs releases. Bug fixed #1172916.
  • innobackupex did not encrypt non-InnoDB files when doing local (i.e. non-streaming) backups. Bug fixed #1160778.
  • Difference in behavior between InnoDB 5.5 and 5.6 codebases in cases when a newly created tablespace has uninitialized first page at the time when XtraBackup opens it while creating a list of tablespaces to backup would cause assertion error. Bug fixed #1187071.
  • xbcrypt could sometimes fail when reading encrypted stream from a pipe or network. Bug fixed #1190610.
  • innobackupex could not prepare the backup if there was no xtrabackup_binary file in the backup directory and the xtrabackup binary was not specified explicitly with innobackupex –ibbackup option. Bug fixed #1199190.
  • Debug builds would fail due to compiler errors on Ubuntu Quantal/Raring builds. Fixed compiler warnings by backporting the corresponding changes from upstream. Bug fixed #1192454.
  • innobackupex would terminate with an error if innobackupex –safe-slave-backup option was used for backing up the master server. Bug fixed #1190716.
  • Under some circumstances XtraBackup could fail on a backup prepare with innodb_flush_method=O_DIRECT when XFS filesystem was being used. Bug fixed #1190779.
  • Percona XtraBackup didn’t recognize checkpoint #0 as a valid checkpoint on xtrabackup –prepare which would cause an error. Bug fixed #1196475.
  • Percona XtraBackup didn’t recognize the O_DIRECT_NO_FSYNC value for innodb_flush_method which was introduced in MySQL 5.6.7. Fixed by adding the value to the list of supported values for innodb_flush_method in xtrabackup_56. Bug fixed #1206363.
  • innobackupex would terminate if innobackupex –galera-info option was specified when backing up non-galera server. Bug fixed #1192347.

Other bug fixes: bug fixed #1097434, bug fixed #1201599, bug fixed #1198220, bug fixed #1097444, bug fixed #1042796, bug fixed #1204463, bug fixed #1197644, bug fixed #1197249, bug fixed #1196894, bug fixed #1194813, bug fixed #1183500, bug fixed #1181432, bug fixed #1201686, bug fixed #1182995, bug fixed #1204085, bug fixed #1204083, bug fixed #1204075, bug fixed #1203672, bug fixed #1190876, bug fixed #1194879, bug fixed #1194837.

Known issues:

  • Backups of MySQL/Percona Server 5.6 versions prior to 5.6.11 cannot be prepared with Percona XtraBackup 2.1.4. Until the upstream bug #69780 is fixed and merged into Percona XtraBackup, Percona XtraBackup 2.1.3 should be used to prepare and restore such backups. This issue is reported as bug #1203669.

Release notes with all the bugfixes for Percona XtraBackup 2.1.4 are available in our online documentation. All of Percona‘s software is open source and free, all the details of the release can be found in the 2.1.4 milestone at Launchpad. Bugs can be reported on the launchpad bug tracker.

The post Percona XtraBackup 2.1.4 is now available appeared first on MySQL Performance Blog.