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

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
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
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

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.

Getting Percona PAM to work with Percona Server & its client apps

Percona Server for MySQLPercona Server is bundled with the PAM plugin which opens a plethora of ways to authenticate to MySQL such as restricting time when users can connect to MySQL, authenticate via a USB key, authenticate to an external authentication system such as LDAP and many, many more PAM compatible mechanisms.

If you want to use PAM authentication on the community version of MySQL, you may follow the instructions here to get it working on your system. If you want to test PAM authentication, the simplest way is to authenticate via /etc/shadow. The steps do so can be found in here or you can follow the steps below.

Here’s a primer for setting up Percona PAM on CentOS 6 to authenticate via /etc/shadow:

1. Install Percona yum repository

# rpm -Uvh http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
Retrieving http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
Preparing...                ########################################### [100%]
   1:percona-release        ########################################### [100%]

2. Install Percona Server 5.5

# yum install Percona-Server-server-55 Percona-Server-client-55
snipped for brevity
  Percona-Server-client-55.x86_64 0:5.5.32-rel31.0.549.rhel6 Percona-Server-server-55.x86_64 0:5.5.32-rel31.0.549.rhel6
Dependency Installed:
  Percona-Server-shared-55.x86_64 0:5.5.32-rel31.0.549.rhel6   perl.x86_64 4:5.10.1-131.el6_4   perl-Module-Pluggable.x86_64 1:3.90-131.el6_4   perl-Pod-Escapes.x86_64 1:1.04-131.el6_4   perl-Pod-Simple.x86_64 1:3.13-131.el6_4   perl-libs.x86_64 4:5.10.1-131.el6_4
  perl-version.x86_64 3:0.77-131.el6_4

3. Start Percona Server 5.5

# service mysql start
Starting MySQL (Percona Server)...... SUCCESS!

4. From the mysql console, enable auth_pam and auth_pam_compat plugins. These PAM plugins will be discussed in detail later.

mysql> INSTALL PLUGIN auth_pam SONAME 'auth_pam.so';
Query OK, 0 rows affected (0.00 sec)
mysql> INSTALL PLUGIN auth_pam_compat SONAME 'auth_pam_compat.so';
Query OK, 0 rows affected (0.00 sec)
| Name                           | Status   | Type               | Library            | License |
| binlog                         | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
snipped for brevity
| auth_pam                       | ACTIVE   | AUTHENTICATION     | auth_pam.so        | GPL     |
| auth_pam_compat                | ACTIVE   | AUTHENTICATION     | auth_pam_compat.so | GPL     |
42 rows in set (0.01 sec)

5. From the MySQL console, create two users that will authenticate using auth_pam and auth_pam_compat respectively. You also need to delete anonymous users:

mysql> CREATE USER ap_user IDENTIFIED WITH auth_pam;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER apc_user IDENTIFIED WITH auth_pam_compat;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM mysql.user WHERE USER='';
Query OK, 2 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

6. Configure Percona Server to authenticate via /etc/shadow by creating a PAM config file in /etc/pam.d/mysqld with the following content:

auth       required     pam_warn.so
auth       required     pam_unix.so audit
account    required     pam_unix.so audit

7. Ensure Percona Server can read /etc/shadow by changing the group ownership and permissions of it

#chgrp mysql /etc/shadow
#chmod g+r /etc/shadow

8. Create system users and respective passwords. The usernames should match the users created from the MySQL console

#useradd ap_user
#passwd ap_user
#useradd apc_user
#passwd apc_user

9. Test if you can connect to Percona Server using the Unix passwords of ap_user and apc_user:

# mysql -u ap_user -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 9
Server version: 5.5.32-31.0 Percona Server (GPL), Release rel31.0, Revision 549
Copyright (c) 2009-2013 Percona Ireland Ltd.
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
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
| USER()            | CURRENT_USER() |
| ap_user@localhost | ap_user@%      |
1 row in set (0.00 sec)
# mysql --enable-cleartext-plugin -u apc_user -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 11
Server version: 5.5.32-31.0 Percona Server (GPL), Release rel31.0, Revision 549
Copyright (c) 2009-2013 Percona Ireland Ltd.
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
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
| USER()             | CURRENT_USER() |
| apc_user@localhost | apc_user@%     |
1 row in set (0.00 sec)

10. Done.

The Percona PAM plugin authentication plugin has 2 types of plugins. The first is auth_pam plugin which is a full-featured implementation of the PAM plugin that uses the dialog plugin. The other is auth_pam_compat which uses mysql_clear_password plugin which Oracle provides.

The drawback of this plugin is it’s only able to accept a password as an input and credentials are sent in cleartext which is not secure. Now, why would you need to choose between one or the other? It all depends if the client supports any of those plugins or worse, none at all. To create a user to authenticate via auth_pam, on the MySQL console run:
CREATE USER <auth_pam_user> IDENTIFIED WITH auth_pam;
For auth_pam_compat, run:
CREATE USER <auth_pam_compat_user> IDENTIFIED WITH auth_pam_compat;

As of now, only Percona Server’s mysql client and an older version of HeidiSQL(version 7), a GUI MySQL client for Windows, are able to authenticate over PAM via the auth_pam plugin by default.

So, if you try to connect to MySQL using Perl, PHP, Ruby, Python and the like, you will receive this error: “Client does not support authentication protocol requested by server; consider upgrading MySQL client.”

The good news is that if the client uses libmysqlclient library to connect via MySQL, you can recompile the client’s source code to use the libmysqlclient library of Percona Server to make it compatible. This involves installing Percona Server development library, compiler tools, and development libraries followed by compiling and installing the client’s source code. This maybe an easy task for a single server but if you have a large deployment of servers and clients, it would be wiser to build RPMs or DEBs instead.

To give you an idea of which clients depend on libmysqlclient, if you’re using CentOS and yum-utils is installed, you can run: repoquery -q –whatrequires mysql-libs

For Ubuntu, just run: apt-cache rdepends libmysqlclient18

Below is an example of rebuilding and installing perl’s DBD MySQL package to make it compatible with the auth_pam plugin:

1. Install Percona yum repository and Percona Server 5.5 development library:

# rpm -Uvh http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
# yum install Percona-Server-devel-55

2. Install rpm-build, the package used to build RPMs

# yum install rpm-build

3. Download and install perl-DBD-MySQL Source RPM.

# wget http://vault.centos.org/6.4/os/Source/SPackages/perl-DBD-MySQL-4.013-3.el6.src.rpm
# rpm -Uvh perl-DBD-MySQL-4.013-3.el6.src.rpm

The source will be installed under rpmbuild of the current directory.

4. Install compilers and dependencies:

yum install Percona-Server-client-55 perl perl-DBI zlib-devel perl-ExtUtils-MakeMaker  make gcc openssl-devel

5. Build the RPM file:

cd rpmbuild/SPECS/
rpmbuild -bb rpmbuild/SPECS/perl-DBD-MySQL.spec

6. Install the RPM file:

rpm -Uvh rpmbuild/RPMS/x86_64/perl-DBD-MySQL-4.013-3.el6.x86_64.rpm

7. Test by creating a perl script and running it:

use DBI;
$dbh = DBI->connect("DBI:mysql:$database:$host", $username, $password) or die "$DBI::errstrn";
$query = $dbh->prepare($sql) or die "$dbh->errstrn";
$query->execute or die "$query->errstr";
while (@row = $query->fetchrow_array()) {
        $table = $row[0];
        print "$tablen";

#chmod +x testpam.pl
#perl testpam.pl
DBI connect('mysql:','ap_user',...) failed: Authentication plugin 'dialog' cannot be loaded: /usr/lib64/mysql/plugin/dialog.so: cannot open shared object file: No such file or directory at test.pl line 10
Authentication plugin 'dialog' cannot be loaded: /usr/lib64/mysql/plugin/dialog.so: cannot open shared object file: No such file or directory

8. Troubleshooting
If you get the error above, it means you need to install the dialog plugin which is bundled in the Percona-Server-server-55 RPM. If you don’t want to install the RPM, you may opt to download and extract it from the binary tar file provided in the Percona website and place the plugin on the /usr/lib64/mysql/plugin directory. In this example, we will install the Percona-Server-server-55 RPM

#yum install Percona-Server-server-55
#perl testpam.pl

9. Done.

As for the auth_pam_compat plugin, if the clients use the libmysqlclient.so.18 and above, you do not need to recompile the client’s source code such as described above.

On Ubuntu 12.04, there’s no need to recompile libdbd-mysql-perl package:

# dpkg -L libdbd-mysql-perl
snipped for brevity
#ldd /usr/lib/perl5/auto/DBD/mysql/mysql.so
linux-vdso.so.1 =>  (0x00007fff943ff000)
libmysqlclient.so.18 => /usr/lib/libmysqlclient.so.18 (0x00007f20517bf000)
snipped for brevity
libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x00007f20504aa000)

On the other hand, perl-DBD-MySQL of CentOS 6 needs to be recompiled because it depends on libmysqlclient.so.16:

# rpm -ql perl-DBD-MySQL
snipped for brevity
snipped for brevity
# ldd /usr/lib64/perl5/auto/DBD/mysql/mysql.so
linux-vdso.so.1 =&gt; (0x00007fff847ff000)
libmysqlclient.so.16 =&gt; /usr/lib64/libmysqlclient.so.16 (0x00007fa2471c3000)
snipped for brevity
libselinux.so.1 =&gt; /lib64/libselinux.so.1 (0x00007fa244730000)

Once you’re able to resolved the issue above, depending on your client, you can use the auth_pam_compat plugin by enabling the use of the cleartext plugin via environment variable, program option or placing the configuration on my.cnf:

Using the environment variable:

$ mysql -h -u apc_user

Enable the plugin as an option. It’s supported on mysql, mysqladmin and mysqlslap.

$ mysql --enable-cleartext-plugin -h -u apc_user

As a my.cnf config:


$ mysql -h -u apc_user

For more information on the cleartext authentication, click here.

A practical example of using auth_pam_compat is being able to use MySQL Workbench 5.2 over PAM on a Mac:

$ /Applications/MySQLWorkbench.app/Contents/MacOS/MySQLWorkbench

and on Linux:

$ mysql-workbench

Do note that on MySQL Workbench 5.2, you need to store the password in the keychain for authentication to PAM to work. Also, sadly, Percona PAM does not work on MySQL Workbench 5.2 on Windows as confirmed in the bug I reported last February 2013.

As for the latest release of MySQL Workbench 6.0, there’s an advanced option to enable the cleartext authentication plugin when setting up connections. However, not all features work with auth_pam_compat. For example, SQL Editor does not connect successfully with auth_pam_compat plugin but the Reverse Engineer tool works fine with the plugin.

If you want to use Percona PAM, you need to test first if your MySQL clients support it and most likely you will need to recompile these clients to make it work. If your only choice is auth_pam_compat but you are weary of using it because credentials are sent in cleartext, you can add layer of security by enabling SSL encryption on MySQL. One such example is JDBC, where cleartext authentication is permissible, but only if the connection is encrypted.

The post Getting Percona PAM to work with Percona Server & its client apps appeared first on MySQL Performance Blog.

Percona University at Washington, D.C. – Sept. 12

Percona University at Washington, D.C. - Sept. 12Following our events earlier this year in Raleigh, Montevideo, Buenos Aires, Toronto and Portland, we bring Percona University to Washington, D.C. on September 12.

This free one-day technical education event will cover many MySQL ecosystem technologies, including: Percona Server, MariaDB, MySQL 5.6, Percona XtraDB Cluster, Tungsten Replicator, Percona XtraBackup, MySQL Cluster and Percona Toolkit. We also will learn about Apache Hadoop and how it can be most successfully used with MySQL environments.

Matt Yonkovit, VP of Percona MySQL Consulting, will give his great talk on the “5 minute DBA,” drawing from a wealth of practical DBA tips for developers and system administrators – professionals who must step up to the plate on occasion and perform DBA tasks.

Ryan Huddleston, director of Percona Remote DBA, will talk about “MySQL Backups in the Real World,” sharing best practices you’ll want to follow if you never want to lose your data again – as well as being able to recover from various data incidents as quickly as possible.

Our special guest for this event is Baron Schwartz, co-Founder and CEO of VividCortex, who will be speaking about Using MySQL with Go. Go Programming language is quickly gaining popularity and we’re excited to bring the most up to date information about using Go with MySQL database.

If you will be in the Washington, D.C. area on September 12, I’d love to see you at this event – space is limited though, so please register now.  And if you’re interested in Percona University events and would like to bring one to your city, please fill out this form to let us know – especially if you can help us by hosting or promoting the event.

The post Percona University at Washington, D.C. – Sept. 12 appeared first on MySQL Performance Blog.

MySQL Security: Armoring Your Dolphin

MySQL Security: Armoring Your DolphinMy colleague and teammate Ernie Souhrada will be presenting a webinar on Wednesday, August 21, 2013 at 10 a.m. PDT titled “MySQL Security: Armoring Your Dolphin.”

This is a popular topic with news breaking routinely that yet another Internet company has leaked private data of one form or another. Ernie’s webinar will be a great overview of security MySQL from top to bottom, including changes related to security in the 5.6 release.

Topics to be covered include:

  • Basic security concepts
  • Security above the MySQL layer (network, hardware, OS, etc.)
  • Tips for application design
  • A more secure MySQL configuration
  • Security-related changes in MySQL 5.6

Attendees will leave this presentation knowing where to start when identifying vulnerability in their systems.

Be sure to register for the webinar in advance!

The post MySQL Security: Armoring Your Dolphin appeared first on MySQL Performance Blog.