Tips and Tricks on MySQL

MySQL Database Restore



* Restore the database schema from the daily archived snapshot:

service msyqld stop
cd /var/lib
service mysqld start












grep -i "^use" db1.sql

* Run import:

mysql db1 <db1.sql


monitor and kill long running mysql select statements

Below script checks and kills any sql SELECT statements that has been running for more than 60 seconds. The script can be added to a cron task to be run periodically. Make sure to check the log file it creates to keep an eye on the long running sql statements and tune it.



echo "####" `date` "####" >>${LOG}
PIDS=$(mysql -t -e 'show full processlist' | awk -F'|' -v seconds="$SECONDS" '$7 > seconds  && toupper($9) ~ /^ SELECT/ {print $0}' | tee -a $LOG | awk -F'|' '{print $2}')
[ -n "$PIDS" ] && mysqladmin kill `echo $PIDS | tr ' ' ','`

Prior to running it, setup the root login in "~/.my.cnf" file with the login credentials:


munin-node mysql setup

  • munin-node installed via epel repository.
  • Install perl-Cache-Cache:
    # yum install munin-node perl-Cache-Cache
  • Create file "/etc/munin/plugin-conf.d/mysql" with below contents:
    env.mysqluser munin
    env.mysqlpassword {PASS}
  • Create mysql user and assign corresponding privileges:
    mysql> create user munin@localhost identified by '{PASS}';
    mysql> GRANT PROCESS, SUPER ON *.* TO 'munin'@'localhost';
    mysql> GRANT SELECT ON `mysql`.* TO 'munin'@'localhost';
    mysql> flush privileges
  • Check the suggestions and install the plugins:
    munin-node-configure  --suggest 2>/dev/null |grep mysql
    munin-node-configure  --shell | grep mysql | sh
  • Test via:
    # cd /etc/munin/plugins
    # munin-run mysql_connections

mysql repair tables


  1. Stop mysql server.
  3. Bring up the mysql server.
  4. Run a mysqlcheck of all databases via:
  6. The file output should be something like:
    REPAIR TABLE database1.table1;
    REPAIR TABLE database1.table2;
    REPAIR TABLE database2.table1;
  7. Log into mysql and source the repair script:
    # mysql> source /tmp/dbrepair.sql
    # mysqlcheck -c -e --all-databases

Tracking slow running mysql queries

First enable logging of slow running queries in "mysqld" section of "my.cnf".


Once queries get logged, you can get the top 10 queries sorted by number of occurrences in the log via:

mysqldumpslow -s c -t 10 /var/log/mysqld.slow.log

Setup sudo access to mysql user

Here is how to give dba user sudo access as mysql user.

visudo and add:

dbauser ALL=(mysql) ALL

Basically, it defines that user "dbauser" can run all commands as "mysql" user.

For the dbauser to get mysql shell, run:

$ sudo -u mysql -i

resolve mysql replication error

MySQL replication stops whenever there is an error running a query on the slave. This happens so the problem query can be identified and resolved.

Such errors can be skipped as long as you know why the query failed.

For example, when you run a query in the slave accidentally instead of running it on the master. You can skip just that one query that is hanging the slave using:


wait_timeout versus interactive_timeout






php with mysqlnd support

You can now get the latest PHP with mysqlnd (MySQL Native Driver) support via remis' yum repository as mentioned in PHP-5.3,-zts-and-mysqlnd. The blog mentions that this is enabld in php-zts. However, no php-pecl extension are available and neither are some of the extensions thread safe. So I went about rebuilding php package from source for mysqlnd support.

  1. Download the source php rpm from . Note, I have used php-5.3.2 which is the latest as of this writing.
  2. Install and apply the below diff patch to the spec file via `patch -p0 < {new_patch_file}` where "new_patch_file" has the below contents:

    --- php-5.3.2-remi.spec.orig 2010-03-11 23:07:04.000000000 -0600
    +++ php-5.3.2-remi.spec 2010-03-11 23:36:03.000000000 -0600
    @@ -24,6 +24,7 @@
    %global phpversion 5.3.2

    # Optional components; pass "--with mssql" etc to rpmbuild.
    +%define with_mysqlnd %{?_with_mysqlnd:1}%{!?_with_mysqlnd:0}
    %define with_ibase %{?_with_ibase:1}%{!?_with_ibase:0}
    %if %{?rhel}%{?fedora} > 4
    @@ -677,6 +678,11 @@
    %if %{?fedora}%{?rhel:99} >= 10
             --with-system-tzdata \
    +%if %{with_mysqlnd}
    +        --with-mysql=shared,mysqlnd \
    +        --with-mysqli=shared,mysqlnd \
    +        --with-pdo-mysql=shared,mysqlnd \
    if test $? != 0; then
       tail -500 config.log
    @@ -704,8 +710,13 @@
           --enable-dba=shared --with-db4=%{_prefix} \
           --with-xmlrpc=shared \
           --with-ldap=shared --with-ldap-sasl \
    +%if %{with_mysqlnd}
    +      --with-mysql=shared,mysqlnd \
    +      --with-mysqli=shared,mysqlnd \
           --with-mysql=shared,%{_prefix} \
           --with-mysqli=shared,%{_bindir}/mysql_config \
    %ifarch x86_64
           %{?_with_oci8:--with-oci8=shared,instantclient,%{_libdir}/oracle/%{oraclever}/client64/lib,%{oraclever}} \
    @@ -725,7 +736,11 @@
           --enable-fastcgi \
           --enable-pdo=shared \
           --with-pdo-odbc=shared,unixODBC,%{_prefix} \
    +%if %{with_mysqlnd}
           --with-pdo-mysql=shared,%{_prefix} \
           --with-pdo-pgsql=shared,%{_prefix} \
           --with-pdo-sqlite=shared,%{_prefix} \
           --with-pdo-dblib=shared,%{_prefix} \
    @@ -756,6 +771,16 @@

    +%if %{with_mysqlnd}
    +without_shared="--without-gd \
    +      --disable-dom --disable-dba --without-unixODBC \
    +      --disable-xmlreader --disable-xmlwriter \
    +      --without-sqlite \
    +      --disable-phar --disable-fileinfo \
    +      --disable-json --without-pspell --disable-wddx \
    +      --without-curl --disable-posix \
    +      --disable-sysvmsg --disable-sysvshm --disable-sysvsem"
    without_shared="--without-mysql --without-gd \
           --disable-pdo --disable-xmlreader --disable-xmlwriter \
    @@ -764,6 +789,7 @@
           --without-curl --disable-posix \

    # Build Apache module, and the CLI SAPI, /usr/bin/php
    pushd build-apache

  3. Package with:
    rpmbuild -bb --with mysqlnd --define "rhel 5" SPECS/php-5.3.2-remi.spec
  4. To install via yum, change to the directory where rpms are located and recreate the repodata via:
    createrepo .
  5. Note: you may need to install some of the missing dependent devel packages. I used a combination of base, epel and remi repository to install the dependencies.

#2002 - The server is not responding

PhpMyAdmin Error:


There are various reasons for this error. Several things to check are:

  1. Edit file, and specify '' (instead of any other value like 'localhost) for the $cfg['Servers'][$i]['host'] setting as follows:


  2. Check my.cnf file for the following entries:



    Only add the 'old-passwords' mentioned above if you are using an older version of PHP (PHP4).

  3. Make sure that you do not have any older versions of MySQL installed that are conflicting.
  4. Edit file, and specify the MySQL socket location (as seen in the above [mysqld] socket setting):

    For example, if your MySQL socket is /tmp/mysql.sock, find the line in that has the $cfg['Servers'][$i]['socket'] setting and change it to:


  5. Set the connection mode to socket by changing $cfg['Servers'][$i]['connect_type'] to:

    If there is no such setting, simply add in the above line and re-try.

  6. Add this entry to php.ini file (again changing the '/tmp/mysql.sock' to your actual socket location) and restart Apache:

    mysql.default_socket = "/tmp/mysql.sock"

  7. Edit the using the phpmyadmin /setup configuration page:

    In the setup screen, select the end-of-line character to be '\r' if you have a Macintosh, or '\n' for a Linux machine.

  8. Disable SELinux if you are using it, and re-try. If it works, SELinux is the issue.
  9. Run the following at your mysql prompt (using the standard mysql client):
    mysql> SELECT USER,HOST FROM mysql.user;

    Next, identify the user your are using for phpMyAdmin, and then run:

    mysql> SHOW GRANTS FOR 'user'@'host';

    Ensure that this userid has appropriate access rights.

Syndicate content