Creating Databases in MySQL
A simple guide on how to use Sequel Pro to setup and create a database.
I could not find a simple guide on how to create a database and set permissions to it using a GUI. Seems like it should be table stakes at this point, but here is a guide!
Most articles I found showed how to set one up with the CLI. This is a useful skill but I find I often pull out Sequel Pro, which is a trusty database tool I have been using for years. It's simple, and a great tool when you are learning. It also comes bundled with MAMP. If you need more more advanced functionality a good alternative is the open source MySQL Workbench, to boot workbench is also cross platform.
On to the steps!
First we need to make sure a database is running, figure out what port its on, and know the root username and password.
One bit of advice I will provide up front. I never install databases or any kind of application specific software directly on my machines. It is a source of confusion, and most often leads to port collisions when you do run something through a VM on the same port.
I typically will use the actual port of the service if I can on the host machine through port forwarding to the VM or Docker. This is much simpler, and if you never run the actual software on your machine there won't be any collisions to worry about. The key exception would be websites, most tools like create-react-app or webpack-dev-server will run on obtuse ports, typically :3000, :4000 etc.
For the purposes of this guide we are going to assume we are working with MAMP. Let's keep it simple as other (more modern) alternatives like Docker etc differ in how they are setup, and typically get more granular.
MAMP Setup:
So let's turn it on.
Start MAMP and open the preferences with <command+,>:
Go to ports:
Click the button "Set Web & MySQL ports to 80 & 3306".
Ah heaven. But really it doesn't matter much you can always set the port in your configuration of whatever application you are writing. But by default you don't have to define a port when setting up your apps. I typically do, but I like to be verbose.
Now that we have MAMP setup correctly, close the settings and turn the servers on. By clicking the "Start Servers" text on the main screen.
On the start page of MAMP you can see the root user and password, which is typically "root" and "root".
A word on opsec:
We need to connect to the database, but that requires some port and password setup.
MAMP by default does not make the port :3306 accessible from your host machine, you have to connect to it with a .sock configuration. This is a security feature to ensure no one can connect to your machine from the outside on the :3306 port. This would make your laptop accessible via that port on your LAN. This could potentially be bad if say... you are at a coffee shop with the root credentials set as "root" "root" and you put your companies database on your MySQL instance with real data. Maybe don't do that.
So here is one more side note. I paid for MAMP Pro years ago when I was using it a lot. I don't use it as much these days because things like React create-react-app etc come with their own tooling, or I use Docker for more complex setups. But I always find times that I go back to MAMP to work on something. Typically things like Laravel are much easier to develop on MAMP and it has the ability to map multiple hosts on the same machine to port :80.
That last part... well it requires you to pay for pro. This whole article is not an ad for them, but I have used MAMP Pro a lot over the years for professional work, so it has more than paid for itself.
So... with that said... lets configure some more MySQL settings in pro.
Go to the advanced settings screen by clicking MySQL in the sidebar and lets make some changes:
Let's change the root password. I used 1Password to generate one for me and saved it.
Connecting to the database:
Open Sequel Pro and setup the database connection as shown below:
Click the "Test Connection" button and it should say "Connection Succeeded". If you get any kind of error check your password, the username should be "root", and make sure MAMP shows MySQL running.
The save this as a favorite and click connect.
We need to create a new database to connect to create a user for it, and grant privileges.
Let's do these one at a time.
Creating a database is simple. Click "Database" in the top bar and "Add Database".
Give it a name, something better than "test" I trust you can do it. Mine is tesseract .. yes.. infinite possibilities.
The options don't really matter for now. But there are different text storage types which matter, and database collations... yada yada yada... questions for Google.
Granting Permissions:
Click the little "Users" icon on the top right. It will bring up a screen where you can create users for the MySQL instance. One thing to note here users are specific to the MySQL instance, not the database directly. You have to create a user for your application and then grant permissions to the databases within MySQL to the new user.
Let's do that.
On the first screen you will see a list of users on the left. Click the + on the bottom left of the pane. Name the user something fun. Mine is cooper. You know, Interstellar.
Hit apply. Now some things in Sequel Pro are weird or buggy. The users screen is one of those things. The window will close... smash that Users button again.
Click on your user... ah cooper.
Go to the second tab "Global Privileges". Opsec note 2... You can click the "Check All" button I did. But understand when you are making a database for production that some of those check boxes don't need to be checked, and shouldn't be for security reasons.
Go to the third tab "Schema Privileges" and click your database schema mine is tesseract then select everything in the "Available Privileges" pane and click the arrow to the left. Very weird UI. But that will add those privileges to the database for the user. Cooper now has super powers.
Click "Apply" and we should be good to go.
Connect from your application:
Set up your application to use the database, username, and password we just set up and test the connection.
You can also create a new Sequel Pro connection favorite with the new user credentials and define the database. It should be able to connect.
For an application like Laravel setup your database connection and try to run a migration.
Update your .env file with the new connection information:
DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=tesseract
DB_USERNAME=cooper
DB_PASSWORD=12345678Amazing... and super cool password.
Then run the migrations (doesn't matter if its a fresh install, it will still try to make the migrations table:
php artisan migrateYou should see this output:
Migration table created successfully.That was fun.
Pretty straightforward. There are plenty of guides online with similar info, but I find they don't really walk you through it clearly or they assume you know the CLI. Many great articles like that exist you can check them out to learn more.
If you want to hear more things from me check out the podcast A Public Function you can get more info on my social page or you can contact me on my connect page.
If the social page doesn't load, refresh it again. There is a fun bug I have not had time to debug where it won't load data on first load. /webdevlife.
Thanks for reading!
UPDATE:
I have gotten some questions about how to ensure that the version of PHP and MySQL used in your shell is the version from MAMP.
I have some handle shell config scriptery that makes this happen. Also see my note above about NOT installing MySQL on the root system. I don't EVER do this.
To enable MAMP's bin and PHP version I setup some bash $PATH variables through a function called "mamp".
First we need to setup some variables to be used in the path:
##
#MAMP
##
MAMP_PHP_5=php5.6.32
MAMP_PHP_DEFAULT=php7.2.1
MAMP_BASE=/Applications/MAMP
MAMP=$MAMP_BASE/bin/php/$MAMP_PHP_DEFAULT/bin
MAMP_PHP_BASE=$MAMP_BASE/bin/php/
MAMP_LIBRARY_BIN=$MAMP_BASE/Library/binEnsure that the "MAMP_PHP_DEFAULT" matches the version found in the MAMP PHP version screen, and ensure it updates if you update PHP in MAMP.
If you use PHP 5 ensure that PHP 5 is installed in MAMP and the correct version string is in the MAMP_PHP_5 value.
Once those are setup you can create a function to swap the version:
##
# Activate MAMP PHP version
##mamp () {
local version=$1
if [ ! -z ${version} ]; then
if [ "${version}" == 5 ]; then
version=${MAMP_PHP_5}
else
version=${MAMP_PHP_DEFAULT}
fi
export PATH="$MAMP_PHP_BASE/${version}/bin:$MAMP_LIBRARY_BIN:$PATH"
else
export PATH="$MAMP:$MAMP_LIBRARY_BIN:$PATH"
fi
}This function is run as:
mamp 5 # For mamp 5mamp # default for default versionBefore running this function my path versions are:
λ ~/ which php
/usr/bin/php
λ ~/ which mysql
mysql not foundAfter they are correct:
λ ~/ which mysql
/Applications/MAMP/Library/bin/mysql
λ ~/ which php
/Applications/MAMP/bin/php/php7.2.1/bin/phpThis is key because anytime you run an artisan command in Laravel (or any other PHP based command like composer etc) it would be running with the default PHP version installed by the xCode command line tools, and any command run against mysql would run against either the one you installed with brew or something else.
Add the two above scripts to your bash config, which by default is "~/.bashrc" or "~/.bash_profile" or "~/.profile" depending on how your shell is configured. Or if you are using zsh its ".zshrc".
Good luck!
If you get stuck here is a good article.