Posts Tagged ‘key’

How to check MASTER / SLAVE MySQL nodes status – Check MySQL Replication Status

Thursday, April 19th, 2012

I'm doing replication for one server. Its not the first time I do configure replication between two MySQL database nodes, however since I haven't done it for a few years, my "know how" has mostly vanished so I had some troubles in setting it up. Once I followed some steps to configure replication I had to check if the two MASTER / Slave MySQL db nodes communicate properly. Hence I decided to drop a short post on that just in case if someone has to do the same or if I myself forget how I did it so I can check later on:

1. Check if MASTER MySQL server node is configured properly

The standard way to check a MySQL master node status info is with:
 

mysql> show master status;
+——————+———-+———————————————————+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+———————————————————+——————+
| mysql-bin.000007 | 106 | database1,database2,database3 | |
+——————+———-+———————————————————+——————+
1 row in set (0.00 sec)

By putting \G some extra status info is provided:
 

mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000007
Position: 106
Binlog_Do_DB: database1,database2,database3
Binlog_Ignore_DB:
1 row in set (0.00 sec)

ERROR:
No query specified

2. Check if Slave MySQL node is configured properly

To check status of the slave the cmd is:
 

mysql> show slave status;

The command returns an output like:
 

mysql> show slave status;+———————————-+————-+————-+————-+—————+——————+———————+————————-+—————+———————–+——————+——————-+——————————————————-+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+——————————-+—————+—————+—————-+—————-+| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |+———————————-+————-+————-+————-+—————+——————+———————+————————-+—————+———————–+——————+——————-+——————————————————-+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+——————————-+—————+—————+—————-+—————-+| Waiting for master to send event | HOST_NAME.COM | slave_user | 3306 | 10 | mysql-bin.000007 | 106 | mysqld-relay-bin.000002 | 251 | mysql-bin.000007 | Yes | Yes | database1,database2,database3 | | | | | | 0 | | 0 | 106 | 407 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | |+———————————-+————-+————-+————-+—————+——————+———————+————————-+—————+———————–+——————+——————-+——————————————————-+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+——————————-+—————+—————+—————-+—————-+

As you can see the output is not too readable, as there are too many columns and data to be displayed and this doesn't fit neither a text console nor a graphical terminal emulator.

To get more readable (more verbose) status for the SQL SLAVE, its better to use command:
 

mysql> show slave status\G;

Here is a sample returned output:
 

mysql> show slave status\G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: HOST_NAME.COM Master_User: slave_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 106 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: database1,database2,database3 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: 106 Relay_Log_Space: 407 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec)ERROR: No query specified

If show master status or shwo slave status commands didn't reveal replication issue, one needs to stare at the mysql log for more info.

Share this on

Don’t revoke GoDaddy SSL certificate. (Expired) Revoked SSL is impossible to revert

Thursday, April 12th, 2012

One of our company SSL (https) Certificates recently expired so I needed to renew the SSL certificate.

I was in a hurry doing plenty of other stuffs so it seemed logical for me to Revoke the Certificate. I thought revoking the certificate will simply cancel it and afterwards, in Godaddy's SSL (Manager Certificates) interface the Revoked – Cancelled certificate will re-appear in the menu, ready to be generated in the same way as earlier I initially generated the Godaddy's bought SSL certificate

Hence I proceeded and used Revoke button:

Godaddy SSL certificate manager browser certificate Screenshot

Well guess what my calculations, were wrong.
 Revoking, just cancel it. The  revoked domain SSL certificate did not show up again in Godaddy's Cert Manager and I have no way from their interface to revert the changes.

To deal with the situation, I contacted Godaddy Support immediately with the following inquiry:
 

Other : Revoked SSL Certificate
Issue :
Hello we have revoked the SSL certificate for our domain our.domain-name.com.
 Can we revert back the certificate as it was.
If not how to generate a new key for our domain https://our.domain-name.com
Thanks in Advance.
Kindest Regards

"My-Company-name" Tech Support

In 5 hours time I received the following tech support answer:
 

Dear Tech Support,

Thank you for contacting Online Support. It is not possible to reinstate a canceled certificate. You will need to purchase a new certificate. I have requested that a refund be applied to your account. Once the credit appears in your account, please allow 5-7 business days to see the funds applied to the associated payment method. Thank you for your patience and understanding in this matter.

Please let us know if we can help you in any other way.

Sincerely,

Christian P.
Online Support Team
Customer Inquiry
Name : Cadia Tech Support
Domain Name : our.domain-name.com
ShopperID : xxxxxxxxx
Phone : xxxxxxxx
Shopper Validated : Yes
Browser : Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.3) Gecko/20100401 Firefox/3.6.3

Apparently Godaddy, can work out a bit on their tech support answering time 5 hours for a simple reply is quite long.

Now taking in consideration, above reply from Godady, my only options are to either wait for 5 to 7 (business days) or buy a new credit for SSL certificate.

Buying a new credit will probably not happen as our company is experiencing some financial troubles because of the crisis. So I guess we will have to wait for this 7 days at worst. So again if you wonder to REVOKE or not an SSL certificate. Think again …

Just a small note to make here, that Godaddy has a very straight forward way to just renew an expered certificate, which I succesfully later have done for 4 domains. Well, if only I knew earlier what REVOKE SSL cert really does I wouldn't have ended in this mess …

Share this on

How to take area screenshots in GNOME – Take quick area selection screenshots in G* / Linux and BSD

Thursday, March 15th, 2012

Quick Area screenshot in GNOME how to make quick area selection screenshots in Linux and FreeBSD gnome-screenshot shot

Often when, you do something on your PC, you need to make a quick screenshot of a screen area.. Yes GNOME's feature to take complete screenshots of Screen with Print Screen SysRQ and consequential picture edit with GIMP is one way, but this is far away from quick. This method to chop out of a complete display screenshot usually takes from 40 secs to 1 minute to properly cut and save a selection of the whole picture.
Another common use, that I love in GNOME is the ALT + Print Screen SysRQ key combination. alt+ print scr sysrq is handy while taking a single window screenshot is desired. Anyways often you only need to make a screenshot of a tiny area of the screen. Many people might think this is not possible currently in GNOME, but they will be wrong as there are no impossible but hard things to achieve on Linux / FreeBSD ;)

There are at least two ways using a predefined command for taking quick area screen snapshot.

1. Taking quick area screenshot by using ImageMagick's import command

To use import you will need to have installed ImageMagickswiss army knife of command line image manipulation ;)
For area screenshot with import, press ALT+F2 and type inside Run Application box:

Screenshot GNOME run application GNU / Linux Debian ImageMagick import area screenshot

import -frame screenshot.png

Now make the selection of the exact screen area you would like to screeshot in file screenshot.png
Note that screenshot.png file will be saved by default in your home directory as it is read from $HOME shell variable:

hipo@noah:~$ echo $HOME/home/hipo
hipo@noah:~$ ls -al screenshot.png
-rw-r--r-- 1 hipo hipo 4950 Mar 14 21:11 screenshot.png

You see my $HOME equals /home/hipo, therefore screenshot.png just grabbed is saved in there.

One downside of taking the screenshot with import is that picture snapshot is not further edittable, if it has to be further processed with GIMP or some other graphic editor program.

In the screenshot, below I show you one screen area of my XMMS taken with import -frame screenshot.png cmd:

XMMS Screen Area Screenshot import screenshot

Trying to open the screenshot.png, file with GIMP displays the following error in GIMP:

PNG image message PNG the file specifies offset that caused the layer to be positioned outiside image GIMP screenshot

Not all area snapshots taken with import -frame, create this issue sometimes screenshots are opening in GIMP but only area of the screenshot.png is visible in gimp.

Thanksfull, there is work around to this issue by converting the import generated PNG format picture to JPEG with ImageMagick's convert and then edit the .JPEG with GIMP etc.:

hipo@noah:~$ convert screenshot.png screenshot.jpg

Hence to permanently work around it, in case you intend to apply (GIMP modifications), once area snapshot is made instruct import to save its output picture in .jpeg, e.g.:

hipo@noah:~$ import -frame screenshot.jpeg

2. Taking quick area screenshot using gnome-screenshot cmd

Once again invoke the GNOME command Launcher by pressing Alt+F2 (holding alt and pressing F2) and type in the launch box:

gnome-screenshot -a

gnome-screenshot Run Application in GNOME 2.30 on Debian GNU / Linux

Below is a small area from my desktop, chopped with gnome-screenshot :)

GNOME desktop area chop screenshot with gnome-screenshot on my home Debian Linux

You see on above screenshot a tiny (picture) icon one of the greatest, if not the greatest bulgarian saint – saint John of Rila. St. John's lived as hermit for many years in Rila mountain and by God's grace possessed incorruptable body. His incorruptable body is still kept and can be venerated in Rila Monastery. The monastery is located 160 km from Bulgaria's capital city Sofia

St. Johns first Bulgarian established monastery Rila Monastery is currently the biggest functioing monastery in Bulgaria. The saints monastery is considered one of the most holy places in Bulgaria. If you have a travel or plan a holiday in Bulgaria, I warmly recommend you go there and venerate the saint incorruptable relics.

3. Binding keys to allow quick area screenshot taking with gnome-screenshot in GNOME

This configuration is for GNOME 2.x and is tested to work on my Debian (Squeeze 6.0), GNOME ver. 2.30.2, it should work in earlier Ubuntu versions shipped with GNOME 2.2.xx too. As I've red on the Internet it works well with Ubuntu 10.10Binding a key for screenshot area grab, should be working properly also on any GNOME 2.2.x supporting OS, including the BSD family OSes (FreeBSD, OpenBSD, NetBSD)

a) setting gnome-screenshot key binding for interactive screenshot area grab

Navigate the mouse cursor to GNOME main menus panel in left top, where you see (Applications, Places, System).
Therein use menus:

System -> Preferences -> Keybord Shortcuts -> Add ->

Alternatively if you prefer you can directly invoke the Keyboard Shortcuts configuration with command:

hipo@noah:~$ gnome-keybinding-properties

Further on, assign a shortcut by filling in something like:

name: grab-screen-area
command: gnome-screenshot -i -a

GNOME add keyboard shortcut map key for area interactive screenshot

press Apply and next map a key to the new defined key binding:

GNOME add keyboard shortcut map key

Under the Shortcut column click on Disabled and assign some key combination to invoke the cmd for example Ctrl+F4

The command gnome-screenshot -i makes gnome-screenshot, show interactive make screenshot dialog like the one in below screenshot.

GNOME screenshot interactive screenshot select area grab shot

b) creating gnome-screenshot -a area screenshot key binding for quick area screenshots "on the fly"

The procedure is precisely the same as with adding interactive screenshot; Under Keyboard Shortcuts GNOME config assign new key binding by pressing Add button and adding:

name: grab-screen-area1
command: gnome-screenshot -a

Once again in Shortcut column in line starting with grab-screen-area1 add your desired key switch. I personally like Ctrl+Print Screen SysRQ as it is close to the default GNOME key combination assigned for taking screenshot for a Windows Alt+Print SysRq

It was logical, that this key binding should work and a direct selection mouse cursor to appear once Alt+Print SysRQ is pressed, however for some reason this is not working (hmm, maybe due to bug) ??

Thanksfully it is always possible to substitute the just assigned gnome-screenshot -a key binding with import -frame /home/hipo/Desktop/screenshot.png

If you have followed literally my article so far and you did tried to place a bind for gnome-screenshot -a, modifty grab-screen-area1 to be something like:

name: grab-screen-area1
command: import -frame /home/hipo/Desktop/screenshot.png

Where modify the path /home/hipo/Desktop/screenshot.png, to wherever you prefer the region screep capture to be stored.

c) bind keys for delayed screenshot

This also a handy binding, especially if you every now and then need to make screenshots of screen with a few secs interval.
Add one more keyboard shortcut;

name: grab-screen-area2
command: gnome-screenshot -d 5

Assign a key to make a screenshot of the active display after a delay of 5 seconds. I prefer Ctrl+F5

Onwards every time you would like to make an area screenshot, just use the defined keys:

Ctrl+F4 - will prompt you interactively for the precise type of screenshot you would like to take
Ctrl+Print SysRQ - will prompt you for a direct area to select and once selected will immediately screenshot it
Ctrl+F5 - would do delayed screenshot of entire screen after a delay of 5 seconds

4. Adding border and drop shadow effects with gnome-screenshot Actually, there is plenty of interesting things to do with Screenshots which I never thought were possible.
While reading gnome-screenshot's man page, I've stumbled to an interesting argument:

-e, --effect=EFFECT,
Add an effect to the outside of the screenshot border. EFFECT can be ``shadow'' (adding drop shadow), ``border'' (adding
rectangular space around the screenshot) or ``none'' (no effect). Default is ``none''.

This would have been a nice feature but as of time of writting this article, untofrtunately it is not working in GNOME 2.30.2. I'm not sure if this is a local Debian bug, however I suspect on other Linux distributions with different GNOME build configuration, this features might be working well. My guess here is drop shadow effect and border effect are not working because, gnome-screenshot was compiled without (support for ImageMagick?).
Anyways the way the feature is supposed to be work is by invoking commands:
:

hipo@noah:~$ gnome-screenshot --border-effect=shadow
hipo@noah:~$ nome-screenshot --border-effect=border

The same basic effects, are also available through GIMP's menus:

Image -> Effects

5. Setting default behaviour of gnome-screenshot in gconf-editor GConf (Gnome config registry db)

Experienced, GNOME users should already know about the existence of gconf-editor and the gnome registry database. For those who have don't, coming from MS-Windows background gconf-editor is GNOME (graphical environment) equivalent to Microsoft Windows registry regedit command

gconf-editor can be used to atune the way the screenshots are taken by default. To do so, launch gconf-editor cmd and follow to sub-structure:

/ -> apps -> gnome-screenshot

gconf-editor GNOME screenshot border effect none default gnome-screenshot gnome behaviour

The settings in above screenshot are configurations which are used by default by gnome-screenshot, right after install.
You can play with the options to change the default way PrintScreen SysRQ key press will take screenshots.
Here is one example for changing the gnome-screenshot default GNOME behaviour:

GConf Editor GNOME screenshot, border effect drop shadow and include border option set on Linux Debian

As you can see in above screenshot, I've changed my default gnome-screenshot snap taking to include a drop shadow effect:
Name | Value
border_effect | shadow include_border | (tick on)
last_save_directory | file://home/hipo/Desktop

As you see you can also control, where by default gnome-screenshot will save its screenshots, by default, its saved in $HOME/Desktop
. If you prefer some custom directory to only contain Screenshots taken for instance $HOME/Screenshots, create the directory:
hipo@noah:~$ mkdir ~/Screenshots

and then change the value for last_save_directory gconf var:

last_save_directory | file://home/hipo/Screenshots

Once settings are applied screenshots with Print Screen SysRQ key will be made with Shadow Border effect and saved in /home/hipo/Screenshots

Strangely enough, changing gnome-screenshot default screenshotting values to include screenshot effects like drop shadow or screenshot border effect works just fine.
Even though gnome-screenshot –border-effect=shadow and gnome-screenshot –border-effect=border doesn't directly affect the current screenshot to be made, I've later noticed writting this two commands in the gnome-terminal, does change the border settings for gconf-editor screenshot border.

If you enjoyed, this article and you intend to become "a professional screnshotter" :) , you might also enjoy my two other articles:

Happy screenshotting ;)

Share this on

Geki2 and Geki3 a Xenon 2 Megablast like games for GNU / Linux and FreeBSD

Thursday, December 22nd, 2011

Do you remember the old arcade spaceship shooter Xenon 2 Megablast? I do ;) For all those who are too young to remember, here are two screenshots:

Xenon2 Main game Screen PC DOS ver

Xenon 2 megablast PC DOS level screenshot

Even though Xenon 2 Megablast original can now be played using dosbox DOS emulator. Its interesting to mention I've found two Linux games that more or less can be qualitified to resemble Xenon 2.
The games are Native Free Software games and existing in package repositories of most Linux distributions and *BSD port trees.

Geki 2 and Geki 3 are of a less quality to Xenon but still, the game experience is nice and is among the Arcade shooter games to bring you fun in the boring days if you're on GNU / Linux or FreeBSD Free OS platforms.

Installing Geki2 and Geki3 on Debian and Ubuntu Linux is standard with apt:

debian:~# apt-get install geki2 geki3
...

On Debian GNU / Linux , after installed the games would not create GNOME Applications -> Games -> game startup shortcuts, however the game startups will get added in GNOME Applications Menu under:

Applications -> Debian -> Games -> Action -> Geki 2
and
Applications -> Debian -> Games -> Action -> Geki 3

The games can be launched also manually with commands:

geki2

Geki 2 Linux Xenon 2 like game Main Menu

or

geki3
Geki3 gameplay screenshot Debian Linux

Geki 2 is a way closer to Xenon 2 as it has similar look and feel and the same vertical direction the spaceship is navigated.
In Geki 3 still the shoot 'em' up spaceship like arcade is present, however instead of penguin you have to fly a flying penguin, as well as the spaceship move direction is horizontal.

 Both the games have the same sound and music effects. The game music and effects are not of top quality but are not bad. In general  the games surely gives some of the arcade atmosphere.

Geki 2 GNU Linux Xenon 2 like vertical shooter arcade
Geki 2 Xenon 2 Megablast like on Debian Linux

In the tradition of the arcade games at the end of each level in both games you face the Level Boss Enemy, you should destroy.

Geki3 Level boss Debian Linux Screenshot
As you can see in below's screenshot the overall graphics of GEKI 3 is poorer while compared to GEKI 2

still GEKI 2 gampley is fun and addictive and I would say not less enjoyable than GEKI 2.
 At times I even think that Geki 3 is more fun because it is more dynamic.

 Maybe other reason, why I enjoyed more Geki 3 is also the fact that Geki 2 is a way harder to play. Dying only 3 times in the game you get  GAME OVER  and the next game you're started from the beginning of the same level you died in …

Geki2 Linux different shooting Screenshot

 Something really annoying that affects both the games; there is no option to play them in Fullscreen mode! ARGH!

Game controls for Geki2 and Geki3 are identical as follows:

Up - Arrow up key
Down - Down arrow key
Right - Right arrow key
left - Left arrow key
Shoot - z or Space
Pause - s

Geki2 and Geki3 are fun and can kill some time, but definitely aren't that (professional) as other spaceship shoot'em'up arcades for Linux and BSD. Games like Starfighter , Critical Mass or  powermanga .
 Lest that they are two worthy to install and play on your Free Software OS.

Share this on

How to renew self signed QMAIL toaster and QMAIL rocks expired SSL pem certificate

Friday, September 2nd, 2011

One of the QMAIL server installs, I have installed very long time ago. I've been notified by clients, that the certificate of the mail server has expired and therefore I had to quickly renew the certificate.

This qmail installation, SSL certificates were located in /var/qmail/control under the names servercert.key and cervercert.pem

Renewing the certificates with a new self signed ones is pretty straight forward, to renew them I had to issue the following commands:

1. Generate servercert encoded key with 1024 bit encoding

debian:~# cd /var/qmail/control
debian:/var/qmail/control# openssl genrsa -des3 -out servercert.key.enc 1024Generating RSA private key, 1024 bit long modulus
...........++++++
.........++++++
e is 65537 (0x10001)
Enter pass phrase for servercert.key.enc:
Verifying - Enter pass phrase for servercert.key.enc:

In the Enter pass phrase for servercert.key.enc I typed twice my encoded key password, any password is good, here though using a stronger one is better.

2. Generate the servercert.key file

debian:/var/qmail/control# openssl rsa -in servercert.key.enc -out servercert.key
Enter pass phrase for servercert.key.enc:
writing RSA key

3. Generate the certificate request

debian:/var/qmail/control# openssl req -new -key servercert.key -out servercert.csr
debian:/var/qmail/control# openssl rsa -in servercert.key.enc -out servercert.key
Enter pass phrase for servercert.key.enc:writing RSA key
root@soccerfame:/var/qmail/control# openssl req -new -key servercert.key -out servercert.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:UK
State or Province Name (full name) [Some-State]:London
Locality Name (eg, city) []:London
Organization Name (eg, company) [Internet Widgits Pty Ltd]:My Company
Organizational Unit Name (eg, section) []:My Org
Common Name (eg, YOUR name) []:
Email Address []:admin@adminmail.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

In the above prompts its necessery to fill in the company name and location, as each of the prompts clearly states.

4. Sign the just generated certificate request

debian:/var/qmail/control# openssl x509 -req -days 9999 -in servercert.csr -signkey servercert.key -out servercert.crt
Notice the option -days 9999 this option instructs the newly generated self signed certificate to be valid for 9999 days which is quite a long time, the reason why the previous generated self signed certificate expired was that it was built for only 365 days

5. Fix the newly generated servercert.pem permissions debian:~# cd /var/qmail/control
debian:/var/qmail/control# chmod 640 servercert.pem
debian:/var/qmail/control# chown vpopmail:vchkpw servercert.pem
debian:/var/qmail/control# cp -f servercert.pem clientcert.pem
debian:/var/qmail/control# chown root:qmail clientcert.pem
debian:/var/qmail/control# chmod 640 clientcert.pem

Finally to load the new certificate, restart of qmail is required:

6. Restart qmail server

debian:/var/qmail/control# qmailctl restart
Restarting qmail:
* Stopping qmail-smtpd.
* Sending qmail-send SIGTERM and restarting.
* Restarting qmail-smtpd.

Test the newly installed certificate

To test the newly installed SSL certificate use the following commands:

debian:~# openssl s_client -crlf -connect localhost:465 -quiet
depth=0 /C=UK/ST=London/L=London/O=My Org/OU=My Company/emailAddress=admin@adminmail.com
verify error:num=18:self signed certificate
verify return:1
...
debian:~# openssl s_client -starttls smtp -crlf -connect localhost:25 -quiet
depth=0 /C=UK/ST=London/L=London/O=My Org/OU=My Company/emailAddress=admin@adminmail.com
verify error:num=18:self signed certificate
verify return:1
250 AUTH LOGIN PLAIN CRAM-MD5
...

If an error is returned like 32943:error:140770FC:SSL routines:SSL23_GET_SERVER_HELLO:unknown protocol:s23_clnt.c:607: this means that SSL variable in the qmail-smtpdssl/run script is set to 0.

To solve this error, change SSL=0 to SSL=1 in /var/qmail/supervise/qmail-smtpdssl/run and do qmailctl restart

The error verify return:1 displayed is perfectly fine and it's more of a warning than an error as it just reports the certificate is self signed.

Share this on

How to tune MySQL Server to increase MySQL performance using mysqltuner.pl and Tuning-primer.sh

Tuesday, August 9th, 2011

MySQL Easy performance tuning with mysqltuner.pl and Tuning-primer.sh scripts

Improving MySQL performance is crucial for improving a website responce times, reduce server load and improve overall work efficiency of a mysql database server.

I've seen however many Linux System administrators who does belittle or completely miss the significance of tuning a newly installed MySQL server installation.
The reason behind that is probably caused by fact that many people think MySQL config variables, would not significantly improve performance and does not pay back for optimization efforts. Moreover there are a bunch of system admins who has to take care for numerous services so they don't have time to get good knowledge to optimize MySQL servers.
Thus many admins and webmasters nowdays, think optimizations depend mostly on the side of the website programmers.
It's also sometimes falsely believed that optimizing a MySQL server could reduce the overall server stability.

With the boom of Internet website building and internet marketing, many webmasters emerged and almost anybody with almost no knowledge on GNU/Linux or minimal or no knowledge on PHP can start his Online store, open a blog or create a website powered by some CMS like joomla.
Thus nowdays many servers even doesn't have a hired system administrators but are managed by people whose knowledge on *Nix is almost next to zero, this is another reason why dozens of MySQL installations online are a default ones and are not taking a good advantage of the server hardware.

The incrase of website visitors leads people servers expectations for hardware also to grow, thus many companies simply buy a new hardware instead of taking the few time to investigate on how current server hardware can be utilized better.
In that manner of thought I though it will be a good idea to write this small article on Tuning mysql servers with two scripts Tuning-primer.sh and mysqltuner.pl.
The scripts are ultra easy to use and does not require only a minimal knowledge on MySQL, Linux or (*BSD *nix if sql is running on BSD).
Tuning-primer.sh and mysqltuner.pl are therefore suitable for a quick MySQL server optimizations to even people who are no computer experts.

I use this two scripts for MySQL server optimizations on almost every new configured GNU/Linux with a MySQL backend.
Use of the script comes to simply download with wget, lynx, curl or some other web client and execute it on the server host which is already running the MySQL server.

Here is an example of how simple it is to run the scripts to Optimize MySQL:

debian:~# perl mysqltuner.pl
>> MySQLTuner 1.2.0 - Major Hayden >major@mhtx.net<
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.49-3
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 6G (Tables: 952)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 12

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 2h 3m 35s (68M q [732.193 qps], 610K conn, TX: 49B, RX: 11B)
[--] Reads / Writes: 76% / 24%
[--] Total buffers: 512.0M global + 2.8M per thread (2000 max threads)
[OK] Maximum possible memory usage: 6.0G (25% of installed RAM)
[OK] Slow queries: 0% (3K/68M)
[OK] Highest usage of available connections: 7% (159/2000)
[OK] Key buffer size / total MyISAM indexes: 230.0M/1.7G
[OK] Key buffer hit rate: 97.8% (11B cached / 257M reads)
[OK] Query cache efficiency: 76.6% (46M cached / 61M selects)
[!!] Query cache prunes per day: 1822075
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 2M sorts)
[!!] Joins performed without indexes: 63635
[OK] Temporary tables created on disk: 1% (26K on disk / 2M total)
[OK] Thread cache hit rate: 99% (159 created / 610K connections)
[!!] Table cache hit rate: 4% (1K open / 43K opened)
[OK] Open file limit used: 17% (2K/16K)
[OK] Table locks acquired immediately: 99% (36M immediate / 36M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 256M) [see warning above] join_buffer_size (> 256.0K, or always use indexes with joins) table_cache (> 7200)

You see there are plenty of things, the script reports, for the unexperienced most of the information can be happily skipped without need to know the cryptic output, the section of importance here is Recommendations for some clarity, I've made this section to show up bold.

The most imporant things from the Recommendations script output is actually the lines who give suggestions for incrase of certain variables for MySQL.In this example case this are the last three variables:
query_cache_size,
join_buffer_size and
table_cache

All of these variables are tuned from /etc/mysql/my.cnf (on Debian) and derivatives distros and from /etc/my.cnf on RHEL, CentOS, Fedora and the other RPM based Linux distributions.

On some custom server installs my.cnf is also located in /usr/local/mysql/etc/ or some other a bit more unstandard location ;)

Anyways now having the Recommendation from the script, it's necessery to edit my.cnf and try to double the values for the suggested variables.

First, I check if all the suggested variables are existent in my config with grep , if they're not then I'll simply add the variable with doubled size of the suggested values.
P.S: One note here is sometimes some values which are configured, are the default value for the MySQL server and does not have a record in my.cnf

debian:~# grep -E 'query_cache_size|join_buffer_size|table_cache' /etc/mysql/my.cnf table_cache = 7200
query_cache_size = 256M
join_buffer_size = 262144

All of my variables are in the config so, now edit my.cnf and set values to:
table_cache = 14400
query_cache_size = 512M
join_buffer_size = 524288

I always, however preserve the old variable's value, because sometimes raising the value might create problem and the MySql server might be unable to restart properly.
Thus before going with adding the new values make sure the old ones are commented with # , e.g.:
#table_cache = 7200
#query_cache_size = 256M
#join_buffer_size = 262144

I would recommend vim as editor of choice while editing my.cnf as vim completely rox ;) If you're not acquainted to vim use nano or mcedit or your editor of choice ;) :

debian:~# vim /etc/mysql/my.cnf
...

Assuming that the changes are made, it's time to restart MySQL to make sure the new values are read by the SQL server.

debian:~# /etc/init.d/mysql restart
* Stopping MySQL database server mysqld [ OK ]
* Starting MySQL database server mysqld [ OK ]
Checking for tables which need an upgrade, are corrupt or were not closed cleanly.

If mysql server fails, however to restart, make sure immediately you reverse back the changed variables to the commented values and restart once again via mysql init script to make server load.

Afterwards start adding the values one by one until find out which one is causing the mysqld to fail.

Now the second script (Tuning-primer.sh) is also really nice for MySQL performance optimizations are necessery. However it's less portable (as it's written in bash scripting language).
Consider running this script among different GNU/Linux distributious (especially the newer ones) might produce errors.
Tuning-primer.sh requires some minor code changes to be able to run on FreeBSD, NetBSD and OpenBSD *nices.

The way Tuning-primer.sh works is precisely like mysqltuner.pl , one runs it it gives some info about current running MySQL server and based on certain factors gives suggestions on how increasing or decresing certain my.cnf variables could reduce sql query bottlenecks, solve table locking issues as well as generally improve INSERT, UPDATE query times.

Here is an example output from tuning-primer.sh run on another server:

server:~# wget http://www.pc-freak.net/files/Tuning-primer.sh
...
server:~# sh Tuning-primer.sh
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 5.0.51a-24+lenny5 x86_64

Uptime = 8 days 10 hrs 19 min 8 sec
Avg. qps = 179
Total Questions = 130851322
Threads Connected = 1

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

SLOW QUERIES
Current long_query_time = 1 sec.
You have 16498 out of 130851322 that take longer than 1 sec. to complete
The slow query log is NOT enabled.
Your long_query_time seems to be fine

MAX CONNECTIONS
Current max_connections = 2000
Current threads_connected = 1
Historic max_used_connections = 85
The number of used connections is 4% of the configured maximum.
Your max_connections variable seems to be fine.

WORKER THREADS
Current thread_cache_size = 128
Current threads_cached = 84
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MEMORY USAGE
Tuning-primer.sh: line 994: let: expression expected
Max Memory Ever Allocated : 741 M
Configured Max Memory Limit : 5049 M
Total System Memory : 23640 M

KEY BUFFER
Current MyISAM index space = 1646 M
Current key_buffer_size = 476 M
Key cache miss rate is 1 / 56
Key buffer fill ratio = 90.00 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.

QUERY CACHE
Query cache is enabled
Current query_cache_size = 64 M
Current query_cache_used = 38 M
Current Query cache fill ratio = 59.90 %

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 256.00 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 128.00 K
You have had 111560 queries where a join could not use an index properly
You have had 91 joins without keys that check for key usage after each row
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

TABLE CACHE
Current table_cache value = 3600 tables
You have a total of 798 tables
You have 1904 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current tmp_table_size = 128 M
1% of tmp tables created were disk based
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128.00 K
Current table scan ratio = 797 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 1782
You may benefit from selective use of InnoDB.

As seen from script output, there are certain variables which might be increased a bit for better SQL performance, one such variable as suggested is key_buffer_size- (You could increase key_buffer_size)

Now the steps to make the tunings to my.cnf are precisely the same as with mysqltuner.pl, e.g.:
1. Preserve old config variables which will be changed by commenting them
2. Double value of current variables in my.cnf suggested by script
3. Restart Mysql server via /etc/init.d/mysql restart cmd.
4. If mysql runs fine monitor mysql performance with mtop or mytop for at least 15 mins / half an hour.

if all is fine run once again the tuning scripts to see if there are no further improvement suggestions, if there are more follow the 4 steps described procedure once again.

It's also a good idea that these scripts are periodically re-run on the server like once per few months as changes in SQL queries amounts and types will require changes in MySQL operational variables.
The authors of these nice scripts has done great job and have saved us a tons of nerves time, downtimes and money spend on meaningless hardware. So big thanks for the awesome scripts guys ;)
Finally after hopefully succesful deployment of changes, enjoy the incresed SQL server performance ;)

Share this on

How to check if newly installed SSL certificate for IMAP and IMAPS is properly installed

Tuesday, June 28th, 2011

Did you have to regenerate your SSL certificate for your mail server’s IMAP and IMAP SSL service?
Did you have to find out if the newly installed certificates are fine after install?

Here is how:

          root@server-hosting [/usr/local ]# openssl s_client -connect imap.example.com:993
root@server-hosting [/usr/local ]# openssl s_client -connect imap.example.com:143 -starttls imap

The output returned by this two commands will be the imap and imaps configured certificates as well as extensive info concerning the installed SSL, the last chunk of info to be spit is most crucial to know if certificate is fine.
It should be something like:

...
New, TLSv1/SSLv3, Cipher is AES256-SHA
Server public key is 1024 bit
Secure Renegotiation IS supported
Compression: NONE
Expansion: NONE
SSL-Session:
Protocol : TLSv1
Cipher : AES256-SHA
Session-ID: 0B69E91022CB56D64F56CFA08405944D9C4C0069EE4097890B98F1406CF084D5
Session-ID-ctx:
Master-Key: 13745B94E0C5A0604EB7529E7409251961DFD5F4134F3A8F
Key-Arg : None
Start Time: 1309265383
Timeout : 300 (sec)
Verify return code: 18 (self signed certificate)
---
. OK CAPABILITY completed
closed


Share this on

Reboot Safely a frozen (hanged up) Linux Server with Magic SysRQ Key combination

Monday, June 20th, 2011

Linux Magic SysRQ Key picture

Being a remote system administrator for many years from now, I’ve experienced many times sudden Linux crashes, mostly caused by system hardware overloads.

This is a common thing especially on busy Web Servers with (Apache/MySQL). Situations like this are really messy and many times in order to bringt the server back online, one has to either physically go to the Data Center or contact the Technical support to request a server reboot.

In this terrible times, data loss might occur if the Server reset button is used to cold reboot it.

Happily in 99% of the cases the data loss which might occur could be prevented with Linux’s kernel capability to support the Magic SysRQ key !

Having the Magic SysRQ key functionality being supported in Linux in past times wasn’t that common, thanksfully these days this has changed andlmost every Linux distrubution supports this handy feature.

Now you might wonder what is the magic with those Magic SysRQ key ?

Let me explain, Magic SysRQ is a kernel level functionality which supports even completely crashed Linux systems with the horrifying:

Kernel Panic
message to be properly shutdown.

Using Magic SysRQ instead of the mostly used indiced cold reboots is really advantageous, as all the opened files by programs on the crashed server which hanged will be properly saved and closed and thus possible data loss caused by the sudden server crash will be minimized.

One other good thing about the Magic SysRQ Key keyboard combination is that initiating the Magic SysRQ could be made blindly (no need for server monitor or display to present any error messages on the failed server).

Now to check if the magic sysrq is enabled on a server one should issue the command:

Here are some Magic SysRQ keyboard combinations one can use in case of server failure:

ALT+SYSRQ+M to dump memory info;
ALT+SYSRQ+P to dump processes states;
ALT+SYSRQ+S to sync disks;
ALT+SYSRQ+U to unmount all mounted filesystems;
ALT+SYSRQ+E to terminate processes;
ALT+SYSRQ+I to kill all processes
ALT+SYSRQ+U to try to unmount once again;
ALT+SYSRQ+B to reboot.

I would skip to explain what each of the keyboard combinations will do as I believe the above description explains it well.

One classics of combinations one might want to issue on a failed Linux server supporting the Magic SysRQ would be:

ALT+SYSRQ+R
ALT+SYSRQ+E
ALT+SYSRQ+I
ALT+SYSRQ+S
ALT+SYSRQ+U
ALT+SYSRQ+B

The ALT+SYSRQ+REISUB key combination is very popular among system administrators.
Also Magic SysRQ is heavily used by kernel developers as it includes many handy debugging options.

If you try the Magic SysRQ key on older servers and you realize it doesn’t react you will have to recompile the linux kernel and enable the CONFIG_MAGIC_SYSRQ kernel time compilation option.

The Magic SysRQ can also be initiated remotely on a properly running server ;) by initiating:

server:~# echo b > /proc/sysrq-trigger

This command would be useful if you want to just have fun and test that magic sysrq works on your system for sure ;)

To sum it up using the Magic SysRQ will guarantee your locked up, server a Safe Reboot and will hopefully save you a lot of time for backups recovery.

Share this on

How to fix wicd 1.7.0+ds1-5 Connection Failed: Bad Password on Ubuntu 10.10 (Maverick Merkaaat)

Tuesday, May 3rd, 2011

I’ve been struggling with fixing a nasty error with wicd network manager for about 2 hours.
The exact error message I faced was:

Connection Failed: Bad Password

The issue occured after some suggested updates from the Ubuntu graphical update tool.
The wireless network to which it was connected was a WPA-PSK (WPA2) Passphrase authentication.
The network key was properly typed in and was working well on another system so the error Connection Failed: Bad Password made no sense.

There was nothing unusual in /var/log/wicd/wicd.log , that made me even more curious about what might be causing the error.After a lot of try outs and a lot of readings and tests I finally got the cause of the weird Bad Password errors produced by wicd

Weirdly enought, somehow the Ubuntu package update tool has installed the default gnome network-manager package.
The installed network-manager package has mismatched somehow the way wicd connects to wireless networks and as a cause the wpa_supplicant binary was not properly invoked.

As a consequence of the network-manager being present on the system the wpa_supplicant process which made the exact connection to the wireless network was not launching in, the exact wpa_supplicant invocation missing was:

wpa_supplicant -B -i wlan0 -c /var/lib/wicd/configurations/0022b0aa424a -D wext

Luckily the solution to the notebook wireless device unable to connect to the Wireless network was simple.

All I had to do is completely remove all occurance of network-manager packages installed on the Ubuntu system, by issuing the commands:

ubuntu:~# apt-get remove --yes network-manager
ubuntu:~# dpkg --purge network-manager-pptp-gnome network-manager-pptp network-manager

The reason for issuing the a dpkg –purge command was my desire to completely get rid of all kind of network-manager related configurations.

Now after re-connecting with wicd wireless manager, it worked fine ;)

Share this on