Blog

Remove or revoke DB access or privileges from mysql user both local and remote: REVOKE ALL PRIVILEGES ON *.* FROM 'plugins_dbuser'@'localhost'; REVOKE ALL PRIVILEGES ON *.* FROM 'plugins_dbuser'@'%'; ...

here is how we can replace partial of the string on all rows in MySQL:  UPDATE my_table_name SET my_field_name = REPLACE(my_field_name, 'unwanted-string', 'new-string'); ...

Merge two user groups users into one current group, and delete the second group. Note: some users might be in both groups already. # Show users in groupID 111 are also in groupID 222 SELECT user_id FROM user_group  WHERE user_group_id={111} AND user_id IN (SELECT user_id FROM user_group WHERE user_group_id={222} )  # Change user_group_id 222 to 111. Use 'UPDATE IGNORE'  assume user_id & user_group_id are unique index UPDATE IGNORE user_group  SET user_group_id={111} WHERE...

Create a mysql user:   CREATE USER 'myUser'@'localhost' IDENTIFIED BY 'YourNewPassword';  CREATE USER 'myUser'@'%' IDENTIFIED BY 'YourNewPassword'; Then GRANT ALL ON *.* TO 'myUser'@'localhost';  GRANT ALL ON *.* TO 'myUser'@'%'; Optional: limit user to single database: GRANT ALL ON name_of_database.* TO 'myUser'@'localhost'; GRANT ALL ON name_of_database.* TO 'myUser'@'%'; ...

Create a remote user for single DB access. GRANT ALL PRIVILEGES ON new_db.* TO 'new_db_user'@'%' IDENTIFIED BY 'YourUserPassword'; FLUSH PRIVILEGES; ...