Slave PowerDNS server does not respond/Security

Discussion in 'Installation/Configuration' started by Linox91, Mar 9, 2011.

  1. Linox91

    Linox91 New Member

    Hello,

    Thanks for the great tutorials.

    I did those tutorials step by step.

    http://www.howtoforge.com/how-to-set-up-mysql-database-replication-with-ssl-encryption-on-centos-5.4
    http://www.howtoforge.com/installing-powerdns-with-mysql-backend-and-poweradmin-on-centos-5.2

    My Master is working well.

    The Slave does not respond to any query.

    I checked the Slave Mysql

    mysql> SHOW SLAVE STATUS \G

    ....

    Slave_IO_Running: No
    ....
    Seconds_Behind_Master: NULL


    Then


    I installed PowerDNS with mysql backend on Slave Server.

    I did this:

    yum -y install pdns pdns-backend-mysql

    vi /etc/pdns/pdns.conf

    add this

    launch=gmysql
    gmysql-host=127.0.0.1
    gmysql-user=slave_user
    gmysql-password=slave_password
    gmysql-dbname=mydatabase


    chkconfig --levels 235 pdns on
    /etc/init.d/pdns start

    A) The Slave does not respond any query. I do not have any firewall yet.

    Any ideas?

    I think that something is missing in those tutorials for Slave Server.

    B) How can I make the security for Master/Slave?
    I do not want that anybody hack them.


    C) Can you add those topics to your tutorials?


    Thanks
     
  2. Linox91

    Linox91 New Member

    I fixed Slave and it responds queries.

    But I have another problem.

    I made some changes in Master Zone with Poweradmin,

    The changes were not updated in Slave DNS Server.

    http://www.intodns.com

    Looks like your nameservers do not agree on the SOA serial.
    Ths SOA records as reported by your nameservers:

    xxx.xxx.xxx.xxx -> 2011031050
    yyy.yyy.yyy.yyy -> 2011030210

    This can cause some serious problems that
    is why you should fix this asap.

    Master and Slave are not working together.

    Any ideas?
     
    Last edited: Mar 10, 2011
  3. Linox91

    Linox91 New Member

    I did this but It is not working yet.

    http://doc.powerdns.com/pdns-users-faq.html

    Q: Master or Slave support is not working, PDNS is not picking up changes
    A: The Master/Slave apparatus is off by default. Turn it on by adding a slave
    and/or master statement to the configuration file. Also, check that the
    configured backend is master or slave capable.
     
  4. falko

    falko Super Moderator ISPConfig Developer

    What does
    Code:
    SHOW SLAVE STATUS \G
    show on the slave?

    Are there any MySQL errors in your logs?
     
  5. Linox91

    Linox91 New Member

    Here is

    mysql> SHOW SLAVE STATUS \G
    *************************** 1. row ***************************
    Slave_IO_State: Connecting to master
    Master_Host: xxx.xxx.xxx.xxx
    Master_User: slave_user
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000004
    Read_Master_Log_Pos: 106
    Relay_Log_File: mysqld-relay-bin.000001
    Relay_Log_Pos: 98
    Relay_Master_Log_File: mysql-bin.000004
    Slave_IO_Running: No
    Slave_SQL_Running: Yes
    Replicate_Do_DB: mydatabase
    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: 106
    Relay_Log_Space: 98
    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
    1 row in set (0.00 sec)
     
  6. Linox91

    Linox91 New Member

    I have tried many times, Here is the info:


    mysql> SHOW MASTER STATUS;
    +------------------+----------+--------------+------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000004 | 106 | mydatabase | |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)





    CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx', MASTER_USER='slave_user', MASTER_PASSWORD='YYYYYYYYYYYYYYYYYY',
    MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=106, MASTER_SSL=1, MASTER_SSL_CA = '/etc/mysql/newcerts/ca-cert.pem',
    MASTER_SSL_CERT = '/etc/mysql/newcerts/client-cert.pem', MASTER_SSL_KEY = '/etc/mysql/newcerts/client-key.pem';



    /var/log/mysqld.log


    110309 20:43:31 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000004' at position 106, relay log './mysqld-relay-bin.000001' position: 98
    110309 20:43:32 [ERROR] Slave I/O thread: error connecting to master '[email protected]:3306': Error: 'SSL connection error' errno: 2026 retry-time: 60 retries: 86400
    110309 20:44:35 [ERROR] Slave I/O thread: error connecting to master '[email protected]:3306': Error: 'Lost connection to MySQL server at 'reading initial communication packet', system error: 113' errno: 2013 retry-time: 60 retries: 86400
    110309 20:46:38 [ERROR] Slave I/O thread: error connecting to master '[email protected]:3306': Error: 'SSL connection error' errno: 2026 retry-time: 60 retries: 86400


    I found this info:

    Error: 'SSL connection error' errno: 2026

    User Comments
    Posted by foo bar on October 24 2007 12:57pm [Delete] [Edit]
    i've been trying to set up replication using ssl and i finally made it after banging my head against the wall

    don't rely on the docs here too much - they're incomplete and partly misleading

    for the ssl-params (like ca, cert and key) you should try to specify all paths as absolute full paths (in my.cnf & CHANGE MASTER) - that did it for me after getting:
    Error: 'SSL connection error' errno: 2026
     
  7. Linox91

    Linox91 New Member

    MASTER SERVER:


    pdns.conf

    master
    setuid=pdns
    setgid=pdns

    allow-recursion=127.0.0.1/8

    chroot=./

    launch=gmysql
    gmysql-host=127.0.0.1
    gmysql-user=power_admin
    gmysql-port=3306
    gmysql-password=power_admin_password
    gmysql-dbname=mydatabase



    my.cnf

    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

    server-id = 1
    log_bin = /var/log/mysql/mysql-bin.log
    expire_logs_days = 10
    max_binlog_size = 100M
    binlog_do_db = mydatabase




    SLAVE SERVER:

    pdns.conf

    slave
    chroot=./
    setuid=pdns
    setgid=pdns

    launch=gmysql
    gmysql-host=127.0.0.1
    gmysql-user=slave_user
    gmysql-port=3306
    gmysql-password=slave_password
    gmysql-dbname=mydatabase


    my.cnf

    ssl
    server-id=2
    master-connect-retry=60
    replicate-do-db=mydatabase
     
  8. Linox91

    Linox91 New Member

    Hello,

    I set up and configured slave server again.
    Also, I made all certificates again.


    I made this change:

    [Master Server]

    GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'slave_ip' IDENTIFIED BY 'slave_password' REQUIRE SSL;



    [Slave Server]

    CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='slave_user',
    MASTER_PASSWORD='slave_password',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000006',
    MASTER_LOG_POS=106,
    MASTER_SSL=1,
    MASTER_SSL_CA='/etc/mysql/newcerts/ca-cert.pem',
    MASTER_SSL_CERT='/etc/mysql/newcerts/client-cert.pem',
    MASTER_SSL_KEY='/etc/mysql/newcerts/client-key.pem';




    mysql> SHOW MASTER STATUS;
    +------------------+----------+--------------+------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000006 | 106 | mydatabase | |
    +------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)




    mysql> SHOW SLAVE STATUS \G
    *************************** 1. row ***************************
    Slave_IO_State: Connecting to master
    Master_Host: master_ip
    Master_User: slave_user
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000006
    Read_Master_Log_Pos: 106
    Relay_Log_File: mysqld-relay-bin.000001
    Relay_Log_Pos: 4
    Relay_Master_Log_File: mysql-bin.000006
    Slave_IO_Running: No
    Slave_SQL_Running: Yes
    Replicate_Do_DB: mydatabase
    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: 106
    Relay_Log_Space: 106
    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: 2026
    Last_IO_Error: error connecting to master '[email protected]_ip:3306' - retry-time: 60 retries: 86400
    Last_SQL_Errno: 0
    Last_SQL_Error:
    1 row in set (0.00 sec)


    mysqld.log


    110311 8:56:03 [Note] Slave I/O thread killed while connecting to master
    110311 8:56:03 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000006', position 106
    110311 8:56:03 [Note] Event Scheduler: Purging the queue. 0 events
    110311 8:56:03 [Note] Error reading relay log event: slave SQL thread was killed
    110311 8:56:03 InnoDB: Starting shutdown...
    110311 8:56:07 InnoDB: Shutdown completed; log sequence number 0 50257
    110311 8:56:07 [Note] /usr/libexec/mysqld: Shutdown complete

    110311 08:56:07 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
    110311 08:56:08 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    110311 8:56:08 [Warning] 'for replication startup options' is deprecated and will be removed in a future release. Please use ''CHANGE MASTER'' instead.
    110311 8:56:08 InnoDB: Initializing buffer pool, size = 8.0M
    110311 8:56:08 InnoDB: Completed initialization of buffer pool
    110311 8:56:08 InnoDB: Started; log sequence number 0 50257
    110311 8:56:08 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem.
    110311 8:56:08 [Note] Event Scheduler: Loaded 0 events
    110311 8:56:08 [Note] /usr/libexec/mysqld: ready for connections.
    Version: '5.1.55' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
    110311 8:56:08 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000006' at position 106, relay log './mysqld-relay-bin.000001' position: 4
    110311 8:56:08 [ERROR] Slave I/O: error connecting to master '[email protected]_ip:3306' - retry-time: 60 retries: 86400, Error_code: 2026
     
  9. falko

    falko Super Moderator ISPConfig Developer

    The slave cannot connect to the master. What are the outputs of
    Code:
    netstat -tap
    and
    Code:
    iptables -L
    on the master?

    Does the slave use the correct slave user and password?
     
  10. Linox91

    Linox91 New Member

    outputs On Master

    [[email protected] ~]# iptables -L
    Chain INPUT (policy ACCEPT)
    target prot opt source destination

    Chain FORWARD (policy ACCEPT)
    target prot opt source destination

    Chain OUTPUT (policy ACCEPT)
    target prot opt source destination



    [[email protected] ~]# netstat -tap

    Active Internet connections (servers and established)
    Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
    tcp 0 0 *:mysql *:* LISTEN 4067/mysqld
    tcp 0 0 *:http *:* LISTEN 5274/httpd
    tcp 0 0 *:domain *:* LISTEN 5190/pdns_server-in
    tcp 0 0 *:ssh *:* LISTEN 3863/sshd
    tcp 0 0 localhost:smtp *:* LISTEN 5378/master

    tcp 0 0 localhost:mysql localhost:45892 ESTABLISHED 4067/mysqld
    tcp 0 0 localhost:mysql localhost:45889 ESTABLISHED 4067/mysqld
    tcp 0 0 localhost:mysql localhost:45888 ESTABLISHED 4067/mysqld
    tcp 0 0 localhost:mysql localhost:45891 ESTABLISHED 4067/mysqld
    tcp 0 0 localhost:mysql localhost:45890 ESTABLISHED 4067/mysqld
    tcp 0 0 localhost:45892 localhost:mysql ESTABLISHED 5190/pdns_server-in
    tcp 0 0 localhost:45890 localhost:mysql ESTABLISHED 5190/pdns_server-in
    tcp 0 0 localhost:45891 localhost:mysql ESTABLISHED 5190/pdns_server-in
    tcp 0 0 localhost:45888 localhost:mysql ESTABLISHED 5190/pdns_server-in
    tcp 0 0 localhost:45889 localhost:mysql ESTABLISHED 5190/pdns_server-in
    tcp 0 264 server1.domain.com:ssh host-xxx-xxx-xxx-xxx.b:uaiact ESTABLISHED 15789/0
    tcp 0 0 *:ssh *:* LISTEN 3863/sshd
    tcp 0 0 localhost:smtp *:* LISTEN 5378/master




    Outputs on Slave


    [[email protected] ~]# iptables -L
    Chain INPUT (policy ACCEPT)
    target prot opt source destination

    Chain FORWARD (policy ACCEPT)
    target prot opt source destination

    Chain OUTPUT (policy ACCEPT)
    target prot opt source destination


    [[email protected] ~]# netstat -tap

    Active Internet connections (servers and established)
    Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
    tcp 0 0 *:mysql *:* LISTEN 7801/mysqld
    tcp 0 0 *:http *:* LISTEN 7845/httpd
    tcp 0 0 *:ssh *:* LISTEN 7649/sshd
    tcp 0 264 server2.domain.com:ssh host-yyy.yyy.yyy.yyy-:abbaccuray ESTABLISHED 11423/0
    tcp 0 0 *:ssh *:* LISTEN 7649/sshd



    >Does the slave use the correct slave user and password?<

    On Master I set up a replication user slave_user that can be used by
    Slave to access the MySQL database on Master:


    GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password' REQUIRE SSL;

    Also, I tried this:

    GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'slave_ip' IDENTIFIED BY 'slave_password' REQUIRE SSL;




    On slave I set up this:

    CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='slave_user',
    MASTER_PASSWORD='slave_password',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='mysql-bin.000006',
    MASTER_LOG_POS=106,
    MASTER_SSL=1,
    MASTER_SSL_CA='/etc/mysql/newcerts/ca-cert.pem',
    MASTER_SSL_CERT='/etc/mysql/newcerts/client-cert.pem',
    MASTER_SSL_KEY='/etc/mysql/newcerts/client-key.pem';
     
  11. falko

    falko Super Moderator ISPConfig Developer

    Looks ok. Did you run
    Code:
    FLUSH PRIVILEGES;
    on the master after you created the slave user?
     
  12. Linox91

    Linox91 New Member

    Yes I did, FLUSH PRIVILEGES;

    I followed step by step your tutorials.
    I made them to the word.

    I have tried everything to make it working but I do not know what else to do.

    Any ideas?
     
  13. falko

    falko Super Moderator ISPConfig Developer

    What are the outputs of
    Code:
    getenforce
    on both systems?
     
  14. Linox91

    Linox91 New Member

    These are the outputs:

    MASTER

    [[email protected] ~]# getenforce
    Disabled



    SLAVE

    [[email protected] ~]# getenforce
    Disabled
     
  15. falko

    falko Super Moderator ISPConfig Developer

    Hm, then I'm running out of ideas. Please double-check your configuration, you must have done something wrong.
     
  16. Linox91

    Linox91 New Member

    I have Poweradmin 2.1.5 in Master Server.

    1. Can Poweradmin block slave_user to access mydatabase?

    2. Is there another way to test if slave_user can access mydatabase?
     
  17. falko

    falko Super Moderator ISPConfig Developer

    No. Replication happens at database level and has nothing to do with the installed applications.

    Yes, you can try to connect from the salve to the master as follows:
    Code:
    mysql -h [I]master[/I] -u [I]slave_user[/I] -p
    Replace master with the hostname or IP address of the master server and slave_user with the username of the slave user.
     
  18. Linox91

    Linox91 New Member

    Thanks for your help.

    I successfully set up and configure the Master and Slave.
    They are working fine.

    I did well the tutorial but certificates must be verified before they can be used.

    If they fail, MySQL will not work.

    # openssl verify -CAfile ca-cert.pem client-cert.pem
     
  19. Linox91

    Linox91 New Member

    Hello,

    I have PowerDNS With MySQL Backend And Poweradmin on Master.
    Master/Slave have MySQL Database Replication With SSL Encryption.

    Do I need to configure TSIG and DNSSEC?

    Thanks
     

Share This Page