Change the stored procedure definer

UPDATE `mysql`.`proc` p SET definer = 'localhost@localhost:88' WHERE definer='localhost@%';

show procedure status;

Show query log in MYSQL

You can log every query to a long file really easily:

mysql> SHOW VARIABLES LIKE "general_log%";

+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                        |
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+----------------------------+

mysql> SET GLOBAL general_log = 'ON';

Do your queries (on any db). Grep or otherwise examine /var/run/mysqld/mysqld.log
Then don't forget to

mysql> SET GLOBAL general_log = 'OFF';

or the performance will plummet and your disk will fill!

What's the difference between MyISAM and InnoDB?

MYISAM:

1. MYISAM supports Table-level Locking
2. MyISAM designed for need of speed
3. MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
4. MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
5. MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
6. MYISAM supports fulltext search
7. You can use MyISAM, if the table is more static with lots of select and less update and delete.

INNODB:

1. InnoDB supports Row-level Locking
2. InnoDB designed for maximum performance when processing high volume of data
3. InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
4. InnoDB stores its tables and indexes in a tablespace
5. InnoDB supports transaction. You can commit and rollback with InnoDB

Mysql permission to single table

If the user account already exists: 
GRANT SELECT,INSERT,UPDATE,DELETE ON database_name.table_name TO 'username'@'localhost';

 If the user account hasn't been created yet you can set it up at the same time as its permissions:

GRANT SELECT,INSERT,UPDATE,DELETE ON database_name.table_name TO 'username'@'localhost' IDENTIFIED BY 'password';

Mysql permission to single table

If the user account already exists:
GRANT SELECT,INSERT,UPDATE,DELETE ON database_name.table_name TO 'username'@'localhost';

If the user account hasn't been created yet you can set it up at the same time as its permissions:

GRANT SELECT,INSERT,UPDATE,DELETE ON database_name.table_name TO 'username'@'localhost' IDENTIFIED BY 'password';







 

Restore mysql database on windows

Restore mysql database on windows

mysql -u root -p dbanme < db.sql

Mysql get last working day (satursday) of previous month

SET @dt = '2012-07-01'; -- Last day of the previous month (June 2012) was a Saturday

 SELECT CASE DAYOFWEEK(LAST_DAY(@dt-INTERVAL 1 MONTH)) 
        WHEN 1 THEN LAST_DAY(@dt-INTERVAL 1 MONTH)-INTERVAL 2 DAY 
        WHEN 7 THEN LAST_DAY(@dt-INTERVAL 1 MONTH)-INTERVAL 1 DAY 
        ELSE LAST_DAY(@dt-INTERVAL 1 MONTH)
        END x;

MYSQL get last working day(friday) of previous month

select LAST_DAY('2013-12-09' - INTERVAL 1 MONTH) -
INTERVAL (CASE WEEKDAY(LAST_DAY('2013-12-09' - INTERVAL 1 MONTH))
          WHEN 5 THEN 1
          WHEN 6 THEN 2
          ELSE 0 END) DAY

Mysql find last friday last month---GOOD.

SET @GETLASTM=LAST_DAY('2013-08-01' - INTERVAL 1 MONTH);
SELECT DATE_FORMAT(LAST_DAY(@GETLASTM) - ((7 + WEEKDAY(LAST_DAY(@GETLASTM)) - 4) % 7), '%Y-%m-%d') last_friday;

MySQL query get first and last day of month

MySQL query get first and last day of month

# For current month
SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01') AS `First Day Of Current Month`;

SELECT LAST_DAY(CURDATE()) AS `Last Day Of Current Month`;


# For previous month
SELECT DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m-01') AS `First Day Of Previous Month`;

SELECT LAST_DAY(CURDATE() - INTERVAL 1 MONTH) AS `Last Day Of Previous Month`;

# For next month
SELECT DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-01') AS `First Day Of Next Month`;

SELECT LAST_DAY(CURDATE() + INTERVAL 1 MONTH) AS `Last Day Of Next Month`; 

Mysql Date time

{$start} 00:00:00'  AND created<='{$end} 23:59:59'

mysql get last value of row

SELECT Name, Dt, Value
FROM (
    SELECT Name, Dt, Value, Name Name2
    FROM YourTable 
    UNION ALL
    SELECT '' Name, 'Last_Value', T.Value, T.Name Name2
    FROM YourTable T
       JOIN (
         SELECT Name, MAX(dt) MaxDt
         FROM YourTable
         GROUP BY Name
       ) T2 ON T.Name = T2.Name AND T.dt = T2.MaxDt
    ORDER BY Name2, Dt, Value
  ) t

mysql sum each group

CREATE TABLE YourTable (Name varchar(1),
                        dt datetime,
                        Value int);

INSERT INTO YourTable VALUES
('A','2013-01-01',3),
('A','2013-01-02',4),
('B','2013-01-04',2),
('B','2013-01-05',8);


SELECT Name, Dt, IF(Name='',summedTotal,Value) Value
FROM (

  SELECT @summedTotal:=IF(@prevRow=Name,@summedTotal+Value,Value) summedTotal,
    Name, Dt, Value, Name Name2,
    @prevRow:=Name
  FROM YourTable
    JOIN (SELECT @summedTotal:=0) t
  UNION ALL
  SELECT summedTotal, '' Name, 'Last_Value', T.Value, T.Name Name2, pr
  FROM (
      SELECT @summedTotal:=IF(@prevRow=Name,@summedTotal+Value,Value) summedTotal,
        Name, Dt, Value, Name Name2,
        @prevRow:=Name pr
      FROM YourTable
        JOIN (SELECT @summedTotal:=0) t
    ) T
    JOIN (
      SELECT Name, MAX(dt) MaxDt
      FROM YourTable
      GROUP BY Name
    ) T2 ON T.Name = T2.Name AND T.dt = T2.MaxDt
  ORDER BY Name2, Dt, Value
  ) t

Remove standy Linux

xset s off
xset s noblank
xset -dpms

to the ~/.X.d/xset file has worked for me.

forgot Mac OS X account password

If you have forgot Mac OS X account password then there is a tricky method which let you create a new administrator account on Mac OS X and through that admin account you can reset password of previous account.
So, let’s see how to create an administrator account on Mac while you lost your Mac passwords. This guide written using Mac OS X Snow Leopard.

Creating new Mac OS X account to recover old account

Step 1

Restart system and hold “Cmd + S” keys from keyboard until it shows Terminal.

Step 2

Now type the following commands one after one and press “Enter” button after each command to execute it individually
/sbin/mount -uaw
rm /var/db/.applesetupdone
reboot

Step 3

“Reboot” command will restart the Mac and you will see the “Welcome wizard” after beautiful welcome messages animation.

Follow all the wizard instructions, until you see the following screen to create new account. Fill all entries and hit Continue button.

This will create a new administrator account and automatically log into it.

Reset old user account password

Now you can reset the password of your old Mac OS X account using this new account, here is the process.

Step-4

Click on “Apple icon” and then “System Preferences”

Click on “Account”s icon

Step-5

Here you can see all Mac OS X user accounts are listed, new account “TrickyWays” that we have created and “Susan” the old one that we want to recover.
Click on lock icon to enable prev. account “Susan” and reset its password.

Enter the Password of current user account and hit OK button

Step-6

Now your prev. account is enabled for changes, select that and hit the “Reset Password” button

Enter “New password”, “Password hint” and hit “Reset Password” button to reset account password.

Now log off and log in to your prev. account “Susan” and you can delete the account “TrickyWays” that we have created to recover prev. account “Susan”.