How to Install MySQL Server on Ubuntu and Enable Remote Access

how-to-install-mysql-server-on-ubuntu-and-enable-remote-access.md

How to Install MySQL Server on Ubuntu and Enable Remote Access

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 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 %.