Notes

MySQL - Remote access

author: Paul Kim

categories: mysql

tags: mysql

How to connect remotely to MySQL database

Steps:

  • Allow MySQL to connect to database from any ip address
  • Create a non-root MySQL user who can connect to any ip address
  • Restart MySQL

Allow MySQL to connect to database from any ip address

check bind-address value

SHOW VARIABLES LIKE 'bind_address';

comment out bind-address (or set it to 0.0.0.0) in my.cnf

# find my.cnf
mysql --help | grep /my.cnf | xargs ls

/etc/mysql/my.cnf or /usr/local/etc/my.cnf

# bind-address = 127.0.0.1
bind-address = 0.0.0.0

Create a non-root MySQL user who can connect to any ip address

create a non-root user with access to localhost and % (or better yet, specify the ip address)

-- get users
SELECT user,host,plugin FROM mysql.user;
-- get current user
SELECT USER(), CURRENT_USER();

-- create user
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP
ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

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

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP
ON *.* TO 'username'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Note: change username and password to unique and hard-to-guess values.

Restart MySQL

# mac
brew services restart mysql
mysql.server restart

# linux
service mysqld restart
systemctl restart mysqld

ChromeEdgeFirefoxOpera

© 2021 paulkode.com. All rights reserved.