Install Mysql and connect with MysqlWorkbench

Install the Mysql

Mysql is one of the most commen used database in the word, and it perfectly works with PHP.

It is very easy to install Mysql in the Ubuntu.
Code is below:

$ sudo apt-get update

$ sudo apt-get upgrade

$ sudo apt-get install mysql-server

$ sudo mysql_secure_installation

The first two commands are for update and upgrade for the whole system.

The 3rd is install mysql server. After installation, you can

$ /etc/init.d/mysql start/stop

start or stop service.

The 4th is a secure process of install mysql. It can provide the basic security configuration.

There are some important information:

/usr/bin        - where mysql client and footscript 

/usr/sbin       - mysql server

/var/lib/mysql  - database and log [Important]

Mysql Workbench

Mysql workbench is a very good mysql management tool with good user interface.

If you want to connect to the remote mysql database, you still need some configuration.

what you should have

1. mysql server
2. remote ip address
3. mysql username and password
4. mysql workbench (could download from oracle)

Configuration

1. make sure your mysql server is running well on the server.
2. Goes to Server Management pannel and open mysql port, 3306. (This is in your service provide web page)
3. Build a new connection in Mysql Workbench, and input remote Ip address, username, and password. Test Connection.

Potential Errors

1. Error (61)
   This is because your server did not open for all ip address.

What you can do, go to your server.

$ netstat -tulpen

check the open ip address and port

And

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Change

bind-address = 127.0.0.1

to

bind-address = 0.0.0.0

Restart mysql server

$ /etc/init.d/mysql restart

After that, you may can login into your remote mysql database.

However, if you set up prohibit root remote login.

You can do the following process, go into mysql:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

The first code give all the rights to root and allow it can login from ‘%’ (means anywhere) with ‘password’ (it will cancel the original password when remotely login), root user can also grant other user.

The seconde code refresh the privilege and make it useful.

This is the method to allow root user login into DB remotely. However, it is not very safe to use it.

Thus, we can create other users to manage DB.

mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password'

About user management,
There are couples keywords:

CREATE, GRANT, SET, REVOKE, DROP

If you want to have profound knowledge, can explore by yourself.