Set up a remote MySQL database server

Set up a remote MySQL database server

2018-10-14 THUDM team Eunbi Choi

0. Environment

   OS : Ubuntu 16.04

1. Install

   # apt-get update
   # apt-get install mysql-server

2. Start server

   # service mysql start

3. Stop server

   # service mysql stop

4. Change bind address

   Enable remote connection.

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

   #
   # Instead of skip-networking the default is now to listen only on
   # localhost which is more compatible and is not less secure.
   bind-address            = 0.0.0.0

   You also need to open 3306 port.

5. Connect to database (localhost)

   # mysql -u root -p

6. Allow external IP connection of root user

   USE mysql;
   GRANT ALL PRIVILEGES ON *.* to 'root'@"%" WITH GRANT OPTION;
   FLUSH PRIVILEGES;

7. Using root account, remote user can connect to database

 

Trouble Shooting

Access denied for user 'root'@'%' (using password: YES) Error

It happens when root account's password is wrong or cannot access to the account. We can solve it by resetting root account's password.

Solution :

UPDATE user SET authentication_string=password('1234') where user='root';
FLUSH PRIVILEGES;