Here’s a simple answer to a simple question. “How do I run a backup of MySQL to another machine without writing to the local server’s filesystem?” – this is especially useful if you are running out of space on the local server and cannot write a temporary file to the filesystem during backups.

Method one – this writes a remote file.
mysqldump [options] [db_name|--all-databases]| gzip -c | ssh user@host.com "cat > /path/to/new/file.sql.gz"

Method two – this writes directly into a remote mysql server
mysqldump [options] [db_name|--all-databases]| mysql --host=[remote host] –user=root –password=[pass] [db_name]

This is part four of an ongoing series about my experiences while writing the MySQL Admin Cookbook for Packt Publishing. All previous parts can be found under the mysql-admin-cookbook label.

This part will be about more software used in the process of writing the book. The last episode covered writing tools, file/version management and backups. What's up now is graphics programs, virtualization and PDF handling.

Outlining

For outlining and structuring thoughts I like mind-maps. I know they are not for everyone, but if you like them and do not want to spend a lot of money on …

A MySQL user group member saw that I use Poderosa as my ssh-on-Windows tool, and asked why I did not use PuTTY. My response was that I like having tabbed windows and hate having to keep opening another PuTTY program every time I want to open another connection. With Poderosa I can open a new connection with Alt-N, and I can even connect directly to Cygwin with an icon.

But Poderosa is not the tool I wanted to mention….Another user group member mentioned PuTTY Connection Manager. It wraps around PuTTY and gets the existing saved connections, makes a nicely tabbed browsing window where you can open sessions by double-clicking the connections, which are now listed on the right-hand side.

See screenshot below:

I have not played with …

Given the announcement earlier this week of an updated release of the actively developed MySQL Workbench 5.2, I thought I'd give it a quick look...

Install on Windows went without a hitch.

One thing I noticed, which I don't remember being previously available in MySQL GUI tools, is the ability to connect to a remote MySQL server via SSH tunneling.

Below is a screen shot:



Well done! A small, but significant addition for folks that require SSH to connect to their systems.

I did notice that the design in Workbench involves selecting "Standard TCP/IP over SSH" from the drop down and then providing credentials in a more "form" oriented layout. An "SSH" tab like we see in Navicat or SQLyog …

Only the other day I was talking with someone who does a lot of work on the shell command line, but hadn’t used the GNU screen tool, so I’d better scribble a post about it as I regard it as an absolute must-have for any remote work, for multiple reasons.

First of all, what screen does. You start screen inside a terminal session (local or SSH remote), and then you can create additional sessions though Ctrl-A C. The initial screen is number 0, the next one 1, and so on. You can switch between screens with Ctrl-A # where # is the screen number. This way, you can have multiple things going within a single ssh connection, very handy. But that’s not all!

If you get disconnected (it happens and you reconnect, your screen sessions will still be there, and running too. You can reattach with screen -r. To do a nice disconnect, you can do Ctrl-A D (detach) before …

Problem

A problem we occasionally see is Relay Log corruption, which is most frequently caused by network errors. At this point in time, the replication IO thread does not perform checksumming on incoming data (currently scheduled for MySQL 6.x). In the mean time, we have a relatively easy workaround: encrypt the replication connection. Because of the nature of encrypted connections, they have to checksum each packet.

Solution 1: Replication over SSH Tunnel

This is the easiest to setup. You simply need to do the following on the Slave:

shell> ssh -f user@master.server -L 4306:master.server:3306 -N

This sets up the tunnel. slave.server:4306 is now a tunnelled link to master.server:3306. So now, you just need to alter the Slave to go through the tunnel:

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO …

Month ago I was on a vacation and as usual even though our hotel provided us with an internet connection on a pretty decent speeds, I wasn’t able to work there because they’ve banned all tcp ports but some major ones (like 80, 21, etc) and I needed to be able to use ssh, mysql, IMs and other non-web software.

After a short research I’ve found a pretty simple to set up and easy to use approach to such a connection problems I’d like to describe here.

First, you’ll need someone (or you can do it before leaving home) to start an ssh daemon on port 80 on one of your servers. I use one of my Slicehub slices for this to permanently have an ability to use it. You can do it like this (if it is a temporary solution):

Notice: this `which sshd` was used because on some OSes sshd does not want to start w/o an …

If you are a GUI-oriented person, you need not read this. But if you are like me, you make heavy use of the console. If you are managing many machines as well as your own Linux workstation, it’s VERY important to know where your console session is.

Too many times in the past I had wanted to bring down my workstation, and would type “shutdown” or “reboot” in the console window, only to find out to my horrors that the console was really a remote session to one of my web servers serving up hundreds of web sites.

Whoops!

Well, that prompted me into developing a solution where I can tell at a glance where I happened to be logged in. This way,  I wouldn’t be in danger of issuing dangerous commands on the wrong server. And if you are working for someone else, it also keeps you from being FIRED!

I use KDE to do my development and …

I have used the ~/.ssh/authorized_keys file with OpenSSH for ages. However, I always treated it as just a dumb list of keys, where I would dump my public key whenever I needed access to a new account or host.

But today I learned from this blog entry that there is a bit more to it. Details are available from `man 8 sshd'.

Basically, each key can be prefixed by a list of options which restrict the kind of access granted to a connection attempt providing the given key.

Of particular interest is the command="/usr/local/bin/foobar" option. This makes sshd run the given command on connect, disabling the normal login shell or direct execution of commands supplied by the remote user. There are also a few further options for disabling port forwarding, …

This week aside from tons of different tasks I was working on one of MMM users complaint regarding some issues with MMM on Solaris 10. I knew that this OS has not so user (admin) friendly environment (especially for people with strong GNU-related background), but had no other options and decided to install Solaris 10 in VMWare Fusion on my desktop.

Installation was a bit strange comparing to Debian/RHEL/Ubuntu and FreeBSD where I have a strong experience, but I’ve managed to install it successfully. The major problem after my first boot was a lack of knowledge about how things could be done in Solaris… Below I’ll describe what generic Linux admin could do with Solaris to make it easier to use and more friendly for GNU-addicted mind

Notice: If you’re …