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 [email protected] 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
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#
Have you tried MySQL Workbench 5.2.44 from Oracle? It has many functions but I have not tried all of them. http://www.mysql.com/downloads/workbench/
Looks ok. Have you tried to restart MySQL or even the whole server? Is there maybe another (external) firewall that is blocking port 3306?
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.
