Tikejhya: Ashish Nepal

Knowledgebase

Month: February 2012

MySql Replication with SSL

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

MySql Replication with SSL

In master Server

mkdir ssl
cd ssl/

Now in Master creating CA Cert

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem
    (While this provide CN = remember what you provided.)

Now Being in Master creating Cert and Key for Master

    Master Server = testos.ashishnepal.com
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout testos.ashishnepal.com-key.pem > testos.ashishnepal.com-req.pem
    (While this provide CN = something diff to previous one.)
openssl x509 -req -in servera-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > testos.ashishnepal.com-cert.pem

Now being in Master creating Cert and Key for Client Server

    Server=slave.ashishnepal.com
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout slave.ashishnepal.com-key.pem > slave.ashishnepal.com-req.pem
openssl x509 -req -in slave.ashishnepal.com-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > slave.ashishnepal.com-cert.pem

vi /etc/my.cnf

[client]

ssl-ca=/data/db/ssl/ca-cert.pem
ssl-cert=/data/db/ssl/slave.ashishnepal.com-cert.pem
ssl-key=/data/db/ssl/slave.ashishnepal.com-key.pem
socket=/data/db/mysql.sock
port=3306


[mysqld]

ssl-ca=/data/db/ssl/ca-cert.pem
ssl-cert=/data/db/ssl/testos.ashishnepal.com-cert.pem
ssl-key=/data/db/ssl/testos.ashishnepal.com-key.pem

for me below given didnt work, had to comment

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

# innodb_old_blocks_time = 1000

For me these parameter’s worked

[client]
ssl-ca=/data/db/ssl/ca-cert.pem
ssl-cert=/data/db/ssl/slave.ashishnepal.com-cert.pem
ssl-key=/data/db/ssl/slave.ashishnepal.com-key.pem
socket=/data/db/mysql.sock
port=3306

[mysqld]
ssl-ca=/data/db/ssl/ca-cert.pem
ssl-cert=/data/db/ssl/testos.ashishnepal.com-cert.pem
ssl-key=/data/db/ssl/testos.ashishnepal.com-key.pem

datadir                 = /data/db
socket                  = /data/db/mysql.sock
user                    = mysql

port                    = 3306
bind-address            = 0.0.0.0

key_buffer                      = 256M
key_buffer_size                 = 128M
max_allowed_packet              = 8M
table_cache                     = 8192
sort_buffer_size                = 2M
read_buffer_size                = 2M
read_rnd_buffer_size            = 8M
myisam_sort_buffer_size         = 64M
thread_cache                    = 64
query_cache_size                = 256M
query_cache_limit               = 2M
thread_concurrency              = 16
tmp_table_size                  = 32M

log-bin                                 = testos.ashishnepal.com-bin
server_id                               = 1
max-binlog-size                         = 512M
expire_logs_days                        = 7

auto_increment_increment                = 3
auto_increment_offset                   = 1



skip-name-resolve
skip-host-cache
innodb_data_home_dir                    = /data/db
innodb_data_file_path                   = testos.ashishnepal.com-ibdata1:1024M:autoextend
innodb_buffer_pool_size                 = 409M
innodb_additional_mem_pool_size         = 64M
innodb_log_file_size                    = 512M
innodb_log_buffer_size                  = 16M
innodb_flush_log_at_trx_commit          = 1
innodb_lock_wait_timeout                = 50
innodb_thread_concurrency               = 16
innodb_flush_method                     = O_DSYNC
innodb_file_per_table


log-warnings                            = 0
log-error                               = mysql.err
log-slow-admin-statements
log-slow-queries
sync_binlog                             = 1
relay-log                               = testos.ashishnepal.com-relay-bin
relay-log-index                         = testos.ashishnepal.com-relay-bin.index
master-info-file                        = testos.ashishnepal.com-master.info
relay-log-info-file                     = testos.ashishnepal.com-relay-log.info
binlog-ignore-db                        = mysql
binlog-ignore-db                        = test
replicate-ignore-db                     = mysql
replicate-ignore-db                     = test

[mysqld_safe]
log-error                               = /var/log/mysqld.log
pid-file                                = /var/run/mysqld/mysqld.pid

Being in Master Shell
mysql>

GRANT USAGE ON *.* TO 'slave_user'@'21x.5x.12x.16x' REQUIRE SSL;
flush privileges;

How to verify if MySQL replication is ssl enabled

mysql> show status like '%cipher%'G;
*************************** 1. row ***************************
Variable_name: Ssl_cipher
        Value: DHE-RSA-A6-SHA
*************************** 2. row ***************************
Variable_name: Ssl_cipher_list
        Value: DHE-RSA-AES256-SHA:DHE-DSS-AES256-SHA:AES25-MD5:EXP-KRB5-RC2-CBC-SHA:EXP-KRB5-DES-CBC-SHA:EXP-EDH-RSA-DES-CBC-SHA:EXP-EDH-DSS-DES-CBC-SHA:EXP-DES-CBC-SHA:EXP-RC2-CBC-MD5:EXP-KRB5-RC4-MD5:EXP-KRB5-RC4-S
2 rows in set (0.00 sec)

(note: There should be simillar value's in cipher.)

Being in Slave server vi /etc/my.cnf

[client]

ssl-ca=/data/db/ssl/ca-cert.pem
ssl-cert=/data/db/ssl/slave.ashishnepal.com-cert.pem
ssl-key=/data/db/ssl/slave.ashishnepal.com-key.pem

Slave server worked with

socket=/var/lib/mysql/mysql.sock
port=3306

[mysqld]

socket=/var/lib/mysql/mysql.sock
user=mysql

# backwards compatibility with mysql 3.x clients and mysqlclient10 package
old_passwords=1

port            = 3306
bind-address    = 0.0.0.0
datadir         = /data/db

socket          = /var/lib/mysql/mysql.sock

key_buffer              = 256M
max_allowed_packet      = 8M
table_cache             = 8192
sort_buffer             = 2M
read_buffer             = 2M
read_rnd_buffer         = 8M
myisam_sort_buffer      = 64M
thread_cache            = 64
query_cache_limit       = 2M
thread_concurrency      = 16
tmp_table_size          = 32M

log-bin                 = slave-bin
server-id               = 3
max-binlog-size         = 256M
expire_logs_days        = 14

auto_increment_increment        = 3
auto_increment_offset           = 2

log-warnings            = 1
log-error                       = mysql.err
log-slow-admin-statements
log-slow-queries
slave-skip-errors = 1062,1050,1060

relay-log = /binlog/slave-relay-bin
relay-log-index = /binlog/slave-relay-bin.index
master-info-file = /binlog/mysql-master.info
relay-log-info-file = /binlog/slave-relay-log.info

In Slave shell mysql>

stop slave;
CHANGE MASTER TO MASTER_HOST='testos.ashishnepal.com', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='testos.000040', MASTER_LOG_POS=98, MASTER_SSL=1, MASTER_SSL_CA = '/data/db/ssl/ca-cert.pem', MASTER_SSL_CERT = '/data/db/ssl/slave.ashishnepal.com-cert.pem', MASTER_SSL_KEY = '/data/db/ssl/slave.ashishnepal.com-key.pem';


Note:
what is Master_log_file and master log position;
In master
mysql> show master statusG;
*************************** 1. row ***************************
            File: testos.000041  -->> this is master_log_file
        Position: 190            -->> this is master log position
    Binlog_Do_DB:
Binlog_Ignore_DB: mysql,test
1 row in set (0.00 sec)

start slave;
show slave statusG;

Change aging password

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

I faced the issue and hope this will be helpful for someone

I had key based authentication for sync user with strict password policy, and after the password expiry day,
I got following error

WARNING: Your password has expired.
Password change required but no TTY available.
rsync: connection unexpectedly closed (0 bytes received so far) [sender]
rsync error: error in rsync protocol data stream (code 12) at io.c(463) [sender=2.6.8]

Solution:

chage -m 0 -M 99999 -I -1 -E -1 user

# check user password expiry date
chage -l ashishnepal

# force password next time
chage -m 0 ashishnepal

# Never expire
chage -m 99999 ashishnepal

Powered by WordPress & Theme by Anders Norén