Installing MySQL on OSX

There are 2 main ways to install mySQL on OSX. Using Homebrew or a .dmg installer from MySQL. Here I’ll go over the MySQL installer because you get a neat panel to start and stop the database.

MySQL installer

Head over to https://dev.mysql.com/downloads/mysql/ and download the latest community version and then run through the install process.

You’ll then notice a MySQL option in your system preferences. When you open it, you’ll see some neat options to start your server and also a list of directories where the files are stored, basically in your usr/local folder.

Now that MySQL is running, how do you interact with it, log in and create tables? There are a few options. You can use CLI, try MySQL Workbench, phpMyAdmin or a number of other tools.

Interacting with MySQL with command line

CLI is the fastest to get up and running but not as user friendly as many of us would like.

To connect, you’ll have to run

> mysql -u root -p

Chances are, you’ll have to be in the bin directory (/usr/local/mysql-8.0.22-macos10.15-x86_64/bin) in order to execute this command. (Make sure MySQL is running first!

Here is a cheatsheet of some basic MySQL commands: http://g2pc1.bu.edu/~qzpeng/manual/MySQL%20Commands.htm

Common ones are:

• show database;
• use <database name>;
• show tables;
• drop database/table <name of db or table>

Interacting with MySQL with MySQL Workbench

Connecting MySQL Workbench to your local MySQL instance is simple and quick. Be sure to use the password you set when you initialised your database.

You can download MySQL Workbench at: https://dev.mysql.com/downloads/workbench/

Interacting with MySQL with phpMyAdmin

phpMyAdmin is a popular and light weight tool for managing your MySQL database. The only disadvantage here is that you’ll need Apache to be running as well and there are some minor steps required to set it up.

Firstly you need to start Apache.

sudo apachectl start

Then you need to download and install phpMyAdmin. Basically extract it and place the entire folder in your web server directory at: /Library/WebServer/Documents

Then you’ll need to create a config.inc.php file with the following:

$i++;
$cfg['Servers'][$i]['verbose'] = 'localhost';
$cfg['Servers'][$i]['host'] = '127.0.0.1';
$cfg['Servers'][$i]['port'] = '3306';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['auth_type'] = 'http';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = 'password_here';

Then restart your Apache server with:

sudo apachectl restart

Then you should be able to log in at http://localhost/phpmyadmin/