Posts Tagged ‘root’

Tracking I/O hard disk server bottlenecks with iostat on GNU / Linux and FreeBSD

Tuesday, March 27th, 2012

Hard disk overhead tracking on Linux and FreeBSD with iostat

I've earlier wrote an article How to find which processes are causing hard disk i/o overhead on Linux there I explained very rawly few tools which can be used to benchmark hard disk read / write operations. My prior article accent was on iotop and dstat and it just mentioned of iostat. Therefore I've wrote this short article in attempt to explain a bit more thoroughfully on how iostat can be used to track problems with excessive server I/O read/writes.

Here is the command man page description;
iostatReport Central Processing Unit (CPU) statistics and input/output statistics for devices, partitions and network filesystems

I will further proceed with few words on how iostat can be installed on various Linux distros, then point at few most common scenarious of use and a short explanation on the meaning of each of the command outputs.

1. Installing iostat on Linux

iostat is a swiss army knife of finding a server hard disk bottlenecks. Though it is a must have tool in the admin outfut, most of Linux distributions will not have iostat installed by default.
To have it on your server, you will need to install sysstat package:

a) On Debian / Ubuntu and other Debian GNU / Linux derivatives to install sysstat:

debian:~# apt-get --yes install sysstat

b) On Fedora, CentOS, RHEL etc. install is with yum:

[root@centos ~]# yum -y install sysstat

c) On Slackware Linux sysstat package which contains iostat is installed by default. 

d) In FreeBSD, there is no need for installation of any external package as iostat is part of the BSD world (bundle commands).
I should mention bsd iostat and Linux's iostat commands are not the same and hence there use to track down hard disk bottlenecks differs a bit, however the general logic of use is very similar as with most tools in BSD and Linux.

2. Checking a server hard disk for i/o disk bottlenecks on G* / Linux

Once having the sysstat installed on G* / Linux systems, the iostat command will be added in /usr/bin/iostat
a) To check what is the hard disk read writes per second (in megabytes) use:

debian:~# /usr/bin/iostat -m
Linux 2.6.32-5-amd64 (debian) 03/27/2012 _x86_64_ (8 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
15.34 0.36 2.76 2.66 0.00 78.88
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
sda 63.89 0.48 8.20 6730223 115541235
sdb 64.12 0.44 8.23 6244683 116039483
md0 2118.70 0.22 8.19 3041643 115528074

In the above output the server, where I issue the command is using sda and sdb configured in software RAID 1 array visible in the output as (md0)

The output of iostat should already be easily to read, for anyone who didn't used the tool here is a few lines explanation of the columns:

The %user 15.34 meaning is that 15.34 out of 100% possible i/o load is generad by system level read/write operations.
%nice – >Show the percentage of CPU utilization that occurred while executing at the user level with nice priority.
%iowait – just like the top command idle it shows the idle time when the system didn't have an outstanding disk I/O requests.
%steal – show percentage in time spent in time wait of CPU or virtual CPUs to service another virtual processor (high numbers of disk is sure sign for i/o problem).
%idle – almost the same as meaning to %iowait
tps – HDD transactions per second
MB_read/s (column) – shows the actual Disk reads in Mbytes at the time of issuing iostat
MB_wrtn/s – displays the writes p/s at the time of iostat invocation
MB_read – shows the hard disk read operations in megabytes, since the server boot 'till moment of invocation of iostat
MB_wrtn – gives the number of Megabytes written on HDD since the last server boot filesystem mount

The reason why the Read / Write values for sda and sdb are similar in this example output is because my disks are configured in software RAID1 (mirror)

The above iostat output reveals in my specific case the server is experiencing mostly Disk writes (observable in the high MB_wrtn/s 8.19 md0 in the above sample output).

It also reveals, the I/O reads experienced on that server hard disk are mostly generated as a system (user level load) – see (%user 15.34 and md0 2118.70).

For all those not familiar with system also called user / level load, this is all kind of load which is generated by running programs on the server – (any kind of load not generated by the Linux kernel or loaded kernel modules).

b) To periodically keep an eye on HDD i/o operations with iostat, there are two ways:

– Use watch in conjunction with iostat;

[root@centos ~]# watch "/usr/bin/iostat -m"
Every 2.0s: iostat -m Tue Mar 27 11:00:30 2012
Linux 2.6.32-5-amd64 (centos) 03/27/2012 _x86_64_ (8 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
15.34 0.36 2.76 2.66 0.00 78.88
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
sda 63.89 0.48 8.20 6730255 115574152
sdb 64.12 0.44 8.23 6244718 116072400
md0 2118.94 0.22 8.20 3041710 115560990
Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
sda 55.00 0.01 25.75 0 51
sdb 52.50 0.00 24.75 0 49
md0 34661.00 0.01 135.38 0 270

Even though watch use and -d might appear like identical, they're not watch does refresh the screen, executing instruction similar to the clear command which clears screen on every 2 seconds, so the output looks like the top command refresh, while passing the -d 2 will output the iostat command output on every 2 secs in a row so all the data is visualized on the screen. Hence -d 2 in cases, where more thorough debug is necessery is better. However for a quick routine view watch + iostat is great too.

c) Outputting extra information for HDD input/output operations;

root@debian:~# iostat -x
Linux 2.6.32-5-amd64 (debian) 03/27/2012 _x86_64_ (8 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
15.34 0.36 2.76 2.66 0.00 78.88
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda 4.22 2047.33 12.01 51.88 977.44 16785.96 278.03 0.28 4.35 3.87 24.72
sdb 3.80 2047.61 11.97 52.15 906.93 16858.32 277.05 0.03 5.25 3.87 24.84
md0 0.00 0.00 20.72 2098.28 441.75 16784.05 8.13 0.00 0.00 0.00 0.00

This command will output extended useful Hard Disk info like;
r/s – number of read requests issued per second
w/s – number of write requests issued per second
rsec/s – numbers of sector reads per second
b>wsec/s – number of sectors wrote per second
etc. etc.

Most of ppl will never need to use this, but it is good to know it exists.

3. Tracking read / write (i/o) hard disk bottlenecks on FreeBSD

BSD's iostat is a bit different in terms of output and arguments.

a) Here is most basic use:

freebsd# /usr/sbin/iostat
tty ad0 cpu
tin tout KB/t tps MB/s us ni sy in id
1 561 45.18 44 1.95 14 0 5 0 82

b) Periodic watch of hdd i/o operations;

freebsd# iostat -c 10
tty ad0 cpu
tin tout KB/t tps MB/s us ni sy in id
1 562 45.19 44 1.95 14 0 5 0 82
0 307 51.96 113 5.73 44 0 24 0 32
0 234 58.12 98 5.56 16 0 7 0 77
0 43 0.00 0 0.00 1 0 0 0 99
0 485 0.00 0 0.00 2 0 0 0 98
0 43 0.00 0 0.00 0 0 1 0 99
0 43 0.00 0 0.00 0 0 0 0 100
...

As you see in the output, there is information like in the columns tty, tin, tout which is a bit hard to comprehend.
Thanksfully the tool has an option to print out only more essential i/o information:

freebsd# iostat -d -c 10
ad0
KB/t tps MB/s
45.19 44 1.95
58.12 97 5.52
54.81 108 5.78
0.00 0 0.00
0.00 0 0.00
0.00 0 0.00
20.48 25 0.50

The output info is quite self-explanatory.

Displaying a number of iostat values for hard disk reads can be also achieved by omitting -c option with:

freebsd# iostat -d 1 10
...

Tracking a specific hard disk partiotion with iostat is done with:

freebsd# iostat -n /dev/ad0s1a
tty cpu
tin tout us ni sy in id
1 577 14 0 5 0 81
c) Getting Hard disk read/write information with gstat

gstat is a FreeBSD tool to print statistics for GEOM disks. Its default behaviour is to refresh the screen in a similar fashion like top command, so its great for people who would like to periodically check all attached system hard disk and storage devices:

freebsd# gstat
dT: 1.002s w: 1.000s
L(q) ops/s r/s kBps ms/r w/s kBps ms/w %busy Name
0 10 0 0 0.0 10 260 2.6 15.6| ad0
0 10 0 0 0.0 10 260 2.6 11.4| ad0s1
0 10 0 0 0.0 10 260 2.8 12.5| ad0s1a
0 0 0 0 0.0 0 0 0.0 20.0| ad0s1b
0 0 0 0 0.0 0 0 0.0 0.0| ad0s1c
0 0 0 0 0.0 0 0 0.0 0.0| ad0s1d
0 0 0 0 0.0 0 0 0.0 0.0| ad0s1e
0 0 0 0 0.0 0 0 0.0 0.0| acd0

It even has colors if your tty supports colors 🙂

Another useful tool in debugging the culprit of excessive hdd I/O operations is procstat command:

Here is a sample procstat run to track (httpd) one of my processes imposing i/o hdd load:

freebsd# procstat -f 50404
PID COMM FD T V FLAGS REF OFFSET PRO NAME
50404 httpd cwd v d -------- - - - /
50404 httpd root v d -------- - - - /
50404 httpd 0 v c r------- 56 0 - -
50404 httpd 1 v c -w------ 56 0 - -
50404 httpd 2 v r -wa----- 56 75581 - /var/log/httpd-error.log
50404 httpd 3 s - rw------ 105 0 TCP ::.80 ::.0
50404 httpd 4 p - rw---n-- 56 0 - -
50404 httpd 5 p - rw------ 56 0 - -
50404 httpd 6 v r -wa----- 56 25161132 - /var/log/httpd-access.log
50404 httpd 7 v r rw------ 56 0 - /tmp/apr8QUOUW
50404 httpd 8 v r -w------ 56 0 - /var/run/accept.lock.49588
50404 httpd 9 v r -w------ 1 0 - /var/run/accept.lock.49588
50404 httpd 10 v r -w------ 1 0 - /tmp/apr8QUOUW
50404 httpd 11 ? - -------- 2 0 - -

Btw fstat is sometimes helpful in identifying the number of open files and trying to estimate which ones are putting the hdd load.
Hope this info helps someone. If you know better ways to track hdd excessive loads on Linux / BSD pls share 'em pls.
 

How to add a new MySQL user to have INSERT,UPDATE,DELETE permissions to a Database

Tuesday, October 25th, 2011

I needed to add a newly created MySQL user with no access to any database with no special permissions (user is created from phpmyadmin) with some permissions to a specific database which is used for the operation of a website, here are the MySQL CLI client commands I issued to make it work:

# mysql -u root -p
mysql> GRANT ALL ON Sql_User_DB.* TO Sql_User@localhost;
mysql> FLUSH PRIVILEGES;

Where in the Example Sql_User_DB is my example database to which the user is granted access and my sample user is Sql_User .
Note that without FLUSH PRIVILEGES; new privileges might not be active. 

To test further if all is fine login with Sql_User and try to list database tables.

$ mysql -u Sql_User -p
password:
mysql> USE Sql_User_DB;
mysql> SHOW TABLES;
...

How to disable ACPI on productive Linux servers to decrease kernel panics and increase CPU fan lifespan

Tuesday, May 15th, 2012

Linux TUX ACPI logo / Tux Hates ACPI logohttps://www.pc-freak.net/images/linux_tux_acpi_logo-tux-hates-acpi.png

Why would anyone disable ACPI support on a server machine??
Well  ACPI support kernel loaded code is just another piece of code constantly being present in the memory,  that makes the probability for a fatal memory mess up leading to  a fatal bug resulting in system crash (kernel panic) more likely.

Many computers ship with buggy or out of specifications ACPI firmware which can cause a severe oddities on a brand new bought piece of comp equipment.

One such oddity related to ACPI motherboard support problems is if you notice your machine randomly powering off or failing to boot with a brand new Linux installed on it.

Another reason to switch off ACPI code will would to be prevent the CPU FAN rotation from being kernel controlled.

If the kernel controls the CPU fan on  high CPU heat up it will instruct the fan to rotate quickly and on low system loads it will bring back the fan to loose speed.
 This frequent switch of FAN from high speed to low speed  increases the probability for a short fan damage due to frequent changes of fan speed. Such a fan damage leads often to  system outage due to fan failure to rotate properly.

Therefore in my view it is better ACPI support is switched off completely on  servers. On some servers ACPI is useful as it can be used to track CPU temperature with embedded motherboard sensors with lm_sensors or any piece of hardwre vendor specific software provided. On many machines, however lm_sensors will not properly recognize the integrated CPU temperature sensors and hence ACPI is mostly useless.

There are 3 ways to disable fully or partially ACPI support.

- One is to disable it straight for BIOS (best way IMHO)
- Disable via GRUB or LILO passing a kernel parameter
- Partial ACPI off-ing - /disabling the software that controls the CPU fan/

1. Disable ACPI in BIOS level

Press DEL, F1, F2, F10 or whatever the enter bios key combination is go through all the different menus (depending on the vios BENDOR) and make sure every occurance of ACPI is set to off / disable whatever it is called.

Below is a screenshot of menus with ACPI stuff on a motherboard equipped with Phoenix AwardBIOS:

BIOS ACPI Disable power Off Phoenix BIOS

This is the in my opinon best and safest way to disable ACPI power saving, Unfortunately some newer PCs lack the functionality to disable ACPI; (probably due to the crazy "green" policy the whole world is nowdays mad of).

If that's the case with you, thanksfully there is a "software way" to disable ACPI via passing kernel options via GRUB and LILO boot loaders.

2. Disabling ACPI support on kernel boot level through GRUB boot loader config

There is a tiny difference in command to pass in order to disable  ACPI depending on the Linux installed  GRUB ver. 1.x or GRUB 2.x.

a) In GRUB 0.99 (GRUB version 1)

Edit file /etc/grub/menu.lst or /etc/grub/grub.conf (location differs across Linux distribution). Therein append:

acpi=off

to the end of kernel command line.

Here is an example of a kernel command line with ACPI not disabled (example taken from CentOS server grub.conf):

[root@centos ~]# grep -i title -A 4 /etc/grub/grub.conf
title Red Hat Enterprise Linux Server (2.6.18-36.el5)
root (hd0,0)
kernel /vmlinuz-2.6.18-36.el5 ro root=/dev/VolGroup00/LogVol00 console=ttyS0,115200n8
initrd /initrd-2.6.18-36.el5.img

The edited version of the file with acpi=off included should look like so:

title Red Hat Enterprise Linux Server (2.6.18-36.el5)
root (hd0,0)
kernel /vmlinuz-2.6.18-36.el5 ro root=/dev/VolGroup00/LogVol00 console=ttyS0,115200n8 acpi=off
initrd /initrd-2.6.18-36.el5.img

The kernel option root=/dev/VolGroup00/LogVol00 means the the server is configured to use LVM (Logical Volume Manager).

b) Disabling ACPI on GRUB version 1.99 +

This version is by default installed on newer Ubuntu and Debian Linux-es.

In grub 1.99 on latest Debian Squeeze, the file to edit is located in /boot/grub/grub.cfg. The file is more messy than with its predecessor menu.lst (grub 0.99).
Thanks God there is no need to directly edit the file (though this is possible), but on newer Linuces (as of time of writting the post), there is another simplied grub config file /etc/grub/config

Hence to add the acpi=off to 1.99 open /etc/grub/config find the line reading:

GRUB_CMDLINE_LINUX_DEFAULT="quiet"

and append the "acpi=off" option, e.g. the line has to change to:

GRUB_CMDLINE_LINUX_DEFAULT="quiet acpi=off"

On some servers it might be better to also disable APIC along with ACPI:

Just in case you don't know what is the difference between ACPI and APIC, here is a short explanation:

ACPI = Advanced Configuration and Power Interface

APIC = Advanced Programmable Interrupt Controllers

ACPI is the system that controls your dynamic speed fans, the power button behavior, sleep states, etc.

APIC is the replacement for the old PIC chip that used to come imbedded on motherboards that allowed you to setup interrupts for your soundcard, ide controllers, etc.

Hence on some machines experiencing still problems with even ACPI switched off, it is helpful  to disable the APIC support too, by using:

acpi=off noapic noacpi

Anyways, while doing the changes, be very very cautious or you might end up with un-boot-able server. Don't blame me if this happens :); be sure you have a backup option if server doesn't boot.

To assure faultless kernel boot, GRUB has ability to be configured to automatically load up a second kernel if 1st one fails to boot, if you need that read the grub documentation on that.

To load up the kernel with the new setting, give it a restart:

[root@centos ~]# shutdown -r now
....

3. Disable ACPI support on kernel boot time on Slackware or other Linuxes still booting kernel with LILO

Still, some Linux distros like Slackware, decided to keep the old way and use LILO (LInux LOader) as a default boot loader.

Disabling ACPI support in LILO is done through /etc/lilo.conf

By default in /etc/lilo.conf, there is a line:

append= acpi=on

it should be changed to:

append= acpi=off

Next to load up the new acpi disabled setting, lilo has to be reloaded:

slackware:~# /sbin/lilo -c /etc/lilo.conf
....

Finally a reboot is required:

slackware:~# reboot
....

(If you don't have a physical access or someone near the server you better not 🙂 )

4. Disable ACPI fan control support on a running Linux server without restart

This is the most secure work-around, to disabling the ACPI control over the machine CPU fan, however it has a downside that still the ACPI code will be loaded in the kernel and could cause kernel issues possibly in the long run – lets say the machine has uptime of more than 2 years…

The acpi support on a user level  is controlled by acpid or haldaemon (depending on the Linux distro), hence to disable the fan control on servers this services has to be switched off:

a) disabling ACPI on Debian and deb based Linux-es

As of time of writting on Debian Linux servers acpid (Advanced Configuration and Power Interface event daemon) is there to control how power management will be handled. To disable it stop it as a service (if running):

debian:~# /etc/init.d/acpid stop

To permanently remove acpid from boot up on system boot disable it with update-rc.d:

debian:~# update-rc.d acpid disable 2 3 4 5
update-rc.d: using dependency based boot sequencing
insserv: Script iptables is broken: incomplete LSB comment.
insserv: missing `Required-Start:' entry: please add even if empty.
insserv: warning: current start runlevel(s) (empty) of script `acpid' overwrites defaults (2 3 4 5).
insserv: warning: current stop runlevel(s) (2 3 4 5) of script `acpid' overwrites defaults (empty).
insserv: missing `Required-Start:' entry: please add even if empty.

b) disabling ACPI on RHEL, Fedora and other Redhat-s (also known as RedHacks 🙂 )

I'm not sure if this is safe,as many newer rpm based server system services,  might not work properly with haldaemon disabled.

Anyways you can give it a try if when it is stopped there are issues just bring it up again.

[root@rhel ~]# /etc/init.d/haldaemon stop

If all is fine with the haldaemon switched off (hope so), you can completely disable it to load on start up with:

[root@centos ~]# /sbin/chkconfig --level 2 3 4 5 haldaemon off

Disabling ACPI could increase a bit your server bills, but same time decrease losses from downtimes, so I guess it worths its costs 🙂

 

swap_pager_getswapspace: failed, MySQL troubles on FreeBSD 7.2 cause and solution

Tuesday, May 3rd, 2011

Every now and then my FreeBSD router dmesg ( /var/log/dmesg.today ) logs, gets filled with error messages like:

pid 86369 (httpd), uid 80, was killed: out of swap space
swap_pager_getswapspace(14): failed
swap_pager_getswapspace(16): failed
swap_pager_getswapspace(11): failed
swap_pager_getswapspace(12): failed
swap_pager_getswapspace(16): failed
swap_pager_getswapspace(16): failed
swap_pager_getswapspace(16): failed
swap_pager_getswapspace(16): failed
swap_pager_getswapspace(14): failed
swap_pager_getswapspace(16): failed
swap_pager_getswapspace(8): failed

Using swapinfo during the swap_pager_getswapspace(16): failed messages were logged in, I figured out that definitely the swap memory over-use is the bottleneck for the troubles, to find this I used the command:

freebsd# swapinfo
Device 1K-blocks Used Avail Capacity Type
/dev/ad0s1b 49712 45920 3792 92% Interleaved

After some investigation, I’ve figured out that the MySQL server is causing the kernel exceeded swap troubles.

My current MySQL server version is installed from the ports tree, whether I’m using the bsd port /usr/ports/databases/mysql51-server/ and it appears to work just fine.

However I have noticed that the mysql-server is missing a my.cnf file!, which means the mysql server is running under a mode with some kind of default configurations.

Strangely in the system process list it appeared it is using a default my.cnf file located in /var/db/mysql/my.cnf

Below you see the paste from the ps command:

ps axuww freebsd# ps axuww | grep -i my.cnf | grep -v grep
mysql 7557 0.0 0.1 3464 1268 p1 I 12:03PM 0:00.01 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --pid-file=/var/db/mysql/pcfreak.pidmysql 7589 0.0 5.1 93284 52852 p1 I 12:03PM 0:59.01 /usr/local/libexec/mysqld --defaults-extra-file=/var/db/mysql/my.cnf --basedir=/usr/local --datadir=/var/db/mysql --user=mysql --pid-file=/var/db/mysql/pcfreak.pid --port=3306 --socket=/tmp/mysql.sock

Nevertheless it appeared the sql server is running the file /var/db/mysql/my.cnf conf was not existing! This was really weird for me as I’m used to have the default my.cnf from my previous experience with Linux servers!

Thus the next logical thing I did was to create my.cnf conf file in order to be able to have a proper limiting configuration for the sql server.

The FreeBSD my.cnf skele files are found in /usr/local/share/mysql/, here are the 4 files one can use as a starting basis for further configuration of the mysql-server.

freebsd# ls -al /usr/local/share/mysql/my-*.cnf
-r--r--r-- 1 root wheel 4948 Aug 12 2009 /usr/local/share/mysql/my-huge.cnf
-r--r--r-- 1 root wheel 20949 Aug 12 2009 /usr/local/share/mysql/my-innodb-heavy-4G.cnf
-r--r--r-- 1 root wheel 4924 Aug 12 2009 /usr/local/share/mysql/my-large.cnf
-r--r--r-- 1 root wheel 4931 Aug 12 2009 /usr/local/share/mysql/my-medium.cnf
-r--r--r-- 1 root wheel 2502 Aug 12 2009 /usr/local/share/mysql/my-small.cnf

I have chosen to use the my-medium.cnf as a skele to tune up, as my server is not high iron one e.g. the host I run the mysql is a (simple dual core 1.2Ghz system).

Further on I copied the /usr/local/share/mysql/my-medium.cnf to /var/db/mysql/my.cnf e.g.:

freebsd# cp -rpf /usr/local/share/mysql/my-medium.cnf /var/db/mysql/my.cnf

As a next step to properly tune up the default values of the newly copied my.cnf to my specific server I used the Tuning-Primer MySQL tuning script

Using tuning-primer.sh is really easy as all I did is download, launch it and follow the script suggestions to correct some of the values already in my.cnf

I have finally ended up with the following my.cnf after using tuning-primer.sh to optimize mysql server to work with my bsd host

Now I really hope the shitty swap_pager_getswapspace: failed errors would not haunt me once again by crashing my server and causing mem overheads.

Still I wonder why the port developer Alex Dupre – ale@FreeBSD.org choose not to provide the default mysql51-server conf with some kind of my.cnf file? I hope he had a good reason.

How to make a mysql root user to login interactive with mysql cli passwordless

Wednesday, June 29th, 2011

MySQL Logo Passwordless root login .my.cnf

I’m using access to the mysql servers via localhost with mysql cli on daily basis.
With time I’ve figured out that it’s pretty unahandy to always login with my root mysql password, I mean each time to enter it, e.g.:

root@mysql-server:~# mysql -u root
Enter password:
...

Thus to make my life a way easier I decided to store my mysql root password in order to allow my root admin user to be able to login to my mysql server without asking for password. This saves time and nerves, as I’m not supposed to look up for the password file I store my server mysql root pass.

To allow my mysql cli interface, to login passwordless to the SQL server I had to create the file /root/.my.cnf readable only for my root user and store my MySQL username and password there.

Here is a sample /root/.my.cnf file:

root@mysql-server:~# cat /root/.my.cnf
[client]
user="root"
pass="mysecretMySQLPasswordgoeshere"

Now next time I use the mysql console interface to access my mysql server I don’t have to supply the password, here is how easier is the mysql login afterwards:

root@mysql-server:~# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 3520
Server version: 5.0.77 Source distribution

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

mysql>

The only downside of using .my.cnf to store permanently the mysql server root and password is from security standpoint.
If for instance somebody roots my servers, where I have stored my root user/pwds in .my.cnf , he will be able immediately to get access to the MySQL server.

Another possible security flaw with using the mysql passwordless login “trick” is if somebody forgets to set proper file permissions to, .my.cnf

Once again the file should possess the permissons of:

root@mysql-server:~# ls -al /root/.my.cnf
-rw------- 1 root root 90 Apr 2 00:05 /root/.my.cnf

Any other permissons might allow non-privileged users to read the file and gain unathorized admin access to the SQL server.
 

How to check and repair broken MySQL ISAM tables

Monday, July 11th, 2011

MySQL repair artistic picture

If you are stuffed with errors in /var/log/mysqld.log similar to:

110711 11:00:48 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './anyboots_moncler_spaccio/zen_seo_cache.frm'
110711 11:00:48 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './anyboots_moncler_spaccio/zen_sessions.frm'

This is a sure sign something terrible has happened with your mysql database tables that lead to corruption.
Having corrupt table in mysql installation can severely lead to data loss as well as significantly reduce the speed and performance of a MySQL server in this awful times mysqlcheck is the best friend of the administrator, here is how you can check and repair broken tables in MySQL server:

mysql-server:~# mysqlcheck --all-databases -u root -p
chillor_hjbgl.vn_users OK
chillor_lul.mybb_adminlog OK
chillor_lul.mybb_adminoptions OK
chillor_lul.mybb_adminsessions OK
chillor_lul.mybb_adminviews OK
chillor_lul.mybb_announcements OK
...

You will notice the corrupt sql tables will be reported as corrupt by the tool and mysqlcheck will try it’s best to recover the corrupt tables.

In most cases this should be enough to recover corrupt tables.

How to change MySQL server root password

Friday, July 29th, 2011

MySQL pass dialog Debian

I had to change my mysql root password for one of the servers since during the install I mispasted the password in the MySQL password prompt I needed the pwd to be changed.

Here is how I changed it to my desired one:

linux:~# /usr/bin/mysqladmin -u root -p'OLD_PASSWORD_STRING' password NEW_PASSWORD_STRING
linux:~#

The password gets changed immediately 😉

If a new password has to be set to a passwordless mysql server, the command to be issued is:

linux:~# /usr/bin/mysqladmin -u root password PASSWORD_STRING

Changing the MySQL password is also possible with mysql cli, after connecting to the sql server, though this method is a bit more time consuming. Here is how to do it from mysql console:

linux:~# mysql -u root -p
Server version: 5.1.49-3 (Debian)

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> use mysql;
mysql> update user set password=PASSWORD(“NEW_PASSWORD”) where User=’root’;mysql> flush privileges;

Of course it’s possible to do change the root pass via phpmyadmin
Cheers 😉