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.0Create 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