Better ssl management for schema-change tool?

Polyglot Projects
1

Hi
Today it is not possible to configure if we need ssl or not to connect to our Mysql database on
percona schema management change too. It would be nice to have a SSL option.

I was able to go around by configuring ssl in our my.cnf client section (percona script use the ‘client’ section).

My problem is our dev, local docker mysql is not SSL enabled, but our production one are,
since percona tools use libmysql 5.6 ssl mode can only be deactivated our required, so I can’t
have a migration docker image that would work for both.
I had to rely on a trick, by removing the my.cnf if it is a dev url.

Can you upgrade to libmysql 5.7, where I would be able to use ssl mode PREFERRED
that should work on both ssl and non-ssl connection ?
Or allow us to to pass a ssl option to the script.

Thanks.

1 Like
2

Hello @cma, you can use any newer MySQL version to talk to older versions. For example, you can use the Percona MySQL docker 8.0 client and talk to MySQL 5.7 or 5.6. You should be able to make SSL work this way.

1 Like
3

It doesn’t seems to work with mysql-lib 5.7 or 8.0
pt-online-schema-change --alter-foreign-keys-method=auto --nocheck-unique-key-change --alter=“ADD COLUMN address VARCHAR(255) NULL” --password=xxx --execute h=mysql,P=3306,u=root,D=database,t=data
with my.cnf
[client]
ssl-mode=PREFERRED
ssl-ca=/etc/pki/ca-trust/extracted/pem/tls-ca-bundle.pem

return
Unknown option to ssl-mode: PREFERRED

The only way I was able to make it work with ssl is
with a my.cnf file that has
ssl-mode=REQUIRED

or by editing pt-online-schema-change script
and add mysql_ssl=1
2287 $dsn = ‘DBI:mysql:’ . ( $info->{D} || ‘’ ) . ‘;’
2288 . join(‘;’, map { “$opts{$}->{dsn}=$info->{$}” }
2289 grep { defined $info->{$_} }
2290 qw(F h P S A))
2291 . ‘;mysql_read_default_group=client;mysql_ssl=1’
2292 . ($info->{L} ? ‘;mysql_local_infile=1’ : ‘’);

It would be nice to have an option to the pt-online-schema-change to pass
additional custom flags to the DBI:Mysql (mysql_ssl=1;mysql_ssl_ca_file=… …)

Thanks

1 Like
4

@cma, I just tested this and I had no issues connecting pt-osc via SSL without changing any code.

[client]
ssl_ca = /etc/ssl/mysql/ca.pem

mysql> ALTER USER checksum@'%' REQUIRE SSL;

# pt-online-schema-change --alter="ADD COLUMN c varchar(10) DEFAULT NULL" h=127.0.0.1,u=checksum,p=checkSum#1,D=imdb,t=title --execute
Found 1 slaves:
db2-T10 -> 10.11.2.210:socket
Will check slave lag on:
db2-T10 -> 10.11.2.210:socket
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `imdb`.`title`...
Creating new table...
Created new table imdb._title_new OK.
Altering new table...
Altered `imdb`.`_title_new` OK.
2021-01-26T01:25:52 Creating triggers...
2021-01-26T01:25:52 Created triggers OK.
2021-01-26T01:25:52 Copying approximately 1548344 rows...
2021-01-26T01:26:21 Copied rows OK.
2021-01-26T01:26:21 Analyzing new table...
2021-01-26T01:26:21 Swapping tables...
2021-01-26T01:26:21 Swapped original and new tables OK.
2021-01-26T01:26:21 Dropping old table...
2021-01-26T01:26:21 Dropped old table `imdb`.`_title_old` OK.
2021-01-26T01:26:21 Dropping triggers...
2021-01-26T01:26:21 Dropped triggers OK.
Successfully altered `imdb`.`title`.

This is how it should work. Just modify your ~/.my.cnf to include the CA and it’ll work just fine via SSL.
If I comment out the ssl_ca in the my.cnf, I get this:

# pt-online-schema-change --alter="ADD COLUMN c varchar(10) DEFAULT NULL" h=127.0.0.1,u=checksum,p=checkSum#1,D=imdb,t=title --execute
Cannot connect to MySQL: DBI connect('imdb;host=127.0.0.1;mysql_read_default_group=client','checksum',...) failed: Access denied for user 'checksum'@'127.0.0.1' (using password: YES) at /bin/pt-online-schema-change line 2345.

So it indeed does work fine.

2 Likes
5

Sorry for the misunderstanding,
pt-osc work with ssl with a config in my.cnf
My request was to be able to enable ssl using parameter to pt-osc directly instead of relying on my.cnf.
Our ‘immutable’ database migration container use liquibase+liquibase-percona+pt-osc to update
local non ssl mysql database and ‘production’ database where ssl is required.
Today my script remove the my.cnf when it run for a non-ssl database, but I would like to rely
entirely on ‘application parameter’ than on removing or changing a config file.

6

@cma, understood. Unfortunately, that’s not how the Percona toolkit is architected. The idea is to not have any native mysql options as part of the code in the toolkit. If you wish, feel free to open a feature request at https://jira.percona.com/
You can create a different my.cnf file just for toolkit, and pass --defaults-file parameter

2 Likes