July 20, 2017

Setting Up MySQL

^ Ad space to help offset hosting costs :D

We'll setup MySQL so our application can connect to it. We need the following:

  1. A user
  2. A database
  3. To ensure the new user can connect to MySQL

Install

We'll first install MySQL, version 5.7, which comes out of the box on Ubuntu 16.04.

sudo apt-get install -y mysql-server 

Then we can, optionally, run the following to help secure the installation of MySQL:

sudo mysql_secure_installation

Database and User

We are then ready to create a new database, a user, and setup the user's privileges on that database:

create database myapp charset utf8mb4;

create user myuser@'localhost' identified by 'secret';

grant all privileges on myapp.* to myuser@'localhost';

flush privileges;

This creates a new database with the utf8mb4 character set. Then it creates a new user who can only connect over localhost, gives it a password of secret and sets those user's privileges to be able to use and change database myapp.

Finally we flush privileges to tell MySQL to re-cache its record of users and permissions in-memory.

Configure Laravel

All we need to do for Laravel is to edit the .env file:

DB_CONNECTION=localhost
DB_HOST=mysql
DB_PORT=3306
DB_DATABASE=myapp
DB_USERNAME=myuser
DB_PASSWORD=secret

We can test that these work by setting up Laravel's auth scaffolding:

php artisan make:auth
php artisan migrate

If the migration works, then we're all set. We can test registering a user and ensure there's a new user in our database.

localhost vs 127.0.0.1, and users

The last part of the video shows the difference between logging in over localhost and 127.0.0.1 - a Unix socket vs a TCP socket.

We then change MySQL's bind-address to the local private network and see we can still log in over host localhost but not 127.0.0.1, since MySQL is no longer listening on the loopback interface network (127.0.0.1).

All Topics