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;
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/
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/
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
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
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
many many thanks!
Davide
Italy