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.infoIn 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;
