Avoid subquery get records with max value for each group of grouped mySQL results

How do you get the rows that contain the max value for each grouped set. I faced issue many times but I ended with following solution of subquery and two queries and using one resultset result in another query.

Here is data of Employee table. I want max salary role based..users
Employee table Data:

Name	Salary	Role
David	130,000	Software Engineer
John	140,000	DevOps Engineer
Bob	120,000	Software Engineer
Sarah	130,000	DevOps Engineer
Alice	110,000	Software Engineer
Steve	95,000	DevOps Engineer

Old query which takes too much time and memory.

SELECT *
FROM Employee a
JOIN
(
SELECT max(salary) FROM Employee c
GROUP BY c.role
) b
ON a.ID = b.ID
ORDER BY book_count DESC LIMIT 1

Finally I found this query which solved my problem. following is correct query. I used this type query with 8CR rows..It works perfectly fine..But you need understand this.. Try to explain query before using it and do proper indexing…on column which are used in left outer join….

SELECT emp1.*, emp2.ID FROM
Employee AS emp1
LEFT OUTER JOIN Employee AS emp2 ON emp2.role = emp1.role AND emp2.salary > emp1.salary
WHERE emp2.salary IS NULL;

query result:

David	130,000	Software Engineer	NULL
John	140,000	DevOps Engineer	        NULL

Please note – for this solution to work, you need to make sure you have the correct index in place. In this example, you’ll need to create an index that includes the role and salary columns in order to avoid full table scans.

 

I found following articles Useful:

https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/


	

upgrade to mysql 5.7 in linux from old mysql version

In this tutorial we will show steps to upgrade to mysql 5.7 in linux. We have many server which are running on old mysql versions. What we want to do is, without loosing any data we want to upgrade mysql version.

We are using centos 6 as linux system for our servers. Following commands can be used for any linux os like fedora, debian, centos and redhat.

upgrade to mysql 5.7 in linux

First execute following command.
rpm -qa | grep mysql
Make a note of result. It will show you list of mysql programs which are installed on your system. In case of any issue this list will be with you.

Note: Before upgrading mysql version don’t forget to take mysql database backup of all databases.

Now download latest repository from mysql community server.
Go here and download repo https://dev.mysql.com/downloads/repo/yum/

Look for Red Hat Enterprise Linux 6 / Oracle Linux 6 (Architecture Independent), RPM Package
(mysql57-community-release-el6-7.noarch.rpm) You need to download this rpm.

Using following command you can install mysql 5.7 community repository.
rpm -Uvh mysql57-community-release-el6-7.noarch.rpm

After that stop mysql server using following command.
/etc/init.d/mysqld stop

After that update mysql community server on linux box. use following command.
yum update mysql-community-server

After that install or update following mysql 5.7 dependencies using following command
yum install gcc-c++ gperf uuid-devel libuuid libuuid-devel uuid boost-devel libevent libevent-devel

Install one another dependency of mysql server.
yum install mysql-community-libs-compat

Now your mysql server is updated to 5.7 version. First thing you need to do is check my.cnf file which is mysql configuration file. If my.cnf file is got replaced than put your old file and start mysql server using following command.
/etc/init.d/mysqld start

if mysql server is not starting than you need to start mysql server in safe mode using bellow command.

I faced many issues while mysql upgrade following is one of them. you can check mysql errrors in /var/log/mysqld.log file. I found following error.

InnoDB: pthread_create returned 11

I solved above issue by changing increase the stack size. I executed following command. which solved my issue.

ulimit -s 8192

mysqld_safe --skip-grant-tables &

Now it is time to upgrade your database to mysql 5.7 version. login to your mysql using root password.
mysql -u root -p

For upgrading your database use bellow command.
mysql_upgrade -u root -p --force

Congratulations! you successfully upgraded mysql version to 5.7 version.

install php mysql apache on centos 7

In this tutorial we are going to tell you how to install php mysql apache on centos 7 with very easy and simple steps and guide.
LAMP is becoming more popular these day. Many people are using LAMP for server solutions.

Add EPEL-7 to your linux os with latest phpMyadmin.

rpm -ivh http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm
Now install mysql-server and client. first became root user and execute following command
yum install mysql-server mysql
mysql_secure_installation

Install Apache2

CentOS 7.0 ships with apache 2.4. Apache2 is directly available as a CentOS 7.0 package, therefore we can install it like this:

yum -y install httpd

By default apache will be installed, but it is not installed then install apache using above command

Start apache and start apache at boot time using following command

systemctl start httpd.service
systemctl enable httpd.service

In CentOS 7.0 uses Firewall-cmd command, so I will customize it to allow external access to port 80 (http) and 443 (https).

firewall-cmd --permanent --zone=public --add-service=http
firewall-cmd --permanent --zone=public --add-service=https
firewall-cmd --reload

Now direct your browser to http://192.168.0.109.

Install PHP5

yum -y install php php-common phpmyadmin

We must restart Apache afterwards:

systemctl restart httpd.service

Now time to test php and apache version.
The document root of the default website is /var/www/html. We can create info.php file and put in document root folder

vi /var/www/html/info.php

put following code in that file

<?php echo phpinfo(); ?>

Now we call that file in a browser (e.g. http://192.168.0.109/info.php):

Now we can install some useful PHP moduels which can be required for CMS Systems like WordPress, Joomla and Drupal:

yum -y install php-gd php-ldap php-odbc php-pear php-xml php-xmlrpc php-mbstring php-snmp php-soap curl curl-devel

Now restart Apache2:

systemctl restart httpd.service

congrats! now you installed apache, php and mysql on your system. Now you are able to execute any php application on your server.

take mysqldump and send email

Taking backups is like day-to-day task for every system admins. In this turotials we will tell you to take mysqldump and send email through linux system.
We can take mysql database backup using cron job and mysqldump command.

We created shell script called db-backup-email.sh. Which can be used on any linux based system. We tested this script on Red Hat,CentOS, Fedora, Debian and Ubuntu systems. It is working fine.

You can download this mysql backup shell script from here..

take mysqldump and send email

How to use this script?

use following command to take mysql backup.

/bin/sh /path-to-sh-script/db-backup-email.sh mysqlusername mysqlpassword database_name your_email@email.com

With Cron
You can put this script on cron job file on linux. following command will execute at night at 2.00 am.

0 2 * * * /bin/sh /path-to-sh-script/db-backup-email.sh mysqlusername mysqlpassword database_name your_email@email.com

In shell script I written following code.

#!/bin/sh

MYSQLUSER=$1; #mysqluser
MYSQLPASS=$2; #mysqlpass
DBNAME=$3; #DBname
EMAIL=$4; #email to send

mysqldump -u $MYSQLUSER -p $MYSQLPASS $DBNAME | gzip > /tmp/$DBNAME.`date +%Y-%m-%d-%H`.sql.gz && echo "the mysqldump has been completed- status received at `date +%Y-%m-%d-%H`"|mail -s mysqldump-status -a "/tmp/$DBNAME.`date +%Y-%m-%d-%H`.sql.gz" $EMAIL

What is mysqldump?
The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

If you want to execute take dump only once than you can execute following mysqldump command.

mysqldump -u root -p sharetale | gzip > /tmp/serverDB.`date +%Y-%m-%d-%H`.sql.gz && echo "the mysqldump has been completed- status received at `date +%Y-%m-%d-%H`"|mail -s mysqldump-status -a "/tmp/serverDB.`date +%Y-%m-%d-%H`.sql.gz" purabdk@gmail.com

Many times E-mailing a database is a pretty bad idea, especially as it gets bigger and bigger, or security info is involved. But if your database is less than 5MB than you can send Database through email.

This script is for very small database. We just want to back it up every now and again in such a way that none technical members of the project can back up and restore.

There are some php scripts which can do same stuff but this script is much faster than other php script.

install apache 2.4, php 5.5 and mysql on centos 6/7

In the article we will tell you to install apache 2.4, php 5.5 and mysql on centos 6/7 with this we will guide you about installing phpmyadmin and related php and mysql modules.

LMAP is becoming more and more popular these days. People are using more vps and dedicated servers for their sites and applications. PHP backend is became more popular so here in this post we will tell you about installation on linux system’s.

Here I am using centos 6 for installation. From sept 2015 php 5.4 is no longer supported by PHP team so people are trying to install PHP 5.5+ version for good security and performance purpose on their linux servers.

install apache 2.4, php 5.5 and mysql on centos 6/7

Install Remi Repository

Remi is a repository where you can find the latest versions of the PHP  in this repository we can find latest programs and dependencies.

# yum update && yum install epel-release
# rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm

Enable Remi Repository

Open following remi.repo file and change enabled=0 to 1 for php55 tag. Here we want to install PHP 5.5 version on centos machine.

# vim /etc/yum.repos.d/remi.repo

After opening this file just edit this file and change enabled flag to 1 for php55 section
[remi]
name=Remi's RPM repository for Enterprise Linux 6 - $basearch
#baseurl=http://rpms.remirepo.net/enterprise/6/remi/$basearch/
mirrorlist=http://rpms.remirepo.net/enterprise/6/remi/mirror
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-remi

[remi-php55]
name=Remi's PHP 5.5 RPM repository for Enterprise Linux 6 - $basearch
#baseurl=http://rpms.remirepo.net/enterprise/6/php55/$basearch/
mirrorlist=http://rpms.remirepo.net/enterprise/6/php55/mirror
# NOTICE: common dependencies are in "remi-safe"
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-remi
 

Afer enabling remi repository. Now it is time to install PHP and apche and mysql

# yum --enablerepo=remi install httpd mysql mysql-server php php-common mod_php phpmyadmin

Step 4: Installing PHP Modules

Do not forget to accept installation and type Y when you asked while installation.     After installation run following command which will be useful while rebooting server(linux). After rebooting server following service will automatically started.   —— Enable Apache and MySQL on Boot ——

# chkconfig --levels 235 httpd on
# chkconfig --levels 235 mysqld on

—— Start Apache and MySQL ——

# /etc/init.d/httpd start
# /etc/init.d/mysqld start

Verify PHP 5.5

Go to /var/www/html directoy and create file called phpinfo.php using following command.

#vi phpinfo.php

put following code in that file.

<?php echo phpinfo(); ?>

Execute php.info file on your web browser

You can check your installation on command prompt also. For checking php modules use following command.

#Php -m

above command will list all php modules which are installed on your centos machine

Congratulations! Just now you completed your php 5.5, apache and mysql installation on your linux box.

 

speed up MySQL restore and backup solve slowness

I am taking daily mysqldump on my servers. I am always tring to speed up MySQL restore and backup using some tricks. Many times restoring mysql backup is like big pain. There will be many types of issues with restoring mysql dump to mysql server.

How can I speed up MySQL restore and backup?

Here is my trick to restore database dump fast.

You need to put following sql statement at the top of the dump SQL file.

SET @OLD_AUTOCOMMIT=@@AUTOCOMMIT, AUTOCOMMIT = 0;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS = 0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0;
You need to put following sql statement at the end of the SQL file

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET AUTOCOMMIT = @OLD_AUTOCOMMIT;
COMMIT;

Using above trick, I am able to restore any database much faster than earlier.
For more information you can look following URL:
http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

Lets talk about taking mysqldump faster.

There is utility called “MYDUMPER” which will take mysqldump much faster than mysqldump command. which is native command of mysql.

“Mydumper” and its companion “myloader”, are relatively new open
source database utilities written by Domas Mituzas and colleagues, providing
built-from-scratch implementations of mysqldump and mysql load
functionality. They clean up a bunch of command line inconsistencies
found in the mysql utilities, making them easier to script. They also fix
some errors and omissions. But their main feature is speed. By
paralleling the operation of dumping and loading database tables, quite dramatic
speed-up can be obtained.

The new Mydumper 0.9.1 version, which includes many new features and bug fixes, is now available. You can download the code from here.
https://launchpad.net/mydumper

Solved issue: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock

One day I faced issue with mysql.sock file. I found Can’t connect to local MySQL error. I solved the issue with following commands.

[root@localhost ~]# mysqladmin -u root shutdown
mysqladmin: connect to server at ‘localhost’ failed
error: ‘Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)’
Check that mysqld is running and that the socket: ‘/var/lib/mysql/mysql.sock’ exists!

Using following command I checked the mysql directory.
[root@localhost ~]# ll /var/lib/mysql/

I did not find the mysql.sock file there.

[root@localhost ~]# cd /var/lib/mysql/
Created file in that location.
[root@localhost mysql]# touch mysql.sock
Gave the executabe permission the sock file.
[root@localhost mysql]# chmod +x mysql.sock
Changed the permission to mysql user
[root@localhost mysql]# chown mysql:mysql -R *

After that restarted the machine
[root@localhost mysql]# /etc/init.d/mysqld start
Starting mysqld:                                           [  OK  ]
[root@localhost mysql]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.52 MySQL Community Server (GPL) by Remi

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> show databases;

mysql create user and grant all privileges on database

When we do the fresh installation of Mysql we got the root user without any password. Keeping and using the root user for application is not safe.

mysql create user and grant all privileges on database

mysql create user and grant all privileges on database
mysql create user and grant all privileges on database

For creating new user use the following commands.

CREATE USER 'wordpressapi'@'localhost' IDENTIFIED BY 'Test@123456';
GRANT ALL ON *.* TO 'wordpressapi'@'localhost';

Solved issue: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock

[root@localhost ~]# mysqladmin -u root shutdown
mysqladmin: connect to server at ‘localhost’ failed
error: ‘Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)’
Check that mysqld is running and that the socket: ‘/var/lib/mysql/mysql.sock’ exists!

Solved issue: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock

Using following command I checked the mysql directory.
[root@localhost ~]# ll /var/lib/mysql/

I did not found the mysql.sock file there.

[viral-lock message=”Solution is Hidden! It’s Visible for Users who Liked/Shared This article on Facebook or Twitter or Google+. Like or Tweet this article to reveal the content.”]

[root@localhost ~]# cd /var/lib/mysql/
Created file in that location.
[root@localhost mysql]# touch mysql.sock
Gave the executabe permission the sock file.
[root@localhost mysql]# chmod +x mysql.sock
Changed the permission to mysql user
[root@localhost mysql]# chown mysql:mysql -R *

[/viral-lock]

After that restarted the machine
[root@localhost mysql]# /etc/init.d/mysqld start
Starting mysqld:                                           [  OK  ]
[root@localhost mysql]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.52 MySQL Community Server (GPL) by Remi

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> show databases;

Can't connect to local MySQL server through socket
Can’t connect to local MySQL server through socket

accidentally deleted mysql root user

Some time back I accidentally deleted mysql root user. Then when checked mysql then mysql is not accessible to me from root user. I have the trick to restore mysql database.

accidentally deleted mysql root user

I am using the fedora 14 on my machine. After trying so many things at the end I found the solution.

use the following steps:

First I stoped mysql using following command.

#/etc/init.d/mysqld stop

Then I created one file with following conent

#file mysql_reset

UPDATE mysql.user SET Password=PASSWORD(‘newpassword’) WHERE User=’root’;

#mysqld_safe –init-file=/home/techiego/mysql-init &

Than again I stooped mysql process and restarted mysql. I was able to access my mysql again.

accidentally deleted mysql root user
accidentally deleted mysql root user