Home > linux, mysql, ssl, web > Workaround for mysqldump SSL error 2026 bug #27669

Workaround for mysqldump SSL error 2026 bug #27669

I had not previously played aroung much with MySQL replication, but got the chance to do so recently. I’m doing some testing with a new mail filter setup composed of amavisd-new, SpamAssassin, and some other SA modules running through Postfix on Debian Etch. The setup uses Maia Mailguard as a web front-end and management system, including per-user settings and quarantines. We’re using MySQL for the database backend for Maia for storing quarantines, per-user settings and the like, but wanted to have a dual-MX setup with a secondary MX sitting in a remote site.

So, we needed some way to keep the MySQL data between the two servers in sync. I had looked at DRBD and linux-ha, but in the end decided to go with a dual-master MySQL replication setup (howto1, howto2), using the built-in SSL capabilities in MySQL to encrypt the replication traffic over the WAN. After a bit of tweaking, I got that going, but then noticed that I could no longer perform mysqldump commands on the databases when running the command locally. I got the following error message whenever I tried a mysqldump command:

 

Got error: 2026: SSL connection error when trying to connect

I wasn’t specifying any SSL parameters in the mysqldump command, though, and while I did configure the replication users to require SSL in their user setup, the mysqldump user did not have an SSL requirement. I got to the official MySQL bug report for this error(#27669) as well as the patch link. But to be honest, that didn’t really get me anywhere. The bug report indicates that this behaviour has been corrected in MySQL release 5.0.42, and I was running release 5.0.32.

I updated the mysql-client package on a test machine to 5.0.75 and tried the mysqldump again; the same error still occurred. I didn’t want to upgrade the actual mysql-server package as well, so I looked elsewhere for a fix. It occurred to me that, in order for the MySQL replication to use SSL, I had specified the SSL-related options (ssl-ca and so forth) in the [client] section of the global configuration file at /etc/mysql/my.cnf. As mysqldump starts a MySQL client connection, it would stand to reason that it uses the [client] configuration settings that it would find in the global config file, and hence would try to use SSL.

I wanted to get mysqldump to not use SSL connections, but still wanted my replication traffic to use SSL. Since the mysqld daemon runs under the mysql user account, I moved the ssl-related config options out of the [client] section of the global config file of /etc/mysql/my.cnf, created a new .my.cnf config file in the mysql user’s $HOME directory (/var/lib/mysql on my system), and entered just the ssl-related options in a [client] section of that .my.cnf file. I reloaded the mysqld daemon and confirmed that the slave connection to the remote server came up properly. It did; the configuration options for the mysql user includes both the global config file of /etc/mysql/my.cnf and the per-user .my.cnf file I had just created, so it is able to pick up the ssl-related options in the [client] section of the per-user settings.

NOTE: The ssl-related options I am talking about here are only those configured in the [client] section. The ssl-related options in the [mysqld] section of the global config file can stay where they are. It should work to have those present in the local mysql user’s .my.cnf config file, but I did not test this.

Since no ssl-related options were specified in the global [client] configuration anymore, and no per-user settings were present for the user I was using for the mysqldump command, the command connected without SSL, the SSL error 2026 messages went away, and I was again able to successfully perform MySQL backups from the local machine.

Bitcoin tip address for this post: 15ZhSjpB8iDRHvzgXphpJ9AhkJUPJw5uE5

Advertisements
Categories: linux, mysql, ssl, web
  1. 2011-06-28 at 10:52

    Hi,

    This article should come in pretty handy for me soon. I’m looking at setting up something similar – two MXes running Maia.

    I’ve never done any DB replication like this. How is the performance over a WAN? In this dual-master setup, each MX is writing to its own local DB first, and the differences are ‘synced’?

    Does your Maia configuration just point to the local DB?

    Hopefully you can give me some insight on best practices. Thanks for the article! E-mail is preferable 😀

    Josh

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: