Blog

Add a new blog
April 17

Remove or revoke DB access or privileges from mysql user both local and remote

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'@'%';


April 15

Update a column value, replacing part of a string

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');


February 9

MySQL - Merge two user groups users into one current group, and delete the second group.

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 


October 23

Create a MySQL user to have both local and remote access for all databases

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'@'%';

October 23

Query to enable a single user to access a remote MySQL database

Create a remote user for single DB access.

GRANT ALL PRIVILEGES ON new_db.* TO 'new_db_user'@'%' IDENTIFIED BY 'YourUserPassword';

FLUSH PRIVILEGES;


Send us a message. We will reply as soon as we can.
×

Save your professional blogger profile.

Join our blogger group and earn more income.

Sign up Log in