Setup MySQL on Linux and Allow Remote Connection

Author Avatar
Nathaniel Oct 08, 2016

Installation

Install MySQL

$ sudo apt-get install mysql-server libapache2-mod-auth-mysql php5-mysql

Activate MySQL. (Skip if after 5.7)

$ sudo mysql_install_db

Finsh up by running the MySQL setup script.

$ sudo mysql_secure_installation

Say yes to all the options.

Login & Quit

$ mysql -u [username] -p
Enter passowrd:
mysql> quit

Create User

mysql> CREATE USER '[newuser]'@'localhost' IDENTIFIED BY '[password]';
mysql> GRANT ALL PRIVILEGES ON * . * TO '[newuser]'@'localhost';
mysql> FLUSH PRIVILEGES;

Manage Permissions

mysql> GRANT [type of permission] ON [database name].[table name] TO '[username]'@'localhost';
mysql> REVOKE [type of permission] ON [database name].[table name] FROM '[username]'@'localhost';
mysql> DROP USER '[username]'@'localhost';

Allow Remote Connection

  1. Open /etc/mysql/my.cnf. (Or /etc/mysql/mysql.conf.d/mysqld.cnf if later than 5.7)
  2. Comment out this following line: bind-address = 127.0.0.1
  3. Run command sudo service mysql restart
  4. Enter mysql shell, execute the following command:
GRANT ALL PRIVILEGES ON *.* TO '[username]'@'[ip]' IDENTIFIED BY '[password]' WITH GRANT OPTION;

If all ip addresses are allowed, replace [ip] to %.