MySQL Database Replication With SSL Encryption

Discussion in 'General' started by pebkac, Nov 6, 2012.

  1. pebkac

    pebkac Member HowtoForge Supporter

    I have two ISPConfig 3 Servers running Ubuntu 12.04. I followed this How-to to a tee, minus the install of MySQL since it is already installed. http://www.howtoforge.com/how-to-se...ication-with-ssl-encryption-on-debian-squeeze

    When I run the "SHOW SLAVE STATUS \G" I get this:
    Code:
    mysql> SHOW SLAVE STATUS \G
    *************************** 1. row ***************************
                   Slave_IO_State: Connecting to master
                      Master_Host: IP_ADDRESS
                      Master_User: slave_user
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000003
              Read_Master_Log_Pos: 107
                   Relay_Log_File: mysqld-relay-bin.000002
                    Relay_Log_Pos: 4
            Relay_Master_Log_File: mysql-bin.000003
                 Slave_IO_Running: Connecting
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: c1uhstem
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 107
                  Relay_Log_Space: 264
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: Yes
               Master_SSL_CA_File: /etc/mysql/newcerts/ca-cert.pem
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: /etc/mysql/newcerts/client-cert.pem
                Master_SSL_Cipher: 
                   Master_SSL_Key: /etc/mysql/newcerts/client-key.pem
            Seconds_Behind_Master: NULL
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 1045
                    Last_IO_Error: error connecting to master 'slave_user@IP_ADDRESS:3306' - retry-time: 60  retries: 86400
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 0
    1 row in set (0.02 sec)
    I try to connect to the master sql server for the slave I get this:
    Code:
    root@dor:~# mysql --ssl -h ip_address -u slave_user -p
    Enter password: 
    ERROR 1045 (28000): Access denied for user 'slave_user'@'ec2-184-169-151-252.us-west-1.compute.amazonaws.com' (using password: YES)
    From the master server directly gives this also:
    Code:
    root@abda:/home/sysadmin# mysql -u slave_user -p
    Enter password: 
    ERROR 1045 (28000): Access denied for user 'slave_user'@'localhost' (using password: YES)
    Here is the master my.cnf:
    Code:
    root@abda:/home/sysadmin# cat /etc/mysql/my.cnf
    #
    # The MySQL database server configuration file.
    #
    # You can copy this to one of:
    # - "/etc/mysql/my.cnf" to set global options,
    # - "~/.my.cnf" to set user-specific options.
    # 
    # One can use all long options that the program supports.
    # Run program with --help to get a list of available options and with
    # --print-defaults to see which it would actually understand and use.
    #
    # For explanations see
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
    
    # This will be passed to all mysql clients
    # It has been reported that passwords should be enclosed with ticks/quotes
    # escpecially if they contain "#" chars...
    # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
    [client]
    port		= 3306
    socket		= /var/run/mysqld/mysqld.sock
    
    # Here is entries for some specific programs
    # The following values assume you have at least 32M ram
    
    # This was formally known as [safe_mysqld]. Both versions are currently parsed.
    [mysqld_safe]
    socket		= /var/run/mysqld/mysqld.sock
    nice		= 0
    
    [mysqld]
    #
    # * Basic Settings
    #
    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
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    # bind-address		= 127.0.0.1
    #
    # * Fine Tuning
    #
    key_buffer		= 16M
    max_allowed_packet	= 16M
    thread_stack		= 192K
    thread_cache_size       = 8
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover         = BACKUP
    #max_connections        = 100
    #table_cache            = 64
    #thread_concurrency     = 10
    #
    # * Query Cache Configuration
    #
    query_cache_limit	= 1M
    query_cache_size        = 16M
    #
    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    # As of 5.1 you can enable the log at runtime!
    #general_log_file        = /var/log/mysql/mysql.log
    #general_log             = 1
    #
    # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
    #
    # Here you can see queries with especially long duration
    #log_slow_queries	= /var/log/mysql/mysql-slow.log
    #long_query_time = 2
    #log-queries-not-using-indexes
    #
    # The following can be used as easy to replay backup logs or for replication.
    # note: if you are setting up a replication slave, see README.Debian about
    #       other settings you may need to change.
    server-id		= 1
    log_bin			= /var/log/mysql/mysql-bin.log
    expire_logs_days	= 10
    max_binlog_size         = 100M
    binlog_do_db		= c1uhstem
    #binlog_ignore_db	= include_database_name
    #
    # * InnoDB
    #
    # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
    # Read the manual for more InnoDB related options. There are many!
    #
    # * Security Features
    #
    # Read the manual, too, if you want chroot!
    # chroot = /var/lib/mysql/
    #
    # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
    ssl
    ssl-ca=/etc/mysql/newcerts/ca-cert.pem
    ssl-cert=/etc/mysql/newcerts/server-cert.pem
    ssl-key=/etc/mysql/newcerts/server-key.pem
    
    
    
    [mysqldump]
    quick
    quote-names
    max_allowed_packet	= 16M
    
    [mysql]
    #no-auto-rehash	# faster start of mysql but no tab completition
    
    [isamchk]
    key_buffer		= 16M
    
    #
    # * IMPORTANT: Additional settings that can override those from this file!
    #   The files must end with '.cnf', otherwise they'll be ignored.
    #
    !includedir /etc/mysql/conf.d/
    
    Here is the slave my.cnf:
    Code:
    root@dor:~# cat /etc/mysql/my.cnf
    #
    # The MySQL database server configuration file.
    #
    # You can copy this to one of:
    # - "/etc/mysql/my.cnf" to set global options,
    # - "~/.my.cnf" to set user-specific options.
    # 
    # One can use all long options that the program supports.
    # Run program with --help to get a list of available options and with
    # --print-defaults to see which it would actually understand and use.
    #
    # For explanations see
    # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
    
    # This will be passed to all mysql clients
    # It has been reported that passwords should be enclosed with ticks/quotes
    # escpecially if they contain "#" chars...
    # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
    [client]
    port		= 3306
    socket		= /var/run/mysqld/mysqld.sock
    
    # Here is entries for some specific programs
    # The following values assume you have at least 32M ram
    
    # This was formally known as [safe_mysqld]. Both versions are currently parsed.
    [mysqld_safe]
    socket		= /var/run/mysqld/mysqld.sock
    nice		= 0
    
    [mysqld]
    #
    # * Basic Settings
    #
    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
    #
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    # bind-address		= 127.0.0.1
    #
    # * Fine Tuning
    #
    key_buffer		= 16M
    max_allowed_packet	= 16M
    thread_stack		= 192K
    thread_cache_size       = 8
    # This replaces the startup script and checks MyISAM tables if needed
    # the first time they are touched
    myisam-recover         = BACKUP
    #max_connections        = 100
    #table_cache            = 64
    #thread_concurrency     = 10
    #
    # * Query Cache Configuration
    #
    query_cache_limit	= 1M
    query_cache_size        = 16M
    #
    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    # As of 5.1 you can enable the log at runtime!
    #general_log_file        = /var/log/mysql/mysql.log
    #general_log             = 1
    #
    # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
    #
    # Here you can see queries with especially long duration
    #log_slow_queries	= /var/log/mysql/mysql-slow.log
    #long_query_time = 2
    #log-queries-not-using-indexes
    #
    # The following can be used as easy to replay backup logs or for replication.
    # note: if you are setting up a replication slave, see README.Debian about
    #       other settings you may need to change.
    server-id		= 2
    #master-connect-retry=60
    replicate-do-db = c1uhstem
    #log_bin			= /var/log/mysql/mysql-bin.log
    expire_logs_days	= 10
    max_binlog_size         = 100M
    #binlog_do_db		= include_database_name
    #binlog_ignore_db	= include_database_name
    #
    # * InnoDB
    #
    # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
    # Read the manual for more InnoDB related options. There are many!
    #
    # * Security Features
    #
    # Read the manual, too, if you want chroot!
    # chroot = /var/lib/mysql/
    #
    # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
    ssl
    # ssl-ca=/etc/mysql/cacert.pem
    # ssl-cert=/etc/mysql/server-cert.pem
    # ssl-key=/etc/mysql/server-key.pem
    
    
    
    [mysqldump]
    quick
    quote-names
    max_allowed_packet	= 16M
    
    [mysql]
    #no-auto-rehash	# faster start of mysql but no tab completition
    
    [isamchk]
    key_buffer		= 16M
    
    #
    # * IMPORTANT: Additional settings that can override those from this file!
    #   The files must end with '.cnf', otherwise they'll be ignored.
    #
    !includedir /etc/mysql/conf.d/
    Here is so more info in case it is pertinent:
    Slave:
    Code:
    root@dor:~# ls -l /etc/mysql/newcerts/
    total 12
    -rw-r--r-- 1 root root 1436 Oct 30 15:48 ca-cert.pem
    -rw-r--r-- 1 root root 1306 Oct 30 15:49 client-cert.pem
    -rw-r--r-- 1 root root 1708 Oct 30 15:49 client-key.pem
    Master:
    Code:
    root@abda:/home/sysadmin# ls -l /etc/mysql/newcerts/
    total 56
    -r-------- 1 root root  1692 Oct 30 10:02 abdakey.pem
    -rw-r--r-- 1 root root  1436 Oct 30 08:48 ca-cert.pem
    -rw-r--r-- 1 root root  1679 Oct 30 08:47 ca-key.pem
    -rw-r--r-- 1 root root 10240 Nov  5 20:24 certs.tar
    -rw-r--r-- 1 root root  1306 Oct 30 08:49 client-cert.pem
    -rw-r--r-- 1 root root  1708 Oct 30 08:49 client-key.pem
    -rw-r--r-- 1 root root  1062 Oct 30 08:49 client-req.pem
    -r-------- 1 root root  1692 Oct 30 09:04 ncqrkey.pem
    -rw-r--r-- 1 root root  1306 Oct 30 08:51 server-cert.pem
    -rw-r--r-- 1 root root  1700 Oct 30 08:51 server-key.pem
    -rw-r--r-- 1 root root  1062 Oct 30 08:51 server-req.pem
    -rw-r--r-- 1 root root  1436 Oct 30 09:42 ubunutu@cloud.nextcode.info
    
    Master:
    Code:
    mysql> SHOW GRANTS FOR slave_user;
    +-----------------------------------------------------------------------------------------------------------------------------------+
    | Grants for slave_user@%                                                                                                           |
    +-----------------------------------------------------------------------------------------------------------------------------------+
    | GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY PASSWORD '***************REMOVED*********7D9279517E9' REQUIRE SSL |
    | GRANT ALL PRIVILEGES ON `c1uhstem`.* TO 'slave_user'@'%'                                                                          |
    +-----------------------------------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    I have tried many things to make this work, all to no avail. I hope more sets of eyes can help out with this.

    --Steve
     
    Last edited: Nov 6, 2012
  2. falko

    falko Super Moderator ISPConfig Developer

    What are the outputs of
    Code:
    netstat -tap
    and
    Code:
     iptables -L
    ?
     
  3. pebkac

    pebkac Member HowtoForge Supporter

    Master:
    Code:
    root@abda:/home/sysadmin# netstat -tap
    Active Internet connections (servers and established)
    Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
    tcp        0      0 localhost.localdo:10025 *:*                     LISTEN      4022/master     
    tcp        0      0 *:mysql                 *:*                     LISTEN      24476/mysqld    
    tcp        0      0 *:20874                 *:*                     LISTEN      857/sshd        
    tcp        0      0 *:submission            *:*                     LISTEN      4022/master     
    tcp        0      0 *:pop3                  *:*                     LISTEN      988/dovecot     
    tcp        0      0 *:imap2                 *:*                     LISTEN      988/dovecot     
    tcp        0      0 *:http-alt              *:*                     LISTEN      1161/apache2    
    tcp        0      0 *:http                  *:*                     LISTEN      1161/apache2    
    tcp        0      0 *:tproxy                *:*                     LISTEN      1161/apache2    
    tcp        0      0 *:ftp                   *:*                     LISTEN      20180/pure-ftpd (SE
    tcp        0      0 abda.nextcode.in:domain *:*                     LISTEN      997/named       
    tcp        0      0 localhost.locald:domain *:*                     LISTEN      997/named       
    tcp        0      0 *:smtp                  *:*                     LISTEN      4022/master     
    tcp        0      0 localhost.localdoma:953 *:*                     LISTEN      997/named       
    tcp        0      0 *:10874                 *:*                     LISTEN      2011/perl       
    tcp        0      0 *:https                 *:*                     LISTEN      1161/apache2    
    tcp        0      0 *:imaps                 *:*                     LISTEN      988/dovecot     
    tcp        0      0 *:pop3s                 *:*                     LISTEN      988/dovecot     
    tcp        0      0 localhost.localdom:9000 *:*                     LISTEN      1729/php-fpm.conf)
    tcp        0      0 localhost.localdo:10024 *:*                     LISTEN      1258/amavisd (maste
    tcp        0      0 abda.nextcode.inf:mysql ec2-184-169-151-2:37566 TIME_WAIT   -               
    tcp        0     81 abda.nextcode.inf:imap2 66-87-66-105.pools:3439 FIN_WAIT1   -               
    tcp        0      0 abda.nextcode.info:pop3 dhcp64-134-221-23:49279 TIME_WAIT   -               
    tcp        0      0 abda.nextcode.inf:imap2 pool-74-100-197-4:44946 ESTABLISHED 17663/imap-login
    tcp        0    288 abda.nextcode.inf:20874 dhcp64-134-221-23:65465 ESTABLISHED 17953/sshd: sysadmi
    tcp        0      0 abda.nextcode.inf:imap2 mobile-166-137-17:46414 ESTABLISHED 17373/imap-login
    tcp        1      0 localhost.localdo:60571 localhost.localdo:10025 CLOSE_WAIT  7283/amavisd (ch13-
    tcp        0      0 abda.nextcode.info:pop3 dhcp64-134-221-23:49278 TIME_WAIT   -               
    tcp        0    942 abda.nextcode.inf:imap2 pool-74-100-197-4:41688 ESTABLISHED 17656/imap-login
    tcp        1      0 localhost.localdo:60574 localhost.localdo:10025 CLOSE_WAIT  7281/amavisd (ch14-
    tcp        0      0 abda.nextcode.inf:imap2 mobile-166-137-17:63374 ESTABLISHED 17553/imap-login
    tcp6       0      0 [::]:20874              [::]:*                  LISTEN      857/sshd        
    tcp6       0      0 [::]:submission         [::]:*                  LISTEN      4022/master     
    tcp6       0      0 [::]:pop3               [::]:*                  LISTEN      988/dovecot     
    tcp6       0      0 [::]:imap2              [::]:*                  LISTEN      988/dovecot     
    tcp6       0      0 [::]:ftp                [::]:*                  LISTEN      20180/pure-ftpd (SE
    tcp6       0      0 [::]:domain             [::]:*                  LISTEN      997/named       
    tcp6       0      0 [::]:smtp               [::]:*                  LISTEN      4022/master     
    tcp6       0      0 ip6-localhost:953       [::]:*                  LISTEN      997/named       
    tcp6       0      0 [::]:imaps              [::]:*                  LISTEN      988/dovecot     
    tcp6       0      0 [::]:pop3s              [::]:*                  LISTEN      988/dovecot     
    Code:
    root@abda:/home/sysadmin#  iptables -L
    Chain INPUT (policy ACCEPT)
    target     prot opt source               destination         
    fail2ban-dovecot-pop3imap  tcp  --  anywhere             anywhere             multiport dports pop3,pop3s,imap2,imaps
    fail2ban-pureftpd  tcp  --  anywhere             anywhere             multiport dports ftp
    fail2ban-ssh  tcp  --  anywhere             anywhere             multiport dports ssh
    
    Chain FORWARD (policy ACCEPT)
    target     prot opt source               destination         
    
    Chain OUTPUT (policy ACCEPT)
    target     prot opt source               destination         
    
    Chain fail2ban-dovecot-pop3imap (1 references)
    target     prot opt source               destination         
    RETURN     all  --  anywhere             anywhere            
    
    Chain fail2ban-pureftpd (1 references)
    target     prot opt source               destination         
    RETURN     all  --  anywhere             anywhere            
    
    Chain fail2ban-ssh (1 references)
    target     prot opt source               destination         
    RETURN     all  --  anywhere             anywhere            
    root@abda:/home/sysadmin# 
    
     
  4. concept21

    concept21 Member

  5. falko

    falko Super Moderator ISPConfig Developer

    Looks ok. Have you tried to restart MySQL or even the whole server?
    Is there maybe another (external) firewall that is blocking port 3306?
     
  6. pebkac

    pebkac Member HowtoForge Supporter

    I've restarted mysql many times, not the server though. I do have a firwall in front of it, but I can telnet to 3306 with no problems.
     
  7. falko

    falko Super Moderator ISPConfig Developer

    Is this a physical server or a virtual machine?
     
  8. pebkac

    pebkac Member HowtoForge Supporter

    They are both physical servers.
     

Share This Page