Difference between revisions of "Software setup for db-class"

From Got Opinion Wiki
Jump to navigation Jump to search
m
m (Update firewall)
 
(2 intermediate revisions by the same user not shown)
Line 161: Line 161:
 
=== Set up MySQL for remote access ===
 
=== Set up MySQL for remote access ===
  
=== Update firwall ===
+
MySQL supports TCP/IP for remote connectivity. I set up MySQL server to accept TCP connections (unsecure) on my local area network (trusted network) & SSH for remote connections.
  
I configured MySQL server to accept TCP connections (unsecure) on my local area network & use SSH for remote connections.
+
==== Update firewall ====
 +
 
 +
By default iptables will not be allowing TCP port 3306 (default port for mysql server).
 +
 
 +
Confirm if 3306 is in iptables
 +
<pre># # cat /etc/sysconfig/iptables | grep 3306
 +
#</pre>
 +
 
 +
Add rule to allow TCP connections from particular local area network [you insert your IP address & subnet for your setup]
 +
<pre># iptables -A INPUT -p tcp -s 192.168.1.0/24 -m state --state NEW -m tcp --dport 3306 -j ACCEPT</pre>
 +
 
 +
If you want to allow TCP connections from any network you can use this command
 +
<pre># iptables -A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT</pre>
 +
 
 +
'''NOTE: Use one of the rules above not both.'''
 +
 
 +
Save the iptables rules to make them persistent [RHEL/CentOS/Fedora Core support this command]
 +
<pre># /etc/init.d/iptables save
 +
iptables: Saving firewall rules to /etc/sysconfig/iptables: [  OK  ]</pre>
 +
 
 +
Confirm new rule was added to configuration file
 +
<pre># cat /etc/sysconfig/iptables | grep 3306
 +
-A INPUT -s 192.168.1.0/24 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT</pre>
 +
 
 +
Restart iptables
 +
<pre># service iptables restartiptables: Flushing firewall rules: [  OK  ]
 +
iptables: Setting chains to policy ACCEPT: filter [  OK  ]
 +
iptables: Unloading modules: [  OK  ]
 +
iptables: Applying firewall rules: [  OK  ]</pre>
 +
 
 +
===== Trouble shooting firewall =====
 +
 
 +
You may need to move your rule above another rule that rejects your MySQL traffic. Use text editor to move your new rule above other rule in /etc/sysconfig/iptables
 +
 
 +
To quickly trouble shoot firewall issue simply disable firewall & test MySQL connection. If MySQL connectivity works then double check your firewall rules.
 +
 
 +
Disable IPtables: <code># service iptables stop</code>
 +
 
 +
Enable IPtables:  <code># service iptables start</code>
 +
 
 +
'''Enable iptables when you're done testing connection!!!'''
 +
 
 +
==== Update SSH server ====
 +
 
 +
SSH server must be configured to support TCP forwarding.
 +
 
 +
Check that AllowTcpForwarding is enabled in ssh daemon configuration
 +
<pre>[root@robot ~]# cat /etc/ssh/sshd_config | grep -i tcpforwarding
 +
AllowTcpForwarding yes</pre>
 +
 
 +
If AllowTcpForwarding is set to '''no''' use text editor to change & save setting then restart sshd
 +
<pre># vi /etc/ssh/sshd_config
 +
# service sshd restart
 +
Stopping sshd: [  OK  ]
 +
Starting sshd: [  OK  ]</pre>
 +
 
 +
==== Update SELinux ====
 +
 
 +
Unable to connect MySQL client over tunnel using SSH when firewall & SSH server configured? Check SELinux!
 +
 
 +
The ssh client might produce some error with SSH_OPEN_ADMINISTRATIVELY_PROHIBITED message in it.
 +
 
 +
You may also see an error message in /var/log/secure on the SSH server that looks like this:
 +
<pre>error: connect to 192.168.1.2 port 3306 failed: Permission denied</pre>
 +
 
 +
Verify enforcement level of SELinux
 +
<pre>$ getenforce
 +
Enforcing</pre>
 +
Enforcing means SELinux is configured & enforcing SELinux policies
 +
 
 +
Verify SELinux sshd forward port connections setting
 +
<pre># semanage boolean -l | grep sshd
 +
sshd_forward_ports            -> off  allow sshd to forward port connections</pre>
 +
 
 +
Set SELinux sshd to allow forward port connections persistently
 +
<pre># setsebool -P sshd_forward_ports on</pre>
 +
 
 +
Verify SELinux sshd forward port connections setting
 +
<pre># semanage boolean -l | grep sshd
 +
sshd_forward_ports            -> on    allow sshd to forward port connections</pre>
 +
 
 +
==== Test MySQL connectivity tunneling through SSH ====
 +
 
 +
On my Window clients I use a handy utility called [http://www.bitvise.com/tunnelier Bitvise Tunnelier]. Complete Login & C2S Forwarding tab in Bitvise.
 +
 
 +
* Login tab
 +
** Host = hostname or IP address of your SSH server
 +
** Port = 22
 +
** Username = user name on SSH server
 +
** Password = user name password on SSH server
 +
* C2S Forwarding tab (select Add)
 +
** Status = enabled
 +
** Listen Interface = 127.0.0.1
 +
** List. Port = 3307
 +
** Destination Host = hostname or IP address of MySQL server (mine was private IP address as I connect to public IP from Login tab then forward to private IP)
 +
 
 +
On Linux clients use terminal.
 +
<pre>$ ssh -v -f -N -L 3307:192.168.1.2:3306 username@hostname_of_mysql_server</pre>
 +
 
 +
Launch your MySQL client.
 +
 
 +
Your MySQL client should connect to localhost or 127.0.0.1 over port 3307 (or any port you used in ssh command above).
 +
 
 +
Your local machine will accept port 3307 traffic and forward that to MySQL server over secure encrypted SSH tunnel. SSH server will perform TCP forwarding of your 3307 port to 3306 on MySQL server.
  
 
<center>[[My db-class.org notes|To My db-class.org notes]]</center>
 
<center>[[My db-class.org notes|To My db-class.org notes]]</center>

Latest revision as of 10:04, 6 November 2011

I used Fedora Core 15 as my server & used both Linux or Windows clients to remotely connect then used X over SSH or CLI. On my Windows 7 64-bit client I use:

  • PuTTY (putty.exe)
  • mRemoteNG is an open source, tabbed, multi-protocol, remote connections manager. mRemoteNG allows you to view all of your remote connections in a simple yet powerful tabbed interface.
  • HeidiSQL a lightweight interface to MySQL (+MS SQL)
  • XMLPad 3 is a freeware XML & DTD editor plus more.

xmllint

My Fedora Core 15 had xmllint installed. To confirm you have the software installed:

[ptay@robot logs]$ xmllint --version
xmllint: using libxml version 20707
   compiled with: Threads Tree Output Push Reader Patterns Writer SAXv1 FTP HTTP DTDValid HTML Legacy
   C14N Catalog XPath XPointer XInclude Iconv ISO8859X Unicode Regexps Automata Expr Schemas Schematron
   Modules Debug Zlib 
[ptay@robot logs]$

Kernow

Download Kernow

[ptay@robot Kernow]$ wget http://sourceforge.net/projects/kernowforsaxon/files/Kernow/Kernow%201.7.2/Kernow%201.7.2.zip/download?use_mirror=iweb

Unzip Kernow file

[ptay@robot Downloads]$ unzip "Kernow 1.7.2.zip" 
Archive:  Kernow 1.7.2.zip
   creating: Kernow 1.7.2

   //////////////omitting text/////////////////////

  inflating: Kernow 1.7.2/readme.txt

Optional, remove .zip file by using rm -f "Kernow 1.7.2.zip" command.

Launch Kernow

Navigate to directory where Kernow is located (or create symbolic link and use symbolic link). This command will launch a Kernow java window.

[ptay@robot Downloads]$ cd Kernow1.7.2/
[ptay@robot Kernow1.7.2]$ ls
ant    extensions  Kernow.exe  Kernow.sh  readme.txt
cache  Kernow.bat  kernow.jar  lib
[ptay@robot Kernow1.7.2]$ java -jar kernow.jar &
[1] 3370
[ptay@robot Kernow1.7.2]$

MySQL

Ubuntu commands:

$ sudo apt-get install mysql-server mysql-common mysql-client

Fedora Core 15 commands:

[ptay@robot logs]$ sudo yum groupinstall "MySQL Database"
[sudo] password for ptay: 

//////////////omitting text/////////////////////

Installed:
  MySQL-python.x86_64 0:1.2.3-1.fc14                                            
  libdbi-dbd-mysql.x86_64 0:0.8.3-6.fc14                                        
  mysql.x86_64 0:5.1.58-1.fc14                                                  
  mysql-connector-odbc.x86_64 0:5.1.5r1144-7.fc13                               
  mysql-server.x86_64 0:5.1.58-1.fc14                                           
  perl-DBD-MySQL.x86_64 0:4.017-1.fc14                                          
  unixODBC.x86_64 0:2.2.14-12.fc14                                              

Dependency Installed:
  libdbi.x86_64 0:0.8.3-4.fc14        libdbi-drivers.x86_64 0:0.8.3-6.fc14     
  perl-DBI.x86_64 0:1.613-1.fc14     

Complete!

Enable MySQL at specific run levels

Note: I did 3 & 5

[ptay@robot logs]$ sudo chkconfig --level 35 mysqld on
[ptay@robot logs]$ chkconfig --list | grep -i mysqld
mysqld          0:off   1:off   2:off   3:on    4:off   5:on    6:off

Start MySQL daemon (mysqld)

As root

[root@robot ~]# service mysqld restart
Stopping mysqld:  [  OK  ]
Initializing MySQL database:  Installing MySQL system tables...
OK

//////////////omitting text/////////////////////

Starting mysqld:  [  OK  ]

Run MySQL secure installation script

As root

[root@robot ~]# /usr/bin/mysql_secure_installation

//////////////omitting text/////////////////////

Set root password? [Y/n] y
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y
Cleaning up...

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

Test MySQL

As any user

[root@robot ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.58 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test
Database changed
mysql> use test;
Database changed
mysql> drop table if exists T;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table T (A text, B text);
insert into T values ('Hello,', 'world!');
select * from T;Query OK, 0 rows affected (0.06 sec)

mysql> insert into T values ('Hello,', 'world!');
Query OK, 1 row affected (0.00 sec)

mysql> select * from T;
+--------+--------+
| A      | B      |
+--------+--------+
| Hello, | world! |
+--------+--------+
1 row in set (0.00 sec)

mysql> \q
Bye

Set up MySQL for remote access

MySQL supports TCP/IP for remote connectivity. I set up MySQL server to accept TCP connections (unsecure) on my local area network (trusted network) & SSH for remote connections.

Update firewall

By default iptables will not be allowing TCP port 3306 (default port for mysql server).

Confirm if 3306 is in iptables

# # cat /etc/sysconfig/iptables | grep 3306
#

Add rule to allow TCP connections from particular local area network [you insert your IP address & subnet for your setup]

# iptables -A INPUT -p tcp -s 192.168.1.0/24 -m state --state NEW -m tcp --dport 3306 -j ACCEPT

If you want to allow TCP connections from any network you can use this command

# iptables -A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

NOTE: Use one of the rules above not both.

Save the iptables rules to make them persistent [RHEL/CentOS/Fedora Core support this command]

# /etc/init.d/iptables save
iptables: Saving firewall rules to /etc/sysconfig/iptables: [  OK  ]

Confirm new rule was added to configuration file

# cat /etc/sysconfig/iptables | grep 3306
-A INPUT -s 192.168.1.0/24 -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

Restart iptables

# service iptables restartiptables: Flushing firewall rules: [  OK  ]
iptables: Setting chains to policy ACCEPT: filter [  OK  ]
iptables: Unloading modules: [  OK  ]
iptables: Applying firewall rules: [  OK  ]
Trouble shooting firewall

You may need to move your rule above another rule that rejects your MySQL traffic. Use text editor to move your new rule above other rule in /etc/sysconfig/iptables

To quickly trouble shoot firewall issue simply disable firewall & test MySQL connection. If MySQL connectivity works then double check your firewall rules.

Disable IPtables: # service iptables stop

Enable IPtables: # service iptables start

Enable iptables when you're done testing connection!!!

Update SSH server

SSH server must be configured to support TCP forwarding.

Check that AllowTcpForwarding is enabled in ssh daemon configuration

[root@robot ~]# cat /etc/ssh/sshd_config | grep -i tcpforwarding
AllowTcpForwarding yes

If AllowTcpForwarding is set to no use text editor to change & save setting then restart sshd

# vi /etc/ssh/sshd_config
# service sshd restart
Stopping sshd: [  OK  ]
Starting sshd: [  OK  ]

Update SELinux

Unable to connect MySQL client over tunnel using SSH when firewall & SSH server configured? Check SELinux!

The ssh client might produce some error with SSH_OPEN_ADMINISTRATIVELY_PROHIBITED message in it.

You may also see an error message in /var/log/secure on the SSH server that looks like this:

error: connect to 192.168.1.2 port 3306 failed: Permission denied

Verify enforcement level of SELinux

$ getenforce
Enforcing

Enforcing means SELinux is configured & enforcing SELinux policies

Verify SELinux sshd forward port connections setting

# semanage boolean -l | grep sshd
sshd_forward_ports             -> off   allow sshd to forward port connections

Set SELinux sshd to allow forward port connections persistently

# setsebool -P sshd_forward_ports on

Verify SELinux sshd forward port connections setting

# semanage boolean -l | grep sshd
sshd_forward_ports             -> on    allow sshd to forward port connections

Test MySQL connectivity tunneling through SSH

On my Window clients I use a handy utility called Bitvise Tunnelier. Complete Login & C2S Forwarding tab in Bitvise.

  • Login tab
    • Host = hostname or IP address of your SSH server
    • Port = 22
    • Username = user name on SSH server
    • Password = user name password on SSH server
  • C2S Forwarding tab (select Add)
    • Status = enabled
    • Listen Interface = 127.0.0.1
    • List. Port = 3307
    • Destination Host = hostname or IP address of MySQL server (mine was private IP address as I connect to public IP from Login tab then forward to private IP)

On Linux clients use terminal.

$ ssh -v -f -N -L 3307:192.168.1.2:3306 username@hostname_of_mysql_server

Launch your MySQL client.

Your MySQL client should connect to localhost or 127.0.0.1 over port 3307 (or any port you used in ssh command above).

Your local machine will accept port 3307 traffic and forward that to MySQL server over secure encrypted SSH tunnel. SSH server will perform TCP forwarding of your 3307 port to 3306 on MySQL server.

To My db-class.org notes