Mysql Operations

Create user in mysql 

CREATE USER username@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* To 'username'@'%' ;

Disable Password policy:

SET GLOBAL validate_password.policy=LOW

SET GLOBAL validate_password_policy=LOW;

Check total total users created into mysql,

select User,Host from mysql.user;

Provide all rights to root user,

GRANT ALL PRIVILEGES ON *.* To 'root’@'%' ; 

FLUSH PRIVILEGES;

 

Apply below Query

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

If below error while restoring dump then,

ERROR 1231 (42000) at line 347: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

Remove 'NO_AUTO_CREATE_USER' word from the .sql file

sed -i 's/NO_AUTO_CREATE_USER,//g' database_dump.sql

Provide database rights to users.

GRANT ALL PRIVILEGES ON db.* To 'user1'@'%' ;

GRANT SELECT ON db.* To 'user1'@'%' ;

FLUSH PRIVILEGES;

Create new user with full privilleges,

 

CREATE USER user1@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* To 'user1'@'%' ;

FLUSH PRIVILEGES;

Provide % access to root user in mysql 8

 

update mysql.user set host="localhost" where user="root";

FLUSH PRIVILEGES;

Provide Select Query access of all tables,

CREATE USER user2@'%' IDENTIFIED BY ‘password’;

GRANT SELECT ON db_name.* To 'user2'@'%' ;

Provide full Grant rights to mysql user,

 

GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

UPDATE mysql.user SET Host='localhost' WHERE Host='%' AND User='root';

FLUSH PRIVILEGES;

 

Logout Mysql from root user and re-login.