Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > ISPConfig 3 > General

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old 6th November 2012, 19:21
pebkac pebkac is offline
Senior Member
 
Join Date: Feb 2009
Posts: 132
Thanks: 2
Thanked 7 Times in 7 Posts
Question MySQL Database Replication With SSL Encryption

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-set...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 by pebkac; 6th November 2012 at 19:22. Reason: passsword removed
Reply With Quote
Sponsored Links
 

Bookmarks

Tags
mysql, replication

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL Replication on Web, Email And MySQL Database Cluster matiasCU Installation/Configuration 0 24th October 2011 09:21
MySQL Database Replication Problem black_magik HOWTO-Related Questions 11 6th July 2010 14:16
Mysql Database replication behind a router Rafiek Installation/Configuration 1 6th February 2009 13:38
Management/system config/settings & /server/settings not working!! dactor Installation/Configuration 9 6th February 2008 09:11
LAMP System Fedora Core 6 MySQL start error gavino HOWTO-Related Questions 13 21st November 2007 19:13


All times are GMT +2. The time now is 05:22.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.