MySQL – MySQL Create + Manage Users + Connect Remotely

Last updated on August 1st, 2019 at 12:38 pm

To connect remotely you have to have MySQL bind port: 3306 to your machines IP in my.cnf. Then you have to have created the user in both localhost and ‘%’ wildcard and grant permissions on all DB’s as such . See below:

my.cnf (my.ini on windows)

#Replace xxx with your IP Address 
bind-address        = xxx.xxx.xxx.xxx

mysql_secure_installation

SELECT User,Host FROM mysql.user;


CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';
Then

GRANT ALL ON *.* TO 'root'@'localhost';
GRANT ALL ON *.* TO 'root'@'%';

FLUSH PRIVILEGES;

———Create Database——–

CREATE DATABASE roundcubemail;
GRANT ALL PRIVILEGES ON roundcubemail.* TO [email protected] IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

!!!One User per host!!!

———Create Database + User and grant permission to user for specific database——–

mysql -uroot -p
CREATE DATABASE your_database_name;
CREATE USER 'YourDB_User'@'localhost' IDENTIFIED BY 'Your_Password';
GRANT ALL ON your_database_name.* TO 'YourDB_User'@'localhost' ;
FLUSH PRIVILEGES;

——Create user With Password———-

mysql -uroot -p
CREATE USER 'backup'@'%' IDENTIFIED BY '123456';
GRANT ALL ON roundcubemail.* TO 'backup'@'%';
FLUSH PRIVILEGES;
CREATE USER 'backup'@'localhost' IDENTIFIED BY '123456';
GRANT ALL ON roundcubemail.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
CREATE USER 'backup'@'127.0.0.1' IDENTIFIED BY '123456';
GRANT ALL ON roundcubemail.* TO 'backup'@'127.0.0.1';
FLUSH PRIVILEGES;
CREATE USER 'backup'@'::1' IDENTIFIED BY '123456';
GRANT ALL ON roundcubemail.* TO 'backup'@'::1';
FLUSH PRIVILEGES;

——Create user Without Password———-

mysql -uroot -p
CREATE USER 'backup'@'%';
GRANT ALL ON roundcubemail.* TO 'backup'@'%';
FLUSH PRIVILEGES;
CREATE USER 'backup'@'localhost';
GRANT ALL ON roundcubemail.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
CREATE USER 'backup'@'127.0.0.1';
GRANT ALL ON roundcubemail.* TO 'backup'@'127.0.0.1';
FLUSH PRIVILEGES;
CREATE USER 'backup'@'::1';
GRANT ALL ON roundcubemail.* TO 'backup'@'::1';
FLUSH PRIVILEGES;

——Create User with Grant options (WITH GRANT OPTION;)———

mysql -uroot -p
CREATE USER 'backup'@'localhost';
GRANT LOCK TABLES ON wiki.* TO 'backup'@'localhost';
GRANT LOCK TABLES ON wiki.* TO 'backup'@'your_server';
GRANT LOCK TABLES ON wiki.* TO 'backup'@'127.0.0.1';
GRANT LOCK TABLES ON wiki.* TO 'backup'@'%';
GRANT ALL ON wiki.* TO 'backup'@'localhost' WITH GRANT OPTION; ------to spesific database
GRANT ALL ON *.* TO 'backup'@'localhost' WITH GRANT OPTION; ------to All databases
FLUSH PRIVILEGES;

 ——Create Super Admin User with access from any host—–

mysql -uroot -p
CREATE USER 'your_admin_user'@'%' IDENTIFIED BY 'your_admin_user_password';
GRANT ALL ON *.* TO 'your_admin_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

 ——Delete User—–

Find User

SELECT User,Host FROM mysql.user;

Delete User

DROP USER 'backup'@'localhost';
FLUSH PRIVILEGES;

Delete MySQL History

rm $HOME/.mysql_history