Useful MySQL Commands

There are a lot of usefully MySQL commands that I either don’t run enough to remember or just end up using some GUI to accomplish. Nothing against Navicat, but what kind of sysadmin would I be if I couldn’t do it at the command line as well. Anyhow, I had to do a bunch of this kind of work lately, so I thought I would write the more common commands down here so I would have them to reference.

It is generally a good idea to firewall off your MySQL port (3306) unless you are on a secure network. The downside to this is that it prevents you from using GUI tools like Navicat to connect to your database server. No problem, just use this command to SSH tunnel a connection. After doing this, you can connect to your database on localhost port 8888:

ssh -fNg -L 8888:127.0.0.1:3306 login@mysql-server

Once you have installed a new instance of MySQL, you have to change the root password:

# mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'yourhostname' = PASSWORD('newpwd');
mysql> exit

Depending on the way you handle your MySQL installation, you will most likely have to run the mysql_upgrade script after upgrades to use all the new features:

mysql_upgrade -u root –password=yourpassword

Do a thorough check to see if your tables are corrupted:

mysqlcheck -u root -p --check --extended --all-databases

Repair any corruption if it exists:

mysqlcheck -u root -p --repair --all-databases

Look at all your tables and optomize them if necessary:

mysqlcheck -u root -p --optimize --all-databases

Basic iptables Configuration

It’s always a good idea to setup a local firewall on hosts that are on unprotected networks. The internet “winds” blow harder and harder each day, and it’s only a matter of time before some daemon has an exploit that gets taken advantage of. I use CentOS 5 for all my web servers, and here is an example of the script I use to create a DEFAULT TO DENY set of firewall rules. This script generates a file called iptables in /etc/sysconfig.

I used to create a special rule for MySQL that only allowed connections from my own network, but lately I have been omitting this rule and tunneling the connection through ssh instead. That is why it is commented out in the script below.


### SCRIPT ###
#!/bin/sh
# Drop all incoming traffic
/sbin/iptables -P INPUT DROP
# Drop all forwarded traffic
/sbin/iptables -P FORWARD DROP
# Allow all outgoing traffic
/sbin/iptables -P OUTPUT ACCEPT
# Allow returning packets
/sbin/iptables -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
# Allow incoming traffic on port 80 for web server
/sbin/iptables -A INPUT -p tcp -m tcp --dport 80 -j ACCEPT
# Allow MySQL only from a certain network
/sbin/iptables -A INPUT -p tcp -m tcp -s XXX.XXX.XXX.0/24 --dport 3306 -j ACCEPT
# Allow local traffic
/sbin/iptables -A INPUT -i lo -j ACCEPT
# Allow incoming SSH on port 22
#/sbin/iptables -A INPUT -p tcp -m tcp --dport 22 -j ACCEPT
# Allow ping
/sbin/iptables -A INPUT -p icmp -m icmp --icmp-type 8 -j ACCEPT
/sbin/iptables-save > /etc/sysconfig/iptables
chmod go-r /etc/sysconfig/iptables
/sbin/sudo service iptables restart
### /SCRIPT ###

Here is what /etc/sysconfig/iptables looks like after running this script:


# Generated by iptables-save v1.3.5 on Wed Dec 31 13:47:40 2008
*filter
:INPUT DROP [0:0]
:FORWARD DROP [0:0]
:OUTPUT ACCEPT [12:8972]
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p tcp -m tcp --dport 80 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -p tcp -m tcp --dport 22 -j ACCEPT
-A INPUT -p icmp -m icmp --icmp-type 8 -j ACCEPT
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p tcp -m tcp --dport 80 -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -p icmp -m icmp --icmp-type 8 -j ACCEPT
COMMIT
# Completed on Wed Dec 31 13:47:40 2008

After you are done, make sure you have iptables setup to start when the system boots.


# /sbin/chkconfig --list | grep iptables

iptables       	0:off	1:off	2:on	3:on	4:on	5:on	6:off

If it’s not on, just run:

/sbin/chkconfig iptables on

X11 Forwarding Broken on Solaris

If you’re running Solaris 8 or 9 and an upgrade results in broken SSH X11 forwarding, the problem may be Sun’s socfs bug. The symptom will be SSH’s failure to set the $DISPLAY variable and an error in your system log looking something like this:

Jun 3 09:40:24 servername sshd[26432]: [ID 800057 auth.error] error: Failed to allocate internet-domain X11 display socket.

To fix this, you can either install Sun’s latest socfs patch for your version of the OS, or simply force sshd into IPv4 mode by doing the following:

Edit you sshd_config file, adding the following:

# IPv4 only
ListenAddress 0.0.0.0

Edit your sshd startup script to issue a “-4″ to sshd on start:

case "$1" in
'start')
echo 'starting ssh daemon'
/usr/local/sbin/sshd -4
;;

Restart sshd, and that should pretty much do it… Enjoy.

Blank Window When SSH Forwarding X11 Sessions

There are a number of applications running on our servers that have GUI’s that I need to display on the Mac in my office. While the traditional method of exporting the server-side display to my desktop works, it is inherently insecure because the entire session, including any passwords that may be sent are all transmitted in clear-text.

Better to use SSH X11 forwarding. This way the entire session is encrypted and nobody can snoop your passwords. The process of using SSH X11 Forwarding goes something like this:

On the server-side (the machine from which you want to forward the display, make sure this line is in your /etc/ssh/sshd_config file:

X11Forwarding yes

If you had to add it, restart the sshd service.

/sbin/service sshd restart

Now, from the client-side (the machine on which you want to display the forwarded X11 application) connect to the server-side machine with the -X flag. Like so:

ssh -X username@remoteserver

Now you should be able to start X applications and have them display on your client machine through an ssh tunnel. If you are like me, however, some Java applications will not display correctly. Instead of popping up a window with the full application in it, I would only see a totally useless blank window. This frustrated me for months until I found this article at kraftek.com that details how to resolve the problem.

It turns out that all you have to do is put this line in the ssh_config file on your client-side:

ForwardX11Trusted yes

After logging out and back into the server-side machine, everything worked perfectly.

Disable SSH Root Logins on RHEL

For one reason or another RHEL does not disallow incoming ssh connections as root. This is, of course a glaring security problem which should be addressed for all systems that allow ssh connections to be made from any but the most restricted networks.

The best practice, of course, would be to make the initial ssh connection as an unprivileged user and then use the “su” command to promote yourself to root. This way, even if an attacker managed to get into the system, it would be as an unprivileged user and they would not able to do much harm. Allowing incoming ssh connections at root leaves you much more exposed to attack. Granted your root password is still protecting you, but it becomes your only layer of defense.

Ok, so how do we disallow incoming ssh connections as root on our RHEL box?

First, edit “/etc/ssh/sshd_config”

Find the section of the file that looks like this:

# Authentication:
#LoginGraceTime 2m
#PermitRootLogin yes
#StrictModes yes
#MaxAuthTries 6

Change this line:
#PermitRootLogin yes

To this:
PermitRootLogin no

Restart sshd:
/sbin/service sshd restart

MySQL Connection Over an ssl Tunnel

Rather than leave MySQL open to the world, consider firewalling it off and tunneling a connection over ssh.

This command will allow you to connect to your MySQL server on hostname.domain.com on localhost:7777.

ssh -L7777:127.0.0.1:3306 user@hostname.domain.com

Changes to Solaris ssh_config for Password-less SSH Login

Zach over at NoSheep has written a really nice article about how to configure a SSH trust relationship on UNIX systems. By doing this, you can prevent the system from prompting you for a password, thus allowing the user to use scripted solutions to move files around with sftp, etc. He didn’t mention, however, that to get this up and working correctly under Solaris, you have to uncomment the following lines in your /usr/local/etc/ssh_config file:

RSAAuthentication yes

And one of these lines. In most cases the Identity File will be id_rsa.

# IdentityFile ~/.ssh/identity
IdentityFile ~/.ssh/id_rsa
# IdentityFile ~/.ssh/id_dsa