Posts Tagged ‘lib’

Fix MySQL ibdata file size – ibdata1 file growing too large, preventing ibdata1 from eating all your server disk space

Thursday, April 2nd, 2015


If you're a webhosting company hosting dozens of various websites that use MySQL with InnoDB  engine as a backend you've probably already experienced the annoying problem of MySQL's ibdata1 growing too large / eating all server's disk space and triggering disk space low alerts. The ibdata1 file, taking up hundreds of gigabytes is likely to be encountered on virtually all Linux distributions which run default MySQL server <= MySQL 5.6 (with default distro shipped my.cnf). The excremental ibdata1 raise appears usually due to a application software bug on how it queries the database. In theory there are no limitation for ibdata1 except maximum file size limitation set for the filesystem (and there is no limitation option set in my.cnf) meaning it is quite possible that under certain conditions ibdata1 grow over time can happily fill up your server LVM (Storage) drive partitions.

Unfortunately there is no way to shrink the ibdata1 file and only known work around (I found) is to set innodb_file_per_table option in my.cnf to force the MySQL server create separate *.ibd files under datadir (my.cnf variable) for each freshly created InnoDB table.

1. Checking size of ibdata1 file

On Debian / Ubuntu and other deb based Linux servers datadir is /var/lib/mysql/ibdata1

server:~# du -hsc /var/lib/mysql/ibdata1
45G     /var/lib/mysql/ibdata1
45G     total

2. Checking info about Databases and Innodb storage Engine

server:~# mysql -u root -p

| Database           |
| information_schema |
| bible              |
| blog               |
| blog-sezoni        |
| blogmonastery      |
| daniel             |
| ezmlm              |
| flash-games        |

Next step is to get some understanding about how many existing InnoDB tables are present within Database server:


mysql> SELECT COUNT(1) EngineCount,engine FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','performance_schema','mysql') GROUP BY engine;
| EngineCount | engine |
|         131 | InnoDB |
|           5 | MEMORY |
|         584 | MyISAM |
3 rows in set (0.02 sec)

To get some more statistics related to InnoDb variables set on the SQL server:

mysqladmin -u root -p'Your-Server-Password' var | grep innodb

Here is also how to find which tables use InnoDb Engine

mysql> SELECT table_schema, table_name
    -> WHERE engine = 'innodb';

| table_schema | table_name               |
| blog         | wp_blc_filters           |
| blog         | wp_blc_instances         |
| blog         | wp_blc_links             |
| blog         | wp_blc_synch             |
| blog         | wp_likes                 |
| blog         | wp_wpx_logs              |
| blog-sezoni  | wp_likes                 |
| icanga_web   | cronk                    |
| icanga_web   | cronk_category           |
| icanga_web   | cronk_category_cronk     |
| icanga_web   | cronk_principal_category |
| icanga_web   | cronk_principal_cronk    |

3. Check and Stop any Web / Mail / DNS service using MySQL

server:~# ps -efl |grep -E 'apache|nginx|dovecot|bind|radius|postfix'

Below cmd should return empty output, (e.g. Apache / Nginx / Postfix / Radius / Dovecot / DNS etc. services are properly stopped on server).

4. Create Backup dump all MySQL tables with mysqldump

Next step is to create full backup dump of all current MySQL databases (with mysqladmin):

server:~# mysqldump –opt –allow-keywords –add-drop-table –all-databases –events -u root -p > dump.sql
server:~# du -hsc /root/dump.sql
940M    dump.sql
940M    total


If you have free space on an external backup server or remotely mounted attached (NFS or SAN Storage) it is a good idea to make a full binary copy of MySQL data (just in case something wents wrong with above binary dump), copy respective directory depending on the Linux distro and install location of SQL binary files set (in my.cnf).
To check where are MySQL binary stored database data (check in my.cnf):

server:~# grep -i datadir /etc/mysql/my.cnf
datadir         = /var/lib/mysql

If server is CentOS / RHEL Fedora RPM based substitute in above grep cmd line /etc/mysql/my.cnf with /etc/my.cnf

if you're on Debian / Ubuntu:

server:~# /etc/init.d/mysql stop
server:~# cp -rpfv /var/lib/mysql /root/mysql-data-backup

Once above copy completes, DROP all all databases except, mysql, information_schema (which store MySQL existing user / passwords and Access Grants and Host Permissions)

5. Drop All databases except mysql and information_schema

server:~# mysql -u root -p



DROP DATABASE wordpress;
DROP DATABASE micropcfreak;
DROP DATABASE statusnet;

          etc. etc.

ACHTUNG !!! DON'T execute!DROP database mysql; DROP database information_schema; !!! – cause this might damage your User permissions to databases

6. Stop MySQL server and add innodb_file_per_table and few more settings to prevent ibdata1 to grow infinitely in future

server:~# /etc/init.d/mysql stop

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

Delete files taking up too much space – ibdata1 ib_logfile0 and ib_logfile1

server:~# cd /var/lib/mysql/
server:~#  rm -f ibdata1 ib_logfile0 ib_logfile1
server:~# /etc/init.d/mysql start
server:~# /etc/init.d/mysql stop
server:~# /etc/init.d/mysql start
server:~# ps ax |grep -i mysql


You should get no running MySQL instance (processes), so above ps command should return blank.

7. Re-Import previously dumped SQL databases with mysql cli client

server:~# cd /root/
server:~# mysql -u root -p < dump.sql

Hopefully import should went fine, and if no errors experienced new data should be in.

Altearnatively if your database is too big and you want to import it in less time to mitigate SQL downtime, instead import the database with:

server:~# mysql -u root -p
mysql> SOURCE /root/dump.sql;


If something goes wrong with the import for some reason, you can always copy over sql binary files from /root/mysql-data-backup/ to /var/lib/mysql/

8. Connect to mysql and check whether databases are listable and re-check ibdata file size

Once imported login with mysql cli and check whther databases are there with:

server:~# mysql -u root -p

Next lets see what is currently the size of ibdata1, ib_logfile0 and ib_logfile1

server:~# du -hsc /var/lib/mysql/{ibdata1,ib_logfile0,ib_logfile1}
19M     /var/lib/mysql/ibdata1
1,1G    /var/lib/mysql/ib_logfile0
1,1G    /var/lib/mysql/ib_logfile1
2,1G    total

Now ibdata1 will grow, but only contain table metadata. Each InnoDB table will exist outside of ibdata1.
To better understand what I mean, lets say you have InnoDB table named blogdb.mytable.
If you go into /var/lib/mysql/blogdb, you will see two files
representing the table:

  •     mytable.frm (Storage Engine Header)
  •     mytable.ibd (Home of Table Data and Table Indexes for blogdb.mytable)

Now construction will be like that for each of MySQL stored databases instead of everything to go to ibdata1.
MySQL 5.6+ admins could relax as innodb_file_per_table is enabled by default in newer SQL releases.

Now to make sure your websites are working take few of the hosted websites URLs that use any of the imported databases and just browse.
In my case ibdata1 was 45GB after clearing it up I managed to save 43 GB of disk space!!!

Enjoy the disk saving! 🙂

Share this on

How to deb upgrade PHP 5.3.3-7 / MySQL Server 5.1 to PHP 5.4.37 MySQL 5.5 Server on Debian 6.0 / 7.0 Squeeze / Wheezy GNU / Linux

Thursday, February 12th, 2015


I've been still running Debian Squeeze 6.0 GNU / Linux on few of the Linux / Apache / MySQL servers I'm administrating and those servers are running few Wordperss / Joomla websites which lately face severe MySQL performance issues. I tried to optimize using various mysql performance optimization scripts such as, and Percona Toolkit – a collection of advanced command-line tools for system administrators and tech / support staff to perform a variety of MySQL and system tasks that are too difficult or complex to perform manually. Though with above tools and some my.cnf tunizations I managed to achieve positive performance improvement results with above optimizations, still I didn't like how MyQSL served queries and since the SQL server is already about 5 years old (running version 5.1) and the PHP on sever is still at 5.3 branch, I was advised by my dear colleague Anatoliy to try version update as a mean to improve SQLserver performance. I took seriously the suggestion to try upgrade as a mean to resolve performance issues in this article I will explain in short what I had to do to make MySQL upgrade a success

Of course to try keep deb installed software versions as fresh as possible possible deb packagse, I'm already using Debian Back Ports (for those who hear it a first time Debian Backports is a special repository for Stable versioned Debian Desktop and Servers  – supporting stable releases of Debian Linux) which allows you to keep install packages versions less outdated (than default installable software which usually are way behind latest stable package versions with 2-5 years).

If you happen to administer Stable Debian servers and you never used BackPorts I warmly recommend it as it often includes security patches of packages part of Debian stable releases that reached End Of Support (EOS) and already too old even for security updates to be issued by respective Debian Long Term Suport (LTS) repositories.

If you're like me and still in situation to manage remotely Debian 6.0 Squeeze and its the first time you hear about BackPorts and Debian LTS to start using those two add to your /etc/apt/sources.list below 3 lines

Open with vim editor and press shift+G to go to last line of file and then press I to enter INSERT mode, once you're done to save, press (ESC) then press : and type x! in short key combination for exit and save setting in vim is 

Esc + :x! 


debian-server:~# vim /etc/apt/sources.list
deb squeeze-lts main contrib non-free
deb-src squeeze-lts main contrib non-free
deb squeeze-backports main

If you haven't been added a security updates line in /etc/apt/sources.list make sure you add also:


deb squeeze/updates main contrib non-free
deb-src squeeze/updates main contrib non-free

Then to apply latest security updates and packages from LTS / Backports repository run the usual:


debian-server:~# apt-get update && apt-get –yes upgrade

If you need to search a package or install something from just added backports repository use:


debian-server:~# apt-cache -t squeeze-backports search "mysql-server"
auth2db – Powerful and eye-candy IDS logger, log viewer and alert generator
torrentflux – web based, feature-rich BitTorrent download manager
cacti – Frontend to rrdtool for monitoring systems and services
mysql-server-5.1 – MySQL database server binaries and system database setup
mysql-server-core-5.1 – MySQL database server binaries
mysql-server – MySQL database server (metapackage depending on the latest version)


To install specific packages only with all their dependencies from Backports while keeping rest of packages from Debian Stable:


debian-server:~# apt-get install -t squeeze-backports "package_name"

In same way you can also search or install specific packages from LTS repo:


debian-server:~# apt-get search -t squeeze-lts "package_name"

debian-server:~# apt-get install -t squeeze-lts "package_name"

Latest mysql available from Debian BackPorts and LTS is still quite old 5.1.73-1+deb6u1 therefore I made an extensive research online on how can I easily update MySQL 5.1 to MySQL 5.5 / 5.6 on Debian Stable Linux.

Luckily there were already DotDeb deb repositories for Debian LAMP (Linux / Apache  / MySQL / PHP / Nginx ) running servers prepared in order to keep the essential Webserver services up2date even long after distro official support is over. I learned about existence of this repo thanks to a Ryan Tate's post who updates his LAMP stack on TurnKey Linux which by the way is based on slightly modified official stable Debian Linux releases packages

To start using DotDeb repos add in /etc/apt/sources.list (depending whereh you're on Squeeze or Wheeze Debian):


deb squeeze all
deb-src squeeze all

or for Debian Wheezy add repos:


deb wheezy all
deb-src wheezy all


I was updating my DebianLatest MySQL / PHP / Apache release to Latest ones on (6.0.4) Squeeze so added above squeeze repos:

Before refreshing list of package repositories, to authenticate repos issue:


debian-server:~# wget -q
debian-server:~# apt-key add dotdeb.gpg

Once again to update my packages from newly added DodDeb repository


debian-server:~# apt-get update

Before running the SQL upgrade to insure myself, I dumped all databases with:


debian-server:~# mysqldump -u root -p -A > /root/dump.sql

Finally I was brave enough to run apt-get dist-upgrade to update with latest LAMP packages


debian-server:~# apt-get dist-upgrade
Reading package lists… Done
Building dependency tree
Reading state information… Done
Calculating upgrade… Done
The following packages will be REMOVED:
  mysql-client-5.1 mysql-server mysql-server-5.1
The following NEW packages will be installed:
  libaio1 libmysqlclient18 mysql-client-5.5 mysql-client-core-5.5 python-chardet python-debian
The following packages will be upgraded:
  curl krb5-multidev libapache2-mod-php5 libc-bin libc-dev-bin libc6 libc6-dev libc6-i386 libcurl3 libcurl3-gnutls libcurl4-openssl-dev libevent-1.4-2
  libgssapi-krb5-2 libgssrpc4 libjasper1 libk5crypto3 libkadm5clnt-mit7 libkadm5srv-mit7 libkdb5-4 libkrb5-3 libkrb5-dev libkrb53 libkrb5support0 libmysqlclient-dev
  libxml2 libxml2-dev locales mysql-client mysql-common ntp ntpdate php-pear php5 php5-cgi php5-cli php5-common php5-curl php5-dev php5-gd php5-imagick php5-mcrypt
  php5-mysql php5-odbc php5-recode php5-sybase php5-xmlrpc php5-xsl python-reportbug reportbug unzip

50 upgraded, 6 newly installed, 3 to remove and 0 not upgraded.
Need to get 51.7 MB of archives.
After this operation, 1,926 kB of additional disk space will be used.
Do you want to continue [Y/n]? Y

As you see from above output above command updates Apache webservers / PHP and PHP related modules, however it doesn't update MySQL installed version, to update also MySQL server 5.1 to MySQL server 5.5


debian-server:~#  apt-get install –yes mysql-server mysql-server-5.5

You will be prompted with the usual Debian ncurses text blue interface to set a root password to mysql server, just set it the same as it used to be on old upgraded MySQL 5.1 server.

Well now see whether mysql has properly restarted with ps auxwwf


debian-server:~#  ps axuwwf|grep -i sql
root     22971  0.0  0.0 112360   884 pts/11   S+   15:50   0:00  |                   \_ grep -i sql
root     19436  0.0  0.0 115464  1556 pts/1    S    12:53   0:00 /bin/sh /usr/bin/mysqld_safe
mysql    19837  4.0  2.3 728192 194552 pts/1   Sl   12:53   7:12  \_ /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –plugin-dir=/usr/lib/mysql/plugin –user=mysql –pid-file=/var/run/mysqld/ –socket=/var/run/mysqld/mysqld.sock –port=3306
root     19838  0.0  0.0 110112   700 pts/1    S    12:53   0:00  \_ logger -t mysqld -p daemon.error

In my case it was running, however if it fails to run try to debug what is going wrong on initialization by manually executing init script /etc/init.d/mysql stop; /etc/init.d/mysql start and look for errors. You can also manually try to run mysqld_safe from console if it is not running run:


debian-server:~# /usr/bin/mysqld_safe &

This should give you a good hint on why it is failing to run

One more thing left is to check whether php modules load correctly to do so issue:


debian-server:~# php -v
Failed loading /usr/lib/php5/20090626/  /usr/lib/php5/20090626/ cannot open shared object file: No such file or directory

Failed loading /usr/lib/php5/20090626/  /usr/lib/php5/20090626/ cannot open shared object file: No such file or directory

You will likely get an exception (error) like above.
To solve the error, reinstall xcache and xcache-debug debs


debian-server:~# apt-get purge php5-xcache php5-xdebug

Now PHP + MySQL + Apache environment should be running much smootly.


Upgrading the MySQL server / PHP library to MySQL server 5.6 / PHP 5.5 on Wheeze Linux is done in very much analogous ways all you have to do is change the repositories with above wheeze 7.0 ones and to follow the process as described in this article. I haven't tested update on Wheezy yet, so if you happen to try my article with wheezy reports and got a positive upgrade result please drop a comment.

Share this on

How to determine WordPress blogs with most spam on multiple blog hosting server

Thursday, November 27th, 2014

If you're a hosting company that is hosts Joomla / WordPress / ModX websites (each) on separate servers and thus you end up with servers hosting multiple WordPress customer Blogs only, lets say (100+ WP blogs per host) soon your MySQL blogs databases will be full (overfilled) with spam comments. Blogs with multitude of spam comments reduces the WordPress site attractiveness, takes useless disk space, makes wp databases hard to backup and slowing drastically the SQL server.

As our duty as system administrators is to keep the servers optimized (improve performance) and prevent spam-bots to hammer your Linux servers, its is always a good idea to keep an eye on which hosted blogs attract more spammers and cause server overheads and bad hardware optimization.

WordPress blogs keeps logged comments under database_name.wp_comments  (table) thus the quickest way to find out blogs with largest comments tables is to use Linux's find command and print out only comments tables larger than set size.

Here is how:

find /var/lib/mysql/ -type f -size +1024k -name "*_comments.MYD" -exec ls -lh {} ; | awk '{ print $9 ": " $5 }'

/var/lib/mysql/funny-blog/wp_comments.MYD: 15,7M
/var/lib/mysql/wordblogger/wp_comments.MYD: 5,3M
/var/lib/mysql/loveblog/wp_comments.MYD: 50,5M

A comments database of 1MB means about at least 500+ comments, thus the blog loveblog's wp_comments.MYD = 50,5 Mbs contains probably about 10000! comments and should be definitely checked in a browser, if its overfilled with spam because of bad anti-spam policy or missing currently best wordpress spam catcher plugin Akismet. In cases of lack of client to protect his spam you can write quickly a script to auto mail him and ask him kindly to check / fix his blog spam.
In some cases it is useful to write a few liners bash script to automatically disable users with extraordinary blog spam comments databases (quickest way to do it is to move users blog data under quarantine directory and adding a Blog Suspended static html webpage with text like "Please contact support for more info".

1024k find arguments is 1MB, on a big hosted blogs this might be low and you might want to use (100Mb) = 102400kbytes.
You should note that *_comments.MYD in above find cmd is because though standardly wordpress sets wp_ as a prefix to its created skele table structures it is not always the case. 

In above command example find looks for spam comments in /var/lib/mysql (because this is a Debian Linux server), however on other MySQL custom installs, it might be in another dir i.e. /usr/local/mysql/data etc.

It is useful to set the wp_comments statistics output to execute at least once a day as a cronjob:

crontab -u root -e 00 24 * * * /usr/sbin/

vim /usr/

Set a script like:

find /var/lib/mysql/ -type f -size +1024k -name "*_comments.MYD" -exec ls -lh {} ; | awk '{ print $9 ": " $5 }' | tee -a /var/log/blogs_with_most_spam_comments.log

Though above commands is to run on GNU / Linux, for Windows servers based hosting you can  install GNUWin tools and adapt above cmd using windows standard commands or PowerShell to do the same.
Finally you can might want to use some other SQL script to clear blogs with enormously large tables from spam or clear all unapproved spam comments

Share this on

Run native Internet Explorer 6 on latest Debian / Ubuntu Linux with IEs4Linux

Thursday, July 24th, 2014

If you're a GNU / Linux Desktop user like me and you have to administrate hybrid server environments running mixture of MS Windows with Microsoft IIS webserver running active server pages (.ASP) developed application or UNIX / GNU Linux servers web applications using Mono as a server-side language, often you need to have browser which properly supports  Internet Explorer Trident web (layout) renderer (also famous as MSHTML).

Having Internet Explorer on your Linux is very useful for web developers who want to test how their website works under IE.

Of course you can always install Windows in Virtualbox VM and do your testing in the Virtual Machine but this takes time to install and also puts a useless load to a PC ….

IES4 Linux is a Linux free (open source) shell script that lets you run Internet Explorer on your Linux desktop.

ies4linux scripts collection uses emulation with WINE (Wine is Not Emulator) emulator to run the native  Windows Internet Explorer thus before use it you have to install Wine.

There are plenty of tutorials online about ies4Linux, problem is as it is not updated and developed most tutorials doesn't work on Debian Wheezy / Ubuntu and rest of deb based linux distros.
This is why I decided to write just another ies4linux tutorial that actually works!

On Debian / Ubuntu / Mint Linux install via apt-get:

apt-get –yes install wine

Then with a non-root user download ies4linux-latest.tar.gz. Just in case ies4linux-latest.tar.gz disappears in future I've created also a ies4linux-latest.tar.gz mirror for download here

and unarchive tar archive:

tar -zxvf ies4linux-latest.tar.gz
cd ies4linux-*

You will get:

IEs4Linux 2 is developed to be used with recent Wine versions (0.9.x). It seems that you are using an old version. It's recommended that you update your wine to the latest version (Go to:

You need to install cabextract first!
Download it here:

To fulfill this requirement you will need to also cabetract package which is luckily part of Debian:

apt-get install –yes cabextract

On wine version 1.0 and onwards winprefixcreate has been changed to winecfg binary.
To prevent missing wineprefixcreate, errors during ies4linux installer run  its necessery to symlink as a workaround:

ln -sv /usr/bin/winecfg /usr/bin/wineprefixcreate

To continue with Internet Explorer ies4Linux installater run again:




You will get the installer GUI window with selection option which Internet Explorer version you want. Choose between IE 5.0, IE 5.5 and IE 6. It is also possible to install IE 7 which is still considered beta version and is less tested and unstable, will probably lead to crashes. If you want to install also IE 7 check it as an option from Advanced menu.


If you get permission errors after running ies4Linux gui installer to solve that chown recursively directory to the user with which you will be running it:

chown -R hipo:hipo ies4linux-


Internet Explorer for Linux downloader, will connect website and download DCOM, MCF and various IE required .CAB files.

If you get some ies4linux GUI installer unexpected crashes you can try to download all required IE binaries, surrounding files and flash player using no-gui installer with cmd:

./ies4linux –no-gui –install-corefonts

IEs4Linux 2 is developed to be used with recent Wine versions (0.9.x). It seems that you are using an old version. It's recommended that you update your wine to the latest version (Go to:

IEs4Linux will:
  – Install Internet Explorers: 6.0
  – Using IE locale: EN-US
  – Install Adobe Flash 9.0
  – Install MS Core Fonts
  – Install everything at: /home/hipo/.ies4linux
[ OK ]

Downloading everything we need
  Downloading from

  Downloading from

  Downloading from
   0%   webdin32.exe[ OK ]bdin32.exe

Installing IE 6
  Creating Wine Prefix
Your wine does not have wineprefixcreate installed. Maybe you are running an old Wine version. Try to update it to the latest version.

To fix the error:

Your wine does not have wineprefixcreate installed. Maybe you are running an old Wine version. Try to update it to the latest version.

vim lib/

Go to line 36 (Type :36 in vim)


wine –version 2>&1 | grep -q "0.9." || warning $MSG_WARNING_OLDWINE

Has to be changed to:

wine –version 2>&1 | egrep -q "0.9.|-1." || warning $MSG_WARNING_OLDWINE

Also you need to substitute wineprefixcreate to wineboot (if you haven't already symlinked wineprefixcreate to winecfg – as pointed earlier in article.

To do so make following substitution in lib/ and in lib/

cp -rpf lib/ lib/; cat lib/ |sed -e 's#wineprefixcreate#wineboot#g' > lib/; mv lib/ lib/

cp -rpf lib/ lib/; cat lib/ |sed -e 's#wineprefixcreate#wineboot#g' > lib/; mv lib/ lib/

Also it is necessery to change default corefonts download url which points to sourceforge but is failing. I've made mirror of corefonts files here

cp -rpf lib/ lib/; cat lib/ |sed -e 's#' > lib/; mv lib/ lib/

Re-run the ies4linux console installer:

 ./ies4linux –no-gui –install-corefonts

Es4Linux installations finished!

On installation success you should get output like this
Hopefully you will see no errors like in my case, if you get the corefonts download error again re-run the installer and it should succesully download the files.

To then run ies4linux:


Though Ies 4 Linux is good for basic testing it is not psosible to use the browser for normal browsing because its a bit buggy and slow.

By default Internet Explorer 6 behavior is to prompt security alert on various actions, though this might be useful for debugging it is really annoying so I personally disabled those by decreasing from:

Tools -> Internet Options -> Security -> (Security Level)
I've decreased it from Medium to Medium-Low

ies4Linux was not developed since 2008 and as of time of writting ies4linux official project website seems abandoned.


Share this on

Allowing MySQL users access from all hosts – Fixing mysql ERROR 1045 (28000): Access denied for user ‘root’@’’ (using password: YES)

Friday, June 20th, 2014


I recently migrated MySQL database server from host A to host B (, because I wanted to have the mysql database server on a separate machine (have separation of server running services and have a dedicated mysql server).

MySQL server host (running on localhost previously was set from my mysql config my.cnf to listen and serve connections on localhost with

bind-address =

). MySQL is used by a Tomcat running Java application on localhost and my task was to set the Tomcat to use the MySQL database remotely to MySQL host B (new remote hostname where MySQL is moved is and is running on IP

Migration from MySQL Db server 1 (host A) to MySQL Db server 2 (host B) is done by binary copying the mysql database directory which in this case is (as it is a Debian server installed MySQL), the standard directory where mysql stores its database data is /var/lib/mysql ( datadir = /var/lib/mysql in /etc/mysql/my.cnf)

Binary copying of data from MySQL db (host A) to MySQL Db (host B) is done with rsync

After migrating and trying to login on migrated mysql  database on with mysql cli client:

remotesysadmin:~$ mysql -u root -p

I got following error:

ERROR 1045 (28000): Access denied for user 'root'@'' (using password: YES)

To fix the issue I had to login remotely from old migration server mysql (host A) cli:

mysql:~$ mysql -u root -p -h

and  run SQL commands:

GRANT USAGE ON *.* TO 'root'@'' IDENTIFIED BY 'secret-mysql-pass';

Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Another way to solve the problem is to add the root user to be able to connect from any host (Enable MySQL root access from all host), to do so issue:


Note: In newer version of MySQL, flush privileges could be omitted.

Another approach if you want to substitute access from localhost for all users and enable all users to be able to authenticate to mysql remotely is to execute SQL Query:

UPDATE USER SET host='%' WHERE host='localhost';

Allowing all users to be able to connect from anywhere on the internet is a very bad security practice anyways, if you already have a tight firewall setup and you can only access the server via specific remote IP addresses allowing MySQL access from all hosts / ips should be ok.

Share this on

Linux: how to show all users crontab – List all cronjobs

Thursday, May 22nd, 2014

I'm doing another server services decomissioning and part of decomissioning plan is: Removing application and all related scripts from related machines (FTP, RSYNC, …). In project documentation I found a list with Cron enabled shell scripts:

#Cron tab excerpt:
1,11,21,31,41,51 * * * */webservices/tools/scripts/

that has to be deleted, however there was nowhere mentioned under what kind of credentials (with what kind of user) are the cron scripts running? Hence I had to look up all users that has cronjobs and find inside each user's cronjobs whether respective script is set to run. Herein I will explain shortly how I did that.

Cronjobs by default has few locations from where cronjobs are setupped depending on their run time schedule. First place I checked for the scripts is

/etc/crontabs # cat /etc/crontabs SHELL=/bin/sh
# check scripts in cron.hourly, cron.daily, cron.weekly, and cron.monthly
-*/15 * * * * root test -x /usr/lib/cron/run-crons && /usr/lib/cron/run-crons >/dev/null 2>&1
59 * * * * root rm -f /var/spool/cron/lastrun/cron.hourly
14 4 * * * root rm -f /var/spool/cron/lastrun/cron.daily
29 4 * * 6 root rm -f /var/spool/cron/lastrun/cron.weekly
44 4 1 * * root rm -f /var/spool/cron/lastrun/cron.monthly

I was not really user via what user is shell script run, therefore I looked first if someone doesn't set the script to run via crontab's standard locations for Daily, Hourly,Weekly and Monthly cronjobs:

a) Daily set cron jobs are in:


b) Hourly set cron jobs:


c) Weekly cron jobs are in:


d) Monthly cron jobs:


There is also a location read by crontab for all Software (package distribution) specific cronjobs – all run under root user privileges.:

e) Software specific script cron jobs are in:

As the system has about 327 users in /etc/passwd, checking each user's cronjob manually with:

# crontab -u UserName -l

was too much time consuming thus it is a good practice to list


directory and to see which users has cron jobs defined


# ls -al /var/spool/cron/*
-rw——- 1 root root 11 2007-07-09 17:08 /var/spool/cron/deny

total 0
drwxr-xr-x 2 root root 80 2014-05-22 11:15 .
drwx—— 4 root root 120 2008-02-25 15:45 ..
-rw-r–r– 1 root root 0 2014-05-22 04:15 cron.daily

total 8
drwx—— 2 root root 72 2014-04-03 03:43 .
drwx—— 4 root root 120 2008-02-25 15:45 ..
-rw——- 1 root root 4901 2014-04-03 03:43 root

/var/spool/cron – is crond (/usr/bin/cron/)'s spool directory.

# ls -al /var/spool/cron/tabs/ total 8
drwx------ 2 root root 72 2014-04-03 03:43 .
drwx------ 4 root root 120 2008-02-25 15:45 ..
-rw------- 1 root root 4901 2014-04-03 03:43 root

Above output shows only root superuser has defined crons.

Alternative way to check all user crontabs is via quick Linux one liner shell script show all user cron jobs

for i in $(cat /etc/passwd | sed -e "s#:# #g" | awk '{ print $1 }'); do
echo "user $i --- crontab ---";
crontab -u $i -l 2>&1 >/dev/null;
echo '----------';

Note that above short script has to run with root user. Enjoy 🙂

Share this on

Mysql: How to disable single database without dropping or renaming it

Wednesday, January 22nd, 2014

mysql rename forbid disable database howto logo, how to disable single database without dropping it
A colleague of mine working on MySQL database asked me How it is possible to disable a MySQL database. He is in situation where the client has 2 databases and application and is not sure which of the two databases the application uses. Therefore the client asked one of the database is disabled and wait for few hours and see if something will break / stop working and in that way determine which of the two database is used by application.

My first guess was to backup both databases and drop one of them, then if it is the wrong one to restore from the SQL dump backup, however this wasn't acceptable solution. So second I though of RENAME of database to another one and then reverting the name, however as it is written in MySQL documentation RENAME database function was removed from MySQL (found to be dangerous) since version 5.1.23 onwards. Anyhow there is a quick hack to rename mysql database using a for loop shell script one below:

mysql -e "CREATE DATABASE \`new_database\`;"
for table in `mysql -B -N -e "SHOW TABLES;" old_database`
  mysql -e "RENAME TABLE \`old_database\`.\`$table\` to \`new_database\`.\`$table\`"
  mysql -e "DROP DATABASE \`old_database\`;"

Other possible solution was to change permissions of Application used username, however this was also complicated from mysql cli, hence I thought of installing and using PHPMyAdmin to make modify of db user permissions easier but on this server there wasn't Apache installed and MySQL is behind a firewall and only accessible via java tomcat host.

Finally after some pondering what can be done I came with solution to request to disable mysql database using chmod in /var/lib/mysql/data/, i.e.:

sql-server:~# chmod 0 /var/lib/mysql/databasename

Where databasename is the same as the database is named listable via mysql cli.

After doing it that way with no need to restart MySQL server database stopped to appear in show databases; and client confirmed that disabled database is no longer needed so we proceeded dropping it.

Hope this little article will help someone out there. Cheers :

Share this on

Windows Explorer (Open directory in command prompt preserving dir PATH) – Add Dos Prompt Here feature via tiny registry tweak

Friday, January 10th, 2014

Windows explorer dos prompt here open directory in windows command line

If you have to use Windows on system administration level, you had to use command prompt daily, thus its useful to be able to be able to open Command Line starting from desired directory with no need to copy directory Path by hand and CD to it manually.

By default Command Prompt, cmd.exe always opens itself setting a path to user home directory, reading what is defined by win system variable %USERPROFILE% or %HOMEPATH% – MS Windows equivalent of UNIX's $HOME shell variable.

To add open in  DOS Prompt Here Command Prompt option to Windows Explorer menus its necessary to apply few rules to Windows registry DB
Use above Download link and launch it and from there on clicking with right Mouse button to any directory will enable you to open directory in CMD.EXE.

Here is content of little registry tweak adding the new menu Dos Prompt Here button
Windows Registry Editor Version 5.00

@="Dos &Prompt Here"

@="cmd.exe /k cd %1"

@="Dos &Prompt Here"

@="cmd.exe /k cd %1"

Windows explorer open program files or any specific directory in windows command line

windows opening directory in command line program files screenshot win 7

This little registry code is originally for Windows 2000, anyways it is compatible with all NT technology based Windowses, Add DOS Prompt Here tweak works fine on Windows XP, Windows 7 and Windows 8 (Home, Pro and Business editions).

By Mentioning $HOME its interesting to say Windows equivalent of Linux's as it might be useful to know:

linux:~# echo $HOME


C:\\> echo %USERPROFILE%

To list all Windows Command Prompt environment variable equivalent to Linux's bash shell env / setenv is SET command), here is example output from my Winblows;

C:\Users\georgi> SET
CLASSPATH=.;C:\\Program Files (x86)\\Java\\jre6\\lib\\ext\\
CommonProgramFiles=C:\\Program Files\Common Files
CommonProgramFiles(x86)=C:\\Program Files (x86)\\Common Files
CommonProgramW6432=C:\\Program Files\\Common Files
Path=C:\\Program Files\\RA2HP\\;C:\\Windows\\system32;C:\\Windows;C:\\Windows\\S
Wbem;C:\\Windows\\System32\\WindowsPowerShell\v1.0\;C:\\Program Files\\WIDCOM
oth Software\\;C:\\Program Files\\WIDCOMM\\Bluetooth Software\\syswow64;C:\\Pr
les (x86)\\Hewlett-Packard\\HP ProtectTools Security Manager\\Bin\\;C:\\Progr
\\ActivIdentity\\ActivClient\\;C:\\Program Files (x86)\\ActivIdentity\\ActivCl
\\Program Files (x86)\\QuickTime\\QTSystem\\
PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 58 Stepping 9, GenuineIntel
ProgramFiles=C:\\Program Files
ProgramFiles(x86)=C:\\Program Files (x86)
ProgramW6432=C:\\Program Files
PTSMInstallPath_X86=C:\\Program Files (x86)\\Hewlett-Packard\\HP ProtectToo
ity Manager\\
QTJAVA=C:\\Program Files (x86)\\Java\\jre6\\lib\\ext\\

Hope this little trick hopes someone out there.
I will be glad to hear of other cool useful windows registry tweaks?


Share this on

‘host-name’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’

Sunday, May 20th, 2012

My home run machine MySQL server was suddenly down as I tried to check my blog and other sites today, the error I saw while trying to open, this blog as well as other hosted sites using the MySQL was:

Error establishing a database connection

The topology, where this error occured is simple, I have two hosts:

1. Apache version 2.0.64 compiled support externally PHP scripts interpretation via libphp – the host runs on (FreeBSD)

2. A Debian GNU / Linux squeeze running MySQL server version 5.1.61

The Apache host is assigned a local IP address and the SQL server is running on a host with IP

To diagnose the error I've logged in to and weirdly the mysql-server was appearing to run just fine:

debian:~# ps ax |grep -i mysql
31781 pts/0 S 0:00 /bin/sh /usr/bin/mysqld_safe
31940 pts/0 Sl 12:08 /usr/sbin/mysqld –basedir=/usr –datadir=/var/lib/mysql –user=mysql –pid-file=/var/run/mysqld/ –socket=/var/run/mysqld/mysqld.sock –port=3306
31941 pts/0 S 0:00 logger -t mysqld -p daemon.error
32292 pts/0 S+ 0:00 grep -i mysql

Moreover I could connect to the localhost SQL server with mysql -u root -p and it seemed to run fine. The error Error establishing a database connection meant that either something is messed up with the database or Mysql port 3306 is not properly accessible.

My first guess was something is wrong due to some firewall rules, so I tried to connect from to with telnet:

freebsd# telnet 3306
Connected to jericho.
Escape character is '^]'.
Host 'webserver' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
Connection closed by foreign host.

Right after the telnet was initiated as I show in the above output the connection was immediately closed with the error:

Host 'webserver' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'Connection closed by foreign host.

In the error 'webserver' is my Apache machine set hostname. The error clearly states the problems with the 'webserver' apache host unable to connect to the SQL database are due to 'many connection errors' and a fix i suggested with mysqladmin flush-hosts

To temporary solve the error and restore my normal connectivity between the Apache and the SQL servers I logged I had to issue on the SQL host:

mysqladmin -u root -p flush-hostsEnter password:

Thogh this temporar fix restored accessibility to the databases and hence the websites errors were resolved, this doesn't guarantee that in the future I wouldn't end up in the same situation and therefore I looked for a permanent fix to the issues once and for all.

The permanent fix consists in changing the default value set for max_connect_error in /etc/mysql/my.cnf, which by default is not too high. Therefore to raise up the variable value, added in my.cnf in conf section [mysqld]:

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

and afterwards restarted MYSQL:

debian:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

To make sure the assigned max_connect_errors=4294967295 is never reached due to Apache to SQL connection errors, I've also added as a cronjob.

debian:~# crontab -u root -e
00 03 * * * mysqladmin flush-hosts

In the cron I have omitted the mysqladmin -u root -p (user/pass) input options because for convenience I have already stored the mysql root password in /root/.my.cnf

Here is how /root/.my.cnf looks like:

debian:~# cat /root/.my.cnf

Now hopefully, this would permanently solve SQL's 'failure to accept connections' due to too many connection errors for future.

Share this on

How to solve “[crit] [client] (13)Permission denied: /var/lib/ejabberd/.htaccess pcfg_openfile: unable to check htaccess file, ensure it is readable, referer:” – Error Cause and Solution

Thursday, January 5th, 2012

While configuring JWchat domain, I've come across around an error:

pcfg_openfile: unable to check htaccess file, ensure it is readable

The exact error I got in /var/log/apache2/error.log looked like so:

[crit] [client] (13)Permission denied: /var/lib/ejabberd/.htaccess pcfg_openfile: unable to check htaccess file, ensure it is readable, referer:

The error message suggested /var/lib/ejabberd/.htaccess – is missing or not readable, however after checking i've seen .htaccess existed as well as was readable:

debian:~# ls -al /var/lib/ejabberd/.htaccess
-rw-r--r-- 1 www-data www-data 114 2012-01-05 07:44 /var/lib/ejabberd/.htaccess

At first glimpse it seems like the message is misleading and not true, however when I switched to www-data user (the user with which Apache runs on Debian), I've figured out the error meaning of unreadability is exactly correct:

www-data@debian:$ ls -al /var/lib/ejabberd/.htaccess
ls: cannot access /var/lib/ejabberd/.htaccess: Permission denied

This permission denied was quite strange, especially when considering the .htaccess is readable for all users:

debian:~# ls -al /var/lib/ejabberd/.htaccess
-rw-r--r-- 1 www-data www-data 114 2012-01-05 07:44 /var/lib/ejabberd/.htaccess

After a thorough look on what might go wrong, thanksfully I've figured it out. The all issues were caused by wrong permissions of /var/lib/ejabberd/.htaccess .You can see below the executable flag for all users (including apache's www-data) is missing :

debian:/var/lib# ls -ld /var/lib/ejabberd/drw-r--r-- 3 ejabberd ejabberd 4096 2012-01-05 07:45 /var/lib/ejabberd/

Solving the error, hence is as easy as adding +x flag to /var/lib/ejabberd :

debian:/var/lib# chmod +x /var/lib/ejabberd

Another way to fix the error is to chmod to 755 to the directory which holds .htaccess:

From now onwards pcfg_openfile: unable to check htaccess file, ensure it is readable err is no more 😉

Share this on