Saturday, 8 June 2013

Slow query log parser

A very nice MySQL log analyzer tool from Percona

#!/usr/bin/perl 
#
# Nathanial Hendler
# http://retards.org/
#
# 2001-06-26 v1.0
#
# This perl script parses a MySQL slow_queries log file
# ignoring all queries less than $min_time and prints
# out how many times a query was greater than $min_time
# with the seconds it took each time to run.  The queries
# are sorted by number of times it took; the most often
# query appearing at the bottom of the output.
#
# Usage: mysql_slow_log_parser logfile
#
# ------------------------
# SOMETHING TO THINK ABOUT (aka: how to read output)
# ------------------------
#
# Also, it does to regex substitutions to normalize
# the queries...
#
#   $query_string =~ s/\d+/XXX/g;
#   $query_string =~ s/([\'\"]).+?([\'\"])/$1XXX$2/g;
#
# These replace numbers with XXX and strings found in
# quotes with XXX so that the same select statement
# with different WHERE clauses will be considered
# as the same query.
#
# so these...
#
#   SELECT * FROM offices WHERE office_id = 3;
#   SELECT * FROM offices WHERE office_id = 19;
#
# become...
#
#   SELECT * FROM offices WHERE office_id = XXX;
#
#
# And these...
#
#   SELECT * FROM photos WHERE camera_model LIKE 'Nikon%';
#   SELECT * FROM photos WHERE camera_model LIKE '%Olympus';
#
# become...
#
#   SELECT * FROM photos WHERE camera_model LIKE 'XXX';
#
#
# ---------------------
# THIS MAY BE IMPORTANT (aka: Probably Not)
# --------------------- 
#
# *SO* if you use numbers in your table names, or column
# names, you might get some oddities, but I doubt it.
# I mean, how different should the following queries be
# considered?
#
#   SELECT car1 FROM autos_10;
#   SELECT car54 FROM autos_11;
#
# I don't think so.
#


$min_time       = 0; # Skip queries less than $min_time
$min_rows = 0;
$max_display    = 10; # Truncate display if more than $max_display occurances of a query

print "\n Starting... \n";

$query_string   = '';
$time           = 0;
$new_sql        = 0;


##############################################
# Loop Through The Logfile
##############################################

while (<>) {

 # Skip Bogus Lines

 next if ( m|/.*mysqld, Version:.+ started with:| );
 next if ( m|Tcp port: \d+  Unix socket: .*mysql.sock| );
 next if ( m|Time\s+Id\s+Command\s+Argument| );
 next if ( m|administrator\s+command:| );


 # print $_;
 # if ( /Query_time:\s+(.*)\s+Lock_time:\s+(.*)\s/ ) {
 #if ( /Query_time:\s+(.*)\s+Lock_time:\s+(.*)\s+Rows_examined:\s+(\d+)/ ) {
 if ( /Query_time:\s+(.*)\s+Lock_time:\s+(.*)\s+Rows_examined:\s+(.*)/ ) {

  $time    = $1;
  $rows  = $3;
  $new_sql = 1;
  # print "found $1 $3\n";
  next;

 }


 if ( /^\#/ && $query_string ) {

   if (($time > $min_time) && ($rows >= $min_rows)) {
    $orig_query = $query_string;

    $query_string =~ s/\d+/XXX/g;
    $query_string =~ s/'([^'\\]*(\\.[^'\\]*)*)'/'XXX'/g;
    $query_string =~ s/"([^"\\]*(\\.[^"\\]*)*)"/"XXX"/g;
    #$query_string =~ s/([\'\"]).+?([\'\"])/$1XXX$2/g;
    #$query_string =~ s/\s+/ /g;
    #$query_string =~ s/\n+/\n/g;

    push @{$queries{$query_string}}, $time;
    push @{$queries_rows{$query_string}}, $rows;
    $queries_tot{$query_string} += $time;
    $queries_orig{$query_string} = $orig_query;
    $query_string = '';

   }

 } else {
  
  if ($new_sql) {
   $query_string = $_;
   $new_sql = 0;
  } else {
   $query_string .= $_;
  }
 }

}


##############################################
# Display Output
##############################################

foreach my $query ( sort { $queries_tot{$b} <=> $queries_tot{$a} } keys %queries_tot )  {
 my $total = 0; 
 my $cnt = 0;
 my @seconds = sort { $a <=> $b } @{$queries{$query}};
 my @rows    = sort { $a <=> $b } @{$queries_rows{$query}};
 ($total+=$_) for @seconds;
 ($cnt++) for @seconds;

 print "### " . @{$queries{$query}} . " Quer" . ((@{$queries{$query}} > 1)?"ies ":"y ") . "\n";
 print "### Total time: " . $total .", Average time: ".($total/$cnt)."\n";
 print "### Taking ";
 print @seconds > $max_display ? "$seconds[0] to $seconds[-1]" : sec_joiner(\@seconds);
 print " seconds to complete\n";
 print "### Rows analyzed ";
        print @rows > $max_display ? "$rows[0] - $rows[-1]": sec_joiner(\@rows);
 print "\n";
 
 print "$query\n";
 print $queries_orig{$query}."\n\n";
}


sub sec_joiner {
 my ($seconds) = @_;
 $string = join(", ", @{$seconds});
 $string =~ s/, (\d+)$/ and $1/;
 return $string;
}

exit(0);

Friday, 31 May 2013

MariaDB master/slave Replication tutorial

{Note: There are no shortage of replication tutorials but I found this one particularly strightforward to follwo. It is originally written for MySQL but happily applied to MariaDB 10}

MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database. This can helpful for many reasons including facilating a backup for the data,a way to analyze it without using the main database, or simply as a means to scale out.

This tutorial will cover a very simple example of mysql replication—one master will send information to a single slave. For the process to work you will need two IP addresses: one of the master server and and one of the slave.

This tutorial will use the following IP addresses:

12.34.56.789- Master Database

12.23.34.456- Slave Database

Setup

This article assumes that you have user with sudo privileges and have MySQL installed. If you do not have mysql, you can install it with this command:

sudo apt-get install mysql-server mysql-client

Step One—Configure the Master Database

Open up the mysql configuration file on the master server.

sudo nano /etc/mysql/my.cnf

Once inside that file, we need to make a few changes.

The first step is to find the section that looks like this, binding the server to the local host:

bind-address            = 127.0.0.1

Replace the standard IP address with the IP address of server.

bind-address            = 12.34.56.789

The next configuration change refers to the server-id, located in the [mysqld] section. You can choose any number for this spot (it may just be easier to start with 1), but the number must be unique and cannot match any other server-id in your replication group. I’m going to go ahead and call this one 1. Make sure this line is uncommented.

server-id               = 1

Move on to the log_bin line. This is where the real details of the replication are kept. The slave is going to copy all of the changes that are registered in the log. For this step we simply need to uncomment the line that refers to log_bin:

log_bin                 = /var/log/mysql/mysql-bin.log

Finally, we need to designate the database that will be replicated on the slave server. You can include more than one database by repeating this line for all of the databases you will need.

binlog_do_db            = newdatabase

After you make all of the changes, go ahead and save and exit out of the configuration file.

Refresh MySQL.

sudo service mysql restart

The next steps will take place in the MySQL shell, itself.

Open up the MySQL shell.

mysql -u root -p

We need to grant privileges to the slave. You can use this line to name your slave and set up their password. The command should be in this format:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

Follow up with:

FLUSH PRIVILEGES;

The next part is a bit finicky. To accomplish the task you will need to open a new window or tab in addition to the one that you are already using a few steps down the line.

In your current tab switch to “newdatabase”.

USE newdatabase;

Following that, lock the database to prevent any new changes:

FLUSH TABLES WITH READ LOCK;

Then type in:

SHOW MASTER STATUS;

You will see a table that should look something like this:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | newdatabase  |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

This is the position from which the slave database will start replicating. Record these numbers, they will come in useful later.

If you make any new changes in the same window, the database will automatically unlock. For this reason, you should open the new tab or window and continue with the next steps there.

Proceeding the with the database still locked, export your database using mysqldump in the new window (make sure you are typing this command in the bash shell, not in MySQL).

mysqldump -u root -p --opt newdatabase > newdatabase.sql

Now, returning to your your original window, unlock the databases (making them writeable again). Finish up by exiting the shell.

UNLOCK TABLES;

QUIT;

Now you are all done with the configuration of the the master database.

Step Two—Configure the Slave Database

Once you have configured the master database. You can put it aside for a while, and we will now begin to configure the slave database.

Log into your slave server, open up the MySQL shell and create the new database that you will be replicating from the master (then exit):

CREATE DATABASE newdatabase;

EXIT;

Import the database that you previously exported from the master database.

mysql -u root -p newdatabase < /path/to/newdatabase.sql

Now we need to configure the slave configuration in the same way as we did the master:

sudo nano /etc/mysql/my.cnf

We have to make sure that we have a few things set up in this configuration. The first is the server-id. This number, as mentioned before needs to be unique. Since it is set on the default (still 1), be sure to change it’s something different.

server-id               = 2

Following that, make sure that your have the following three criteria appropriately filled out:

relay-log               = /var/log/mysql/mysql-relay-bin.log

log_bin                 = /var/log/mysql/mysql-bin.log

binlog_do_db            = newdatabase

You will need to add in the relay-log line: it is not there by default.

Once you have made all of the necessary changes, save and exit out of the slave configuration file.

Restart MySQL once again:

sudo service mysql restart

The next step is to enable the replication from within the MySQL shell.

Open up the the MySQL shell once again and type in the following details, replacing the values to match your information:

CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  107;

This command accomplishes several things at the same time:

It designates the current server as the slave of our master server.

It provides the server the correct login credentials

Last of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously.

With that—you have configured a master and slave server. Activate the slave server:

START SLAVE;

You be able to see the details of the slave replication by typing in this command. The \G rearranges the text to make it more readable.

SHOW SLAVE STATUS\G

Note that you should have both

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

If either of values is NO, you can try starting slave with a command to skip over it:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;

Also

grep mysql /var/log/syslog

May show some hints about the cause of problem.

All done.

Source
============================== Troubleshootings:
http://dev.mysql.com/doc/refman/5.0/en/replication-problems.html

Wednesday, 29 May 2013

Set FQDN on Debian

On Debin 9+ user

sudo hostnamectl set-hostname host.example.com

And to check:

hostnamectl

On Debian 8 and older

, the hostname is set at startup thanks to the script /etc/init.d/hostname.sh which uses the file /etc/hostname. We can use this file to update the computer hostname and its FQDN (fully qualified domain name).

Caution: in the /etc/hosts file, the hostname and FQDN order must be respected.

~$ echo "my-computer" > /etc/hostname

~$ echo "127.0.0.1 my-computer.my-domain.ext my-computer localhost" > /etc/hosts

~$ /etc/init.d/hostname.sh

You can check the change with the following lines:

~$ hostname

my-computer

~$ hostname --fqdn

my-computer.my-domain.ext

Source

Friday, 1 February 2013

Execute a bash script on multiple folders

curdir=$(pwd)
for folder in /path/to/Files/*; do 
  [ -d $folder ] && cd "$folder" && bash /path/to/conv.sh
done
cd $curdir

Thursday, 3 January 2013

Force Chrome to render Google search in English

1. In Chrome, click the wrench icon. (upper-right)

2. Click 'Options' [This item is called 'Preferences' on Macs]

3. Under the 'Basic' tab, select 'Manage Search Engines' in the Search section

4. At the bottom of the list of search engines, you'll see three fields that allow you to add a new one. Add this info into the three fields in the correct order:

Name: Google US Search Keyword: google.com/ URL: http://www.google.com/search?{google:RLZ}{google:acceptedSuggestion}{google:originalQueryForSuggestion}sourceid=chrome&ie={inputEncoding}&q=%s

(You should paste this link without any breaks or spaces)

5. Hit 'Enter' and your search engine should be saved. Finally, hover your mouse pointer over the newly added Google entry to see a button that reads 'Make default' - select this in order to change your default Google search.

Close the preferences tab and test your new Google search by typing a search term into the Omnibox (Chrome's address and search bar).

Source

Tuesday, 1 January 2013

Query cache check

mysql> show status like 'qc%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 3051 |
| Qcache_free_memory | 10365720 |
| Qcache_hits | 1650400 |
| Qcache_inserts | 757805 |
| Qcache_lowmem_prunes | 348499 |
| Qcache_not_cached | 9146 |
| Qcache_queries_in_cache | 10180 |
| Qcache_total_blocks | 24809 |
+-------------------------+----------+
8 rows in set (0.00 sec)

A couple important numbers from these values:

free space / free mem blocks = average free block size (in bytes)
10365720 / 3051 = 3398
used space / num queries in cache = average result size (in bytes)
(32 * 1024 * 1024 – 10365720) / 10180 = 2278

Source

Saturday, 29 December 2012

POST using Curl , Python and Lynx

Source:

Hemanth.HM

Assume that you have a form , in the source look for something similar to :

input name="rid"  type="TEXT"
input name="submit" value="SUBMIT" type="SUBMIT" align="center"

Then , based on your needs select any of the methods to POST the data to the form and get the required results back

Using curl :

curl -s -d "rid=value%&submit=SUBMIT" <URL> > out.html

Using Python :

#!/usr/bin/env python
import urllib
# URL to post to
url = 'URL'
values = {'rid':    rid, 
            'submit': 'SUBMIT'}
 
html = urllib.urlopen(url, urllib.urlencode(values)).read()
print html

Using Lynx to dump data :

 curl -sd 'rid=1ap05cs013&submit=SUBMIT' <URL> | lynx -dump -nolist -stdin > out.html

Friday, 21 December 2012

my.cnf for large database + high end server

#
# The MySQL database server configuration file for Large innodb database.
# This is one of my better my.cnf

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]

user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
#skip-innodb
default-storage-engine=InnoDB
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
#bind-address = 1.2.3.4
#
# * Fine Tuning
#
key_buffer = 192M
max_allowed_packet = 64M
thread_stack = 192K
thread_cache_size = 64
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
max_connections = 800

interactive_timeout= 5
wait_timeout= 5

log-slow-queries = /var/log/mysql/slow_query.log
long_query_time = 10

table_cache = 6048
thread_concurrency = 22

join_buffer_size = 64M
sort_buffer_size = 16M
max_heap_table_size = 4412M
max_connect_errors = 10
tmp_table_size = 4412M

#*** MyISAM Specific options
key_buffer_size = 64M
read_buffer_size = 4M
read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 128M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G

#finally added
table_definition_cache = 2048

#default-storage-engine = innodb
innodb_buffer_pool_size = 10000M
innodb_log_file_size = 1500M
innodb_flush_method = O_DIRECT #O_DSYNC
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
innodb_additional_mem_pool_size = 20M

# num cpu's/cores *2 is a good base line for innodb_thread_concurrency
innodb_thread_concurrency = 16

#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 128M
query_cache_min_res_unit = 1k

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1

log_error = /var/log/mysql/error.log

#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer = 16M

!includedir /etc/mysql/conf.d/

Saturday, 20 October 2012

drush functions

drush php-eval 'user_delete_cron();' &&
drush php-eval 'dblog_cron();' &&
drush php-eval 'filter_cron();' &&
drush php-eval 'node_cron();' &&
drush php-eval 'ping_cron();' &&
drush php-eval 'poll_cron();' &&
drush php-eval 'statistics_cron();' &&
drush php-eval 'update_cron();' &&
drush php-eval 'captcha_cron();' &&
drush php-eval 'ctools_cron();' &&
drush php-eval 'db_maintenance_cron();' &&
drush php-eval 'googleanalytics_cron();' &&
drush php-eval 'image_cron();' &&
drush php-eval 'messaging_cron();' &&
drush php-eval 'notifications_cron();' &&
drush php-eval 'privatemsg_cron();' &&
drush php-eval 'scheduler_cron();' &&
drush php-eval 'session_expire_cron();' &&
drush php-eval 'spam_cron();' &&
drush php-eval 'user_stats_cron();' &&
drush php-eval 'votingapi_cron();
#drush php-eval 'system_cron();' &&

Friday, 7 September 2012

Reset mysql forgotten password

First of all you will need to ensure that your database is stopped:

root@steve:~# /etc/init.d/mysql stop

Now you should start up the database in the background, via the mysqld_safe command:

root@steve:~# /usr/bin/mysqld_safe --skip-grant-tables &
[1] 6702
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6763]: started

Here you can see the new job (number "1") has started and the server is running with the process ID (PID) of 6702.

Now that the server is running with the --skip-grant-tables flag you can connect to it without a password and complete the job:

root@steve:~$ mysql --user=root mysql
Enter password:

mysql> update user set Password=PASSWORD('new-password-here') WHERE User='root';
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye

Source