Retrieve nearest drivers - query- laravel mysql

$results = \DB::select(DB::raw('SELECT id, ( 3959 * acos( cos( radians(' . $order->delivery_lattitude . ') ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(' . $order->delivery_logitude . ') ) + sin( radians(' . $order->delivery_lattitude . ') ) * sin( radians(lat) ) ) ) AS distance FROM rider_location HAVING distance < ' . $distance . ' ORDER BY distance'));

solve access denied issue to mysql server from remote machine

netstat -l --tcp -n -p

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      -

sudo nano /etc/mysql/mysql.conf.d and comment "bind  127.0.0.1:3306"

 

netstat -l --tcp -n -p

 

tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN      17043/apache2

CentOS6.6 MySql Login with admin password

1. cat /etc/psa/.psa.shadow
2. Display Eg : "$AES-128-CBC$33yZTimLYQFax/iFgiQGkQ==$gqGlImLkTalp26cDeS3C9g=="
3. mysql -u admin -p
4. When say Enter Password: Just enter above displayed password. And hit Enter.

Plesk 12 Login error after changing MySql Password -Resolved

1. old-passwords = 1 should be disabled in the /etc/my.cnf file.
2. Obtain the correct Plesk password: cat /etc/psa/.psa.shadow ($AES-128-CBC$33yZTimLYQFax/iFgiQGkQ==$gqGlImLkTalp26cDeS3C9g==)
3. sudo /etc/init.d/mysqld stop
4. sudo mysqld_safe --skip-grant-tables &
5. mysql -u admin
6. update mysql.user set password=PASSWORD("$AES-128-CBC$33yZTimLYQFax/iFgiQGkQ==$gqGlImLkTalp26cDeS3C9g==") where User='admin';
7. flush privileges;
8. sudo /etc/init.d/mysqld stop
9. sudo /etc/init.d/mysqld start

Update the password for Plesk using the ch_admin_passwd utility:

1. /usr/local/psa/bin/admin --show-password
2. export PSA_PASSWORD=Abe3s3
3. /usr/local/psa/admin/bin/ch_admin_passwd

Change MYSQL Root Password

Do not confuse server root user with the MySQL root user.

Server's main user is server root. The MySQL root(admin) user has complete control over MySQL only.
This two 'root' users are not connected.

Stop MySQL

Ubuntu or Debian:
sudo /etc/init.d/mysql stop

For CentOS, Fedora, and RHEL:
sudo /etc/init.d/mysqld stop

Safe mode
Next we need to start MySQL in safe mode - start MySQL but skip the user privileges table.

sudo mysqld_safe --skip-grant-tables &
The ampersand (&) at the end of the command is required.

Login
Log into MySQL and set the password.

mysql -u root
when we started MySQL we skipped the user privileges table.So no password needed.

Next, instruct MySQL which database to use:

use mysql;(Where all details kept)(in mysql db there is user table. All users stored there with passwords.
We are going to change That user's table's root user's password. If there is no user called root in this table,
We need to get main user from this table. once my main user was admin. So instead "root" I had to use "admin")

Reset Password

Enter the new password for the root user as follows:
update user set password=PASSWORD("mynewpassword") where User='root'; //where User='admin'

Flush the privileges:
flush privileges;

Restart
Now the password has been reset, we need to restart MySQL by logging out:
quit

Stop and Star MySQL.

Ubuntu and Debian:
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

CentOS and Fedora and RHEL:
sudo /etc/init.d/mysqld stop
sudo /etc/init.d/mysqld start

Login
Again login and test password:
mysql -u root -p // mysql -u admin -p

MySQL Triggers

BEGIN
declare msg varchar(255);
IF NEW.update_by = 0  OR NEW.update_by='' OR NEW.update_by=Null THEN
set msg = concat('MyTriggerError: Trying to insert a invalied value: ', cast(new.id as char));
signal sqlstate '45000' set message_text = msg;  
END IF;
END

====================
CREATE TRIGGER `allow_with_update_by` BEFORE INSERT ON  `table_order`
FOR EACH
ROW BEGIN
IF NEW.update_by <>0
THEN
SET NEW.update_by = 2;
END IF ;
END ;

Restore a large MySQL database using cmd in Windows machine

First open the cmd

Change the directory to mysql installed directory's bin. Mine is
D:/wamp/bin/mysql/mysql5.5.24/bin

D:/wamp/bin/mysql/mysql5.5.24/bin> mysql -u root -p

mysql> create database mydb;
mysql> use mydb;
mysql> source db_backup.sql;

For dump file
mysql> source db_backup.dump;

OR
First open the cmd

Change the directory to mysql installed directory's bin. Mine is
D:/wamp/bin/mysql/mysql5.5.24/bin

D:\wamp\bin\mysql\mysql5.5.24\bin>mysql -u root -p testing1 < D:\test2.sql

OR (For file type :File MySQL dump 10.13  Distrib 5.1.73, for redhat-linux-gnu (x86_64))
First open the cmd

Change the directory to mysql installed directory's bin. Mine is
D:/wamp/bin/mysql/mysql5.5.24/bin

D:\wamp\bin\mysql\mysql5.5.24\bin>mysql -u root testing1<test