Category Archives: MYSQL

Reset MySQL Root/Admin/AnyUser forgoten password using SSH

Bash, MYSQL

Stop MySQL

#For Ubuntu or Debian
sudo /etc/init.d/mysql stop

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

Start MySQL in safe mode
start MySQL but skip the user privileges table, you will need to have sudo access for these commands so you don’t need to worry about any user being able to reset the MySQL root password:

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

Login MySQL
No password is required at this stage as when we started MySQL we skipped the user privileges table

mysql> mysql -u root

Tell MySQL which database to use

mysql> use mysql;

Check list of users from user

mysql> select User, Password from user;

Reset Password

mysql> update user set password=PASSWORD("mynewpassword") where User='root';

Flush the privileges

mysql> flush privileges;

Exit MySQL

mysql> quit

Stop and start MySQL

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

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

Login with new Password

mysql -u root -p

Compute similarity between two strings in MYSQL using Levenshtein algorithm

MYSQL, SQL 
DELIMITER $$
  CREATE FUNCTION levenshtein( s1 VARCHAR(255), s2 VARCHAR(255) )
  RETURNS INT
  DETERMINISTIC
  BEGIN
    DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
    DECLARE s1_char CHAR;
    -- max strlen=255
    DECLARE cv0, cv1 VARBINARY(256);
    SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0;
    IF s1 = s2 THEN
      RETURN 0;
    ELSEIF s1_len = 0 THEN
      RETURN s2_len;
    ELSEIF s2_len = 0 THEN
      RETURN s1_len;
    ELSE
      WHILE j <= s2_len DO
        SET cv1 = CONCAT(cv1, UNHEX(HEX(j))), j = j + 1;
      END WHILE;
      WHILE i <= s1_len DO
        SET s1_char = SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i)), j = 1;
        WHILE j <= s2_len DO
          SET c = c + 1;
          IF s1_char = SUBSTRING(s2, j, 1) THEN
            SET cost = 0; ELSE SET cost = 1;
          END IF;
          SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost;
          IF c > c_temp THEN SET c = c_temp; END IF;
          SET c_temp = CONV(HEX(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1;
          IF c > c_temp THEN
            SET c = c_temp;
          END IF;
          SET cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + 1;
        END WHILE;
        SET cv1 = cv0, i = i + 1;
      END WHILE;
    END IF;
    RETURN c;
  END$$
DELIMITER ;

Helper Function to find Ration in Percentage


DELIMITER $$
CREATE FUNCTION levenshtein_ratio( s1 VARCHAR(255), s2 VARCHAR(255) )
  RETURNS INT
  DETERMINISTIC
  BEGIN
    DECLARE s1_len, s2_len, max_len INT;
    SET s1_len = LENGTH(s1), s2_len = LENGTH(s2);
    IF s1_len > s2_len THEN 
      SET max_len = s1_len; 
    ELSE 
      SET max_len = s2_len; 
    END IF;
    RETURN ROUND((1 - LEVENSHTEIN(s1, s2) / max_len) * 100);
END$$
DELIMITER ;

Sample Usage

SELECT levenshtein_ratio(company_name, 'Company Name here') as perc, company_name FROM `clients` order by perc desc
/*return company name with percentage matching order by matching percentage.*/