Configuring MySQL Replication on ssl connection

[danjde]

Hi friends,
I'm wondering about the MySQL Replication with ssl access (port 60319) configuration on two VPS Debian Jessie and MySQL 5.5.54-0+deb8u1;

I've followed this tutorial but now I've some issues with the user connection and (perhaps) with the firewall (shorewall). But for the moment the problems there are also with firewalls stopped.

I've created the user "replication":

Code: Select all

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'PASSWORD-USER-REPLICATION-25-character' REQUIRE SSL;

And configured MASTER and SLAVE (as below), but just if I try to login manually with mysql from the SLAVE obtain an error:

mysql --user=replication --host=91.205.175.213 --port=60319 -p
Enter password:
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0


and if I try to connect from the MASTER obtain this error:

mysql -u replication -p
Enter password:
ERROR 1045 (28000): Access denied for user 'replication'@'localhost' (using password: YES)

These MySQL configurations:

MASTER:

Code: Select all

[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0
[mysqld]
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
key_buffer		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit	= 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
server-id		= 1
log_bin			= /var/log/mysql/replication.log
binlog-format = mixed
innodb_flush_log_at_trx_commit=1
sync_binlog = 1
expire_logs_days	= 7
max_binlog_size         = 100M
binlog_do_db		= mailserver
ssl
ssl-ca=/etc/mysql/certificati/ca-cert.pem
ssl-cert=/etc/mysql/certificati/server-cert.pem
ssl-key=/etc/mysql/certificati/server-key.pem
[mysqldump]
quick
quote-names
max_allowed_packet	= 16M
[mysql]
[isamchk]
key_buffer		= 16M
!includedir /etc/mysql/conf.d/

SLAVE:

Code: Select all

[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0
[mysqld]
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
key_buffer		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit	= 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
server-id		= 2
log_bin			= /var/log/mysql/replication.log
relay-log 		= /var/log/mysql/replication-relay.log
log-slave-updates 	= 1
read-only 		= 1
expire_logs_days	= 7
max_binlog_size         = 100M
binlog_do_db            = mailserver
ssl
[mysqldump]
quick
quote-names
max_allowed_packet	= 16M
[mysql]
[isamchk]
key_buffer		= 16M
!includedir /etc/mysql/conf.d/

The certs was generated by:

Create CA certificate (4096 bit):

Code: Select all

openssl genrsa 4096 > ca-key.pem
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

Create server certificate (4096 bit):

Code: Select all

openssl req -newkey rsa:4096 -days 1000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Create client certificate (4096 bit):

Code: Select all

openssl req -newkey rsa:4096 -days 1000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

I'm going crazy!

many many thanks!

Davide
Italy