# Install and Configure MySQL database on Ubuntu for remote connection over SSH with MySQL Workbench

> Install MySQL: After creating root user, login as root: To create and grant access to a user to a specific db use: Replace all the quotes(') parts and db.table with your relevant information. IP would be the server IP…

- Canonical: https://preview.arshadmehmood.com/tutorial/install-mysql-and-allow-remote-connection-over-ssh-ubuntu/
- Published: 2020-05-28
- Section: Tutorial
- Tags: mysql, ssh, remote, workbench, droplet, ubuntu
- Author: Arshad Mehmood

Install MySQL:

```
 sudo apt install mysql-server
 sudo mysql_secure_installation
```

After creating root user, login as root:

```
mysql -u root -p
```

To create and grant access to a user to a specific db use:

```
GRANT ALL PRIVILEGES ON databasename.tablename TO 'username'@'123.456.789.123' IDENTIFIED BY 'password';
```

Replace all the **quotes(')** parts and db.table with your relevant information. IP would be the server IP address.

If you want the user to be able to create new databases and modify permissions, then use:

```
GRANT ALL PRIVILEGES ON *.* TO 'username'@'123.456.789.123' IDENTIFIED BY 'password' WITH GRANT OPTION;
```

Save changes:

```
FLUSH PRIVILEGES;
exit
```


Bind MySQL to the server IP:

```
 sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```

Replace the line `bind-address = 127.0.0.1`  with `bind-address = 123.456.789.123` where `123.456.789.123` is your server IP.

Restart MySQL:

```
sudo systemctl restart mysql
```

Allow MySQL from Firewall:

```
sudo ufw allow mysql
```

Reload the Firewall rules:

```
sudo ufw reload
```

MySQL Workbench accepts SSH key in OpenSSH Format, so to covert your private ssh key to OpenSSH Format do these steps:

- Start **puttygen.exe**

- Load your **putty private key**

- Insert your **passphrase**

- Export **the key** (Conversions > Export OpenSSH Key [openssh_key.ppk])

Once the key is setup, we can configure the MySQL Workbench Connection.

Create a new connection. Set the connection name.

For Connection Method, select **Standard TCP/IP over SSH**

Under the Parameters Tab, enter the following information:

**SSH Hostname:** 123.456.789.123:22

**SSH Username:** {your SSH username}

**SSH Password:** leave blank/click “Clear” to be safe

**SSH Keyfile:** path to openssh_key.ppk we just created

**MySQL Hostname:** 123.456.789.123

**MySQL Port:** 3306

**Username:** your database username

**Password:** your database password

![ ](/assets/images/posts/mysqlwork.PNG)

Click Test Connection and everything should work fine.