#mysql #bash mysql grant remote access from MySQL server and SSH tunnel

mysql-remote-access.md

Option 1 - SSH Tunneling

For quick access or when enabling remote access is not possible, ssh tunneling/port forwarding can be used to access a database (that is not allowed to be accessed remotely) temporarily

First create a SSH session with port forwarding

ssh -L 3300:localhost:3306 user@server

Then connect using the local port on local machine

mysql -P 3300 -uuser -p 

Option 2 - Enable and Grant remote access

modify conf file

vi /etc/mysql/mysql.conf.d/mysqld.cnf

and comment out the following line (i.e., disable it, mysql be default listens to only local machine)

bind-address            = 127.0.0.1

restart mysql server

sudo service mysql restart

if new user needed

mysql> create user 'username'@'localhost' identified by 'password';

allow access from all machines

mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

or from specific ip

mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

check to make sure

SELECT * from information_schema.user_privileges where grantee like "'username'%";

and run

mysql> FLUSH PRIVILEGES;

Revoke access

revoke all options for username from all machines

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'%';

revoke all options for username from particular IP:

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'1.2.3.4';

mysql_grant_access.sh

#!/bin/bash

db_user=user
db_pwd=pwd

#setting env var so that dont have to provide -p 
export MYSQL_PWD=$db_pwd

db_new_user=user
db_new_pwd=pwd

mysql -u$db_user -e "create user '$db_new_user'@'localhost' identified by '$db_new_pwd'; GRANT ALL PRIVILEGES ON *.* TO '$db_new_user'@'%' IDENTIFIED BY '$db_new_pwd' WITH GRANT OPTION; FLUSH PRIVILEGES; SELECT * from information_schema.user_privileges where grantee like \"'$db_new_user'%\""
sudo service mysql restart