Username & Password for MySQL Driver

Original problem

MySQL drivers will request a user and password. For instance, I was trying to use the go-sql-driver:

import "database/sql"
import _ "github.com/go-sql-driver/mysql"

db, err := sql.Open("mysql", "user:password@/dbname")

This should be pretty straight forward… except that I didn’t know where to find my database’s usernames and passwords or how to set them. When I accessed my local MySQL database, I always used this command:

mysql -u root

This command is signing into mysql as the root user without a password. I used frameworks in the past, they always glossed over the setup part and always used root.

Solution

Let’s set up a sample database called fooDatabase.

CREATE DATABASE fooDatabase;

Now, when you show the databases on localhost…

SHOW DATABASES;

You should see this:

mysql> SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| foodDatabase       |
| sys                |
+--------------------+

5 rows in set (0.02 sec)

Your usernames and passwords for your databases are located in the mysql database. So lets swap to that database:

USE mysql;

Your terminal will print out this:

Database changed

Let’s set up a new user and password.

mysql> CREATE USER 'user'@'localhost'
    -> IDENTIFIED BY 'password';

Next, lets give this user permission to use our existing database.

mysql> GRANT ALL PRIVILEGES ON fooDatabase.* TO 'user'@'localhost'
    -> IDENTIFIED BY 'password';

Now, user on your localhost has permissions to all of foodDatabase’s tables.

Great! Let’s wrap this all up by going back to the driver code. Replace the user, password, and database with whatever is appropriate. Now you can start manipulating your database with code instead of terminal commands!

import "database/sql"
import _ "github.com/go-sql-driver/mysql"

db, err := sql.Open("mysql", "user:password@/fooDatabase")