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
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?
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.
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)
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
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
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 'slave_user@master_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 'slave_user@master_ip:3306' - retry-time: 60 retries: 86400, Error_code: 2026
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?
outputs On Master [root@server1 ~]# 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 [root@server1 ~]# 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 [root@server2 ~]# 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 [root@server2 ~]# 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';
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?
These are the outputs: MASTER [root@server1 ~]# getenforce Disabled SLAVE [root@server2 ~]# getenforce Disabled
Hm, then I'm running out of ideas. Please double-check your configuration, you must have done something wrong.
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?
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.
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
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
