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
yum -y install mariadb-server mariadb systemctl start mariadb.service systemctl enable mariadb.service mysql_secure_installation mysql -u root -p
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 roundcubemail; GRANT ALL PRIVILEGES ON roundcubemail.* TO [email protected] IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
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;
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;
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;
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;
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;
Find User
SELECT User,Host FROM mysql.user;
Delete User
DROP USER 'backup'@'localhost'; FLUSH PRIVILEGES;
rm $HOME/.mysql_history